"""Phase 1 - 核心重构：清理废弃表

Revision ID: 011
Revises: 010
Create Date: 2026-06-28

This migration:
1. Drops 6 deprecated tables:
   - tasks (任务管理)
   - milestones (里程碑)
   - task_issues (任务问题)
   - project_titles (项目标题，已被 title_libraries 替代)
   - edit_plans (编辑计划)
   - edit_plan_clips (编辑计划片段)
2. Removes edit_plan_id column from generation_tasks table
"""

import sqlalchemy as sa

from alembic import op

# revision identifiers
revision = "011"
down_revision = "010"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()

    # ── 1. Drop deprecated tables ──

    # Drop in reverse dependency order
    conn.execute(sa.text("DROP TABLE IF EXISTS task_issues"))
    conn.execute(sa.text("DROP TABLE IF EXISTS milestones"))
    conn.execute(sa.text("DROP TABLE IF EXISTS tasks"))
    conn.execute(sa.text("DROP TABLE IF EXISTS project_titles"))
    conn.execute(sa.text("DROP TABLE IF EXISTS edit_plan_clips"))
    conn.execute(sa.text("DROP TABLE IF EXISTS edit_plans"))

    # ── 2. Remove edit_plan_id from generation_tasks ──

    conn.execute(sa.text("ALTER TABLE generation_tasks DROP COLUMN IF EXISTS edit_plan_id"))


def downgrade() -> None:
    conn = op.get_bind()

    # ── 1. Re-add edit_plan_id to generation_tasks ──

    conn.execute(sa.text("ALTER TABLE generation_tasks ADD COLUMN IF NOT EXISTS edit_plan_id VARCHAR(32)"))

    # ── 2. Recreate deprecated tables (basic structure) ──

    # Note: Full schema recreation is complex; this is a minimal downgrade
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS edit_plans (
            id VARCHAR(32) PRIMARY KEY,
            project_id VARCHAR(32) NOT NULL,
            name VARCHAR(255) NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            status VARCHAR(20) NOT NULL DEFAULT 'draft',
            created_by_user_id VARCHAR(32) NOT NULL DEFAULT '',
            metadata JSONB NOT NULL DEFAULT '{}',
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS edit_plan_clips (
            id VARCHAR(32) PRIMARY KEY,
            edit_plan_id VARCHAR(32) NOT NULL,
            asset_id VARCHAR(32) NOT NULL,
            order_index INTEGER NOT NULL DEFAULT 0,
            start_time FLOAT NOT NULL DEFAULT 0,
            end_time FLOAT NOT NULL DEFAULT 0,
            metadata JSONB NOT NULL DEFAULT '{}',
            created_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS project_titles (
            id VARCHAR(36) PRIMARY KEY,
            project_id VARCHAR(36) NOT NULL,
            text VARCHAR(500) NOT NULL,
            category VARCHAR(50) NOT NULL DEFAULT 'default',
            source VARCHAR(20) NOT NULL DEFAULT 'manual',
            tags JSONB NOT NULL DEFAULT '[]',
            favorite BOOLEAN NOT NULL DEFAULT FALSE,
            usage_count INTEGER NOT NULL DEFAULT 0,
            metadata JSONB NOT NULL DEFAULT '{}',
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS tasks (
            id VARCHAR(32) PRIMARY KEY,
            project_id VARCHAR(32) NOT NULL,
            title VARCHAR(255) NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            priority VARCHAR(20) NOT NULL DEFAULT 'medium',
            assigned_to_user_id VARCHAR(32) NOT NULL DEFAULT '',
            due_date TIMESTAMP,
            metadata JSONB NOT NULL DEFAULT '{}',
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS milestones (
            id VARCHAR(32) PRIMARY KEY,
            project_id VARCHAR(32) NOT NULL,
            name VARCHAR(255) NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            due_date TIMESTAMP,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            metadata JSONB NOT NULL DEFAULT '{}',
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS task_issues (
            id VARCHAR(32) PRIMARY KEY,
            task_id VARCHAR(32) NOT NULL,
            title VARCHAR(255) NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            status VARCHAR(20) NOT NULL DEFAULT 'open',
            priority VARCHAR(20) NOT NULL DEFAULT 'medium',
            metadata JSONB NOT NULL DEFAULT '{}',
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))
