src/services/closing_service.py

import os
from typing import Dict, List, Tuple
from src.db.connection import get_conn
from src.db.closing import is_closed, save_closing, get_closing
from src.db.audit import recent as recent_audit
from src.utils.time_utils import utc_now_iso, muscat_now, business_day_for_close
from src.reports.daily_closing_pdf import build_daily_closing_pdf

def _compute_closing_payload(conn, business_date: str, closed_by: str) -> dict:
    # Occupancy counts (current snapshot)
    rooms = conn.execute("SELECT status, COUNT(*) AS c FROM rooms GROUP BY status").fetchall()
    occupancy = {r["status"]: int(r["c"]) for r in rooms}

    # Room revenue summary for business_date:
    # We include:
    # - payments recorded on that business_date (based on changed_at_utc -> we do not have local-date indexing)
    # Practical approach: summarize by reservations that have check_in_date or check_out_date = business_date, plus all payments.
    # For a hotel desk, you mainly want "today's desk movements". We'll do:
    # - total payments where reservation check_in_date <= business_date <= check_out_date (active/closed)
    # - outstanding balances for those reservations.
    res_rows = conn.execute(
        """
        SELECT id, room_no, guest_name, total_ro, status
        FROM reservations
        WHERE check_in_date <= ? AND check_out_date >= ?
        """,
        (business_date, business_date),
    ).fetchall()

    res_ids = [r["id"] for r in res_rows]
    paid_total = 0.0
    if res_ids:
        q = "SELECT COALESCE(SUM(amount_ro),0) AS s FROM payments WHERE reservation_id IN (%s)" % ",".join(["?"] * len(res_ids))
        paid_total = float(conn.execute(q, res_ids).fetchone()["s"])

    total_room_value = sum(float(r["total_ro"]) for r in res_rows) if res_rows else 0.0
    balance = max(0.0, total_room_value - paid_total)

    # Other income / expenses for business_date
    other_income = float(conn.execute(
        "SELECT COALESCE(SUM(amount_ro),0) AS s FROM income_expense WHERE entry_date=? AND kind='income'",
        (business_date,),
    ).fetchone()["s"])
    expenses = float(conn.execute(
        "SELECT COALESCE(SUM(amount_ro),0) AS s FROM income_expense WHERE entry_date=? AND kind='expense'",
        (business_date,),
    ).fetchone()["s"])

    net = (paid_total + other_income) - expenses

    # Active problems list
    probs = conn.execute(
        """
        SELECT room_no, problem_type, description
        FROM room_problems
        WHERE is_active=1
        ORDER BY room_no
        """
    ).fetchall()
    prob_lines = [f"{p['room_no']} - {p['problem_type']}" for p in probs]

    # Audit highlights
    audit = recent_audit(conn, limit=12)
    audit_lines = [
        f"{a['changed_at_utc']} | {a['entity']} {a.get('entity_id','')} | {a['action']} | by {a['changed_by']}"
        for a in audit
    ]

    summary_lines = [
        f"Occupancy snapshot: free={occupancy.get('free',0)}, occupied={occupancy.get('occupied',0)}, need_cleaning={occupancy.get('need_cleaning',0)}, out_of_service={occupancy.get('out_of_service',0)}",
        f"Room revenue (payments): {paid_total:.3f} RO",
        f"Outstanding balances (approx.): {balance:.3f} RO",
        f"Other income: {other_income:.3f} RO",
        f"Expenses: {expenses:.3f} RO",
        f"Net: {net:.3f} RO",
    ]

    return {
        "business_date": business_date,
        "closed_by": closed_by,
        "closed_at_utc": utc_now_iso(),
        "summary_lines": summary_lines,
        "active_problems": prob_lines,
        "audit_lines": audit_lines,
    }

def close_day(db_path: str, business_date: str, closed_by: str, note: str) -> Tuple[bytes, str]:
    """
    Returns (pdf_bytes, saved_path)
    """
    conn = get_conn(db_path)
    try:
        if is_closed(conn, business_date):
            existing = get_closing(conn, business_date)
            if existing and existing.get("pdf_bytes"):
                return bytes(existing["pdf_bytes"]), existing.get("pdf_path") or ""
            raise ValueError("Day already closed, but PDF not found.")

        payload = _compute_closing_payload(conn, business_date, closed_by)
        pdf_bytes = build_daily_closing_pdf(payload)

        # Save path
        base_dir = "/data/reports" if db_path.startswith("/data/") else "./data/reports"
        os.makedirs(base_dir, exist_ok=True)
        pdf_path = os.path.join(base_dir, f"closing_{business_date}.pdf")
        with open(pdf_path, "wb") as f:
            f.write(pdf_bytes)

        save_closing(conn, business_date, closed_by, note, pdf_path, pdf_bytes)
        return pdf_bytes, pdf_path
    finally:
        conn.close()

def compute_default_business_date_to_close() -> str:
    # after 06:00, close yesterday; we always close yesterday when asked
    return business_day_for_close(muscat_now())