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

use lib '/app/tools/bookpub/lib';
use BookPub::DB::Item::Sale;

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

use constant kTable => '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_ON_HOLD    => 7;

use constant TYPE_UNSPLIT => 1;
use constant TYPE_SPLIT   => 2;

sub GetAllOpenByService {
    my $self       = shift;
    my %args       = @_;
    my $serviceIDs = $args{service_id};

    $serviceIDs = [$serviceIDs] if ( $serviceIDs && !ref($serviceIDs) );

    my $sql = "SELECT * FROM " . kTable . " WHERE file_status = " . STATUS_OPEN;

    @$serviceIDs = map { $self->quote($_) } @$serviceIDs;
    $sql .= " AND service_id IN " . join( ',', @$serviceIDs ) if ($serviceIDs);

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

sub GetAllOpenByServiceID {
    my ( $class, $serviceID ) = @_;
    assert($serviceID);

    my $dbo = Common::RSApp::GetClientDB();
    $serviceID = $dbo->DBQuote($serviceID);

    my $sql = "SELECT * FROM " . kTable . " WHERE file_status = " . STATUS_OPEN . " AND service_id = $serviceID";

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

sub GetByPeriodID {
    my ( $class, $periodID, %args ) = @_;
    assert( defined $periodID );

    my $dbo = Common::RSApp::GetClientDB();
    $periodID = $dbo->DBQuote($periodID);

    my @whereClause;
    push @whereClause, "period_id=$periodID";

    if ( defined $args{service_id} && $args{service_id} > 0 ) {
        my $serviceID = $dbo->DBQuote( $args{service_id} );
        push @whereClause, "service_id=$serviceID";
    }

    if ( defined $args{file_status} ) {
        if ( 'ARRAY' eq ref $args{file_status} ) {
            my @fileStatus = map { $dbo->DBQuote($_) } @{ $args{file_status} };
            push @whereClause, "file_status IN (" . join( ',', @fileStatus ) . ")";
        } else {
            my $fileStatus = $dbo->DBQuote($args{file_status});
            push @whereClause, "file_status=$fileStatus";
        }
    }

    # handle the file_type magic
    if ( defined $args{file_type} or defined $args{no_parent} ) {
        if ( $args{file_type} == TYPE_UNSPLIT or $args{no_parent} ) {
            push @whereClause, 'parent_file_id IS NULL';
        } elsif ( $args{file_type} == TYPE_SPLIT ) {
            push @whereClause, 'parent_file_id IS NOT NULL';
        }
    }

    # 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(remaining_price_exceptions,0))) OR records = 0, 1, 0)
                   AS can_finish FROM " . kTable . ' f';

    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 'can_finish' ) {
            $sql .= ' LEFT JOIN service using (service_id)';
            $sortBy = " ORDER BY can_finish$sortOrder";
            $sortBy .= ', service_name';
        } elsif ( $args{sort_by} eq 'service_name' ) {
            $sql .= ' LEFT JOIN service using (service_id)';
            $sortBy = " ORDER BY service_name$sortOrder, orig_file_name, generation";
        } else {
            $sortBy = " ORDER BY $args{sort_by}$sortOrder";
        }

    }

    $sql .= " WHERE " . join( ' AND ', @whereClause ) . $sortBy;

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

sub GetAllForeignByPeriodID {
    my ( $class, $periodID, $currencyCode ) = @_;
    assert($periodID);
    assert($currencyCode);

    my $dbo = Common::RSApp::GetClientDB();
    $periodID     = $dbo->DBQuote($periodID);
    $currencyCode = $dbo->DBQuote($currencyCode);

    my $sql = "SELECT * FROM " . kTable . " WHERE currency_code != $currencyCode AND period_id = $periodID";

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

sub GetSummaryByPeriodID {

    # Note that this exluces files in a foreign currency, since we have to take some extra steps for those.
    my ( $class, $periodID, $currencyCode ) = @_;
    assert($periodID);
    assert($currencyCode);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            count(*) AS count,
            sum(units) AS units,
            sum(ROUND(revenue,2)) AS revenue
        FROM file
        WHERE 1 = 1
            AND period_id = ?
            AND currency_code = ?
    /;
    my @sqlParams = ($periodID, $currencyCode);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my $href = $sth->fetchrow_hashref();

    return $href;
}

sub GetServiceFilesByPeriodID {
    my ( $class, $periodID, %args ) = @_;
    $args{sort_by} ||= 'service_name';

    my $fileList = $class->GetByPeriodID( $periodID, %args );

    my %svcFiles = ( _order => [] );
    my $lastSvcID = 0;
    while ( my $file = $fileList->next() ) {
        my $svcID = $file->service_id;

        if ( $lastSvcID != $svcID ) {
            $lastSvcID = $svcID;
            $svcFiles{$svcID} = [];
            push( @{ $svcFiles{_order} }, $lastSvcID );
        }
        push( @{ $svcFiles{$svcID} }, $file );
    }

    return \%svcFiles;
}

sub GetByOrigName {
    my ( $class, $origName ) = @_;
    assert( defined $origName );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE orig_file_name = " . $dbo->DBQuote($origName);

    my $collection = $class->GetAll($sql);
    return undef unless ( $collection && $collection->size() > 0 );
    return $collection->next();
}

sub GetByMD5 {
    my ( $class, $md5 ) = @_;
    assert( defined $md5 );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT * FROM " . kTable . " WHERE file_md5sum=" . $dbo->DBQuote($md5);

    my $collection = $class->GetAll($sql);
    return undef unless ( $collection && $collection->size() > 0 );
    return $collection->next();
}

sub ChildCount {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT count(*) AS count
        FROM file
        WHERE parent_file_id = ?
    /;
    my @sqlParams = ($fileID);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    return 0 unless ( defined $sth && $sth->rows > 0 );

    my $href = $sth->fetchrow_hashref();
    return $href->{count};
}

sub StartPeriod {
    my ( $class, $periodID ) = @_;
    assert($periodID);

    my $today = Common::Util::today_and_now();

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "UPDATE "
      . kTable
      . " SET period_id = ?, date_finished = ?"
      . " WHERE period_id = ?"
      . " AND file_status = ?";
    my @sqlParams = ($periodID, $today, 0, STATUS_CLOSED);

    $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
}

sub GetRecentlyImported {
    my $class = shift;

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE parent_file_id IS NULL AND file_status = "
      . $class->quote(STATUS_OPEN)
      . " ORDER BY date_created DESC LIMIT 5";

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

sub GetFilesWithPriceExceptions {
    my $self = shift;
    my %args = @_;

    my $sql = "SELECT * FROM " . kTable . " WHERE file_status = " . STATUS_OPEN . " AND price_exceptions <> 0";

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

sub updatePriceValidationCounts {
    my $class     = shift;
    my %args      = @_;

    my $fileID    = $args{file_id};
    my $serviceID = $args{service_id};

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            file_id,
            approved,
            COUNT(*) AS count
        FROM (
            SELECT
                file_id,
                approved,
                COUNT(*)
            FROM file
            JOIN sale USING (file_id)
            LEFT JOIN sale_price_exception USING (sale_id)
            WHERE file_status = ?
    /;
    my @sqlParams = (STATUS_OPEN);

    if ($fileID) {
        $sql .= " AND file.file_id = ?";
        push @sqlParams, $fileID;
    }
    if ($serviceID) {
        $sql .= " AND file.service_id = ?";
        push @sqlParams, $serviceID;
    }

    # the following group by is _very_ tightly coupled with the group by in
    # BookPub::DB::Item::SalePriceExceptionSearch::_searchComponents
    #
    # i'm sure i should marry the two (i.e. pull it centrally), but i also just want
    # to get what it mostly a nice enhancement rolled out
    #
    # if you muck with this, you probably should muck with the other

    $sql .=
        " GROUP BY approved, date_begin, date_end, product_id, list_price,"
      . " sale.currency_code, sale.price_type_id, sale.country_code, price, variance) AS agg_except"
      . " GROUP BY file_id, approved";

    Common::Log::Debug("QUERY: $sql, PARAMS: @sqlParams");
    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);

    if ($sth) {
        my %counts;
        while ( my $row = $sth->fetchrow_hashref() ) {
            $counts{ $row->{file_id} }{ $row->{approved} } = $row->{count} if defined( $row->{approved} );
        }

        $sql = "UPDATE " . kTable . " SET" . " price_exceptions = ?," . " price_exceptions_unapproved = ?" . " WHERE file_id = ?";

        foreach my $file_id ( keys(%counts) ) {
            my $approved   = 0;
            my $unapproved = 0;
            my $total;

            $approved   = $counts{$file_id}{1} if exists $counts{$file_id}{1};
            $unapproved = $counts{$file_id}{0} if exists $counts{$file_id}{0};
            $total      = $approved + $unapproved;

            Common::Log::Debug("UPDATES: $sql, PARAMS: $total, $unapproved, $file_id");
            $dbo->DoCmdWithPlaceholders( $sql, [ $total, $unapproved, $file_id ] );
            $class->UpdateRemainingPriceExceptions($file_id);

        }
    }
}

sub UpdateRemainingPriceExceptions {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $count = $class->RemainingPriceExceptionCount($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "UPDATE " . kTable . " SET remaining_price_exceptions = ? WHERE file_id = ?";
    my @sqlParams = ($count, $fileID);

    $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
}

sub RemainingPriceExceptionCount {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            COUNT(*) as count
        FROM sale_price_exception
        LEFT JOIN sale USING (sale_id)
        WHERE 1 = 1
            AND sale.file_id = ?
            AND sale_price_exception.approved = ?
    /;
    my @sqlParams = ($fileID, 0);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    return 0 unless ( defined $sth && $sth->rows > 0 );

    my $href = $sth->fetchrow_hashref();
    return $href->{count};
}

sub getPriceExceptionCount {
    my $class     = shift;
    my %args      = @_;
    my $serviceID = $args{service_id};

    assert($serviceID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT SUM(price_exceptions) count FROM " . kTable;
    $sql .= " WHERE file_status = ? AND file.service_id = ?";
    my @sqlParams = (STATUS_OPEN, $serviceID);

    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);

    my $count = 0;
    if ( defined $sth && $sth->rows ) {
        $count = $sth->fetchrow_hashref()->{count};
    }

    return $count;
}

sub GetAllClosed {
    my $class  = shift;
    my %args   = @_;
    my $fileID = $args{fileID};

    my $sql = "SELECT * FROM " . kTable . " WHERE file_status = " . STATUS_CLOSED;
    $sql .= " AND file_id = " . $class->quote($fileID) if ($fileID);

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

sub ClosedChildCount {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            count(*) AS count
        FROM file
        WHERE 1 = 1
            AND parent_file_id = ?
            AND file_status = ?
    /;
    my @sqlParams = ($fileID, STATUS_CLOSED);
    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    return 0 unless ( defined $sth && $sth->rows > 0 );

    my $href = $sth->fetchrow_hashref();
    return $href->{count};
}

sub HasReturns {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            count(*) as count
        FROM file
        INNER JOIN sale USING (file_id)
        WHERE 1 = 1
            AND sale.units < ?
            AND file_id = ?
    /;
    my @sqlParams = (0, $fileID);
    Log->debug("QUERY: $sql, PARAMS: @sqlParams");

    my $sth     = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my ($count) = $sth->fetchrow_array();

    return $count ? $count : undef;
}

sub HasSales {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            count(*) as count
        FROM file
        INNER JOIN sale USING (file_id)
        WHERE 1 = 1
            AND sale.units >= ?
            AND file_id = ?
    /;
    my @sqlParams = (0, $fileID);
    Log->debug("QUERY: $sql, PARAMS: @sqlParams");

    my $sth     = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my ($count) = $sth->fetchrow_array();

    return $count ? $count : undef;
}

sub GetNewestSaleDate {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            max(sale.date_end) as count
        FROM file
        INNER JOIN sale USING (file_id)
        WHERE file_id = ?
    /;
    my @sqlParams = ($fileID);
    Log->debug("QUERY: $sql, PARAMS: @sqlParams");

    my $sth    = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my ($date) = $sth->fetchrow_array();

    return $date;
}

sub HasSubscriptionSales {
    my ( $class, $fileID ) = @_;
    assert($fileID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = qq/
        SELECT
            count(sale.purchase_type) as count
        FROM file
        INNER JOIN sale USING (file_id)
        WHERE 1 = 1
            AND file_id = ?
            AND purchase_type = ?
    /;
    my @sqlParams = ( $fileID, BookPub::DB::Item::Sale::kPurchaseTypeSubscriptionDL() );
    Log->debug("QUERY: $sql, PARAMS: @sqlParams");

    my $sth    = $dbo->DoCmdWithPlaceholders($sql, \@sqlParams);
    my ($date) = $sth->fetchrow_array();

    return $date;
}

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

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

    my $dateField = ( $args{date_type} && $args{date_type} eq 'closed' )
        ? 'p.end_date'
        : 'f.date_created';

    my @whereClause;

    if ( $args{date_type} && $args{date_type} eq 'closed' ) {
        push @whereClause, 'f.file_status = ' . STATUS_CLOSED;
        push @whereClause, 'p.end_date IS NOT NULL';
    } else {
        push @whereClause,
            '(f.file_status = ' . STATUS_CLOSED . ' OR f.file_status = ' . STATUS_OPEN . ')';
    }

    if ( defined $args{service_id} && $args{service_id} =~ /^\d+$/ && $args{service_id} > 0 ) {
        push @whereClause, 'f.service_id = ' . $dbo->DBQuote( $args{service_id} );
    }

    if ( $args{start_date} ) {
        push @whereClause, "DATE($dateField) >= " . $dbo->DBQuote( $args{start_date} );
    }

    if ( $args{end_date} ) {
        push @whereClause, "DATE($dateField) <= " . $dbo->DBQuote( $args{end_date} );
    }

    my $sql =
        'SELECT f.*, p.name AS period_name, p.end_date AS period_date_closed'
        . ' FROM ' . kTable . ' f'
        . ' LEFT JOIN period p ON f.period_id = p.period_id';

    my $col         = $args{sort_by} || 'date_created';
    my $sortOrder   = $args{sort_desc} ? ' DESC' : '';
    my $sortByClause;

    if ( $col eq 'service_name' ) {
        $sql         .= ' LEFT JOIN service ON f.service_id = service.service_id';
        $sortByClause = " ORDER BY service_name$sortOrder, f.orig_file_name";
    } elsif ( $col eq 'period_date_closed' ) {
        $sortByClause = " ORDER BY period_date_closed$sortOrder";
    } elsif ( $col =~ /^(orig_file_name|date_created|date_finished|units|revenue|records|notes|period_id)$/ ) {
        $sortByClause = " ORDER BY f.$col$sortOrder";
    } else {
        $sortByClause = ' ORDER BY f.date_created';
    }

    $sql .= ' WHERE ' . join( ' AND ', @whereClause ) if @whereClause;
    $sql .= $sortByClause;

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

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

    my @where;
    if ( defined $args{file_id} && $args{file_id} =~ /^\d+$/ ) {
        push @where, 'f.file_id = ' . $args{file_id};
    } elsif ( defined $args{parent_file_id} && $args{parent_file_id} =~ /^\d+$/ ) {
        push @where, 'f.parent_file_id = ' . $args{parent_file_id};
    } else {
        return undef;
    }

    my $sql = qq/
        SELECT
            f.*,
            p.name AS period_name,
            p.end_date AS period_date_closed
        FROM file f
        LEFT JOIN period p ON f.period_id = p.period_id
        WHERE / . join( ' AND ', @where ) . qq/
        LIMIT 1
    /;

    my $oFiles = $class->GetAll($sql);
    return $oFiles->next();
}


1;