#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::NewArtistContract;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Util qw(escape_mysql_regexp);
use Common::DB::ItemCollection;
use Common::DB::Item;
use Common::Assert;
use base 'Common::DB::Item';

use RPS::DB::Item::ReserveLiquidation;

use constant kTable => 'new_artist_contract';
use constant kDB    => Common::DB::Item::kClientDB();

sub GetAll {
    my ( $class, $sql, $dbid ) = @_;

    return $class->SUPER::GetAll($sql, $dbid) if $sql;

    $sql = "SELECT * FROM new_artist_contract WHERE deleted = 0";
	$dbid = defined $dbid ? $dbid : $class->kDB;

    my $collection = Common::DB::ItemCollection->new(
        class => $class,
        query => $sql,
        dbID  => $dbid
    );

    return $collection;
}

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

    my @where;

    # Are we paging alphanumerically?
    #
    my $alpha = $args{alpha};
    if ( defined $alpha ) {
        if ( '0' eq uc($alpha) ) {
            push @where, "title < 'A'";
        } elsif ( 'Z' eq $alpha ) {
            push @where, "title >= 'Z'";
        } else {
            push @where, "title >= '" . uc($alpha) . "'";
            push @where, "title < '" . chr( ord( uc($alpha) ) + 1 ) . "'";
        }
    }

    if ( $args{payor_id} ) {
        push @where, "payor_id = " . $class->quote( $args{payor_id} );
    }

    my $whereClause;
    if ( scalar @where ) {
        push @where, 'deleted = 0';
        $whereClause = "WHERE " . join( ' AND ', @where );
    } else {
        $whereClause = "WHERE deleted = 0 ";
    }

    my $sql = "SELECT * FROM " . kTable . " $whereClause ORDER BY title";

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

sub IsAlbumOnContract {
    my ( $class, $albumID, $contractID ) = @_;

    my $sql = "SELECT * FROM album_contract WHERE artist_contract_id = " . $class->quote($contractID) . " AND album_id = " . $class->quote($albumID);
    my $c   = $class->GetAll($sql);
    return 1 if ( $c->size() > 0 );

    $sql =
"SELECT * FROM track_contract WHERE artist_contract_id = " . $class->quote($contractID) . " AND track_id IN (SELECT track_id FROM track WHERE album_id = " . $class->quote($albumID) . " )";
    $c = $class->GetAll($sql);
    return ( $c->size() > 0 );
}

sub IsTrackOnContract {
    my ( $class, $trackID, $contractID ) = @_;

    my $sql = "SELECT * FROM track_contract WHERE artist_contract_id = " . $class->quote($contractID) . " AND track_id = " . $class->quote($trackID);
    my $c   = $class->GetAll($sql);
    return 1 if ( $c->size() > 0 );

    $sql =
"SELECT * FROM album_contract WHERE artist_contract_id = " . $class->quote($contractID) . " AND album_id IN (SELECT album_id FROM track WHERE track_id = " . $class->quote($trackID) . " )";
    $c = $class->GetAll($sql);
    return ( $c->size() > 0 );
}

sub GetByPayee {
    my ( $class, $payeeID ) = @_;
    assert($payeeID);

    my $sql = "SELECT * FROM " . kTable . " WHERE deleted = 0 AND artist_payee_id = " . $class->quote($payeeID) . " ORDER BY title";
    return $class->GetAll($sql);
}

sub GetByPayor {
    my ( $class, $payorID ) = @_;
    assert($payorID);

    my $sql = "SELECT * FROM " . kTable . " WHERE deleted = 0 AND payor_id = " . $class->quote($payorID) . " ORDER BY title";
    return $class->GetAll($sql);
}

sub GetCountByPayor {
    my ( $class, $payorID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE deleted = 0 AND payor_id = " . $dbo->DBQuote($payorID);
    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

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

    my $searchTerm = $args{searchTerm};
    my $limit      = $args{limit};
    my $trackID    = $args{trackID};
    my $albumID    = $args{albumID};

    my $where;

    if ($searchTerm) {
        $where = "("
          . $class->SearchSyntax( field => 'title', value => $searchTerm ) . " OR "
          . $class->SearchSyntax( field => "client_contract_id", value => $searchTerm ) . ")";

        if ( $albumID && $trackID ) {
            $where .=
                " AND (artist_contract_id IN (SELECT artist_contract_id FROM album_contract WHERE album_id = "
              . $class->quote($albumID)
              . ") OR artist_contract_id IN (SELECT artist_contract_id FROM track_contract WHERE track_id = "
              . $class->quote($trackID)
              . "))";
        } elsif ($albumID) {
            $where .= " AND artist_contract_id IN (SELECT artist_contract_id FROM album_contract WHERE album_id = "
            . $class->quote($albumID)
            . ")";
        } elsif ($trackID) {
            $where .= " AND artist_contract_id IN (SELECT artist_contract_id FROM track_contract WHERE track_id = "
            . $class->quote($trackID)
            . ")";
        }

    }

    my $sql = 'SELECT * FROM ' . kTable;
    if ($where) {
        $sql .= " WHERE deleted = 0 AND $where ";
    } else {
        $sql .= " WHERE deleted = 0 ";
    }

    $sql .= ' ORDER BY title';

    if ($limit && $limit =~ /^\d+$/) {
        $sql .= " LIMIT $limit";
    }

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

# !!! Odd that this is not referenced anyplace - might be something funny going on.
#
sub Search {
    my ( $class, $searchTerm ) = @_;
    assert( $searchTerm, "no valid param passed to artist_contract search" );

    my $where =
        $class->SearchSyntax( field => "artist_payee.name",     value => $searchTerm ) . " OR "
      . $class->SearchSyntax( field => "ac.client_contract_id", value => $searchTerm ) . " OR "
      . $class->SearchSyntax( field => "ac.title",              value => $searchTerm );

    my $order = "ac.title, artist_payee.name";

    my $sql =
        "SELECT ac.* FROM "
      . kTable . " ac"
      . " LEFT JOIN artist_payee ON artist_payee.artist_payee_id=ac.artist_payee_id"
      . " WHERE ac.deleted = 0 AND ( $where )"
      . " ORDER BY $order";

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

sub GetByPayorAlbumTrack {
    my ( $class, $payorID, $albumID, $trackID ) = @_;
    assert($payorID);
    assert($albumID);

    # !!! Track is optional;
    #
    $payorID = $class->quote($payorID);
    $albumID = $class->quote($albumID);
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE deleted = 0 AND payor_id=$payorID"
      . " AND ("
      . "(artist_contract_id IN (select artist_contract_id from album_contract where album_id=$albumID))";

    # If we have a track id, then we only want to match track contracts for that track.
    # Otherwise, any track contract for that album would match.
    #
    if ($trackID) {
        $trackID = $class->quote($trackID);
        $sql .= " OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id=$trackID))";
    } else {
        $sql .=
" OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id IN (select track_id from track where album_id=$albumID)))";
    }
    $sql .= ")";

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

sub GetByAlbumTrack {
    my ( $class, $albumID, $trackID ) = @_;
    assert($albumID);

    # !!! Track is optional;
    #
    $albumID = $class->quote($albumID);
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE deleted = 0 AND ("
      . "(artist_contract_id IN (select artist_contract_id from album_contract where album_id=$albumID))";

    # If we have a track id, then we only want to match track contracts for that track.
    # Otherwise, any track contract for that album would match.
    #
    if ($trackID) {
        $trackID = $class->quote($trackID);
        $sql .= " OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id=$trackID))";
    } else {
        $sql .=
" OR (artist_contract_id IN (select artist_contract_id from track_contract where track_id IN (select track_id from track where album_id=$albumID)))";
    }
    $sql .= ")";

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

sub GetAllWithoutReserveLiquidation {
    my $class = shift;

    my $types = join(',', (
        RPS::DB::Item::ReserveLiquidation::kTypeContractUnitBased,
        RPS::DB::Item::ReserveLiquidation::kTypeContractNetBased,
        RPS::DB::Item::ReserveLiquidation::kTypeArtistContract
    ));
    my $table = kTable;

    my $sql = qq/
        SELECT *
        FROM $table
           LEFT JOIN track_contract USING ( artist_contract_id )
           LEFT JOIN album_contract USING ( artist_contract_id )
           LEFT JOIN reserve_liquidation ON ( new_artist_contract.artist_contract_id = reserve_liquidation.id AND type IN ($types) )
        WHERE 1 = 1
            AND new_artist_contract.deleted = 0
            AND reserve_rate > 0
            AND reserve_liquidation.id IS NULL
            AND ( track_contract.status = 1 OR album_contract.status = 1 )
    /;

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

sub GetCountByArtistContractID {
    my ($class, $artistContractID) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) AS count FROM " . kTable . " WHERE deleted = ? AND artist_contract_id = ?";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [0, $artistContractID]);
    my $hr  = $sth->fetchrow_hashref();

    return $hr->{count};
}

sub Delete {
    my ($class, $artistContractID) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "UPDATE " . kTable . " SET deleted = ? WHERE deleted = ? AND artist_contract_id = ?";

    return $dbo->DoCmdWithPlaceholders($sql, [1, 0, $artistContractID]);
}

1;