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

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

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

# Error types
use constant kErrorNonqualifying => '0';
use constant kErrorService       => '1';
use constant kErrorCountry       => '2';
use constant kErrorProductType   => '3';
use constant kErrorFormatType    => '4';
use constant kErrorMediaType     => '5';

sub DeleteByFileID {
    my ( $class, $fileID ) = @_;

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

    return 1;
}

sub GetAllNonqualifyingByFileID {
    my ( $class, $fileID ) = @_;

    my $sql =
      "SELECT * FROM "
      . kTable
      . " WHERE file_id = "
      . $class->quote($fileID)
      . " AND error_type = "
      . kErrorNonqualifying
      . " ORDER BY sale_id";

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

sub GetAllQualifyingByFileID {
    my ( $class, $fileID ) = @_;

    my $sql =
      "SELECT * FROM "
      . kTable
      . " WHERE file_id = "
      . $class->quote($fileID)
      . " AND error_type != "
      . kErrorNonqualifying
      . " ORDER BY sale_id";

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

sub GetAllNonqualifyingBySaleID {
    my ( $class, $saleID ) = @_;

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE sale_id = "
      . $class->quote($saleID)
      . " AND error_type = "
      . kErrorNonqualifying
      . " ORDER BY description";

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

sub GetAllQualifyingBySaleID {
    my ( $class, $saleID ) = @_;

    my $sql =
        "SELECT * FROM "
      . kTable . " "
      . " WHERE sale_id = "
      . $class->quote($saleID)
      . " AND error_type != "
      . kErrorNonqualifying
      . " ORDER BY description";

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

sub GetAllByMappingID {
    my ( $class, $mappingID ) = @_;

    my $sql =
        "SELECT * FROM "
        . kTable
        . " WHERE mapface_temp_mapping_id = "
        . $class->quote($mappingID)
        . " ORDER BY sale_id";

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

sub GetDistinctSalesByMappingID {
    my ( $class, $mappingID, $limit, $index ) = @_;

    my $sql =
        "SELECT DISTINCT sale_id FROM "
        . kTable
        . " WHERE mapface_temp_mapping_id = "
        . $class->quote($mappingID)
        . " ORDER BY sale_id";

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

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

sub GetCountByFileID {
    my ( $class, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE file_id = ?";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub GetNonQualifiedCountByFileID {
    my ( $class, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE file_id = ? AND error_type = ?";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, kErrorNonqualifying]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub GetQualifiedCountByFileID {
    my ( $class, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE file_id = ? AND error_type != ?";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, kErrorNonqualifying]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub GetUnmappedByFileID {
    my ( $class, $fileID, $dataType ) = @_;

    my $sql =
        "SELECT * FROM "
      . kTable . " "
      . "WHERE file_id = ? "
      . "AND error_type != ? "
      . "AND mapped = 'N' "
      . "ORDER BY sale_id";

    if ( $dataType && $dataType eq 'ARRAY' ) {
        $sql = "
            SELECT
                file_id,
                mapface_temp_mapping_id,
                GROUP_CONCAT(DISTINCT error_type) AS error_types,
                GROUP_CONCAT(sale_import_error_id) AS sale_import_error_ids,
                GROUP_CONCAT(DISTINCT sale_id) AS sale_ids
            FROM " . kTable . "
            WHERE
                file_id = ?
                AND error_type != ?
                AND mapped = 'N'
            GROUP BY mapface_temp_mapping_id
        ";

        my $dbo   = Common::RSApp::GetClientDB();
        my $hData = $dbo->DoCmdWithPlaceholders($sql, [$fileID, kErrorNonqualifying])->fetchall_arrayref({});
        return $hData;
    }

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

sub GetUnmappedCountByFileID {
    my ( $class, $fileID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE file_id = ? AND mapped = 'N'";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub GetUnmappedCountBySaleID {
    my ( $class, $saleID ) = @_;

    my @saleIDs = grep { defined $_ && $_ =~ /^\d+$/ } split /,/, ( $saleID // '' );
    return 0 unless @saleIDs;

    my $dbo = Common::RSApp::GetClientDB();
    my $placeholders = join ',', ('?') x @saleIDs;
    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE sale_id IN ($placeholders) AND mapped = 'N'";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, \@saleIDs);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub GetUnmappedCountByMappingID {
    my ( $class, $mappingID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE mapface_temp_mapping_id = ? AND mapped = 'N'";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$mappingID]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub UpdateUnmappedByMappingIDs {
    my ( $class, $hData, $mappingID ) = @_;

    my $args = join ', ', map { "$_ = " . $class->quote( $hData->{$_} ) } keys %$hData;
    return unless $mappingID && $args;

    my $sql = "UPDATE " . kTable . " SET $args WHERE mapface_temp_mapping_id IN (" . $class->quote($mappingID) . ")";
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd($sql);

    return 1;
}

1;