<?php
require_once __DIR__ . '/../config/db.php';

class LaundryBag
{
  public static function getByUnit(int $unitId): ?array
  {
    $stmt = db()->prepare("SELECT * FROM laundry_bags WHERE unit_id = ? LIMIT 1");
    $stmt->execute([$unitId]);
    $row = $stmt->fetch();
    return $row ?: null;
  }

  public static function getWithUnit(int $bagId): ?array
  {
    $stmt = db()->prepare("
      SELECT b.*,
             un.unit_code,
             un.id AS unit_id,
             bl.name AS building_name
      FROM laundry_bags b
      JOIN units un ON un.id = b.unit_id
      JOIN buildings bl ON bl.id = un.building_id
      WHERE b.id = ?
      LIMIT 1
    ");
    $stmt->execute([$bagId]);
    $row = $stmt->fetch();
    return $row ?: null;
  }

  public static function createOrUpdateBag(int $unitId, string $bagNumber): int
  {
    $existing = self::getByUnit($unitId);

    if ($existing) {
      $stmt = db()->prepare("UPDATE laundry_bags SET bag_number = ?, updated_at = NOW() WHERE id = ?");
      $stmt->execute([$bagNumber, (int)$existing['id']]);
      return (int)$existing['id'];
    }

    $stmt = db()->prepare("INSERT INTO laundry_bags (unit_id, bag_number, updated_at) VALUES (?, ?, NOW())");
    $stmt->execute([$unitId, $bagNumber]);
    return (int)db()->lastInsertId();
  }

  public static function items(int $bagId): array
  {
    // Order by laundry_item_types.sort_order when available, else fallback by code
    $stmt = db()->prepare("
      SELECT bi.*
      FROM laundry_bag_items bi
      LEFT JOIN laundry_item_types it ON it.code = bi.item_type
      WHERE bi.bag_id = ?
      ORDER BY COALESCE(it.sort_order, 9999) ASC, bi.item_type ASC
    ");
    $stmt->execute([$bagId]);
    return $stmt->fetchAll();
  }

  public static function upsertItem(int $bagId, string $itemType, string $status): void
  {
    $stmt = db()->prepare("
      INSERT INTO laundry_bag_items (bag_id, item_type, status, updated_at)
      VALUES (?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE status = VALUES(status), updated_at = NOW()
    ");
    $stmt->execute([$bagId, $itemType, $status]);
  }

  public static function listBuildingsWithBags(): array
  {
    $stmt = db()->query("
      SELECT DISTINCT bl.name AS building_name
      FROM laundry_bags b
      JOIN units un ON un.id = b.unit_id
      JOIN buildings bl ON bl.id = un.building_id
      ORDER BY bl.name ASC
    ");
    return $stmt->fetchAll();
  }

  /**
   * Laundry dashboard list with filters + summary.
   * Filters:
   * - q: search unit_code or bag_number
   * - building: exact building name
   * - status: picked_up/delivered/clean/dirty (bags that have ANY item with that status)
   */
  public static function listDashboard(array $filters = [], int $limit = 500): array
  {
    $limit = max(1, min((int)$limit, 2000));

    $where = [];
    $params = [];

    $q = trim((string)($filters['q'] ?? ''));
    if ($q !== '') {
      $where[] = "(un.unit_code LIKE ? OR b.bag_number LIKE ?)";
      $params[] = '%' . $q . '%';
      $params[] = '%' . $q . '%';
    }

    $building = trim((string)($filters['building'] ?? ''));
    if ($building !== '') {
      $where[] = "bl.name = ?";
      $params[] = $building;
    }

    $status = trim((string)($filters['status'] ?? ''));
    $allowedStatus = ['picked_up','delivered','clean','dirty'];
    if ($status !== '' && in_array($status, $allowedStatus, true)) {
      $where[] = "EXISTS (
        SELECT 1 FROM laundry_bag_items i
        WHERE i.bag_id = b.id AND i.status = ?
      )";
      $params[] = $status;
    }

    $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

    $sql = "
      SELECT
        b.*,
        un.unit_code,
        bl.name AS building_name,
        (
          SELECT GROUP_CONCAT(CONCAT(
            COALESCE(it.name, i.item_type),
            ': ', REPLACE(i.status,'_',' ')
          ) ORDER BY COALESCE(it.sort_order, 9999) ASC, i.item_type ASC SEPARATOR ' | ')
          FROM laundry_bag_items i
          LEFT JOIN laundry_item_types it ON it.code = i.item_type
          WHERE i.bag_id = b.id
        ) AS items_summary
      FROM laundry_bags b
      JOIN units un ON un.id = b.unit_id
      JOIN buildings bl ON bl.id = un.building_id
      {$whereSql}
      ORDER BY b.updated_at DESC, b.id DESC
      LIMIT {$limit}
    ";

    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
  }
}
