#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2010 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#---------------------------------------------------------------
package BookPub::DB::Item::Region;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use lib '/app/tools/bookpub/lib';

use Common::Assert;

use base 'BookPub::DB::MetadataItem';
use BookPub::DB::Item::RegionIncludedCountry;
use BookPub::DB::Item::RegionExcludedCountry;
use BookPub::DB::Item::RegionIncludedTerritory;
use BookPub::DB::Item::RegionExcludedTerritory;
use BookPub::DB::Item::Country;
use BookPub::DB::Item::CountryTerritory;
use BookPub::DB::Item::Archive::Region;

use constant kTable => 'region';
use constant kDB    => Common::DB::Item::kClientDB;

sub _NewArchiveItem {
    my ( $class, %args ) = @_;
    return BookPub::DB::Item::Archive::Region->Create(%args);
}

sub _ArchiveTableName {
    return 'archive_region';
}

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

    my $oParent             = $args{caller_obj};
    my $countries           = $args{includedCountries};
    my $countriesExcluded   = $args{excludedCountries};
    my $territories         = $args{includedTerritories};
    my $territoriesExcluded = $args{excludedTerritories};

    # First we create the region table entry.
    # Then we'll create the matching country/territory records.
    #
    my %createArgs;
    $createArgs{catalog_import_id} = $args{catalogImportID} if $args{catalogImportID};
    $createArgs{name}              = $args{name}            if defined $args{name};
    my $newRegion = $class->Create(%createArgs);
    $newRegion->save();

    my $regionID = $newRegion->region_id;

    if ($countries) {
        foreach my $countryID (@$countries) {
            my $newCountry = BookPub::DB::Item::RegionIncludedCountry->Create(
                region_id  => $regionID,
                country_id => $countryID,
            );
            $newCountry->save();
        }
    }

    if ($countriesExcluded) {
        foreach my $countryID (@$countriesExcluded) {
            my $newCountry = BookPub::DB::Item::RegionExcludedCountry->Create(
                region_id  => $regionID,
                country_id => $countryID,
            );
            $newCountry->save();
        }
    }

    if ($territories) {
        foreach my $territoryID (@$territories) {
            my $newTerritory = BookPub::DB::Item::RegionIncludedTerritory->Create(
                region_id            => $regionID,
                country_territory_id => $territoryID,
            );
            $newTerritory->save();
        }
    }

    if ($territoriesExcluded) {
        foreach my $territoryID (@$territoriesExcluded) {
            my $newTerritory = BookPub::DB::Item::RegionExcludedTerritory->Create(
                region_id    => $regionID,
                territory_id => $territoryID,
            );
            $newTerritory->save();
        }
    }

    if ( $oParent && $oParent->can('_getFromCache') ) {

        # clear cached data (it will be renewed automatically)
        $oParent->_clearCache('a_common_find_matching_region');
    }

    return $newRegion;
}

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

    my $oParent             = $args{caller_obj};
    my $country             = $args{country};
    my $countries           = $args{includedCountries};
    my $territories         = $args{includedTerritories};
    my $excludedCountries   = $args{excludedCountries};
    my $excludedTerritories = $args{excludedTerritories};

    my $countryString;
    my $territoryString;
    my $excludedCountryString;
    my $excludedTerritoryString;

    if ($country) {
        $countryString = $country;
    } elsif ($countries) {
        $countryString = join( ',', sort { $a <=> $b } @$countries );
    }

    if ($excludedCountries) {
        $excludedCountryString = join( ',', sort { $a <=> $b } @$excludedCountries );
    }

    if ($territories) {
        $territoryString = join( ',', sort { $a <=> $b } @$territories );
    }

    if ($excludedTerritories) {
        $excludedTerritoryString = join( ',', sort { $a <=> $b } @$excludedTerritories );
    }

    my $aData = [];
    if ( $oParent && $oParent->can('_getFromCache') ) {
        $aData = $oParent->_getFromCache( 'a_common_find_matching_region', %args );
    } else {
        $aData = getAllForMatchingRegion(%args);
    }

    foreach my $hr (@$aData) {
        no warnings qw(uninitialized);
        if (   $countryString eq $hr->{country_list}
            && $excludedCountryString eq $hr->{excluded_country_list}
            && $territoryString eq $hr->{territory_list}
            && $excludedTerritoryString eq $hr->{excluded_territory_list} ) {
            return $class->Lookup( hash => $hr );
        }
    }

    return;
}

sub GetAllRegions {
    my $class   = shift;
    my %args    = @_;
    my $country = $args{country_code};

    assert($country);

    my $countryJoin =
        "LEFT JOIN region_included_country     ON ( region.region_id = region_included_country.region_id ) "
      . "LEFT JOIN country as country_included ON ( country_included.country_id = region_included_country.country_id ) ";

    my $countryWhere =
        "( country_included.alpha2 IS NULL OR country_included.alpha2 = "
      . $class->quote($country)
      . " ) AND "
      . "( SELECT count(*) FROM region_excluded_country as exclude "
      . " LEFT JOIN country as country_excluded ON ( exclude.country_id = country_excluded.country_id ) "
      . "WHERE exclude.region_id = region.region_id AND country_excluded.alpha2 = "
      . $class->quote($country)
      . " ) = 0 ";

    my $sql = "SELECT region.* FROM region $countryJoin " . "WHERE ($countryWhere)";

    Log->debug("QUERY: $sql");

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

sub getAllForMatchingRegion {
    my @args = @_;

    my $sql = qq{
        SELECT *,
            IFNULL(GROUP_CONCAT(DISTINCT region_included_country.country_id ORDER BY region_included_country.country_id), '') as country_list,
            IFNULL(GROUP_CONCAT(DISTINCT region_excluded_country.country_id ORDER BY region_excluded_country.country_id), '') as excluded_country_list,
            IFNULL(GROUP_CONCAT(DISTINCT region_included_territory.country_territory_id ORDER BY region_included_territory.country_territory_id), '') as territory_list,
            IFNULL(GROUP_CONCAT(DISTINCT region_excluded_territory.country_territory_id ORDER BY region_excluded_territory.country_territory_id), '') as excluded_territory_list
        FROM region
        LEFT JOIN region_included_country USING (region_id)
        LEFT JOIN region_excluded_country USING (region_id)
        LEFT JOIN region_included_territory USING (region_id)
        LEFT JOIN region_excluded_territory USING (region_id)
        GROUP BY region_id;
    };

    my $dbo   = Common::RSApp::GetClientDB();
    my $sth   = $dbo->DoCmd($sql);
    my $aData = $sth->fetchall_arrayref( {} );

    return $aData;
}

sub GetAllByProductID {
    my $class     = shift;
    my %args      = @_;

    my $productID = $class->quote( $args{productID} ) if defined $args{productID};
    assert($productID);

    my $sql = "SELECT * FROM region WHERE region_id IN ";
    $sql .= "( SELECT region_id FROM product_market_price WHERE product_market_id IN ";
    $sql .= "( SELECT product_market_id FROM product_market WHERE product_id = $productID ) ";
    $sql .= ")";

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


1;