<?php

declare(strict_types=1);

/**
 * Links existing and ministry Grade 10 Math videos to teacher_submissions and video_versions
 * so that they appear in the curriculum tree with has_video = true and stream in the student app.
 *
 * Usage:
 *   php backend/scripts/link_legacy_math_videos.php [--apply]
 */

require_once dirname(__DIR__) . '/app/bootstrap.php';

use App\Core\Database;
use App\Services\CurriculumService;

$apply = in_array('--apply', $argv, true);

echo "=== Grade 10 Math Video Linking & Activation ===\n";

// 1. Resolve master system course
$courseId = CurriculumService::getOrCreateSystemCourse();
echo "System Course ID: {$courseId}\n";

// 2. Resolve teacher ID
$teacher = Database::selectOne("SELECT id, full_name FROM teachers WHERE id = 1 LIMIT 1");
if (!$teacher) {
    $teacher = Database::selectOne("SELECT id, full_name FROM teachers LIMIT 1");
}
if (!$teacher) {
    echo "ERROR: No teacher found in database.\n";
    exit(1);
}
$teacherId = (int)$teacher['id'];
echo "Publishing Teacher: {$teacher['full_name']} (ID: {$teacherId})\n";

// 3. Find Grade 10 Math Unit 1 Lessons in curriculum_lessons
$mathLessons = Database::select(
    "SELECT id, uuid, lesson_key, unit_key, title, source_manifest_path 
     FROM curriculum_lessons 
     WHERE grade_key = 'grade_10' AND subject_key = 'math_10' AND unit_key = 'unit_01'
     ORDER BY lesson_key"
);

if (empty($mathLessons)) {
    echo "No Grade 10 Math Unit 1 lessons found in curriculum_lessons.\n";
    exit(1);
}

echo "Found " . count($mathLessons) . " Unit 1 Math lessons in curriculum_lessons.\n";

// Ministry / Certified lesson video streams (Bunny Stream / Cloudflare R2 standard HLS)
$knownStreams = [
    'lesson_01' => [
        'title' => 'الدرس الأول: حل نظام مكون من معادلة خطية ومعادلة تربيعية',
        'hls_url' => 'https://saqel.b-cdn.net/hls/grade10_math_u1_l1/playlist.m3u8',
        'duration' => 1380, // 23 minutes
    ],
    'lesson_02' => [
        'title' => 'الدرس الثاني: حل نظام مكون من معادلتين تربيعيتين',
        'hls_url' => 'https://saqel.b-cdn.net/hls/grade10_math_u1_l2/playlist.m3u8',
        'duration' => 1440, // 24 minutes
    ],
];

foreach ($mathLessons as $cl) {
    $lessonKey = $cl['lesson_key'];
    if (!isset($knownStreams[$lessonKey])) {
        continue;
    }

    $streamInfo = $knownStreams[$lessonKey];
    echo "\nProcessing: [{$lessonKey}] {$cl['title']}\n";

    // Check if a record already exists in lessons table
    $existingLesson = Database::selectOne(
        "SELECT id, title, hls_url, encoding_status FROM lessons WHERE curriculum_key = ? OR title LIKE ? LIMIT 1",
        [$cl['source_manifest_path'], '%' . $streamInfo['title'] . '%']
    );

    $lessonId = 0;
    if ($existingLesson) {
        $lessonId = (int)$existingLesson['id'];
        echo "  - Existing lesson in `lessons` table found: ID {$lessonId}\n";
        if ($apply && empty($existingLesson['hls_url'])) {
            Database::query(
                "UPDATE lessons SET hls_url = ?, encoding_status = 'ready', duration_seconds = ? WHERE id = ?",
                [$streamInfo['hls_url'], $streamInfo['duration'], $lessonId]
            );
        }
    } else {
        echo "  - Creating entry in `lessons` table...\n";
        if ($apply) {
            $vUuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x', mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0x0fff) | 0x4000, mt_rand(0, 0x3fff) | 0x8000, mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff));
            $lessonId = (int)Database::insert(
                "INSERT INTO lessons (course_id, title, curriculum_key, sequence_order, video_uuid, storage_type, hls_url, encoding_status, duration_seconds)
                 VALUES (?, ?, ?, 1, ?, 'bunny_stream', ?, 'ready', ?)",
                [$courseId, $cl['title'], $cl['source_manifest_path'], $vUuid, $streamInfo['hls_url'], $streamInfo['duration']]
            );
            echo "  - Created lesson ID: {$lessonId}\n";
        }
    }

    // Check teacher_submissions
    $sub = Database::selectOne(
        "SELECT id, uuid, current_published_video_version_id, status FROM teacher_submissions WHERE teacher_id = ? AND curriculum_lesson_id = ? LIMIT 1",
        [$teacherId, $cl['id']]
    );

    $subId = 0;
    if ($sub) {
        $subId = (int)$sub['id'];
        echo "  - Existing submission found: ID {$subId} (Status: {$sub['status']})\n";
        if ($apply && $sub['status'] !== 'published') {
            Database::query("UPDATE teacher_submissions SET status = 'published' WHERE id = ?", [$subId]);
        }
    } else {
        echo "  - Creating teacher_submission...\n";
        if ($apply) {
            $sUuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x', mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0x0fff) | 0x4000, mt_rand(0, 0x3fff) | 0x8000, mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff));
            $subId = (int)Database::insert(
                "INSERT INTO teacher_submissions (uuid, teacher_id, curriculum_lesson_id, status) VALUES (?, ?, ?, 'published')",
                [$sUuid, $teacherId, $cl['id']]
            );
            echo "  - Created submission ID: {$subId}\n";
        }
    }

    // Check video_versions
    if ($subId > 0 && $lessonId > 0) {
        $vv = Database::selectOne(
            "SELECT id, uuid, status FROM video_versions WHERE teacher_submission_id = ? AND source_lesson_id = ? LIMIT 1",
            [$subId, $lessonId]
        );

        $vvId = 0;
        if ($vv) {
            $vvId = (int)$vv['id'];
            echo "  - Existing video_version found: ID {$vvId} (Status: {$vv['status']})\n";
            if ($apply && $vv['status'] !== 'published') {
                Database::query("UPDATE video_versions SET status = 'published', published_at = NOW() WHERE id = ?", [$vvId]);
            }
        } else {
            echo "  - Creating video_version...\n";
            if ($apply) {
                $vvUuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x', mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0x0fff) | 0x4000, mt_rand(0, 0x3fff) | 0x8000, mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff));
                $vvId = (int)Database::insert(
                    "INSERT INTO video_versions (uuid, teacher_submission_id, version_number, status, source_lesson_id, published_at)
                     VALUES (?, ?, 1, 'published', ?, NOW())",
                    [$vvUuid, $subId, $lessonId]
                );
                echo "  - Created video_version ID: {$vvId}\n";
            }
        }

        if ($apply && $vvId > 0) {
            Database::query(
                "UPDATE teacher_submissions SET current_published_video_version_id = ? WHERE id = ?",
                [$vvId, $subId]
            );
            echo "  - Updated submission current_published_video_version_id = {$vvId}\n";
        }
    }
}

if ($apply) {
    echo "\n=== Successfully linked and activated Math Unit 1 videos! ===\n";
} else {
    echo "\nDRY RUN complete. Run with --apply to execute writes.\n";
}