"""Backfill user_preferences rows and normalize language values for multilingual support (PRD-HWUI-004)

Revision ID: hwui004_backfill_user_language
Revises: prd010_add_invoice_split_id
Create Date: 2026-05-04

- Inserts missing user_preferences rows for any users record with no corresponding row
  (defaults: language='en', date_format='MM/DD/YYYY', time_format='12h',
   timezone='America/New_York', currency_display='symbol',
   notifications_email=true, notifications_sms=false, created_at=NOW())
- Updates existing rows where language IS NULL or language NOT IN ('en','es','fr','it')
  to language='en'
- Idempotent: safe to run multiple times
- Downgrade: no-op (data-only migration, no safe rollback)
"""

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic
revision = "hwui004_backfill_user_language"
down_revision = "prd010_add_invoice_split_id"
branch_labels = None
depends_on = None

# Canonical language set as of PRD-HWUI-004
_ALLOWED_LANGUAGES = ("en", "es", "fr", "it")


def upgrade() -> None:
    # -------------------------------------------------------------------
    # Step 1: Insert missing user_preferences rows for any users that
    #         have no corresponding row yet.
    # -------------------------------------------------------------------
    op.execute(
        sa.text(
            """
            INSERT INTO user_preferences (
                user_id,
                language,
                date_format,
                time_format,
                timezone,
                currency_display,
                notifications_email,
                notifications_sms,
                created_at
            )
            SELECT
                u.id,
                'en',
                'MM/DD/YYYY',
                '12h',
                'America/New_York',
                'symbol',
                TRUE,
                FALSE,
                NOW()
            FROM users u
            WHERE NOT EXISTS (
                SELECT 1
                FROM user_preferences up
                WHERE up.user_id = u.id
            )
            """
        )
    )

    # -------------------------------------------------------------------
    # Step 2: Normalise any existing row whose language is NULL or not in
    #         the new allowed set ('en', 'es', 'fr', 'it').
    # -------------------------------------------------------------------
    op.execute(
        sa.text(
            """
            UPDATE user_preferences
            SET language = 'en'
            WHERE language IS NULL
               OR language NOT IN ('en', 'es', 'fr', 'it')
            """
        )
    )


def downgrade() -> None:
    # Data-only migration — no safe rollback possible.
    pass