<?php
namespace App\Models;

use App\Core\DB;

class StatsModel extends BaseModel
{
    public function hasColumn(string $table, string $column): bool
    {
        try {
            $stmt = $this->db->prepare('SHOW COLUMNS FROM `' . str_replace('`', '', $table) . '` LIKE ?');
            $stmt->execute([$column]);
            return (bool)$stmt->fetch();
        } catch (\Throwable $e) {
            return false;
        }
    }

    public function overview(): array
    {
        return $this->overviewByScope(null);
    }

    public function overviewForUser(int $userId): array
    {
        if ($userId <= 0) {
            return $this->overviewByScope(-1);
        }
        return $this->overviewByScope($userId);
    }

    private function overviewByScope(?int $registratorId): array
    {
        $now = new \DateTimeImmutable('now');
        $todayStart = $now->setTime(0, 0, 0)->format('Y-m-d H:i:s');

        $isScoped = $registratorId !== null;
        $usersTotalSql = 'SELECT COUNT(*) FROM users';
        $usersTodaySql = 'SELECT COUNT(*) FROM users WHERE created_at >= ?';
        $usersTotalParams = [];
        $usersTodayParams = [$todayStart];
        if ($isScoped) {
            $usersTotalSql .= ' WHERE uplink_id = ?';
            $usersTodaySql .= ' AND uplink_id = ?';
            $usersTotalParams[] = $registratorId;
            $usersTodayParams[] = $registratorId;
        }
        $usersTotal = (int)$this->scalar($usersTotalSql, $usersTotalParams);
        $newUsersToday = (int)$this->scalar($usersTodaySql, $usersTodayParams);

        $keyWhere = '';
        $keyParams = [];
        if ($isScoped) {
            $keyWhere = ' WHERE registrator_id = ?';
            $keyParams[] = $registratorId;
        }

        $keysTotal = (int)$this->scalar('SELECT COUNT(*) FROM keys_code' . $keyWhere, $keyParams);

        $keysActiveSql = "SELECT COUNT(*) FROM keys_code WHERE status = 'active'";
        $keysInactiveSql = "SELECT COUNT(*) FROM keys_code WHERE status = 'inactive'";
        $keysActivatedSql = "SELECT COUNT(*) FROM keys_code WHERE expired_at IS NOT NULL";
        $keysExpiredSql = "SELECT COUNT(*) FROM keys_code WHERE expired_at IS NOT NULL AND expired_at < NOW()";
        $keysCreatedTodaySql = "SELECT COUNT(*) FROM keys_code WHERE created_at >= ?";
        $keysActiveParams = [];
        $keysInactiveParams = [];
        $keysActivatedParams = [];
        $keysExpiredParams = [];
        $keysCreatedTodayParams = [$todayStart];
        if ($isScoped) {
            $keysActiveSql .= ' AND registrator_id = ?';
            $keysInactiveSql .= ' AND registrator_id = ?';
            $keysActivatedSql .= ' AND registrator_id = ?';
            $keysExpiredSql .= ' AND registrator_id = ?';
            $keysCreatedTodaySql .= ' AND registrator_id = ?';
            $keysActiveParams[] = $registratorId;
            $keysInactiveParams[] = $registratorId;
            $keysActivatedParams[] = $registratorId;
            $keysExpiredParams[] = $registratorId;
            $keysCreatedTodayParams[] = $registratorId;
        }
        $keysActive = (int)$this->scalar($keysActiveSql, $keysActiveParams);
        $keysInactive = (int)$this->scalar($keysInactiveSql, $keysInactiveParams);
        $keysActivated = (int)$this->scalar($keysActivatedSql, $keysActivatedParams);
        $keysExpired = (int)$this->scalar($keysExpiredSql, $keysExpiredParams);
        $keysCreatedToday = (int)$this->scalar($keysCreatedTodaySql, $keysCreatedTodayParams);

        $hasActivatedAt = $this->hasColumn('keys_code', 'activated_at');
        $todayActivated = 0;
        if ($hasActivatedAt) {
            $todayActivatedSql = "SELECT COUNT(*) FROM keys_code WHERE activated_at IS NOT NULL AND activated_at >= ?";
            $todayActivatedParams = [$todayStart];
            if ($isScoped) {
                $todayActivatedSql .= ' AND registrator_id = ?';
                $todayActivatedParams[] = $registratorId;
            }
            $todayActivated = (int)$this->scalar($todayActivatedSql, $todayActivatedParams);
        }

        $daily = [];
        if ($hasActivatedAt) {
            $dailySql = "SELECT DATE(activated_at) AS d, COUNT(*) AS c FROM keys_code WHERE activated_at IS NOT NULL AND activated_at >= (CURDATE() - INTERVAL 6 DAY)";
            $dailyParams = [];
            if ($isScoped) {
                $dailySql .= ' AND registrator_id = ?';
                $dailyParams[] = $registratorId;
            }
            $dailySql .= ' GROUP BY DATE(activated_at) ORDER BY d ASC';
            $stmt = $this->db->prepare($dailySql);
            foreach ($dailyParams as $idx => $val) {
                $stmt->bindValue($idx + 1, $val, \PDO::PARAM_INT);
            }
            $stmt->execute();
            $rows = $stmt->fetchAll();
            $map = [];
            foreach ($rows as $r) {
                $map[(string)$r['d']] = (int)$r['c'];
            }
            for ($i = 6; $i >= 0; $i--) {
                $d = (new \DateTimeImmutable('today'))->modify('-' . $i . ' day')->format('Y-m-d');
                $daily[] = ['date' => $d, 'count' => (int)($map[$d] ?? 0)];
            }
        }

        return [
            'users_total' => $usersTotal,
            'users_new_today' => $newUsersToday,
            'keys_total' => $keysTotal,
            'keys_created_today' => $keysCreatedToday,
            'keys_active' => $keysActive,
            'keys_inactive' => $keysInactive,
            'keys_activated_total' => $keysActivated,
            'keys_expired_total' => $keysExpired,
            'keys_activated_today' => $todayActivated,
            'has_activated_at' => $hasActivatedAt,
            'daily_activations' => $daily,
        ];
    }

    private function scalar(string $sql, array $params = [])
    {
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
}
