"""ERGON - Migration F13 (sprint 2): tablas auth.

Idempotente: CREATE TABLE IF NOT EXISTS. Safe de correr multiples veces.
Agrega 3 tablas: usuarios, usuarios_obras (m2m), sesiones.
"""
from __future__ import annotations

import os
import sqlite3
from pathlib import Path

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


SCHEMA = """
CREATE TABLE IF NOT EXISTS usuarios (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    email           TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%.%'),
    password_hash   TEXT NOT NULL,
    nombre          TEXT NOT NULL,
    rol             TEXT NOT NULL CHECK (rol IN ('admin','cliente')),
    activo          INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0,1)),
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    last_login_at   TEXT
);

CREATE TABLE IF NOT EXISTS usuarios_obras (
    usuario_id      INTEGER NOT NULL REFERENCES usuarios(id) ON DELETE CASCADE,
    obra_id         INTEGER NOT NULL REFERENCES obras(id) ON DELETE CASCADE,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (usuario_id, obra_id)
);

CREATE TABLE IF NOT EXISTS sesiones (
    token           TEXT PRIMARY KEY,
    usuario_id      INTEGER NOT NULL REFERENCES usuarios(id) ON DELETE CASCADE,
    expira_at       TEXT NOT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_sesiones_usuario  ON sesiones (usuario_id);
CREATE INDEX IF NOT EXISTS idx_sesiones_expira   ON sesiones (expira_at);
CREATE INDEX IF NOT EXISTS idx_usuarios_obras_o  ON usuarios_obras (obra_id);
"""


def main():
    conn = sqlite3.connect(str(DB_PATH))
    conn.execute("PRAGMA foreign_keys = ON")
    conn.executescript(SCHEMA)
    conn.commit()

    tablas = [r[0] for r in conn.execute(
        "SELECT name FROM sqlite_master WHERE type='table' "
        "AND name IN ('usuarios','usuarios_obras','sesiones') ORDER BY name"
    ).fetchall()]
    print(f"[F13] Tablas presentes: {tablas}")
    if len(tablas) != 3:
        raise SystemExit("ERROR: no se crearon las 3 tablas esperadas.")

    indices = [r[0] for r in conn.execute(
        "SELECT name FROM sqlite_master WHERE type='index' "
        "AND name LIKE 'idx_sesiones%' OR name LIKE 'idx_usuarios_obras%' "
        "ORDER BY name"
    ).fetchall()]
    print(f"[F13] Indices: {indices}")

    print(f"[F13] Migration aplicada contra {DB_PATH}. Sin cambios en tablas existentes.")
    conn.close()


if __name__ == "__main__":
    main()