"""
DRP Backend — SQLAlchemy Models
All 12+ tables as defined in the PRD and implementation plan.
"""

import uuid
from datetime import datetime

from sqlalchemy import (
    Column, String, Integer, Float, Boolean, DateTime, Text,
    ForeignKey, JSON
)
from sqlalchemy.orm import relationship
from database import Base


def generate_uuid():
    return str(uuid.uuid4())


# ---------------------------------------------------------------------------
# Geographic / Organisational Hierarchy
# ---------------------------------------------------------------------------

class Programme(Base):
    __tablename__ = "programme"

    programme_id = Column(String, primary_key=True, default=generate_uuid)
    name = Column(String, nullable=False)
    state_code = Column(String, nullable=False)
    year = Column(Integer, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    schools = relationship("School", back_populates="programme")


class District(Base):
    __tablename__ = "district"

    district_id = Column(String, primary_key=True, default=generate_uuid)
    name = Column(String, nullable=False)
    state_code = Column(String, nullable=False)

    schools = relationship("School", back_populates="district")


class School(Base):
    __tablename__ = "school"

    school_id = Column(String, primary_key=True, default=generate_uuid)
    udise_code = Column(String, unique=True, nullable=False)
    name = Column(String, nullable=False)
    district_id = Column(String, ForeignKey("district.district_id"))
    block = Column(String)
    programme_id = Column(String, ForeignKey("programme.programme_id"))
    whatsapp_group_link = Column(String, nullable=True)  # For teacher onboarding
    created_at = Column(DateTime, default=datetime.utcnow)

    programme = relationship("Programme", back_populates="schools")
    district = relationship("District", back_populates="schools")
    classes = relationship("ClassAccount", back_populates="school")
    teachers = relationship("TeacherAccount", back_populates="school")


# ---------------------------------------------------------------------------
# Class & Teacher
# ---------------------------------------------------------------------------

class ClassAccount(Base):
    __tablename__ = "class_account"

    class_id = Column(String, primary_key=True, default=generate_uuid)
    school_id = Column(String, ForeignKey("school.school_id"), nullable=False)
    grade = Column(Integer, nullable=False)
    section = Column(String, nullable=False)
    year = Column(Integer, nullable=False)
    state = Column(String, default="not_started")  # not_started | in_progress | completed
    current_cluster_id = Column(String, ForeignKey("cluster.cluster_id"), nullable=True)
    reading_level = Column(String, nullable=True)  # Set during level selection
    whatsapp_group_link = Column(String, nullable=True)  # Class-specific link
    created_at = Column(DateTime, default=datetime.utcnow)

    school = relationship("School", back_populates="classes")
    journey = relationship("ClassJourney", back_populates="class_account", uselist=False)
    sessions = relationship("SessionLog", back_populates="class_account")
    achievements = relationship("Achievement", back_populates="class_account")
    teacher_mappings = relationship("TeacherClassMapping", back_populates="class_account")


class TeacherAccount(Base):
    __tablename__ = "teacher_account"

    teacher_id = Column(String, primary_key=True, default=generate_uuid)
    name = Column(String, nullable=False)
    mobile_hash = Column(String, nullable=True)
    phone = Column(String, nullable=True)  # For prototype display
    email = Column(String, unique=True, nullable=True)  # For email login
    password_hash = Column(String, nullable=True)  # For email login
    school_id = Column(String, ForeignKey("school.school_id"), nullable=True)
    role = Column(String, default="teacher")  # teacher | substitute
    activation_token = Column(String, unique=True, nullable=True)
    is_active = Column(Boolean, default=False)
    has_seen_onboarding = Column(Boolean, default=False)  # Onboarding wizard flag
    created_at = Column(DateTime, default=datetime.utcnow)

    school = relationship("School", back_populates="teachers")
    class_mappings = relationship("TeacherClassMapping", back_populates="teacher")
    sessions = relationship("SessionLog", back_populates="teacher")


class TeacherClassMapping(Base):
    __tablename__ = "teacher_class_mapping"

    id = Column(String, primary_key=True, default=generate_uuid)
    teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=False)
    class_id = Column(String, ForeignKey("class_account.class_id"), nullable=False)
    role = Column(String, default="primary")  # primary | substitute
    is_default = Column(Boolean, default=False)

    teacher = relationship("TeacherAccount", back_populates="class_mappings")
    class_account = relationship("ClassAccount", back_populates="teacher_mappings")


# ---------------------------------------------------------------------------
# Content: Books & Clusters
# ---------------------------------------------------------------------------

class Book(Base):
    __tablename__ = "book"

    book_id = Column(String, primary_key=True, default=generate_uuid)
    title = Column(String, nullable=False)
    author = Column(String)
    illustrator = Column(String)
    level = Column(Integer, nullable=False)  # 1, 2, 3, 4
    theme = Column(String, nullable=False)   # Animals, Nature, Friendship, etc.
    language = Column(String, default="English")
    cover_url = Column(String)
    has_readalong = Column(Boolean, default=False)
    pages = Column(JSON, default=list)  # List of {page_num, text, image_url}
    activity_lets_talk = Column(Text, nullable=True)  # Discussion prompt (not scored)
    activity_lets_understand = Column(JSON, nullable=True)  # MCQ: {question, options[], correct}
    activity_lets_play = Column(JSON, nullable=True)  # MCQ/rhyme/SEL: {question, options[], correct}
    read_count = Column(Integer, default=0)


class Cluster(Base):
    __tablename__ = "cluster"

    cluster_id = Column(String, primary_key=True, default=generate_uuid)
    grade = Column(Integer, nullable=False)
    level = Column(Integer, nullable=False)
    theme = Column(String, nullable=False)
    book_ids = Column(JSON, nullable=False)  # List of 3 book_ids
    connecting_thread = Column(String, nullable=True)  # Narrative thread tying the 3 books
    secondary_theme = Column(String, nullable=True)  # Fallback if insufficient books


# ---------------------------------------------------------------------------
# Journey & Progress
# ---------------------------------------------------------------------------

class ClassJourney(Base):
    __tablename__ = "class_journey"

    journey_id = Column(String, primary_key=True, default=generate_uuid)
    class_id = Column(String, ForeignKey("class_account.class_id"), unique=True, nullable=False)
    cluster_sequence = Column(JSON, default=list)  # Ordered list of cluster_ids
    completed_cluster_ids = Column(JSON, default=list)  # Subset of cluster_sequence
    current_book_index = Column(Integer, default=0)  # 0-2 within current cluster
    total_books_read = Column(Integer, default=0)

    class_account = relationship("ClassAccount", back_populates="journey")


class SessionLog(Base):
    __tablename__ = "session_log"

    session_id = Column(String, primary_key=True, default=generate_uuid)
    class_id = Column(String, ForeignKey("class_account.class_id"), nullable=False)
    teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=True)
    substitute_name = Column(String, nullable=True)
    book_id = Column(String, ForeignKey("book.book_id"), nullable=False)
    cluster_id = Column(String, ForeignKey("cluster.cluster_id"), nullable=False)
    timestamp = Column(DateTime, default=datetime.utcnow)
    device_type = Column(String, default="web")
    readalong_done = Column(Boolean, default=False)
    activity_submitted = Column(Boolean, default=False)
    completed = Column(Boolean, default=False)

    class_account = relationship("ClassAccount", back_populates="sessions")
    teacher = relationship("TeacherAccount", back_populates="sessions")
    activity_submissions = relationship("ActivitySubmission", back_populates="session")
    feedback = relationship("Feedback", back_populates="session", uselist=False)


class ActivitySubmission(Base):
    __tablename__ = "activity_submission"

    submission_id = Column(String, primary_key=True, default=generate_uuid)
    session_id = Column(String, ForeignKey("session_log.session_id"), nullable=False)
    activity_type = Column(String, nullable=False)  # lets_understand | lets_play
    selected_option = Column(String, nullable=False)
    is_correct = Column(Boolean, nullable=False)
    score = Column(Float, default=0.0)
    submitted_at = Column(DateTime, default=datetime.utcnow)

    session = relationship("SessionLog", back_populates="activity_submissions")


class Feedback(Base):
    __tablename__ = "feedback"

    feedback_id = Column(String, primary_key=True, default=generate_uuid)
    session_id = Column(String, ForeignKey("session_log.session_id"), unique=True, nullable=False)
    feedback_emoji = Column(String, nullable=True)  # e.g., 😊, 😐, 😟
    feedback_quality = Column(String, nullable=True)  # e.g., excellent, good, needs_work

    session = relationship("SessionLog", back_populates="feedback")


class Achievement(Base):
    __tablename__ = "achievement"

    achievement_id = Column(String, primary_key=True, default=generate_uuid)
    class_id = Column(String, ForeignKey("class_account.class_id"), nullable=False)
    type = Column(String, nullable=False)  # cluster_complete | programme_complete
    badge_key = Column(String, nullable=True)
    earned_at = Column(DateTime, default=datetime.utcnow)

    class_account = relationship("ClassAccount", back_populates="achievements")


# ---------------------------------------------------------------------------
# Admin & Programme Team
# ---------------------------------------------------------------------------

class AdminAccount(Base):
    __tablename__ = "admin_account"

    admin_id = Column(String, primary_key=True, default=generate_uuid)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)
    password_hash = Column(String, nullable=False)
    role = Column(String, nullable=False)  # school_admin | district_admin | state_admin | programme_team
    school_id = Column(String, ForeignKey("school.school_id"), nullable=True)
    district_id = Column(String, ForeignKey("district.district_id"), nullable=True)
    state_code = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)


class EscalationTicket(Base):
    __tablename__ = "escalation_ticket"

    ticket_id = Column(String, primary_key=True, default=generate_uuid)
    type = Column(String, nullable=False)  # content | technical | access | other
    severity = Column(String, default="medium")  # low | medium | high | critical
    school_id = Column(String, ForeignKey("school.school_id"), nullable=True)
    class_id = Column(String, ForeignKey("class_account.class_id"), nullable=True)
    description = Column(Text, nullable=False)
    status = Column(String, default="open")  # open | in_progress | resolved | closed
    created_by = Column(String, nullable=True)
    assigned_to = Column(String, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    resolved_at = Column(DateTime, nullable=True)


class SystemHealthLog(Base):
    __tablename__ = "system_health_log"

    log_id = Column(String, primary_key=True, default=generate_uuid)
    timestamp = Column(DateTime, default=datetime.utcnow)
    api_latency_ms = Column(Float, default=0.0)
    error_count = Column(Integer, default=0)
    active_sessions = Column(Integer, default=0)
    sync_queue_depth = Column(Integer, default=0)
    uptime_percent = Column(Float, default=100.0)


class EventLog(Base):
    __tablename__ = "event_log"

    event_id = Column(String, primary_key=True, default=generate_uuid)
    event_type = Column(String, nullable=False)  # page_view | book_open | activity_submit | etc.
    actor_type = Column(String, nullable=True)  # teacher | admin | system
    actor_id = Column(String, nullable=True)
    metadata_json = Column(JSON, nullable=True)
    timestamp = Column(DateTime, default=datetime.utcnow)


class Notification(Base):
    __tablename__ = "notification"

    notification_id = Column(String, primary_key=True, default=generate_uuid)
    teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=True)
    class_id = Column(String, ForeignKey("class_account.class_id"), nullable=True)
    type = Column(String, nullable=False)  # reminder | nudge | alert
    message = Column(Text)
    status = Column(String, default="pending")  # pending | sent | read
    created_at = Column(DateTime, default=datetime.utcnow)


# ---------------------------------------------------------------------------
# Field Staff
# ---------------------------------------------------------------------------

class FieldStaff(Base):
    __tablename__ = "field_staff"

    staff_id = Column(String, primary_key=True, default=generate_uuid)
    name = Column(String, nullable=False)
    phone = Column(String, nullable=True)
    email = Column(String, unique=True, nullable=False)
    password_hash = Column(String, nullable=False)
    district_id = Column(String, ForeignKey("district.district_id"), nullable=False)
    assigned_blocks = Column(JSON, default=list)  # List of block names
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)

    school_mappings = relationship("SchoolStaffMapping", back_populates="staff")


class SchoolStaffMapping(Base):
    __tablename__ = "school_staff_mapping"

    id = Column(String, primary_key=True, default=generate_uuid)
    staff_id = Column(String, ForeignKey("field_staff.staff_id"), nullable=False)
    school_id = Column(String, ForeignKey("school.school_id"), nullable=False)
    is_primary = Column(Boolean, default=True)

    staff = relationship("FieldStaff", back_populates="school_mappings")


class TrainingLog(Base):
    __tablename__ = "training_log"

    log_id = Column(String, primary_key=True, default=generate_uuid)
    staff_id = Column(String, ForeignKey("field_staff.staff_id"), nullable=False)
    school_id = Column(String, ForeignKey("school.school_id"), nullable=False)
    date = Column(DateTime, default=datetime.utcnow)
    teachers_trained_count = Column(Integer, default=0)
    notes = Column(Text, nullable=True)


class SupportLog(Base):
    __tablename__ = "support_log"

    log_id = Column(String, primary_key=True, default=generate_uuid)
    staff_id = Column(String, ForeignKey("field_staff.staff_id"), nullable=False)
    teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=True)
    issue_type = Column(String, nullable=False)  # login | technical | content | process | device
    description = Column(Text, nullable=False)
    resolution = Column(Text, nullable=True)
    timestamp = Column(DateTime, default=datetime.utcnow)