<?php
namespace App\Models;

class KeyModel extends BaseModel
{
    private static bool $schemaEnsured = false;
    private ?bool $supportsBatchSchema = null;

    public function __construct()
    {
        parent::__construct();
        $this->ensureSchema();
    }

    public function create(array $data): int
    {
        if ($this->supportsBatchSchema()) {
            $stmt = $this->db->prepare(
                'INSERT INTO keys_code (game, batch_code, batch_name, user_key, duration_hours, max_devices, devices, status, registrator_id, expired_at, created_at, updated_at)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())'
            );
            $stmt->execute([
                $data['game'],
                $data['batch_code'] ?? null,
                $data['batch_name'] ?? null,
                $data['user_key'],
                $data['duration_hours'],
                $data['max_devices'],
                $data['devices'] ?? null,
                $data['status'] ?? 'active',
                $data['registrator_id'],
                $data['expired_at'] ?? null,
            ]);
        } else {
            $stmt = $this->db->prepare(
                'INSERT INTO keys_code (game, user_key, duration_hours, max_devices, devices, status, registrator_id, expired_at, created_at, updated_at)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())'
            );
            $stmt->execute([
                $data['game'],
                $data['user_key'],
                $data['duration_hours'],
                $data['max_devices'],
                $data['devices'] ?? null,
                $data['status'] ?? 'active',
                $data['registrator_id'],
                $data['expired_at'] ?? null,
            ]);
        }
        return (int)$this->db->lastInsertId();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM keys_code WHERE id = ? LIMIT 1');
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findByKeyAndGame(string $userKey, string $game): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM keys_code WHERE user_key = ? AND game = ? LIMIT 1');
        $stmt->execute([$userKey, $game]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function consumeLogin(string $userKey, string $game, string $serial, array $serialAliases = []): array
    {
        $serial = $this->normalizeDeviceSerial($serial);
        if ($serial === '') {
            return ['ok' => false, 'code' => 'SERIAL_REQUIRED'];
        }

        $aliasMap = [$serial => true];
        foreach ($serialAliases as $alias) {
            $alias = $this->normalizeDeviceSerial((string)$alias);
            if ($alias === '') {
                continue;
            }
            $aliasMap[$alias] = true;
        }
        $serialAliases = array_keys($aliasMap);

        $this->db->beginTransaction();
        try {
            $stmt = $this->db->prepare('SELECT * FROM keys_code WHERE user_key = ? AND game = ? LIMIT 1 FOR UPDATE');
            $stmt->execute([$userKey, $game]);
            $key = $stmt->fetch();
            if (!$key) {
                $this->db->rollBack();
                return ['ok' => false, 'code' => 'KEY_NOT_FOUND'];
            }

            if ((string)($key['status'] ?? '') !== 'active') {
                $this->db->rollBack();
                return ['ok' => false, 'code' => 'KEY_DISABLED'];
            }

            $now = new \DateTimeImmutable('now');
            $durationHours = max(0, (int)($key['duration_hours'] ?? 0));
            if (empty($key['expired_at'])) {
                $createdAtRaw = trim((string)($key['created_at'] ?? ''));
                if ($createdAtRaw !== '') {
                    try {
                        $createdAt = new \DateTimeImmutable($createdAtRaw);
                        $preActivatedExpire = $createdAt->modify('+' . $durationHours . ' hours');
                        if ($now > $preActivatedExpire) {
                            $this->db->rollBack();
                            return ['ok' => false, 'code' => 'KEY_EXPIRED'];
                        }
                    } catch (\Throwable $ignored) {
                    }
                }

                $expired = $now->modify('+' . $durationHours . ' hours');
                $key['expired_at'] = $expired->format('Y-m-d H:i:s');

                $updateExpire = $this->db->prepare('UPDATE keys_code SET expired_at = ?, activated_at = COALESCE(activated_at, NOW()), updated_at = NOW() WHERE id = ?');
                $updateExpire->execute([(string)$key['expired_at'], (int)$key['id']]);
            } else {
                $expired = new \DateTimeImmutable((string)$key['expired_at']);
                if ($now > $expired) {
                    $this->db->rollBack();
                    return ['ok' => false, 'code' => 'KEY_EXPIRED'];
                }
            }

            $devices = $this->normalizeDeviceList((string)($key['devices'] ?? ''));
            $deviceCount = count($devices);
            $matchedIndex = null;
            foreach ($devices as $index => $device) {
                if ($device === $serial || in_array($device, $serialAliases, true)) {
                    $matchedIndex = $index;
                    break;
                }
            }

            if ($matchedIndex === null) {
                if ($deviceCount >= (int)$key['max_devices']) {
                    $this->db->rollBack();
                    return ['ok' => false, 'code' => 'DEVICE_LIMIT'];
                }
                $devices[] = $serial;
            } elseif ($devices[$matchedIndex] !== $serial) {
                $devices[$matchedIndex] = $serial;
            }

            $devices = $this->normalizeDeviceList(implode(',', $devices));
            $normalizedDevices = implode(',', $devices);
            if ($normalizedDevices !== (string)($key['devices'] ?? '')) {
                $updateDevice = $this->db->prepare('UPDATE keys_code SET devices = ?, updated_at = NOW() WHERE id = ?');
                $updateDevice->execute([$normalizedDevices, (int)$key['id']]);
                $key['devices'] = $normalizedDevices;
            }

            $this->db->commit();
            return [
                'ok' => true,
                'key' => $key,
                'devices' => $devices,
                'server_time' => $now->getTimestamp(),
            ];
        } catch (\Throwable $e) {
            if ($this->db->inTransaction()) {
                $this->db->rollBack();
            }
            throw $e;
        }
    }

    public function findByUserKey(string $userKey): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM keys_code WHERE user_key = ? LIMIT 1');
        $stmt->execute([$userKey]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function listForUser(int $userId, bool $isAdmin, array $filters = []): array
    {
        $supportsBatchSchema = $this->supportsBatchSchema();
        $where = [];
        $params = [];

        if (!$isAdmin) {
            $where[] = 'k.registrator_id = ?';
            $params[] = $userId;
        }

        $game = trim((string)($filters['game'] ?? ''));
        if ($game !== '') {
            $where[] = 'k.game = ?';
            $params[] = $game;
        }

        $status = trim((string)($filters['status'] ?? ''));
        if (in_array($status, ['active', 'inactive'], true)) {
            $where[] = 'k.status = ?';
            $params[] = $status;
        }

        $batch = trim((string)($filters['batch'] ?? ''));
        if ($supportsBatchSchema && $batch !== '') {
            $where[] = '(k.batch_code LIKE ? OR k.batch_name LIKE ?)';
            $params[] = '%' . $batch . '%';
            $params[] = '%' . $batch . '%';
        }

        $keyword = trim((string)($filters['keyword'] ?? ''));
        if ($keyword !== '') {
            if ($supportsBatchSchema) {
                $where[] = '(k.user_key LIKE ? OR k.batch_code LIKE ? OR k.batch_name LIKE ?)';
                $params[] = '%' . $keyword . '%';
                $params[] = '%' . $keyword . '%';
                $params[] = '%' . $keyword . '%';
            } else {
                $where[] = 'k.user_key LIKE ?';
                $params[] = '%' . $keyword . '%';
            }
        }

        $select = $supportsBatchSchema
            ? 'SELECT k.*, u.username AS registrator_name'
            : "SELECT k.*, NULL AS batch_code, NULL AS batch_name, u.username AS registrator_name";
        $sql = $select . ' FROM keys_code k LEFT JOIN users u ON u.id = k.registrator_id';
        if ($where !== []) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }
        $sql .= ' ORDER BY k.id DESC';

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function listBatchSummariesForUser(int $userId, bool $isAdmin, array $filters = [], int $limit = 20): array
    {
        if (!$this->supportsBatchSchema()) {
            return [];
        }

        $where = ["k.batch_code IS NOT NULL", "k.batch_code <> ''"];
        $params = [];

        if (!$isAdmin) {
            $where[] = 'k.registrator_id = ?';
            $params[] = $userId;
        }

        $game = trim((string)($filters['game'] ?? ''));
        if ($game !== '') {
            $where[] = 'k.game = ?';
            $params[] = $game;
        }

        $batch = trim((string)($filters['batch'] ?? ''));
        if ($batch !== '') {
            $where[] = '(k.batch_code LIKE ? OR k.batch_name LIKE ?)';
            $params[] = '%' . $batch . '%';
            $params[] = '%' . $batch . '%';
        }

        $sql = 'SELECT k.batch_code, MAX(k.batch_name) AS batch_name, MAX(k.game) AS game, COUNT(*) AS keys_total, MAX(k.created_at) AS last_created_at
                FROM keys_code k
                WHERE ' . implode(' AND ', $where) . '
                GROUP BY k.batch_code
                ORDER BY MAX(k.id) DESC
                LIMIT ' . (int)$limit;

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function countForUser(int $userId, bool $isAdmin): int
    {
        if ($isAdmin) {
            return (int)$this->db->query('SELECT COUNT(*) FROM keys_code')->fetchColumn();
        }
        $stmt = $this->db->prepare('SELECT COUNT(*) FROM keys_code WHERE registrator_id = ?');
        $stmt->execute([$userId]);
        return (int)$stmt->fetchColumn();
    }

    public function update(int $id, array $data): bool
    {
        if (!$this->supportsBatchSchema()) {
            unset($data['batch_code'], $data['batch_name']);
        }
        if ($data === []) {
            return true;
        }

        $fields = [];
        $values = [];
        foreach ($data as $k => $v) {
            $fields[] = "$k = ?";
            $values[] = $v;
        }
        $values[] = $id;
        $sql = 'UPDATE keys_code SET ' . implode(', ', $fields) . ', updated_at = NOW() WHERE id = ?';
        $stmt = $this->db->prepare($sql);
        return $stmt->execute($values);
    }

    public function deleteByIdForUser(int $id, int $userId, bool $isAdmin): bool
    {
        if ($isAdmin) {
            $stmt = $this->db->prepare('DELETE FROM keys_code WHERE id = ?');
            return $stmt->execute([$id]);
        }
        $stmt = $this->db->prepare('DELETE FROM keys_code WHERE id = ? AND registrator_id = ?');
        return $stmt->execute([$id, $userId]);
    }

    public function deleteByIdsForUser(array $ids, int $userId, bool $isAdmin): int
    {
        $ids = array_values(array_unique(array_filter($ids, 'is_numeric')));
        if (!$ids) {
            return 0;
        }
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        if ($isAdmin) {
            $sql = "DELETE FROM keys_code WHERE id IN ($placeholders)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute($ids);
            return $stmt->rowCount();
        }
        $sql = "DELETE FROM keys_code WHERE id IN ($placeholders) AND registrator_id = ?";
        $stmt = $this->db->prepare($sql);
        $params = array_merge($ids, [$userId]);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    private function ensureSchema(): void
    {
        if (self::$schemaEnsured) {
            return;
        }

        try {
            $this->db->exec("ALTER TABLE keys_code ADD COLUMN batch_code VARCHAR(40) NULL AFTER game");
        } catch (\Throwable $e) {
        }

        try {
            $this->db->exec("ALTER TABLE keys_code ADD COLUMN batch_name VARCHAR(120) NULL AFTER batch_code");
        } catch (\Throwable $e) {
        }

        try {
            $this->db->exec("ALTER TABLE keys_code ADD INDEX idx_keys_batch_code (batch_code)");
        } catch (\Throwable $e) {
        }

        self::$schemaEnsured = true;
    }

    private function supportsBatchSchema(): bool
    {
        if ($this->supportsBatchSchema !== null) {
            return $this->supportsBatchSchema;
        }

        $this->supportsBatchSchema = $this->hasColumn('keys_code', 'batch_code') && $this->hasColumn('keys_code', 'batch_name');
        return $this->supportsBatchSchema;
    }

    private 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;
        }
    }

    private function normalizeDeviceList(string $raw): array
    {
        return app_normalize_device_list($raw);
    }

    private function normalizeDeviceSerial(string $serial): string
    {
        return app_normalize_device_serial($serial);
    }
}
