#------------------------------------------------------------
# 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 = $fileID AND group_num = $groupNum";

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

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

	my $sql = "SELECT * FROM " . kTable . " WHERE file_id = $fileID";

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

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

	my $sql = "SELECT * FROM " . kTable . " WHERE file_id = $fileID ORDER by group_num";

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

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

	my $sql = "SELECT * FROM " . kTable . " WHERE file_id = $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 = $fileID";

    $dbo->DoCmd($sql);
    
    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=$fileID AND "
	        . "(status = " . STATUS_MAPPED . " OR status = " . STATUS_APPROVED . ")";
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID AND "
			. "status = " . STATUS_APPROVED;
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID AND "
	        . "(status = " . STATUS_NEW . " OR status = " . STATUS_UNDONE . ")";
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID AND "
			. "status != " . STATUS_AUTO_APPROVED;
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID";
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID " 
			. "AND status != " . STATUS_AUTO_APPROVED;
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID " 
			. "AND status != " . STATUS_AUTO_APPROVED . " "
			. "AND group_num > $groupNum";
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID " 
			. "AND status != " . STATUS_AUTO_APPROVED . " "
			. "AND group_num < $groupNum";
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID " 
			. "AND status != " . STATUS_AUTO_APPROVED;
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID " 
			. "AND group_num = $groupNum";
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID " 
			. "AND status IN (" . STATUS_NEW . "," . STATUS_UNDONE . ")";
	
    my $sth = $dbo->DoCmd($sql);
    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=$fileID " 
			. "AND status = " . STATUS_MAPPED;
	
    my $sth = $dbo->DoCmd($sql);
    my $hr = $sth->fetchrow_hashref();
    return $hr->{min_group_num};
}

1;