import sqlite3
import pathlib
import os
import secrets
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from fastapi import Response

BASE_DIR = pathlib.Path(__file__).resolve().parent.parent
DB_PATH = BASE_DIR / "db.sqlite3"

IS_PROD = os.getenv("APP_ENV", "dev").lower() in ("prod", "production")

SECRET_KEY = os.getenv("APP_SECRET_KEY") or secrets.token_urlsafe(64)

class CachedStaticFiles(StaticFiles):
    def file_response(self, *args, **kwargs) -> Response:
        resp = super().file_response(*args, **kwargs)
        resp.headers["Cache-Control"] = "public, max-age=31536000, immutable"
        return resp

# Templates configuration
# Assuming app/templates exists relative to where this file is run/imported
templates = Jinja2Templates(directory=os.path.join(BASE_DIR, "app", "templates"))

def get_conn():
    conn = sqlite3.connect(DB_PATH, isolation_level=None, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.execute("PRAGMA synchronous = NORMAL;")
    conn.execute("PRAGMA busy_timeout = 5000;")
    conn.execute("PRAGMA cache_size = -20000;")
    conn.execute("PRAGMA temp_store = MEMORY;")
    try:
        conn.execute("PRAGMA mmap_size = 268435456;")
    except Exception:
        pass
    return conn

def _conn():
    return get_conn()

def _table_columns(conn, table):
    try:
        return {r[1] for r in conn.execute(f"PRAGMA table_info({table})").fetchall()}
    except Exception:
        return set()

def init_db():
    with _conn() as conn:
        c = conn.cursor()
        c.execute("PRAGMA foreign_keys=ON")

        c.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT NOT NULL UNIQUE,
            name TEXT NOT NULL,
            first_name TEXT,
            last_name TEXT,
            phone TEXT,
            password_hash TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'alumno',
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime'))
        )
        """)
        cols_users = {r[1] for r in c.execute("PRAGMA table_info(users)").fetchall()}
        if "first_name" not in cols_users:
            c.execute("ALTER TABLE users ADD COLUMN first_name TEXT")
        if "last_name" not in cols_users:
            c.execute("ALTER TABLE users ADD COLUMN last_name TEXT")
        if "phone" not in cols_users:
            c.execute("ALTER TABLE users ADD COLUMN phone TEXT")

        c.execute("""
        CREATE TABLE IF NOT EXISTS user_profile (
            user_id INTEGER PRIMARY KEY,
            phone TEXT,
            avatar TEXT,
            city TEXT,
            dni TEXT,
            birth_date TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS user_sports (
            user_id INTEGER PRIMARY KEY,
            team TEXT,
            category TEXT,
            position TEXT,
            dominant_foot TEXT,
            strengths TEXT,
            weaknesses TEXT,
            injury_history TEXT,
            training_type TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS user_health (
            user_id INTEGER PRIMARY KEY,
            physical_work INTEGER,
            physical_work_details TEXT,
            smoking INTEGER,
            alcohol INTEGER,
            recovery TEXT,
            chest_pain INTEGER,
            discomfort INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS user_consent (
            user_id INTEGER PRIMARY KEY,
            whatsapp_content INTEGER,
            video_permission INTEGER,
            privacy_acceptance INTEGER,
            data_confirmation INTEGER,
            agreement INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS user_guardians (
            user_id INTEGER PRIMARY KEY,
            occupation TEXT,
            study_place TEXT,
            parent_name TEXT,
            parent_email TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS user_marketing (
            user_id INTEGER PRIMARY KEY,
            found_us TEXT,
            enjoyment TEXT,
            nerves_confidence TEXT,
            additional_comments TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS user_finance (
            user_id INTEGER PRIMARY KEY,
            matricula_eur REAL NOT NULL DEFAULT 0,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS reservations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            date TEXT NOT NULL,
            time TEXT,
            duration_minutes INTEGER,
            price_cents INTEGER NOT NULL DEFAULT 0,
            name TEXT,
            email TEXT,
            phone TEXT,
            notes TEXT,
            user_id INTEGER,
            status TEXT NOT NULL DEFAULT 'confirmada',
            docente_id INTEGER NOT NULL,
            paid INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY(docente_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)
        cols_res = {r[1] for r in c.execute("PRAGMA table_info(reservations)").fetchall()}
        if "created_at" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN created_at TEXT")
            c.execute("UPDATE reservations SET created_at = strftime('%Y-%m-%dT%H:%M:%S','now','localtime') WHERE created_at IS NULL")
        if "time" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN time TEXT")
        if "duration_minutes" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN duration_minutes INTEGER")
        if "price_cents" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN price_cents INTEGER NOT NULL DEFAULT 0")
        if "name" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN name TEXT")
        if "email" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN email TEXT")
        if "phone" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN phone TEXT")
        if "notes" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN notes TEXT")
        if "user_id" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN user_id INTEGER")
        if "status" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN status TEXT NOT NULL DEFAULT 'confirmada'")
        if "docente_id" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN docente_id INTEGER NOT NULL DEFAULT 1")
        if "paid" not in cols_res:
            c.execute("ALTER TABLE reservations ADD COLUMN paid INTEGER NOT NULL DEFAULT 0")

        c.execute("""
        CREATE TABLE IF NOT EXISTS reservation_students (
            reservation_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            paid INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (reservation_id, user_id),
            FOREIGN KEY(reservation_id) REFERENCES reservations(id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)
        cols_rs = {r[1] for r in c.execute("PRAGMA table_info(reservation_students)").fetchall()}
        if "paid" not in cols_rs:
            c.execute("ALTER TABLE reservation_students ADD COLUMN paid INTEGER NOT NULL DEFAULT 0")

        c.execute("""
        CREATE TABLE IF NOT EXISTS jugadores_gastos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            date TEXT NOT NULL,
            user_id INTEGER,
            reservation_id INTEGER,
            amount_cents INTEGER NOT NULL,
            category TEXT,
            concept TEXT,
            notes TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
        )
        """)
        c.execute("CREATE INDEX IF NOT EXISTS idx_jg_date ON jugadores_gastos(date)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_jg_user ON jugadores_gastos(user_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_jg_reservation ON jugadores_gastos(reservation_id)")

        c.execute("""
        CREATE TABLE IF NOT EXISTS notifications (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            title TEXT,
            body TEXT,
            sender_id INTEGER,
            recipient_id INTEGER NOT NULL,
            reservation_id INTEGER,
            is_read INTEGER NOT NULL DEFAULT 0,
            deleted_by_sender INTEGER NOT NULL DEFAULT 0,
            deleted_by_recipient INTEGER NOT NULL DEFAULT 0,
            FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY(recipient_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(reservation_id) REFERENCES reservations(id) ON DELETE CASCADE
        )
        """)
        c.execute("CREATE INDEX IF NOT EXISTS idx_notif_rec ON notifications(recipient_id, is_read)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_notif_send ON notifications(sender_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_notif_created ON notifications(created_at)")

        c.execute("""
        CREATE TABLE IF NOT EXISTS player_ratings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            player_id INTEGER NOT NULL,
            docente_id INTEGER NOT NULL,
            rating INTEGER NOT NULL,
            notes TEXT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            FOREIGN KEY(player_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(docente_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS valoraciones_jugador (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            eval_id TEXT NOT NULL,
            player_id INTEGER NOT NULL,
            docente_id INTEGER NOT NULL,
            area TEXT NOT NULL,
            subescala TEXT NOT NULL,
            indicador TEXT NOT NULL,
            score INTEGER NOT NULL,
            observaciones TEXT,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            FOREIGN KEY(player_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(docente_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS "formulario-registro" (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            pin TEXT NOT NULL UNIQUE,
            email TEXT,
            name TEXT,
            used INTEGER NOT NULL DEFAULT 0,
            expires_at TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime'))
        )
        """)
        c.execute('CREATE INDEX IF NOT EXISTS idx_formreg_used_exp ON "formulario-registro"(used, expires_at)')

        c.execute("INSERT OR IGNORE INTO user_profile(user_id)  SELECT id FROM users")
        c.execute("INSERT OR IGNORE INTO user_sports(user_id)   SELECT id FROM users")
        c.execute("INSERT OR IGNORE INTO user_health(user_id)   SELECT id FROM users")
        c.execute("INSERT OR IGNORE INTO user_consent(user_id)  SELECT id FROM users")
        c.execute("INSERT OR IGNORE INTO user_guardians(user_id)SELECT id FROM users")
        c.execute("INSERT OR IGNORE INTO user_marketing(user_id)SELECT id FROM users")
        c.execute("INSERT OR IGNORE INTO user_finance(user_id, matricula_eur) SELECT id, 0.0 FROM users")

        conn.commit()