"""ERGON - Migration: free trial 14 dias + plan tiers.

Agrega columnas a `usuarios` para soportar el flow de prueba gratuita y la
suscripcion comercial (Sprint 5 GTM).

Columnas nuevas (idempotente):
    plan_tier            TEXT DEFAULT 'trial'    -- trial|n1|n2|n3|enterprise
    trial_started_at     TEXT                    -- ISO 'YYYY-MM-DD HH:MM:SS'
    trial_ends_at        TEXT                    -- ISO, default = started + 14 dias
    subscription_status  TEXT DEFAULT 'trial'    -- trial|active|expired|cancelled
    empresa              TEXT                    -- razon social Empresa Cliente (B2B)

Backfill: para cualquier usuario existente sin trial_started_at, se setea
trial_started_at = created_at (o 'now' si esta vacio) y trial_ends_at =
trial_started_at + 14 dias.

El admin@ergonpy.com se sale automaticamente del trial -> plan_tier='enterprise',
subscription_status='active', trial_ends_at = NULL.

Uso:
    python db/migrate_trial.py
"""
from __future__ import annotations

import os
import sqlite3
import sys
from pathlib import Path


DB_PATH = Path(os.environ.get("DATABASE_PATH") or (Path(__file__).parent / "ergon.db"))

COLUMNAS_NUEVAS = [
    ("plan_tier", "TEXT NOT NULL DEFAULT 'trial'"),
    ("trial_started_at", "TEXT"),
    ("trial_ends_at", "TEXT"),
    ("subscription_status", "TEXT NOT NULL DEFAULT 'trial'"),
    ("empresa", "TEXT"),
]


def _columnas_existentes(conn: sqlite3.Connection, tabla: str) -> set[str]:
    rows = conn.execute(f"PRAGMA table_info({tabla})").fetchall()
    return {r[1] for r in rows}


def main() -> int:
    if not DB_PATH.exists():
        print(f"[ERROR] DB no existe: {DB_PATH}", file=sys.stderr)
        return 1
    conn = sqlite3.connect(str(DB_PATH))
    try:
        existentes = _columnas_existentes(conn, "usuarios")
        if not existentes:
            print("[ERROR] tabla usuarios no existe — correr migrate_f13 primero", file=sys.stderr)
            return 1

        agregadas: list[str] = []
        for nombre, definicion in COLUMNAS_NUEVAS:
            if nombre in existentes:
                print(f"  [skip] columna '{nombre}' ya existe")
                continue
            conn.execute(f"ALTER TABLE usuarios ADD COLUMN {nombre} {definicion}")
            agregadas.append(nombre)
            print(f"  [add]  columna '{nombre}' agregada")
        conn.commit()

        # Backfill trial dates si quedaron NULL
        backfill_sql = """
        UPDATE usuarios
        SET
            trial_started_at = COALESCE(trial_started_at, created_at, datetime('now')),
            trial_ends_at = COALESCE(
                trial_ends_at,
                datetime(COALESCE(created_at, datetime('now')), '+14 days')
            )
        WHERE trial_started_at IS NULL OR trial_ends_at IS NULL
        """
        cur = conn.execute(backfill_sql)
        print(f"  [backfill] {cur.rowcount} usuario(s) con trial dates rellenados")

        # Admin se sale del trial automaticamente
        cur = conn.execute("""
            UPDATE usuarios
            SET plan_tier = 'enterprise',
                subscription_status = 'active',
                trial_ends_at = NULL
            WHERE email = 'admin@ergonpy.com' AND plan_tier != 'enterprise'
        """)
        if cur.rowcount > 0:
            print(f"  [admin] {cur.rowcount} admin promovido a enterprise")
        conn.commit()

        # Indice por trial_ends_at para queries de expiracion
        try:
            conn.execute(
                "CREATE INDEX IF NOT EXISTS idx_usuarios_trial_ends ON usuarios (trial_ends_at)"
            )
            conn.commit()
        except sqlite3.OperationalError:
            pass

        print(f"  [migrate_trial] OK. {len(agregadas)} columna(s) nuevas.")
        return 0
    except sqlite3.Error as e:
        print(f"[ERROR] migration fallo: {e}", file=sys.stderr)
        return 1
    finally:
        conn.close()


if __name__ == "__main__":
    sys.exit(main())