saqel/backend/scripts/backfill_curriculum_identity.php

<?php

declare(strict_types=1);

/**
 * Builds a review queue from manifest.json for migration 20260909.
 * Default mode is dry-run and never connects to MySQL. --apply creates only
 * unverified lessons, draft bundles, and draft assets; it never publishes.
 *
 * Usage:
 *   php backend/scripts/backfill_curriculum_identity.php
 *   php backend/scripts/backfill_curriculum_identity.php --apply
 */

$apply = in_array('--apply', $argv, true);
$manifestPath = dirname(__DIR__) . '/storage/curriculum/manifest.json';
$storageRoot = realpath(dirname(__DIR__) . '/storage/curriculum');

if (!$storageRoot || !is_file($manifestPath)) {
    fwrite(STDERR, "Manifest or curriculum storage is unavailable.\n");
    exit(1);
}

$manifest = json_decode((string)file_get_contents($manifestPath), true);
if (!is_array($manifest)) {
    fwrite(STDERR, "Manifest is not valid JSON.\n");
    exit(1);
}

$rows = [];
foreach ($manifest as $gradeKey => $grade) {
    foreach (($grade['subjects'] ?? []) as $subjectKey => $subject) {
        foreach (($subject['semesters'] ?? []) as $semesterKey => $semester) {
            foreach (($semester['units'] ?? []) as $unitKey => $unit) {
                foreach (($unit['lessons'] ?? []) as $lesson) {
                    $file = (string)($lesson['file'] ?? '');
                    $candidate = realpath($storageRoot . '/' . ltrim($file, '/'));
                    $insideStorage = $candidate && str_starts_with(
                        $candidate,
                        rtrim($storageRoot, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR
                    );
                    $rows[] = [
                        'grade_key' => (string)$gradeKey,
                        'subject_key' => (string)$subjectKey,
                        'semester_key' => (string)$semesterKey,
                        'unit_key' => (string)$unitKey,
                        'lesson_key' => (string)($lesson['id'] ?? ''),
                        'title' => (string)($lesson['title'] ?? ''),
                        'manifest_path' => $file,
                        'path' => $insideStorage ? $candidate : null,
                        'missing' => !$insideStorage,
                    ];
                }
            }
        }
    }
}

$identityCounts = [];
foreach ($rows as $row) {
    $identity = implode('|', [$row['grade_key'], $row['subject_key'], $row['semester_key'], $row['unit_key'], $row['lesson_key']]);
    $identityCounts[$identity] = ($identityCounts[$identity] ?? 0) + 1;
}
$conflicts = array_keys(array_filter($identityCounts, static fn (int $count): bool => $count > 1));
$missing = array_values(array_filter($rows, static fn (array $row): bool => $row['missing']));

$report = [
    'mode' => $apply ? 'apply' : 'dry_run',
    'curriculum_version' => 'manifest-2026-09-09',
    'lessons_found' => count($rows),
    'missing_assets' => array_map(static fn (array $row): array => [
        'identity' => implode('/', [$row['grade_key'], $row['subject_key'], $row['semester_key'], $row['unit_key'], $row['lesson_key']]),
        'title' => $row['title'],
        'manifest_path' => $row['manifest_path'] ?: null,
        'reason' => $row['manifest_path'] === '' ? 'missing_manifest_file_reference' : 'file_not_found_in_local_storage',
    ], $missing),
    'identity_conflicts' => $conflicts,
    'eligible_for_review_queue' => count($rows) - count($missing),
];

if (!$apply) {
    echo json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . PHP_EOL;
    exit(($missing || $conflicts) ? 2 : 0);
}

if ($conflicts) {
    fwrite(STDERR, "Conflicting curriculum identities found. Resolve them before --apply.\n");
    echo json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . PHP_EOL;
    exit(2);
}

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

use App\Core\Database;

$pdo = Database::getConnection();
$pdo->beginTransaction();
try {
    foreach ($rows as $row) {
        if ($row['missing']) {
            continue;
        }
        $lessonUuid = selfUuid();
        Database::query(
            "INSERT INTO curriculum_lessons
                (uuid, grade_key, subject_key, semester_key, unit_key, lesson_key, curriculum_version, title, source_manifest_path, source_status)
             VALUES (?, ?, ?, ?, ?, ?, 'manifest-2026-09-09', ?, ?, 'unverified')
             ON DUPLICATE KEY UPDATE title = VALUES(title), source_manifest_path = VALUES(source_manifest_path)",
            [$lessonUuid, $row['grade_key'], $row['subject_key'], $row['semester_key'], $row['unit_key'], $row['lesson_key'], $row['title'], $row['manifest_path']]
        );
        $lessonId = (int)Database::selectOne(
            "SELECT id FROM curriculum_lessons WHERE grade_key = ? AND subject_key = ? AND semester_key = ? AND unit_key = ? AND lesson_key = ? AND curriculum_version = 'manifest-2026-09-09' LIMIT 1",
            [$row['grade_key'], $row['subject_key'], $row['semester_key'], $row['unit_key'], $row['lesson_key']]
        )['id'];
        Database::query(
            "INSERT INTO publication_bundles (uuid, curriculum_lesson_id, bundle_version, status)
             VALUES (?, ?, 'manifest-2026-09-09', 'draft')
             ON DUPLICATE KEY UPDATE id = id",
            [selfUuid(), $lessonId]
        );
        $bundleId = (int)Database::selectOne(
            "SELECT id FROM publication_bundles WHERE curriculum_lesson_id = ? AND bundle_version = 'manifest-2026-09-09' LIMIT 1",
            [$lessonId]
        )['id'];
        $sha = hash_file('sha256', $row['path']);
        $bytes = filesize($row['path']);
        Database::query(
            "INSERT INTO content_assets (uuid, asset_type, storage_driver, storage_key, mime_type, byte_size, sha256, source_reference, review_status)
             VALUES (?, 'lesson_markdown', 'local', ?, 'text/markdown; charset=utf-8', ?, ?, ?, 'draft')
             ON DUPLICATE KEY UPDATE id = id",
            [selfUuid(), $row['manifest_path'], $bytes, $sha, 'manifest.json']
        );
        $assetId = (int)Database::selectOne(
            "SELECT id FROM content_assets WHERE storage_driver = 'local' AND storage_key = ? AND sha256 = ? LIMIT 1",
            [$row['manifest_path'], $sha]
        )['id'];
        Database::query(
            "INSERT IGNORE INTO publication_bundle_assets (publication_bundle_id, content_asset_id, role) VALUES (?, ?, 'primary_lesson')",
            [$bundleId, $assetId]
        );
    }
    $pdo->commit();
    $report['created_review_queue'] = true;
    echo json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE) . PHP_EOL;
} catch (Throwable $e) {
    $pdo->rollBack();
    fwrite(STDERR, "Backfill rolled back: {$e->getMessage()}\n");
    exit(1);
}

function selfUuid(): string
{
    $bytes = random_bytes(16);
    $bytes[6] = chr((ord($bytes[6]) & 0x0f) | 0x40);
    $bytes[8] = chr((ord($bytes[8]) & 0x3f) | 0x80);
    return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($bytes), 4));
}