<?php
// Define the base file path
define ('BASE_PATH',  dirname (dirname (dirname (__FILE__) ) ) . '/');

require_once (BASE_PATH . 'bootstrap.php');

echo 'Starting to process stats: ' . date ('r') . "\n";

/* Start timer */
$time = microtime ();
$time = explode (' ', $time);
$time = $time[1] + $time[0];
$start = $time;

// Get all stores
$sql = "SELECT `s`.`shop_id`, `s`.`shopify_domain`, `s`.`iana_timezone`, SUBDATE(CONVERT_TZ(CONCAT(CURDATE(), ' 23:59:59'), 'UTC', `s`.`iana_timezone`), 1) AS `yesterday`
FROM `shop` `s`
INNER JOIN `stats` ON `stats`.`shop_id` = `s`.`shop_id`
WHERE `s`.`processing_stats` = 0
GROUP BY `s`.`shop_id`
ORDER BY `s`.`shop_id`";
foreach ($db1->query ($sql) as $data) {
    $shop_id = $data['shop_id'];
    $shopify_domain = $data['shopify_domain'];
    $shop_timezone = $data['iana_timezone'];
    $yesterday = $data['yesterday'];

    // echo 'Processing stats for ' . $shopify_domain . ' - ' . $shop_id . ' - ' . $shop_timezone . ' - ' . $yesterday . "\n";

    // Set processing flag
    $sql = "UPDATE `shop` SET `processing_stats` = 1 WHERE `shop_id` = ?";
    $stmt = $db1->prepare ($sql);
    $stmt->execute ([$shop_id]);
    $stmt = null;

    // process views
    $sql = "SELECT DATE(CONVERT_TZ(`created`, 'UTC', ?)) AS `date`, COUNT(*) AS `views`
    FROM `stats`
    WHERE `created` <= ?
    AND `shop_id` = ?
    AND `click` = 0
    AND `conversion` = 0
    GROUP BY DATE(CONVERT_TZ(`created`, 'UTC', ?))
    ORDER BY DATE(CONVERT_TZ(`created`, 'UTC', ?))";
    $stmt = $db1->prepare ($sql);
    $stmt->execute ([$shop_timezone, $yesterday, $shop_id, $shop_timezone, $shop_timezone]);
    while ($data2 = $stmt->fetch ()) {
        $date = $data2['date'];
        $views = $data2['views'];

        // echo 'Inserting views for ' . $date . ' . "\n";

        // add the daily view stats
        $sql = "INSERT INTO `stats_daily`
            (`shop_id`, `date`, `views`)
        VALUES
            (?, ?, ?)
        ON DUPLICATE KEY UPDATE
            `views` = `views` + ?";
        $stmt2 = $db1->prepare ($sql);
        $stmt2->execute ([$shop_id, $date, $views, $views]);
        $stmt2 = null;
    }
    $stmt = null;

    // process clicks
    $sql = "SELECT DATE(CONVERT_TZ(`created`, 'UTC', ?)) AS `date`, COUNT(*) AS `clicks`
    FROM `stats`
    WHERE `created` <= ?
    AND `shop_id` = ?
    AND `click` = 1
    AND `conversion` = 0
    GROUP BY DATE(CONVERT_TZ(`created`, 'UTC', ?))
    ORDER BY DATE(CONVERT_TZ(`created`, 'UTC', ?))";
    $stmt = $db1->prepare ($sql);
    $stmt->execute ([$shop_timezone, $yesterday, $shop_id, $shop_timezone, $shop_timezone]);
    while ($data2 = $stmt->fetch ()) {
        $date = $data2['date'];
        $clicks = $data2['clicks'];

        //echo 'Inserting clicks for ' . $date . "\n";

        // add the daily click stats
        $sql = "INSERT INTO `stats_daily`
            (`shop_id`, `date`, `clicks`)
        VALUES
            (?, ?, ?)
        ON DUPLICATE KEY UPDATE
            `clicks` = `clicks` + ?";
        $stmt2 = $db1->prepare ($sql);
        $stmt2->execute ([$shop_id, $date, $clicks, $clicks]);
        $stmt2 = null;
    }
    $stmt = null;

    // process conversions
    $sql = "SELECT DATE(CONVERT_TZ(`created`, 'UTC', ?)) AS `date`, COUNT(*) AS `conversions`
    FROM `stats`
    WHERE `created` <= ?
    AND `shop_id` = ?
    AND `click` = 0
    AND `conversion` = 1
    GROUP BY DATE(CONVERT_TZ(`created`, 'UTC', ?))
    ORDER BY DATE(CONVERT_TZ(`created`, 'UTC', ?))";
    $stmt = $db1->prepare ($sql);
    $stmt->execute ([$shop_timezone, $yesterday, $shop_id, $shop_timezone, $shop_timezone]);
    while ($data2 = $stmt->fetch ()) {
        $date = $data2['date'];
        $conversions = $data2['conversions'];

        //echo 'Inserting conversions for ' . $date . ' . "\n";

        // add the daily conversion stats
        $sql = "INSERT INTO `stats_daily`
            (`shop_id`, `date`, `conversions`)
        VALUES
            (?, ?, ?)
        ON DUPLICATE KEY UPDATE
            `conversions` = `conversions` + ?";
        $stmt2 = $db1->prepare ($sql);
        $stmt2->execute ([$shop_id, $date, $conversions, $conversions]);
        $stmt2 = null;
    }
    $stmt = null;

    //echo 'Deleting stats older than ' . $yesterday . ' . "\n";

    // delete the stats before today
    $sql = "DELETE FROM `stats` WHERE `created` <= ? AND `shop_id` = ?";
    $stmt = $db1->prepare ($sql);
    $stmt->execute ([$yesterday, $shop_id]);
    $stmt = null;

    // Set processing complete flag
    $sql = "UPDATE `shop` SET `processing_stats` = 0 WHERE `shop_id` = ?";
    $stmt = $db1->prepare ($sql);
    $stmt->execute ([$shop_id]);
    $stmt = null;
}

$time = microtime ();
$time = explode (' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round (($finish - $start), 4);

// Delete all jobs older than one day
$sql = "DELETE FROM `job` WHERE `status` = 'complete' AND `created` < DATE_SUB(NOW(), INTERVAL 1 DAY)";
$db1->query ($sql);

echo 'Completed processing stats in ' . $total_time . ' seconds' . "\n";

// Close the DB connection
$db1 = null;
?>