"""migrate old dictionary entries and translation records

Revision ID: 543d43d752b8
Revises: c4a3a2f74bfe
Create Date: 2026-05-14 18:00:00.000000

"""
from collections.abc import Sequence
from datetime import UTC

from sqlalchemy.sql import text

from alembic import op

# revision identifiers, used by Alembic.
revision: str = '543d43d752b8'
down_revision: str | Sequence[str] | None = 'c4a3a2f74bfe'
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Upgrade schema — migrate old dictionary entries and create TranslationLanguage records."""

    connection = op.get_bind()

    # === Part 1: Migrate DictionaryEntry rows without language pair ===

    # 1a. Set source_language = 'und' for rows where it's NULL or empty
    connection.execute(
        text("""
            UPDATE dictionary_entries
            SET source_language = 'und'
            WHERE source_language IS NULL OR source_language = '' OR source_language = 'und'
        """)
    )

    # 1b. Set target_language from parent dictionary for rows where it's NULL, empty, or 'und'
    connection.execute(
        text("""
            UPDATE dictionary_entries
            SET target_language = COALESCE(
                (SELECT td.target_language FROM terminology_dictionaries td
                 WHERE td.id = dictionary_entries.dictionary_id),
                'und'
            )
            WHERE target_language IS NULL
               OR target_language = ''
               OR target_language = 'und'
        """)
    )

    # 1c. Set target_language default fallback in case dictionary's own target_language is also NULL/empty
    connection.execute(
        text("""
            UPDATE dictionary_entries
            SET target_language = 'und'
            WHERE target_language IS NULL OR target_language = ''
        """)
    )

    # === Part 2: Create TranslationLanguage entries for old TranslationRecord rows ===
    # Records that have deprecated fields (final_value, llm_translation, user_edit) populated
    # but no corresponding TranslationLanguage row

    # Determine target_language from the parent run's job
    # We use a two-step approach: find records missing TranslationLanguage rows,
    # then create one per record using the default language code from the job config
    missing = connection.execute(
        text("""
            SELECT r.id AS record_id,
                   r.run_id,
                   COALESCE(r.final_value, r.llm_translation, r.target_sql, '') AS resolved_value,
                   COALESCE(r.user_edit, r.llm_translation, r.target_sql, '') AS resolved_edit,
                   r.status
            FROM translation_records r
            WHERE r.status = 'SUCCESS'
              AND (r.final_value IS NOT NULL OR r.llm_translation IS NOT NULL OR r.target_sql IS NOT NULL)
              AND NOT EXISTS (
                  SELECT 1 FROM translation_languages tl
                  WHERE tl.record_id = r.id
              )
        """)
    ).fetchall()

    from datetime import datetime
    import uuid

    for row in missing:
        record_id = row[0]
        resolved_value = row[2] or ""
        resolved_edit = row[3] or ""
        status = row[4] or "SUCCESS"

        # Map record status to language status
        lang_status = "translated"
        if status in ("APPROVED", "approved"):
            lang_status = "approved"
        elif status in ("EDITED", "edited"):
            lang_status = "edited"

        # Determine language code from job
        lang_code_row = connection.execute(
            text("""
                SELECT j.target_language, j.target_languages
                FROM translation_records r
                JOIN translation_runs rn ON rn.id = r.run_id
                JOIN translation_jobs j ON j.id = rn.job_id
                WHERE r.id = :rid
            """),
            {"rid": record_id},
        ).fetchone()

        language_code = "und"
        if lang_code_row:
            lang_code = lang_code_row[0]
            lang_codes_json = lang_code_row[1]

            if lang_code:
                language_code = lang_code
            elif lang_codes_json:
                try:
                    import json
                    codes = json.loads(lang_codes_json) if isinstance(lang_codes_json, str) else lang_codes_json
                    if isinstance(codes, list) and codes:
                        language_code = codes[0]
                except (json.JSONDecodeError, TypeError):
                    pass

        # Determine final value (prefer user_edit over llm_translation)
        final_value = resolved_edit if resolved_edit else resolved_value

        now = datetime.now(UTC)
        lang_id = str(uuid.uuid4())

        connection.execute(
            text("""
                INSERT INTO translation_languages
                    (id, record_id, language_code, source_language_detected,
                     translated_value, user_edit, final_value, status, created_at)
                VALUES
                    (:id, :record_id, :language_code, 'und',
                     :translated_value, :user_edit, :final_value, :status, :created_at)
            """),
            {
                "id": lang_id,
                "record_id": record_id,
                "language_code": language_code,
                "translated_value": resolved_value or "",
                "user_edit": resolved_edit or "",
                "final_value": final_value or "",
                "status": lang_status,
                "created_at": now,
            },
        )


def downgrade() -> None:
    """Downgrade — reverse the migration (delete created TranslationLanguage rows, restore old values)."""
    connection = op.get_bind()

    # Remove TranslationLanguage entries that were created by this migration
    # (those linked to records that had no existing TranslationLanguage rows)
    connection.execute(
        text("""
            DELETE FROM translation_languages
            WHERE id IN (
                SELECT tl.id FROM translation_languages tl
                JOIN translation_records r ON r.id = tl.record_id
                WHERE r.created_at < '2026-05-15'
                  AND tl.created_at >= '2026-05-14T18:00:00'
            )
        """)
    )

    # Note: dictionary_entries language updates are intentionally NOT reverted
    # because 'und' is an acceptable safe default, and reverting could break
    # unique constraints if entries were duplicated.
