package com.zioinfo.kintex.ops;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 홀 현장 운영 매퍼 — read-only 집계. 마이그레이션 불변(기존 테이블 집계만).
 *
 * <p>실집계원:
 * <ul>
 *   <li>배정 홀 + 판매면적(hall_assignment·hall·layout·booth ST_Area) — 혼잡 분배 가중치</li>
 *   <li>전력 부하(utility_order.quote.requestedKw) — 홀 단위 신청 용량 합</li>
 *   <li>체크인(visitor_registration.checkin_at) — 현장 인원·시간대 추이</li>
 * </ul>
 * HVAC/조명/주차 센서 원천 없음 → 조회하지 않는다.
 */
@Mapper
public interface OpsMapper {

    /**
     * 행사 배정 홀 목록 + 판매면적(salesArea, ㎡) + 신청 전력(powerKw, kW).
     * powerKw 는 유틸리티 신청이 없으면 null. 대표 홀 우선 정렬.
     */
    @Select("""
            SELECT h.id AS "hallId", h.label AS "label",
                   COALESCE(ar.sales_area, 0) AS "salesArea",
                   pw.req_kw AS "powerKw"
            FROM hall_assignment ha
            JOIN hall h ON h.id = ha.hall_id
            LEFT JOIN (
                SELECT l.hall_id AS hall_id, sum(ST_Area(b.geom)) AS sales_area
                FROM layout l JOIN booth b ON b.layout_id = l.id
                WHERE l.event_id = #{eventId}
                GROUP BY l.hall_id
            ) ar ON ar.hall_id = h.id
            LEFT JOIN (
                SELECT l.hall_id AS hall_id, sum((u.quote->>'requestedKw')::numeric) AS req_kw
                FROM utility_order u
                JOIN booth b ON b.id = u.booth_id
                JOIN layout l ON l.id = b.layout_id
                WHERE u.event_id = #{eventId}
                  AND u.quote->>'requestedKw' ~ '^[0-9]+(\\.[0-9]+)?$'
                GROUP BY l.hall_id
            ) pw ON pw.hall_id = h.id
            WHERE ha.event_id = #{eventId}
            ORDER BY ha.is_primary DESC, h.id
            """)
    List<Map<String, Object>> findHallOps(@Param("eventId") String eventId);

    /** 현장 체크인 완료 인원(checkin_at 기록) — 추정 현장 인원 근거. */
    @Select("""
            SELECT count(*)
            FROM visitor_registration
            WHERE event_id = #{eventId} AND checkin_at IS NOT NULL
            """)
    long countCheckedIn(@Param("eventId") String eventId);

    /** 시간대(0~23시)별 체크인 집계 — 혼잡 추이(hour = 'HH:00'). */
    @Select("""
            SELECT lpad(extract(hour FROM checkin_at)::int::text, 2, '0') || ':00' AS "hour",
                   count(*) AS "count"
            FROM visitor_registration
            WHERE event_id = #{eventId} AND checkin_at IS NOT NULL
            GROUP BY extract(hour FROM checkin_at)
            ORDER BY extract(hour FROM checkin_at)
            """)
    List<Map<String, Object>> findCheckinSeries(@Param("eventId") String eventId);
}
