from fastapi import APIRouter, Depends, HTTPException, Query
from sqlalchemy import select, func
from typing import Optional
from datetime import date

from app.core.deps import DB, CurrentUser, require_school
from app.models.user import User, TeacherProfile, StudentProfile
from app.models.school import (
    School, SchoolStudentEnrollment, SchoolTeacherAssignment,
    Organization, SchoolOrgAffiliation, SchoolPaymentAccount
)
from app.models.progress import AcademicProgressSummary
from app.models.payment import Subscription

router = APIRouter(
    prefix="/schools", tags=["Schools"], dependencies=[Depends(require_school)]
)


async def get_my_school(current_user: CurrentUser, db: DB) -> School:
    result = await db.execute(
        select(School).where(School.user_id == current_user.id)
    )
    school = result.scalar_one_or_none()
    if not school:
        raise HTTPException(status_code=404, detail="School profile not found")
    return school


@router.get("/profile", summary="Get School Profile")
async def get_school_profile(current_user: CurrentUser, db: DB):
    school = await get_my_school(current_user, db)
    account = await db.execute(
        select(SchoolPaymentAccount).where(SchoolPaymentAccount.school_id == school.id)
    )
    return {
        "school": school,
        "payment_account": account.scalar_one_or_none(),
    }


@router.get("/dashboard", summary="School Dashboard Stats")
async def get_dashboard(current_user: CurrentUser, db: DB):
    school = await get_my_school(current_user, db)
    total_students = (await db.execute(
        select(func.count(SchoolStudentEnrollment.id)).where(
            SchoolStudentEnrollment.school_id == school.id,
            SchoolStudentEnrollment.is_active == True,
        )
    )).scalar()
    total_teachers = (await db.execute(
        select(func.count(SchoolTeacherAssignment.id)).where(
            SchoolTeacherAssignment.school_id == school.id,
            SchoolTeacherAssignment.is_active == True,
        )
    )).scalar()
    affiliations = (await db.execute(
        select(func.count(SchoolOrgAffiliation.id)).where(
            SchoolOrgAffiliation.school_id == school.id,
            SchoolOrgAffiliation.status == "approved",
        )
    )).scalar()

    return {
        "school_name": school.school_name,
        "school_code": school.school_code,
        "access_code": school.access_code,
        "is_verified": school.is_verified,
        "total_students": total_students,
        "total_teachers": total_teachers,
        "affiliated_organizations": affiliations,
    }


# ===================== STUDENTS =====================

@router.get("/students", summary="List Enrolled Students")
async def list_students(
    current_user: CurrentUser,
    db: DB,
    search: Optional[str] = None,
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
):
    school = await get_my_school(current_user, db)
    query = (
        select(User, SchoolStudentEnrollment)
        .join(SchoolStudentEnrollment, User.id == SchoolStudentEnrollment.student_user_id)
        .where(
            SchoolStudentEnrollment.school_id == school.id,
            SchoolStudentEnrollment.is_active == True,
        )
    )
    if search:
        from sqlalchemy import or_
        query = query.where(
            or_(
                User.first_name.ilike(f"%{search}%"),
                User.last_name.ilike(f"%{search}%"),
                User.stem_id.ilike(f"%{search}%"),
            )
        )

    total = (await db.execute(select(func.count()).select_from(query.subquery()))).scalar()
    result = await db.execute(query.offset((page - 1) * page_size).limit(page_size))
    rows = result.all()

    return {
        "items": [
            {
                "stem_id": row.User.stem_id,
                "full_name": row.User.full_name,
                "enrollment_date": row.SchoolStudentEnrollment.enrollment_date,
            }
            for row in rows
        ],
        "total": total,
        "page": page,
        "page_size": page_size,
    }


@router.post("/students/enroll/{student_stem_id}", summary="Enroll Student by STEM ID")
async def enroll_student(student_stem_id: str, current_user: CurrentUser, db: DB):
    school = await get_my_school(current_user, db)
    student = await db.execute(
        select(User).where(User.stem_id == student_stem_id, User.role == "student")
    )
    student_user = student.scalar_one_or_none()
    if not student_user:
        raise HTTPException(status_code=404, detail="Student not found")

    existing = await db.execute(
        select(SchoolStudentEnrollment).where(
            SchoolStudentEnrollment.student_user_id == student_user.id,
            SchoolStudentEnrollment.school_id == school.id,
            SchoolStudentEnrollment.is_active == True,
        )
    )
    if existing.scalar_one_or_none():
        raise HTTPException(status_code=400, detail="Student already enrolled")

    enrollment = SchoolStudentEnrollment(
        school_id=school.id,
        student_user_id=student_user.id,
        enrollment_date=date.today(),
    )
    db.add(enrollment)
    await db.commit()
    return {"message": f"Student {student_user.full_name} enrolled"}


@router.delete("/students/{student_stem_id}", summary="Withdraw Student")
async def withdraw_student(student_stem_id: str, current_user: CurrentUser, db: DB):
    school = await get_my_school(current_user, db)
    student = await db.execute(select(User).where(User.stem_id == student_stem_id))
    student_user = student.scalar_one_or_none()
    if not student_user:
        raise HTTPException(status_code=404, detail="Student not found")

    enrollment = await db.execute(
        select(SchoolStudentEnrollment).where(
            SchoolStudentEnrollment.student_user_id == student_user.id,
            SchoolStudentEnrollment.school_id == school.id,
            SchoolStudentEnrollment.is_active == True,
        )
    )
    enroll = enrollment.scalar_one_or_none()
    if not enroll:
        raise HTTPException(status_code=404, detail="Enrollment not found")

    enroll.is_active = False
    enroll.withdrawal_date = date.today()
    await db.commit()
    return {"message": "Student withdrawn"}


# ===================== TEACHERS =====================

@router.get("/teachers", summary="List Assigned Teachers")
async def list_teachers(current_user: CurrentUser, db: DB):
    school = await get_my_school(current_user, db)
    result = await db.execute(
        select(User, SchoolTeacherAssignment, TeacherProfile)
        .join(SchoolTeacherAssignment, User.id == SchoolTeacherAssignment.teacher_user_id)
        .outerjoin(TeacherProfile, User.id == TeacherProfile.user_id)
        .where(
            SchoolTeacherAssignment.school_id == school.id,
            SchoolTeacherAssignment.is_active == True,
        )
    )
    rows = result.all()
    return [
        {
            "stem_id": row.User.stem_id,
            "full_name": row.User.full_name,
            "teacher_code": row.TeacherProfile.teacher_code if row.TeacherProfile else None,
            "subject_ids": row.SchoolTeacherAssignment.subject_ids,
            "assignment_date": row.SchoolTeacherAssignment.assignment_date,
        }
        for row in rows
    ]


@router.post("/teachers/assign/{teacher_code}", summary="Assign Teacher by Teacher Code")
async def assign_teacher(
    teacher_code: str,
    current_user: CurrentUser,
    db: DB,
    subject_ids: Optional[list] = None,
):
    school = await get_my_school(current_user, db)
    teacher_profile = await db.execute(
        select(TeacherProfile).where(TeacherProfile.teacher_code == teacher_code)
    )
    profile = teacher_profile.scalar_one_or_none()
    if not profile:
        raise HTTPException(status_code=404, detail="Teacher code not found")

    existing = await db.execute(
        select(SchoolTeacherAssignment).where(
            SchoolTeacherAssignment.teacher_user_id == profile.user_id,
            SchoolTeacherAssignment.school_id == school.id,
            SchoolTeacherAssignment.is_active == True,
        )
    )
    if existing.scalar_one_or_none():
        raise HTTPException(status_code=400, detail="Teacher already assigned")

    assignment = SchoolTeacherAssignment(
        school_id=school.id,
        teacher_user_id=profile.user_id,
        subject_ids=subject_ids or [],
        assignment_date=date.today(),
    )
    db.add(assignment)
    await db.commit()
    return {"message": "Teacher assigned successfully"}


@router.delete("/teachers/{teacher_code}", summary="Remove Teacher Assignment")
async def remove_teacher(teacher_code: str, current_user: CurrentUser, db: DB):
    school = await get_my_school(current_user, db)
    profile = await db.execute(
        select(TeacherProfile).where(TeacherProfile.teacher_code == teacher_code)
    )
    teacher_profile = profile.scalar_one_or_none()
    if not teacher_profile:
        raise HTTPException(status_code=404, detail="Teacher not found")

    assignment = await db.execute(
        select(SchoolTeacherAssignment).where(
            SchoolTeacherAssignment.teacher_user_id == teacher_profile.user_id,
            SchoolTeacherAssignment.school_id == school.id,
            SchoolTeacherAssignment.is_active == True,
        )
    )
    assign = assignment.scalar_one_or_none()
    if not assign:
        raise HTTPException(status_code=404, detail="Assignment not found")
    assign.is_active = False
    await db.commit()
    return {"message": "Teacher removed from school"}


# ===================== ORGANIZATIONS =====================

@router.get("/affiliations", summary="Get Organization Affiliations")
async def get_affiliations(current_user: CurrentUser, db: DB):
    school = await get_my_school(current_user, db)
    result = await db.execute(
        select(SchoolOrgAffiliation, Organization)
        .join(Organization, SchoolOrgAffiliation.organization_id == Organization.id)
        .where(SchoolOrgAffiliation.school_id == school.id)
    )
    rows = result.all()
    return [
        {
            "org_name": row.Organization.org_name,
            "org_type": row.Organization.org_type,
            "status": row.SchoolOrgAffiliation.status,
            "affiliation_date": row.SchoolOrgAffiliation.affiliation_date,
        }
        for row in rows
    ]


# ===================== PROGRESS REPORTS =====================

@router.get("/reports/progress", summary="School-wide Progress Report")
async def get_progress_report(current_user: CurrentUser, db: DB, subject_id: Optional[int] = None):
    school = await get_my_school(current_user, db)
    # Get all student IDs enrolled in this school
    enrolled = await db.execute(
        select(SchoolStudentEnrollment.student_user_id).where(
            SchoolStudentEnrollment.school_id == school.id,
            SchoolStudentEnrollment.is_active == True,
        )
    )
    student_ids = [row[0] for row in enrolled.all()]

    if not student_ids:
        return []

    query = select(AcademicProgressSummary).where(
        AcademicProgressSummary.student_user_id.in_(student_ids)
    )
    if subject_id:
        query = query.where(AcademicProgressSummary.subject_id == subject_id)

    result = await db.execute(query)
    return result.scalars().all()