package LIMS::Model::Roles::Query;

use Moose::Role;

use LIMS::Local::QueryLibrary;
use SQL::Abstract::More;
use Data::Dumper;
use IO::All;

has search_fields_map => ( is => 'ro', isa => 'HashRef', lazy_build => 1 );
has relationships_map => ( is => 'ro', isa => 'HashRef', lazy_build => 1 );
has search_constraint => ( is => 'rw', isa => 'HashRef', default => sub { {} });

has sql_lib => ( is => 'ro', isa => 'LIMS::Local::QueryLibrary', lazy_build => 1 );

#-------------------------------------------------------------------------------
sub get_sql_with_constraint {
    my $self  = shift;
    my $entry = shift; # sql lib entry
    my $time_column = shift; # to set duration on
    my $default_constraint_type = shift || ''; # optional - to override return

    # set constraint & constraint_title params:
    $self->_set_search_constraints($time_column, $default_constraint_type);
    my $constraint = join ' AND ', $self->all_constraints; # list format

	my $sql = $self->sql_lib->retr($entry);
    $sql =~ s/%CONSTRAINT%/$constraint/; # warn $sql;

    return $sql;
}

#-------------------------------------------------------------------------------
sub get_date_constraints { # used by M::Audit methods using date_constratints.tt & not dbix
    my $self  = shift;
    my $time_column = shift; # to set duration on
    my $default_constraint_type = shift || ''; # optional - to override return

    $self->_set_search_constraints($time_column, $default_constraint_type);
    my $constraint = join ' AND ', $self->all_constraints; # list format
	return $constraint;
}

#-------------------------------------------------------------------------------
sub get_args_for_requests_count {
    my ($self, $search_constraints) = @_; # warn Dumper $search_constraints;

    $self->search_constraint($search_constraints);

    # require data from these tables:
    my @tables = (); # none for count

    # format args_for_search:
    my $args_for_search = $self->_format_args_for_search(\@tables);

    return $args_for_search; # warn Dumper $args_for_search;
}

#-------------------------------------------------------------------------------
sub get_args_for_request_id {
    my ($self, $search_constraints)  = @_;

    $self->search_constraint($search_constraints);

    # require data from these tables:
    my @tables = qw( patients specimens referrers referral_sources clinical_trials );

    # format args_for_search:
    my $args_for_search = $self->_format_args_for_search(\@tables);
    return $args_for_search; # warn Dumper $self->args_for_search;
}

#-------------------------------------------------------------------------------
# recieves $args hashref with keys 'search_constraints' & 'args_for_search':
sub get_args_for_find_requests {
    my ($self, $args) = @_; # warn Dumper $args;

    my $search_constraints = $args->{search_constraints}; # $self->debug($search_constraints);

    # add $search_constraints to $self for _format_relationships():
    $self->search_constraint($search_constraints);

    # require data from these tables:
    my @tables = qw( patients referrers referral_sources diagnoses status_options);

    # format args_for_search:
    my @args = (\@tables, $args->{args_for_search});
    my $args_for_search = $self->_format_args_for_search(@args);

    return $args_for_search; # warn Dumper $self->args_for_search;
}

#-------------------------------------------------------------------------------
# requires keys cols (aref), joins (aref) OR tbl (str), optional where (href),
# sort_by (aref), group_by (aref), order_by (aref), limit (int), offset (int)
sub sqla_query {
    my ($self, $args) = @_; # warn Dumper $args;

    # always have cols:
    my $cols = $args->{cols} || die 'no columns supplied';
    my @params = ( -columns  => $cols );
    # either single table or table joins:
    my $from = $args->{joins}
        ? [ -join => @{ $args->{joins} } ]
        : $args->{tbl};
    push @params, ( -from => $from );

    # optional params where, group_by, order_by, limit, offset:
    push @params, ( -$_ => $args->{$_} )
        for grep $args->{$_}, qw(where order_by group_by limit offset); # warn Dumper @params;

    my ($sql, @bind) = SQL::Abstract::More->new->select(@params); # warn Dumper [$sql,\@bind];
    return ($sql, @bind);
}

#-------------------------------------------------------------------------------
# accepts pairs of params & returns them in a format suitable for RDBO::Manager
sub format_query {
    my ($self, %params) = @_;

    # RBDO::Manager needs query as arrayref of pairs:
    return [ %params ];
}

#-------------------------------------------------------------------------------
# accepts arrayref of table_names, returns arrayref of relationship names:
sub get_relationships {
    my ($self, $tables) = @_; # arrayref

	my $relationships_map = $self->relationships_map;

	# get relationship names needed for table joins:
	my @relationships = map {
		$relationships_map->{$_}->{rel_name}
	} grep {
		# check entry exists (or rel silently skipped & db gets hit again for missing data):
		$relationships_map->{$_} || die "no such entry '$_' in _build_relationships_map()"
	} @$tables;

    return \@relationships;
}

#-------------------------------------------------------------------------------
# accepts $required_relationships arrayref and optional $args_for_search hashref
# or creates new; adds 'query' arrayref from $self->search_constraints; adds
# 'with_objects' & 'require_objects' arrayrefs if required; returns $args_for_search
sub _format_args_for_search {
    my $self            = shift;
    my $tables          = shift; # arrayref
    my $args_for_search = shift || {}; # optional (contains limit, sort_by, etc)

    my $search_fields_map  = $self->search_fields_map; # warn Dumper $search_fields_map;
    my $relationships_map  = $self->relationships_map;
    my $search_constraints = $self->search_constraint; # warn Dumper $search_constraints;

    { # convert $search_constraints hashref format into arrayref for query func:
        my @params = %$search_constraints;

        # put 'query' into $args_for_search:
        $args_for_search->{query} = \@params;
    }

    # convert $tables array(ref) into a hash to prevent duplicate relationships
    # in FIELD block below:
    my %tables = map { $_ => 1 } @$tables; # warn Dumper \%tables;

    # get any additional table joins required for this query:
    FIELD:
    foreach my $field (keys %$search_constraints) { # warn $field;
        # get table name for 'key' of search_constraints (last_name, unit_number, etc):
        my $table_name = $search_fields_map->{$field}
        || next FIELD; # eg request_number, year - don't need additional table joins

        $tables{$table_name}++;
    }  # warn Dumper \%tables;

    # create 'with_objects' & 'require_objects' from data in %tables:
    foreach my $table (keys %tables) { # keys are unique so tbl only loaded once
        my $relationship_data = $relationships_map->{$table} # eg with => 'request_trial'
        || die "no such entry '$table' in _build_relationships_map()";

        my $join_type = $relationship_data->{join_type}; # 'with_objects' or 'require_objects'
        my $rel_name  = $relationship_data->{rel_name};  # eg 'patient_case.patient', etc

        # push relationship name onto $args_for_search $join_type key:
        push @{ $args_for_search->{$join_type} }, $rel_name;
    }

    if ( $args_for_search->{with_objects} ) {
        # needs multi_many_ok => 1 to silence warnings and nested_joins => 0 for speed:
        $args_for_search->{multi_many_ok} = 1;
        $args_for_search->{nested_joins}  = 0;
    } # warn Dumper $args_for_search;

	# set default 'order by':
	$args_for_search->{sort_by} ||= 'requests.id'; # not supplied in patient merge

    return $args_for_search;
}

#-------------------------------------------------------------------------------
sub _build_search_fields_map {
    my $self = shift;

    # create map of form field => $self->relationships_map name:
    my %search_fields_map = (
        # field_name        # in table
        last_name           => 'patients',
        first_name          => 'patients',
        dob                 => 'patients',
        nhs_number          => 'patients',
        unit_number         => 'patients',
		gender              => 'patients',
        name                => 'referrers',
        specimen_id         => 'specimens',
        patient_id          => 'patient_case', # added for HMRN new maligs link
        option_name         => 'request_options',
        trial_id            => 'clinical_trials',
        trial_number        => 'patient_trials',
        referral_source_id  => 'referral_sources',
        parent_code         => 'parent_organisations',
        external_reference  => 'request_external_ref',

		# for simple sql query:
		clinical_details    	        => 'request_report',
		morphology             	        => 'request_report',
		comment             	        => 'request_report',
		biopsy_site          	        => 'request_report',
		gross_description      	        => 'request_report',
		sample_code         	        => 'sample_code',
		'diagnoses.name'    	        => 'diagnoses',
        'referrers.name'    	        => 'referrers', # or could just use 'name' as above
		'display_name'      	        => 'referral_sources',
		'patients.id'       	        => 'patients',
		'unit_number'       	        => 'patient_case',
		'screens.description' 	        => 'request_initial_screen',
        'patient_notes.detail'          => 'patient_notes',
		results_summary       	        => 'results_summary',
        trial_name                      => 'clinical_trials',
        foreign_id                      => 'section_foreign_id',
        vialId                          => 'request_storage',
        'request_general_notes.detail'  => 'request_general_notes',
    );

    return \%search_fields_map;
}

#-------------------------------------------------------------------------------
# map of relationship names for tables, relative to LIMS::DB::Request:
sub _build_relationships_map {
    my $self = shift;

    my %relationship_map = (
      # table name => join-type / relationship name
		# require = 'inner join'; with = 'left outer join'
		authorised_reports => {
            join_type => 'require_objects',
            rel_name  => 'authorised_report',
		},
        clinical_trials => {
            join_type => 'with_objects',
            rel_name  => 'request_trial.trial',
        },
        diagnoses => {
            join_type => 'with_objects',
            rel_name  => 'request_report.diagnosis',
        },
		diagnostic_categories => {
			join_type => 'with_objects', # changed from diagnosis.icdo3_category.diagnostic_category
            rel_name  => 'request_report.diagnosis.diagnostic_category',
		},
        hospital_departments => {
            join_type => 'require_objects',
            rel_name  => 'referrer_department.hospital_department',
        },
        outreach_pack_dispatches => {
            join_type => 'require_objects',
            rel_name  => 'outreach_request_pack_dispatch',
        },
		parent_organisations => {
            join_type => 'require_objects',
            rel_name  => 'patient_case.referral_source.parent_organisation',
		},
        patients => {
            join_type => 'require_objects',
            rel_name  => 'patient_case.patient',
        },
        patient_case => {
            join_type => 'require_objects',
            rel_name  => 'patient_case',
        },
        patient_demographics => {
            join_type => 'require_objects',
            rel_name  => 'patient_case.patient.patient_demographic',
        },
        patient_gps => {
            join_type => 'require_objects',
            rel_name  => 'patient_case.patient.patient_demographic.referrer',
        },
        patient_notes => {
            join_type => 'with_objects',
            rel_name  => 'patient_case.patient.patient_note',
        },
        patient_practices => { # patient GP practices
            join_type => 'require_objects',
            rel_name  => 'patient_case.patient.patient_demographic.practice',
        },
        patient_trials => {
            join_type => 'require_objects',
            rel_name  => 'patient_case.patient.patient_trial',
        },
        referral_sources => {
            join_type => 'require_objects',
            rel_name  => 'patient_case.referral_source',
        },
        referrer_departments => {
            join_type => 'require_objects',
            rel_name  => 'referrer_department',
        },
        referrers => {
            join_type => 'require_objects',
            rel_name  => 'referrer_department.referrer',
        },
        request_external_ref => {
            join_type => 'with_objects',
            rel_name  => 'request_external_ref',
        },
		request_general_notes => {
            join_type => 'with_objects',
            rel_name  => 'request_general_note',
		},
		request_history => {
            join_type => 'require_objects',
            rel_name  => 'request_history',
		},
		request_history_user => {
            join_type => 'require_objects',
            rel_name  => 'request_history.user',
		},
        request_initial_screen => {
            join_type => 'with_objects',
            rel_name  => 'request_initial_screen.screen',
        },
        request_options => {
            join_type => 'with_objects',
            rel_name  => 'request_option.option',
        },
		request_report => {
            join_type => 'require_objects',
            rel_name  => 'request_report',
		},
		request_status => {
            join_type => 'require_objects',
            rel_name  => 'request_status.status_option',
		},
        request_storage => {
            join_type => 'require_objects',
            rel_name  => 'request_storage',
        },
        request_specimens => {
            join_type => 'require_objects',
            rel_name  => 'request_specimen.specimen',
        },
        request_specimen_detail => {
            join_type => 'with_objects',
            rel_name  => 'request_specimen_detail',
        },
        results_summary => {
            join_type => 'require_objects',
            rel_name  => 'results_summary',
        },
		sample_code => { # used by simple sql search only
            join_type => 'require_objects',
            rel_name  => 'request_specimen.specimen',
		},
        secondary_diagnosis => {
            join_type => 'with_objects',
            rel_name  => 'request_secondary_diagnosis.diagnosis',
        },
        section_foreign_id => {
            join_type => 'with_objects',
            rel_name => 'request_lab_section_foreign_id',
        },
        specimens => {
            join_type => 'require_objects',
            rel_name  => 'request_specimen',
        },
        status_options => {
            join_type => 'require_objects',
            rel_name  => 'status_option',
        },
    );

    return \%relationship_map;
}

=begin
my $type = $vars->{constraint_type};
my $method = "_build_${type}_sql";
my $sql = $self->$method($vars);
and have sub _build_lastyear_sql { ... } and such

you'll always have a constraint_type and you can dispatch by that,
e.g. simply use it to call a method named "handle_${constraint_type}_query"

the nice thing about dispatching to different methods per constraint type means
you can do things like put them into reusable roles and such
=cut

# sets __PACKAGE__->constraint & __PACKAGE__constraint_title:
sub _set_search_constraints {
    my $self = shift;
    my $time_column = shift; # warn $time_column;
    my $default_constraint_type = shift || ''; # optional - to override return

    my $vars = $self->params; # warn Dumper $vars;

    my $constraint_type # undef on 1st call, unless default set in model method
        = $vars->{constraint_type} || $default_constraint_type || '';

    my ($constraint, $title);

    # for checkboxes it's safe to examine $constraint_type, for textboxes &
    # selects, need to test for defined query param as it's possible to submit an
    # empty field, where constraint_type is defined but equivalent param is undef
    if ( $constraint_type eq 'all_data' ) {
        $constraint = qq!YEAR($time_column) IS NOT NULL!; # ie everything
        $title = 'all requests';
    }
    elsif ( $constraint_type eq 'this_year' ) {
        my $this_year = DateTime->now->year;
        $constraint = qq!YEAR($time_column) = $this_year!;
        $title =  'year ' . $this_year;
    }
    elsif ( $constraint_type eq 'one_month' ) {
        $constraint = qq!$time_column BETWEEN DATE_SUB(CURDATE(), INTERVAL
            1 MONTH) AND CURDATE()!;
        $title = 'last 1 month';
    }
    elsif ( $constraint_type eq 'year_since' && $vars->{year} ) { # check var sent
        $constraint = qq!YEAR($time_column) >= $vars->{year}!;
        $title = 'all from ' . $vars->{year};
    }
    # textboxes & select fields:
    elsif ( my $days = $vars->{days} ) { # $constraint_type eq 'days'
        $constraint = qq!$time_column >= DATE_SUB(CURDATE(), INTERVAL $days DAY)!;
        $title = 'previous ' . $days . ' days';
    }
    elsif ( my $year = $vars->{year} ) { # $constraint_type eq 'year'
        $constraint = qq!YEAR($time_column) = $year!;
        $title = 'year ' . $vars->{year};
    }
    # need both 'from' & 'to' dates:
    elsif ( $vars->{date_from} && $vars->{date_to} ) { # $constraint_type eq 'date_range'
        # NB: silently fails if date is invalid:
        my $start_date =
            ref $vars->{date_from} eq 'DateTime'
                ? $vars->{date_from}->ymd
                : LIMS::Local::Utils::date_to_mysql($vars->{date_from});
        my $end_date =
            ref $vars->{date_to} eq 'DateTime'
                ? $vars->{date_to}->ymd
                : LIMS::Local::Utils::date_to_mysql($vars->{date_to});

        # need to ensure no date conversion error:
        if ($start_date && $end_date) {
            $constraint = qq!$time_column BETWEEN '$start_date' AND '$end_date'!;
            $title = 'dates between ' . $vars->{date_from} . ' & ' . $vars->{date_to};
        }
    }
    # set default to previous calendar year if no form constraint or invalid date:
    $constraint ||= qq!$time_column BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
        AND CURDATE()!;
    $title ||= 'last 365 days';

    # set package accessors:
    $self->add_constraint($constraint);
    $self->constraint_title($title);
}

sub _build_sql_lib {
    my $self = shift;

    my $cfg = LIMS::Local::Config->instance;
    my $path_to_app_root = $cfg->{path_to_app_root}; # warn $path_to_app_root;

	my @libs = map {
        io( sprintf '%s/src/lib/%s.sql', $path_to_app_root, $_ )->slurp;
    } qw(library local chart);

    my $sql_lib = new LIMS::Local::QueryLibrary( { lib => \@libs } );
        # warn Dumper [$sql_lib->elements]; # array
    return $sql_lib;
}

1;