"""Create translation tables for multi-language support

Revision ID: f2b3c4d5e6f7
Revises: e1a2b3c4d5e6
Create Date: 2025-12-11

This migration creates:
- marketplace_product_translations: Localized content from marketplace sources
- product_translations: Vendor-specific localized overrides

The translation tables support multi-language product information with
language fallback capabilities. Fields in product_translations can be
NULL to inherit from marketplace_product_translations.
"""

from collections.abc import Sequence

import sqlalchemy as sa

from alembic import op

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


def upgrade() -> None:
    # Create marketplace_product_translations table
    # Note: Unique constraint is included in create_table for SQLite compatibility
    op.create_table(
        "marketplace_product_translations",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "marketplace_product_id",
            sa.Integer(),
            sa.ForeignKey("marketplace_products.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("language", sa.String(5), nullable=False),
        # Localized content
        sa.Column("title", sa.String(), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("short_description", sa.String(500), nullable=True),
        # SEO fields
        sa.Column("meta_title", sa.String(70), nullable=True),
        sa.Column("meta_description", sa.String(160), nullable=True),
        sa.Column("url_slug", sa.String(255), nullable=True),
        # Source tracking
        sa.Column("source_import_id", sa.Integer(), nullable=True),
        sa.Column("source_file", sa.String(), nullable=True),
        # Timestamps
        sa.Column(
            "created_at",
            sa.DateTime(),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        # Unique constraint included in table creation for SQLite
        sa.UniqueConstraint(
            "marketplace_product_id",
            "language",
            name="uq_marketplace_product_translation",
        ),
    )

    # Create indexes for marketplace_product_translations
    op.create_index(
        "idx_mpt_marketplace_product_id",
        "marketplace_product_translations",
        ["marketplace_product_id"],
    )
    op.create_index(
        "idx_mpt_language",
        "marketplace_product_translations",
        ["language"],
    )

    # Create product_translations table
    # Note: Unique constraint is included in create_table for SQLite compatibility
    op.create_table(
        "product_translations",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column(
            "product_id",
            sa.Integer(),
            sa.ForeignKey("products.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("language", sa.String(5), nullable=False),
        # Overridable localized content (NULL = inherit from marketplace)
        sa.Column("title", sa.String(), nullable=True),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("short_description", sa.String(500), nullable=True),
        # SEO overrides
        sa.Column("meta_title", sa.String(70), nullable=True),
        sa.Column("meta_description", sa.String(160), nullable=True),
        sa.Column("url_slug", sa.String(255), nullable=True),
        # Timestamps
        sa.Column(
            "created_at",
            sa.DateTime(),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        # Unique constraint included in table creation for SQLite
        sa.UniqueConstraint("product_id", "language", name="uq_product_translation"),
    )

    # Create indexes for product_translations
    op.create_index(
        "idx_pt_product_id",
        "product_translations",
        ["product_id"],
    )
    op.create_index(
        "idx_pt_product_language",
        "product_translations",
        ["product_id", "language"],
    )


def downgrade() -> None:
    # Drop product_translations table and its indexes
    op.drop_index("idx_pt_product_language", table_name="product_translations")
    op.drop_index("idx_pt_product_id", table_name="product_translations")
    op.drop_table("product_translations")

    # Drop marketplace_product_translations table and its indexes
    op.drop_index("idx_mpt_language", table_name="marketplace_product_translations")
    op.drop_index(
        "idx_mpt_marketplace_product_id", table_name="marketplace_product_translations"
    )
    op.drop_table("marketplace_product_translations")