import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
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:
    # Fallback to local postgres if not set for safety, or raise a warning
    DATABASE_URL = "postgresql://postgres:postgres@localhost:5432/postgres"

# For Supabase, pool_pre_ping=True is highly recommended to handle stale connections
engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,
    pool_recycle=3600
)

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

from sqlalchemy import Column, Integer, String, Numeric, Boolean, DateTime, ForeignKey
import datetime

Base = declarative_base()

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

class DimDocente(Base):
    __tablename__ = "dim_docente"
    id_docente = Column(Integer, primary_key=True)
    nombre = Column(String(200), nullable=False, default="Docente Generico")
    especialidad = Column(String(100), 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")
    version = Column(String(50), default="1.0")

class DimTiempo(Base):
    __tablename__ = "dim_tiempo"
    id_tiempo = Column(Integer, primary_key=True)
    anio = Column(Integer, nullable=False, default=2026)
    mes = Column(Integer, nullable=False, default=5)
    dia = Column(Integer, nullable=False, default=30)
    trimestre = Column(Integer, nullable=False, default=2)

class DimDocumento(Base):
    __tablename__ = "dim_documento"
    id_documento = Column(Integer, primary_key=True)
    tipo_documento = Column(String(100), nullable=False, default="Consolidado Generico")

class DimUsuario(Base):
    __tablename__ = "dim_usuario"
    id_usuario = Column(Integer, primary_key=True)
    rol = Column(String(50), nullable=False, default="Sistema")
    nombre = Column(String(100), nullable=False, default="Admin")

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)
    id_modulo = Column(Integer)
    id_tiempo = Column(Integer)
    id_documento = Column(Integer)
    id_usuario_carga = Column(Integer)
    nota_final = Column(Numeric(5,2))
    asistencia_pct = Column(Numeric(5,2))
    incumplimiento_actividades_pct = Column(Numeric(5,2))
    nivel_confianza_ia = Column(Numeric(5,4))
    requiere_revision = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)


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