<?php
declare(strict_types=1);

final class AchievementService
{
    private PDO $db;

    public function __construct(PDO $db)
    {
        $this->db = $db;
    }

    /**
     * Kiểm tra và mở khóa thành tựu.
     * Các requirement_type đang hỗ trợ:
     * - lifetime_xp
     * - yearly_xp
     * - transactions_count
     * - activity_type_count:flashcards
     * - activity_type_count:listen_repeat
     * - activity_type_count:practice
     * - activity_type_count:game
     */
    public function checkAndUnlock(
        int $studentId,
        int $academicYearId
    ): array {
        $stmt = $this->db->prepare(
            'SELECT a.*
             FROM achievements a
             LEFT JOIN student_achievements sa
               ON sa.achievement_id = a.id
              AND sa.student_id = :student_id
             WHERE a.is_active = 1
               AND sa.id IS NULL
             ORDER BY a.id ASC'
        );

        $stmt->execute([':student_id' => $studentId]);
        $achievements = $stmt->fetchAll();

        $unlocked = [];

        foreach ($achievements as $achievement) {
            $progress = $this->getProgress(
                $studentId,
                $academicYearId,
                (string)$achievement['requirement_type']
            );

            if ($progress < (int)$achievement['requirement_value']) {
                continue;
            }

            $insert = $this->db->prepare(
                'INSERT IGNORE INTO student_achievements
                    (student_id, achievement_id, earned_at)
                 VALUES
                    (:student_id, :achievement_id, NOW())'
            );

            $insert->execute([
                ':student_id' => $studentId,
                ':achievement_id' => (int)$achievement['id'],
            ]);

            if ($insert->rowCount() > 0) {
                $achievement['progress'] = $progress;
                $unlocked[] = $achievement;
            }
        }

        return $unlocked;
    }

    public function getStudentAchievements(int $studentId): array
    {
        $stmt = $this->db->prepare(
            'SELECT
                a.id,
                a.code,
                a.name,
                a.description,
                a.icon,
                a.xp_reward,
                sa.earned_at
             FROM student_achievements sa
             INNER JOIN achievements a ON a.id = sa.achievement_id
             WHERE sa.student_id = :student_id
             ORDER BY sa.earned_at DESC'
        );

        $stmt->execute([':student_id' => $studentId]);

        return $stmt->fetchAll();
    }

    private function getProgress(
        int $studentId,
        int $academicYearId,
        string $requirementType
    ): int {
        if ($requirementType === 'lifetime_xp') {
            return $this->scalar(
                'SELECT lifetime_xp FROM students WHERE id = :student_id',
                [':student_id' => $studentId]
            );
        }

        if ($requirementType === 'yearly_xp') {
            return $this->scalar(
                'SELECT xp
                 FROM student_xp
                 WHERE student_id = :student_id
                   AND academic_year_id = :academic_year_id',
                [
                    ':student_id' => $studentId,
                    ':academic_year_id' => $academicYearId,
                ]
            );
        }

        if ($requirementType === 'transactions_count') {
            return $this->scalar(
                'SELECT COUNT(*)
                 FROM xp_transactions
                 WHERE student_id = :student_id
                   AND academic_year_id = :academic_year_id
                   AND xp_change > 0
                   AND xp_source <> "achievement"',
                [
                    ':student_id' => $studentId,
                    ':academic_year_id' => $academicYearId,
                ]
            );
        }

        if (str_starts_with($requirementType, 'activity_type_count:')) {
            $activityType = substr($requirementType, strlen('activity_type_count:'));

            return $this->scalar(
                'SELECT COUNT(*)
                 FROM xp_transactions
                 WHERE student_id = :student_id
                   AND academic_year_id = :academic_year_id
                   AND activity_type = :activity_type
                   AND xp_change > 0',
                [
                    ':student_id' => $studentId,
                    ':academic_year_id' => $academicYearId,
                    ':activity_type' => $activityType,
                ]
            );
        }

        return 0;
    }

    private function scalar(string $sql, array $params): int
    {
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);

        return (int)($stmt->fetchColumn() ?: 0);
    }
}
