<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.mall.event.mapper.EventMapper">

  <!-- ===== 이벤트 ===== -->
  <select id="findEvents" resultType="com.zioinfo.mall.event.MallEvent">
    SELECT * FROM mall_event
    <where>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="type != null and type != ''">AND event_type = #{type}</if>
      <if test="activeOnly != null and activeOnly == true">AND active = TRUE</if>
    </where>
    ORDER BY id DESC
  </select>

  <!-- 진행중(PUBLISHED + 기간 내). tier 지정 시 전체대상 또는 해당 등급 포함만 -->
  <select id="findOngoing" resultType="com.zioinfo.mall.event.MallEvent">
    SELECT * FROM mall_event
    WHERE status = 'PUBLISHED' AND active = TRUE
      AND (start_date IS NULL OR start_date &lt;= CURRENT_DATE)
      AND (end_date IS NULL OR end_date &gt;= CURRENT_DATE)
      <if test="tier != null and tier != ''">
        AND (target_tiers IS NULL OR target_tiers = '' OR position(#{tier} in target_tiers) > 0)
      </if>
    ORDER BY start_date DESC, id DESC
  </select>

  <select id="findById" resultType="com.zioinfo.mall.event.MallEvent">SELECT * FROM mall_event WHERE id = #{id}</select>
  <select id="findByCode" resultType="com.zioinfo.mall.event.MallEvent">SELECT * FROM mall_event WHERE code = #{code}</select>

  <insert id="insert" parameterType="com.zioinfo.mall.event.MallEvent" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_event
      (code, title, event_type, description, start_date, end_date, target_tiers, store_ids,
       coupon_id, bonus_point_rate, gift_description, status, active, created_by)
    VALUES
      (#{code}, #{title}, COALESCE(#{eventType},'DISCOUNT'), #{description}, #{startDate}, #{endDate},
       #{targetTiers}, #{storeIds}, #{couponId}, #{bonusPointRate}, #{giftDescription},
       COALESCE(#{status},'DRAFT'), COALESCE(#{active},TRUE), #{createdBy})
  </insert>
  <update id="update">
    UPDATE mall_event SET
      title=#{title}, event_type=COALESCE(#{eventType},event_type), description=#{description},
      start_date=#{startDate}, end_date=#{endDate}, target_tiers=#{targetTiers}, store_ids=#{storeIds},
      coupon_id=#{couponId}, bonus_point_rate=#{bonusPointRate}, gift_description=#{giftDescription},
      status=COALESCE(#{status},status), active=COALESCE(#{active},active), updated_at=NOW()
    WHERE id=#{id}
  </update>
  <update id="updateStatus">UPDATE mall_event SET status=#{status}, updated_at=NOW() WHERE id=#{id}</update>
  <delete id="delete">DELETE FROM mall_event WHERE id=#{id}</delete>

  <!-- ===== 배너 ===== -->
  <select id="findBanners" resultType="com.zioinfo.mall.event.MallEventBanner">
    SELECT * FROM mall_event_banner
    <where>
      event_id = #{eventId}
      <if test="activeOnly != null and activeOnly == true">AND active = TRUE</if>
    </where>
    ORDER BY sort_order ASC, id ASC
  </select>
  <!-- 스토어프론트: 진행중 이벤트의 활성 배너 -->
  <select id="findActiveBanners" resultType="com.zioinfo.mall.event.MallEventBanner">
    SELECT b.* FROM mall_event_banner b
    JOIN mall_event e ON e.id = b.event_id
    WHERE b.active = TRUE AND e.status = 'PUBLISHED' AND e.active = TRUE
      AND (e.start_date IS NULL OR e.start_date &lt;= CURRENT_DATE)
      AND (e.end_date IS NULL OR e.end_date &gt;= CURRENT_DATE)
    ORDER BY b.sort_order ASC, b.id ASC
  </select>
  <insert id="insertBanner" parameterType="com.zioinfo.mall.event.MallEventBanner" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO mall_event_banner (event_id, image_url, headline, subtext, link_url, sort_order, active)
    VALUES (#{eventId}, #{imageUrl}, #{headline}, #{subtext}, #{linkUrl}, COALESCE(#{sortOrder},0), COALESCE(#{active},TRUE))
  </insert>
  <delete id="deleteBanner">DELETE FROM mall_event_banner WHERE id=#{id}</delete>
  <delete id="deleteBannersByEvent">DELETE FROM mall_event_banner WHERE event_id=#{eventId}</delete>

  <!-- ===== 참여 / 성과 ===== -->
  <insert id="insertParticipation">
    INSERT INTO mall_event_participation (event_id, owner, coupon_id, order_no, reward)
    VALUES (#{eventId}, #{owner}, #{couponId}, #{orderNo}, #{reward})
  </insert>
  <select id="performance" resultType="java.util.Map">
    SELECT
      (SELECT COUNT(*) FROM mall_event_participation p WHERE p.event_id = #{eventId}) AS "participations",
      (SELECT COUNT(DISTINCT p.owner) FROM mall_event_participation p WHERE p.event_id = #{eventId}) AS "participants",
      (SELECT COUNT(DISTINCT p.order_no) FROM mall_event_participation p WHERE p.event_id = #{eventId} AND p.order_no IS NOT NULL) AS "linkedOrders",
      COALESCE((SELECT SUM(o.pay_amount) FROM mall_event_participation p
                JOIN mall_order o ON o.order_no = p.order_no
                WHERE p.event_id = #{eventId} AND o.status NOT IN ('CANCELLED','PENDING')),0) AS "attributedSales"
  </select>

</mapper>
