import json
from pathlib import Path

from sqlalchemy.orm import Session

from ..config import settings
from ..models import Section, Student, Subject, User
from ..security import hash_password
from .academic import ensure_default_school_year
from .workflow import seed_period_windows


SUBJECTS = [
    "Castellano",
    "Matemática",
    "Inglés",
    "Ciencias Naturales",
    "Geografía, Historia y Ciudadanía",
    "Educación Física",
    "Arte y Patrimonio",
    "Orientación y Convivencia",
    "Biología",
    "Física",
    "Química",
    "Ciencias de la Tierra",
    "Formación para la Soberanía Nacional",
    "Grupos de Creación, Recreación y Producción",
]


def seed_database(db: Session) -> None:
    if not db.query(User).filter(User.username == settings.admin_username).first():
        db.add(
            User(
                username=settings.admin_username,
                full_name="Administrador Institucional",
                role="admin",
                password_hash=hash_password(settings.admin_password),
                must_change_password=True,
            )
        )

    for name in SUBJECTS:
        if not db.query(Subject).filter(Subject.name == name).first():
            db.add(Subject(name=name))

    seed_dir = Path(__file__).resolve().parent.parent / "seed_data"
    students_path = seed_dir / "students.json"
    if students_path.exists():
        records = json.loads(students_path.read_text(encoding="utf-8"))
        section_cache: dict[str, Section] = {item.name: item for item in db.query(Section).all()}
        existing_cis = {value for (value,) in db.query(Student.ci).all()}
        # Mezcla idempotente: en instalaciones nuevas carga toda la matrícula y,
        # al actualizar, agrega únicamente las cédulas validadas que aún no existan.
        # Nunca sobreescribe nombres, secciones o notas de estudiantes existentes.
        for row in records:
            if row["ci"] in existing_cis:
                continue
            section_name = row["section"]
            section = section_cache.get(section_name)
            if not section:
                grade, letter = section_name.rsplit(" ", 1)
                section = Section(name=section_name, grade_level=grade, letter=letter)
                db.add(section)
                db.flush()
                section_cache[section_name] = section
            db.add(
                Student(
                    ci=row["ci"],
                    full_name=row["name"],
                    sex=row.get("sex"),
                    list_number=row.get("number"),
                    section_id=section.id,
                )
            )
            existing_cis.add(row["ci"])

    # Add empty sections present in the source template, such as 1ERO J.
    sections_path = seed_dir / "sections.json"
    if sections_path.exists():
        section_counts = json.loads(sections_path.read_text(encoding="utf-8"))
        for section_name in section_counts:
            if not db.query(Section).filter(Section.name == section_name).first():
                grade, letter = section_name.rsplit(" ", 1)
                db.add(Section(name=section_name, grade_level=grade, letter=letter))

    db.commit()
    active_year = ensure_default_school_year(db)
    seed_period_windows(db, active_year.name)