import sqlite3
import pathlib
import os
from dotenv import load_dotenv

load_dotenv()  # Load .env file if exists
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_DIR = BASE_DIR / "app" / "database"
DB_DIR.mkdir(parents=True, exist_ok=True)
DB_PATH = DB_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")
        if "is_mvp90" not in cols_users:
            c.execute("ALTER TABLE users ADD COLUMN is_mvp90 INTEGER NOT NULL DEFAULT 0")

        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,
            gender TEXT,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )
        """)
        cols_profile = {r[1] for r in c.execute("PRAGMA table_info(user_profile)").fetchall()}
        if "gender" not in cols_profile:
            c.execute("ALTER TABLE user_profile ADD COLUMN gender TEXT")
            # Attempt to migrate from estadisticas_jugadores if exists
            try:
                c.execute("""
                    UPDATE user_profile 
                    SET gender = (
                        SELECT sexo FROM estadisticas_jugadores 
                        WHERE estadisticas_jugadores.user_id = user_profile.user_id
                    )
                    WHERE gender IS NULL
                """)
            except Exception:
                pass

        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)')

        # Academia Tables
        c.execute("""
        CREATE TABLE IF NOT EXISTS academy_courses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            thumbnail TEXT,
            status TEXT NOT NULL DEFAULT 'draft',
            created_by INTEGER NOT NULL,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            order_index INTEGER NOT NULL DEFAULT 0,
            resource_url TEXT,
            resource_name TEXT,
            FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE CASCADE
        )
        """)
        
        # Add resource columns to existing table
        cols_courses = _table_columns(conn, 'academy_courses')
        if 'resource_url' not in cols_courses:
            c.execute("ALTER TABLE academy_courses ADD COLUMN resource_url TEXT")
        if 'resource_name' not in cols_courses:
            c.execute("ALTER TABLE academy_courses ADD COLUMN resource_name TEXT")

        c.execute("""
        CREATE TABLE IF NOT EXISTS academy_sections (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            course_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            description TEXT,
            order_index INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            FOREIGN KEY(course_id) REFERENCES academy_courses(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS academy_lessons (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            course_id INTEGER NOT NULL,
            section_id INTEGER,
            title TEXT NOT NULL,
            description TEXT,
            content_type TEXT NOT NULL,
            content_data TEXT,
            duration_minutes INTEGER,
            order_index INTEGER NOT NULL DEFAULT 0,
            status TEXT NOT NULL DEFAULT 'draft',
            created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            FOREIGN KEY(course_id) REFERENCES academy_courses(id) ON DELETE CASCADE,
            FOREIGN KEY(section_id) REFERENCES academy_sections(id) ON DELETE SET NULL
        )
        """)
        
        # Add section_id column to existing lessons table if needed
        cols_lessons = _table_columns(conn, 'academy_lessons')
        if 'section_id' not in cols_lessons:
            c.execute("ALTER TABLE academy_lessons ADD COLUMN section_id INTEGER REFERENCES academy_sections(id) ON DELETE SET NULL")
        
        # Add attachment columns to lessons table
        if 'attachment_url' not in cols_lessons:
            c.execute("ALTER TABLE academy_lessons ADD COLUMN attachment_url TEXT")
        if 'attachment_name' not in cols_lessons:
            c.execute("ALTER TABLE academy_lessons ADD COLUMN attachment_name TEXT")

        c.execute("""
        CREATE TABLE IF NOT EXISTS academy_enrollments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            course_id INTEGER NOT NULL,
            enrolled_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            completed_at TEXT,
            UNIQUE(user_id, course_id),
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(course_id) REFERENCES academy_courses(id) ON DELETE CASCADE
        )
        """)

        c.execute("""
        CREATE TABLE IF NOT EXISTS academy_progress (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            lesson_id INTEGER NOT NULL,
            completed INTEGER NOT NULL DEFAULT 0,
            completed_at TEXT,
            last_accessed_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S','now','localtime')),
            UNIQUE(user_id, lesson_id),
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY(lesson_id) REFERENCES academy_lessons(id) ON DELETE CASCADE
        )
        """)

        c.execute("CREATE INDEX IF NOT EXISTS idx_academy_sections_course ON academy_sections(course_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_academy_lessons_course ON academy_lessons(course_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_academy_lessons_section ON academy_lessons(section_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_academy_enrollments_user ON academy_enrollments(user_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_academy_enrollments_course ON academy_enrollments(course_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_academy_progress_user ON academy_progress(user_id)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_academy_progress_lesson ON academy_progress(lesson_id)")


        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()