package com.zioinfo.kintex.publicsite;

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;

/**
 * 공개 홍보 사이트(M12) 매퍼 — 기존 event·hall·layout·booth 는 read-only 조회, 문의만 write.
 * <p>★ Map 반환 @Select 는 camelCase 별칭에 반드시 쌍따옴표({@code AS "x"})를 사용한다(PG lower-fold 방지).
 * <p>공개 응답 정책: 내부 booth id·폴리곤·비용·PII 컬럼은 select 하지 않는다.
 */
@Mapper
public interface PublicSiteMapper {

    // ── 공개 카탈로그(event read-only) ──────────────────────────────────────
    /** 공개 행사 카탈로그 — year(시작연도)·q(이름 부분검색) 선택 필터. 시작일 내림차순. */
    @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,
                   h.label AS "hallLabel",
                   e.poster_url AS "posterUrl",
                   (SELECT count(*)::int
                      FROM booth b
                      JOIN layout l ON l.id = b.layout_id
                     WHERE l.event_id = e.id) AS "boothCount"
            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>
              <if test="year != null and year != ''">
                AND extract(year FROM e.start_date) = CAST(#{year} AS integer)
              </if>
              <if test="q != null and q != ''">
                AND e.name ILIKE ('%' || #{q} || '%')
              </if>
            </where>
            ORDER BY e.start_date DESC NULLS LAST, e.id DESC
            LIMIT #{limit} OFFSET #{offset}
            </script>
            """)
    List<Map<String, Object>> findEvents(@Param("year") String year,
                                         @Param("q") String q,
                                         @Param("limit") int limit,
                                         @Param("offset") int offset);

    /** 공개 행사 상세(단건) — 공개 필드만. */
    @Select("""
            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,
                   h.label AS "hallLabel",
                   e.poster_url AS "posterUrl",
                   (SELECT count(*)::int
                      FROM booth b
                      JOIN layout l ON l.id = b.layout_id
                     WHERE l.event_id = e.id) AS "boothCount"
            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.id = #{eventId}
            """)
    Map<String, Object> findEvent(@Param("eventId") String eventId);

    // ── 공개 플로어플랜(hall·booth read-only) ───────────────────────────────
    /** 행사 배정 홀 목록 — 공개 라벨·전시장만. */
    @Select("""
            SELECT h.id                AS "hallId",
                   h.label             AS "label",
                   h.exhibition_center AS "center",
                   ha.is_primary       AS "primary"
            FROM hall_assignment ha
            JOIN hall h ON h.id = ha.hall_id
            WHERE ha.event_id = #{eventId}
            ORDER BY ha.is_primary DESC, h.id
            """)
    List<Map<String, Object>> findEventHalls(@Param("eventId") String eventId);

    /**
     * 공개 부스 요약 — 홀별 최신 버전 배치안의 부스만. 폴리곤은 중심점(cx/cy)으로 단순화.
     * status 는 업체 배정 여부로 파생(reserved/available). 내부 booth id 미노출.
     */
    @Select("""
            SELECT b.booth_no                                       AS "boothNo",
                   b.assigned_company_name                          AS "companyName",
                   b.booth_type                                     AS "boothType",
                   CASE WHEN b.assigned_company_name IS NOT NULL
                             AND b.assigned_company_name <> ''
                        THEN 'reserved' ELSE 'available' END         AS "status",
                   round(ST_X(ST_Centroid(b.geom))::numeric, 2)     AS "cx",
                   round(ST_Y(ST_Centroid(b.geom))::numeric, 2)     AS "cy",
                   h.label                                          AS "hallLabel"
            FROM booth b
            JOIN layout l ON l.id = b.layout_id
            JOIN hall   h ON h.id = l.hall_id
            WHERE l.event_id = #{eventId}
              AND l.version = (SELECT max(l2.version) FROM layout l2
                                WHERE l2.event_id = l.event_id AND l2.hall_id = l.hall_id)
            ORDER BY b.booth_no NULLS LAST
            LIMIT #{limit}
            """)
    List<Map<String, Object>> findPublicBooths(@Param("eventId") String eventId,
                                               @Param("limit") int limit);

    // ── 문의 접수(write) + rate ─────────────────────────────────────────────
    /** 동일 이메일의 최근 window(초) 내 접수 건수 — 단순 rate 제어(1분 1건). */
    @Select("""
            SELECT count(*)::int FROM exhibit_inquiry
            WHERE email = #{email}
              AND created_at > now() - make_interval(secs => #{windowSeconds})
            """)
    int countRecentByEmail(@Param("email") String email, @Param("windowSeconds") int windowSeconds);

    /** 문의 저장(공개 접수). */
    @org.apache.ibatis.annotations.Insert("""
            INSERT INTO exhibit_inquiry
                (id, receipt_no, inquiry_type, company, contact_name, email, phone,
                 scale, hall_pref, message, agree_privacy, agree_marketing, status)
            VALUES
                (#{id}, #{receiptNo}, #{inquiryType}, #{company}, #{contactName}, #{email}, #{phone},
                 #{scale}, #{hallPref}, #{message}, #{agreePrivacy}, #{agreeMarketing}, 'received')
            """)
    void insertInquiry(@Param("id") String id,
                       @Param("receiptNo") String receiptNo,
                       @Param("inquiryType") String inquiryType,
                       @Param("company") String company,
                       @Param("contactName") String contactName,
                       @Param("email") String email,
                       @Param("phone") String phone,
                       @Param("scale") String scale,
                       @Param("hallPref") String hallPref,
                       @Param("message") String message,
                       @Param("agreePrivacy") boolean agreePrivacy,
                       @Param("agreeMarketing") boolean agreeMarketing);
}
