"""Additive car dealer migration — adds car-specific columns to listings,
listing_images, ai_generations, and site_settings tables.

Usage from project root:
    python migrations/20260718_car_dealer.py

The application also invokes the same idempotent migration during init_db().
"""
from pathlib import Path
import shutil
import sqlite3
import sys

ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))

DB = ROOT / "instance" / "inventory.sqlite3"
BACKUP = ROOT / "instance" / "inventory.pre_car_dealer_20260718.sqlite3"


def migrate_car_dealer_schema(db):
    """Additive migration — adds car-specific columns to existing tables."""

    # ── listings: car-specific fields ──────────────────────────────────────
    listing_columns = {
        "vin": "TEXT",
        "odometer": "INTEGER",
        "transmission": "TEXT",
        "fuel_type": "TEXT",
        "body_style": "TEXT",
        "drive_line": "TEXT",
        "exterior_color": "TEXT",
        "interior_color": "TEXT",
        "doors": "INTEGER",
        "engine": "TEXT",
        "trim": "TEXT",
        "certified": "INTEGER DEFAULT 0",
        "carfax_url": "TEXT",
        "safety_certified": "INTEGER DEFAULT 0",
        "msrp": "REAL",
        "savings": "REAL",
        "ai_description_score": "REAL",
        "ai_description_keywords": "TEXT",
        "ai_description_status": "TEXT DEFAULT ''",
    }
    for name, definition in listing_columns.items():
        _add_column_if_missing(db, "listings", name, definition)

    # ── listing_images: car-specific image metadata ────────────────────────
    image_columns = {
        "image_role": "TEXT",
        "view_angle": "TEXT",
    }
    for name, definition in image_columns.items():
        _add_column_if_missing(db, "listing_images", name, definition)

    # ── ai_generations: QA tracking ───────────────────────────────────────
    ai_gen_columns = {
        "qa_score": "REAL",
        "qa_issues": "TEXT",
        "generation_type": "TEXT",
        "attempt": "INTEGER DEFAULT 1",
    }
    for name, definition in ai_gen_columns.items():
        _add_column_if_missing(db, "ai_generations", name, definition)

    # ── site_settings: car-dealer specific settings ────────────────────────
    settings_columns = {
        "dealer_license_number": "TEXT",
        "dealer_license_url": "TEXT",
        "dealer_province": "TEXT DEFAULT 'ON'",
        "dealer_city": "TEXT DEFAULT 'Toronto'",
        "hst_number": "TEXT",
        "financing_enabled": "INTEGER DEFAULT 0",
    }
    for name, definition in settings_columns.items():
        _add_column_if_missing(db, "site_settings", name, definition)

    # Set defaults
    db.execute("UPDATE site_settings SET dealer_province = 'ON' WHERE dealer_province IS NULL OR dealer_province = ''")
    db.execute("UPDATE site_settings SET dealer_city = 'Toronto' WHERE dealer_city IS NULL OR dealer_city = ''")
    db.execute("UPDATE site_settings SET financing_enabled = 0 WHERE financing_enabled IS NULL")

    # Migrate old trust columns to new car-dealer equivalents
    for old_col, new_col in [
        ("companies_house_url", "dealer_license_url"),
        ("company_registration_number", "dealer_license_number"),
        ("vat_number", "hst_number"),
    ]:
        if old_col in _table_columns(db, "site_settings"):
            try:
                db.execute(
                    f"UPDATE site_settings SET {new_col} = {old_col} "
                    f"WHERE ({new_col} IS NULL OR {new_col} = '') AND {old_col} IS NOT NULL AND {old_col} != ''"
                )
            except Exception:
                pass

    db.commit()
    print("Car dealer migration complete.")


def _table_columns(db, table_name):
    rows = db.execute(f"PRAGMA table_info({table_name})").fetchall()
    return {row[1] for row in rows}


def _add_column_if_missing(db, table_name, column_name, definition):
    if column_name not in _table_columns(db, table_name):
        db.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} {definition}")
        print(f"  Added {table_name}.{column_name} ({definition})")


if __name__ == "__main__":
    if not DB.exists():
        raise SystemExit(f"Database not found: {DB}")
    if not BACKUP.exists():
        shutil.copy2(DB, BACKUP)
        print(f"Backup created: {BACKUP}")

    connection = sqlite3.connect(str(DB))
    try:
        migrate_car_dealer_schema(connection)
    finally:
        connection.close()
    print("Done.")