from app.models.keterangan import Keterangan
from app.models.role import Role
from app.models.unit import Unit
from sqlalchemy.orm import Session, aliased, joinedload
from sqlalchemy import select, func, and_, or_
from app.models.pengajuan_ijin import PengajuanIjin
from app.models.user import User
from app.models.status import Status
from app.utils.db import fetch_all, paginate, fetch_one
from fastapi import HTTPException, status as http_status

u = aliased(User)  # users as u
u1 = aliased(User)  # users as u1 (pengganti)
u2 = aliased(User)  # kanit (tidak dipakai di select, tapi tetap join)
u3 = aliased(User)  # tu
u4 = aliased(User)  # manager
s = aliased(Status)
k = aliased(Keterangan)


def get_pengajuan_ijin(db, params, user, owned: bool = False):
    itemsPerPage = int(params.get("itemsPerPage", 0))
    page = int(params.get("page", 1))
    sortBy = params.get("sortBy", "updated_at")
    sortOrder = params.get("sortOrder", "desc")
    stmt = (
        select(
            PengajuanIjin.id,
            PengajuanIjin.user_id,
            u.name.label("name"),
            u.unit_id.label("unit_id"),
            PengajuanIjin.status_slug,
            k.name.label("keterangan"),
            s.name.label("status"),
            s.level.label("status_level"),
            PengajuanIjin.kanit_id,
            PengajuanIjin.tu_id,
            PengajuanIjin.manager_id,
            PengajuanIjin.jumlah_hari,
            PengajuanIjin.start_date,
            PengajuanIjin.end_date,
            PengajuanIjin.created_at,
            PengajuanIjin.updated_at,
        )
        .outerjoin(u, u.id == PengajuanIjin.user_id)
        .outerjoin(s, s.slug == PengajuanIjin.status_slug)
        .outerjoin(u2, u2.id == PengajuanIjin.kanit_id)
        .outerjoin(u3, u3.id == PengajuanIjin.tu_id)
        .outerjoin(u4, u4.id == PengajuanIjin.manager_id)
        .outerjoin(k, k.id == PengajuanIjin.keterangan_id)
    )
    stmt, conditions = __generate_conditions(params, owned, user, stmt)
    stmt = stmt.where(*conditions)
    rows = paginate(
        db, stmt, page=page, per_page=itemsPerPage, sort_by=sortBy, sort_order=sortOrder
    )
    return rows


def get_ijin_agg(db, params, user, owned=False):
    q = (
        select(PengajuanIjin.status_slug, func.count(PengajuanIjin.id).label("total"))
        .outerjoin(u, u.id == PengajuanIjin.user_id)
        .outerjoin(s, s.slug == PengajuanIjin.status_slug)
    )

    q, conditions = __generate_conditions(params, owned, user, q)
    q = q.where(*conditions).group_by(PengajuanIjin.status_slug)
    rows = fetch_all(db, q)

    def get(ids):
        return sum(x["total"] for x in rows if x["status_slug"] in ids)

    return {
        "Diajukan": get(
            [
                "diajukan",
                "disetujui_atasan",
                "disetujui_manager",
            ]
        ),
        "Dibatalkan": get(["dibatalkan"]),
        "Ditolak": get(["ditolak_atasan", "ditolak_manager", "ditolak_tu"]),
        "Disetujui": get(["disetujui_tu"]),
    }


def __generate_conditions(params, owned, user, stmt):
    from_date = params.get("from")
    to_date = params.get("to")
    status = params.get("status")
    conditions = []
    if from_date and to_date:
        conditions.append(
            and_(
                PengajuanIjin.tanggal <= to_date,
                PengajuanIjin.tanggal >= from_date,
            )
        )
    if status:
        conditions.append(PengajuanIjin.status_slug == status)
    if owned:
        conditions.append(PengajuanIjin.user_id == user.id)
    else:
        stmt = stmt.outerjoin(Role, u.role_id == Role.id).outerjoin(
            Unit, u.unit_id == Unit.id
        )
        if user.role.slug == "kanit":
            cond = and_(Unit.id == user.unit.id, s.level >= 1)
            if user.unit.slug in ["operasional", "tu"]:
                level = 4 if user.unit.slug == "tu" else 3
                cond = or_(cond, s.level >= level)
            conditions.append(cond)
        elif "admin" in user.role.slug:
            pass
        else:
            raise HTTPException(
                status_code=http_status.HTTP_403_FORBIDDEN,
                detail="User does not have enough permissions",
            )
    return stmt, conditions


def get_ijin_details(db, id):
    stmt = (
        select(PengajuanIjin)
        .options(
            joinedload(PengajuanIjin.user),
            joinedload(PengajuanIjin.status),
        )
        .where(PengajuanIjin.id == id)
    )

    return fetch_one(db, stmt)