package API::DB::Item::PortalInvite;

use strict;
use warnings;
use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::DB::ItemCollection;
use Common::DB::Item;
use base 'Common::DB::Item';

use constant kTable => 'portal_invite';
use constant kDB    => Common::DB::Item::kCommonDB;

sub GetAll {
    my $class = shift;
    my $sql   = "SELECT * FROM " . kTable . " ORDER BY id";
    return $class->SUPER::GetAll($sql);
}

sub GetByInviteKey {
    my ( $class, $inviteKey ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE invite_key = " . $dbo->DBQuote($inviteKey);
    return $class->SUPER::GetAll($sql);
}

sub GetByInvitee {
    my ( $class, $invitee ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE invitee = " . $dbo->DBQuote($invitee);
    return $class->SUPER::GetAll($sql);
}

sub GetCountByInvitee {
    my ( $class, $invitee ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = "SELECT COUNT(*) AS count FROM " . kTable . " WHERE invitee = " . $dbo->DBQuote($invitee);
    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub GetLatestInvite {
    my ( $class, %args ) = @_;

    my $dbo = Common::RSApp::GetCommonDB();

    my $clientID             = $args{client_id};
    assert( $clientID, "client id required" );
    my $payeeID              = $args{payee_id};
    assert( $payeeID, "payee id required" );
    my $payeeType            = $args{payee_type};
    assert( $payeeType, "payee type required" );
    my $statementRecipientID = $args{statement_recipient_id} || 0;

    my $sql = "SELECT * FROM " . kTable . " WHERE";
    $sql .= " client_id = " . $dbo->DBQuote($clientID);
    $sql .= " AND payee_id = " . $dbo->DBQuote($payeeID);
    $sql .= " AND payee_type = " . $dbo->DBQuote($payeeType);
    $sql .= " AND statement_recipient_id = " . $dbo->DBQuote($statementRecipientID);
    $sql .= " ORDER BY date_created DESC LIMIT 1";

    return $class->SUPER::GetAll($sql);
}

sub GetPendingByInvitee {
    # There may be multiple invites for an invitee with the same
    # client_id, payee_id, payee_type, and statement_recipient_id.
    # So we need to filter this down to the latest invite.
    my ( $class, $invitee ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = "SELECT pi1.* FROM " . kTable . " pi1";
    $sql .= " RIGHT JOIN client USING (client_id)";
    $sql .= " LEFT JOIN " . kTable . " pi2 ON pi1.client_id = pi2.client_id";
    $sql .= " AND pi1.payee_id = pi2.payee_id";
    $sql .= " AND pi1.payee_type = pi2.payee_type";
    $sql .= " AND pi1.statement_recipient_id = pi2.statement_recipient_id";
    $sql .= " AND pi1.date_created  < pi2.date_created";
    $sql .= " WHERE pi2.date_created is NULL";
    $sql .= " AND pi1.invitee = " . $dbo->DBQuote($invitee);
    $sql .= " AND pi1.date_confirmed = 0";
    return $class->SUPER::GetAll($sql);
}

sub GetConfirmedByInvitee {
    # This will need to be updated in a similar fashion to GetPendingByInvitee above.
    # But since we don't currently display this value anywhere,
    # I'm going to leave it as-is for now.
    my ( $class, $invitee ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = "SELECT * FROM " . kTable;
    $sql .= " RIGHT JOIN client USING (client_id)";
    $sql .= " WHERE invitee = " . $dbo->DBQuote($invitee);
    $sql .= " AND date_confirmed != 0";
    return $class->SUPER::GetAll($sql);
}

sub Delete {
    my ( $class, %args ) = @_;
    return unless %args;

    my $dbo = Common::RSApp::GetCommonDB();

    my $where = '';
    while ( my($k, $v) = each %args ) {
        next unless defined $k && defined $v;
        $v = $dbo->DBQuote($v);
        $where .= " AND $k = $v";
    }

    return $dbo->DoCmd("DELETE FROM " . kTable . " WHERE 1 = 1 $where");
}

1;