source: uc_reserve.py

import datetime

from db import get_connection, get_transaction, pick_from_list, print_table, input_date, input_time


def make_reservation(user_id):
    """UC0002: Make a Resource Reservation."""
    print("\n=== Make a Resource Reservation ===\n")

    # Step 1: Select resource
    resources = _fetch_all_resources()
    if not resources:
        print("  No resources available.")
        return

    items = []
    for r in resources:
        loc = f"{r['building']} / {r['room']}" if r["building"] else "Digital"
        items.append(f"{r['name']} [{r['type_name']}] - {loc}")
    idx = pick_from_list("  Select a resource to reserve: ", items)
    if idx is None:
        return

    resource = resources[idx]
    rid = resource["resource_id"]

    # Step 2: Show resource details and upcoming reservations
    print(f"\n  === {resource['name']} ===")
    print(f"  Available: {resource['available_from'].strftime('%H:%M')} - {resource['available_to'].strftime('%H:%M')}")
    print(f"  Weekends:  {'Yes' if resource['available_weekends'] else 'No'}")

    _show_upcoming(rid)

    # Step 3: Enter reservation details
    while True:
        print()
        date = input_date("  Reservation date (YYYY-MM-DD): ")
        start = input_time("  Start time (HH:MM): ")
        end = input_time("  End time (HH:MM): ")

        if end <= start:
            print("  End time must be after start time.")
            continue

        purpose = input("  Purpose: ").strip()
        if not purpose:
            print("  Purpose is required.")
            continue
        if len(purpose) > 512:
            purpose = purpose[:512]

        # Step 4: Validate availability window
        day_of_week = date.isoweekday()
        if day_of_week > 5 and not resource["available_weekends"]:
            print(f"\n  ERROR: {resource['name']} is not available on weekends.")
            if not _retry():
                return
            continue

        if start < resource["available_from"] or end > resource["available_to"]:
            print(
                f"\n  ERROR: {resource['name']} is only available "
                f"{resource['available_from'].strftime('%H:%M')} - {resource['available_to'].strftime('%H:%M')}."
            )
            if not _retry():
                return
            continue

        # Step 5: Check conflicts
        start_dt = datetime.datetime.combine(date, start)
        end_dt = datetime.datetime.combine(date, end)
        conflicts = _check_conflicts(rid, start_dt, end_dt)

        if conflicts:
            print(f"\n  CONFLICT: {len(conflicts)} overlapping reservation(s) found:")
            print_table(
                ["ID", "Start", "End", "Purpose", "Reserved by"],
                [
                    (c[0], c[1].strftime("%Y-%m-%d %H:%M"), c[2].strftime("%Y-%m-%d %H:%M"), c[3][:30], c[4])
                    for c in conflicts
                ],
            )
            if not _retry():
                return
            continue

        # Step 6: Confirm
        print(f"\n  --- Reservation Summary ---")
        print(f"  Resource: {resource['name']}")
        print(f"  Date:     {date}")
        print(f"  Time:     {start.strftime('%H:%M')} - {end.strftime('%H:%M')}")
        print(f"  Purpose:  {purpose}")
        confirm = input("\n  Confirm reservation? (y/n): ").strip().lower()
        if confirm not in ("y", "yes"):
            print("  Reservation cancelled.")
            return

        # Step 7: INSERT (transaction: re-check conflicts + insert atomically)
        try:
            with get_transaction() as conn:
                with conn.cursor() as cur:
                    # Re-check conflicts inside the transaction to prevent
                    # race conditions (another user inserting between our
                    # check and insert)
                    cur.execute(
                        """
                        SELECT COUNT(*) FROM reservations
                        WHERE resource_id = %s
                          AND status IN ('approved', 'pending')
                          AND start_time < %s AND end_time > %s
                        """,
                        (rid, end_dt, start_dt),
                    )
                    if cur.fetchone()[0] > 0:
                        raise RuntimeError("Conflict: another reservation was just created for this time slot")

                    cur.execute(
                        """
                        INSERT INTO reservations
                            (start_time, end_time, status, purpose, created_at, user_id, resource_id)
                        VALUES (%s, %s, 'pending', %s, CURRENT_TIMESTAMP, %s, %s)
                        RETURNING reservation_id, status, created_at
                        """,
                        (start_dt, end_dt, purpose, user_id, rid),
                    )
                    result = cur.fetchone()
            # Transaction committed automatically
            print(f"\n  Reservation created successfully!")
            print(f"  Reservation ID: {result[0]}")
            print(f"  Status: {result[1]} (awaiting administrator approval)")
            print(f"  Created at: {result[2].strftime('%Y-%m-%d %H:%M')}")
            return
        except Exception as e:
            # Transaction rolled back automatically
            print(f"\n  Error creating reservation: {e}")
            return


def _fetch_all_resources():
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT r.resource_id, r.name, r.description,
                       r.available_from, r.available_to, r.available_weekends,
                       rt.type_name, l.building, l.room
                FROM resources r
                JOIN resource_types rt ON r.type_id = rt.type_id
                LEFT JOIN locations l ON r.location_id = l.location_id
                ORDER BY rt.type_name, r.name
            """)
            cols = [desc[0] for desc in cur.description]
            return [dict(zip(cols, row)) for row in cur.fetchall()]


def _show_upcoming(resource_id):
    today = datetime.date.today()
    week_end = today + datetime.timedelta(days=7)
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT res.start_time, res.end_time, res.status, res.purpose,
                       u.first_name || ' ' || u.last_name AS reserved_by
                FROM reservations res
                JOIN users u ON res.user_id = u.user_id
                WHERE res.resource_id = %s
                  AND res.status IN ('approved', 'pending')
                  AND res.start_time >= %s AND res.start_time < %s
                ORDER BY res.start_time
                """,
                (resource_id, today, week_end),
            )
            rows = cur.fetchall()
    if rows:
        print(f"\n  Reservations in the next 7 days:")
        print_table(
            ["Start", "End", "Status", "Purpose", "By"],
            [
                (r[0].strftime("%Y-%m-%d %H:%M"), r[1].strftime("%Y-%m-%d %H:%M"), r[2], r[3][:30], r[4])
                for r in rows
            ],
        )
    else:
        print("\n  No reservations in the next 7 days.")


def _check_conflicts(resource_id, start_dt, end_dt):
    with get_connection() as conn:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT res.reservation_id, res.start_time, res.end_time,
                       res.purpose,
                       u.first_name || ' ' || u.last_name AS reserved_by
                FROM reservations res
                JOIN users u ON res.user_id = u.user_id
                WHERE res.resource_id = %s
                  AND res.status IN ('approved', 'pending')
                  AND res.start_time < %s
                  AND res.end_time > %s
                """,
                (resource_id, end_dt, start_dt),
            )
            return cur.fetchall()


def _retry():
    choice = input("  Try a different time? (y/n): ").strip().lower()
    return choice in ("y", "yes")