<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
  TicketMapper — 입장권 상품·주문·발권 영속 (V50). tenant_id='KINTEX' 고정.
  보안(§0-3): 조회에서 구매자 원문(이름·연락처·이메일) 은 SELECT 하지 않는다 — 마스킹 컬럼/해시만 다룬다.
  동시성: decrementStock 는 조건부 UPDATE 로 oversell 를 원자적으로 차단한다.
-->
<mapper namespace="com.zioinfo.kintex.ticket.TicketMapper">

    <!-- 판매 상품 목록 — remaining = total-sold, onSale/soldOut 은 서버 계산. -->
    <select id="findProducts" resultType="map">
        SELECT id,
               event_id      AS "eventId",
               code,
               kind,
               name,
               description,
               price,
               currency,
               to_char(sale_start, 'YYYY-MM-DD"T"HH24:MI:SS') AS "saleStart",
               to_char(sale_end,   'YYYY-MM-DD"T"HH24:MI:SS') AS "saleEnd",
               total_qty     AS "totalQty",
               (total_qty - sold_qty) AS "remaining",
               max_per_order AS "maxPerOrder",
               CASE WHEN status = 'active'
                     AND (sale_start IS NULL OR sale_start &lt;= now())
                     AND (sale_end   IS NULL OR sale_end   &gt;= now())
                    THEN true ELSE false END AS "onSale",
               CASE WHEN (total_qty - sold_qty) &lt;= 0 THEN true ELSE false END AS "soldOut",
               sort_order    AS "sortOrder"
        FROM ticket_product
        WHERE tenant_id = 'KINTEX'
          AND event_id = #{eventId}
          AND status = 'active'
        ORDER BY sort_order, id
    </select>

    <!-- 단일 상품(주문 검증) — 판매기간·상태·잔여 판정에 필요한 원시값 포함. -->
    <select id="findProduct" resultType="map">
        SELECT id,
               event_id      AS "eventId",
               name,
               kind,
               price,
               currency,
               max_per_order AS "maxPerOrder",
               status,
               (total_qty - sold_qty) AS "remaining",
               CASE WHEN status = 'active'
                     AND (sale_start IS NULL OR sale_start &lt;= now())
                     AND (sale_end   IS NULL OR sale_end   &gt;= now())
                    THEN true ELSE false END AS "onSale"
        FROM ticket_product
        WHERE tenant_id = 'KINTEX'
          AND id = #{productId}
    </select>

    <!-- 조건부 재고 차감 — 판매중 + 잔여 충분할 때만 1행 갱신(oversell 차단). -->
    <update id="decrementStock">
        UPDATE ticket_product
        SET sold_qty = sold_qty + #{qty}
        WHERE tenant_id = 'KINTEX'
          AND id = #{productId}
          AND status = 'active'
          AND (sale_start IS NULL OR sale_start &lt;= now())
          AND (sale_end   IS NULL OR sale_end   &gt;= now())
          AND (total_qty - sold_qty) &gt;= #{qty}
    </update>

    <select id="countOrderNo" resultType="int">
        SELECT COUNT(*) FROM ticket_order WHERE order_no = #{orderNo}
    </select>

    <insert id="insertOrder">
        INSERT INTO ticket_order
            (tenant_id, id, order_no, event_id, product_id, product_name, qty, unit_price, total_amount,
             status, pay_method, pay_approval_no, buyer_name_masked, buyer_contact_masked, buyer_email_masked,
             buyer_contact_hash, ordered_at, paid_at)
        VALUES
            ('KINTEX', #{id}, #{orderNo}, #{eventId}, #{productId}, #{productName}, #{qty}, #{unitPrice},
             #{totalAmount}, #{status}, #{payMethod}, #{payApprovalNo}, #{buyerNameMasked},
             #{buyerContactMasked}, #{buyerEmailMasked}, #{buyerContactHash}, now(), now())
    </insert>

    <insert id="insertIssue">
        INSERT INTO ticket_issue (tenant_id, id, order_id, ticket_code, seq)
        VALUES ('KINTEX', #{id}, #{orderId}, #{ticketCode}, #{seq})
    </insert>

    <!-- 조회 — 주문번호 + 연락처 해시 둘 다 일치. 원문 PII 컬럼은 SELECT 하지 않음. -->
    <select id="findOrderForLookup" resultType="map">
        SELECT id,
               order_no      AS "orderNo",
               event_id      AS "eventId",
               product_name  AS "productName",
               qty,
               unit_price    AS "unitPrice",
               total_amount  AS "totalAmount",
               status,
               pay_method    AS "payMethod",
               buyer_name_masked    AS "buyerNameMasked",
               buyer_contact_masked AS "buyerContactMasked",
               buyer_email_masked   AS "buyerEmailMasked",
               to_char(ordered_at, 'YYYY-MM-DD"T"HH24:MI:SS') AS "orderedAt",
               to_char(paid_at,    'YYYY-MM-DD"T"HH24:MI:SS') AS "paidAt"
        FROM ticket_order
        WHERE order_no = #{orderNo}
          AND buyer_contact_hash = #{contactHash}
        LIMIT 1
    </select>

    <select id="findIssuesByOrder" resultType="map">
        SELECT ticket_code AS "ticketCode",
               seq,
               used
        FROM ticket_issue
        WHERE tenant_id = 'KINTEX'
          AND order_id = #{orderId}
        ORDER BY seq
    </select>

</mapper>
