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

use strict;
use warnings;

use lib '/app/tools/common/lib';
use lib '/app/tools/bookpub/lib';
use Data::Dumper;
use Common::Assert;
use Common::Log;

use BookPub::DB::Item::CatalogImportItem::RegionIncludedCountry;
use BookPub::DB::Item::CatalogImportItem::RegionExcludedCountry;
use BookPub::DB::Item::CatalogImportItem::RegionIncludedTerritory;
use BookPub::DB::Item::CatalogImportItem::RegionExcludedTerritory;
use base 'BookPub::DB::Item::CatalogImportItem';

use constant kTable => 'catalog_import_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->catalog_import_region_id;

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

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

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

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

    if ( $oParent && $oParent->can('_getFromCache') ) {
        # clear cached data (it will be renewed automatically)
        $oParent->_clearCache('a_client_find_matching_region');
        $oParent->_clearCache('h_client_countries_territories');
    }

    return $newRegion;
}

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

    my $oParent         = $args{caller_obj};
    my $catalogImportID = $args{catalogImportID};
    assert($catalogImportID);

    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 ($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_client_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;
}

# This method conveniently queries the database for the country and territory lists.
#
sub getCountryAndTerritoryHash {
    my ($self, %args) = @_;

    my $oParent  = $args{caller_obj};
    my $regionID = $self->catalog_import_region_id;
    assert($regionID);

    my $hData = $oParent && $oParent->can('_getFromCache')
        ? $oParent->_getFromCache('h_client_countries_territories')
        : getAllCountryAndTerritory();

    my $hResult = {
        includedCountries   => undef,
        includedTerritories => undef,
        excludedCountries   => undef,
        excludedTerritories => undef,
    };

    if ( exists $hData->{$regionID} ) {
        $hResult->{$_} = [ split ',', $hData->{$regionID}{$_} ] for keys %$hResult;
    }

    return $hResult;
}

sub getAllForMatchingRegion {
    my (%args) = @_;

    my $catalogImportID = $args{catalogImportID} // 0;

    my $sql = qq{
        SELECT 
            catalog_import_region_id, catalog_import_id,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_included_country.country_id ORDER BY catalog_import_region_included_country.country_id), '') as country_list,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_excluded_country.country_id ORDER BY catalog_import_region_excluded_country.country_id), '') as excluded_country_list,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_included_territory.country_territory_id ORDER BY catalog_import_region_included_territory.country_territory_id), '') as territory_list,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_excluded_territory.country_territory_id ORDER BY catalog_import_region_excluded_territory.country_territory_id), '') as excluded_territory_list
        FROM catalog_import_region
        LEFT JOIN catalog_import_region_included_country USING (catalog_import_region_id)
        LEFT JOIN catalog_import_region_excluded_country USING (catalog_import_region_id)
        LEFT JOIN catalog_import_region_included_territory USING (catalog_import_region_id)
        LEFT JOIN catalog_import_region_excluded_territory USING (catalog_import_region_id)
        WHERE catalog_import_id = $catalogImportID
        GROUP BY catalog_import_region_id;
    };

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

    return $aData;
}

sub getAllCountryAndTerritory {
    my @args = @_;

    my $sql = qq{
        SELECT catalog_import_region_id,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_included_country.country_id ORDER BY catalog_import_region_included_country.country_id), '') AS includedCountries,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_excluded_country.country_id ORDER BY catalog_import_region_excluded_country.country_id), '') AS excludedCountries,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_included_territory.country_territory_id ORDER BY catalog_import_region_included_territory.country_territory_id), '') AS includedTerritories,
            IFNULL(GROUP_CONCAT(DISTINCT catalog_import_region_excluded_territory.country_territory_id ORDER BY catalog_import_region_excluded_territory.country_territory_id), '') AS excludedTerritories
        FROM catalog_import_region
        LEFT JOIN catalog_import_region_included_country USING (catalog_import_region_id)
        LEFT JOIN catalog_import_region_excluded_country USING (catalog_import_region_id)
        LEFT JOIN catalog_import_region_included_territory USING (catalog_import_region_id)
        LEFT JOIN catalog_import_region_excluded_territory USING (catalog_import_region_id)
        GROUP BY catalog_import_region_id;
    };

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    my $hData = $sth->fetchall_hashref('catalog_import_region_id');

    return $hData;
}

1;