<?php
declare(strict_types=1);

/**
 * EnglishSpeak Shared XP + Activity Progress Engine (V1)
 *
 * Single entry point for all student games.
 * Rules:
 * - preview mode saves nothing;
 * - best_score never decreases;
 * - Completion XP is awarded only once, on the first score >= Passing Score;
 * - later attempts award +2 Practice XP at most twice per activity;
 * - scores below Passing Score before first completion earn 0 XP;
 * - an already completed activity never becomes incomplete again.
 */

function esXpColumns(PDO $pdo, string $table): array
{
    $stmt = $pdo->prepare(
        "SELECT COUNT(*) FROM information_schema.TABLES
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?"
    );
    $stmt->execute([$table]);
    if ((int)$stmt->fetchColumn() === 0) {
        return [];
    }
    $rows = $pdo->query("SHOW COLUMNS FROM `{$table}`")->fetchAll(PDO::FETCH_ASSOC);
    return array_column($rows, null, 'Field');
}

function esXpEnsureActivityAttemptsTable(PDO $pdo): void
{
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS student_activity_attempts (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            student_id BIGINT UNSIGNED NOT NULL,
            academic_year_id INT NOT NULL,
            book_id BIGINT UNSIGNED DEFAULT NULL,
            level_id BIGINT UNSIGNED DEFAULT NULL,
            unit_id BIGINT UNSIGNED DEFAULT NULL,
            lesson_id BIGINT UNSIGNED DEFAULT NULL,
            activity_id BIGINT UNSIGNED NOT NULL,
            activity_type VARCHAR(50) DEFAULT NULL,
            score TINYINT UNSIGNED NOT NULL DEFAULT 0,
            passed TINYINT(1) NOT NULL DEFAULT 0,
            duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
            attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_attempt_date (attempted_at),
            KEY idx_attempt_student_date (student_id, attempted_at),
            KEY idx_attempt_lesson_date (lesson_id, attempted_at),
            KEY idx_attempt_activity_date (activity_id, attempted_at)
        ) ENGINE=InnoDB
          DEFAULT CHARSET=utf8mb4
          COLLATE=utf8mb4_unicode_ci"
    );
}

function esXpCurrentAcademicYearId(PDO $pdo): int
{
    $columns = esXpColumns($pdo, 'academic_years');

    if ($columns === [] || !isset($columns['id'])) {
        throw new RuntimeException('Missing academic_years table.');
    }

    if (isset($columns['is_current'])) {
        $stmt = $pdo->query(
            "SELECT id
             FROM academic_years
             WHERE is_current = 1
             ORDER BY id DESC
             LIMIT 1"
        );

        $id = (int)($stmt->fetchColumn() ?: 0);

        if ($id > 0) {
            return $id;
        }
    }

    /*
     * Nếu không có is_current (hoặc chưa đánh dấu), ưu tiên năm học
     * chứa ngày hiện tại thay vì lấy ID lớn nhất. Điều này tránh việc
     * một năm học tương lai (ví dụ 2027-2028) bị chọn nhầm trong 2026.
     */
    if (isset($columns['start_date']) && isset($columns['end_date'])) {
        $stmt = $pdo->query(
            "SELECT id
             FROM academic_years
             WHERE CURDATE() BETWEEN start_date AND end_date
             ORDER BY start_date DESC, id DESC
             LIMIT 1"
        );

        $id = (int)($stmt->fetchColumn() ?: 0);

        if ($id > 0) {
            return $id;
        }
    }

    $stmt = $pdo->query(
        "SELECT id
         FROM academic_years
         ORDER BY id DESC
         LIMIT 1"
    );

    $id = (int)($stmt->fetchColumn() ?: 0);

    if ($id <= 0) {
        throw new RuntimeException('No academic year found.');
    }

    return $id;
}

function esXpAwardLearningStreak(
    PDO $pdo,
    int $studentId,
    int $academicYearId
): array {
    /*
     * Một "ngày học" = có ít nhất 1 Activity passed=1.
     * Chuỗi có thể đi qua tháng/năm; chỉ đứt khi thiếu một ngày.
     * Mốc thưởng: 3=>5, 7=>10, 14=>15, 30=>30 XP.
     */
    $dateStmt = $pdo->prepare(
        "SELECT DISTINCT DATE(attempted_at) AS learning_date
         FROM student_activity_attempts
         WHERE student_id = ?
           AND passed = 1
           AND attempted_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
         ORDER BY learning_date DESC"
    );
    $dateStmt->execute([$studentId]);
    $dates = array_values(array_filter(array_map(
        static fn($value): string => trim((string)$value),
        $dateStmt->fetchAll(PDO::FETCH_COLUMN)
    )));

    if ($dates === [] || $dates[0] !== date('Y-m-d')) {
        return ['days' => 0, 'xp_added' => 0, 'milestone' => 0];
    }

    $streakDays = 1;
    $cursor = new DateTimeImmutable($dates[0]);

    for ($i = 1, $count = count($dates); $i < $count; $i++) {
        $expected = $cursor->modify('-1 day')->format('Y-m-d');

        if ($dates[$i] !== $expected) {
            break;
        }

        $streakDays++;
        $cursor = new DateTimeImmutable($dates[$i]);
    }

    $milestones = [3 => 5, 7 => 10, 14 => 15, 30 => 30];

    if (!isset($milestones[$streakDays])) {
        return ['days' => $streakDays, 'xp_added' => 0, 'milestone' => 0];
    }

    $streakStart = (new DateTimeImmutable(date('Y-m-d')))
        ->modify('-' . ($streakDays - 1) . ' days')
        ->format('Y-m-d');

    // reference_id gắn với ngày bắt đầu chuỗi + mốc, để chuỗi mới có thể nhận lại.
    $referenceId = (int)sprintf(
        '%s%02d',
        str_replace('-', '', $streakStart),
        $streakDays
    );

    $alreadyStmt = $pdo->prepare(
        "SELECT id
         FROM xp_transactions
         WHERE student_id = ?
           AND xp_source = 'streak'
           AND reference_id = ?
         LIMIT 1"
    );
    $alreadyStmt->execute([$studentId, $referenceId]);

    if ($alreadyStmt->fetchColumn()) {
        return ['days' => $streakDays, 'xp_added' => 0, 'milestone' => $streakDays];
    }

    $streakXp = $milestones[$streakDays];

    $event = $pdo->prepare(
        "INSERT INTO xp_transactions
        (
            student_id,
            academic_year_id,
            book_id,
            unit_id,
            lesson_id,
            level_id,
            activity_id,
            activity_type,
            xp_source,
            reference_id,
            xp_change,
            reason,
            created_at
        )
        VALUES (?, ?, NULL, NULL, NULL, NULL, NULL, 'streak', 'streak', ?, ?, ?, NOW())"
    );
    $event->execute([
        $studentId,
        $academicYearId,
        $referenceId,
        $streakXp,
        "Learning streak: {$streakDays} days ({$streakStart})",
    ]);

    $studentColumns = esXpColumns($pdo, 'students');
    if (isset($studentColumns['lifetime_xp'])) {
        $update = $pdo->prepare(
            "UPDATE students
             SET lifetime_xp = COALESCE(lifetime_xp,0) + ?
             WHERE id = ?"
        );
        $update->execute([$streakXp, $studentId]);
    }

    return [
        'days' => $streakDays,
        'xp_added' => $streakXp,
        'milestone' => $streakDays,
    ];
}


function englishSpeakSaveActivityProgress(PDO $pdo, array $input): array
{
    $preview = filter_var($input['preview'] ?? false, FILTER_VALIDATE_BOOL);
    if ($preview) {
        return ['ok'=>true,'saved'=>false,'preview'=>true,'xp_added'=>0,'message'=>'Preview mode. Progress was not saved.'];
    }

    $studentId = max(0, (int)($input['student_id'] ?? 0));
    $activityId = max(0, (int)($input['activity_id'] ?? 0));
    $score = max(0, min(100, (int)round((float)($input['score'] ?? 0))));
    $duration = max(0, (int)($input['duration'] ?? 0));
    $passingScore = max(0, min(100, (int)($input['passing_score'] ?? 80)));

    if ($studentId <= 0 || $activityId <= 0) {
        throw new InvalidArgumentException('Missing student_id or activity_id.');
    }

    $progressColumns = esXpColumns($pdo, 'student_content_progress');
    foreach (['student_id','content_type','content_id','status','best_score','attempt_count','xp_earned','first_started_at','completed_at','last_attempt_at'] as $column) {
        if (!isset($progressColumns[$column])) {
            throw new RuntimeException("Missing student_content_progress column: {$column}");
        }
    }

    $activityColumns = esXpColumns($pdo, 'activities');
    if ($activityColumns === []) {
        throw new RuntimeException('Missing activities table.');
    }

    $xpSelect = isset($activityColumns['xp_reward'])
        ? 'a.xp_reward AS xp_reward'
        : '0 AS xp_reward';

    $activityTypeSelect = isset($activityColumns['activity_type'])
        ? 'a.activity_type AS activity_type'
        : (isset($activityColumns['type'])
            ? 'a.type AS activity_type'
            : "'' AS activity_type");

    $stmt = $pdo->prepare(
        "SELECT
            a.id,
            {$xpSelect},
            {$activityTypeSelect},
            le.id AS lesson_id,
            u.id AS unit_id,
            l.id AS level_id,
            b.id AS book_id
         FROM activities a
         INNER JOIN lessons le ON le.id = a.lesson_id
         INNER JOIN units u ON u.id = le.unit_id
         INNER JOIN levels l ON l.id = u.level_id
         INNER JOIN books b ON b.id = l.book_id
         WHERE a.id = ?
         LIMIT 1"
    );
    $stmt->execute([$activityId]);
    $activity = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$activity) {
        throw new RuntimeException('Activity not found.');
    }

    $completionXp = max(0, (int)($activity['xp_reward'] ?? 0));
    if ($completionXp <= 0) $completionXp = 10;
    $isPassed = $score >= $passingScore;

    $xpTransactionColumns = esXpColumns($pdo, 'xp_transactions');

    foreach ([
        'student_id',
        'academic_year_id',
        'book_id',
        'unit_id',
        'lesson_id',
        'level_id',
        'activity_id',
        'activity_type',
        'xp_source',
        'reference_id',
        'xp_change',
        'reason',
        'created_at'
    ] as $column) {
        if (!isset($xpTransactionColumns[$column])) {
            throw new RuntimeException("Missing xp_transactions column: {$column}");
        }
    }

    $academicYearId = esXpCurrentAcademicYearId($pdo);

    /*
     * Nhật ký từng lượt học để Teacher Daily Report có thể trả lời chính xác:
     * ngày nào, Lesson nào, bao nhiêu học sinh đã vào làm và bao nhiêu lượt làm.
     * Preview không đi tới đây nên không ghi log.
     */
    esXpEnsureActivityAttemptsTable($pdo);

    $pdo->beginTransaction();
    try {
        $existingStmt = $pdo->prepare(
            "SELECT id,status,best_score,attempt_count,xp_earned,completed_at
             FROM student_content_progress
             WHERE student_id=? AND content_type='activity' AND content_id=?
             LIMIT 1 FOR UPDATE"
        );
        $existingStmt->execute([$studentId,$activityId]);
        $existing = $existingStmt->fetch(PDO::FETCH_ASSOC);

        $hasCompletionStmt = $pdo->prepare(
            "SELECT id
             FROM xp_transactions
             WHERE student_id = ?
               AND activity_id = ?
               AND xp_source = 'completion'
             LIMIT 1"
        );
        $hasCompletionStmt->execute([$studentId,$activityId]);
        $hasCompletionEvent = (bool)$hasCompletionStmt->fetchColumn();

        /*
         * Tương thích dữ liệu cũ:
         * Một số game trước đây đã đánh dấu Activity completed nhưng
         * chưa tạo dòng completion trong xp_transactions.
         *
         * Nếu chỉ kiểm tra event, lần chơi lại đầu tiên sẽ bị hiểu nhầm
         * là Completion mới và cộng lại +10 XP. Vì vậy status/completed_at
         * của Progress cũng là bằng chứng Activity đã từng hoàn thành.
         */
        $existingStatus = $existing
            ? strtolower(trim((string)($existing['status'] ?? '')))
            : '';

        $hasLegacyCompletion = $existing
            && (
                $existingStatus === 'completed'
                || !empty($existing['completed_at'])
                || max(0, (int)($existing['xp_earned'] ?? 0)) > 0
            );

        $hasCompletion = $hasCompletionEvent || $hasLegacyCompletion;

        $practiceCountStmt = $pdo->prepare(
            "SELECT COUNT(*)
             FROM xp_transactions
             WHERE student_id = ?
               AND activity_id = ?
               AND xp_source IN ('practice','new_best')"
        );
        $practiceCountStmt->execute([$studentId,$activityId]);
        $practiceCount = (int)$practiceCountStmt->fetchColumn();

        $oldBest = $existing ? max(0,(int)($existing['best_score'] ?? 0)) : 0;
        $newBest = max($oldBest,$score);
        $isNewBest = $score > $oldBest;
        $xpAdded = 0;
        $eventType = '';

        if ($isPassed && !$hasCompletion) {
            /*
             * Completion XP chỉ cấp khi lần đầu đạt Passing Score.
             */
            $xpAdded = $completionXp;
            $eventType = 'completion';
        } elseif ($hasCompletion && $practiceCount < 2) {
            /*
             * Activity đã từng đạt Passing Score:
             * các lượt học lại được tính Practice XP, kể cả điểm
             * của lượt hiện tại thấp hơn Passing Score.
             */
            $xpAdded = 2;
            $eventType = $isNewBest ? 'new_best' : 'practice';
        }

        $oldStatus = $existing ? strtolower(trim((string)($existing['status'] ?? ''))) : '';
        $status = ($oldStatus === 'completed' || $isPassed) ? 'completed' : 'in_progress';

        /*
         * student_content_progress.xp_earned chỉ lưu Completion XP
         * của Activity, không cộng dồn Practice XP hoặc New Best XP.
         * Practice/New Best được lưu trong xp_transactions và tổng XP.
         */
        $oldCompletionXp = $existing
            ? max(0, (int)($existing['xp_earned'] ?? 0))
            : 0;

        if ($eventType === 'completion') {
            $activityCompletionXp = max($oldCompletionXp, $completionXp);
        } elseif ($hasCompletion) {
            /*
             * Activity đã từng hoàn thành (qua event hoặc Progress cũ)
             * thì xp_earned giữ đúng Completion XP cấu hình.
             */
            $activityCompletionXp = $completionXp;
        } else {
            $activityCompletionXp = 0;
        }

        if ($existing) {
            $update = $pdo->prepare(
                "UPDATE student_content_progress SET
                    status=?, best_score=?, attempt_count=COALESCE(attempt_count,0)+1,
                    xp_earned=?, last_attempt_at=NOW(),
                    completed_at=CASE WHEN ?='completed' THEN COALESCE(completed_at,NOW()) ELSE completed_at END
                 WHERE id=?"
            );
            $update->execute([$status,$newBest,$activityCompletionXp,$status,(int)$existing['id']]);
            $progressId = (int)$existing['id'];
            $attemptCount = max(0,(int)($existing['attempt_count'] ?? 0))+1;
        } else {
            $insert = $pdo->prepare(
                "INSERT INTO student_content_progress
                (student_id,content_type,content_id,status,best_score,attempt_count,xp_earned,first_started_at,completed_at,last_attempt_at)
                VALUES (?,'activity',?,?,?,?,?,NOW(),CASE WHEN ?='completed' THEN NOW() ELSE NULL END,NOW())"
            );
            $insert->execute([$studentId,$activityId,$status,$score,1,$activityCompletionXp,$status]);
            $progressId = (int)$pdo->lastInsertId();
            $attemptCount = 1;
        }

        /*
         * Mỗi lần một game gọi Shared Progress Engine sau khi kết thúc lượt chơi
         * đều ghi đúng 1 dòng. Log này độc lập với việc có được XP hay không.
         */
        $attemptLog = $pdo->prepare(
            "INSERT INTO student_activity_attempts
            (
                student_id,
                academic_year_id,
                book_id,
                level_id,
                unit_id,
                lesson_id,
                activity_id,
                activity_type,
                score,
                passed,
                duration_ms,
                attempted_at
            )
            VALUES (?,?,?,?,?,?,?,?,?,?,?,NOW())"
        );
        $attemptLog->execute([
            $studentId,
            $academicYearId,
            (int)$activity['book_id'],
            (int)$activity['level_id'],
            (int)$activity['unit_id'],
            (int)$activity['lesson_id'],
            $activityId,
            (string)($activity['activity_type'] ?? ''),
            $score,
            $isPassed ? 1 : 0,
            $duration,
        ]);

        if ($xpAdded > 0 && $eventType !== '') {
            /*
             * xp_transactions is the single XP transaction ledger.
             * student_xp_events and student_xp are no longer written here.
             */
            $reason = $eventType === 'completion'
                ? "Activity completion: {$score}%"
                : ($eventType === 'new_best'
                    ? "Activity practice - new best: {$score}%"
                    : "Activity practice: {$score}%");

            $event = $pdo->prepare(
                "INSERT INTO xp_transactions
                (
                    student_id,
                    academic_year_id,
                    book_id,
                    unit_id,
                    lesson_id,
                    level_id,
                    activity_id,
                    activity_type,
                    xp_source,
                    reference_id,
                    xp_change,
                    reason,
                    created_at
                )
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,NOW())"
            );

            $event->execute([
                $studentId,
                $academicYearId,
                (int)$activity['book_id'],
                (int)$activity['unit_id'],
                (int)$activity['lesson_id'],
                (int)$activity['level_id'],
                $activityId,
                (string)($activity['activity_type'] ?? ''),
                $eventType,
                $activityId,
                $xpAdded,
                $reason,
            ]);

            /*
             * Keep lifetime_xp as the current cached total used by existing UI.
             * XP history/source of truth is xp_transactions.
             */
            $studentColumns = esXpColumns($pdo,'students');

            if (isset($studentColumns['lifetime_xp'])) {
                $u = $pdo->prepare(
                    "UPDATE students
                     SET lifetime_xp = COALESCE(lifetime_xp,0) + ?
                     WHERE id = ?"
                );
                $u->execute([$xpAdded,$studentId]);
            }
        }

        /*
         * Streak XP chạy sau khi attempt hiện tại đã được ghi.
         * Chỉ passed=1 mới tạo ngày học, nên fail/replay không đạt không làm tăng streak.
         * XP streak cộng riêng, không thay đổi Completion/Practice XP của Activity.
         */
        $streak = $isPassed
            ? esXpAwardLearningStreak($pdo, $studentId, $academicYearId)
            : ['days' => 0, 'xp_added' => 0, 'milestone' => 0];

        $pdo->commit();
        return [
            'ok'=>true,'saved'=>true,'preview'=>false,'progress_id'=>$progressId,
            'status'=>$status,'best_score'=>$newBest,'attempt_count'=>$attemptCount,
            'xp_earned'=>$activityCompletionXp,'xp_added'=>$xpAdded,'xp_event_type'=>$eventType,
            'streak_days'=>(int)($streak['days'] ?? 0),
            'streak_xp_added'=>(int)($streak['xp_added'] ?? 0),
            'streak_milestone'=>(int)($streak['milestone'] ?? 0),
            'new_best'=>$isNewBest,'duration'=>$duration,'passed'=>$isPassed,
            'passing_score'=>$passingScore,
            'reward_message'=>!$isPassed
                ? "Good effort! You need {$passingScore}% to earn XP. Try Again."
                : ($xpAdded>0 ? '' : 'Practice complete.'),
        ];
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        throw $e;
    }
}
