"""Programme hierarchy: levels, modules, weeks (intro + 12 weeks), sessions, assessments."""

from __future__ import annotations

import re
from pathlib import Path
from types import SimpleNamespace

from sqlalchemy import CheckConstraint, ForeignKey, Integer, String, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.database import Base

# CSV seed slugs only — admin-created levels use any string in programme_levels.slug.
CSV_LEVEL_SLUGS: tuple[str, ...] = ("beginner", "hb", "intermediate", "hi", "advanced")

# Back-compat for scripts: ProgrammeLevelSlug.beginner, etc.
ProgrammeLevelSlug = SimpleNamespace(
    beginner="beginner",
    hb="hb",
    intermediate="intermediate",
    hi="hi",
    advanced="advanced",
    custom="custom",
)

ProgrammeModuleKind = SimpleNamespace(
    foundations="foundations",
    developmental="developmental",
    creativity="creativity",
)


def normalize_programme_level_slug(value: str) -> str:
    normalized = re.sub(r"\s+", "_", value.strip().lower())
    if not normalized:
        raise ValueError("level slug is required")
    return normalized[:64]


def programme_level_slug_str(slug: str | None) -> str:
    if slug is None:
        return ""
    return str(slug)


def normalize_programme_module_kind(value: str) -> str:
    normalized = re.sub(r"\s+", "_", value.strip().lower())
    if not normalized:
        raise ValueError("module kind is required")
    return normalized[:64]


def programme_module_kind_str(kind: str | None) -> str:
    if kind is None:
        return ""
    return str(kind)


class ProgrammeLevel(Base):
    __tablename__ = "programme_levels"

    id: Mapped[int] = mapped_column(primary_key=True)
    slug: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    display_name: Mapped[str] = mapped_column(String(255), nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=0)

    modules: Mapped[list["ProgrammeModule"]] = relationship(
        "ProgrammeModule", back_populates="level", cascade="all, delete-orphan", order_by="ProgrammeModule.sort_order"
    )


class ProgrammeModule(Base):
    __tablename__ = "programme_modules"

    id: Mapped[int] = mapped_column(primary_key=True)
    level_id: Mapped[int] = mapped_column(ForeignKey("programme_levels.id", ondelete="CASCADE"), nullable=False)
    kind: Mapped[str] = mapped_column(String(64), nullable=False)
    display_name: Mapped[str] = mapped_column(String(255), nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=0)

    level: Mapped["ProgrammeLevel"] = relationship("ProgrammeLevel", back_populates="modules")
    weeks: Mapped[list["ProgrammeWeek"]] = relationship(
        "ProgrammeWeek", back_populates="module", cascade="all, delete-orphan", order_by="ProgrammeWeek.week_index"
    )

    __table_args__ = (UniqueConstraint("level_id", "kind", name="uq_programme_module_level_kind"),)


class ProgrammeWeek(Base):
    __tablename__ = "programme_weeks"

    id: Mapped[int] = mapped_column(primary_key=True)
    module_id: Mapped[int] = mapped_column(ForeignKey("programme_modules.id", ondelete="CASCADE"), nullable=False)
    week_index: Mapped[int] = mapped_column(Integer, nullable=False)  # 0 = intro, 1..12 = Week 1..12
    display_label: Mapped[str] = mapped_column(String(64), nullable=False)

    module: Mapped["ProgrammeModule"] = relationship("ProgrammeModule", back_populates="weeks")
    sessions: Mapped[list["ProgrammeSession"]] = relationship(
        "ProgrammeSession",
        back_populates="week",
        cascade="all, delete-orphan",
        order_by="ProgrammeSession.session_number",
    )

    __table_args__ = (
        UniqueConstraint("module_id", "week_index", name="uq_programme_week_module_index"),
        CheckConstraint("week_index >= 0 AND week_index <= 12", name="ck_programme_week_index_range"),
    )


class ProgrammeSession(Base):
    __tablename__ = "programme_sessions"

    id: Mapped[int] = mapped_column(primary_key=True)
    week_id: Mapped[int] = mapped_column(ForeignKey("programme_weeks.id", ondelete="CASCADE"), nullable=False)
    session_number: Mapped[int] = mapped_column(Integer, nullable=False)
    title: Mapped[str] = mapped_column(String(512), nullable=False)
    admin_checked: Mapped[bool] = mapped_column(default=False)

    week: Mapped["ProgrammeWeek"] = relationship("ProgrammeWeek", back_populates="sessions")
    assessments: Mapped[list["ProgrammeAssessment"]] = relationship(
        "ProgrammeAssessment",
        back_populates="session",
        cascade="all, delete-orphan",
        order_by="ProgrammeAssessment.sort_order",
    )

    __table_args__ = (
        UniqueConstraint("week_id", "session_number", name="uq_programme_session_week_number"),
        CheckConstraint("session_number >= 1", name="ck_programme_session_number_positive"),
    )


class ProgrammeAssessment(Base):
    __tablename__ = "programme_assessments"

    id: Mapped[int] = mapped_column(primary_key=True)
    session_id: Mapped[int] = mapped_column(ForeignKey("programme_sessions.id", ondelete="CASCADE"), nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False)
    title: Mapped[str] = mapped_column(String(512), nullable=False)

    session: Mapped["ProgrammeSession"] = relationship("ProgrammeSession", back_populates="assessments")

    __table_args__ = (UniqueConstraint("session_id", "sort_order", name="uq_programme_assessment_session_order"),)


def repo_root() -> Path:
    """Project root (directory that contains the `app` package)."""
    return Path(__file__).resolve().parents[2]


LEVEL_CSV_FILES: dict[str, str] = {
    "beginner": "UPP - Assessments - Beginner.csv",
    "hb": "UPP - Assessments - HB.csv",
    "intermediate": "UPP - Assessments - Intermediate.csv",
    "hi": "UPP - Assessments - HI.csv",
    "advanced": "UPP - Assessments - Advanced.csv",
}


def resolve_upp_csv_path(slug: str) -> Path | None:
    """
    Locate one UPP CSV on disk. Uses `programme_csv_dir` from settings when set; otherwise
    tries common locations (repo root, app/, app/all csv/, etc.).
    """
    from app.config import get_settings

    filename = LEVEL_CSV_FILES.get(slug)
    if not filename:
        # e.g. custom levels are built in the admin UI and do not have a CSV seed file.
        return None
    root = repo_root().resolve()
    raw = (getattr(get_settings(), "programme_csv_dir", None) or "").strip()
    bases: list[Path]
    if raw:
        p = Path(raw.strip())
        bases = [p.resolve() if p.is_absolute() else (root / p).resolve()]
    else:
        bases = [
            root / "app" / "programme",
            root,
            root / "app",
            root / "all csv",
            root / "app" / "all csv",
            root / "app" / "all_csv",
        ]
    for base in bases:
        candidate = base / filename
        if candidate.is_file():
            return candidate
    return None


LEVEL_DISPLAY_DEFAULTS: dict[str, str] = {
    "beginner": "Beginner",
    "hb": "Higher Beginner (HB)",
    "intermediate": "Intermediate",
    "hi": "Higher Intermediate (HI)",
    "advanced": "Advanced",
    "custom": "Custom",
}


def _module_kind_from_cell(cell: str) -> str | None:
    cl = cell.lower()
    if "creativity" in cl:
        return "creativity"
    if "developmental" in cl:
        return "developmental"
    if "foundation" in cl:
        return "foundations"
    return None


_SESSION_RE = re.compile(r"^session\s+(\d+)\s*-?\s*(.*)$", re.IGNORECASE)


def parse_upp_assessment_csv(path: Path) -> list[tuple[ProgrammeModuleKind, list[dict]]]:
    """
    Parse one UPP assessment CSV into module payloads.
    Each module payload is a list of week dicts:
      {"display_label": str, "sessions": {1: {"title": str, "assessments": [str,...]}}, ...}}
    """
    import csv

    text = path.read_text(encoding="utf-8", errors="replace")
    rows = list(csv.reader(text.splitlines()))

    modules_out: list[tuple[str, list[dict]]] = []
    r = 0
    while r < len(rows):
        row = rows[r]
        c0 = (row[0] if row else "").strip()
        kind = _module_kind_from_cell(c0) if c0 else None
        if kind is None:
            r += 1
            continue

        week_row_idx = r + 1
        if week_row_idx >= len(rows):
            break
        week_row = rows[week_row_idx]
        week_labels: list[str] = []
        for c in range(0, len(week_row), 3):
            label = (week_row[c] or "").strip()
            if not label:
                break
            ll = label.lower()
            if ll in ("progress assessment", "grading"):
                break
            if label.upper() == "TO COMPLETE":
                continue
            week_labels.append(label)

        num_weeks = len(week_labels)
        if num_weeks == 0:
            r += 1
            continue

        weeks_payload: list[dict] = [{"display_label": week_labels[i], "sessions": {}} for i in range(num_weeks)]
        current_sn: list[int | None] = [None] * num_weeks

        data_r = week_row_idx + 1
        # Skip blank separator rows only. HB/Intermediate/etc. put week data in cols 3,6,…
        # with an empty column 0 — do not require col 0 to be non-empty.
        while data_r < len(rows) and not any((c or "").strip() for c in rows[data_r]):
            data_r += 1

        while data_r < len(rows):
            dr = rows[data_r]
            c0b = (dr[0] if dr else "").strip()
            if c0b and _module_kind_from_cell(c0b) is not None:
                break
            if c0b.lower() == "grading":
                break

            for wi in range(num_weeks):
                col = wi * 3
                if col >= len(dr):
                    continue
                cell = (dr[col] or "").strip()
                if not cell:
                    continue
                sm = _SESSION_RE.match(cell)
                if sm:
                    sn = int(sm.group(1))
                    title = (sm.group(2) or "").strip() or f"Session {sn}"
                    current_sn[wi] = sn
                    weeks_payload[wi]["sessions"].setdefault(sn, {"title": title, "assessments": []})
                    weeks_payload[wi]["sessions"][sn]["title"] = title
                    continue
                low = cell.lower()
                if low.startswith("assessment:"):
                    title = cell.split(":", 1)[1].strip() if ":" in cell else cell
                    if title.upper() in ("NONE", ""):
                        continue
                    sn = current_sn[wi]
                    if sn is not None and title:
                        weeks_payload[wi]["sessions"][sn]["assessments"].append(title)

            data_r += 1

        modules_out.append((kind, weeks_payload))
        r = data_r

    return modules_out


def programme_level_payload_from_csv(slug: str) -> dict | None:
    """
    Full programme tree for one level (levels → modules → weeks → sessions → assessments).
    Returns None if the CSV file is missing.
    """
    path = resolve_upp_csv_path(slug)
    if path is None:
        return None

    modules: list[dict] = []
    for mod_order, (kind, weeks_payload) in enumerate(parse_upp_assessment_csv(path)):
        weeks: list[dict] = []
        for week_index, week in enumerate(weeks_payload):
            sessions: list[dict] = []
            for session_number, sdata in sorted(week["sessions"].items()):
                sessions.append(
                    {
                        "session_number": session_number,
                        "title": sdata["title"],
                        "assessments": list(sdata["assessments"]),
                    }
                )
            weeks.append(
                {
                    "week_index": week_index,
                    "display_label": week["display_label"],
                    "sessions": sessions,
                }
            )
        modules.append(
            {
                "kind": kind,
                "display_name": kind.replace("_", " ").title(),
                "sort_order": mod_order,
                "weeks": weeks,
            }
        )

    return {
        "level": {
            "slug": slug,
            "display_name": LEVEL_DISPLAY_DEFAULTS.get(slug, slug.replace("_", " ").title()),
        },
        "source_csv": path.name,
        "modules": modules,
    }