package com.zioinfo.kintex.catalog;

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;

/**
 * 전시 일정 카탈로그 매퍼 — {@code event} 테이블 read-only 조회(V10 시드 1,098건).
 * <p>hallLabel 은 {@code hall_assignment}(대표 홀 우선) → {@code hall.label} 조인 우선, 없으면 V23 백필
 * {@code event.hall_label}(크롤 배정 홀 문자열)로 폴백. category·estVisitors 는 V23 백필 컬럼을 그대로 반환.
 *
 * <p>status 필터는 DB 원문(active/ended)이 계약 값(ongoing|upcoming|ended)과 다르므로 <b>날짜 기준</b>으로 판정한다
 * — 프론트의 재계산 규칙과 동일: ended=end&lt;오늘, ongoing=시작≤오늘≤종료, upcoming=시작&gt;오늘.
 *
 * <p>★ PG 소문자 폴딩 회피: Map 반환 카멜케이스 별칭은 반드시 {@code AS "camelCase"} 쌍따옴표.
 */
@Mapper
public interface EventCatalogMapper {

    /** 카탈로그 조회 — year(시작연도)·status(날짜 기준)·category 선택 필터. 시작일 내림차순. */
    @Select("""
            <script>
            SELECT e.id,
                   e.name,
                   to_char(e.start_date, 'YYYY-MM-DD') AS "startDate",
                   to_char(e.end_date,   'YYYY-MM-DD') AS "endDate",
                   e.status,
                   COALESCE(h.label, e.hall_label) AS "hallLabel",
                   e.category            AS "category",
                   e.est_visitors        AS "estVisitors"
            FROM event e
            LEFT JOIN LATERAL (
                SELECT ha.hall_id
                FROM hall_assignment ha
                WHERE ha.event_id = e.id
                ORDER BY ha.is_primary DESC
                LIMIT 1
            ) pa ON true
            LEFT JOIN hall h ON h.id = pa.hall_id
            <where>
              AND e.tenant_id = #{tenantId}
              <if test="year != null and year != ''">
                AND extract(year FROM e.start_date) = CAST(#{year} AS integer)
              </if>
              <if test="category != null and category != ''">
                AND e.category = #{category}
              </if>
              <if test="status == 'ended'">AND e.end_date &lt; CURRENT_DATE</if>
              <if test="status == 'ongoing'">AND e.start_date &lt;= CURRENT_DATE AND e.end_date &gt;= CURRENT_DATE</if>
              <if test="status == 'upcoming'">AND e.start_date &gt; CURRENT_DATE</if>
            </where>
            ORDER BY e.start_date DESC NULLS LAST, e.id DESC
            LIMIT #{limit} OFFSET #{offset}
            </script>
            """)
    List<Map<String, Object>> findEvents(@Param("tenantId") String tenantId,
                                         @Param("year") String year,
                                         @Param("status") String status,
                                         @Param("category") String category,
                                         @Param("limit") int limit,
                                         @Param("offset") int offset);

    /**
     * 홀 가동률·캘린더 점유 공용 — 지정 월과 겹치는 행사(시작·종료·홀 라벨)를 반환한다.
     * hallLabel 은 hall_assignment(대표 홀) 우선, 없으면 V23 {@code event.hall_label} 폴백.
     * 집계(홀 그룹 버킷·일자 확장)는 서비스에서 수행한다.
     */
    @Select("""
            SELECT to_char(e.start_date, 'YYYY-MM-DD') AS "startDate",
                   to_char(e.end_date,   'YYYY-MM-DD') AS "endDate",
                   COALESCE(h.label, e.hall_label)      AS "hallLabel"
            FROM event e
            LEFT JOIN LATERAL (
                SELECT ha.hall_id
                FROM hall_assignment ha
                WHERE ha.event_id = e.id
                ORDER BY ha.is_primary DESC
                LIMIT 1
            ) pa ON true
            LEFT JOIN hall h ON h.id = pa.hall_id
            WHERE e.tenant_id = #{tenantId}
              AND e.start_date IS NOT NULL AND e.end_date IS NOT NULL
              AND e.start_date <= CAST(#{monthEnd} AS date)
              AND e.end_date   >= CAST(#{monthStart} AS date)
            """)
    List<Map<String, Object>> findEventsOverlappingMonth(@Param("tenantId") String tenantId,
                                                         @Param("monthStart") String monthStart,
                                                         @Param("monthEnd") String monthEnd);

    /** 필터 조건 총 건수(페이징 메타). */
    @Select("""
            <script>
            SELECT count(*) FROM event e
            <where>
              AND e.tenant_id = #{tenantId}
              <if test="year != null and year != ''">
                AND extract(year FROM e.start_date) = CAST(#{year} AS integer)
              </if>
              <if test="status == 'ended'">AND e.end_date &lt; CURRENT_DATE</if>
              <if test="status == 'ongoing'">AND e.start_date &lt;= CURRENT_DATE AND e.end_date &gt;= CURRENT_DATE</if>
              <if test="status == 'upcoming'">AND e.start_date &gt; CURRENT_DATE</if>
            </where>
            </script>
            """)
    long countEvents(@Param("tenantId") String tenantId,
                     @Param("year") String year, @Param("status") String status);
}
