package Common::DB::Item::Country;

use strict;
use warnings;

use Data::Dumper;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::DB::ItemCollection;
use Common::DB::Item;
use base 'Common::DB::Item';

use constant kTable => 'country';
use constant kDB    => Common::DB::Item::kCommonDB;

sub GetAll {
    my $class = shift;
    my $sql   = "SELECT * FROM " . kTable . " ORDER BY alpha2";

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

sub getAllWithCustom {
    my $class = shift;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = qq{
        SELECT
            IFNULL(country.country_id, '')  AS country_id,
            IFNULL(country.alpha2, '')      AS alpha2,
            IFNULL(country.alpha3, '')      AS alpha3,
            IFNULL(country.numeric3, '')    AS numeric3,
            IFNULL(country.name, '')        AS name,
            IFNULL(country_custom.name, '') AS custom_name
        FROM country
        LEFT JOIN country_custom USING (country_id)
    };
    my $sth = $dbo->DoCmd($sql);
    my $aData = $sth->fetchall_arrayref({});
    my %data = map {
        my $key = '|' . (join '|', @$_{qw/country_id alpha2 alpha3 numeric3 name custom_name/}) . '|';
        $key => $_;
    } @$aData;

    return \%data;
}

sub SearchByString {
    my $class = shift;
    my $searchCriteria = shift || return;

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

    my $sql =
        "SELECT country.* FROM "
      . kTable . " "
      . " LEFT JOIN country_custom USING (country_id) "
      . " WHERE "
      . " alpha2 = "
      . $dbo->DBQuote($searchCriteria) . " OR "
      . " alpha3 = "
      . $dbo->DBQuote($searchCriteria) . " OR "
      . " numeric3 = "
      . $dbo->DBQuote($searchCriteria) . " OR "
      . " country.name = "
      . $dbo->DBQuote($searchCriteria) . " OR "
      . " country_custom.name = "
      . $dbo->DBQuote($searchCriteria) . " "
      . "GROUP BY country.country_id";

    my $collection = $class->SUPER::GetAll($sql);

    assert(
        $collection->totalSize <= 1,
        "Country code search collision, "
          . "more than one record returned for term '$searchCriteria'"
          . "(match count: "
          . $collection->totalSize . ")"
    );

    return $collection->next();
}

sub SearchByCustom {
    my $class = shift;
    my $searchCriteria = shift || return;

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

    my $sql =
        "SELECT country.* FROM "
      . kTable . " "
      . " LEFT JOIN country_custom USING (country_id) "
      . " WHERE "
      . " country_custom.name = "
      . $dbo->DBQuote($searchCriteria) . " "
      . "GROUP BY country.country_id";

    my $collection = $class->SUPER::GetAll($sql);

    assert( $collection->totalSize == 1, "Country code search collision, more than one record returned for term '$searchCriteria'" );

    return $collection->next();
}

1;