<?php

/*
 * This file is part of Chevereto.
 *
 * (c) Rodolfo Berrios <rodolfo@chevereto.com>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace Chevereto\Legacy\Classes;

use DateTime;
use Exception;
use LogicException;
use OverflowException;
use function Chevere\Message\message;
use function Chevereto\Legacy\G\datetimegmt;
use function Chevereto\Vars\env;

class Stat
{
    public static function getTotals(): array
    {
        $columns = implode(', ', [
            'stat_users',
            'stat_images',
            'stat_albums',
            'stat_tags',
            'stat_cron_runs',
            'stat_cron_time',
            'stat_image_views',
            'stat_album_views',
            'stat_image_likes',
            'stat_album_likes',
            'stat_disk_used',
        ]);
        $tableStats = DB::getTable('stats');
        $res = DB::queryFetchSingle(
            <<<SQL
            SELECT {$columns}
            FROM {$tableStats}
            WHERE stat_type = "total";

            SQL
        );
        if (is_array($res)) {
            $res = DB::formatRow($res, 'stat');
        } else {
            $res = [
                'users' => 0,
                'images' => 0,
                'albums' => 0,
                'tags' => 0,
                'image_views' => 0,
                'album_views' => 0,
                'image_likes' => 0,
                'album_likes' => 0,
                'disk_used' => 0,
            ];
        }

        return $res;
    }

    public static function getDaily(): array
    {
        $tableStats = DB::getTable('stats');
        $res = DB::queryFetchAll(
            <<<SQL
            SELECT *
            FROM {$tableStats}
            WHERE stat_type = "date" AND stat_date_gmt IS NOT NULL
            ORDER BY stat_date_gmt DESC LIMIT 365;

            SQL
        );
        if (is_array($res)) {
            $res = DB::formatRows($res, 'stat');
        } else {
            $res = [];
        }

        return array_reverse($res);
    }

    public static function getByDateCumulative(): array
    {
        $res = static::getDaily();
        if ($res === []) {
            return [];
        }
        $return = [];
        $cumulative = [
            'users' => 0,
            'images' => 0,
            'albums' => 0,
            'tags' => 0,
            'image_views' => 0,
            'album_views' => 0,
            'image_likes' => 0,
            'disk_used' => 0,
        ];
        foreach ($res as $v) {
            foreach ($cumulative as $col => &$sum) {
                $sum += $v[$col] ?? 0;
                $v[$col . '_acc'] = $sum;
            }
            $return[$v['date_gmt']] = $v;
        }

        return $return;
    }

    public static function assertMax(string $env, int $add = 1): void
    {
        $envToStat = [
            'CHEVERETO_MAX_FILES' => 'images',
            'CHEVERETO_MAX_ALBUMS' => 'albums',
            'CHEVERETO_MAX_USERS' => 'users',
            'CHEVERETO_MAX_TAGS' => 'tags',
        ];
        if (! array_key_exists($env, $envToStat)) {
            throw new LogicException(
                message('Invalid stat type: `%s`', s: $env),
                600
            );
        }
        $maxLimit = (int) (env()[$env] ?? 0);
        if ($maxLimit === 0) {
            return;
        }
        $count = self::getTotals()[$envToStat[$env]] ?? 0;
        if (($count + $add) > $maxLimit) {
            throw new OverflowException(
                message(
                    'Maximum %t% reached (limit %s%).',
                    t: $env,
                    s: strval($maxLimit),
                ),
                999
            );
        }
    }

    public static function rebuildTotals(): void
    {
        $query = <<<SQL
        TRUNCATE TABLE `%table_prefix%stats`;

        INSERT INTO `%table_prefix%stats` (stat_id, stat_date_gmt, stat_type)
        VALUES ("1", NULL, "total")
        ON DUPLICATE KEY UPDATE stat_type=stat_type;

        UPDATE `%table_prefix%stats` SET
            stat_images = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%images`),
            stat_albums = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%albums`),
            stat_users = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%users`),
            stat_tags = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%tags`),
            stat_image_views = (SELECT IFNULL(SUM(image_views),0) FROM `%table_prefix%images`),
            stat_disk_used = (SELECT IFNULL(SUM(image_size) + SUM(image_thumb_size) + SUM(image_medium_size),0) FROM `%table_prefix%images`)
            WHERE stat_type = "total";

        INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_images, stat_image_views, stat_disk_used)
            SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_images, sb.stat_image_views, sb.stat_disk_used
            FROM (
                SELECT "date" AS stat_type, DATE(image_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_images, SUM(image_views) AS stat_image_views, SUM(image_size + image_thumb_size + image_medium_size) AS stat_disk_used
                FROM `%table_prefix%images`
                GROUP BY DATE(image_date_gmt)
            ) AS sb
            ON DUPLICATE KEY UPDATE stat_images = sb.stat_images;

        INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_users)
            SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_users
            FROM (SELECT "date" AS stat_type, DATE(user_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_users FROM `%table_prefix%users` GROUP BY DATE(user_date_gmt)) AS sb
            ON DUPLICATE KEY UPDATE stat_users = sb.stat_users;

        INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_albums)
            SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_albums
            FROM (SELECT "date" AS stat_type, DATE(album_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_albums FROM `%table_prefix%albums` GROUP BY DATE(album_date_gmt)) AS sb
            ON DUPLICATE KEY UPDATE stat_albums = sb.stat_albums;

        INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_tags)
            SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_tags
            FROM (SELECT "date" AS stat_type, DATE(tag_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_tags FROM `%table_prefix%tags` GROUP BY DATE(tag_date_gmt)) AS sb
            ON DUPLICATE KEY UPDATE stat_tags = sb.stat_tags;

        UPDATE `%table_prefix%users`
        SET user_content_views = COALESCE(
            (SELECT SUM(image_views)  FROM `%table_prefix%images` WHERE image_user_id = user_id GROUP BY user_id),
            "0"
        );

        SQL;
        $sql = strtr($query, [
            '%table_prefix%' => env()['CHEVERETO_DB_TABLE_PREFIX'],
        ]);
        $db = DB::getInstance();
        $db->query($sql);
        $db->exec();
    }

    public static function track(array $args = []): void
    {
        if (! in_array($args['action'], ['insert', 'update', 'delete'], true)) {
            throw new Exception(sprintf('Invalid stat action "%s" in ', $args['action']), 600);
        }
        $tables = DB::getTables();
        if (! array_key_exists($args['table'], $tables)) {
            throw new Exception(sprintf('Unknown table "%s"', $args['table']), 601);
        }
        if ($args['action'] === 'insert'
            && ! in_array($args['table'], ['albums', 'images', 'likes', 'users', 'tags'], true)
        ) {
            throw new Exception(sprintf('Table "%s" does not bind an stat procedure', $args['table']), 601);
        }
        if ($args['table'] === 'images'
            && in_array($args['action'], ['insert', 'delete'], true)
        ) {
            if (! isset($args['disk_sum'])) {
                $disk_sum_value = 0;
            } elseif (preg_match('/^([\+\-]{1})?\s*([\d]+)$/', (string) $args['disk_sum'], $matches)) {
                $disk_sum_value = $matches[2];
            } else {
                throw new Exception(sprintf('Invalid disk_sum value "%s"', $args['disk_sum']), 604);
            }
        }
        if (! isset($args['value'])) {
            $value = 1;
        } elseif (preg_match('/^([\+\-]{1})?\s*([\d]+)$/', (string) $args['value'], $matches)) {
            $value = $matches[2];
        } else {
            throw new Exception(sprintf('Invalid value "%s"', $args['value']), 602);
        }
        if (! isset($args['date_gmt'])) {
            switch ($args['action']) {
                case 'insert':
                case 'update':
                    $args['date_gmt'] = datetimegmt();

                    break;
                case 'delete':
                    throw new Exception('Missing date_gmt value', 605);
            }
        } else {
            $date = new DateTime($args['date_gmt']);
            $args['date_gmt'] = $date->format('Y-m-d');
        }
        $sql_tpl = '';
        switch ($args['action']) {
            case 'insert':
                switch ($args['table']) {
                    case 'images':
                        if (! isset($args['disk_sum'])) {
                            throw new Exception('Missing disk_sum value', 603);
                        }
                        $sql_tpl = <<<SQL
                        UPDATE `%table_stats`
                        SET stat_images = stat_images + %value, stat_disk_used = stat_disk_used + %disk_sum
                        WHERE stat_type = "total";

                        INSERT INTO `%table_stats` (stat_type, stat_date_gmt, stat_images, stat_disk_used)
                        VALUES ("date",DATE("%date_gmt"),"%value", "%disk_sum")
                        ON DUPLICATE KEY UPDATE stat_images = stat_images + %value, stat_disk_used = stat_disk_used + %disk_sum;

                        SQL;

                        break;
                    default: // albums, likes, users, tags
                        $sql_tpl = <<<SQL
                        UPDATE `%table_stats`
                        SET stat_%related_table = stat_%related_table + %value
                        WHERE stat_type = "total";

                        INSERT `%table_stats` (stat_type, stat_date_gmt, stat_%related_table)
                        VALUES ("date",DATE("%date_gmt"),"%value")
                        ON DUPLICATE KEY UPDATE stat_%related_table = stat_%related_table + %value;

                        SQL;

                        break;
                }

                break;

            case 'update':
                switch ($args['table']) {
                    case 'images':
                    case 'albums':
                        // Track (image | album | user) views
                        $sql_tpl = <<<SQL
                        UPDATE `%table_stats`
                        SET stat_%aux_views = stat_%aux_views + %value
                        WHERE stat_type = "total";

                        INSERT INTO `%table_stats` (stat_type, stat_date_gmt, stat_%aux_views)
                        VALUES ("date",DATE("%date_gmt"),"%value")
                        ON DUPLICATE KEY UPDATE stat_%aux_views = stat_%aux_views + %value;

                        SQL;
                        if (isset($args['user_id'])) {
                            $sql_tpl .= <<<SQL
                            UPDATE `%table_users` SET user_content_views = user_content_views + %value WHERE user_id = %user_id;

                            SQL;
                        }
                        $sql_tpl = strtr($sql_tpl, [
                            '%aux' => DB::getFieldPrefix($args['table']),
                        ]);

                        break;
                }

                break;

            case 'delete':
                switch ($args['table']) {
                    case 'images':
                        $sql_tpl = <<<SQL
                        UPDATE `%table_stats`
                        SET stat_images = GREATEST(GREATEST(0, stat_images) - %value, 0)
                        WHERE stat_type = "total";

                        UPDATE `%table_stats`
                        SET stat_images = GREATEST(GREATEST(0, stat_images) - %value, 0)
                        WHERE stat_type = "date"
                            AND stat_date_gmt = DATE("%date_gmt");

                        UPDATE `%table_stats`
                        SET stat_image_likes = GREATEST(GREATEST(0, stat_image_likes) - %likes, 0)
                        WHERE stat_type = "total";

                        UPDATE `%table_stats`
                        SET stat_image_likes = GREATEST(GREATEST(0, stat_image_likes) - %likes, 0)
                        WHERE stat_type = "date"
                            AND stat_date_gmt = DATE("%date_gmt");

                        UPDATE `%table_stats`
                        SET stat_disk_used = GREATEST(GREATEST(0, stat_disk_used) - %disk_sum, 0)
                        WHERE stat_type = "total";

                        UPDATE `%table_stats`
                        SET stat_disk_used = GREATEST(GREATEST(0, stat_disk_used) - %disk_sum, 0)
                        WHERE stat_type = "date"
                            AND stat_date_gmt = DATE("%date_gmt");

                        SQL;

                        break;
                    default:  // albums, likes, users, tags
                        $sql_tpl = <<<SQL
                        UPDATE `%table_stats`
                        SET stat_%related_table = GREATEST(GREATEST(0, stat_%related_table) - %value, 0)
                        WHERE stat_type = "total";

                        UPDATE `%table_stats`
                        SET stat_%related_table = GREATEST(GREATEST(0, stat_%related_table) - %value, 0)
                        WHERE stat_type = "date"
                            AND stat_date_gmt = DATE("%date_gmt");

                        SQL;
                        if ($args['table'] === 'users') {
                            $sql_tpl .= <<<SQL
                            UPDATE IGNORE `%table_stats` AS S
                            INNER JOIN (
                                SELECT DATE(like_date_gmt) AS like_date_gmt, COUNT(*) AS cnt
                                FROM `%table_likes`
                                    WHERE like_user_id = %user_id
                                GROUP BY DATE(like_date_gmt)
                            ) AS L ON S.stat_date_gmt = L.like_date_gmt
                            SET S.stat_image_likes = GREATEST(GREATEST(0, S.stat_image_likes) - COALESCE(L.cnt, "0"), 0) WHERE stat_type = "date";

                            UPDATE IGNORE `%table_stats`
                            SET stat_image_likes = GREATEST(GREATEST(0, stat_image_likes) - COALESCE((SELECT COUNT(*) FROM `%table_likes` WHERE like_user_id = %user_id), "0"), 0)
                            WHERE stat_type = "total";

                            UPDATE IGNORE `%table_stats` AS S
                                INNER JOIN (
                                    SELECT DATE(album_date_gmt) AS album_date_gmt, COUNT(*) AS cnt
                                    FROM `%table_albums`
                                        WHERE album_user_id = %user_id
                                    GROUP BY DATE(album_date_gmt)
                                ) AS A ON S.stat_date_gmt = A.album_date_gmt
                            SET S.stat_albums = GREATEST(GREATEST(0, S.stat_albums) - COALESCE(A.cnt, "0"), 0) WHERE stat_type = "date";

                            UPDATE IGNORE `%table_stats`
                            SET stat_albums = GREATEST(GREATEST(0, stat_albums) - COALESCE((SELECT COUNT(*) FROM `%table_albums` WHERE album_user_id = %user_id), "0"), 0)
                            WHERE stat_type = "total";

                            SQL;
                        }

                        break;
                }

                break;
        }
        if ($sql_tpl === '') {
            throw new LogicException();
        }
        $related_table = (
            isset($args['content_type'])
            ? ($args['content_type'] . '_')
            : ''
        ) . $args['table'];
        $sql = strtr($sql_tpl, [
            '%table_stats' => $tables['stats'],
            '%table_users' => $tables['users'],
            '%table_tags' => $tables['tags'],
            '%table_likes' => $tables['likes'],
            '%table_albums' => $tables['albums'],
            '%related_table' => $related_table,
            '%value' => $value,
            '%date_gmt' => $args['date_gmt'],
            '%user_id' => $args['user_id'] ?? 0,
            '%disk_sum' => $disk_sum_value ?? 0,
            '%likes' => $args['likes'] ?? 0,
        ]);
        DB::queryExecute($sql);
    }

    public static function getStatQuery(?string $tablePrefix = null): string
    {
        return static::buildStatQuery(
            $tablePrefix ?? env()['CHEVERETO_DB_TABLE_PREFIX'],
            false
        );
    }

    public static function getStatJsonQuery(?string $tablePrefix = null): string
    {
        return static::buildStatQuery(
            $tablePrefix ?? env()['CHEVERETO_DB_TABLE_PREFIX'],
            true
        );
    }

    private static function buildStatQuery(string $tablePrefix, bool $asJsonColumn): string
    {
        $aliases = [
            'users' => 's.stat_users',
            'files' => 's.stat_images',
            'albums' => 's.stat_albums',
            'tags' => 's.stat_tags',
            'cron_time' => 's.stat_cron_time',
            'file_views' => 's.stat_image_views',
            'album_views' => 's.stat_album_views',
            'file_likes' => 's.stat_image_likes',
            'album_likes' => 's.stat_album_likes',
            'storage_used' => 's.stat_disk_used',
            'admins' => 'u.admins',
            'managers' => 'u.managers',
            'pages' => 'u.pages',
            'storages' => 'u.storages',
            'categories' => 'u.categories',
        ];
        $pairs = [];
        $replaceTpl = match ($asJsonColumn) {
            true => '"%alias", %column',
            false => '%column %alias',
        };
        foreach ($aliases as $alias => $column) {
            $pairs[] = strtr($replaceTpl, [
                '%alias' => $alias,
                '%column' => $column,
            ]);
        }
        $selectColumns = implode(',', $pairs);
        $select = match ($asJsonColumn) {
            true => <<<SQL
            SELECT JSON_OBJECT(
                {$selectColumns}
            ) AS stats
            SQL,
            false => <<<SQL
            SELECT {$selectColumns}
            SQL,
        };

        return <<<SQL
        {$select}
        FROM `{$tablePrefix}stats` s
        CROSS JOIN (
            SELECT
                SUM(CASE WHEN `user_is_admin` = 1 THEN 1 ELSE 0 END) AS admins,
                SUM(CASE WHEN `user_is_manager` = 1 THEN 1 ELSE 0 END) AS managers,
                (SELECT COUNT(*) FROM `{$tablePrefix}pages`) AS pages,
                (SELECT COUNT(*) FROM `{$tablePrefix}storages` WHERE storage_deleted_at IS NULL) AS storages,
                (SELECT COUNT(*) FROM `{$tablePrefix}categories`) AS categories
            FROM `{$tablePrefix}users`
        ) u
        WHERE s.stat_type = "total"
        LIMIT 1

        SQL;
    }
}