"""Phase 2 - 查重功能：duplication_records + duplication_segments

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

This migration creates two new tables:
1. duplication_records — 查重记录主表
2. duplication_segments — 重复片段详情表
"""

import sqlalchemy as sa

from alembic import op

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


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

    # ── 1. Create duplication_records table ──

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS duplication_records (
            id VARCHAR(36) PRIMARY KEY,
            user_id VARCHAR(36) NOT NULL,
            filename VARCHAR(500) NOT NULL,
            file_size INTEGER NOT NULL,
            storage_key VARCHAR(500) NOT NULL,
            duration_seconds FLOAT NOT NULL DEFAULT 0,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            duplicate_rate FLOAT,
            duplicate_count INTEGER NOT NULL DEFAULT 0,
            video_fingerprint TEXT,
            error_message TEXT NOT NULL DEFAULT '',
            created_at TIMESTAMP NOT NULL DEFAULT NOW(),
            updated_at TIMESTAMP NOT NULL DEFAULT NOW()
        )
    """))
    conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_duplication_records_user_id ON duplication_records(user_id)"))
    conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_duplication_records_status ON duplication_records(status)"))

    # ── 2. Create duplication_segments table ──

    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS duplication_segments (
            id VARCHAR(36) PRIMARY KEY,
            record_id VARCHAR(36) NOT NULL,
            source_start FLOAT NOT NULL,
            source_end FLOAT NOT NULL,
            matched_video_id VARCHAR(36) NOT NULL,
            matched_video_name VARCHAR(500) NOT NULL DEFAULT '',
            matched_start FLOAT NOT NULL,
            matched_end FLOAT NOT NULL,
            similarity FLOAT NOT NULL
        )
    """))
    conn.execute(
        sa.text("CREATE INDEX IF NOT EXISTS ix_duplication_segments_record_id ON duplication_segments(record_id)")
    )


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