"""Phase 3 - 剪辑计划模板：templates + template_segments + template_categories

Revision ID: 014
Revises: 013
Create Date: 2026-06-29

This migration creates three new tables:
1. templates — 剪辑计划模板主表
2. template_segments — 模板片段表
3. template_categories — 模板分类表
"""

import sqlalchemy as sa

from alembic import op

# revision identifiers
revision = "014"
down_revision = "013"
branch_labels = None
depends_on = None


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

    # ── 1. Create templates table ──
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS templates (
            id VARCHAR(36) PRIMARY KEY,
            user_id VARCHAR(36) NOT NULL,
            name VARCHAR(200) NOT NULL,
            mode VARCHAR(30) NOT NULL,
            category VARCHAR(100) NOT NULL DEFAULT '',
            tags JSONB NOT NULL DEFAULT '[]',
            title_config JSONB NOT NULL DEFAULT '{}',
            subtitle_config JSONB NOT NULL DEFAULT '{}',
            bgm_config JSONB NOT NULL DEFAULT '{}',
            estimated_duration FLOAT NOT NULL DEFAULT 0.0,
            is_active BOOLEAN NOT NULL DEFAULT TRUE,
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))
    conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_templates_user_id ON templates(user_id)"))
    conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_templates_mode ON templates(mode)"))

    # ── 2. Create template_segments table ──
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS template_segments (
            id VARCHAR(36) PRIMARY KEY,
            template_id VARCHAR(36) NOT NULL,
            segment_order INTEGER NOT NULL,
            duration_min FLOAT NOT NULL,
            duration_max FLOAT NOT NULL,
            material_type VARCHAR(20),
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))
    conn.execute(
        sa.text("CREATE INDEX IF NOT EXISTS ix_template_segments_template_id " "ON template_segments(template_id)")
    )

    # ── 3. Create template_categories table ──
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS template_categories (
            id VARCHAR(36) PRIMARY KEY,
            user_id VARCHAR(36) NOT NULL,
            name VARCHAR(100) NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))
    conn.execute(
        sa.text("CREATE INDEX IF NOT EXISTS ix_template_categories_user_id " "ON template_categories(user_id)")
    )


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("DROP TABLE IF EXISTS template_categories"))
    conn.execute(sa.text("DROP TABLE IF EXISTS template_segments"))
    conn.execute(sa.text("DROP TABLE IF EXISTS templates"))