package com.zioinfo.kintex.logistics;

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;

/**
 * M8 물류 확장 매퍼 — 장비 마스터/신청·렌탈 카탈로그/주문·선입고. camelCase 별칭은 쌍따옴표(PG 소문자 폴딩 함정).
 * 상태 전이 UPDATE 는 WHERE status 조건으로 원자 처리(0행이면 서비스가 409/404 매핑).
 * tenant_id 는 항상 조회/삽입 조건에 포함(멀티테넌시 표준). 날짜는 to_char 로 문자열 정규화.
 */
@Mapper
public interface LogisticsExtMapper {

    // ── 장비 카탈로그 ────────────────────────────────────────────────────────
    @Select("""
            SELECT id,
                   code,
                   name,
                   category,
                   unit,
                   unit_price AS "unitPrice",
                   spec
            FROM logistics_equipment
            WHERE tenant_id = #{tenantId} AND active = true
            ORDER BY category, name
            """)
    List<Map<String, Object>> findEquipmentCatalog(@Param("tenantId") String tenantId);

    @Select("""
            SELECT id, name, unit_price AS "unitPrice"
            FROM logistics_equipment
            WHERE tenant_id = #{tenantId} AND id = #{id}
            """)
    Map<String, Object> findEquipmentById(@Param("tenantId") String tenantId, @Param("id") String id);

    // ── 장비 신청 ────────────────────────────────────────────────────────────
    @Select("""
            SELECT r.id,
                   r.equipment_id   AS "equipmentId",
                   r.equipment_name AS "equipmentName",
                   e.category,
                   r.unit_price     AS "unitPrice",
                   r.booth_no       AS "boothNo",
                   r.quantity,
                   to_char(r.use_date, 'YYYY-MM-DD') AS "useDate",
                   r.days,
                   r.amount,
                   r.status,
                   r.note,
                   r.reject_reason  AS "rejectReason",
                   to_char(r.created_at, 'YYYY-MM-DD"T"HH24:MI:SS') AS "createdAt"
            FROM logistics_equipment_request r
            LEFT JOIN logistics_equipment e ON e.tenant_id = r.tenant_id AND e.id = r.equipment_id
            WHERE r.tenant_id = #{tenantId} AND r.event_id = #{eventId}
            ORDER BY r.created_at DESC
            """)
    List<Map<String, Object>> findEquipmentRequests(@Param("tenantId") String tenantId,
                                                     @Param("eventId") String eventId);

    @Insert("""
            INSERT INTO logistics_equipment_request
              (id, tenant_id, event_id, equipment_id, equipment_name, unit_price,
               booth_no, quantity, use_date, days, amount, status, note, requested_by)
            VALUES
              (#{id}, #{tenantId}, #{eventId}, #{equipmentId}, #{equipmentName}, #{unitPrice},
               #{boothNo}, #{quantity}, CAST(#{useDate} AS date), #{days}, #{amount}, 'requested', #{note}, #{userId})
            """)
    int insertEquipmentRequest(Map<String, Object> p);

    @Update("""
            UPDATE logistics_equipment_request
               SET status        = #{status},
                   reject_reason = #{reason},
                   decided_by    = #{userId},
                   decided_at    = now(),
                   updated_at    = now()
             WHERE tenant_id = #{tenantId} AND event_id = #{eventId} AND id = #{id}
            """)
    int updateEquipmentStatus(Map<String, Object> p);

    // ── 렌탈 카탈로그 ────────────────────────────────────────────────────────
    @Select("""
            SELECT id,
                   code,
                   name,
                   category,
                   unit,
                   daily_price AS "dailyPrice",
                   spec
            FROM logistics_rental_item
            WHERE tenant_id = #{tenantId} AND active = true
            ORDER BY category, name
            """)
    List<Map<String, Object>> findRentalCatalog(@Param("tenantId") String tenantId);

    @Select("""
            SELECT id, name, daily_price AS "dailyPrice"
            FROM logistics_rental_item
            WHERE tenant_id = #{tenantId} AND id = #{id}
            """)
    Map<String, Object> findRentalItemById(@Param("tenantId") String tenantId, @Param("id") String id);

    // ── 렌탈 주문 ────────────────────────────────────────────────────────────
    @Select("""
            SELECT o.id,
                   o.item_id     AS "itemId",
                   o.item_name   AS "itemName",
                   i.category,
                   o.daily_price AS "dailyPrice",
                   o.booth_no    AS "boothNo",
                   o.quantity,
                   to_char(o.start_date, 'YYYY-MM-DD') AS "startDate",
                   to_char(o.end_date, 'YYYY-MM-DD')   AS "endDate",
                   o.days,
                   o.amount,
                   o.status,
                   o.note,
                   to_char(o.created_at, 'YYYY-MM-DD"T"HH24:MI:SS') AS "createdAt"
            FROM logistics_rental_order o
            LEFT JOIN logistics_rental_item i ON i.tenant_id = o.tenant_id AND i.id = o.item_id
            WHERE o.tenant_id = #{tenantId} AND o.event_id = #{eventId}
            ORDER BY o.created_at DESC
            """)
    List<Map<String, Object>> findRentalOrders(@Param("tenantId") String tenantId,
                                               @Param("eventId") String eventId);

    @Insert("""
            INSERT INTO logistics_rental_order
              (id, tenant_id, event_id, item_id, item_name, daily_price,
               booth_no, quantity, start_date, end_date, days, amount, status, note, ordered_by)
            VALUES
              (#{id}, #{tenantId}, #{eventId}, #{itemId}, #{itemName}, #{dailyPrice},
               #{boothNo}, #{quantity}, CAST(#{startDate} AS date), CAST(#{endDate} AS date),
               #{days}, #{amount}, 'ordered', #{note}, #{userId})
            """)
    int insertRentalOrder(Map<String, Object> p);

    @Update("""
            UPDATE logistics_rental_order
               SET status     = #{status},
                   decided_by = #{userId},
                   decided_at = now(),
                   updated_at = now()
             WHERE tenant_id = #{tenantId} AND event_id = #{eventId} AND id = #{id}
            """)
    int updateRentalStatus(Map<String, Object> p);

    // ── 선입고 트래킹 ────────────────────────────────────────────────────────
    @Select("""
            SELECT id,
                   tracking_no AS "trackingNo",
                   carrier,
                   booth_no    AS "boothNo",
                   item_desc   AS "itemDesc",
                   quantity,
                   status,
                   to_char(eta_date, 'YYYY-MM-DD') AS "etaDate",
                   to_char(warehouse_at, 'YYYY-MM-DD"T"HH24:MI:SS') AS "warehouseAt",
                   to_char(delivered_at, 'YYYY-MM-DD"T"HH24:MI:SS') AS "deliveredAt",
                   note,
                   to_char(created_at, 'YYYY-MM-DD"T"HH24:MI:SS') AS "createdAt"
            FROM logistics_inbound
            WHERE tenant_id = #{tenantId} AND event_id = #{eventId}
            ORDER BY created_at DESC
            """)
    List<Map<String, Object>> findInbound(@Param("tenantId") String tenantId,
                                          @Param("eventId") String eventId);

    @Insert("""
            INSERT INTO logistics_inbound
              (id, tenant_id, event_id, tracking_no, carrier, booth_no, item_desc,
               quantity, status, eta_date, note, registered_by)
            VALUES
              (#{id}, #{tenantId}, #{eventId}, #{trackingNo}, #{carrier}, #{boothNo}, #{itemDesc},
               #{quantity}, 'registered', CAST(#{etaDate} AS date), #{note}, #{userId})
            """)
    int insertInbound(Map<String, Object> p);

    /**
     * 선입고 상태 전이 — 창고 도착/부스 전달 시각을 상태에 맞춰 기록(멱등적 COALESCE).
     * (비교연산 없음 · raw @Update 본문이라 이스케이프 불필요.)
     */
    @Update("""
            UPDATE logistics_inbound
               SET status       = #{status},
                   warehouse_at = CASE WHEN #{status} = 'arrived_warehouse' THEN COALESCE(warehouse_at, now()) ELSE warehouse_at END,
                   delivered_at = CASE WHEN #{status} = 'delivered_booth'  THEN COALESCE(delivered_at, now()) ELSE delivered_at END,
                   note         = COALESCE(#{note}, note),
                   updated_by   = #{userId},
                   updated_at   = now()
             WHERE tenant_id = #{tenantId} AND event_id = #{eventId} AND id = #{id}
            """)
    int updateInboundStatus(Map<String, Object> p);
}
