<?php

/**
 * MiniMax H3 短剧工坊迁移（可重复执行）。
 * 用法: php database/migrate_short_drama.php
 */

require __DIR__ . '/../vendor/autoload.php';

$app = new think\App();
$app->initialize();

use app\service\SettingsService;
use app\service\ShortDramaPlannerService;
use think\facade\Db;

function shortDramaTableExists(string $table): bool
{
    $db = Db::getConfig('database') ?: env('DB_NAME', 'ai_chat');
    $rows = Db::query(
        'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? LIMIT 1',
        [$db, $table]
    );
    return !empty($rows);
}

function shortDramaColumnExists(string $table, string $column): bool
{
    $db = Db::getConfig('database') ?: env('DB_NAME', 'ai_chat');
    $rows = Db::query(
        'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1',
        [$db, $table, $column]
    );
    return !empty($rows);
}

function shortDramaColumnType(string $table, string $column): string
{
    $db = Db::getConfig('database') ?: env('DB_NAME', 'ai_chat');
    $rows = Db::query(
        'SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1',
        [$db, $table, $column]
    );
    return strtolower((string) ($rows[0]['DATA_TYPE'] ?? ''));
}

try {
    if (!shortDramaTableExists('video_projects')) {
        Db::execute("CREATE TABLE video_projects (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id INT UNSIGNED NOT NULL,
            title VARCHAR(160) NOT NULL,
            idea TEXT NOT NULL,
            style VARCHAR(100) DEFAULT '电影写实',
            aspect_ratio VARCHAR(10) NOT NULL DEFAULT '9:16',
            episode_duration INT UNSIGNED NOT NULL DEFAULT 30,
            duration_mode VARCHAR(10) NOT NULL DEFAULT 'fixed',
            quality VARCHAR(20) NOT NULL DEFAULT 'fast',
            voice_language VARCHAR(20) NOT NULL DEFAULT 'zh-CN',
            show_subtitles TINYINT(1) NOT NULL DEFAULT 1,
            character_origin VARCHAR(20) NOT NULL DEFAULT 'east_asian',
            screen_text_language VARCHAR(20) NOT NULL DEFAULT 'zh-CN',
            shot_duration_mode VARCHAR(10) NOT NULL DEFAULT 'auto',
            status VARCHAR(30) NOT NULL DEFAULT 'draft',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_video_project_user (user_id, updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        echo "Created table video_projects\n";
    }

    if (!shortDramaColumnExists('video_projects', 'voice_language')) {
        // 历史成片保留原音；尚未完成的项目升级为普通话重配音。
        Db::execute("ALTER TABLE video_projects ADD COLUMN voice_language VARCHAR(20) NOT NULL DEFAULT 'native' AFTER quality");
        Db::execute("UPDATE video_projects SET voice_language = 'zh-CN'
            WHERE status IN ('storyboard', 'generating', 'needs_attention')
               OR id = (SELECT id FROM (SELECT id FROM video_projects ORDER BY id DESC LIMIT 1) recent_project)");
        Db::execute("ALTER TABLE video_projects ALTER COLUMN voice_language SET DEFAULT 'zh-CN'");
        echo "Added video_projects.voice_language\n";
    }

    if (!shortDramaColumnExists('video_projects', 'duration_mode')) {
        Db::execute("ALTER TABLE video_projects ADD COLUMN duration_mode VARCHAR(10) NOT NULL DEFAULT 'fixed' AFTER episode_duration");
        echo "Added video_projects.duration_mode\n";
    }

    if (!shortDramaColumnExists('video_projects', 'show_subtitles')) {
        // 已有成片没有烧录字幕，保持关闭；新建项目默认显示字幕。
        Db::execute('ALTER TABLE video_projects ADD COLUMN show_subtitles TINYINT(1) NOT NULL DEFAULT 0 AFTER voice_language');
        Db::execute('ALTER TABLE video_projects ALTER COLUMN show_subtitles SET DEFAULT 1');
        echo "Added video_projects.show_subtitles\n";
    }

    if (!shortDramaColumnExists('video_projects', 'character_origin')) {
        Db::execute("ALTER TABLE video_projects ADD COLUMN character_origin VARCHAR(20) NOT NULL DEFAULT 'east_asian' AFTER show_subtitles");
        echo "Added video_projects.character_origin\n";
    }

    if (!shortDramaColumnExists('video_projects', 'screen_text_language')) {
        // 历史项目没有精确场景文字层，保持关闭；新项目默认使用简体中文。
        Db::execute("ALTER TABLE video_projects ADD COLUMN screen_text_language VARCHAR(20) NOT NULL DEFAULT 'none' AFTER character_origin");
        Db::execute("ALTER TABLE video_projects ALTER COLUMN screen_text_language SET DEFAULT 'zh-CN'");
        echo "Added video_projects.screen_text_language\n";
    }

    if (!shortDramaColumnExists('video_projects', 'shot_duration_mode')) {
        // 历史项目已按 5 秒分镜生成；新项目默认交给 AI 在 5/10 秒间选择。
        Db::execute("ALTER TABLE video_projects ADD COLUMN shot_duration_mode VARCHAR(10) NOT NULL DEFAULT '5' AFTER screen_text_language");
        Db::execute("ALTER TABLE video_projects ALTER COLUMN shot_duration_mode SET DEFAULT 'auto'");
        echo "Added video_projects.shot_duration_mode\n";
    }

    if (shortDramaColumnType('video_projects', 'episode_duration') !== 'int') {
        Db::execute('ALTER TABLE video_projects MODIFY COLUMN episode_duration INT UNSIGNED NOT NULL DEFAULT 30');
        echo "Expanded video_projects.episode_duration to INT UNSIGNED\n";
    }

    if (shortDramaColumnType('video_projects', 'idea') !== 'mediumtext') {
        Db::execute('ALTER TABLE video_projects MODIFY COLUMN idea MEDIUMTEXT NOT NULL');
        echo "Expanded video_projects.idea to MEDIUMTEXT\n";
    }

    if (!shortDramaTableExists('video_characters')) {
        Db::execute("CREATE TABLE video_characters (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            project_id BIGINT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            name VARCHAR(80) NOT NULL,
            description TEXT NULL,
            reference_upload_id INT UNSIGNED NULL,
            voice_key VARCHAR(100) NULL,
            is_locked TINYINT(1) NOT NULL DEFAULT 1,
            asset_version INT UNSIGNED NOT NULL DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (project_id) REFERENCES video_projects(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            INDEX idx_video_character_project (project_id, id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        echo "Created table video_characters\n";
    }

    if (!shortDramaTableExists('video_episodes')) {
        Db::execute("CREATE TABLE video_episodes (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            project_id BIGINT UNSIGNED NOT NULL,
            episode_no INT UNSIGNED NOT NULL DEFAULT 1,
            title VARCHAR(160) NOT NULL,
            script MEDIUMTEXT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'storyboard',
            progress TINYINT UNSIGNED NOT NULL DEFAULT 0,
            progress_message VARCHAR(255) NULL,
            final_upload_id INT UNSIGNED NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (project_id) REFERENCES video_projects(id) ON DELETE CASCADE,
            UNIQUE KEY uk_video_episode_number (project_id, episode_no),
            INDEX idx_video_episode_status (status, updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        echo "Created table video_episodes\n";
    }

    if (!shortDramaTableExists('video_shots')) {
        Db::execute("CREATE TABLE video_shots (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            episode_id BIGINT UNSIGNED NOT NULL,
            shot_no INT UNSIGNED NOT NULL,
            title VARCHAR(160) NOT NULL,
            prompt TEXT NOT NULL,
            dialogue TEXT NULL,
            duration_seconds SMALLINT UNSIGNED NOT NULL DEFAULT 5,
            workflow_type VARCHAR(20) NOT NULL DEFAULT 'fl2va',
            status VARCHAR(30) NOT NULL DEFAULT 'draft',
            prompt_id VARCHAR(80) NULL,
            seed BIGINT UNSIGNED NULL,
            output_upload_id INT UNSIGNED NULL,
            error_message TEXT NULL,
            meta JSON NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (episode_id) REFERENCES video_episodes(id) ON DELETE CASCADE,
            UNIQUE KEY uk_video_shot_number (episode_id, shot_no),
            INDEX idx_video_shot_prompt (prompt_id),
            INDEX idx_video_shot_status (status, updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        echo "Created table video_shots\n";
    }

    // 旧版项目没有保存对白。为升级成普通话的项目补齐每个镜头的短句，避免保留 H3 含糊原声。
    $dialogueBackfillCount = 0;
    $mandarinProjects = Db::query("SELECT * FROM video_projects WHERE voice_language = 'zh-CN'");
    foreach ($mandarinProjects as $mandarinProject) {
        $plan = ShortDramaPlannerService::plan(
            (string) $mandarinProject['idea'],
            (int) $mandarinProject['episode_duration'],
            (string) $mandarinProject['aspect_ratio'],
            (string) $mandarinProject['style'],
            'zh-CN',
            (string) ($mandarinProject['character_origin'] ?? ShortDramaPlannerService::CHARACTER_ORIGIN_EAST_ASIAN),
            (string) ($mandarinProject['screen_text_language'] ?? ShortDramaPlannerService::SCREEN_TEXT_LANGUAGE_NONE),
            (string) ($mandarinProject['shot_duration_mode'] ?? ShortDramaPlannerService::SHOT_DURATION_FIVE)
        );
        $projectShots = Db::query(
            'SELECT s.id, s.shot_no, s.dialogue FROM video_shots s '
                . 'INNER JOIN video_episodes e ON e.id = s.episode_id '
                . 'WHERE e.project_id = ? ORDER BY s.shot_no',
            [(int) $mandarinProject['id']]
        );
        foreach ($projectShots as $projectShot) {
            if (trim((string) ($projectShot['dialogue'] ?? '')) !== '') {
                continue;
            }
            $plannedShot = $plan['shots'][max(0, (int) $projectShot['shot_no'] - 1)] ?? null;
            if (!is_array($plannedShot) || trim((string) ($plannedShot['dialogue'] ?? '')) === '') {
                continue;
            }
            Db::execute(
                'UPDATE video_shots SET dialogue = ? WHERE id = ?',
                [(string) $plannedShot['dialogue'], (int) $projectShot['id']]
            );
            $dialogueBackfillCount++;
        }
    }
    if ($dialogueBackfillCount > 0) {
        echo "Backfilled {$dialogueBackfillCount} Mandarin shot dialogues\n";
    }

    $features = SettingsService::getFeatures();
    $features['short_drama'] = $features['short_drama'] ?? true;
    SettingsService::set('features', $features);

    echo "Short drama migration completed.\n";
} catch (\Throwable $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
    exit(1);
}