package com.zioinfo.kintex.boothsales;

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;

/**
 * F005/F006 부스 판매 매퍼 — booth_sale(판매 오버레이) + M2 booth/layout(좌표, read-only).
 * <p><b>★ Map 반환 @Select 별칭은 반드시 쌍따옴표(AS "x")</b> — PG 는 따옴표 없는 별칭을 소문자로 접어
 * Map 키가 null 이 된다(WORK_STATUS §7). 선택 파라미터(null)는 {@code COALESCE(CAST(#{p} AS type), 대체)}
 * 로 흡수해 PG 파라미터 타입 미결정 오류를 회피한다. ★{@code <script>} 미사용 — 비교연산 {@code <} 부팅
 * 크래시 회피(비교는 raw @Select 본문에서만 사용).
 */
@Mapper
public interface BoothSalesMapper {

    /**
     * 행사(+선택 홀)의 판매 인벤토리 — 최신 layout 버전의 booth 좌표 + booth_sale 오버레이 병합.
     * 실효 상태: 만료 hold(held_until 경과)는 available 로 강등. 면적은 ㎡(ST_Area, SRID 0=미터).
     * heldByMe 판정을 위해 held_by 를 그대로 노출(내부 식별자, 응답 DTO 에는 미포함 — 서비스에서 소거).
     */
    @Select("""
            SELECT b.id                                        AS "boothId",
                   b.booth_no                                  AS "boothNo",
                   b.booth_type                                AS "boothType",
                   l.hall_id                                   AS "hallId",
                   h.label                                     AS "hallLabel",
                   ROUND(ST_Area(b.geom)::numeric, 2)          AS "areaM2",
                   CASE
                       WHEN bs.status = 'held' AND bs.held_until IS NOT NULL AND bs.held_until < now()
                            THEN 'available'
                       ELSE COALESCE(bs.status, 'available')
                   END                                         AS "status",
                   bs.unit_price                               AS "unitPrice",
                   bs.held_by                                  AS "heldBy",
                   to_char(bs.held_until AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "heldUntil",
                   bs.sold_to_company                          AS "soldToCompany",
                   b.assigned_company_name                     AS "assignedCompany"
            FROM booth b
            JOIN layout l ON l.id = b.layout_id
            JOIN hall   h ON h.id = l.hall_id
            LEFT JOIN booth_sale bs
                   ON bs.booth_id = b.id
                  AND bs.event_id = l.event_id
                  AND bs.tenant_id = #{tenantId}
            WHERE l.event_id = #{eventId}
              AND l.hall_id = COALESCE(CAST(#{hallId} AS varchar), l.hall_id)
              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 l.hall_id, b.booth_no
            """)
    List<Map<String, Object>> findInventory(@Param("tenantId") String tenantId,
                                            @Param("eventId") String eventId,
                                            @Param("hallId") String hallId);

    /** 단일 판매 단위(전이 후 응답) — 인벤토리와 동일 shape, booth 1행. 없으면 null. */
    @Select("""
            SELECT b.id                                        AS "boothId",
                   b.booth_no                                  AS "boothNo",
                   b.booth_type                                AS "boothType",
                   l.hall_id                                   AS "hallId",
                   h.label                                     AS "hallLabel",
                   ROUND(ST_Area(b.geom)::numeric, 2)          AS "areaM2",
                   CASE
                       WHEN bs.status = 'held' AND bs.held_until IS NOT NULL AND bs.held_until < now()
                            THEN 'available'
                       ELSE COALESCE(bs.status, 'available')
                   END                                         AS "status",
                   bs.unit_price                               AS "unitPrice",
                   bs.held_by                                  AS "heldBy",
                   to_char(bs.held_until AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "heldUntil",
                   bs.sold_to_company                          AS "soldToCompany",
                   b.assigned_company_name                     AS "assignedCompany"
            FROM booth b
            JOIN layout l ON l.id = b.layout_id
            JOIN hall   h ON h.id = l.hall_id
            LEFT JOIN booth_sale bs
                   ON bs.booth_id = b.id
                  AND bs.event_id = l.event_id
                  AND bs.tenant_id = #{tenantId}
            WHERE l.event_id = #{eventId}
              AND b.id = #{boothId}
            LIMIT 1
            """)
    Map<String, Object> findUnit(@Param("tenantId") String tenantId,
                                 @Param("eventId") String eventId,
                                 @Param("boothId") String boothId);

    /** booth 존재·판매면적 확인(hold/purchase 사전검증). 없으면 null. */
    @Select("""
            SELECT b.id                               AS "boothId",
                   b.booth_no                         AS "boothNo",
                   l.hall_id                          AS "hallId",
                   ROUND(ST_Area(b.geom)::numeric, 2) AS "areaM2",
                   b.assigned_company_name            AS "assignedCompany"
            FROM booth b
            JOIN layout l ON l.id = b.layout_id
            WHERE l.event_id = #{eventId} AND b.id = #{boothId}
            LIMIT 1
            """)
    Map<String, Object> findBooth(@Param("eventId") String eventId, @Param("boothId") String boothId);

    /** 전시홀 임대 요율(원/㎡) — master_data RATE.exhibitionHallPerM2(rates-v1.0). 없으면 null. */
    @Select("""
            SELECT (value_json #>> '{}')::numeric
            FROM master_data WHERE category = 'RATE' AND code = 'exhibitionHallPerM2'
            """)
    Double findRatePerM2();

    /**
     * 부스 hold 원자 전이(참가업체 셀프 선택).
     * <p>행 없으면 INSERT(held), 있으면 available/만료 held/본인 held 일 때만 UPDATE(held·TTL 갱신).
     * sold/blocked/타인 활성 held 는 WHERE 불일치로 0행 → 서비스가 409 로 매핑.
     * (비교연산 {@code <} 는 raw @Insert 본문이라 이스케이프 불필요.)
     */
    @Insert("""
            INSERT INTO booth_sale
              (id, tenant_id, event_id, hall_id, booth_id, booth_no, status,
               held_by, held_until, area_m2, created_by)
            VALUES
              (#{id}, #{tenantId}, #{eventId}, #{hallId}, #{boothId}, #{boothNo}, 'held',
               #{userId}, now() + make_interval(mins => #{ttlMinutes}), CAST(#{areaM2} AS numeric), #{userId})
            ON CONFLICT (event_id, booth_id) DO UPDATE
               SET status     = 'held',
                   held_by    = #{userId},
                   held_until = now() + make_interval(mins => #{ttlMinutes}),
                   hall_id    = EXCLUDED.hall_id,
                   booth_no   = EXCLUDED.booth_no,
                   updated_at = now()
             WHERE booth_sale.status = 'available'
                OR (booth_sale.status = 'held'
                    AND (booth_sale.held_until IS NULL OR booth_sale.held_until < now()))
                OR (booth_sale.status = 'held' AND booth_sale.held_by = #{userId})
            """)
    int holdBooth(Map<String, Object> p);

    /**
     * 구매 확정(hold→sold) — 본인이 유효 hold(만료 전) 중일 때만 전이. 금액은 서버 재계산.
     * 0행이면 hold 만료·미보유·이미 판매 → 서비스가 409.
     */
    @Update("""
            UPDATE booth_sale
               SET status          = 'sold',
                   sold_to_company = #{company},
                   sold_by         = #{userId},
                   sold_at         = now(),
                   unit_price      = COALESCE(unit_price, #{price}),
                   held_until      = NULL,
                   updated_at      = now()
             WHERE tenant_id = #{tenantId}
               AND event_id  = #{eventId}
               AND booth_id  = #{boothId}
               AND status    = 'held'
               AND held_by   = #{userId}
               AND (held_until IS NULL OR held_until >= now())
            """)
    int purchaseBooth(Map<String, Object> p);

    /** 본인 hold 해제(available 복원). 타인·판매건 미영향. */
    @Update("""
            UPDATE booth_sale
               SET status = 'available', held_by = NULL, held_until = NULL, updated_at = now()
             WHERE tenant_id = #{tenantId} AND event_id = #{eventId} AND booth_id = #{boothId}
               AND status = 'held' AND held_by = #{userId}
            """)
    int releaseHold(Map<String, Object> p);

    /**
     * 주최자 blocked 전환(available/held → blocked). sold 는 차단 불가(WHERE status <> 'sold').
     * 행이 없으면 신규 blocked 생성(booth 인벤토리에 상태를 확정). 0행이면 sold 충돌 → 409.
     */
    @Insert("""
            INSERT INTO booth_sale
              (id, tenant_id, event_id, hall_id, booth_id, booth_no, status, blocked_reason, area_m2, created_by)
            VALUES
              (#{id}, #{tenantId}, #{eventId}, #{hallId}, #{boothId}, #{boothNo}, 'blocked',
               #{reason}, CAST(#{areaM2} AS numeric), #{userId})
            ON CONFLICT (event_id, booth_id) DO UPDATE
               SET status         = 'blocked',
                   blocked_reason = #{reason},
                   held_by        = NULL,
                   held_until     = NULL,
                   updated_at     = now()
             WHERE booth_sale.status <> 'sold'
            """)
    int blockBooth(Map<String, Object> p);

    /** 주최자 blocked 해제(available 복원). blocked 아닌 건 미영향. */
    @Update("""
            UPDATE booth_sale
               SET status = 'available', blocked_reason = NULL, updated_at = now()
             WHERE tenant_id = #{tenantId} AND event_id = #{eventId} AND booth_id = #{boothId}
               AND status = 'blocked'
            """)
    int unblockBooth(Map<String, Object> p);

    /**
     * 주최자 판매가 override(원, null=파생 복원). 행 없으면 available 로 생성 후 가격 설정.
     */
    @Insert("""
            INSERT INTO booth_sale
              (id, tenant_id, event_id, hall_id, booth_id, booth_no, status, unit_price, area_m2, created_by)
            VALUES
              (#{id}, #{tenantId}, #{eventId}, #{hallId}, #{boothId}, #{boothNo}, 'available',
               #{unitPrice}, CAST(#{areaM2} AS numeric), #{userId})
            ON CONFLICT (event_id, booth_id) DO UPDATE
               SET unit_price = #{unitPrice}, updated_at = now()
            """)
    int setPrice(Map<String, Object> p);
}
