#!/usr/bin/perl
# use strict;
# use warnings;
# use DBI;
# use DBI qw(:sql_types);
# use Getopt::Long;
# use Config::Simple;
# use Carp qw(croak);
# use DateTime;
# use Time::HiRes qw{gettimeofday};
# use POSIX qw{ceil floor strftime};
# use Log::Log4perl qw{:easy};
# use MIME::Lite;

# my $dbName = 'reivernet';
# my $month = -1;
# my $year = -1;
# my $dayofmonth = -1;
# my $debug;
# my $all;
# my $today;
# my $yesterday;
# my $thismonth;
# my $background;
# my $block_type;
# my $previous_hours;

# GetOptions(
#     "db|database=s" => \$dbName,
#     "day=i" => \$dayofmonth,
#     "month=i" => \$month,
#     "year=i" => \$year,
#     "d|debug" => \$debug,
#     "all" => \$all,
#     "today" => \$today,
#     "yesterday" => \$yesterday,
#     "thismonth" => \$thismonth,
#     "background" => \$background,
#     "block-type=s" => \$block_type,
#     "hours=i" => \$previous_hours,
# );

if (defined($background) and $background) {
    use Proc::Daemon;
    Proc::Daemon::Init;
}

use Proc::PID::File;
croak("Already running!") if Proc::PID::File->running();

my $layout = Log::Log4perl::Layout::PatternLayout->new('%d [%p] %m%n');

my $file_appender = Log::Log4perl::Appender->new(
    'Log::Dispatch::File',
    filename => '/var/log/reivernet/crunch_postpaid_stats.log',
    mode     => 'append',
);
$file_appender->layout($layout);

# my $logger = get_logger('crunch_postpaid_stats');
# if (!$logger) {
#     print "ERROR: Can't get logger\n";
#     exit;
# }
# $logger->add_appender($file_appender);
# $logger->level($INFO);

# if ($debug) {
#     $logger->level($DEBUG);
#     my $screen_appender =
#     Log::Log4perl::Appender->new("Log::Dispatch::Screen", stderr => "0" );
#     $screen_appender->layout($layout);
#     $logger->add_appender($screen_appender);
# }

# my $localtz = DateTime::TimeZone->new( name => 'local' );

# my %config;
# my $config_file = '/etc/reivernet.conf';
# Config::Simple->import_from( $config_file, \%config );

# # Database Parameters
# if (!defined($dbName)) {
#     $dbName = $config{'dbName'};
#     if ( (!defined($dbName)) or ($dbName eq '') ) { $dbName = 'reivernet'; }
# }
# my $dbUsername = $config{'dbuser'};
# if ( (!defined($dbUsername)) or ($dbUsername eq '') ) { $dbUsername = 'root'; }
# my $dbPassword = $config{'dbpassword'};
# if ( (!defined($dbPassword)) or ($dbPassword eq '') ) { $dbPassword = 'hmm'; }

# # Connect to the database
# our $dbh = DBI->connect( 'dbi:mysql:' . $dbName, $dbUsername, $dbPassword,
#     { AutoCommit => 1, 'mysql_auto_reconnect' => 1, RaiseError => 0 } )
#     or croak "ERROR: Database connection not made: $DBI::errstr";

# my $start_dt;
# my $finish_dt;
# my $now_dt = DateTime->now(
#     time_zone => $localtz
# );



if (defined($all) and $all == 1) {
    my $sql_firstLast = $dbh->prepare('SELECT DATE(MIN(calculation_time)), DATE(MAX(calculation_time)) FROM BillingSession')
        or $logger->critical("Unable to execute min and max times from pool_readings: $DBI::errstr");

    die if (!$sql_firstLast);
    $sql_firstLast->execute
        or $logger->critical("Unable to execute min and max times from pool_readings: $DBI::errstr");

    die if (!$sql_firstLast);
    if ($sql_firstLast->rows == 0) {
        print "There are no pool_readings available\n";
        exit 0;
    }






    my ($min_date,$max_date) = $sql_firstLast->fetchrow_array;
    my ($min_year,$min_month,$min_day) = split /-/x, $min_date;
    my ($max_year,$max_month,$max_day) = split /-/x, $max_date;




    $start_dt = DateTime->new(
        year => $min_year,
        month => $min_month,
        day => $min_day,
        time_zone => $localtz
    );
    $finish_dt = DateTime->new(
        year => $max_year,
        month => $max_month,
        day => $max_day,
        time_zone => $localtz
    );
}
elsif ($dayofmonth > 0 and $month >= 0 and $year > 0) {
    $start_dt = DateTime->new(
        year => $year,
        month => ($month == 0 ? 1 : $month),
        day => $dayofmonth,
        time_zone => $localtz
    );
    $finish_dt = $start_dt->clone();
    $finish_dt->add(new DateTime::Duration('days' => 1));
}
elsif ($month >= 0 and $year > 0) {
    $start_dt = DateTime->new(
        year => $year,
        month => ($month == 0 ? 1 : $month),
        day => 1,
        time_zone => $localtz
    );
    $finish_dt = $start_dt->clone();
    if ($month == 0) {
        $finish_dt->add(new DateTime::Duration('years' => 1));
    }
    else {
        $finish_dt->add(new DateTime::Duration('months' => 1));
    }
}
elsif (defined($thismonth)) {
    my $today = DateTime->today(
        time_zone => $localtz
    );
    $start_dt = $today->clone();
    $start_dt->set(day => 1);
    $finish_dt = $today->clone();
}
elsif (defined($today)) {
    my $today = DateTime->today(
        time_zone => $localtz
    );
    $start_dt = $today->clone();
    $finish_dt = $today->clone()->add(new DateTime::Duration('days' => 1));
}
else {
    if (defined($previous_hours)) {
        $start_dt = $now_dt->clone()->subtract(new DateTime::Duration('hours' => $previous_hours));
        $finish_dt = $now_dt->clone();
    }
    else {
        my $today = DateTime->today(
            time_zone => $localtz
        );
        $start_dt = $today->clone()->subtract(new DateTime::Duration('days' => 1));
        $finish_dt = $today->clone();
    }
}

$start_dt->set( second => 0 );
$finish_dt->set( second => 0 );

if (!defined($previous_hours)) {
    $start_dt->set( hour => 0, minute => 0 );
    $finish_dt->set( hour => 0, minute => 0 );
}

my $total_time_start = gettimeofday();

my %billing_types;
my $sql_getDataBillingType = $dbh->prepare("SELECT id, name FROM BillingType")
    or croak("Unable to prepare billing type select: $DBI::errstr");
$sql_getDataBillingType->execute();
if ($sql_getDataBillingType->rows > 0) {
  while (my ($id, $name) = $sql_getDataBillingType->fetchrow_array()) {
    $billing_types{$name} = $id;
  }
}


my $sql_deleteStats = $dbh->prepare("DELETE FROM PostpaidStatistic WHERE date = ?")
    or croak("Unable to prepare postpaid statistic delete: $DBI::errstr");

my $sql_insertStats = $dbh->prepare("
    INSERT INTO PostpaidStatistic
        (date, room_name, room_type, pricing_plan, total_data_used, total_revenue)
    SELECT
        calc_date, room_name, IF(room_type = 'Guest room', 'guest_room', IF(room_type = 'Guest room 2', 'guest_room_2', 'other')) AS room_type, pricing_plan,
        SUM(total_data_used), SUM(total_revenue)
        FROM (
                SELECT DATE(bd.calculation_time) AS calc_date,
                       e.room_name AS room_name,
                       e.room_type AS room_type,
                       e.pricing_plan AS pricing_plan,
                       SUM(bd.units) as total_data_used,
                       SUM(bd.charge) AS total_revenue
                  FROM Enrolment e, BillingSession bd
                  WHERE e.id = bd.enrolment_id
                    AND e.room_type IN ('Guest room', 'Guest room 2', 'Other')
                    AND bd.billing_type_id = $billing_types{'data'}
                    AND bd.calculation_time BETWEEN ? AND ?
                    AND e.cc_trans_id IN ('0','')
                    AND e.description != 'Master'
                    AND (e.vip_code = '' OR e.vip_code IS NULL)
                  GROUP BY calc_date, room_name, room_type, pricing_plan
        UNION
                SELECT DATE(bt.calculation_time) AS calc_date,
                       e.room_name AS room_name,
                       e.room_type AS room_type,
                       e.pricing_plan AS pricing_plan,
                       0 as total_data_used,
                       SUM(bt.charge) AS total_revenue
                  FROM Enrolment e, BillingSession bt
                  WHERE e.id = bt.enrolment_id
                    AND e.room_type IN ('Guest room', 'Guest room 2', 'Other')
                    AND bt.billing_type_id = $billing_types{'time'}
                    AND bt.calculation_time BETWEEN ? AND ?
                    AND e.cc_trans_id IN ('0','')
                    AND e.description != 'Master'
                    AND (e.vip_code = '' OR e.vip_code IS NULL)
                  GROUP BY calc_date, room_name, room_type, pricing_plan
        ) AS inception_query
        GROUP BY calc_date, room_name, room_type, pricing_plan

    ")
    or croak("Unable to prepare insert postpaid statistic: $DBI::errstr");

my $sql_selectPlanPurchases = $dbh->prepare("
        SELECT IF(e.room_type = 'Guest room', 'guest_room', IF(e.room_type = 'Guest room 2', 'guest_room_2', 'other')) AS room_type,
               room_name, pricing_plan, COUNT(DISTINCT(e.id))
        FROM Enrolment e
        WHERE e.room_type IN ('Guest room', 'Guest room 2', 'Other')
          AND e.start_time BETWEEN ? AND ?
          AND e.cc_trans_id IN ('0','')
          AND e.description != 'Master'
          AND (e.vip_code = '' OR e.vip_code IS NULL)
        GROUP BY room_type, room_name, pricing_plan
    ")
    or croak("Unable to prepare select postpaid statistic: $DBI::errstr");

my $sql_updatePlanPurchases = $dbh->prepare("
    UPDATE PostpaidStatistic
    SET enrolment_count = ?
    WHERE date = ?
      AND room_type = ?
      AND room_name = ?
      AND pricing_plan = ?
    ")
    or croak("Unable to prepare update postpaid statistic: $DBI::errstr");

while ($start_dt < $finish_dt && $start_dt < $now_dt) {
    my $start_time = $start_dt->ymd('-').' '.$start_dt->hms(':');
    my $finish_time = $start_dt->clone()->add(new DateTime::Duration('days' => 1))->ymd('-').' '.$finish_dt->hms(':');
    if (defined($previous_hours)) { $finish_time = $finish_dt->ymd('-').' '.$finish_dt->hms(':'); }

    my $date_processing = $start_dt->ymd('-');
    $logger->info("Processing $start_time to $finish_time");

    my $day_timer_start = gettimeofday();


    $sql_deleteStats->execute($start_dt->ymd('-'))
        or croak("Unable to delete postpaid statistic between '$start_time' AND '$finish_time': $DBI::errstr");

    $logger->debug(".. .. deleted ".$sql_deleteStats->rows." rows");

    $sql_insertStats->execute($start_time, $finish_time, $start_time, $finish_time)
        or croak("Unable to insert postpaid statistic between '$start_time' AND '$finish_time': $DBI::errstr");

    $logger->debug(".. .. inserted ".$sql_insertStats->rows." rows");

    $sql_selectPlanPurchases->execute($start_time, $finish_time)
        or croak("Unable to execute select purchases between '$start_time' AND '$finish_time': $DBI::errstr");

    my $update_count = 0;
    my ($room_type, $room_name, $pricing_plan, $count);
    $sql_selectPlanPurchases->bind_columns(\$room_type, \$room_name, \$pricing_plan, \$count);
    while ($sql_selectPlanPurchases->fetch()) {
      $sql_updatePlanPurchases->execute($count, $start_dt->ymd('-'), $room_type, $room_name, $pricing_plan)
        or croak("Unable to update postpaid plan purchase statistic between '$start_time' AND '$finish_time': $DBI::errstr");
      $update_count++;
    }

    $logger->debug(".. .. updated $update_count rows");

    my $day_timer_end = gettimeofday();
    my $day_timer_diff = floor($day_timer_end - $day_timer_start);
    $logger->info(".. $day_timer_diff seconds to process $date_processing");

    $start_dt->add(new DateTime::Duration('days' => 1));
}
my $total_time_end = gettimeofday();
my $total_time_diff = floor($total_time_end - $total_time_start);
$logger->info("Done. $total_time_diff seconds in total");