package Model::SQL;

use Moo;
use SQL::Abstract::More;
use Data::Printer alias => 'p';
use Types::Standard 'InstanceOf';

has sql_abs => ( is => 'lazy', builder => sub {  SQL::Abstract::More->new() } );
has dbix    => ( is => 'ro', isa => InstanceOf['Local::DB'] ); # for get_cols()

sub user_details {
    my ($self, $ref) = @_; # p $ref;

    # $ref = key => userId, where key = nhs_number, email or patient_id
    if ( $ref->{patient_id} ) { # patient_id is ambiguous, replace with p.id:
        $ref->{'p.id'} = delete $ref->{patient_id};
    } # p $where;

    my @cols = qw(
        p.dob
        p.nhs_number
        pa.email
        pa.patient_id
        pa.last_login
        pa.password
        pd.address
        pd.post_code
        pd.contact_number
        ref.name|GP
        rs.display_name|practice
    ); # p \@cols;
    # names cols depend on db in use (patient_access_demo or outreach_patient_access):
    unshift @cols, ( grep $_ eq 'last_name', $self->dbix->get_cols('patients') )
        ? qw( p.last_name p.first_name ) : qw(p.initials); # p \@cols;

    my @rels = (                   'patient_access|pa'          ,
        'pa.patient_id=p.id'    => 'patients|p'                 ,
        'pd.patient_id=p.id'    => 'patient_demographics|pd'    ,
        'pd.practice_id=rs.id'  => 'referral_sources|rs'        ,
        'pd.gp_id=ref.id'       => 'referrers|ref'              ,
    );
    my @params = (
        -columns  => \@cols,
        -from     => [ -join => @rels ],
        -where    => $ref,
        # -order_by => $sort_by,
	);
    return $self->sql_abs->select(@params);
}

sub get_pack_dispatches {
    my ($self, $patient_id) = @_; # p $patient_id;
    # last 10 requests & pack dispatches, reverse chronological order:
    my @cols = (
        'r.id',
        'r.request_number',
        'r.year',
        'r.registered',
        'IFNULL(d.count, 0)|section_data_count',
        'so.description|request_status',
        'fo.option',
        'fo.label|outcome',
        # p.status|patient_status',
        'rpd.pack_due',
        'rpd.pack_sent',
        'rpd.return_due',
    );
    my @rels = (                               'requests|r'                 ,
        'r.status_option_id=so.id'          => 'status_options|so'          ,
        'r.patient_case_id=pc.id'           => 'patient_case|pc'            ,
        'pc.patient_id=p.id'                => 'patients|p'                 ,
        # for deployment with access to HILIS4 db:
#       'pd.patient_id=p.id'                => 'hilis4.patient_demographics|pd',
#       'rs.request_id=r.id'                => 'hilis4.request_specimen|rs'    ,
#       q!rs.specimen_id=s.id,s.sample_code='CMP'!
#                                            => 'hilis4.specimens|s'           ,
        # left joins:
        '=>rpd.request_id=r.id'             => 'request_pack_dispatch|rpd'  ,
        '=>rfu.request_id=r.id'             => 'request_followup|rfu'       ,
        '=>rfu.followup_option_id=fo.id'    => 'followup_options|fo'        ,
        '=>d.request_id=r.id'               => 'lab_sections_data|d'        ,
    );
    my %where = ( 'p.id' => $patient_id );
    my @params = (
        -columns  => \@cols,
        -from     => [ -join => @rels ],
        -where    => \%where,
        -order_by => { -desc => 'r.id' }, # or just '-r.id' will work
        -limit    => 10,
	);
    return $self->sql_abs->select(@params);
}

1;