"""
migrate_001_ai_outfit_tables.py
────────────────────────────────────────────────────────────
Migration: Tạo 2 bảng ai_outfit_set + ai_outfit_items

  - Tự động chọn backend dựa theo env USE_LOCAL_SQLITE:
      USE_LOCAL_SQLITE=true  → SQLite  (local dev / home)
      USE_LOCAL_SQLITE=false → Postgres (company / production)

Cách chạy:
    python backend/database/migrate/migrate_001_ai_outfit_tables.py

Hoặc được gọi tự động qua outfit_db.ensure_tables() khi server khởi động.
"""

import logging
import os
import sys

logger = logging.getLogger(__name__)

SCHEMA = "dashboard_canifa"
SET_TABLE   = f"{SCHEMA}.ai_outfit_set"
ITEMS_TABLE = f"{SCHEMA}.ai_outfit_items"

USE_SQLITE = os.getenv("USE_LOCAL_SQLITE", "false").strip().lower() == "true"


# ─── SQLite DDL (không có schema prefix, không có TIMESTAMPTZ) ────────────────
SQLITE_DDL = """
CREATE TABLE IF NOT EXISTS ai_outfit_set (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    source_code  TEXT    NOT NULL,
    occasion_tag TEXT    NOT NULL,
    created_at   TEXT    DEFAULT (datetime('now')),
    updated_at   TEXT    DEFAULT (datetime('now')),
    UNIQUE (source_code, occasion_tag)
);

CREATE INDEX IF NOT EXISTS idx_outfit_set_source
    ON ai_outfit_set(source_code);

CREATE TABLE IF NOT EXISTS ai_outfit_items (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    outfit_id   INTEGER NOT NULL REFERENCES ai_outfit_set(id) ON DELETE CASCADE,
    target_code TEXT    NOT NULL,
    role        TEXT    NOT NULL,
    rank        INTEGER NOT NULL DEFAULT 1,
    score       INTEGER DEFAULT 0,
    reason      TEXT    DEFAULT '',
    is_pinned   INTEGER DEFAULT 0,
    created_at  TEXT    DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_outfit_items_outfit_id
    ON ai_outfit_items(outfit_id);

CREATE INDEX IF NOT EXISTS idx_outfit_items_target
    ON ai_outfit_items(target_code);
"""


# ─── Postgres DDL ─────────────────────────────────────────────────────────────
POSTGRES_DDL = f"""
CREATE SCHEMA IF NOT EXISTS {SCHEMA};

CREATE TABLE IF NOT EXISTS {SET_TABLE} (
    id           SERIAL PRIMARY KEY,
    source_code  VARCHAR(50)  NOT NULL,
    occasion_tag VARCHAR(50)  NOT NULL,
    created_at   TIMESTAMPTZ  DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  DEFAULT NOW(),
    UNIQUE (source_code, occasion_tag)
);

CREATE INDEX IF NOT EXISTS idx_outfit_set_source
    ON {SET_TABLE}(source_code);

CREATE TABLE IF NOT EXISTS {ITEMS_TABLE} (
    id          SERIAL PRIMARY KEY,
    outfit_id   INT          NOT NULL REFERENCES {SET_TABLE}(id) ON DELETE CASCADE,
    target_code VARCHAR(50)  NOT NULL,
    role        VARCHAR(30)  NOT NULL,
    rank        SMALLINT     NOT NULL DEFAULT 1,
    score       SMALLINT     DEFAULT 0,
    reason      TEXT         DEFAULT '',
    is_pinned   BOOLEAN      DEFAULT FALSE,
    created_at  TIMESTAMPTZ  DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_outfit_items_outfit_id
    ON {ITEMS_TABLE}(outfit_id);

CREATE INDEX IF NOT EXISTS idx_outfit_items_target
    ON {ITEMS_TABLE}(target_code);
"""


def run_sqlite():
    """Tạo bảng trên SQLite local."""
    sqlite_path = os.getenv(
        "SQLITE_PATH",
        os.path.join(os.path.dirname(__file__), "..", "canifa_ai_dump.sqlite")
    )
    sqlite_path = os.path.abspath(sqlite_path)

    import sqlite3
    conn = sqlite3.connect(sqlite_path)
    try:
        conn.executescript(SQLITE_DDL)
        conn.commit()
        logger.info("[migrate] ✅ SQLite: ai_outfit_set + ai_outfit_items created at %s", sqlite_path)
        print(f"[OK] SQLite tables created: {sqlite_path}")
    except Exception as e:
        logger.error("[migrate] SQLite error: %s", e)
        print(f"[ERROR] SQLite: {e}")
    finally:
        conn.close()


def run_postgres():
    """Tạo bảng trên Postgres qua pool_wrapper."""
    # Thêm thư mục backend vào sys.path để import được common.*
    backend_dir = os.path.abspath(os.path.join(os.path.dirname(__file__), "..", ".."))
    if backend_dir not in sys.path:
        sys.path.insert(0, backend_dir)

    from common.pool_wrapper import get_pooled_connection_compat
    conn = None
    try:
        conn = get_pooled_connection_compat()
        cur = conn.cursor()
        # Chạy từng statement riêng để tránh lỗi "can't execute multiple commands"
        for stmt in POSTGRES_DDL.split(";"):
            stmt = stmt.strip()
            if stmt:
                cur.execute(stmt)
        conn.commit()
        cur.close()
        logger.info("[migrate] ✅ Postgres: ai_outfit_set + ai_outfit_items created")
        print("[OK] Postgres tables created: dashboard_canifa.ai_outfit_set + dashboard_canifa.ai_outfit_items")
    except Exception as e:
        if conn:
            conn.rollback()
        logger.error("[migrate] Postgres error: %s", e)
        print(f"[ERROR] Postgres: {e}")
    finally:
        if conn:
            conn.close()


def run():
    """Entry point: tự chọn backend theo USE_LOCAL_SQLITE."""
    logging.basicConfig(level=logging.INFO)
    if USE_SQLITE:
        print("[migrate] Mode: SQLite (USE_LOCAL_SQLITE=true)")
        run_sqlite()
    else:
        print("[migrate] Mode: Postgres (USE_LOCAL_SQLITE=false)")
        run_postgres()


if __name__ == "__main__":
    run()
