#!/usr/bin/perl

use strict;
use CGI;

use Opals::Constant;
use Opals::Context;

use SIP::Map;
use SIP::Template;
use SIP::Session;
use SIP::Utility;

my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;

# $cgi->param('aaa') returns an array of aaa
my $input = $cgi->Vars();

#my $op = $input->{'op'};
my $util = SIP::Utility->new();

my $template = SIP::Template->new(filename => 'patron_information.tmpl',);

my $language =
  SIP::Map::LANGUAGE->{ $input->{'language'} } || SIP::Map::LANGUAGE->{'000'};
my $transaction_datetime = $util->now();
my $hold_items_limit     = 0;
my $overdue_items_limit  = 0;
my $charged_items_limit  = 0;
my $items                = {};
my $count                = {};

#my $hold_items_count        = 0;
#my $overdue_items_count     = 0;
#my $charged_items_count     = 0;
#my $fine_items_count        = 0;
#my $recall_items_count      = 0;
#my $unavailable_holds_count = 0;
my $currency_type         = substr($util->getPreference($dbh, 'currency'), 0, 3);
my $fee_amount            = 0.00;
my $fee_limit             = 0.00;
my $home_address          = '';
my $email_address         = '';
my $home_phone_number     = '';
my $valid_patron_password = 'false';
my $ext_auth              = {};

my @cat = qw( hold overdue charged fine recall unavailable_hold );
foreach my $c (@cat) {
  $count->{$c} = 0;
}

my $session = SIP::Session->new(
  dbh               => $dbh,
  session_id        => $input->{'session_id'},
  patron_identifier => $input->{'patron_identifier'}
);
$session->validate(patron_identifier => 1);

my $screen_message = $session->get('screen_message');
my $user           = $session->get('user');

if ($session->isValid()) {
  my $uid = $user->getUID();

  $hold_items_limit    = 0;    #_get_hold_items_limit($dbh, $uid);
  $overdue_items_limit = 0;    #_get_overdue_items_limit($dbh, $uid);
  $charged_items_limit = $user->{'maxloans'};

  my @cat = qw( hold overdue charged fine recall unavailable_hold );

  $items->{'hold'} = _get_hold_items($dbh, $uid);
  $items->{'overdue'} = _get_overdue_items($dbh, $uid);
  $items->{'charged'} = _get_charged_items($dbh, $uid);
  $items->{'fine'} = _get_fine_items($dbh, $uid);
  $items->{'recall'} = _get_recall_items($dbh, $uid);
  $items->{'unavailable_hold'} = _get_unavailable_hold_items($dbh, $uid);

  foreach my $c (@cat) {
    $count->{$c} = ($items->{$c}) ? scalar(@{ $items->{$c} }) : 0;
  }

  $fee_amount = $user->getBalance();

  my $s = $input->{'summary'};

  my $requested_category = '';
  if ($input->{'summary'} =~ m/^( *)Y/) {
    my $pos = length($1);
    ($pos < scalar(@cat)) && ($requested_category = $cat[$pos]);
  }

  foreach my $c (keys %{$items}) {

    # clear all list unless requested category matched
    @{ $items->{$c} } = () unless ($c eq $requested_category);
  }

  $home_address = <<_STR_;
$user->{'addrLine1'}
$user->{'addrLine2'}
$user->{'city'} $user->{'state'}  $user->{'zip'}
_STR_
  $email_address     = $user->{'email'};
  $home_phone_number = $user->{'phone'};

  $valid_patron_password =
    $user->isValidPatronPassword($input->{'patron_password'});
  if ($input->{'ppr'} eq 'false' || $valid_patron_password eq 'true') {
    $ext_auth = $user->getPermissions();
  }
}

my $response = {
  debug               => $session->get('debug'),
  patron_status       => $user->getPatronStatus(),
  language            => $language,
  transaction_date    => $util->format_datetime($transaction_datetime, 'SIP'),
  hold_items_count    => $count->{'hold'},
  overdue_items_count => $count->{'overdue'},
  charged_items_count => $count->{'charged'},
  fine_items_count    => $count->{'fine'},
  recall_items_count  => $count->{'recall'},
  unavailable_holds_count => $count->{'unavailable_hold'},
  institution_id          => $input->{'institution_id'},
  patron_identifier       => $user->getPatronIdentifier(),
  personal_name           => $user->getPersonalName(),
  hold_items_limit        => $hold_items_limit,
  overdue_items_limit     => $overdue_items_limit,
  charged_items_limit     => $charged_items_limit,
  valid_patron            => $user->isValidPatron(),
  valid_patron_password   => $valid_patron_password,
  hold_items              => \@{ $items->{'hold'} },
  overdue_items           => \@{ $items->{'overdue'} },
  charged_items           => \@{ $items->{'charged'} },
  fine_items              => \@{ $items->{'fine'} },
  recall_items            => \@{ $items->{'recall'} },
  unavailable_hold_items  => \@{ $items->{'unavailable_hold'} },
  currency_type           => $currency_type,
  fee_amount              => $fee_amount,
  fee_limit               => $fee_limit,
  home_address            => $home_address,
  email_address           => $email_address,
  home_phone_number       => $home_phone_number,
  card_expiry_date => $util->format_datetime($user->getExpiryDate(), 'SIP'),
  birthday => $util->format_datetime($user->getBirthday() . ' 00:00:00', 'SIP'),
  patron_type    => $user->getPatronType(),
  u1             => ($ext_auth->{'u1'}) ? 'true' : 'false',
  u2             => ($ext_auth->{'u2'}) ? 'true' : 'false',
  u3             => ($ext_auth->{'u3'}) ? 'true' : 'false',
  u4             => ($ext_auth->{'u4'}) ? 'true' : 'false',
  u5             => ($ext_auth->{'u5'}) ? 'true' : 'false',
  u6             => ($ext_auth->{'u6'}) ? 'true' : 'false',
  u7             => ($ext_auth->{'u7'}) ? 'true' : 'false',
  u8             => ($ext_auth->{'u8'}) ? 'true' : 'false',
  u9             => ($ext_auth->{'u9'}) ? 'true' : 'false',
  screen_message => $screen_message,
};

$template->param($response);

$template->write(cgi => $cgi);
################################################################################

# $reserve_on_hold:
# - 0: reserved
# - 1: hold
sub _get_reserve_hold_items_by_rid {
  my ($dbh, $rid, $reserve_on_hold) = @_;

  my @val = ($rid);
  my $bc = $dbh->selectall_hashref(<<_SQL_, 'barcode', undef, @val);
select  *
from    opl_item
where   rid = ?
     && available = 1
_SQL_

  my $sth = $dbh->prepare(<<_SQL_);
select  count(*)
from    opl_loan
where   barcode = ?
     && dateReturn is null
_SQL_

  my @barcodes;
  my $is_on_loan;
  foreach my $i (keys %{$bc}) {
    $sth->execute($i);
    ($is_on_loan) = $sth->fetchrow_array();

    if ($is_on_loan xor $reserve_on_hold) {
      push @barcodes, $i;
    }
  }

  $sth->finish;

  return \@barcodes;
}
############################################################

sub _get_hold_items {
  my ($dbh, $uid) = @_;

  my @val = ($uid);

  my $reserves = $dbh->selectall_hashref(<<_SQL_, 'idReserve', undef, @val);
select  *
from    opl_reserve
where   uid = ?
     && dateCancel is null
     && dateExpiry > now()
_SQL_

  my $hold_items;

  my @resrv_ids = keys %{$reserves};

  if (scalar(@resrv_ids) > 0) {
    my $reserve_ids = join(',', @resrv_ids);

    $hold_items = $dbh->selectall_arrayref(<<_SQL_, { Slice => {} });
select  *
from    opl_hold
where   idReserve in ($reserve_ids)
     && dateLoan is null
     && dateCancel is null
     && dateExpiry > now()
_SQL_

    my $r;
    my $reserve_on_hold = 1;
    foreach my $i (@{$hold_items}) {
      $r = $reserves->{ $i->{'idReserve'} };
      unless ($r->{'barcodes'}) {
        $r->{'barcodes'} =
          _get_reserve_hold_items_by_rid($dbh, $r->{'rid'}, $reserve_on_hold);
      }

      $i->{'barcode'} = pop @{ $r->{'barcodes'} };
    }
  }

  return $hold_items;
}
############################################################

# SIP: unavailable hold => reserve
sub _get_unavailable_hold_items {
  my ($dbh, $uid) = @_;

  my @val = ($uid);

  my $reserves = $dbh->selectall_hashref(<<_SQL_, 'idReserve', undef, @val);
select  *
from    opl_reserve
where   uid = ?
     && numCopyReserve > 0
     && dateCancel is null
     && dateExpiry > now()
_SQL_

  my @barcodes = ();
  my $len;
  my $reserve_on_hold = 0;
  my $r;

  foreach my $i (keys %{$reserves}) {
    $r = $reserves->{$i};

    $r->{'barcodes'} =
      _get_reserve_hold_items_by_rid($dbh, $r->{'rid'}, $reserve_on_hold);

    $len = $r->{'numCopyReserve'};
    if ($len > scalar(@{ $r->{'barcodes'} })) {
      $len = scalar(@{ $r->{'barcodes'} });
    }

    push @barcodes, splice(@{ $r->{'barcodes'} }, 0, $len);
  }

  return \@barcodes;
}
############################################################

sub _get_overdue_items {
  my ($dbh, $uid) = @_;

  my @val = ($uid);

  my $overdue_items = $dbh->selectall_arrayref(<<_SQL_, { Slice => {} }, @val);
select  *
from    opl_loan
where   uid = ?
     && dateReturn is null
     && dateDue < now()
_SQL_

  return $overdue_items;
}
############################################################

sub _get_charged_items {
  my ($dbh, $uid) = @_;

  my @val = ($uid);

  my $charged_items = $dbh->selectall_arrayref(<<_SQL_, { Slice => {} }, @val);
select  *
from    opl_loan
where   uid = ?
     && dateReturn is null
_SQL_

  return $charged_items;
}
############################################################

sub _get_fine_items {
  my ($dbh, $uid) = @_;

  my @val = ($uid);

  my $fine_items = $dbh->selectall_arrayref(<<_SQL_, { Slice => {} }, @val);
select  *
from    opl_loan l 
        inner join opl_odl odl using (idloan)
where   l.uid = ?
     && odl.settleDate is null
_SQL_
#        inner join opl_transactiondetail td using (odl_id)

  return $fine_items;
}
############################################################

sub _get_recall_items {
  my ($dbh, $uid) = @_;

  my @val = ($uid);

  my $overdue_items = $dbh->selectall_hashref(<<_SQL_, 'barcode', undef, @val);
select  *
from    opl_loan
where   uid = ?
     && dateReturn is null
     && dateDue < now()
_SQL_

  my $all_recall_items =
    $dbh->selectall_arrayref(<<_SQL_, { Slice => {} }, @val);
select  *
from    opl_emailRequest r inner join opl_emailDetails d using (eid)
where   uid = ?
     && status = 'sent'
_SQL_

  my $recall = {};
  my $eBarcodes;

  foreach my $r (@{$all_recall_items}) {
    $eBarcodes = $r->{'eBarcodes'};
    $eBarcodes =~ s/,$//;

    foreach my $bc (split(/,/, $eBarcodes)) {
      next if $recall->{$bc};

      if ($overdue_items->{$bc}->{'dateLoan'} < $r->{'sendDate'}) {
        $recall->{$bc} = $overdue_items->{$bc};
      }
    }
  }

  my @recall_items = ();
  foreach my $bc (keys %{$recall}) {
    push @recall_items, $recall->{$bc};
  }

  return \@recall_items;
}
############################################################

#sub _get_unavailable_holds_count {
#    my ($dbh, $uid) = @_;
#
#    my @val = ($uid);
#
#    my ($count) = $dbh->selectrow_array(<<_SQL_, undef, @val);
#select  sum(numCopyReserve)
#from    opl_reserve
#where   uid = ?
#     && dateExpiry > now()
#_SQL_
#
#    return ($count)?$count:0;
#}
############################################################

#sub _get_hold_items_limit {
#    my ($dbh, $uid) = @_;
#
#    return 0;
#}
############################################################

#sub _get_overdue_items_limit {
#    my ($dbh, $uid) = @_;
#
#    return 0;
#}