#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package Metadata::DB::Item::ContentImportData;
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::Product;

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

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

use constant kAlbumColumns => qw(
  content_import_id
  content_import_data_id
  album_title
  album_artist
  additional_album_artists
  label_name
  catalog_no
  client_album_id
  digital_product_id
  digital_upc
  digital_release_date
  cd_product_id
  cd_upc
  cd_release_date
  vinyl_product_id
  vinyl_upc
  vinyl_release_date
  dvd_product_id
  dvd_upc
  dvd_release_date
  primary_genre
  secondary_genre
  c_line
  p_line
  territories_allowed
  territories_denied
  album_custom_1
  album_custom_2
  album_custom_3
  cleared_for_distribution
  price_tiers
  override
);

use constant kTrackColumns => qw(
  content_import_data_id
  track_title
  digital_disc_number
  digital_track_number
  cd_disc_number
  cd_track_number
  vinyl_disc_number
  vinyl_track_number
  dvd_disc_number
  dvd_track_number
  track_artist
  additional_track_artists
  track_composer
  track_minutes
  track_seconds
  explicit
  client_track_id
  isrc
  media_type
  exclude_digitally
  album_purchase_only
  track_custom_1
  track_custom_2
  track_custom_3
);

sub GetAsHashref {
    my ($class) = @_;
    my @keys = $class->GetColumns();
    my %result;

    foreach (@keys) {
        next if ( $_ =~ /^_/ );    #ignore private data
        $result{$_} = $class->$_();
    }

    return \%result;
}

sub GetColumns {
    my ($class) = @_;

    my $config = $class->_config();

    return keys(%$config);
}

sub HasImportDuplicates {
    my %args           = @_;
    my $target_fields  = $args{fields};
    my $target_data    = $args{target};
    my $group_by_field = $args{group_by};
    my $other_criteria = $args{other_criteria};
    my $dbo            = Common::RSApp::GetClientDB();
    my $where_sql;

    assert( defined($target_fields) && defined($target_data) );

    # Build the where clause
    if ( ref($target_fields) ) {
        foreach my $t (@$target_fields) {
            $where_sql .= " OR " if $where_sql;
            $where_sql .= " $t = " . $dbo->DBQuote($target_data);
        }
        $where_sql = "( $where_sql )";

    } else {
        $where_sql = " $target_fields = " . $dbo->DBQuote($target_data);
    }

    my $sql = <<SQL;
SELECT content_import_data_id
FROM
    content_import_data
    INNER JOIN content_import USING (content_import_id)
WHERE
    store_date IS NULL AND reject_date IS NULL AND
    $where_sql
SQL

    $sql .= " AND " . $dbo->DBQuote($other_criteria) if $other_criteria;
    $sql .= " GROUP BY $group_by_field" if ($group_by_field);

    $sql = "SELECT count(*) FROM ( $sql ) as subq ";

    Common::Log::Debug("Running Query: $sql");
    my $sth = $dbo->DoCmd($sql);

    my ($count) = $sth->fetchrow_array();

    return $count && $count > 1 ? 1 : undef;
}

sub GetImportSummary {
    my $status           = shift;
    my $include_override = shift;
    my $result           = {};

    my $sql = <<SQL;
SELECT
    COUNT(DISTINCT(if( catalog_no IS NULL, "", catalog_no))) as TotalAlbums,
    SUM(tracks) as TotalTracks,
    COUNT( IF( status = 9, 1, NULL ) ) as Errors,
    COUNT( IF( status = 7 OR status = 8, 1, NULL ) ) as Duplicates,
    COUNT( IF( status = 6, 1, NULL ) ) as Warnings,
    COUNT( IF( status = 3, 1, NULL ) ) as Notice,
    COUNT( IF( status = 0 or status IS NULL, 1, NULL ) ) as Good
FROM (
    SELECT
        catalog_no,
        COUNT(DISTINCT(content_import_data.content_import_data_id)) as tracks,
        max(status) as status, override
    FROM content_import_data
         LEFT JOIN content_import_data_status USING ( content_import_data_id )
    GROUP BY catalog_no ) as subq
SQL

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

    $sql .= " WHERE ( status IS NULL OR status <= " . $dbo->DBQuote($status) . ") "
      if ( defined($status) );

    $sql .= " OR ( override IS NOT NULL AND override > 0 ) "
      if ($include_override);

    $sql .= " OR ( override IS NOT NULL AND override > 0 ) "
      if ($include_override);

    Common::Log::Debug("Running Query: $sql");

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

    $result = $sth->fetchrow_hashref();

    $result->{TotalTracks} = 0 unless ( $result->{TotalTracks} );

    return $result;
}

sub GetAlbumCount {
    my ( $class, %args ) = @_;
    my $criteria = $args{criteria};
    my $where_sql;
    my $dbo = Common::RSApp::GetClientDB();

    #
    # Build the where statement if we are limiting by status
    #
    if ( defined($criteria) ) {
        $where_sql = " WHERE status = " . $dbo->DBQuote($criteria);
        if ( $criteria == 0 ) {
            $where_sql .= " OR status IS NULL ";
        } elsif ( $criteria == 7 ) {
            $where_sql .= " OR status = 8 ";
        }
    }

    return 0 unless ( defined($where_sql) );

    #
    # Put it all together and runt the query
    #
    my $table = kTable;
    my $sql   = <<SQL;
SELECT COUNT(*) FROM (
    SELECT catalog_no, MAX(status) as status
    FROM   $table
           LEFT JOIN content_import_data_status USING (content_import_data_id)
    GROUP BY catalog_no
) as subq
$where_sql
SQL

    Common::Log::Debug("Running Query: $sql");

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

    return $count;
}

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

    my $limit     = $args{limit};
    my $page      = $args{page} - 1 unless ( $args{show_all} );
    my $criteria  = $args{criteria};
    my $import_id = $args{content_import_data_id};
    my $table     = kTable;

    my $limit_sql  = " ";
    my $select_sql = " ";
    my $where_sql  = "";

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

    #
    # Build out limit statement if we are not "showing all"
    #
    unless ( $args{show_all} ) {

        # Set the page to 0 unless it is a valid positive integer
        $page = ( defined($page) && $page =~ /^[0-9]+$/ ) ? $page : 0;

        # Set the limit to 10 records unless the limit is a valid + int.
        $limit = ( defined($limit) && $limit =~ /^[0-9]+$/ ) ? $limit : 10;

        $limit_sql = sprintf( " LIMIT %d,%d", $page * $limit, $limit );
    }

    #
    # Build out select statement
    #
    $select_sql = join( ", ", (Metadata::DB::Item::ContentImportData::kAlbumColumns) );

    $select_sql =~ s/(content_import_data_id)/MIN($1) as $1/;
    $select_sql =~ s/(content_import_data_id)/content_import_data.$1/;
    $select_sql .= ",MAX(status) as status";
    $select_sql .= ",COUNT(DISTINCT(content_import_data_id)) as track_count";

    #
    # Build the where statement if we are limiting by status
    #
    if ( defined($criteria) ) {
        $where_sql = " WHERE status = " . $dbo->DBQuote($criteria);
        if ( $criteria == 0 ) {
            $where_sql .= " OR status IS NULL ";
        } elsif ( $criteria == 7 ) {
            $where_sql .= " OR status = 8 ";
        }
    }

    if ( defined($import_id) ) {
        $where_sql .= length($where_sql) ? " AND " : " WHERE ";
        $where_sql .= "content_import_data_id = " . $dbo->DBQuote($import_id);
    }

    #
    # Put it all together and runt the query
    #
    my $sql = <<SQL;
SELECT * FROM (
    SELECT $select_sql
    FROM   $table
           LEFT JOIN content_import_data_status USING (content_import_data_id)
    GROUP BY catalog_no
) as subq
$where_sql
$limit_sql
SQL

    Common::Log::Debug("Running Query: $sql");

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

sub GetTrackSummary {
    my ( $class, %args ) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $where_sql =
      $args{catalog_no}
      ? "catalog_no = " . $dbo->DBQuote( $args{catalog_no} )
      : "(catalog_no = '' OR catalog_no IS NULL )";

    my $select = join( ", ", (Metadata::DB::Item::ContentImportData::kTrackColumns) );
    $select =~ s/(content_import_data_id)/content_import_data.$1/;
    $select .= ",MAX(status) as status";

    my $table      = kTable;
    my $catalog_no = $dbo->DBQuote( $args{catalog_no} );
    my $sql        = <<SQL;
SELECT $select
FROM $table
     LEFT JOIN content_import_data_status USING (content_import_data_id)
WHERE $where_sql
GROUP BY $table.content_import_data_id
ORDER BY digital_disc_number, digital_track_number + 0,
         cd_disc_number, cd_track_number + 0,
         vinyl_disc_number, vinyl_track_number + 0,
         dvd_disc_number, dvd_track_number + 0
SQL

    Common::Log::Debug("Running Query: $sql");

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

sub GetAllTracks {
    my ( $class, %args ) = @_;
    my $dbo       = Common::RSApp::GetClientDB();
    my $where_sql = "";
    my $criteria;

    Common::Log::Debug( $args{criteria} );

    if ( defined( $args{criteria} ) ) {
        $criteria = ref( $args{criteria} ) ? $args{criteria} : [ $args{criteria} ];
    }

    my @albums;
    my @all_albums;
    my @quoted_albums;

    my $nullCatalogClause = "";

    if ($criteria) {
        foreach my $crit ( @{$criteria} ) {
            @albums = $class->_getCatalogNoByStatus( criteria => $crit );
            push( @all_albums, @albums );
        }

        if (@all_albums) {
            foreach (@all_albums) {
                push( @quoted_albums, $dbo->DBQuote($_) );
                $nullCatalogClause = " OR catalog_no IS NULL " unless ($_);
            }

            $where_sql = " WHERE catalog_no IN (" . join( ',', @quoted_albums ) . ") $nullCatalogClause";
        } else {
            $where_sql = " WHERE 0 = 1";
        }
    }

    my $table = kTable;
    my $sql   = <<SQL;
SELECT *
FROM $table
$where_sql
GROUP BY $table.content_import_data_id
ORDER BY catalog_no
SQL

    Common::Log::Debug("Running Query: $sql");

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

sub ToggleOverride {
    my ( $class, %args ) = @_;
    my $dbo   = Common::RSApp::GetClientDB();
    my $table = kTable;
    my $cid   = $dbo->DBQuote( $args{cidID} );

    my $sql = <<SQL;
UPDATE $table
       INNER JOIN $table as cid USING ( catalog_no )
SET cid.override = ( $table.override + 1 ) % 2
WHERE $table.content_import_data_id = $cid
SQL

    Common::Log::Debug("Running Query: $sql");

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

sub UpdateValidationStatus {
    my ( $class, %args ) = @_;
    my $dbo    = Common::RSApp::GetClientDB();
    my $table  = kTable;
    my $status = $dbo->DBQuote( $args{status} );

    assert( $args{catalog_no} || $args{content_import_data_id} || $args{all} );

    my $sql = <<SQL;
UPDATE $table
SET validated = $status
WHERE
SQL

    if ( $args{content_import_data_id} ) {
        $sql .= " content_import_data_id = " . $dbo->DBQuote( $args{content_import_data_id} );
    }

    if ( $args{catalog_no} ) {
        $sql .= " catalog_no = " . $dbo->DBQuote( $args{catalog_no} );
    }

    if ( $args{all} ) {
        $sql .= " 1 = 1";
    }

    Common::Log::Debug("Running Query: $sql");

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

sub _getCatalogNoByStatus {
    my ( $class, %args ) = @_;
    my $dbo = Common::RSApp::GetClientDB();
    my @result;
    my $where_sql;

    return () unless ( $args{criteria} );

    #
    # Build the where statement if we are limiting by status
    #
    if ( defined( $args{criteria} ) ) {
        $where_sql = " WHERE status = " . $dbo->DBQuote( $args{criteria} );
    }

    #
    # Put it all together and runt the query
    #
    my $table = kTable;
    my $sql   = <<SQL;
SELECT catalog_no FROM (
    SELECT catalog_no, MAX(status) as status
    FROM   $table
           LEFT JOIN content_import_data_status USING (content_import_data_id)
    GROUP BY catalog_no
) as subq
$where_sql
SQL

    my $sth = $dbo->DoCmd($sql) || die "$@";
    my $catalog_no;

    while ( ($catalog_no) = $sth->fetchrow_array() ) {
        push( @result, $catalog_no );
    }

    return @result;
}

sub SearchForDuplicates {
    my ( $class, %args ) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $where;

    $where .= " a.title = " . $dbo->DBQuote( $args{album_title} )
      if ( $args{album_title} );

    $where .= sprintf( " %s %s = %s", $where ? "OR" : "", "client_album_id", $dbo->DBQuote( $args{client_album_id} ) )
      if ( $args{client_album_id} );

    $where .= sprintf( " %s %s = %s", $where ? "OR" : "", "isrc", $dbo->DBQuote( $args{isrc} ) )
      if ( $args{isrc} );

    $where .= sprintf( " %s %s = %s", $where ? "OR" : "", "client_track_id", $dbo->DBQuote( $args{client_track_id} ) )
      if ( $args{client_track_id} );

    if ( $args{digital_upc} ) {
        $where .= sprintf(
            " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote( $args{digital_upc} ),
            RPS::DB::Item::Product::kProductTypeDigital
        );

    }

    if ( $args{cd_upc} ) {
        $where .= sprintf(
            " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote( $args{cd_upc} ),
            RPS::DB::Item::Product::kProductTypeDigital
        );

    }

    if ( $args{vinyl_upc} ) {
        $where .= sprintf(
            " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote( $args{vinyl_upc} ),
            RPS::DB::Item::Product::kProductTypeDigital
        );

    }

    if ( $args{dvd_upc} ) {
        $where .= sprintf(
            " %s (upc_ean = %s AND product_type_id = %s)",
            $where ? "OR" : "",
            $dbo->DBQuote( $args{dvd_upc} ),
            RPS::DB::Item::Product::kProductTypeDigital
        );

    }

    unless ($where) {

        #        die "no valid param passed to album search";
        $where = "1=1";
    }

    my $productIDs = {
        CD           => RPS::DB::Item::Product::kProductTypeCD,
        Digital      => RPS::DB::Item::Product::kProductTypeDigital,
        DigitalTrack => RPS::DB::Item::Product::kProductTypeDigitalTrack,
        DVD          => RPS::DB::Item::Product::kProductTypeDVD,
        LP           => RPS::DB::Item::Product::kProductTypeLP
    };

    my $sql = <<SQL;
SELECT
   a.album_id       as album_id,
   name             as album_artist,
   a.custom_1       as album_custom_1,
   a.custom_2       as album_custom_2,
   a.custom_3       as album_custom_3,
   a.title          as album_title,
   c_line           as c_line,
   catalog_number   as catalog_no,
   client_album_id  as client_album_id,
   label_name       as label_name,
   p_line           as p_line,
   release_date     as release_date,
   COUNT( DISTINCT( track_id ) ) as track_count,
   IF( product_type_id = $productIDs->{CD},      upc_ean, NULL ) as cd_upc,
   IF( product_type_id = $productIDs->{Digital}, upc_ean, NULL ) as digital_upc,
   IF( product_type_id = $productIDs->{DVD},     upc_ean, NULL ) as dvd_upc,
   IF( product_type_id = $productIDs->{LP},      upc_ean, NULL ) as vinyl_upc
FROM album as a
     LEFT JOIN artist  as ar ON ( a.artist_id = ar.artist_id )
     LEFT JOIN label   as l  ON ( a.label_id  = l.label_id )
     LEFT JOIN product as p  ON ( a.album_id  = p.asset_id )
     LEFT JOIN track   as t  ON ( a.album_id  = t.album_id )
     LEFT JOIN master  as m  ON ( t.master_id = m.master_id )
WHERE ( $where )
GROUP BY a.album_id
SQL

    Common::Log::Debug("Running Query: $sql");

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

sub SearchForDuplicateTracks {
    my ( $class, %args ) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $where = " album_id = " . $dbo->DBQuote( $args{album_id} );

    use Data::Dumper;
    Common::Log::Debug( "HERE: " . Dumper( \%args ) );

    $where .= sprintf( " %s %s = %s", $where ? "OR" : "", "client_track_id", $dbo->DBQuote( $args{client_track_id} ) )
      if ( $args{client_track_id} );

    $where .= sprintf( " %s %s = %s", $where ? "AND" : "", "isrc", $dbo->DBQuote( $args{isrc} ) );

    unless ($where) {
        die "no valid param passed to album search";
    }

    my $sql = <<SQL;
SELECT
   client_track_id as client_track_id,
   isrc as isrc,
   a.name as track_artist,
   custom_1 as track_custom_1,
   custom_2 as track_custom_2,
   custom_3 as track_custom_3,
   if( duration, TRUNCATE(duration / 60, 0), NULL ) as track_minutes,
   track_order as track_number,
   if( duration, duration % 60, NULL ) as track_seconds,
   t.title as track_title
FROM track as t
     LEFT JOIN master  as m  USING ( master_id )
     LEFT JOIN song    as s  USING ( song_id )
     LEFT JOIN artist  as a  ON ( m.artist_id = a.artist_id )
WHERE ( $where )
ORDER BY track_order
SQL

    Common::Log::Debug("Running Query: $sql");

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