from sqlalchemy import (
    Column, Integer, String, Boolean, DateTime, Enum, Text, JSON,
    ForeignKey, Date, func
)
from sqlalchemy.orm import relationship
from app.core.database import Base


class School(Base):
    __tablename__ = "schools"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), unique=True, nullable=False)
    school_name = Column(String(255), nullable=False)
    school_code = Column(String(20), unique=True, nullable=False)
    school_type = Column(Enum("primary", "secondary", "both", "university", "other"), default="primary")
    registration_number = Column(String(100), nullable=True)
    logo_url = Column(Text, nullable=True)
    website_url = Column(String(500), nullable=True)
    address = Column(Text, nullable=True)
    city = Column(String(100), nullable=True)
    state_province = Column(String(100), nullable=True)
    country = Column(String(100), default="Uganda")
    contact_email = Column(String(255), nullable=True)
    contact_phone = Column(String(20), nullable=True)
    access_code = Column(String(50), nullable=True)
    is_verified = Column(Boolean, default=False)
    verified_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    verified_at = Column(DateTime, nullable=True)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    user = relationship("User", back_populates="school", foreign_keys=[user_id])
    verified_by = relationship("User", foreign_keys=[verified_by_user_id])
    student_enrollments = relationship("SchoolStudentEnrollment", back_populates="school")
    teacher_assignments = relationship("SchoolTeacherAssignment", back_populates="school")
    org_affiliations = relationship("SchoolOrgAffiliation", back_populates="school")
    payment_account = relationship("SchoolPaymentAccount", back_populates="school", uselist=False)


class Organization(Base):
    __tablename__ = "organizations"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), unique=True, nullable=False)
    org_name = Column(String(255), nullable=False)
    org_code = Column(String(20), unique=True, nullable=False)
    org_type = Column(
        Enum("government", "ngo", "private", "academic", "faith_based", "other"),
        default="private"
    )
    registration_number = Column(String(100), nullable=True)
    logo_url = Column(Text, nullable=True)
    website_url = Column(String(500), nullable=True)
    address = Column(Text, nullable=True)
    city = Column(String(100), nullable=True)
    country = Column(String(100), default="Uganda")
    contact_email = Column(String(255), nullable=True)
    contact_phone = Column(String(20), nullable=True)
    is_verified = Column(Boolean, default=False)
    verified_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    verified_at = Column(DateTime, nullable=True)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    user = relationship("User", back_populates="organization", foreign_keys=[user_id])
    verified_by = relationship("User", foreign_keys=[verified_by_user_id])
    school_affiliations = relationship("SchoolOrgAffiliation", back_populates="org")


class SchoolOrgAffiliation(Base):
    __tablename__ = "school_org_affiliations"

    id = Column(Integer, primary_key=True)
    school_id = Column(Integer, ForeignKey("schools.id", ondelete="CASCADE"), nullable=False)
    org_id = Column(Integer, ForeignKey("organizations.id", ondelete="CASCADE"), nullable=False)
    status = Column(Enum("pending", "approved", "rejected"), default="pending")
    requested_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    reviewed_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    reviewed_at = Column(DateTime, nullable=True)
    notes = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    school = relationship("School", back_populates="org_affiliations")
    org = relationship("Organization", back_populates="school_affiliations")
    requested_by = relationship("User", foreign_keys=[requested_by_user_id])
    reviewed_by = relationship("User", foreign_keys=[reviewed_by_user_id])


class SchoolStudentEnrollment(Base):
    __tablename__ = "school_student_enrollments"

    id = Column(Integer, primary_key=True)
    school_id = Column(Integer, ForeignKey("schools.id", ondelete="CASCADE"), nullable=False)
    student_user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    grade_id = Column(Integer, ForeignKey("grades.id"), nullable=True)
    enrollment_date = Column(Date, nullable=False)
    grade_at_enrollment = Column(String(10), nullable=True)
    end_date = Column(Date, nullable=True)
    is_active = Column(Boolean, default=True)
    enrolled_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    notes = Column(Text, nullable=True)
    created_at = Column(DateTime, server_default=func.now())

    school = relationship("School", back_populates="student_enrollments")
    student_user = relationship("StudentProfile", primaryjoin="foreign(SchoolStudentEnrollment.student_user_id) == StudentProfile.user_id", viewonly=True)
    grade = relationship("Grade", back_populates="enrollments")
    enrolled_by = relationship("User", foreign_keys=[enrolled_by_user_id])


class SchoolTeacherAssignment(Base):
    __tablename__ = "school_teacher_assignments"

    id = Column(Integer, primary_key=True)
    school_id = Column(Integer, ForeignKey("schools.id", ondelete="CASCADE"), nullable=False)
    teacher_user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    subject_ids = Column(JSON, nullable=True)
    join_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=True)
    is_active = Column(Boolean, default=True)
    assigned_by_user_id = Column(Integer, ForeignKey("users.id"), nullable=True)
    created_at = Column(DateTime, server_default=func.now())

    school = relationship("School", back_populates="teacher_assignments")
    teacher_user = relationship("TeacherProfile", primaryjoin="foreign(SchoolTeacherAssignment.teacher_user_id) == TeacherProfile.user_id", viewonly=True)
    assigned_by = relationship("User", foreign_keys=[assigned_by_user_id])


class SchoolPaymentAccount(Base):
    __tablename__ = "school_payment_accounts"

    id = Column(Integer, primary_key=True)
    school_id = Column(Integer, ForeignKey("schools.id", ondelete="CASCADE"), nullable=False)
    balance = Column(Integer, default=0)
    currency = Column(String(3), default="UGX")
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    school = relationship("School", back_populates="payment_account")