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_organization
from app.models.user import User
from app.models.school import (
    Organization, School, SchoolOrgAffiliation, SchoolStudentEnrollment
)
from app.models.progress import AcademicProgressSummary

router = APIRouter(
    prefix="/organizations", tags=["Organizations"], dependencies=[Depends(require_organization)]
)


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


@router.get("/profile", summary="Get Organization Profile")
async def get_profile(current_user: CurrentUser, db: DB):
    org = await get_my_org(current_user, db)
    return {"organization": org}


@router.get("/dashboard", summary="Organization Dashboard Stats")
async def get_dashboard(current_user: CurrentUser, db: DB):
    org = await get_my_org(current_user, db)
    total_affiliations = (await db.execute(
        select(func.count(SchoolOrgAffiliation.id)).where(
            SchoolOrgAffiliation.organization_id == org.id,
            SchoolOrgAffiliation.status == "approved",
        )
    )).scalar()
    pending_requests = (await db.execute(
        select(func.count(SchoolOrgAffiliation.id)).where(
            SchoolOrgAffiliation.organization_id == org.id,
            SchoolOrgAffiliation.status == "pending",
        )
    )).scalar()

    return {
        "org_name": org.org_name,
        "org_type": org.org_type,
        "is_verified": org.is_verified,
        "affiliated_schools": total_affiliations,
        "pending_requests": pending_requests,
    }


@router.get("/affiliations", summary="Get School Affiliations")
async def get_affiliations(current_user: CurrentUser, db: DB, status: Optional[str] = None):
    org = await get_my_org(current_user, db)
    query = (
        select(SchoolOrgAffiliation, School)
        .join(School, SchoolOrgAffiliation.school_id == School.id)
        .where(SchoolOrgAffiliation.organization_id == org.id)
    )
    if status:
        query = query.where(SchoolOrgAffiliation.status == status)

    result = await db.execute(query)
    rows = result.all()
    return [
        {
            "school_name": row.School.school_name,
            "school_code": row.School.school_code,
            "city": row.School.city,
            "status": row.SchoolOrgAffiliation.status,
            "affiliation_date": row.SchoolOrgAffiliation.affiliation_date,
        }
        for row in rows
    ]


@router.post("/affiliations/request/{school_code}", summary="Request Affiliation with a School")
async def request_affiliation(school_code: str, current_user: CurrentUser, db: DB):
    """Send an affiliation request to a school."""
    if not current_user:
        raise HTTPException(status_code=401)

    org = await get_my_org(current_user, db)
    if not org.is_verified:
        raise HTTPException(status_code=403, detail="Organization must be verified first")

    school = await db.execute(
        select(School).where(School.school_code == school_code, School.is_verified == True)
    )
    school_obj = school.scalar_one_or_none()
    if not school_obj:
        raise HTTPException(status_code=404, detail="School not found or not verified")

    existing = await db.execute(
        select(SchoolOrgAffiliation).where(
            SchoolOrgAffiliation.school_id == school_obj.id,
            SchoolOrgAffiliation.organization_id == org.id,
        )
    )
    if existing.scalar_one_or_none():
        raise HTTPException(status_code=400, detail="Affiliation already exists or pending")

    affiliation = SchoolOrgAffiliation(
        school_id=school_obj.id,
        organization_id=org.id,
        status="pending",
    )
    db.add(affiliation)
    await db.commit()
    return {"message": f"Affiliation request sent to {school_obj.school_name}"}


@router.delete("/affiliations/{school_code}", summary="Cancel Affiliation")
async def cancel_affiliation(school_code: str, current_user: CurrentUser, db: DB):
    org = await get_my_org(current_user, db)
    school = await db.execute(
        select(School).where(School.school_code == school_code)
    )
    school_obj = school.scalar_one_or_none()
    if not school_obj:
        raise HTTPException(status_code=404, detail="School not found")

    affiliation = await db.execute(
        select(SchoolOrgAffiliation).where(
            SchoolOrgAffiliation.school_id == school_obj.id,
            SchoolOrgAffiliation.organization_id == org.id,
        )
    )
    aff = affiliation.scalar_one_or_none()
    if not aff:
        raise HTTPException(status_code=404, detail="Affiliation not found")

    await db.delete(aff)
    await db.commit()
    return {"message": "Affiliation cancelled"}


@router.get("/schools/{school_code}/progress", summary="View Affiliated School Progress")
async def view_school_progress(school_code: str, current_user: CurrentUser, db: DB):
    """View aggregate progress data for an affiliated school."""
    org = await get_my_org(current_user, db)
    school = await db.execute(
        select(School).where(School.school_code == school_code)
    )
    school_obj = school.scalar_one_or_none()
    if not school_obj:
        raise HTTPException(status_code=404, detail="School not found")

    # Verify affiliation
    affiliation = await db.execute(
        select(SchoolOrgAffiliation).where(
            SchoolOrgAffiliation.school_id == school_obj.id,
            SchoolOrgAffiliation.organization_id == org.id,
            SchoolOrgAffiliation.status == "approved",
        )
    )
    if not affiliation.scalar_one_or_none():
        raise HTTPException(status_code=403, detail="Not affiliated with this school")

    # Get student IDs
    enrolled = await db.execute(
        select(SchoolStudentEnrollment.student_user_id).where(
            SchoolStudentEnrollment.school_id == school_obj.id,
            SchoolStudentEnrollment.is_active == True,
        )
    )
    student_ids = [row[0] for row in enrolled.all()]

    total_students = len(student_ids)
    if not student_ids:
        return {"school_name": school_obj.school_name, "total_students": 0, "progress": []}

    progress = await db.execute(
        select(AcademicProgressSummary).where(
            AcademicProgressSummary.student_user_id.in_(student_ids)
        ).limit(200)
    )

    return {
        "school_name": school_obj.school_name,
        "total_students": total_students,
        "progress": progress.scalars().all(),
    }