package Newslash::Model::Cowrapper;
# Cowrapper - Connection Wrapper for MySQL

use strict;
use warnings;
use utf8;
use feature ':5.10';

use Carp qw(croak);
use FileHandle;

use List::Util qw(any);
use DBI;
use Data::Dumper;
use DateTime;
use DateTime::Format::MySQL;
use JSON;

sub import {
    my $class = shift;
    return unless my $flag = shift;

    if ($flag eq '-base') {
        $flag = $class;
    } elsif ($flag eq '-strict') {
        $flag = undef;
    }

    if ($flag) {
        my $caller = caller;
        no strict 'refs';
        push @{"${caller}::ISA"}, $flag;
    }

    $_->import for qw(strict warings utf8);
    feature->import(':5.10');
}

sub options {
    my ($self, @rest) = @_;
    return $self->{options} if @rest == 0;

    my $hash = $self->{options} || {};
    my $node;
    for $node (@rest) {
        $node = $hash->{$node};
        return if !$node;
    }
    return $node;
}

sub new {
    my $class = shift;
    my $options = shift || {};
    my $params = {@_};

    bless {options => {%$options, @_},
           _error => "",
           _errorno => 0,
           _last_sql => "",
           _last_attr => [],
	  }, $class;
}

sub _last_sql {
    my ($self, $value) = @_;
    if (defined $value) {
        $self->{_last_sql} = $value;
    }
    return $self->{_last_sql};
}

sub _last_attr {
    my ($self, $value) = @_;
    if (defined $value) {
        $self->{_last_attr} = $value;
    }
    return $self->{_last_attr};
}

sub _last_query {
    my ($self, $sql, $attr) = @_;
    if (defined $sql) {
        $self->{_last_sql} = $sql;
        $self->{_last_attr} = $attr;
    }
    return ($self->{_last_sql}, $self->{_last_attr});
}

sub _dumper {
    my $self = shift;
    return Dumper @_;
}

sub _dump_last_query {
    my $self = shift;
    my $cls = ref($self);
    return "+---------\n>> LAST QUERY of $cls: \n" .
      $self->_last_sql . Dumper($self->_last_attr) .
      "+---------\n";
}
  
######### connect/disconnect functions

sub connect_db {
    my $self = shift;
    my $options = shift || {};

    if ($self->transaction_mode) {
        return $self->{_tr_dbh};
    }

    my $DB_HOST = $self->{options}->{Database}->{host};
    my $DB_NAME = $self->{options}->{Database}->{name};
    my $DB_USER = $self->{options}->{Database}->{user};
    my $DB_PASSWORD = $self->{options}->{Database}->{password};

    my $settings = 'mysql_read_default_group=libmysqlclient;mysql_read_default_file=/etc/mysql/my.cnf';
    my $attr = {
                mysql_enable_utf8 => 1,
                ShowErrorStatement => 1,
                Callbacks => { # hack to use utf8mb4. see http://d.hatena.ne.jp/hirose31/20141028/1414496347
                              connected => sub {
                                  shift->do('SET NAMES utf8mb4');
                                  return;
                              }
                             },
                %$options,
               };

    my $dbh = DBI->connect("DBI:mysql:$DB_NAME:$DB_HOST;$settings", $DB_USER, $DB_PASSWORD, $attr);

    $self->{_tr_dbh} = $dbh;
    return $dbh;

}

sub disconnect_db {
    my $self = shift;
    return if $self->transaction_mode;
    return if !$self->{_tr_dbh};

    return $self->{_tr_dbh}->disconnect;
}


########## Transaction related functions

sub transaction_mode {
    my $self = shift;
    return 0 if !defined $self->{_transactions};
    return ($self->{_transactions} > 0);
}

sub start_transaction {
    my $self = shift;
    my $options = shift || {};

    if (!defined $self->{_transactions}) {
        $self->{_transactions} = 0;
    }

    # already transaction mode
    if ($self->transaction_mode) {
        $self->{_transactions} += 1;
        return $self->{_tr_dbh};
    }

    # start new transaction
    $options->{AutoCommit} = 0;
    $self->connect_db($options);
    $self->{_transactions} = 1;


    return $self->{_tr_dbh};
}

# use external defined dbh
sub use_transaction {
    my $self = shift;
    my $dbh = shift;

    if ($self->transaction_mode) {
        die "already transaction started";
    }

    if ($dbh) {
        #$self->{_transactions} = 2;
        $self->{_tr_dbh} = $dbh;
        return $dbh;
    }
    return;
}

sub commit {
    my $self = shift;
    return if !$self->transaction_mode;

    if ($self->{_transactions} == 1) {
        $self->{_tr_dbh}->commit;
        $self->{_tr_dbh}->disconnect;

        delete $self->{_tr_dbh};
    }
    $self->{_transactions} -= 1;
}

sub rollback {
    my $self = shift;
    return if !$self->transaction_mode;

    #$self->set_error($self->{_tr_dbh}->errstr);

    $self->{_tr_dbh}->rollback;
    $self->{_tr_dbh}->disconnect;
    delete $self->{_tr_dbh};
    $self->{_transactions} = 0;
}

########## Utility functions

sub table_exists {
    my ($self, $table) = @_;

    my $dbh = $self->connect_db;
    my $DB_NAME = $self->{options}->{Database}->{name};

    my $sql = <<"EOSQL";
SELECT * FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = ?
    AND TABLE_NAME = ?
EOSQL

    my $sth = $dbh->prepare($sql);
    $sth->execute($DB_NAME, $table);
    my $rs = $sth->fetchall_arrayref({});
    $self->disconnect_db;
    if (@$rs == 0) {
        return;
    }
    return 1;
}

sub check_readonly {
    my $self = shift;
    if ($self->options->{readonly}) {
        $self->set_error("readonly mode");
        return 1;
    }
    return;
}

########## error handling
sub clear_error {
    my $self = shift;
    $self->{_error} = undef;
    $self->{_errorno} = undef;
}

sub set_error {
    my ($self, $error, $errorno) = @_;
    $self->{_error} = $error;
    $self->{_errorno} = $errorno if $errorno;
}
sub set_errorno {
    my ($self, $errorno) = @_;
    $self->{_errorno} = $errorno;
}

sub last_error {
    my ($self, $err) = @_;
    $self->{_error} = $err if defined $err;
    return $self->{_error};
}

sub last_errorno {
    my ($self, $errno) = @_;
    $self->{_errorno} = $errno if defined $errno;
    return $self->{_errorno};
}

########## Utility functions
sub calculate_time_range {
    my ($self, $params) = @_;

    my $date = $params->{date};
    my $year = $params->{year};
    my $month = $params->{month};
    my $day = $params->{day};

    my $years = $params->{years};
    my $months = $params->{months};
    my $days = $params->{days};
    my $weeks = $params->{weeks};
    my $hours = $params->{hours};
    my $minutes = $params->{minutes};

    my $until = $params->{until};
    my $since = $params->{since};

    my $offset_sec = $params->{offset_sec};
    my ($dt_since, $dt_until);

    my $range_mode = ($years || $months || $days || $weeks || $hours || $minutes);

    # check parameters
    if ($since) {
        if (ref($since) eq 'HASH') {
            $dt_since = $since;
        }
        elsif ($since =~ m/^\d{4}-\d{2}-\d{2}$/) {
            $dt_since = DateTime::Format::MySQL->parse_date($since);
        }
        else {
            $dt_since = DateTime::Format::MySQL->parse_datetime($since);
        }
    }
    if ($until) {
        if (ref($until) eq 'HASH') {
            $dt_until = $until;
        }
        elsif ($until =~ m/^\d{4}-\d{2}-\d{2}$/) {
            $dt_until = DateTime::Format::MySQL->parse_date($until);
        }
        else {
            $dt_until = DateTime::Format::MySQL->parse_datetime($until);
        }
    }
    if ($year) {
        $dt_since = DateTime->new(year => $year,
                                 month => $month || 1,
                                 day => $day || 1);
    }
    if ($date) {
        if ($date eq 'today') {
            $dt_since = DateTime->today;
        }
        else {
            $dt_since = DateTime::Format::MySQL->parse_date($date);
        }
        if (!$range_mode) {
            $days = 1;
            $range_mode = 1;
        }
    }

    # check time range
    if ($range_mode) {
        if ($dt_since) {
            $dt_until = $dt_since->clone;
            $dt_until->add("years" => $years) if $years;
            $dt_until->add("months" => $months) if $months;
            $dt_until->add("days" => $days) if $days;
            $dt_until->add("weeks" => $weeks) if $weeks;
            $dt_until->add("hours" => $hours) if $hours;
            $dt_until->add("minutes" => $minutes) if $minutes;
        }
        else {
            $dt_until = DateTime->now;
            $dt_since = $dt_until->clone;
            $dt_since->add("years" => -$years) if $years;
            $dt_since->add("months" => -$months) if $months;
            $dt_since->add("days" => -$days) if $days;
            $dt_since->add("weeks" => -$weeks) if $weeks;
            $dt_since->add("hours" => -$hours) if $hours;
            $dt_since->add("minutes" => -$minutes) if $minutes;
        }
    }
    elsif ($year) {
        my $term = "year";
        $term = "month" if $month;
        $term = "day" if $day;

        $dt_until = $dt_since->clone;
        $dt_until->add("${term}s" => 1);
    }

    if ($offset_sec) {
        $dt_since->add(seconds => -$offset_sec) if $dt_since;
        $dt_until->add(seconds => -$offset_sec) if $dt_until;
    }

    my ($from, $to);
    $from = DateTime::Format::MySQL->format_datetime($dt_since) if $dt_since;
    $to = DateTime::Format::MySQL->format_datetime($dt_until) if $dt_until;

    return ($from, $to);
}


=head2 build_where_clause(unique_keys => $unique_keys,
                          keys => $keys,
                          timestamps => $timestamps,
                          where => $where_params,
                          params => \%params,)

build SQL WHERE clause.

=over 4

=item Parameters

=over 4

=item $unique_keys

STRING or ARRAYREF or HASHREF.
If STRING, $keys contains unique keys.
If ARRAREF, @$keys contains unique keys.
If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.

'unique key' is a column name defined with 'UNIQUE' or 'PRIMARY'.

If you want to use key which is not correspod to table's column,
use alias. When aliases given, the aliases are replaced to keys in SQL.

=back

=item $keys

STRING or ARRAYREF or HASHREF.
If STRING, $keys contains non-unique keys.
If ARRAREF, @$keys contains non-unique keys.
If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.

=back

=item \%params

HASHREF to query parameters

=item Return value

when list context, returns ($clause, \@values, $unique).
when scalar context, return hashref like:
 { clause => $clause, values => \@values, unique => $unique };

=back

=cut

sub build_where_clause {
    my $self = shift;
    my $args = {@_};

    my $uniques = {};
    my $keys = {};
    if ($args->{keys} || $args->{unique_keys}) {
        $uniques = $args->{unique_keys};
        $keys = $args->{keys};
    }
    else {
        $uniques = $self->unique_keys;
        $keys = $self->get_keys("non-unique");
    }
    my $timestamp = $args->{timestamp} || $self->{timestamp};

    my $params = $args->{params};
    my $where = $args->{where} || $params;

    my @clauses;
    my @values;

    # check and process "OR" parameter
    if (defined $where->{OR}) {
        my $sub_params = $where->{OR};
        croak "invalid OR parameter" if (ref($sub_params) ne "HASH");

        my ($sub_clause, $sub_values, $sub_unique) = $self->_parse_where_clause($uniques, $keys, $timestamp, $sub_params);
        my $or_clause = join(" OR ", @$sub_clause);
        if ($or_clause) {
            push @clauses, "( $or_clause )";
            push @values, @$sub_values;
        }
    }

    my ($sub_clauses, $sub_values, $unique) = $self->_parse_where_clause($uniques, $keys, $timestamp, $where);
    push @clauses, @$sub_clauses;
    push @values, @$sub_values;

    my $clause = "";
    if (@clauses != 0) {
        $clause = "WHERE " . join(" AND ", @clauses);
    }

    return wantarray ? ($clause, \@values, $unique)
      : { clause => $clause,
          values => \@values,
          unique => $unique, };
}


## subfunctions
sub _parse_where_clause {
    my ($self, $uniques, $keys, $timestamp, $params) = @_;

    my @clauses;
    my @values;
    my $unique = 0;

    # subfunctions
    my $decode_param = sub {
        my ($k, $v) = @_;

        # simply equal clause
        if (!ref($v)) {
            return ("$k = ?", $v);
        }

        # multiple items
        if (ref($v) eq 'ARRAY') {
            my $placeholder = join(", ", map { "?" } @$v);
            return ("$k IN ($placeholder)", $v);
        }

        # gt/lt/ge/le (>, <, >=, <=)
        if (ref($v) eq 'HASH') {
            return ("$k > ?", $v->{gt}) if defined $v->{gt};
            return ("$k < ?", $v->{lt}) if defined $v->{lt};
            return ("$k >= ?", $v->{ge}) if defined $v->{ge};
            return ("$k <= ?", $v->{le}) if defined $v->{le};
            return ("$k != ?", $v->{ne}) if defined $v->{ne};
        }

        die "invalid query parameter: $k, $v";
    };

    # define closure
    my $decode_and_set_params = sub {
        my ($k, $p, $is_unique) = @_;
        my ($c, $v) = $decode_param->($k, $p);
        push @clauses, $c if $c;
        if (defined $v) {
            if (ref($v) eq 'ARRAY') {
                push @values, @$v;
            }
            else {
                push @values, $v;
                $unique = 1 if $is_unique;
            }
        }
    };


    # reqularize keys to hashref
    my $ky = $self->_keys_to_hash($keys);
    if ($ky) {
        for my $k (keys(%$ky)) {
            if (defined $params->{$k}) {
                $decode_and_set_params->($ky->{$k}, $params->{$k});
            }
        }
    }
    # reqularize uniques to hashref
    my $u = $self->_keys_to_hash($uniques);
    if ($u) {
        for my $k (keys(%$u)) {
            if (defined $params->{$k}) {
                $decode_and_set_params->($u->{$k}, $params->{$k}, 1);
            }
        }
    }

    # year, month, day, years, months, days, date
    if ($timestamp) {
        my ($begin, $end) = $self->calculate_time_range($params);
        if ($begin) {
            push @clauses, "$timestamp >= ?";
            push @values, $begin;
        }
        if ($end) {
            push @clauses, "$timestamp < ?";
            push @values, $end;
        }
    }

    return (\@clauses, \@values, $unique);
}

# reqularize keys to hashref
sub _keys_to_hash {
    my ($self, $keys) = @_;
    return if !defined $keys;

    my $ky;
    if(ref($keys) eq 'HASH') {
        $ky = $keys;
    }
    elsif (ref($keys) eq 'ARRAY') {
        $ky = {};
        for my $k (@$keys) {
            $ky->{$k} = $k;
        }
    }
    elsif (!ref($keys)) {
        $ky = { $keys => $keys };
    }
    return $ky;
}

=head2 build_limit_clause(params => \@params)

build SQL's LIMIT clause.

=over 4

=item Parameters

=over 4

=item \@params

HASHREF to query parameters

=item Return value

when list context, returns ($clause, \@values).
when scalar context, return hashref like:
 { clause => $clause, values => \@values };

=back

=cut

sub build_limit_clause {
    my $self = shift;
    my $args = {@_};

    my $params = $args->{params};
    my $limit = $args->{default};
    my $offset = $args->{default_offset};

    $limit = $args->{default_limit} if defined $args->{default_limit};
    $limit = $params->{limit} if defined $params->{limit};
    $offset = $params->{offset} if defined $params->{offset};

    my @clauses;
    my @values;
    if (defined $limit) {
        push @clauses, "LIMIT ?";
        push @values, $limit;
    }
    if (defined $offset) {
        push @clauses, "OFFSET ?";
        push @values, $offset;
    }

    my $clause = join(" ", @clauses);

    return wantarray ? ($clause, \@values)
      : { clause => $clause, values => \@values };
}

=head2 build_order_by_clause(keys => $keys, params => \@params)

build SQL ORDER BY clause.

=over 4

=item Parameters

=over 4

=item $keys

ARRAYREF or HASHREF.
If ARRAREF, @$keys contains selectSable columns.
If HASHREF, %$keys is { key1 => alias1, key2 => alias2, ... }.

=item \@params

HASHREF to query parameters

=item Return value

when list context, returns ($clause, \@values).
when scalar context, return hashref like:
 { clause => $clause, values => \@values };

=back

=cut

sub build_order_by_clause {
    my $self = shift;
    my $args = {@_};

    my $unique_keys = $args->{unique_keys} || $self->unique_keys;
    my $keys = $args->{keys} || $self->get_keys("all");

    my $params = $args->{params};
    my $order_by = $params->{order_by};

    if (!$keys || !$params || !$order_by) {
        return wantarray ? ("", [])
          : { clause => "", values => [] };
    }

    my @clauses;
    my @values;

    my $k_names;
    my $use_alias = 0;
    # convert $keys to hash style
    if (ref($keys) eq 'HASH') {
        $k_names = [keys %$keys];
        $use_alias = 1;
    }
    if (ref($keys) eq 'ARRAY') {
        $k_names = $keys;
    }
    elsif (!ref($keys)) {
        $k_names = [$keys];
    }

    # "ORDER BY" clause has order, so
    # use list

    # if hash style, convert to array
    my $orders = [];
    my $cols = [];
    if (ref($order_by) eq "HASH") {
        for my $k (keys %$order_by) {
            push @$cols, $k;
            push @$orders, $order_by->{$k};
        }
    }
    elsif (!ref($order_by) && defined($order_by)) {
            push @$cols, $order_by;
            push @$orders, "ASC";
    }
    elsif (ref($order_by) eq "ARRAY") {
        my $is_key = 1;
        # check hashed parameter
        for my $item (@$order_by) {
            if (ref($item) eq "HASH") {
                for my $k (keys %$item) {
                    push @$cols, $k;
                    push @$orders, $item->{$k};
                }
            }
            else {
                if ($is_key) {
                    push @$cols, $item;
                    $is_key = 0;
                }
                else {
                    push @$orders, $item;
                    $is_key = 1;
                }
            }
        }
        # fallback for compatibility
        if ($is_key == 0) {
            push @$orders, "ASC";
        }
    }

    # when $order_by is not ARRAY or SCALAR or HASH,
    # this block is passed.
    if (@$cols) {
        for my $k (@$cols) {
            next if !any {$_ eq $k} @$k_names;

            my $order = shift @$orders;
            if ($use_alias && $keys->{$k}) {
                $k = $keys->{$k};
            }
            push @clauses, "$k $order" if $k;
        }
    }

    my $clause = "";
    if (@clauses) {
        $clause = "ORDER BY " . join(", ", @clauses);
    }

    return wantarray ? ($clause, \@values)
      : { clause => $clause, values => \@values };
}

########## Insert method

sub generic_insert {
    my $self = shift;
    return if $self->check_readonly;
    my $args = {@_};

    my $table = $args->{table} || $self->primary_table;
    if (!$table) {
        $self->set_error("table not given", -1);
        return;
    }

    my $keys;
    if ($args->{keys}) {
        $keys = $self->_build_keys($args->{keys});
    }
    else {
        $keys = $self->get_keys("all");
    }

    my $params = $args->{params};
    if (!$params) {
        $self->set_error("params not given", -1);
        return;
    };

    my @cols;
    my @values;
    my @placeholders;

    # extract key and values
    for my $k (keys %$keys) {
        next if !defined $params->{$k};
        if (!ref($params->{$k})) {
            push @cols, $keys->{$k};
            push @values, $params->{$k};
            push @placeholders, "?";
        }
        elsif(ref($params->{$k}) eq "HASH") {
            for my $subkey (keys %{$params->{$k}}) {
                if (lc($subkey) eq "function") {
                    push @cols, $keys->{$k};
                    push @placeholders, $params->{$k}->{$subkey};
                }
            }
        }
    }

    if (!@cols || !@values) {
        $self->set_error("no valid values", -1);
        return;
    }

    my $cols_clause = join(", ", @cols);
    my $values_clause = join(", ", @placeholders);


    my $sql = "INSERT INTO $table ($cols_clause) VALUES ($values_clause)";
    $self->_last_query($sql, \@values);

    #warn $sql;
    #warn Dumper @values;

    my $dbh = $self->connect_db;
    my $rs = $dbh->do($sql, undef, @values);
    if ($rs) {
        $rs = $dbh->last_insert_id(undef, undef, undef, undef);
    }
    $self->disconnect_db;
    return $rs;

}


########## Select method

=head2 generic_select(table => $table, uniques => $uniques, keys => $keys, params => $params)

build SQL's ORDER BY clause.

=over 4

=item Parameters

=over 4

=item $table

table name

=item $uniques

ARRAYREF to unique keys. 'unique key' is a column name
defined with 'UNIQUE' or 'PRIMARY'.

=back

=item $keys

ARRAYREF to acceptable keys (column names)

=back

=item $params

HASHREF to query parameters

=item Return value

when list context, returns ($clause, \@values).
when scalar context, return hashref like:
 { clause => $clause, values => \@values };

=back

=cut

sub generic_select {
    my $self = shift;
    my $args = {@_};
    my $table = $args->{table} || $self->primary_table;
    if (!$table) {
        $self->set_error("table no given");
        return;
    }
    my $params = $args->{params} || {};

    if ($args->{uniques}) {
        warn "Cowrapper::generic_select(): 'uniques' parameter is deprecated. use 'unique_keys'.";
        $args->{unique_keys} ||= $args->{uniques};
    }
    my $uniques = $args->{unique_keys};
    my $keys = $args->{keys};
    my $timestamp = $args->{timestamp};

    my @arguments;
    my ($values, $orderby, $limit, $where, $unique_query);

    ($where, $values, $unique_query) = $self->build_where_clause(unique_keys => $uniques,
                                                                 keys => $keys,
                                                                 timestamp => $timestamp,
                                                                 params => $params);
    push @arguments, @$values if @$values;

    ($orderby, $values) = $self->build_order_by_clause(keys => $keys, params => $params);
    push @arguments, @$values if @$values;

    ($limit, $values) = $self->build_limit_clause(params => $params);
    push @arguments, @$values if @$values;

    my $dbh = $self->connect_db;
    my $generic_sql = <<"EOSQL";
SELECT * FROM $table
  $where
  $orderby
  $limit
EOSQL

    my $sql = $args->{sql} || $generic_sql;
    $self->_last_query($sql, \@arguments);

    my $sth = $dbh->prepare($sql);
    $sth->execute(@arguments);
    my $rs = $sth->fetchall_arrayref(+{});
    if (!defined $rs) {
        $self->set_error("select failed", $dbh->errstr, $dbh->err);
        $self->disconnect_db;
        return;
    }
    $self->disconnect_db;

    if ($unique_query) {
        $self->clear_error;
        return $rs->[0] if @$rs;
        return;
    }
    return $rs;
}

########## Count method

=head2 generic_count(
 table => $table,
 target => $target_column,
 timestamp => $timestamp_column,
 year => $year,
 month => $month,
 day => $day,
 offset_sec => $offset_sec,
 join => $join_clause,
 where => $where_clause)

count items

=over 4

=item Parameters

=over 4

=item $table

table name

=item $target_column

count target column

=item $timestamp_column

timestamp column

=item $year

target year

=item $month

target month (omissible)

=item $day

target day (omissible)

=item $whare_clause

additional WHERE clause (must not include 'WHERE' !; omissible)

=item $join_clause

additional JOIN clause (must include 'JOIN' !; omissible)

=item Return value

count of items

=back

=cut

sub generic_count {
    my $self = shift;
    my $params = {@_};

    my $table = $params->{table} || $self->primary_table;
    my $target = $params->{target};
    my $timestamp = $params->{timestamp} || $self->timestamp;

    return if (!$table || !$target || !$timestamp || !$params->{year});

    my $term = "day";
    $term = "month" if !$params->{day};
    $term = "year" if !$params->{month};

    my ($year, $month, $day) = ($params->{year}, $params->{month}, $params->{day});
    $year  = 1 if (!$year  || $year  !~ m/^[0-9]{4}$/);
    $month = 1 if (!$month || $month !~ m/^(1[0-2]|0?[0-9])$/);
    $day   = 1 if (!$day   || $day   !~ m/^(3[0-1]|[1-2][0-9]|0?[0-9])$/);

    my $offset = $params->{offset_sec} || 0;
    $offset = 0 if $offset !~ m/^[+-]?[0-9]+$/;

    my $dt = DateTime->new(year => $year,
                           month => $month,
                           day => $day);
    $dt->add(seconds => -$offset);
    my $dt_string = DateTime::Format::MySQL->format_datetime($dt);

    # create end of term datetime
    # we must consider timezone offset, so use relative day/month.
    # why use "DATE_ADD(?, INTERVAL 1 MONTH)" ? bacause, DATE_ADD function add simply 30 days...
    my $dt_end = DateTime->new(year => $year,
                               month => $month,
                               day => $day);
    if ($term eq "month") {
        $dt_end->add(months => 1);
    }
    elsif ($term eq "year") {
        $dt_end->add(years => 1);
    }
    $dt->add(seconds => -$offset);
    my $dt_end_string = DateTime::Format::MySQL->format_datetime($dt_end);

    # build SQL
    my $sql;
    my $where_clause = "";
    if ($params->{where}) {
        $where_clause = "$params->{where} AND \n";
    }
    my $join_clause = $params->{join} || "";


    my @attrs;
    if ($term eq "day") {
        # `stories` table not contain display/non-display flag,
        # so use firehose.
        $sql = <<"EOSQL";
SELECT COUNT($table.$target) AS count FROM $table
    $join_clause
  WHERE $where_clause
        $table.$timestamp >= ? 
    AND $table.$timestamp < DATE_ADD(?, INTERVAL 1 DAY)
EOSQL
        push @attrs, $dt_string, $dt_string;
    }
    elsif ($term eq "month") {
        $sql = <<"EOSQL";
SELECT TIMESTAMPDIFF(DAY, ?, $table.$timestamp) AS day,
       COUNT($table.$target) AS count
  FROM $table
    $join_clause
  WHERE $where_clause
        $table.$timestamp >= ?
    AND $table.$timestamp < ?
  GROUP BY TIMESTAMPDIFF(DAY, ?, $table.$timestamp)
  ORDER BY day ASC
EOSQL
        push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
    }
    elsif ($term eq "year") {
        $sql = <<"EOSQL";
SELECT TIMESTAMPDIFF(MONTH, ?, $table.$timestamp) AS month,
       COUNT($table.$target) AS count
  FROM $table
    $join_clause
  WHERE $where_clause
        $table.$timestamp >= ?
    AND $table.$timestamp < ?
  GROUP BY TIMESTAMPDIFF(MONTH, ?, $table.$timestamp)
  ORDER BY month ASC
EOSQL
        push @attrs, $dt_string, $dt_string, $dt_end_string, $dt_string;
    }
    my $dbh = $self->connect_db;
    my $sth = $dbh->prepare($sql);

    $self->_last_query($sql, \@attrs);
    $sth->execute(@attrs);
    my $rs = $sth->fetchall_arrayref({});

    if (!$rs) {
        $self->disconnect_db;
        return;
    }
    $self->disconnect_db;

    my $hash = {};
    my $key;
    if ($term eq "day") {
        return $rs->[0]->{count};
    }
    elsif ($term eq "month") {
        $key = "day";
    }
    elsif ($term eq "year") {
        $key = "month";
    }
    else {
        return;
    }

    for my $counts (@$rs) {
        # day / month is differential from base datetime, so add 1
        $hash->{$counts->{$key} + 1} = $counts->{count};
    }
    return $hash;
}

sub generic_count2 {
    my $self = shift;
    my $args = {@_};
    my $table = $args->{table} || $self->primary_table;
    if (!$table) {
        $self->set_error("table no given");
        return;
    }
    my $params = $args->{params} || {};

    if ($args->{uniques}) {
        warn "Cowrapper::generic_select(): 'uniques' parameter is deprecated. use 'unique_keys'.";
        $args->{unique_keys} ||= $args->{uniques};
    }
    my $uniques = $args->{unique_keys};
    my $keys = $args->{keys};
    my $timestamp = $args->{timestamp};

    my @arguments;
    my ($values, $orderby, $limit, $where, $unique_query);

    ($where, $values, $unique_query) = $self->build_where_clause(unique_keys => $uniques,
                                                                 keys => $keys,
                                                                 timestamp => $timestamp,
                                                                 params => $params);
    push @arguments, @$values if @$values;

    my $target_key = $self->primary_key || "";
    if ($target_key) {
        $target_key = "*.$target_key";
    }
    else {
        $target_key = "*";
    }

    my $dbh = $self->connect_db;
    my $generic_sql = <<"EOSQL";
SELECT COUNT($target_key) AS count FROM $table
  $where
EOSQL

    my $sql = $args->{sql} || $generic_sql;
    $self->_last_query($sql, \@arguments);

    my $sth = $dbh->prepare($sql);
    $sth->execute(@arguments);
    my $rs = $sth->fetchall_arrayref(+{});
    if (!defined $rs) {
        $self->set_error("select failed", $dbh->errstr, $dbh->err);
        $self->disconnect_db;
        return;
    }
    $self->disconnect_db;

    if ($unique_query) {
        $self->clear_error;
        return $rs->[0] if @$rs;
        return;
    }
    return $rs;
}



########## Update method

=head2 generic_update(table => $table,
                      updatable_keys => $updatables,
                      addable_keys => $addables,
                      condition_keys => $conditions,
                      params => $params)

execute UPDATE SQL command.

=over 4

=item Parameters

=over 4

=item $table

table name

=item $updatables

ARRAYREF or HASHREF to updatable keys. 'updatable key' is a updatable column name.

=item $addables

ARRAYREF or HASHREF to addable keys. 'addable key' is a addable column name.

=item $conditions

ARRAYREF or HASHREF to conditional keys. 'conditional key' is a column name which can use in WHERE clause.

=item $params

HASHREF to query parameters

=item Return value

when list context, returns ($clause, \@values).
when scalar context, return hashref like:
 { clause => $clause, values => \@values };

=back

=cut

sub generic_update {
    my $self = shift;
    return if $self->check_readonly;
    my $args = {@_};
    return if !%$args;

    my $table = $args->{table} || $self->primary_table;
    if (!$table) {
        $self->set_error("table given", -1);
        return;
    }

    my $updatables = $args->{updatable_keys} || $self->get_keys("updatable");
    my $addables = $args->{addable_keys} || $self->get_keys("addable");

    my $conditions = $args->{condition_keys};
    my $params = $args->{params} || {};
    my $where = $args->{where} || $params->{where};

    if ($where) {
        $conditions = $args->{condition_keys} || $self->get_keys("all");
    }
    else {
        $conditions = $args->{condition_keys} || $self->get_keys("primary");
    }

    if (!$conditions || !%$conditions) {
        $self->set_error("no_condition_keys", -1);
        return;
    }
    my $u_hash = $self->_keys_to_hash($updatables);
    #my $a_hash = $self->_keys_to_hash($addables);

    # first, create WHERE clause
    my ($where_clause, $where_values) = $self->build_where_clause(keys => $conditions,
                                                                  params => $params,
                                                                  where => $where);
    if (!$where_clause) {
        $self->set_error("no_where_clauses", -1);
        return;
    }

    # build SET clause
    my @set_clauses;
    my @values;

    for my $col (keys %$u_hash) {
        next if !defined $params->{$col};
        my $c = $u_hash->{$col};

        # if $params->{$col} is SCALAR, set to the value
        if (!ref($params->{$col})) {
            push @values, $params->{$col};
            push @set_clauses, "$c = ?";
        }
        # if $params->{$col} is HASH, do given operation
        elsif (ref($params->{$col}) eq 'HASH') {
            # key to lc
            my $p = {};
            for my $k (keys %{$params->{$col}}) {
                $p->{lc($k)} = $params->{$col}->{$k};
            }
            if ($p->{add}) {
                if (defined $p->{max} && defined $p->{min}) {
                    push @values, $p->{min};
                    push @values, $p->{max};
                    push @values, $p->{add};
                    push @set_clauses, "$c = GREATEST(?, LEAST(?, $c + ?))";
                }
                elsif (defined $p->{max}) {
                    push @values, $p->{min};
                    push @values, $p->{add};
                    push @set_clauses, "$c = GREATEST(?, $c + ?)";
                }
                elsif (defined $p->{min}) {
                    push @values, $p->{min};
                    push @values, $p->{add};
                    push @set_clauses, "$c = LEAST(?, $c + ?)";
                }
                else {
                    push @values, $p->{add};
                    push @set_clauses, "$c = $c + ?";
                }
            }
            if ($p->{function}) {
                push @set_clauses, "$c = ($p->{function})";
            }
        }
    }

    return 0 if !@set_clauses;
    my $set_clause = join(", ", @set_clauses);

    my $sql = "UPDATE $table SET $set_clause $where_clause";
    push @values, @$where_values;
    $self->_last_query($sql, \@values);

    #warn $sql;
    #warn Dumper @values;

    my $dbh = $self->connect_db;
    my $rs = $dbh->do($sql, undef, @values);
    $self->disconnect_db;
    return $rs;

}

########## count helper

sub build_interval_times {
    my $self = shift;
    my $attr = {@_};
    my $params = $attr->{params} || {};

    my $target = "day";
    return if !$params->{year};
    $target = "month" if !$params->{day};
    $target = "year" if !$params->{month};

    my ($year, $month, $day) = ($params->{year}, $params->{month}, $params->{day});
    $year = 1 if (!$year || $year !~ m/^[0-9]{4}$/);
    $month = 1 if (!$month || $month !~ m/^(1[0-2]|0?[0-9])$/);
    $day = 1 if (!$day || $day !~ m/^(3[0-1]|[1-2][0-9]|0?[0-9])$/);

    my $offset = $params->{offset_sec} || 0;
    $offset = 0 if $offset !~ m/^[+-]?[0-9]+$/;

    my $dt = DateTime->new(year => $year,
                             month => $month,
                             day => $day);
    $dt->add(seconds => -$offset);
    my $dt_string = DateTime::Format::MySQL->format_datetime($dt);

    # create end of term datetime
    # why use "DATE_ADD(?, INTERVAL 1 MONTH)" ? bacause, this function add simply 30 days...
    my $dt_end = DateTime->new(year => $year,
                               month => $month,
                               day => $day);
    if ($target eq "month") {
        $dt_end->add(months => 1);
    }
    elsif ($target eq "year") {
        $dt_end->add(years => 1);
    }
    $dt->add(seconds => -$offset);
    my $dt_end_string = DateTime::Format::MySQL->format_datetime($dt_end);

    return wantarray ? ($target, $dt_string, $dt_end_string)
      : { target => $target,
          start => $dt_string,
          end => $dt_end_string };
}


########## Delete method

=head2 generic_delete(table => $table, uniques => $uniques, keys => $keys, params => $params)

DELETE item from table

=over 4

=item Parameters

=over 4

=item $table

table name

=item $uniques

ARRAYREF to unique keys. 'unique key' is a column name
defined with 'UNIQUE' or 'PRIMARY'.

=back

=item $keys

ARRAYREF to acceptable keys (column names)

=back

=item $params

HASHREF to query parameters

=item Return value

when list context, returns ($clause, \@values).
when scalar context, return hashref like:
 { clause => $clause, values => \@values };

=back

=cut

sub generic_delete {
    my $self = shift;
    my $args = {@_};
    my $table = $args->{table} || $self->primary_table;
    if (!$table) {
        $self->set_error("table no given");
        return;
    }
    my $params = $args->{params} || {};

    if ($args->{uniques}) {
        warn "Cowrapper::generic_select(): 'uniques' parameter is deprecated. use 'unique_keys'.";
        $args->{unique_keys} ||= $args->{uniques};
    }
    my $uniques = $args->{unique_keys};
    my $keys = $args->{keys};
    my $timestamp = $args->{timestamp};

    my @arguments;
    my ($values, $orderby, $limit, $where, $unique_query);

    ($where, $values, $unique_query) = $self->build_where_clause(unique_keys => $uniques,
                                                                 keys => $keys,
                                                                 timestamp => $timestamp,
                                                                 params => $params);
    push @arguments, @$values if @$values;

    my $dbh = $self->connect_db;
    my $generic_sql = <<"EOSQL";
DELETE FROM $table
  $where
EOSQL

    my $sql = $args->{sql} || $generic_sql;
    $self->_last_query($sql, \@arguments);

    my $rs = $dbh->do($sql, undef, @arguments);
    if (!defined $rs) {
        $self->set_error("delete_failed", $dbh->errstr, $dbh->err);
        $self->disconnect_db;
        return;
    }
    $self->disconnect_db;

    return $rs;
}

######### data export / import functions

=head2 export_json()

export as json data.

$obj->export_json(file => "foobar.json",
                  table => "foo",
                  exclude => [qw(foo bar)],
                  sort_key => [qw(hoge moge)],
                 );

=over 4

=item Parameters

=over 4

=back

=back

=cut

sub export_json {
    my $self = shift;
    my $params = {@_};

    my $table = $params->{table};
    return if !$table;

    my $keys = $params->{sort_key} || [];
    if (!ref($keys)) {
        $keys = [$keys];
    }

    my $query_params = {};
    if ($params->{sort_key}) {
        $query_params->{order_by} = $keys;
    }

    my $datas = $self->generic_select(table => $table, keys => $keys, params => $query_params);
    return if !$datas;

    my $exclude = $params->{exclude} || [];
    if (!ref($exclude)) {
        $exclude = [$exclude];
    }

    for my $data (@$datas) {
        for my $k (@$exclude) {
            delete $data->{$k};
        }
    }

    #warn Dumper($datas);

    my $j = JSON->new;
    $j->indent(1);
    $j->space_after(1);
    $j->canonical(1);
    if ($params->{file}) {
        my $bin_data = $j->utf8->encode($datas);
        my $fh = FileHandle->new($params->{file}, "w");
        if (!defined $fh) {
            $self->set_error($!);
            return;
        }
        $fh->print($bin_data);
        $fh->close;
        return 1;
    }
    return JSON::to_json($datas);
}

=head2 import_json()

export as json data.

$obj->export_json(file => "foobar.json",
                  table => "foo",
                  exclude => [qw(foo bar)],
                  unique_key => "id",
                 );

$obj->export_json(json => '{ "foo": "bar", "hoge": 1 }',
                  table => "foo",
                  exclude => [qw(foo bar)],
                  unique_key => "id",
                 );

=over 4

=item Parameters

=over 4

=back

=back

=cut

sub import_json {
    my $self = shift;
    my $params = {@_};

    my $table = $params->{table};
    return if !$table;

    if ($params->{json} && $params->{file}) {
        return;
    }

    my $datas;
    if ($params->{file}) {
        my $fh = FileHandle->new($params->{file}, "r");
        if (!defined $fh) {
            $self->set_error($!);
            return;
        }
        my $json = do { local $/; <$fh> };
        $fh->close;
        $datas = JSON::decode_json($json);
    }
    if ($params->{json}) {
        $datas = JSON::from_json($params->{json});
    }

    return if !$datas;
    return if ref($datas) ne "ARRAY";

    my $primary_key = $params->{unique_key};
    my $exclude = $params->{exclude} || [];
    if (!ref($exclude)) {
        $exclude = [$exclude];
    }
    # insert data
    my $dbh = $self->start_transaction;
    my $updated = 0;
    for my $data (@$datas) {
        for my $k (@$exclude) {
            delete $data->{$k};
        }
        my @cols;
        my @plhs;
        my @vals;
        my @updates;
        my @update_vals;
        for my $k (keys(%$data)) {
            #my $quoted_k = '`' . $k . '`';
            my $quoted_k = $dbh->quote_identifier($k);
            push @cols, $quoted_k;
            push @plhs, '?';
            push @vals, $data->{$k};
            if ($k ne $primary_key) {
                push @updates, "$quoted_k = ?";
                push @update_vals, $data->{$k};
            }
        }
        my $cols_clause = join(", ", @cols);
        my $placeholders = join(", ", @plhs);
        my $update_clauses = join(", ", @updates);

        my $sql = <<"EOSQL";
INSERT INTO $table
    ($cols_clause)
  VALUES
    ($placeholders)
  ON DUPLICATE KEY UPDATE
    $update_clauses
EOSQL
        push @vals, @update_vals;
        $self->_last_query($sql, \@vals);
        #warn $sql;
        my $rs = $dbh->do($sql, undef, @vals);
        if (!defined $rs) {
            $self->rollback;
            return;
        }
        $updated += $rs;
    }
    $self->commit;
    return $updated;
}

########## virtual method for O/R mapping function
sub key_definition { return {}; }

sub _build_keys {
    my ($self, $target) = @_;
    return {} if !$target;
    return $target if ref($target) eq "HASH";
    if (!ref($target)) {
        $target = [$target];
    }

    my $def = $self->key_definition || {};
    my $basename = "";
    if ($def->{basename}) {
        $basename = "$def->{basename}.";
    }
    elsif ($def->{table}) {
        $basename = "$def->{table}.";
    }

    my $rs = {};
    for my $uk (@$target) {
        $rs->{$uk} = "$basename$uk";
        for my $k (keys %{$def->{aliases}}) {
            if ($uk eq $def->{aliases}->{$k}) {
                if ($uk =~ m/\S+\.\S+/) {
                    $rs->{$k} = "$uk";
                }
                else {
                    $rs->{$k} = "$basename$uk";
                }
            }
        }
    }

    return $rs;
}

sub primary_table {
    my $self = shift;
    my $def = $self->key_definition || {};
    return $def->{table};
}

sub primary_key {
    my $self = shift;
    my $def = $self->key_definition || {};
    return $self->_build_keys($def->{primary});
}

sub timestamp_key {
    my $self = shift;
    my $def = $self->key_definition || {};
    return $self->_build_keys($def->{timestamp});
}

sub datetime_keys {
    my $self = shift;
    my $def = $self->key_definition || {};
    return $self->_build_keys($def->{datetime});
}

sub other_keys {
    my $self = shift;
    my $def = $self->key_definition || {};
    return $self->_build_keys($def->{other});
}

sub unique_keys {
    my $self = shift;
    my $def = $self->key_definition || {};
    my $uniq = $self->_build_keys($def->{unique});
    my $primary = $self->_build_keys($def->{primary});
    return $self->_merge_keys($uniq, $primary);
}

sub addable_keys {
    my $self = shift;
    my $def = $self->key_definition || {};
    return $self->_build_keys($def->{addable});
}

sub get_keys {
    my $self = shift;
    my @keys;
    my $def = $self->key_definition || {};
    while (@_) {
        my $target = shift;
        if (defined $def->{$target}) {
            push @keys, @{_to_array($def->{$target})};
        }
        elsif ($target eq "all") {
            push @keys, $def->{primary} if $def->{primary};
            push @keys,
              @{_to_array($def->{unique})},
              @{_to_array($def->{datetime})},
              @{_to_array($def->{addable})},
              @{_to_array($def->{other})};
        }
        elsif ($target eq "non-unique") {
            push @keys,
              @{_to_array($def->{datetime})},
              @{_to_array($def->{addable})},
              @{_to_array($def->{other})};
        }
        elsif ($target eq "updatable") {
            push @keys,
              @{_to_array($def->{unique})},
              @{_to_array($def->{datetime})},
              @{_to_array($def->{addable})},
              @{_to_array($def->{other})};
        }
    }
    return $self->_build_keys(\@keys) if @keys;
    return;
}

sub _to_array {
    my $item = shift;
    return [] if !defined $item;
    return $item if (ref($item) eq 'ARRAY');
    return [$item] if !ref($item);
}

sub _merge_keys {
    my $self = shift;
    my $rs = {};

    while(@_) {
        my $keys = shift;
        next if !defined $keys;
        if (ref($keys) eq 'ARRAY') {
            for my $k (@$keys) {
                $rs->{$k} = $k;
            }
        }
        elsif (ref($keys) eq 'HASH') {
            for my $k (keys %$keys) {
                $rs->{$k} = $keys->{$k};
            }
        }
        elsif (!ref($keys)) {
            $rs->{$keys} = $keys;
        }
    }

    return $rs;
}

########## END OF FILE
1;
