#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::Track;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

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

#use RPS::DB::Item::Product;
use base 'Common::DB::Item';

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

sub Search {
    my ( $class, $searchTerm ) = @_;

    my $where;

    if ($searchTerm) {
        $where =
            "WHERE "
          . $class->SearchSyntax( field => "track.title", value => $searchTerm ) . " OR "
          . $class->SearchSyntax( field => "isrc",        value => $searchTerm ) . " OR "
          . $class->SearchSyntax( field => "artist.name", value => $searchTerm );
    } else {
        die "no valid param passed to track search";
    }

    my $sql =
"SELECT track.* FROM track LEFT JOIN artist ON artist.artist_id=track.artist_id LEFT JOIN master ON ( track.master_id = master.master_id ) $where GROUP BY track_id ORDER BY track.title";

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

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

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

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

    my $where;

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

        if ($albumID) {
            $where .= " AND album_id = " . $dbo->DBQuote($albumID);
        }

    }

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

    $sql .= ' ORDER BY title';

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

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

sub hasDuplicateISRC {
    my %args       = @_;
    my $album_id   = $args{album_id};
    my $track_id   = $args{track_id};
    my $master_id  = $args{master_id};
    my $isrc       = $args{isrc};
    my $media_type = $args{media_type};

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

    assert( $album_id, "album id required" );
    assert( $isrc,     "ISRC required" );

    my $sql   = "SELECT count(*) FROM track INNER JOIN master USING (master_id) ";
    my $where = " WHERE album_id = " . $dbo->DBQuote($album_id) . " AND media_type = " . $dbo->DBQuote($media_type);

    if ($track_id) {
        $where .= " AND track_id <> " . $dbo->DBQuote($track_id);
    } elsif ($master_id) {
        $where .= " AND master_id <> " . $dbo->DBQuote($master_id);
    }

    $where .= " AND isrc = " . $dbo->DBQuote($isrc);
    $sql   .= $where;

    Common::Log::Debug("QUERY: $sql ");
    my $sth = $dbo->DoCmd($sql);
    my ($count) = $sth->fetchrow_array();

    return $count && $count > 0;
}

# This currently knows nothing about 'bonus tracks'...
#
sub GetTracksByAlbumID {
    my ( $class, $albumID ) = @_;

    my $sql = "SELECT * FROM track WHERE album_id = " . $class->quote($albumID) . " ORDER BY track_order, track_id DESC";
    return $class->GetAll($sql);
}

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

    my $sql = "SELECT track_id FROM track WHERE album_id = " . $class->quote($albumID) . " ORDER BY track_order, track_id DESC";
    return $class->GetAll($sql);
}

# This currently knows nothing about 'bonus tracks'...
#
sub GetTracksByAlbumIDFiltered {
    my ( $class, $albumID, $type, %args ) = @_;
    return $class->GetTracksByAlbumID($albumID) unless (%args);

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

    my $exemptField    = 'mechanical_exempt';
    my $licenseIdField = 'track_license_id';
    my $licenseTable   = 'track_license';
    if ( $type && $type =~ /CA/i ) {
        map { $_ = 'ca_' . $_ } $exemptField, $licenseIdField, $licenseTable;
    }

    # initialize filter values
    my ( $productFilter, $licenseFilter, $statusFilter, $where ) = ('') x 4;
    if ( exists $args{filter} ) {
        $productFilter = exists $args{filter}{productFilter} && $args{filter}{productFilter};
        $licenseFilter = exists $args{filter}{licenseFilter} && $args{filter}{licenseFilter};
        $statusFilter  = exists $args{filter}{statusFilter}  && $args{filter}{statusFilter};
    }

    # FILTER
    # product filter
    if ( $productFilter && !$licenseFilter ) {
        my $productID = $dbo->DBQuote($productFilter);
        $where = qq{ AND track_license_type IN ($productID) };
    }

    # license filter
    elsif ( !$productFilter && $licenseFilter ) {
        if ( $licenseFilter =~ /^licensed$/i ) {
            $where = qq{ AND track_license_type NOT IN ('n/a') AND $exemptField != 1 };
        } elsif ( $licenseFilter =~ /^unlicensed$/i ) {
            $where = qq{ AND track_license_type IN ('n/a') };
        }
    }

    # product and license filters
    elsif ( $productFilter && $licenseFilter ) {
        my $productID = $dbo->DBQuote($productFilter);

        # RING product type
        if ( $productFilter =~ /^15$/ ) {
            $where =
              $licenseFilter =~ /^licensed$/
              ? qq{ AND track_license_type IN ($productID) AND $exemptField != 1 }
              : qq{ AND track_license_type NOT IN ($productID) };
        }

        # DA, DT - digital products
        elsif ( $productFilter =~ /^3|4$/ ) {
            $where =
              $licenseFilter =~ /^licensed$/
              ? qq{ AND track_license_type IN ($productID, 'all_products', 'all_digital_products') AND $exemptField != 1 }
              : qq{ AND track_license_type NOT IN ($productID, 'all_products', 'all_digital_products') };
        }

        # other - physical products
        elsif ( $productFilter =~ /^\d+$/ ) {
            $where =
              $licenseFilter =~ /^licensed$/
              ? qq{ AND track_license_type IN ($productID, 'all_products', 'all_physical_products') AND $exemptField != 1 }
              : qq{ AND track_license_type NOT IN ($productID, 'all_products', 'all_physical_products') };
        }
    }

    # status filter
    if ($statusFilter) {
        $where .=
          $statusFilter =~ /^active$/
          ? qq{ AND $exemptField != 1 AND track_license_inactive = 0 }
          : qq{ AND $exemptField != 1 AND track_license_inactive = 1 };
    }

    $albumID = $dbo->DBQuote($albumID);
    my $sql = qq{
        SELECT *
        FROM (
            SELECT
                t.track_order AS num,
                tl.inactive AS track_license_inactive,
                IFNULL(tl.product_type_id, 0) AS product_type_id,
                CASE
                    WHEN tl.$licenseIdField AND tl.product_type_id IS NULL THEN 'all_products'
                    WHEN tl.product_type_id = 255 THEN 'all_physical_products'
                    WHEN tl.product_type_id = 254 THEN 'all_digital_products'
                    ELSE IFNULL(tl.product_type_id, 'n/a')
                END AS track_license_type,
                t.*
            FROM track AS t
            LEFT JOIN $licenseTable AS tl USING (track_id)
            WHERE t.album_id = $albumID
        ) AS tmp
        WHERE 1 $where
        GROUP BY track_id
        ORDER BY track_order, track_id DESC
    };

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

sub GetIncludedTracksByProductID {
    my ( $class, $productID ) = @_;

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

    $productID = $dbo->DBQuote($productID);
    my $sql = qq{
        SELECT t.*, pt.disc_number, pt.disc_track, pt.is_bonus_track, pt.album_only FROM
            track as t
            LEFT JOIN product_track as pt USING (track_id)
        WHERE product_id = $productID
        ORDER BY disc_number, disc_track
    };

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

sub GetTracksByMasterID {
    my ( $class, $masterID ) = @_;
    assert( $masterID, "Missing master_id" );

    my $sql = "SELECT * FROM track WHERE master_id = " . $class->quote($masterID) . " ORDER BY track_id";
    return $class->GetAll($sql);
}

sub GetTrackCountByAlbumID {
    my ( $class, $albumID ) = @_;
    assert( $albumID, "Missing album_id" );

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT count(*) FROM track WHERE album_id = " . $dbo->DBQuote($albumID);
    my $sth = $dbo->DoCmd($sql);
    my $aref = $sth->fetchrow() if ($sth);

    return ( defined $aref ) ? $aref : 0;
}

# pass the trackDBItem that just had its track_number changed
#
# It will grab all tracks for this album and renumber them
#
sub RenumberTracks {
    my ( $class, $trackDBObj ) = @_;

    # get all tracks for this album
    #
    my $coll      = RPS::DB::Item::Track->GetTracksByAlbumID( $trackDBObj->album_id() );
    my @trackList = ();
    while ( $coll->hasNext() ) {
        push @trackList, $coll->next();
    }

    my $nextTrackNumber = 1;
    foreach my $track ( sort { $a->track_order() <=> $b->track_order() } @trackList ) {

        # skip the track we just updated
        #
        if ( $track->track_id == $trackDBObj->track_id() ) {
            next;
        }

        # if(this track does not match the next track_number)
        # OR
        # if(the next track_number is the one we just updated)
        #
        # Basically, if something needs to be resolved...
        #
        if ( $track->track_order() != $nextTrackNumber || $nextTrackNumber == $trackDBObj->track_order() ) {

            # if this track happens to have the same track_number as the one
            # we just updated, then this one gets bumped, increment to the
            # next track_number and update
            #
            if ( $nextTrackNumber == $trackDBObj->track_order() ) {
                $nextTrackNumber++;
            }

            $track->track_order($nextTrackNumber);
            $track->save();
            $nextTrackNumber++;
        } else {

            # everything's cool, just move on to the next one
            #
            $nextTrackNumber++;
            next;
        }
    }
}

sub GetTableStatus {
    my ($class) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SHOW TABLE STATUS LIKE '" . kTable . "'";

    my $sth = $dbo->DoCmd($sql);

    my $result;
    my @results;
    while ( $result = $sth->fetchrow_arrayref ) {
        push @results, @{$result};
    }
    return @results;
}

sub GetTrackIDAlbumID {
    my $self = shift;

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

    my $sql = 'SELECT track_id, album_id FROM ' . kTable;

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

1;