"""Normalized, ownership-scoped student learning state.

These tables are the durable truth behind planning, teaching, assessment, and
progress. The versioned student workspace remains a fast UI aggregate/cache;
it must not be the only place a learning event exists.
"""
from __future__ import annotations

from datetime import date, datetime
from typing import Any

from sqlalchemy import (
    Boolean,
    Date,
    DateTime,
    Float,
    ForeignKey,
    Index,
    Integer,
    JSON,
    String,
    Text,
    UniqueConstraint,
    func,
    text,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.core.database import Base
from app.utils.ids import prefixed_id


class StudentProfileState(Base):
    __tablename__ = "student_profiles"

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("stp"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), unique=True, index=True, nullable=False)
    class_level: Mapped[str] = mapped_column(String(80), nullable=False)
    board: Mapped[str] = mapped_column(String(120), nullable=False)
    # Optional: a student who does not yet know their exam date can still onboard.
    exam_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    goal: Mapped[str] = mapped_column(String(80), nullable=False)
    daily_minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    preferred_time: Mapped[str | None] = mapped_column(String(40), nullable=True)
    available_study_days: Mapped[int] = mapped_column(Integer, nullable=False)
    current_subject_id: Mapped[str | None] = mapped_column(String(40), nullable=True)
    current_chapter_id: Mapped[str | None] = mapped_column(String(40), nullable=True)
    current_mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True)
    onboarding_completed: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    preferences: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class Subject(Base):
    __tablename__ = "subjects"
    __table_args__ = (
        UniqueConstraint("user_id", "name", "board", "class_level", name="uq_subjects_user_context"),
        Index("ix_subjects_user_priority", "user_id", "priority"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("sub"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    board: Mapped[str] = mapped_column(String(120), nullable=False)
    class_level: Mapped[str] = mapped_column(String(80), nullable=False)
    priority: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="active", nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class Chapter(Base):
    __tablename__ = "chapters"
    __table_args__ = (
        UniqueConstraint("user_id", "subject_id", "catalog_id", name="uq_chapters_user_subject_catalog"),
        Index("ix_chapters_user_subject_order", "user_id", "subject_id", "order_index"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("chp"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    subject_id: Mapped[str] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="CASCADE"), index=True, nullable=False)
    catalog_id: Mapped[str] = mapped_column(String(120), nullable=False)
    title: Mapped[str] = mapped_column(String(180), nullable=False)
    order_index: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    importance: Mapped[float] = mapped_column(Float, default=1.0, nullable=False)
    estimated_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="available", nullable=False)
    curated: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class StudyPlan(Base):
    __tablename__ = "study_plans"
    __table_args__ = (Index("ix_study_plans_user_status_start", "user_id", "status", "start_date"),)

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("spl"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    name: Mapped[str] = mapped_column(String(180), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="active", nullable=False)
    start_date: Mapped[date] = mapped_column(Date, nullable=False)
    end_date: Mapped[date] = mapped_column(Date, nullable=False)
    daily_minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    algorithm_version: Mapped[str] = mapped_column(String(40), default="adaptive-v1", nullable=False)
    planning_factors: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class DailyPlan(Base):
    """One persisted, explainable plan for one student and local study date."""

    __tablename__ = "daily_plans"
    __table_args__ = (
        UniqueConstraint("user_id", "date", name="uq_daily_plans_user_date"),
        Index("ix_daily_plans_user_status_date", "user_id", "status", "date"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("dpl"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    plan_date: Mapped[date] = mapped_column("date", Date, nullable=False)
    generation: Mapped[int] = mapped_column(Integer, default=1, nullable=False)
    version: Mapped[str] = mapped_column(String(40), default="learning-engine-v1", nullable=False)
    status: Mapped[str] = mapped_column(String(24), default="active", nullable=False)
    available_minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    planned_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    completed_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    input_summary: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    reason_snapshot: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    generated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    regenerated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class DailyTask(Base):
    __tablename__ = "daily_tasks"
    __table_args__ = (
        UniqueConstraint("daily_plan_id", "logical_key", name="uq_daily_tasks_plan_logical_key"),
        Index("ix_daily_tasks_user_status_due", "user_id", "status", "scheduled_for"),
        Index("ix_daily_tasks_plan_due", "study_plan_id", "scheduled_for"),
        Index("ix_daily_tasks_daily_plan_status", "daily_plan_id", "status"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("tsk"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    study_plan_id: Mapped[str] = mapped_column(String(40), ForeignKey("study_plans.id", ondelete="CASCADE"), index=True, nullable=False)
    daily_plan_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_plans.id", ondelete="CASCADE"), index=True, nullable=True)
    logical_key: Mapped[str | None] = mapped_column(String(220), nullable=True)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True)
    task_type: Mapped[str] = mapped_column(String(32), nullable=False)
    title: Mapped[str] = mapped_column(String(220), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="pending", nullable=False)
    scheduled_for: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    duration_minutes: Mapped[int] = mapped_column(Integer, nullable=False)
    priority: Mapped[float] = mapped_column(Float, default=1.0, nullable=False)
    href: Mapped[str | None] = mapped_column(String(500), nullable=True)
    mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True)
    rescheduled_from_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_tasks.id", ondelete="SET NULL"), index=True, nullable=True)
    task_metadata: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False)
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class StudySession(Base):
    __tablename__ = "study_sessions"
    __table_args__ = (Index("ix_study_sessions_user_started", "user_id", "started_at"),)

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("ses"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    daily_task_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_tasks.id", ondelete="SET NULL"), index=True, nullable=True)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True)
    status: Mapped[str] = mapped_column(String(32), default="started", nullable=False)
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    ended_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    duration_minutes: Mapped[int | None] = mapped_column(Integer, nullable=True)
    session_data: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)


class LessonProgress(Base):
    __tablename__ = "lesson_progress"
    __table_args__ = (
        UniqueConstraint("user_id", "chapter_id", "mission_id", name="uq_lesson_progress_user_mission"),
        Index("ix_lesson_progress_user_status", "user_id", "status"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("lpr"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    chapter_id: Mapped[str] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="CASCADE"), index=True, nullable=False)
    mission_id: Mapped[str] = mapped_column(String(120), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="not_started", nullable=False)
    progress_percent: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    current_step: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)


class RevisionItem(Base):
    """One intentional item the student asked DocDoe to bring back for revision.

    RepairItem is deliberately narrower: it models an unresolved wrong answer.
    RevisionItem covers manual saves and lesson recaps as well, with an
    idempotency key so returning to a lesson or double-clicking Save cannot
    manufacture duplicate revision work.
    """

    __tablename__ = "revision_items"
    __table_args__ = (
        UniqueConstraint("user_id", "client_item_id", name="uq_revision_items_user_client"),
        Index("ix_revision_items_user_status_due", "user_id", "status", "due_at"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("rev"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    client_item_id: Mapped[str] = mapped_column(String(180), nullable=False)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True)
    mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True)
    topic_key: Mapped[str | None] = mapped_column(String(180), nullable=True)
    title: Mapped[str] = mapped_column(String(220), nullable=False)
    source_kind: Mapped[str] = mapped_column(String(40), nullable=False)
    source_ref: Mapped[str | None] = mapped_column(String(120), nullable=True)
    status: Mapped[str] = mapped_column(String(24), default="pending", nullable=False)
    due_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    item_data: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class LearningEvent(Base):
    """Append-only, ownership-scoped audit record for meaningful learning actions.

    It is intentionally a transactional application ledger, not a second
    source of truth. Domain rows remain authoritative; this links together the
    action, its owning student, and the record that caused it.
    """

    __tablename__ = "learning_events"
    __table_args__ = (
        UniqueConstraint("user_id", "idempotency_key", name="uq_learning_events_user_key"),
        Index("ix_learning_events_user_occurred", "user_id", "occurred_at"),
        Index("ix_learning_events_user_type_occurred", "user_id", "event_type", "occurred_at"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("lev"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    event_type: Mapped[str] = mapped_column(String(64), nullable=False)
    entity_type: Mapped[str] = mapped_column(String(64), nullable=False)
    entity_id: Mapped[str] = mapped_column(String(100), nullable=False)
    idempotency_key: Mapped[str] = mapped_column(String(220), nullable=False)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True)
    topic_key: Mapped[str | None] = mapped_column(String(180), nullable=True)
    event_data: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False)
    occurred_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)


class StudyFollowUp(Base):
    """A concrete unresolved action promoted from Study Chat into planning."""

    __tablename__ = "study_followups"
    __table_args__ = (
        UniqueConstraint("user_id", "client_followup_id", name="uq_study_followups_user_client"),
        Index("ix_study_followups_user_status_due", "user_id", "status", "due_at"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("sfu"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    chat_session_id: Mapped[str] = mapped_column(String(40), ForeignKey("chat_sessions.id", ondelete="CASCADE"), index=True, nullable=False)
    source_message_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chat_messages.id", ondelete="SET NULL"), nullable=True)
    client_followup_id: Mapped[str] = mapped_column(String(160), nullable=False)
    kind: Mapped[str] = mapped_column(String(40), nullable=False)
    title: Mapped[str] = mapped_column(String(220), nullable=False)
    subject: Mapped[str | None] = mapped_column(String(120), nullable=True)
    chapter: Mapped[str | None] = mapped_column(String(180), nullable=True)
    topic: Mapped[str | None] = mapped_column(String(180), nullable=True)
    status: Mapped[str] = mapped_column(String(24), default="open", nullable=False)
    due_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    estimated_minutes: Mapped[int] = mapped_column(Integer, default=10, nullable=False)
    source_ref: Mapped[str | None] = mapped_column(String(180), nullable=True)
    target_data: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class QuizAttempt(Base):
    __tablename__ = "quiz_attempts"
    __table_args__ = (
        UniqueConstraint(
            "user_id",
            "client_attempt_id",
            name="uq_quiz_attempts_user_client",
        ),
        Index("ix_quiz_attempts_user_completed", "user_id", "completed_at"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("qat"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    client_attempt_id: Mapped[str | None] = mapped_column(String(180), nullable=True)
    quiz_id: Mapped[str | None] = mapped_column(String(40), nullable=True)
    daily_task_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_tasks.id", ondelete="SET NULL"), index=True, nullable=True)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True)
    score: Mapped[float] = mapped_column(Float, nullable=False)
    max_score: Mapped[float] = mapped_column(Float, nullable=False)
    answers: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list, nullable=False)
    corrections: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list, nullable=False)
    missing_keywords: Mapped[list[str]] = mapped_column(JSON, default=list, nullable=False)
    misconceptions: Mapped[list[str]] = mapped_column(JSON, default=list, nullable=False)
    completed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class TopicMastery(Base):
    __tablename__ = "topic_mastery"
    __table_args__ = (
        UniqueConstraint("user_id", "topic_key", name="uq_topic_mastery_user_topic"),
        Index("ix_topic_mastery_user_score_review", "user_id", "score", "next_review_at"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("mst"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True)
    topic_key: Mapped[str] = mapped_column(String(180), nullable=False)
    topic_label: Mapped[str] = mapped_column(String(180), nullable=False)
    score: Mapped[float] = mapped_column(Float, default=0.0, nullable=False)
    confidence: Mapped[float] = mapped_column(Float, default=0.0, nullable=False)
    attempts_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    last_result: Mapped[str | None] = mapped_column(String(32), nullable=True)
    next_review_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    evidence: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class CheckpointEvent(Base):
    """One student answer to one checkpoint question — the atomic evidence unit.

    ``(user_id, client_event_id)`` is unique so double-clicks, retries after
    network errors, and two open tabs cannot double-count evidence: replays
    return the stored consequence instead of re-applying it.
    """

    __tablename__ = "checkpoint_events"
    __table_args__ = (
        UniqueConstraint("user_id", "client_event_id", name="uq_checkpoint_events_user_client"),
        Index("ix_checkpoint_events_user_concept", "user_id", "concept_key", "created_at"),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("ckp"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    client_event_id: Mapped[str] = mapped_column(String(80), nullable=False)
    concept_key: Mapped[str] = mapped_column(String(180), nullable=False)
    question_id: Mapped[str] = mapped_column(String(120), nullable=False)
    attempt_index: Mapped[int] = mapped_column(Integer, default=1, nullable=False)
    kind: Mapped[str] = mapped_column(String(40), nullable=False)
    correct: Mapped[bool] = mapped_column(Boolean, nullable=False)
    error_category: Mapped[str | None] = mapped_column(String(48), nullable=True)
    hint_used: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    payload: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    consequence: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)


class RepairItem(Base):
    """A visible consequence of a wrong answer.

    One OPEN row per (user, concept_key, error_category) — repeated failures
    escalate the same item instead of duplicating it; a successful retry
    resolves it idempotently. DailyTasks link back via
    ``task_metadata["repair_item_id"]`` so the plan and the queue stay one story.
    """

    __tablename__ = "repair_items"
    __table_args__ = (
        Index("ix_repair_items_user_status", "user_id", "status"),
        Index("ix_repair_items_user_concept", "user_id", "concept_key"),
        Index(
            "uq_repair_items_open_concept_error",
            "user_id",
            "concept_key",
            "error_category",
            unique=True,
            postgresql_where=text("status IN ('open', 'escalated')"),
            sqlite_where=text("status IN ('open', 'escalated')"),
        ),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("rep"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), nullable=True)
    concept_key: Mapped[str] = mapped_column(String(180), nullable=False)
    concept_label: Mapped[str] = mapped_column(String(180), nullable=False)
    mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True)
    error_category: Mapped[str] = mapped_column(String(48), nullable=False)
    diagnosis: Mapped[str] = mapped_column(Text, nullable=False)
    recommended_activity: Mapped[str] = mapped_column(String(48), nullable=False)
    activity_prompt: Mapped[str] = mapped_column(Text, nullable=False)
    priority: Mapped[float] = mapped_column(Float, default=1.5, nullable=False)
    estimated_minutes: Mapped[int] = mapped_column(Integer, default=10, nullable=False)
    status: Mapped[str] = mapped_column(String(24), default="open", nullable=False)
    support_level: Mapped[int] = mapped_column(Integer, default=1, nullable=False)
    failed_attempts: Mapped[int] = mapped_column(Integer, default=1, nullable=False)
    retry_result: Mapped[str | None] = mapped_column(String(24), nullable=True)
    mastery_recovered: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    source_kind: Mapped[str] = mapped_column(String(40), default="checkpoint", nullable=False)
    source_ref: Mapped[str | None] = mapped_column(String(80), nullable=True)
    evidence: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)


class GeneratedResource(Base):
    __tablename__ = "generated_resources"
    __table_args__ = (Index("ix_generated_resources_user_type_created", "user_id", "resource_type", "created_at"),)

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("res"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True)
    chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True)
    source_id: Mapped[str | None] = mapped_column(String(80), nullable=True)
    resource_type: Mapped[str] = mapped_column(String(40), nullable=False)
    title: Mapped[str] = mapped_column(String(220), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="ready", nullable=False)
    storage_url: Mapped[str | None] = mapped_column(Text, nullable=True)
    resource_data: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)


class Subscription(Base):
    __tablename__ = "subscriptions"
    __table_args__ = (
        Index("ix_subscriptions_provider_customer", "provider_customer_id", unique=True),
        Index("ix_subscriptions_provider_subscription", "provider_subscription_id", unique=True),
    )

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("subn"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), unique=True, index=True, nullable=False)
    plan_key: Mapped[str] = mapped_column(String(80), default="free", nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="active", nullable=False)
    usage_limits: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False)
    provider_customer_id: Mapped[str | None] = mapped_column(String(180), nullable=True)
    provider_subscription_id: Mapped[str | None] = mapped_column(String(180), nullable=True)
    provider_price_id: Mapped[str | None] = mapped_column(String(180), nullable=True)
    cancel_at_period_end: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    current_period_start: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    current_period_end: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False)


class StripeWebhookEvent(Base):
    """Minimal idempotency ledger for Stripe event delivery.

    The raw webhook payload is deliberately not stored because it can contain
    customer and payment data. The event ID is enough to prevent duplicate
    entitlement changes while still allowing failed transactions to retry.
    """

    __tablename__ = "stripe_webhook_events"

    event_id: Mapped[str] = mapped_column(String(180), primary_key=True)
    event_type: Mapped[str] = mapped_column(String(100), nullable=False)
    processed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        nullable=False,
    )


class UsageEvent(Base):
    __tablename__ = "usage_events"
    __table_args__ = (Index("ix_usage_events_user_type_occurred", "user_id", "event_type", "occurred_at"),)

    id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("uev"))
    user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    event_type: Mapped[str] = mapped_column(String(80), nullable=False)
    resource_type: Mapped[str | None] = mapped_column(String(80), nullable=True)
    units: Mapped[float] = mapped_column(Float, default=1.0, nullable=False)
    event_data: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False)
    occurred_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False)