"""General Affairs repository — operators, jadwal, absensi DB queries."""

from datetime import date, datetime, timezone

from sqlalchemy import delete, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.general_affairs import Absensi, Housekeeping, HousekeepingFoto, HousekeepingItem, JadwalShift, Sapras, SaprasItem
from app.models.role import Role, UserSpbuAssignment
from app.models.user import User


# ── Operators ────────────────────────────────────────────────────────────────

async def get_operators(db: AsyncSession, spbu_id: int) -> list[dict]:
    """Return users assigned to spbu_id whose role has can_be_scheduled=True."""
    result = await db.execute(
        select(UserSpbuAssignment, User, Role)
        .join(User, UserSpbuAssignment.user_id == User.id)
        .join(Role, UserSpbuAssignment.role_id == Role.id)
        .where(
            UserSpbuAssignment.spbu_id == spbu_id,
            Role.can_be_scheduled.is_(True),
            User.deleted_at.is_(None),
        )
    )
    rows = result.all()
    return [
        {
            "id": user.id,
            "nama": user.name,
            "posisi": role.nama,
            "is_active": user.is_active,
        }
        for _, user, role in rows
    ]


# ── Jadwal ───────────────────────────────────────────────────────────────────

async def get_jadwal(
    db: AsyncSession, spbu_id: int, start: date, end: date
) -> list[JadwalShift]:
    result = await db.execute(
        select(JadwalShift)
        .options(selectinload(JadwalShift.user), selectinload(JadwalShift.shift))
        .where(
            JadwalShift.spbu_id == spbu_id,
            JadwalShift.tanggal >= start,
            JadwalShift.tanggal <= end,
        )
        .order_by(JadwalShift.tanggal, JadwalShift.user_id)
    )
    return list(result.scalars().all())


async def create_jadwal_bulk(
    db: AsyncSession,
    spbu_id: int,
    items: list[dict],
    created_by_id: int,
) -> list[JadwalShift]:
    """Insert multiple jadwal rows, skip if already exists."""
    created = []
    for item in items:
        existing = await db.execute(
            select(JadwalShift).where(
                JadwalShift.spbu_id == spbu_id,
                JadwalShift.user_id == item["user_id"],
                JadwalShift.shift_id == item["shift_id"],
                JadwalShift.tanggal == item["tanggal"],
            )
        )
        if existing.scalar_one_or_none():
            continue
        j = JadwalShift(
            spbu_id=spbu_id,
            user_id=item["user_id"],
            shift_id=item["shift_id"],
            tanggal=item["tanggal"],
            created_by_id=created_by_id,
        )
        db.add(j)
        created.append(j)
    await db.flush()
    return created


async def delete_jadwal(db: AsyncSession, jadwal_id: int, spbu_id: int) -> bool:
    result = await db.execute(
        select(JadwalShift).where(
            JadwalShift.id == jadwal_id,
            JadwalShift.spbu_id == spbu_id,
        )
    )
    j = result.scalar_one_or_none()
    if not j:
        return False
    await db.delete(j)
    await db.flush()
    return True


# ── Absensi ──────────────────────────────────────────────────────────────────

async def get_absensi(
    db: AsyncSession, spbu_id: int, start: date, end: date
) -> list[Absensi]:
    result = await db.execute(
        select(Absensi)
        .options(
            selectinload(Absensi.shift),
            selectinload(Absensi.uploaded_by),
            selectinload(Absensi.reviewed_by),
        )
        .where(
            Absensi.spbu_id == spbu_id,
            Absensi.tanggal >= start,
            Absensi.tanggal <= end,
        )
        .order_by(Absensi.tanggal.desc(), Absensi.shift_id)
    )
    return list(result.scalars().all())


async def get_absensi_by_id(db: AsyncSession, absensi_id: int) -> Absensi | None:
    result = await db.execute(
        select(Absensi)
        .options(
            selectinload(Absensi.shift),
            selectinload(Absensi.uploaded_by),
            selectinload(Absensi.reviewed_by),
        )
        .where(Absensi.id == absensi_id)
    )
    return result.scalar_one_or_none()


async def get_jadwal_for_slot(
    db: AsyncSession, spbu_id: int, shift_id: int, tanggal: date
) -> list[dict]:
    """Return operators scheduled for a specific shift+date."""
    result = await db.execute(
        select(JadwalShift)
        .options(selectinload(JadwalShift.user))
        .where(
            JadwalShift.spbu_id == spbu_id,
            JadwalShift.shift_id == shift_id,
            JadwalShift.tanggal == tanggal,
            JadwalShift.deleted_at.is_(None),
        )
    )
    rows = result.scalars().all()
    return [{"id": r.user.id, "nama": r.user.name} for r in rows if r.user]


async def upsert_absensi(
    db: AsyncSession,
    spbu_id: int,
    shift_id: int,
    tanggal: date,
    foto_url: str,
    uploaded_by_id: int,
    foto_eksif_waktu: str | None = None,
) -> Absensi:
    """Create or replace absensi record for a shift+date. Resets status to pending."""
    result = await db.execute(
        select(Absensi).where(
            Absensi.spbu_id == spbu_id,
            Absensi.shift_id == shift_id,
            Absensi.tanggal == tanggal,
        )
    )
    absensi = result.scalar_one_or_none()
    now = datetime.now(timezone.utc)
    if absensi:
        absensi.foto_url = foto_url
        absensi.foto_eksif_waktu = foto_eksif_waktu
        absensi.uploaded_by_id = uploaded_by_id
        absensi.uploaded_at = now
        absensi.status = "pending"
        absensi.reviewed_by_id = None
        absensi.reviewed_at = None
    else:
        absensi = Absensi(
            spbu_id=spbu_id,
            shift_id=shift_id,
            tanggal=tanggal,
            foto_url=foto_url,
            foto_eksif_waktu=foto_eksif_waktu,
            uploaded_by_id=uploaded_by_id,
            uploaded_at=now,
            status="pending",
        )
        db.add(absensi)
    await db.flush()
    await db.refresh(absensi)
    return absensi


async def approve_absensi(
    db: AsyncSession, spbu_id: int, absensi_id: int, reviewed_by_id: int
) -> Absensi | None:
    absensi = await get_absensi_by_id(db, absensi_id)
    if not absensi or absensi.spbu_id != spbu_id:
        return None
    absensi.status = "approved"
    absensi.reviewed_by_id = reviewed_by_id
    absensi.reviewed_at = datetime.now(timezone.utc)
    await db.flush()
    await db.refresh(absensi)
    return absensi


# ── Housekeeping ──────────────────────────────────────────────────────────────

async def get_housekeeping(
    db: AsyncSession, spbu_id: int, start: date, end: date
) -> list[Housekeeping]:
    result = await db.execute(
        select(Housekeeping)
        .options(
            selectinload(Housekeeping.uploaded_by),
            selectinload(Housekeeping.reviewed_by),
            selectinload(Housekeeping.items),
            selectinload(Housekeeping.fotos),
        )
        .where(
            Housekeeping.spbu_id == spbu_id,
            Housekeeping.tanggal >= start,
            Housekeeping.tanggal <= end,
        )
        .order_by(Housekeeping.tanggal.desc())
    )
    return list(result.scalars().all())


async def get_housekeeping_by_id(db: AsyncSession, hk_id: int) -> Housekeeping | None:
    result = await db.execute(
        select(Housekeeping)
        .options(
            selectinload(Housekeeping.uploaded_by),
            selectinload(Housekeeping.reviewed_by),
            selectinload(Housekeeping.items),
            selectinload(Housekeeping.fotos),
        )
        .where(Housekeeping.id == hk_id)
    )
    return result.scalar_one_or_none()


async def get_housekeeping_slot(
    db: AsyncSession, spbu_id: int, tanggal: date
) -> Housekeeping | None:
    result = await db.execute(
        select(Housekeeping)
        .options(
            selectinload(Housekeeping.uploaded_by),
            selectinload(Housekeeping.reviewed_by),
            selectinload(Housekeeping.items),
            selectinload(Housekeeping.fotos),
        )
        .where(
            Housekeeping.spbu_id == spbu_id,
            Housekeeping.tanggal == tanggal,
        )
    )
    return result.scalar_one_or_none()


async def upsert_housekeeping(
    db: AsyncSession,
    spbu_id: int,
    tanggal: date,
    uploaded_by_id: int,
    items: list[str],          # list of deskripsi strings
    foto_urls: list[str],
    submitted: bool = False,
) -> Housekeeping:
    """Create or replace housekeeping for a date. Items and photos are fully replaced."""
    existing = await get_housekeeping_slot(db, spbu_id, tanggal)
    now = datetime.now(timezone.utc)
    new_status = "approved" if submitted else "pending"

    if existing:
        hk = existing
        hk.uploaded_by_id = uploaded_by_id
        hk.uploaded_at = now
        hk.status = new_status
        hk.reviewed_by_id = None
        hk.reviewed_at = None
        for item in list(hk.items):
            await db.delete(item)
        for foto in list(hk.fotos):
            await db.delete(foto)
        await db.flush()
    else:
        hk = Housekeeping(
            spbu_id=spbu_id,
            tanggal=tanggal,
            status=new_status,
            uploaded_by_id=uploaded_by_id,
            uploaded_at=now,
        )
        db.add(hk)
        await db.flush()

    for i, deskripsi in enumerate(items):
        db.add(HousekeepingItem(housekeeping_id=hk.id, deskripsi=deskripsi, urutan=i))
    for i, url in enumerate(foto_urls):
        db.add(HousekeepingFoto(housekeeping_id=hk.id, tipe="foto", foto_url=url, urutan=i))

    await db.flush()
    return await get_housekeeping_by_id(db, hk.id)


async def back_to_draft_housekeeping(
    db: AsyncSession, spbu_id: int, hk_id: int
) -> Housekeeping | None:
    hk = await get_housekeeping_by_id(db, hk_id)
    if not hk or hk.spbu_id != spbu_id:
        return None
    hk.status = "pending"
    await db.flush()
    return await get_housekeeping_by_id(db, hk_id)


async def hard_delete_absensi(db: AsyncSession, absensi_id: int) -> None:
    await db.execute(delete(Absensi).where(Absensi.id == absensi_id))
    await db.flush()


async def hard_delete_housekeeping(db: AsyncSession, hk_id: int) -> None:
    await db.execute(delete(Housekeeping).where(Housekeeping.id == hk_id))
    await db.flush()


async def approve_housekeeping(
    db: AsyncSession, spbu_id: int, hk_id: int, reviewed_by_id: int
) -> Housekeeping | None:
    hk = await get_housekeeping_by_id(db, hk_id)
    if not hk or hk.spbu_id != spbu_id:
        return None
    hk.status = "approved"
    hk.reviewed_by_id = reviewed_by_id
    hk.reviewed_at = datetime.now(timezone.utc)
    await db.flush()
    return await get_housekeeping_by_id(db, hk_id)


# ── Sapras ────────────────────────────────────────────────────────────────────

async def get_sapras(
    db: AsyncSession, spbu_id: int, start: date, end: date
) -> list[Sapras]:
    result = await db.execute(
        select(Sapras)
        .options(
            selectinload(Sapras.uploaded_by),
            selectinload(Sapras.reviewed_by),
            selectinload(Sapras.items),
        )
        .where(
            Sapras.spbu_id == spbu_id,
            Sapras.tanggal >= start,
            Sapras.tanggal <= end,
        )
        .order_by(Sapras.tanggal.desc())
    )
    return list(result.scalars().all())


async def get_sapras_by_id(db: AsyncSession, sapras_id: int) -> Sapras | None:
    result = await db.execute(
        select(Sapras)
        .options(
            selectinload(Sapras.uploaded_by),
            selectinload(Sapras.reviewed_by),
            selectinload(Sapras.items),
        )
        .where(Sapras.id == sapras_id)
    )
    return result.scalar_one_or_none()


async def get_sapras_slot(
    db: AsyncSession, spbu_id: int, tanggal: date
) -> Sapras | None:
    result = await db.execute(
        select(Sapras)
        .options(
            selectinload(Sapras.uploaded_by),
            selectinload(Sapras.reviewed_by),
            selectinload(Sapras.items),
        )
        .where(
            Sapras.spbu_id == spbu_id,
            Sapras.tanggal == tanggal,
        )
    )
    return result.scalar_one_or_none()


async def upsert_sapras(
    db: AsyncSession,
    spbu_id: int,
    tanggal: date,
    uploaded_by_id: int,
    catatan: str | None,
    items_data: list[dict],  # [{kegiatan, foto_sebelum_url, foto_sesudah_url}]
    submitted: bool = False,
) -> Sapras:
    """Create or replace sapras for a date. Items are fully replaced."""
    existing = await get_sapras_slot(db, spbu_id, tanggal)
    now = datetime.now(timezone.utc)
    new_status = "approved" if submitted else "pending"

    if existing:
        sapras = existing
        sapras.catatan = catatan
        sapras.uploaded_by_id = uploaded_by_id
        sapras.uploaded_at = now
        sapras.status = new_status
        sapras.reviewed_by_id = None
        sapras.reviewed_at = None
        for item in list(sapras.items):
            await db.delete(item)
        await db.flush()
    else:
        sapras = Sapras(
            spbu_id=spbu_id,
            tanggal=tanggal,
            catatan=catatan,
            status=new_status,
            uploaded_by_id=uploaded_by_id,
            uploaded_at=now,
        )
        db.add(sapras)
        await db.flush()

    for i, item in enumerate(items_data):
        db.add(SaprasItem(
            sapras_id=sapras.id,
            kegiatan=item.get("kegiatan", ""),
            foto_sebelum_url=item.get("foto_sebelum_url"),
            foto_sesudah_url=item.get("foto_sesudah_url"),
            urutan=i,
        ))

    await db.flush()
    return await get_sapras_by_id(db, sapras.id)


async def back_to_draft_sapras(
    db: AsyncSession, spbu_id: int, sapras_id: int
) -> Sapras | None:
    sapras = await get_sapras_by_id(db, sapras_id)
    if not sapras or sapras.spbu_id != spbu_id:
        return None
    sapras.status = "pending"
    await db.flush()
    return await get_sapras_by_id(db, sapras_id)


async def hard_delete_sapras(db: AsyncSession, sapras_id: int) -> None:
    await db.execute(delete(Sapras).where(Sapras.id == sapras_id))
    await db.flush()