const {pool} = require("../../config/db");

exports.getCommercialParkingAvailability = async () => {
  const query = `
    SELECT 
      pb.id,
      pb.code,
      pb.type,
      pb.capacity,

      COUNT(cv.id) FILTER (
        WHERE cv.parking_id = pb.id 
        AND cv.gate_status = 'Inside' 
        AND cv.is_deleted = FALSE
      ) AS occupied,

      COALESCE(
        JSON_AGG(
          JSON_BUILD_OBJECT(
            'vehicleNumber', cv.vehicle_number,
            'driverName', cv.driver_name,
            'status', cv.gate_status,
            'slot', cv.slot, 
            'checkInTime', cv.time_in
          )
        ) FILTER (WHERE cv.id IS NOT NULL),
        '[]'
      ) AS vehicles

    FROM parking_bays pb
    LEFT JOIN commercial_vehicles cv
      ON cv.parking_id = pb.id

    WHERE pb.is_deleted = FALSE

    GROUP BY pb.id
    ORDER BY pb.id ASC;
  `;

  const result = await pool.query(query);

  return result.rows.map((bay) => {
    const occupied = parseInt(bay.occupied, 10);
    const available = bay.capacity - occupied;

    let status = "AVAILABLE";
    if (occupied === bay.capacity) status = "FULL";
    else if (occupied > 0) status = "PARTIAL";

    return {
      id: bay.id,
      code: bay.code,
      type: bay.type,
      capacity: bay.capacity,
      occupied,
      available,
      status,
      vehicles: bay.vehicles,
    };
  });
};