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

use strict;
use warnings;

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

use Common::DB::Item;
use base 'Common::DB::Item';

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

sub _reload { }

sub _config {
    return {
        'accrual'               => {},
        'album_artist'          => {},
        'album_custom_1'        => {},
        'album_custom_2'        => {},
        'album_custom_3'        => {},
        'album_date_created'    => {},
        'album_id'              => {},
        'album_title'           => {},
        'ca_mechanical_run_id'  => {},
        'catalog_number'        => {},
        'client_album_id'       => {},
        'isrc'                  => {},
        'label_id'              => {},
        'label_name'            => {},
        'product_id'            => {},
        'product_title'         => {},
        'product_type'          => {},
        'rate'                  => {},
        'track_artist'          => {},
        'track_custom_1'        => {},
        'track_custom_2'        => {},
        'track_custom_3'        => {},
        'track_id'              => {},
        'track_title'           => {},
        'units'                 => {},
        'unlicensed_share'      => {},
        'upc_ean'               => {},
        'year'                  => {},
    };
}

sub GetByRunID {
    my ( $class, $runID, $labelID ) = @_;

    my @selectFields;

    # Report data
    push @selectFields, "track_log.unlicensed_share";
    push @selectFields, "track_log.accrual";
    push @selectFields, "track_log.year";
    push @selectFields, "track_log.rate";
    push @selectFields, "track_log.units";

    # Album metadata
    push @selectFields, "track_log.album_id";
    push @selectFields, "album.title as album_title";
    push @selectFields, "a_artist.name as album_artist";
    push @selectFields, "album.catalog_number";
    push @selectFields, "album.client_album_id";
    push @selectFields, "album.date_created as album_date_created";
    push @selectFields, "album.custom_1 as album_custom_1";
    push @selectFields, "album.custom_2 as album_custom_2";
    push @selectFields, "album.custom_3 as album_custom_3";

    # Track metadata
    push @selectFields, "track_log.track_id";
    push @selectFields, "track.title as track_title";
    push @selectFields, "t_artist.name as track_artist";
    push @selectFields, "track.custom_1 as track_custom_1";
    push @selectFields, "track.custom_2 as track_custom_2";
    push @selectFields, "track.custom_3 as track_custom_3";
    push @selectFields, "master.isrc";

    # Product metadata
    push @selectFields, "track_log.product_type";
    push @selectFields, "track_log.upc_ean";

    # Label metadata
    push @selectFields, "label.label_name";

    my $selectClause = join( ',', @selectFields );

    my $sql =
          "SELECT $selectClause FROM " . kTable . " as track_log"
        . " LEFT JOIN album ON (album.album_id = track_log.album_id)"
        . " LEFT JOIN artist as a_artist ON (a_artist.artist_id = album.artist_id)"
        . " LEFT JOIN track ON (track.track_id = track_log.track_id)"
        . " LEFT JOIN master ON (track.master_id = master.master_id)"
        . " LEFT JOIN artist as t_artist ON (t_artist.artist_id = track.artist_id)"
        . " LEFT JOIN label ON (label.label_id = album.label_id)"
        . " WHERE ca_mechanical_run_id = " . $class->quote($runID)
        . " AND (units != 0 OR accrual != 0)";

    if ( defined($labelID) ) {
        $sql .= " AND track_log.label_id = " . $class->quote($labelID);
    }

    # These should be sorted by label, and then album title, and then config (and then maybe track title...).
    #
    $sql .= " ORDER BY label_name, album_title, product_type, track_title, year";

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

sub DeleteByRunID {
    my ( $class, $id ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "DELETE FROM " . kTable . " WHERE ca_mechanical_run_id = ?";
    $dbo->DoCmdWithPlaceholders($sql, [$id]);
}

sub GetLabelsByRunID {
    my ( $class, $id ) = @_;

    my $sql = "SELECT DISTINCT " . kTable . ".label_id, label.label_name";
    $sql .=  " FROM " . kTable . " LEFT JOIN label USING (label_id)";
    $sql .=  " WHERE ca_mechanical_run_id = " . $class->quote($id);
    $sql .=  " ORDER BY label.label_name";
    return $class->SUPER::GetAll($sql);
}

sub ReportAvailable {
    my ( $class, $id ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders( "SELECT COUNT(*) from " . kTable . " where ca_mechanical_run_id = ?", [$id] );
    my $hr  = $sth->fetchrow_hashref();
    if ( $hr->{'COUNT(*)'} > 0 ) {
        return 1;
    }
    return 0;
}

1;