package com.zioinfo.kintex.marketing;

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 java.util.List;
import java.util.Map;

/**
 * M12 EDM·캠페인 · 스폰서십 매퍼. Map 반환 @Select 는 별칭에 쌍따옴표(AS "x")로 카멜케이스 키 보존.
 */
@Mapper
public interface MarketingMapper {

    // ── 캠페인 ──
    @Select("""
            <script>
            SELECT id, name, status, audience, meta,
                   open_rate AS "openRate", click_rate AS "clickRate"
            FROM edm_campaign
            WHERE event_id = #{eventId}
              <if test="status != null and status != ''">AND status = #{status}</if>
            ORDER BY created_at DESC, id DESC
            </script>
            """)
    List<Map<String, Object>> findCampaigns(@Param("eventId") String eventId, @Param("status") String status);

    @Insert("""
            INSERT INTO edm_campaign (id, event_id, name, status, audience, meta, open_rate, click_rate, scheduled_at)
            VALUES (#{id}, #{eventId}, #{name}, #{status}, #{audience}, #{meta}, '-', '-',
                    CASE WHEN #{scheduledAt} IS NULL OR #{scheduledAt} = '' THEN NULL ELSE CAST(#{scheduledAt} AS timestamptz) END)
            """)
    int insertCampaign(@Param("id") String id,
                       @Param("eventId") String eventId,
                       @Param("name") String name,
                       @Param("status") String status,
                       @Param("audience") int audience,
                       @Param("meta") String meta,
                       @Param("scheduledAt") String scheduledAt);

    @Select("SELECT count(*) FROM event WHERE id = #{eventId}")
    long eventExists(@Param("eventId") String eventId);

    // ── 캠페인 발송(수신동의자 한정) ──
    /** 캠페인 단건(발송 게이트용) — 소속 행사·현재 상태. */
    @Select("""
            SELECT id, event_id AS "eventId", name, status
            FROM edm_campaign
            WHERE id = #{id} AND event_id = #{eventId}
            """)
    Map<String, Object> findCampaign(@Param("eventId") String eventId, @Param("id") String id);

    /**
     * 발송 대상 이메일(수신동의 = agree_marketing=true, 이메일 보유). 원문 이메일은 발송 경로 한정 —
     * 응답/로그에 노출하지 않는다. 회당 상한(limit) 가드는 서비스가 적용.
     */
    @Select("""
            SELECT DISTINCT lower(email) AS "email"
            FROM visitor_registration
            WHERE event_id = #{eventId}
              AND agree_marketing = true
              AND email IS NOT NULL AND email <> ''
            ORDER BY lower(email)
            LIMIT #{limit}
            """)
    List<Map<String, Object>> listMarketingRecipients(@Param("eventId") String eventId, @Param("limit") int limit);

    /** 발송 결과 반영 — 상태·발송 건수(audience)·표시 메타 갱신. */
    @org.apache.ibatis.annotations.Update("""
            UPDATE edm_campaign
               SET status = #{status}, audience = #{sentCount}, meta = #{meta}, updated_at = now()
             WHERE id = #{id} AND event_id = #{eventId}
            """)
    int markCampaignSent(@Param("eventId") String eventId, @Param("id") String id,
                         @Param("status") String status, @Param("sentCount") int sentCount,
                         @Param("meta") String meta);

    // ── 스폰서십 ──
    @Select("""
            SELECT id, code, name, name_ko AS "nameKo", price, accent,
                   benefits::text AS "benefits",
                   total_qty AS "totalQty", sold_qty AS "soldQty", dday
            FROM sponsorship_package
            WHERE event_id = #{eventId}
            ORDER BY sort_order, id
            """)
    List<Map<String, Object>> findPackages(@Param("eventId") String eventId);

    @Select("""
            SELECT id, name, tier_label AS "tierLabel", contract_status AS "contractStatus",
                   fulfillment::text AS "fulfillment"
            FROM sponsorship_sponsor
            WHERE event_id = #{eventId}
            ORDER BY created_at, id
            """)
    List<Map<String, Object>> findSponsors(@Param("eventId") String eventId);

    /** 스폰서십 요약 원자 집계(스폰서 수·판매액·잔여 패키지·이행 진행률). */
    @Select("""
            SELECT
              (SELECT count(*) FROM sponsorship_sponsor s WHERE s.event_id = #{eventId})                       AS "sponsorCount",
              (SELECT COALESCE(sum(p.price), 0) FROM sponsorship_package p
                 WHERE p.event_id = #{eventId})                                                                AS "priceTotalRef",
              (SELECT COALESCE(sum(GREATEST(p.total_qty - p.sold_qty, 0)), 0) FROM sponsorship_package p
                 WHERE p.event_id = #{eventId})                                                                AS "remainingPackages"
            """)
    Map<String, Object> findSponsorshipCounts(@Param("eventId") String eventId);
}
