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

use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::Log;
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use base 'RPS::DB::Item::DynamicReport';

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

# Most if not all of the Catalog group will have the same base group of columns.

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

    return {
        'title'          => {},
        'primary_artist' => {},

        #        'secondary_artist'  => {},
        'label_name'           => {},
        'catalog_number'       => {},
        'client_album_id'      => {},
        'genre_primary_id'     => {},
        'genre_secondary_id'   => {},
        'c_line'               => {},
        'p_line'               => {},
        'status'               => {},
        'custom_1'             => {},
        'custom_2'             => {},
        'custom_3'             => {},
        'date_created'         => {},
        'created_by'           => {},
        'date_modified'        => {},
        'modified_by'          => {},
        'album_id'             => {},
        'track_title'          => {},
        'track_number'         => {},
        'primary_track_artist' => {},

        #        'secondary_track_artist' => {},
        'track_composer'      => {},
        'duration'            => {},
        'explicit'            => {},
        'client_track_id'     => {},
        'isrc'                => {},
        'media_type'          => {},
        'track_custom_1'      => {},
        'track_custom_2'      => {},
        'track_custom_3'      => {},
        'track_date_created'  => {},
        'track_date_modified' => {},
        'track_created_by'    => {},
        'track_modified_by'   => {},
        'track_id'            => {},

    };

    # To add additional columns in a subclass, do this:
    #
    # my $config = $class->SUPER::_config();
    # $config->{'additional_column_1'} = {};
    # ...
    # return $config;
}

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

    my @select;
    $class->_BuildSelect( \@select, %args );

    my @joins;
    $class->_BuildJoins( \@joins, %args );

    my @groupBy;
    $class->_BuildGroupBy( \@groupBy, %args );

    my @orderBy;
    $class->_BuildOrderBy( \@orderBy, %args );

    my @where;
    $class->_BuildWhere( \@where, %args );

    my @from;
    $class->_BuildFrom( \@from, %args );

    my $sql = 'SELECT ' . join( ',', @select ) . ' FROM ' . join( ',', @from );

    if ( scalar @joins ) {
        $sql .= ' ' . join( ' ', @joins );
    }

    if ( scalar @where ) {
        $sql .= ' WHERE ' . join( ' AND ', @where );
    }

    if ( scalar @groupBy ) {
        $sql .= ' GROUP BY ' . join( ',', @groupBy );
    }

    if ( scalar @orderBy ) {
        $sql .= ' ORDER BY ' . join( ',', @orderBy );
    }

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

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

    push @$from, 'track';
}

sub _BuildSelect {
    my ( $class, $select, %args ) = @_;
    push @$select, "album.title AS title";
    push @$select, "primary_artist_table.name AS primary_artist";

    #    push @$select, "GROUP_CONCAT(secondary_artist_table.name) AS secondary_artist";
    #    push @$select, "secondary_artist_table.name AS secondary_artist";
    push @$select, "label.label_name AS label_name";
    push @$select, "album.catalog_number AS catalog_number";
    push @$select, "album.client_album_id AS client_album_id";
    push @$select, "album.genre_primary_id AS genre_primary_id";
    push @$select, "album.genre_secondary_id AS genre_secondary_id";
    push @$select, "album.c_line as c_line";
    push @$select, "album.p_line as p_line";
    push @$select, "IF(album.status = 1, 'Active', 'Inactive') as status";
    push @$select, "album.custom_1 as custom_1";
    push @$select, "album.custom_2 as custom_2";
    push @$select, "album.custom_3 as custom_3";
    push @$select, "album.date_created as date_created";
    push @$select, "album.created_by as created_by";
    push @$select, "album.date_modified as date_modified";
    push @$select, "album.modified_by as modified_by";
    push @$select, "album.album_id as album_id";

    #    push @$select, "secondary_track_artist_table.name AS secondary_track_artist";
    push @$select, "track.title AS track_title";
    push @$select, "track.track_id as track_id";
    push @$select, "track.track_order as track_number";
    push @$select, "primary_track_artist_table.name AS primary_track_artist";
    push @$select, "composer.name AS track_composer";
    push @$select, "master.duration AS duration";
    push @$select, "track.parental_advisory AS explicit";
    push @$select, "track.client_track_id AS client_track_id";
    push @$select, "master.isrc AS isrc";
    push @$select, "track.media_type AS media_type";
    push @$select, "track.custom_1 AS track_custom_1";
    push @$select, "track.custom_2 AS track_custom_2";
    push @$select, "track.custom_3 AS track_custom_3";
    push @$select, "track.date_created AS track_date_created";
    push @$select, "track.date_modified AS track_date_modified";
    push @$select, "track.created_by AS track_created_by";
    push @$select, "track.modified_by AS track_modified_by";
}

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

    push @$joins, "LEFT JOIN master ON (track.master_id = master.master_id)";
    push @$joins, "LEFT JOIN song ON (master.song_id = song.song_id)";
    push @$joins, "LEFT JOIN composer ON (song.composer_id = composer.composer_id)";
    push @$joins, "LEFT JOIN album ON (track.album_id = album.album_id)";
    push @$joins, "LEFT JOIN label ON (album.label_id = label.label_id)";

#    push @$joins, "LEFT OUTER JOIN album_additional_artist ON (album.album_id = album_additional_artist.album_id)";
#    push @$joins, "LEFT OUTER JOIN artist AS secondary_artist_table ON (album_additional_artist.artist_id = secondary_artist_table.artist_id)";
    push @$joins, "LEFT OUTER JOIN artist AS primary_artist_table ON (album.artist_id = primary_artist_table.artist_id)";

    #    push @$joins, "LEFT OUTER JOIN track_additional_artist ON (track.track_id = track_additional_artist.track_id)";
    push @$joins, "LEFT OUTER JOIN artist AS primary_track_artist_table ON (track.artist_id = primary_track_artist_table.artist_id)";

#    push @$joins, "LEFT OUTER JOIN artist AS secondary_track_artist_table ON (track_additional_artist.artist_id = secondary_track_artist_table.artist_id)";
}

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

    #    push @$groupBy, "album_id";
}

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

    my $dateType  = $args{dateType};
    my $startDate = $args{startDate};
    my $endDate   = $args{endDate};

    if ($dateType) {
        if ($startDate) {
            push @$where, "TO_DAYS(album.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate) {
            push @$where, "TO_DAYS(album.$dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }
    }
}

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

    #    push @$orderBy, "title";
    #    push @$orderBy, "track_number"; # Not positive that track_number is always populated.
    #    push @$orderBy, "track_id";     # ... so we'll also sort by track_id to make sure things aggregate properly

    # !!! From a performance point of view, it's slow to order by titles.
    # !!! Since these are not indexed.  Better to sort by our ids, and let the client sort stuff.
    push @$orderBy, "album_id";
    push @$orderBy, "track_number";
    push @$orderBy, "track_id";
}

1;