"""Lightweight SQLite column adds for evolving schema without Alembic."""

from __future__ import annotations

from sqlalchemy import text
from sqlalchemy.engine import Engine


JOB_COLUMNS = {
    "contact_email": "ALTER TABLE jobs ADD COLUMN contact_email VARCHAR(255)",
    "contact_phone": "ALTER TABLE jobs ADD COLUMN contact_phone VARCHAR(64)",
    "contact_name": "ALTER TABLE jobs ADD COLUMN contact_name VARCHAR(255)",
    "contact_linkedin": "ALTER TABLE jobs ADD COLUMN contact_linkedin VARCHAR(500)",
    "contacts_json": "ALTER TABLE jobs ADD COLUMN contacts_json JSON",
}

CV_VERSION_COLUMNS = {
    "review_status": "ALTER TABLE cv_versions ADD COLUMN review_status VARCHAR(32) DEFAULT 'draft'",
    "base_snapshot_md": "ALTER TABLE cv_versions ADD COLUMN base_snapshot_md TEXT DEFAULT ''",
    "sections_json": "ALTER TABLE cv_versions ADD COLUMN sections_json JSON",
}

PROFILE_COLUMNS = {
    "desired_roles": "ALTER TABLE profiles ADD COLUMN desired_roles JSON",
    "job_categories": "ALTER TABLE profiles ADD COLUMN job_categories JSON",
    "employment_types": "ALTER TABLE profiles ADD COLUMN employment_types JSON",
}

JOB_SOURCE_COLUMNS = {
    "region": "ALTER TABLE job_sources ADD COLUMN region VARCHAR(16) DEFAULT 'intl'",
    "category": "ALTER TABLE job_sources ADD COLUMN category VARCHAR(32) DEFAULT 'general'",
    "tier_min": "ALTER TABLE job_sources ADD COLUMN tier_min INTEGER DEFAULT 0",
    "priority": "ALTER TABLE job_sources ADD COLUMN priority INTEGER DEFAULT 100",
    "connector_type": "ALTER TABLE job_sources ADD COLUMN connector_type VARCHAR(16) DEFAULT 'html'",
}

USER_COLUMNS = {
    "privacy_accepted_at": "ALTER TABLE users ADD COLUMN privacy_accepted_at DATETIME",
    "marketing_consent": "ALTER TABLE users ADD COLUMN marketing_consent BOOLEAN DEFAULT 0",
    "referral_code": "ALTER TABLE users ADD COLUMN referral_code VARCHAR(32)",
    "referred_by": "ALTER TABLE users ADD COLUMN referred_by VARCHAR(32)",
    "utm_source": "ALTER TABLE users ADD COLUMN utm_source VARCHAR(64)",
    "utm_medium": "ALTER TABLE users ADD COLUMN utm_medium VARCHAR(64)",
    "utm_campaign": "ALTER TABLE users ADD COLUMN utm_campaign VARCHAR(128)",
    "onboarding_done": "ALTER TABLE users ADD COLUMN onboarding_done BOOLEAN DEFAULT 0",
    "active_persona_id": "ALTER TABLE users ADD COLUMN active_persona_id INTEGER",
    "telegram_chat_id": "ALTER TABLE users ADD COLUMN telegram_chat_id VARCHAR(64)",
}


def _existing_columns(engine: Engine, table: str) -> set[str]:
    with engine.connect() as conn:
        rows = conn.execute(text(f"PRAGMA table_info({table})")).fetchall()
    return {r[1] for r in rows}


def _add_missing(conn, table: str, columns: dict[str, str]) -> None:
    try:
        existing = {r[1] for r in conn.execute(text(f"PRAGMA table_info({table})")).fetchall()}
    except Exception:
        return
    for name, sql in columns.items():
        if name not in existing:
            conn.execute(text(sql))


def migrate_sqlite(engine: Engine) -> None:
    if not str(engine.url).startswith("sqlite"):
        return
    try:
        _existing_columns(engine, "jobs")
    except Exception:
        return
    with engine.begin() as conn:
        _add_missing(conn, "jobs", JOB_COLUMNS)
        _add_missing(conn, "cv_versions", CV_VERSION_COLUMNS)
        _add_missing(conn, "profiles", PROFILE_COLUMNS)
        _add_missing(conn, "job_sources", JOB_SOURCE_COLUMNS)
        _add_missing(conn, "users", USER_COLUMNS)
        try:
            conn.execute(text("UPDATE jobs SET contacts_json = '{}' WHERE contacts_json IS NULL"))
        except Exception:
            pass
        try:
            conn.execute(text("UPDATE cv_versions SET sections_json = '{}' WHERE sections_json IS NULL"))
            conn.execute(text("UPDATE cv_versions SET review_status = 'draft' WHERE review_status IS NULL"))
            conn.execute(text("UPDATE cv_versions SET base_snapshot_md = '' WHERE base_snapshot_md IS NULL"))
        except Exception:
            pass
        try:
            conn.execute(text("UPDATE profiles SET desired_roles = '[]' WHERE desired_roles IS NULL"))
            conn.execute(text("UPDATE profiles SET job_categories = '[]' WHERE job_categories IS NULL"))
            conn.execute(text("UPDATE profiles SET employment_types = '[]' WHERE employment_types IS NULL"))
        except Exception:
            pass
        try:
            conn.execute(text("UPDATE job_sources SET region = 'intl' WHERE region IS NULL"))
            conn.execute(text("UPDATE job_sources SET category = 'general' WHERE category IS NULL"))
            conn.execute(text("UPDATE job_sources SET tier_min = 0 WHERE tier_min IS NULL"))
            conn.execute(text("UPDATE job_sources SET priority = 100 WHERE priority IS NULL"))
            conn.execute(text("UPDATE job_sources SET connector_type = 'html' WHERE connector_type IS NULL"))
        except Exception:
            pass