"""Add source_hash column to translation_records for cache dedup

Adds source_hash (SHA256 of source_text + source_data + dict/config hashes)
enabling cache-hit lookups: if the same source row + dictionary + config
combination has already been successfully translated, the LLM call is skipped.

Revision ID: b1c2d3e4f5a6
Revises: aa1b2c3d4e5f
Create Date: 2026-05-15 23:00:00.000000

"""
from collections.abc import Sequence

import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "b1c2d3e4f5a6"
down_revision: str | Sequence[str] | None = "aa1b2c3d4e5f"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Add source_hash column with indices."""
    bind = op.get_bind()
    if bind.engine.name == "sqlite":
        # SQLite — check existence first
        inspector = sa.inspect(bind)
        columns = [c["name"] for c in inspector.get_columns("translation_records")]
        if "source_hash" not in columns:
            op.add_column("translation_records",
                          sa.Column("source_hash", sa.String(), nullable=True,
                                    comment="SHA256(source_text+source_data+dict_snapshot_hash+config_hash) for cache dedup"))
            op.create_index("ix_translation_records_source_hash_status",
                            "translation_records", ["source_hash", "status"])
    else:
        # PostgreSQL and others
        op.add_column("translation_records",
                      sa.Column("source_hash", sa.String(), nullable=True,
                                comment="SHA256(source_text+source_data+dict_snapshot_hash+config_hash) for cache dedup"))
        op.create_index("ix_translation_records_source_hash_status",
                        "translation_records", ["source_hash", "status"])


def downgrade() -> None:
    """Drop source_hash column and index."""
    bind = op.get_bind()
    if bind.engine.name == "sqlite":
        inspector = sa.inspect(bind)
        columns = [c["name"] for c in inspector.get_columns("translation_records")]
        if "source_hash" in columns:
            op.drop_index("ix_translation_records_source_hash_status",
                          table_name="translation_records")
            op.drop_column("translation_records", "source_hash")
    else:
        op.drop_index("ix_translation_records_source_hash_status",
                      table_name="translation_records")
        op.drop_column("translation_records", "source_hash")
