#!/usr/bin/perl 
#

use strict;

use Getopt::Std;
use File::Basename;

my %opt;
usage() unless getopts('p:f:ixc:qhldvrs', \%opt);

if( $opt{l} ) { 
    list_clients( $ARGV[0] );
}

if( $opt{h} ) { 
    usage();
}

my $controller = new DeleteFile( client_id    => $opt{c},
                                 period_id    => $opt{p},
                                 file_id      => $opt{f},
                                 delete_children => $opt{x},
                                 ignore_children => $opt{i},
                                 no_report    => $opt{r},
                                 simulate     => $opt{s},
                                 quiet        => $opt{q},
                                 verbose      => $opt{v},
                                 debug        => $opt{d} );

$controller->run();

sub usage {
    my $prog = basename $0;

    print <<EofUSAGE;

usage: $prog -c client_id [-p period_id] [-f file_id] [-s] [-r] [-x] [-i] [-l client_name/pattern] [-h] [-d]
            -p - Period ID
            -f - File ID
            -x - Delete child files
            -i - Ignore child files
            -r - Don't regenerate reports on closed periods
            -s - Simulate run, but don't commit any changes
            -l - List client ids (host pattern is optional)
            -d - display debug information to STDERR
            -v - display verbose debug information to STDERR
            -q - Do not output summary report
            -h - display this screen
EofUSAGE

exit 1;
}

sub list_clients {
    my $host = shift;
    my $key;
    foreach $key ( sort {$a <=> $b} keys( %Common::RSDB::CLIENT_DB ) ) {
        printf( "%-20s %-10s ID: %s\n",
                $Common::RSDB::CLIENT_DB{$key}->{db_name},
                "($Common::RSDB::CLIENT_DB{$key}->{host_id})",
                $key )
            if( ! defined($host) || $Common::RSDB::CLIENT_DB{$key}->{db_name} =~ /$host/i );
    }
    exit 1;
}


package DeleteFile;

use lib '/app/tools/common/lib';
use Common::RSApp;
use Common::Util;
use Common::Assert;
use Common::WriteXML;

use lib '/app/tools/common/lib';
use base 'Common::Script';

use lib '/app/tools/rps/lib';
use RPS::DB::Item::SaleRunMap;

use lib '/app/tools/raptor/lib';
use Raptor::DB::Item::File;
use Raptor::Tracker::Job::DumpRoyaltyReport;

use Data::Dumper;

sub _init {
    my $self = shift;
    my %args = @_;
    $self->SUPER::_init(@_);

    assert( $args{client_id}, "Client ID required" );

    $self->{fileID} = $args{file_id};
    $self->{periodID} = $args{period_id};
    $self->{ignoreChildren} = $args{ignore_children};
    $self->{deleteChildren} = $args{delete_children};
    $self->{noReport} = $args{no_report};
    $self->{simulate} = $args{simulate};

    die "ignore children and delete children are mutually exclusive options"
        if( $self->{ignoreChildren} && $self->{deleteChildren} );

    die "Period ID or File ID required"
        unless( $self->{periodID} || $self->{fileID} );

    return $self;
}

sub _process 
{
    my $self = shift;

    if( my @ids = $self->_filesUsedInRun() ) {
        die "One or more of the files specified have been used in a run already" .
            " IDs: @ids";
    }

    my @ids = $self->_getFileIDs( fileID         => $self->{fileID},
                                  periodID       => $self->{periodID},
                                  ignoreChildren => $self->{ignoreChildren} );

    $self->debug( "Found " . scalar @ids . " files to delete" );

    # If we specified a file ID then we won't allow it to be deleted if it is 
    # the child of another file.
    if( $self->{fileID} && $self->_isAChild($self->{fileID}) ) {
        die "File ID $self->{fileID} has a parent and can't be deleted";
    }

    if( $self->_hasChildren() ) {
        die "Files have children but you haven't forced children to be deleted"
            unless( $self->{deleteChildren} || $self->{ignoreChildren} );
    }

    if( @ids ) {
        $self->debug( "Deleting file ids: @ids" );

        my @periods = $self->_getPeriods( @ids );
        $self->_deleteFiles( @ids );
    
        $self->_deleteEmptyPeriods( @periods );

        $self->_rebuildReports( @periods )
            unless( $self->{noReports} );
    } else {
        $self->print( "No files found to delete" );
    }
}

sub _deleteFiles {
    my $self = shift;
    my @ids = @_;

    return unless( @ids );

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

    my $sql = "DELETE sale_run_map.* FROM file " .
                     "INNER JOIN sale USING (file_id) " .
                     "INNER JOIN sale_run_map USING (sale_id) " .
              "WHERE file_id IN ( " . join( ', ', @ids ) . " ) ";

    $self->debug( "QUERY: $sql" );
    $dbo->DoCmd($sql) unless( $self->{simulate} );

    my $sql = "DELETE sale.* FROM file " .
                     "INNER JOIN sale USING (file_id) " .
              "WHERE file_id IN ( " . join( ', ', @ids ) . " ) ";

    $self->debug( "QUERY: $sql" );
    $dbo->DoCmd($sql) unless( $self->{simulate} );

    my $sql = "DELETE FROM file " .
              "WHERE file_id IN ( " . join( ', ', @ids ) . " ) ";
    $self->debug( "QUERY: $sql" );
    $dbo->DoCmd($sql) unless( $self->{simulate} );


    my $cdbo = Common::RSApp::GetCommonDB();
    my $sql = "DELETE FROM orchard_report " .
              "WHERE client_id = " . $opt{c} . " AND file_id IN ( " . join( ', ', @ids ) . " ) ";
    $self->debug( "QUERY: $sql" );
    $cdbo->DoCmd($sql) unless( $self->{simulate} );
}

sub _getPeriods {
    my $self = shift;
    my @fileIDs = @_;
    my @periodIDs;

    return () unless( @fileIDs );

    my $sql = "SELECT distinct(period_id) as period_id FROM file " .
              "WHERE file_id IN ( " . join( ', ', @fileIDs ) . " ) ";

    $self->debug( "QUERY: $sql" );
    my $collection = Raptor::DB::Item::File->GetAll($sql);

    while( my $row = $collection->next() ) {
        push( @periodIDs, $row->period_id );
    }

    return @periodIDs;
}

sub _isAChild {
    my $self = shift;
    my $fileID = shift || die;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT * FROM file " .
              "WHERE file_id = " . $dbo->DBQuote($fileID);

    $self->debug( "QUERY: $sql" );
    my $collection = Raptor::DB::Item::File->GetAll($sql);
    my $row = $collection->next();

    return $row ? $row->parent_file_id : undef;
}


sub _hasChildren {
    my $self = shift;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT * FROM file " .
                  "INNER JOIN file as child_file ON ( file.file_id = child_file.parent_file_id ) ";

    if( $self->{fileID} ) {
        $sql .= " WHERE file.file_id = " . $dbo->DBQuote($self->{fileID});
    } elsif( $self->{periodID} ) {
        $sql .= " WHERE file.period_id = " . $dbo->DBQuote($self->{periodID});
    } else {
        die " period id or file id required";
    }

    $self->debug( "QUERY: $sql" );
    my $collection = Raptor::DB::Item::File->GetAll($sql);

    return $collection->hasNext();
}

# Search to see if the target files or any of their children have been commit
# in a run.  We do not honor the "ignore_children" flag here.
sub _filesUsedInRun {
    my $self = shift;
    my @ids;

    my @fileIDs = $self->_getFileIDs( fileID => $self->{fileID}, periodID => $self->{periodID} );

    foreach my $fileID ( @fileIDs ) { 
        push( @ids, $fileID ) if( $self->_usedInRun( $fileID ) );
    }

    return @ids;
}


# If we find any entries in the sale_run_map table then the file has been 
# used in a run.
sub _usedInRun {
    my $self = shift;
    my $fileID = shift || die "FileID Required";
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT * FROM file " .
                            "INNER JOIN sale USING (file_id) " .
                            "INNER JOIN sale_run_map USING (sale_id) " .
              "WHERE file_id = " . $dbo->DBQuote($fileID) . " " .
                    "AND status = 'paid'";

    $self->debug( "QUERY: $sql" );
    my $collection = RPS::DB::Item::SaleRunMap->GetAll( $sql );

    return $collection->hasNext();
}


# Depth first search of files and their children
sub _getFileIDs {
    my $self = shift;
    my %args = @_;

    my $fileID         = $args{fileID};
    my $periodID       = $args{periodID};
    my $parentFileID   = $args{parentFileID};
    my $ignoreChildren = $args{ignoreChildren};

    my @ids;
    my $dbo = Common::RSApp::GetClientDB;

    my $sql = "SELECT * FROM file";

    if( $fileID ) {
        $sql .= " WHERE file_id = " . $dbo->DBQuote($fileID);
    } elsif( $parentFileID ) {
        $sql .= " WHERE parent_file_id = " . $dbo->DBQuote($parentFileID);
    } elsif( $periodID ) {
        $sql .= " WHERE period_id = " . $dbo->DBQuote($periodID);
    }

    my $collection = Raptor::DB::Item::File->GetAll( $sql );
    $self->debug( "QUERY: $sql" );

    while( my $item = $collection->next ) {
        push( @ids, $item->file_id );

        # Now get the children
        push( @ids, $self->_getFileIDs( parentFileID => $item->file_id ) )
            unless( $ignoreChildren );
    }

    $self->debug( "Found " . scalar @ids . " files" );
    return @ids;
}

sub _deleteEmptyPeriods {
    my $self = shift;
    my @periods = @_;
    my $sql;
    my $collection;
    my $dbo = Common::RSApp::GetClientDB();

    return if( $self->{simulate} );

    foreach my $period ( @periods ) {
        my $sql = "SELECT * FROM file WHERE period_id = " . $dbo->DBQuote($period);
        my $collection = Raptor::DB::Item::File->GetAll($sql);
        
        if( $collection->hasNext() ) {
            $self->print( "Period $period not empty, not deleting" );
        } else {
            $dbo->DoCmd( "DELETE FROM period WHERE period_id = " . $dbo->DBQuote($period) );
        }
        
    }
}

sub _rebuildReports {
    my $self = shift;
    my @periods = @_;

    return if( $self->{simulate} );

    $self->debug( "Regenerating reports" );
    foreach my $period ( @periods ) {
        next unless( $period );
        my $jobArgs = Raptor::Tracker::Job::DumpRoyaltyReport->new(periodID => $period);
        my $job = $jobArgs->enqueue();
    }
}

1;