#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::MapFaceTempMapping;
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 => 'mapface_temp_mapping';
use constant kDB    => Common::DB::Item::kClientDB();

use constant STATUS_NEW           => 0;
use constant STATUS_MAPPED        => 1;
use constant STATUS_UNDONE        => 2;
use constant STATUS_APPROVED      => 3;
use constant STATUS_AUTO_APPROVED => 4;

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

    my $sql =
        "SELECT * FROM "
        . kTable
        . " WHERE file_id = "
        . $class->quote($fileID)
        . " AND group_num = "
        . $class->quote($groupNum);

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

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

    my $sql = "SELECT * FROM " . kTable . " WHERE file_id = " . $class->quote($fileID);

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

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

    my $sql = "SELECT * FROM " . kTable . " WHERE file_id = " . $class->quote($fileID) . " ORDER by group_num";

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

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

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE file_id = "
      . $class->quote($fileID)
      . "AND status != "
      . STATUS_AUTO_APPROVED . " "
      . "AND status != "
      . STATUS_APPROVED;

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

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

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

    return 1;
}

# Note that this count includes both mapped and approved mappings (but NOT auto-approved mappings).
sub GetResolvedCountByFileID {
    my ( $class, $fileID ) = @_;

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

    my $sql =
        "SELECT COUNT(*) AS count FROM "
      . kTable
      . " WHERE file_id = ? AND (status = ?  OR status = ?)";

    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, STATUS_MAPPED, STATUS_APPROVED]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

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

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

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT COUNT(*) AS count FROM "
      . kTable
      . " WHERE file_id = ? AND (status = ? OR status = ?)";

    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, STATUS_NEW, STATUS_UNDONE]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

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

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

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

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

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT MIN(group_num) AS min_group_num FROM " . kTable . " WHERE file_id = ? AND status != ?";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, STATUS_AUTO_APPROVED]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{min_group_num};
}

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT MIN(group_num) AS min_group_num FROM "
      . kTable
      . " WHERE file_id = ? AND status != ? AND group_num > ?";

    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, STATUS_AUTO_APPROVED, $groupNum]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{min_group_num};
}

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT MAX(group_num) AS max_group_num FROM "
      . kTable
      . " WHERE file_id = ? AND status != ? AND group_num < ?";

    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, STATUS_AUTO_APPROVED, $groupNum]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{max_group_num};
}

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

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

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

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

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

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

    my $sql =
        "SELECT MIN(group_num) AS min_group_num FROM "
      . kTable
      . " WHERE file_id = ? AND status IN (?, ?)";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, STATUS_NEW, STATUS_UNDONE]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{min_group_num};
}

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

    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "SELECT MIN(group_num) AS min_group_num FROM " . kTable . " WHERE file_id = ? AND status = ?";
    my $sth = $dbo->DoCmdWithPlaceholders($sql, [$fileID, STATUS_MAPPED]);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{min_group_num};
}

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

    my $args = join ', ', map { "$_ = NULL" } keys %$hData;
    return unless $mappingID && $args;

    my $sql = "UPDATE " . kTable . " SET $args WHERE mapface_temp_mapping_id IN (?)";
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmdWithPlaceholders($sql, [$mappingID]);

    return 1;
}

1;