database.py

import os
import datetime
from dotenv import load_dotenv
from sqlalchemy import create_engine, Column, Integer, String, Numeric, Boolean, DateTime, ForeignKey, CheckConstraint
from sqlalchemy.orm import declarative_base, sessionmaker

# Load environment variables from .env file
load_dotenv()

DATABASE_URL = os.getenv("DATABASE_URL")

if not DATABASE_URL:
    # En producción (Hugging Face), DATABASE_URL DEBE estar en los Secrets del Space.
    # Nunca hardcodear credenciales aquí — este archivo está en el repositorio público.
    raise RuntimeError(
        "DATABASE_URL no configurada. "
        "Añádela en Hugging Face Space → Settings → Variables and secrets."
    )

# Supabase Transaction Pooler (pgBouncer, puerto 6543):
# - pool_pre_ping: detecta conexiones muertas antes de usarlas
# - pool_size / max_overflow: límites conservadores para entorno serverless
# - connect_args: deshabilita prepared statements (requerido por pgBouncer Transaction mode)
engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,
    pool_size=5,
    max_overflow=10,
    pool_recycle=300,           # recicla más rápido en pooler
    connect_args={
        "connect_timeout": 10,
        "options": "-c statement_timeout=30000"  # 30s max por query
    },
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()


# =============================================================================
# DIMENSIONES — mapeadas exactamente al DDL de Supabase
# =============================================================================

class DimTiempo(Base):
    __tablename__ = "dim_tiempo"
    id_tiempo   = Column(Integer, primary_key=True)
    gestion     = Column(Integer, nullable=False, default=2026)
    semestre    = Column(Integer, default=1)
    mes         = Column(String(20), default="Mayo")


class DimEstudiante(Base):
    __tablename__ = "dim_estudiante"
    id_estudiante     = Column(Integer, primary_key=True, index=True)
    nombre_completo   = Column(String(200), nullable=False)
    codigo_estudiante = Column(String(50))
    genero            = Column(String(20))
    ciudad            = Column(String(100))
    nivel_academico   = Column(String(100))
    edad              = Column(Integer)
    ocupacion         = Column(String(100))
    estado_civil      = Column(String(50))


class DimDocente(Base):
    __tablename__ = "dim_docente"
    id_docente        = Column(Integer, primary_key=True)
    nombre_completo   = Column(String(200), nullable=False, default="Docente Generico")
    area_especialidad = Column(String(200), default="Generico")


class DimModulo(Base):
    __tablename__ = "dim_modulo"
    id_modulo           = Column(Integer, primary_key=True)
    nombre_modulo       = Column(String(200), nullable=False, default="Modulo Generico")
    nombre_institucion  = Column(String(200), nullable=False, default="GiraGroup")
    programa            = Column(String(200), default="General")
    pos_code            = Column(String(50))


class DimOrigenDocumental(Base):
    """Mapeada a la tabla dim_origen_documental en Supabase."""
    __tablename__ = "dim_origen_documental"
    id_documento        = Column(Integer, primary_key=True)
    tipo_documento      = Column(String(10), default="SHEET")
    nombre_archivo      = Column(String(500), default="archivo_generico")
    fecha_procesamiento = Column(DateTime, default=datetime.datetime.utcnow)


class Users(Base):
    """Tabla de usuarios/autenticación en Supabase."""
    __tablename__ = "users"
    id              = Column(Integer, primary_key=True)
    username        = Column(String(100), unique=True, nullable=False, default="sistema")
    hashed_password = Column(String(255), nullable=False, default="$placeholder$")
    role            = Column(String(20), default="admin")
    created_at      = Column(DateTime, default=datetime.datetime.utcnow)

class LogAuditoriaNlp(Base):
    """Tabla para registrar retroalimentación de correcciones MLOps."""
    __tablename__ = "log_auditoria_nlp"
    id_log = Column(Integer, primary_key=True, index=True)
    texto_original = Column(String(500), nullable=False)
    prediccion_beto = Column(String(200))
    confianza_ia = Column(Numeric(5, 4))
    correccion_humana = Column(String(200), nullable=False)
    usuario_auditor = Column(Integer, ForeignKey("users.id"))
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

class DimCategoriaFinanciera(Base):
    """Nueva dimension para Egresos, Ingresos y OKRs Financieros"""
    __tablename__ = "dim_categoria_financiera"
    id_categoria = Column(Integer, primary_key=True)
    nombre_categoria = Column(String(200), nullable=False)
    tipo = Column(String(50)) # 'INGRESO', 'EGRESO', 'RENTABILIDAD', 'EBITDA'


# =============================================================================
# TABLAS DE HECHOS
# =============================================================================

class FactRendimientoAcademico(Base):
    __tablename__ = "fact_rendimiento_academico"
    id_hecho_aca                   = Column(Integer, primary_key=True, index=True)
    id_estudiante                  = Column(Integer, ForeignKey("dim_estudiante.id_estudiante"))
    id_docente                     = Column(Integer, ForeignKey("dim_docente.id_docente"))
    id_modulo                      = Column(Integer, ForeignKey("dim_modulo.id_modulo"))
    id_tiempo                      = Column(Integer, ForeignKey("dim_tiempo.id_tiempo"))
    id_documento                   = Column(Integer, ForeignKey("dim_origen_documental.id_documento"))
    id_usuario_carga               = Column(Integer, ForeignKey("users.id"))
    nota_final                     = Column(Numeric(5, 2))
    asistencia_pct                 = Column(Numeric(5, 2))
    incumplimiento_actividades_pct = Column(Numeric(5, 2), default=0.00)
    nivel_confianza_ia             = Column(Numeric(5, 4))
    requiere_revision              = Column(Boolean, default=False)
    estado_academico               = Column(String(50))
    created_at                     = Column(DateTime, default=datetime.datetime.utcnow)


class FactSituacionFinanciera(Base):
    __tablename__ = "fact_situacion_financiera"
    id_hecho_fin   = Column(Integer, primary_key=True)
    id_estudiante  = Column(Integer, ForeignKey("dim_estudiante.id_estudiante"))
    id_tiempo      = Column(Integer, ForeignKey("dim_tiempo.id_tiempo"))
    monto_deuda    = Column(Numeric(10, 2))
    cuotas_impagas = Column(Integer)
    estado_cartera = Column(String(20))
    tipo_alerta    = Column(String(20))
    fecha_registro = Column(DateTime, default=datetime.datetime.utcnow)

class FactCobranzasProyectadas(Base):
    """Almacena montos despivotados de ingresos mensuales proyectados."""
    __tablename__ = "fact_cobranzas_proyectadas"
    id_hecho_cobro = Column(Integer, primary_key=True, index=True)
    id_estudiante  = Column(Integer, ForeignKey("dim_estudiante.id_estudiante"))
    id_tiempo      = Column(Integer, ForeignKey("dim_tiempo.id_tiempo")) # Referencia al mes proyectado
    monto_esperado = Column(Numeric(10, 2))
    estado_pago    = Column(String(50)) # Ej. PAGADO, PENDIENTE
    created_at     = Column(DateTime, default=datetime.datetime.utcnow)

class FactEvaluacionDocente(Base):
    """Respuestas a encuestas de satisfacción docente y NPS."""
    __tablename__ = "fact_evaluacion_docente"
    id_hecho_eval = Column(Integer, primary_key=True)
    id_docente    = Column(Integer, ForeignKey("dim_docente.id_docente"))
    id_modulo     = Column(Integer, ForeignKey("dim_modulo.id_modulo"))
    id_estudiante = Column(Integer, ForeignKey("dim_estudiante.id_estudiante"), nullable=True) # A veces anonimo
    id_tiempo     = Column(Integer, ForeignKey("dim_tiempo.id_tiempo"))
    pregunta_bloque = Column(String(500))
    puntuacion    = Column(Numeric(4, 2)) # Ej. 1 al 5
    comentario    = Column(String(1000))

class FactMarketingInscripciones(Base):
    """Métricas de OKRs de Marketing y ventas."""
    __tablename__ = "fact_marketing"
    id_hecho_mkt  = Column(Integer, primary_key=True)
    id_modulo     = Column(Integer, ForeignKey("dim_modulo.id_modulo"))
    id_tiempo     = Column(Integer, ForeignKey("dim_tiempo.id_tiempo"))
    leads         = Column(Integer, default=0)
    reservas      = Column(Integer, default=0)
    inscritos     = Column(Integer, default=0)
    costo_programa= Column(Numeric(12, 2))

class FactRentabilidadPresupuesto(Base):
    """Indicadores de Rentabilidad, Egresos y EBITDA (Ejecutado vs Meta)."""
    __tablename__ = "fact_rentabilidad"
    id_hecho_rent = Column(Integer, primary_key=True)
    id_modulo     = Column(Integer, ForeignKey("dim_modulo.id_modulo"))
    id_tiempo     = Column(Integer, ForeignKey("dim_tiempo.id_tiempo"))
    id_categoria  = Column(Integer, ForeignKey("dim_categoria_financiera.id_categoria"))
    monto_ejecutado = Column(Numeric(14, 2))
    monto_meta      = Column(Numeric(14, 2))


def get_db():
    """
    Dependency injection helper to yield a database session.
    Guarantees that the session is closed after the request completes.
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Database tables creation is handled manually in Supabase.
# Base.metadata.create_all(bind=engine) has been removed.