#------------------------------------------------------------
# Copyright (C) 2009 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::DB::Item::POSFile;
use strict;
use warnings;

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

use constant kTable => 'pos_file';
use constant kDB    => Common::DB::Item::kClientDB;

use constant STATUS_NEW         => 1;
use constant STATUS_PROCESSING  => 2;
use constant STATUS_INVALID     => 3;
use constant STATUS_OPEN        => 4;
use constant STATUS_CLOSED      => 5;
use constant STATUS_IN_QUEUE    => 6;
use constant STATUS_FETCHING    => 7;
use constant STATUS_FETCH_ERROR => 8;
use constant STATUS_EXPIRED     => 9;

my %gStatusCodeToText = (
    STATUS_NEW()         => 'New',
    STATUS_PROCESSING()  => 'Processing',
    STATUS_INVALID()     => 'Invalid',
    STATUS_OPEN()        => 'Received',      # Open is not really relevant to POS files.
    STATUS_CLOSED()      => 'Closed',
    STATUS_IN_QUEUE()    => 'In Queue',
    STATUS_FETCHING()    => 'Fetching',
    STATUS_FETCH_ERROR() => 'Fetch Error',
    STATUS_EXPIRED()     => 'Expired',
);

sub StatusCodeToText {
    my ( $class, $code ) = @_;

    return defined($code) ? $gStatusCodeToText{$code} : undef;
}

sub DetectDuplicate {
    my $class    = shift;
    my %args     = @_;
    my $filename = $args{file_name};
    my $md5      = $args{file_md5sum};
    my $feedID   = $args{pos_feed_id};

    assert($filename);
    assert($feedID);

    my $sql = "SELECT * FROM " . kTable . " WHERE pos_feed_id = " . $class->quote($feedID);
    if ($md5) {
        $sql .= " AND ( orig_file_name = " . $class->quote($filename) . " OR file_md5sum = " . $class->quote($md5) . " ) ";
    } else {
        $sql .= " AND orig_file_name = " . $class->quote($filename);
    }

    Log->debug("QUERY: $sql");
    return $class->SUPER::GetAll($sql)->next();
}

sub GetAllSortedByDates {
    my ($class) = @_;

    # This interface is used primarily by the 'POS Feed Tracker' support page.
    # The intention is to order files by the start and end dates.
    # !!! Might need to add a second-order sorting during development, since
    # !!! we have files that haven't populated those columns (they were added later).
    #

    my $sql = "SELECT * FROM " . $class->kTable . " ORDER BY date_start,date_created,pos_feed_id";
    return $class->SUPER::GetAll($sql);
}

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

# Added the IF section to this query to be able to sort on status.
# The derived valued "can_finish" is accessed through the form object
# so the template can also label files as finishable
#my $sql = "SELECT f.*,IF ((records AND records > (IFNULL(remaining_exceptions,0) + #IFNULL(price_exceptions_unapproved,0))) OR records = 0, 1, 0)
#              AS can_finish FROM " . kTable . ' f';

    my $sql = "SELECT p.*";

    my $sortBy;
    if ( $args{sort_by} ) {
        my $sortOrder = $args{sort_desc} ? ' DESC' : '';

        # added the join to the service table to be able to order
        # by service_name as the secondary sort
        if ( $args{sort_by} eq 'file_status' ) {
            $sql .=
", CASE file_status WHEN 5 THEN 1  WHEN 9 THEN 2  WHEN 8 THEN 3  WHEN 7 THEN 4  WHEN 6 THEN 5  WHEN 3 THEN 6  WHEN 1 THEN 7  WHEN 2 THEN 8  WHEN 4 THEN 9 END AS sort_order";
            $sql .= " FROM " . kTable . ' p';
            $sql .= ' LEFT JOIN service using (service_id)';
            $sortBy = " ORDER BY sort_order $sortOrder";
            $sortBy .= ', service_name';
        } elsif ( $args{sort_by} eq 'service_name' ) {
            $sql .= " FROM " . kTable . ' p';
            $sql .= ' LEFT JOIN service using (service_id)';
            $sortBy = " ORDER BY service_name$sortOrder, orig_file_name";
        } else {
            $sql .= " FROM " . kTable . ' p';
            $sortBy = " ORDER BY $args{sort_by}$sortOrder";
        }
    } else {
        $sql .= " FROM " . kTable . ' p';
    }

    $sql .= $sortBy;

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

sub unsetFile {
    my $class = shift;
    my %args  = @_;
    my $id    = $args{pos_file_id};

    assert($id);

    my $sql =
        "UPDATE "
      . kTable . ' set '
      . 'file_dir = NULL, '
      . 'file_name = NULL, '
      . 'orig_file_name = NULL, '
      . 'file_md5sum = NULL, '
      . 'records = NULL, '
      . 'units = NULL, '
      . 'revenue = NULL, '
      . 'currency_code = NULL, '
      . 'total_exceptions = NULL, '
      . 'remaining_exceptions = NULL, '
      . 'date_processed = NULL, '
      . 'version_num = NULL '
      . "WHERE pos_file_id = "
      . $class->quote($id);

    my $dbo = Common::RSApp::GetClientDB();
    Log->debug("QUERY: $sql");
    $dbo->DoCmd($sql) || die $!;

    $sql = "DELETE FROM pos_sale WHERE pos_file_id = " . $class->quote($id);
    Log->debug("QUERY: $sql");
    $dbo->DoCmd($sql) || die $!;
}

1;