package com.zioinfo.kintex.visitor;

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

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

/**
 * M10 관람객·리드 매퍼 — {@code visitor_registration}·{@code lead} read/insert.
 * <p>★ PII 불변: 리스트 조회 SQL 은 원문 name/phone/email 을 SELECT 하지 않고 <b>마스킹 표현식</b>만 반환한다.
 * Map 반환 @Select 는 별칭에 쌍따옴표(AS "x") 를 사용해 카멜케이스 키를 보존한다.
 */
@Mapper
public interface VisitorMapper {

    // ── 요약 집계 ──

    /** 등록/체크인/유형 원자 집계(단일 행). */
    @Select("""
            SELECT
              count(*)                                              AS "total",
              count(*) FILTER (WHERE checkin_state = 'done')        AS "checkedIn",
              count(*) FILTER (WHERE checkin_state = 'waiting')     AS "waiting",
              count(*) FILTER (WHERE checkin_state = 'cancelled')   AS "cancelled",
              count(*) FILTER (WHERE visitor_type = 'buyer')        AS "buyers",
              count(*) FILTER (WHERE visitor_type = 'vip')          AS "vips",
              count(*) FILTER (WHERE badge_issued)                  AS "badgeIssued"
            FROM visitor_registration
            WHERE event_id = #{eventId}
            """)
    Map<String, Object> findSummaryCounts(@Param("eventId") String eventId);

    /** 리드 건수(스코어 80+ 핫리드 포함). */
    @Select("""
            SELECT count(*)                                AS "total",
                   count(*) FILTER (WHERE score >= 80)     AS "hot"
            FROM lead WHERE event_id = #{eventId}
            """)
    Map<String, Object> findLeadCounts(@Param("eventId") String eventId);

    /** 일자별 등록/체크인 건수(누적은 서비스에서 산출). */
    @Select("""
            SELECT to_char(registered_at, 'MM.DD')                 AS "day",
                   count(*)                                        AS "regCount",
                   count(*) FILTER (WHERE checkin_state = 'done')  AS "checkinCount"
            FROM visitor_registration
            WHERE event_id = #{eventId}
            GROUP BY registered_at::date, to_char(registered_at, 'MM.DD')
            ORDER BY registered_at::date
            """)
    List<Map<String, Object>> findDailyTrend(@Param("eventId") String eventId);

    /** 유형별 분포 집계. */
    @Select("""
            SELECT visitor_type AS "type", count(*) AS "cnt"
            FROM visitor_registration
            WHERE event_id = #{eventId}
            GROUP BY visitor_type
            """)
    List<Map<String, Object>> findTypeCounts(@Param("eventId") String eventId);

    // ── 목록(마스킹) ──

    /** 등록자 목록 — 마스킹 필드만 SELECT(원문 미반환). */
    @Select("""
            SELECT id,
                   CASE WHEN char_length(name) <= 1 THEN name
                        WHEN char_length(name) = 2 THEN left(name,1) || '*'
                        ELSE left(name,1) || repeat('*', char_length(name)-2) || right(name,1)
                   END                                            AS "nameMasked",
                   visitor_type                                   AS "type",
                   company,
                   to_char(registered_at, 'YYYY.MM.DD')           AS "registeredAt",
                   checkin_state                                  AS "checkin",
                   badge_issued                                   AS "badgeIssued"
            FROM visitor_registration
            WHERE event_id = #{eventId}
            ORDER BY registered_at DESC, id DESC
            LIMIT #{limit} OFFSET #{offset}
            """)
    List<Map<String, Object>> findVisitorPage(@Param("eventId") String eventId,
                                              @Param("limit") int limit,
                                              @Param("offset") int offset);

    @Select("SELECT count(*) FROM visitor_registration WHERE event_id = #{eventId}")
    long countVisitors(@Param("eventId") String eventId);

    /** 리드 목록 — 마스킹 + AI 저장값. jsonb 는 ::text 로 반환해 서비스에서 파싱. booth 선택 필터. */
    @Select("""
            <script>
            SELECT id,
                   CASE WHEN char_length(name) &lt;= 1 THEN name
                        WHEN char_length(name) = 2 THEN left(name,1) || '*'
                        ELSE left(name,1) || repeat('*', char_length(name)-2) || right(name,1)
                   END                                            AS "nameMasked",
                   role, company, product, interest, score,
                   to_char(collected_at, 'HH24:MI:SS')            AS "collectedAt",
                   CASE WHEN phone IS NULL THEN NULL
                        WHEN char_length(regexp_replace(phone,'\\D','','g')) >= 7
                          THEN left(regexp_replace(phone,'\\D','','g'),3) || '-****-' || right(regexp_replace(phone,'\\D','','g'),4)
                        ELSE '***' END                            AS "phoneMasked",
                   CASE WHEN email IS NULL OR position('@' in email) = 0 THEN NULL
                        ELSE left(split_part(email,'@',1),3) || '***@' || split_part(email,'@',2)
                   END                                            AS "emailMasked",
                   ai_reasons::text                               AS "aiReasons",
                   activity::text                                 AS "activity",
                   followup_draft                                 AS "followupDraft",
                   ai_generated                                   AS "aiGenerated"
            FROM lead
            WHERE event_id = #{eventId}
              <if test="boothId != null and boothId != ''">AND booth_id = #{boothId}</if>
            ORDER BY score DESC, collected_at DESC
            LIMIT #{limit} OFFSET #{offset}
            </script>
            """)
    List<Map<String, Object>> findLeadPage(@Param("eventId") String eventId,
                                           @Param("boothId") String boothId,
                                           @Param("limit") int limit,
                                           @Param("offset") int offset);

    @Select("""
            <script>
            SELECT count(*) FROM lead
            WHERE event_id = #{eventId}
              <if test="boothId != null and boothId != ''">AND booth_id = #{boothId}</if>
            </script>
            """)
    long countLeads(@Param("eventId") String eventId, @Param("boothId") String boothId);

    // ── 공개 사전등록 ──

    /** 행사+이메일 기존 등록 조회(중복 시 재반환). 원문 미반환(id·badge_code 만). */
    @Select("""
            SELECT id AS "registrationId", badge_code AS "badgeCode"
            FROM visitor_registration
            WHERE event_id = #{eventId} AND lower(email) = lower(#{email})
            LIMIT 1
            """)
    Map<String, Object> findByEventEmail(@Param("eventId") String eventId, @Param("email") String email);

    /** 행사 존재 확인(공개 등록 대상 유효성). */
    @Select("SELECT count(*) FROM event WHERE id = #{eventId}")
    long eventExists(@Param("eventId") String eventId);

    /** 사전등록 삽입 — 원문 저장(응답에는 노출 금지). id·badgeCode 는 서비스가 생성. */
    @Insert("""
            INSERT INTO visitor_registration
              (id, event_id, name, phone, email, company, visitor_type, agree_privacy, agree_marketing, badge_code, badge_issued, checkin_state)
            VALUES
              (#{id}, #{eventId}, #{name}, #{phone}, #{email}, NULL, #{visitorType}, #{agreePrivacy}, #{agreeMarketing}, #{badgeCode}, false, 'waiting')
            """)
    int insertRegistration(@Param("id") String id,
                                           @Param("eventId") String eventId,
                                           @Param("name") String name,
                                           @Param("phone") String phone,
                                           @Param("email") String email,
                                           @Param("visitorType") String visitorType,
                                           @Param("agreePrivacy") boolean agreePrivacy,
                                           @Param("agreeMarketing") boolean agreeMarketing,
                                           @Param("badgeCode") String badgeCode);

    // ── 현장 체크인(SCR-31) ──

    /** QR 토큰 또는 등록ID 로 등록 1건 해소 — 체크인 판정에 필요한 상태·마스킹 필드만 반환(원문 미반환). */
    @Select("""
            <script>
            SELECT id                                             AS "registrationId",
                   CASE WHEN char_length(name) &lt;= 1 THEN name
                        WHEN char_length(name) = 2 THEN left(name,1) || '*'
                        ELSE left(name,1) || repeat('*', char_length(name)-2) || right(name,1)
                   END                                            AS "nameMasked",
                   visitor_type                                   AS "type",
                   company                                        AS "company",
                   badge_code                                     AS "badgeCode",
                   badge_issued                                   AS "badgeIssued",
                   checkin_state                                  AS "checkinState",
                   to_char(checkin_at, 'YYYY.MM.DD HH24:MI')      AS "checkinAt"
            FROM visitor_registration
            WHERE event_id = #{eventId}
              <choose>
                <when test="token != null and token != ''">AND qr_token = #{token}</when>
                <otherwise>AND id = #{registrationId}</otherwise>
              </choose>
            LIMIT 1
            </script>
            """)
    Map<String, Object> findForCheckin(@Param("eventId") String eventId,
                                       @Param("token") String token,
                                       @Param("registrationId") String registrationId);

    /** 체크인 확정 — 아직 완료가 아닌 건만 done 처리(중복 방지: 이미 done 이면 0 행 갱신). 갱신 행 수 반환. */
    @Update("""
            UPDATE visitor_registration
               SET checkin_state = 'done', checkin_at = now(), badge_issued = true
             WHERE event_id = #{eventId} AND id = #{registrationId}
               AND checkin_state <> 'done'
            """)
    int markCheckedIn(@Param("eventId") String eventId, @Param("registrationId") String registrationId);

    /** 현장 수동 검색 — 이름/연락처(숫자)/배지코드/등록ID 부분일치. 마스킹 필드만 반환. */
    @Select("""
            SELECT id                                             AS "registrationId",
                   CASE WHEN char_length(name) <= 1 THEN name
                        WHEN char_length(name) = 2 THEN left(name,1) || '*'
                        ELSE left(name,1) || repeat('*', char_length(name)-2) || right(name,1)
                   END                                            AS "nameMasked",
                   visitor_type                                   AS "type",
                   company                                        AS "company",
                   CASE WHEN phone IS NULL THEN NULL
                        WHEN char_length(regexp_replace(phone,'\\D','','g')) >= 7
                          THEN left(regexp_replace(phone,'\\D','','g'),3) || '-****-' || right(regexp_replace(phone,'\\D','','g'),4)
                        ELSE '***' END                            AS "phoneMasked",
                   badge_code                                     AS "badgeCode",
                   checkin_state                                  AS "checkinState"
            FROM visitor_registration
            WHERE event_id = #{eventId}
              AND ( name ILIKE '%' || #{q} || '%'
                 OR regexp_replace(coalesce(phone,''),'\\D','','g') LIKE '%' || regexp_replace(#{q},'\\D','','g') || '%'
                 OR upper(badge_code) LIKE '%' || upper(#{q}) || '%'
                 OR id = #{q} )
              AND ( #{qDigits} = '' OR regexp_replace(coalesce(phone,''),'\\D','','g') LIKE '%' || #{qDigits} || '%'
                    OR name ILIKE '%' || #{q} || '%' OR upper(badge_code) LIKE '%' || upper(#{q}) || '%' OR id = #{q} )
            ORDER BY registered_at DESC
            LIMIT 20
            """)
    List<Map<String, Object>> searchRegistrations(@Param("eventId") String eventId,
                                                  @Param("q") String q,
                                                  @Param("qDigits") String qDigits);

    // ── 배지 재발급 ──

    /** 배지 재발급 — 새 배지코드+QR 토큰 갱신, 발급 표시. 갱신 행 수 반환. */
    @Update("""
            UPDATE visitor_registration
               SET badge_code = #{badgeCode}, qr_token = #{qrToken}, badge_issued = true
             WHERE event_id = #{eventId} AND id = #{registrationId}
            """)
    int reissueBadge(@Param("eventId") String eventId, @Param("registrationId") String registrationId,
                     @Param("badgeCode") String badgeCode, @Param("qrToken") String qrToken);

    // ── 실시간 입장 집계(SCR-31) ──

    /** 시간대별 체크인 집계(당일 checkin_at 기준). */
    @Select("""
            SELECT to_char(checkin_at, 'HH24') || '시'                 AS "hour",
                   count(*)                                            AS "count"
            FROM visitor_registration
            WHERE event_id = #{eventId} AND checkin_state = 'done' AND checkin_at IS NOT NULL
            GROUP BY to_char(checkin_at, 'HH24')
            ORDER BY to_char(checkin_at, 'HH24')
            """)
    List<Map<String, Object>> findCheckinByHour(@Param("eventId") String eventId);

    // ── 리드 수집(SCR-32) ──

    /** 리드 삽입 — 원문 저장(응답 노출 금지). score·ai_reasons 는 서비스가 규칙 엔진으로 산출해 전달. */
    @Insert("""
            INSERT INTO lead
              (id, event_id, booth_id, exhibitor_id, name, phone, email, role, company, product,
               interest, score, ai_reasons, activity, note, followup_draft, ai_generated, agree_privacy, collected_at)
            VALUES
              (#{id}, #{eventId}, #{boothId}, #{exhibitorId}, #{name}, #{phone}, #{email}, #{role}, #{company}, #{product},
               #{interest}, #{score}, CAST(#{aiReasonsJson} AS jsonb), '[]'::jsonb, #{note}, NULL, false, #{agreePrivacy}, now())
            """)
    int insertLead(@Param("id") String id, @Param("eventId") String eventId, @Param("boothId") String boothId,
                   @Param("exhibitorId") String exhibitorId, @Param("name") String name, @Param("phone") String phone,
                   @Param("email") String email, @Param("role") String role, @Param("company") String company,
                   @Param("product") String product, @Param("interest") int interest, @Param("score") int score,
                   @Param("aiReasonsJson") String aiReasonsJson, @Param("note") String note,
                   @Param("agreePrivacy") boolean agreePrivacy);

    // ── 리드 AI 재계산(SCR-32, F051) ──

    /**
     * 단일 리드의 <b>비-PII</b> 신호만 조회(AI 재계산 입력). name/phone/email 은 절대 SELECT 하지 않는다.
     * activity·ai_reasons 는 ::text 로 반환해 서비스가 프롬프트 컨텍스트로 사용한다.
     */
    @Select("""
            SELECT role, company, product, interest, score,
                   activity::text    AS "activity",
                   ai_reasons::text  AS "aiReasons"
            FROM lead
            WHERE event_id = #{eventId} AND id = #{id}
            """)
    Map<String, Object> findLeadForRescore(@Param("eventId") String eventId, @Param("id") String id);

    /** 리드 스코어·근거·AI 생성 플래그 갱신(재계산 결과 반영). 갱신 행 수 반환. */
    @Update("""
            UPDATE lead
               SET score = #{score},
                   ai_reasons = CAST(#{aiReasonsJson} AS jsonb),
                   ai_generated = #{aiGenerated}
             WHERE event_id = #{eventId} AND id = #{id}
            """)
    int updateLeadScore(@Param("eventId") String eventId, @Param("id") String id,
                        @Param("score") int score, @Param("aiReasonsJson") String aiReasonsJson,
                        @Param("aiGenerated") boolean aiGenerated);

    /** CSV 내보내기용 리드 조회 — 마스킹 필드만(원문 미반환). booth 선택 필터. */
    @Select("""
            <script>
            SELECT CASE WHEN char_length(name) &lt;= 1 THEN name
                        WHEN char_length(name) = 2 THEN left(name,1) || '*'
                        ELSE left(name,1) || repeat('*', char_length(name)-2) || right(name,1)
                   END                                            AS "nameMasked",
                   role, company, product, interest, score,
                   CASE WHEN phone IS NULL THEN NULL
                        WHEN char_length(regexp_replace(phone,'\\D','','g')) &gt;= 7
                          THEN left(regexp_replace(phone,'\\D','','g'),3) || '-****-' || right(regexp_replace(phone,'\\D','','g'),4)
                        ELSE '***' END                            AS "phoneMasked",
                   CASE WHEN email IS NULL OR position('@' in email) = 0 THEN NULL
                        ELSE left(split_part(email,'@',1),3) || '***@' || split_part(email,'@',2)
                   END                                            AS "emailMasked",
                   to_char(collected_at, 'YYYY-MM-DD HH24:MI:SS')  AS "collectedAt"
            FROM lead
            WHERE event_id = #{eventId}
              <if test="boothId != null and boothId != ''">AND booth_id = #{boothId}</if>
            ORDER BY score DESC, collected_at DESC
            </script>
            """)
    List<Map<String, Object>> findLeadsForCsv(@Param("eventId") String eventId, @Param("boothId") String boothId);
}
