package com.zioinfo.kintex.settlement;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * M9 정산 매퍼 — invoice·invoice_payment + 임대/유틸리티 집계(read-only).
 * <p><b>★ Map 반환 @Select 별칭은 반드시 쌍따옴표(AS "x")</b> — PG는 따옴표 없는 별칭을 소문자로
 * 접어 Map 키가 전부 null 이 된다(WORK_STATUS §7). ★{@code <script>} 블록의 {@code <}/{@code <=}는
 * 사용하지 않는다(부팅 크래시 방지 — 비교는 뒤집거나 회피).
 */
@Mapper
public interface SettlementMapper {

    // ── 대시보드 집계 (취소분 제외 — 재무 KPI 왜곡 방지) ────────────────────────
    @Select("""
            SELECT COALESCE(sum(total), 0)        AS "billed",
                   COALESCE(sum(paid_amount), 0)  AS "collected",
                   count(*)                       AS "invoiceCount",
                   count(*) FILTER (WHERE status = 'overdue') AS "overdueCount"
            FROM invoice WHERE event_id = #{eventId} AND status <> 'cancelled'
            """)
    Map<String, Object> dashboardTotals(@Param("eventId") String eventId);

    @Select("""
            SELECT status                        AS "status",
                   count(*)                      AS "count",
                   COALESCE(sum(total), 0)       AS "totalAmount",
                   COALESCE(sum(paid_amount), 0) AS "paidAmount"
            FROM invoice WHERE event_id = #{eventId}
            GROUP BY status
            ORDER BY status
            """)
    List<Map<String, Object>> statusDistribution(@Param("eventId") String eventId);

    // ── 청구서 목록/상세 ─────────────────────────────────────────────────────
    String INVOICE_COLS = """
            SELECT i.id,
                   i.event_id       AS "eventId",
                   i.invoice_no     AS "invoiceNo",
                   i.exhibitor_name AS "exhibitorName",
                   i.subtotal, i.vat, i.total,
                   i.paid_amount    AS "paidAmount",
                   i.status,
                   to_char(i.due_date, 'YYYY-MM-DD')                                    AS "dueDate",
                   to_char(i.issued_at AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "issuedAt",
                   to_char(i.paid_at   AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "paidAt",
                   i.note,
                   COALESCE(i.category, 'rental') AS "category",
                   i.line_items::text AS "lineItems"
            FROM invoice i
            """;

    @Select("<script>" + INVOICE_COLS
            + "<where>"
            + "  i.event_id = #{eventId}"
            + "  <if test='status != null and status != \"\"'>AND i.status = #{status}</if>"
            + "</where>"
            + " ORDER BY i.created_at DESC, i.invoice_no DESC LIMIT #{limit} OFFSET #{offset}"
            + "</script>")
    List<Map<String, Object>> listInvoices(@Param("eventId") String eventId,
                                           @Param("status") String status,
                                           @Param("limit") int limit,
                                           @Param("offset") int offset);

    @Select("<script>"
            + "SELECT count(*) FROM invoice"
            + "<where>"
            + "  event_id = #{eventId}"
            + "  <if test='status != null and status != \"\"'>AND status = #{status}</if>"
            + "</where>"
            + "</script>")
    long countInvoices(@Param("eventId") String eventId, @Param("status") String status);

    @Select("<script>" + INVOICE_COLS + " WHERE i.id = #{id}</script>")
    Map<String, Object> findInvoice(@Param("id") String id);

    @Select("""
            SELECT id,
                   amount,
                   method,
                   memo,
                   to_char(recorded_at AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "recordedAt"
            FROM invoice_payment WHERE invoice_id = #{invoiceId}
            ORDER BY recorded_at ASC
            """)
    List<Map<String, Object>> listPayments(@Param("invoiceId") String invoiceId);

    // ── 상태 전이/수납 ────────────────────────────────────────────────────────
    /** 발행: pending → invoiced (issued_at·due_date 확정). 이미 발행된 건은 미영향(WHERE 조건). */
    @Update("""
            UPDATE invoice
               SET status = 'invoiced', issued_at = now(),
                   due_date = COALESCE(due_date, (now() + interval '30 day')::date),
                   updated_at = now()
             WHERE id = #{id} AND status = 'pending'
            """)
    int issueInvoice(@Param("id") String id);

    @Insert("""
            INSERT INTO invoice_payment (id, invoice_id, amount, method, memo, recorded_by)
            VALUES (#{id}, #{invoiceId}, #{amount}, #{method}, #{memo}, #{recordedBy})
            """)
    int insertPayment(Map<String, Object> p);

    /**
     * 수납 반영 — paid_amount 를 결제 이력 합계로 재계산하고 상태를 파생.
     * 완납(paid_amount &gt;= total)이면 paid, 부분 수납이면 invoiced, 그 외 유지.
     * (비교 연산은 SQL 본문 — {@code <script>} 아님 — 이므로 이스케이프 불필요.)
     */
    @Update("""
            UPDATE invoice i
               SET paid_amount = p.sum_paid,
                   status = CASE
                                WHEN p.sum_paid >= i.total THEN 'paid'
                                WHEN p.sum_paid > 0        THEN 'invoiced'
                                ELSE i.status
                            END,
                   paid_at = CASE WHEN p.sum_paid >= i.total THEN now() ELSE i.paid_at END,
                   issued_at = COALESCE(i.issued_at, now()),
                   due_date = COALESCE(i.due_date, (now() + interval '30 day')::date),
                   updated_at = now()
              FROM (SELECT COALESCE(sum(amount), 0) AS sum_paid
                      FROM invoice_payment WHERE invoice_id = #{id}) p
             WHERE i.id = #{id}
            """)
    int applyPayment(@Param("id") String id);

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

    /** 행사 참가업체별(배정 업체명) 판매면적·부스 수 — 임대료 산정 원천. */
    @Select("""
            SELECT lower(btrim(b.assigned_company_name)) AS "companyKey",
                   max(btrim(b.assigned_company_name))   AS "exhibitorName",
                   COALESCE(sum(ST_Area(b.geom)), 0)     AS "areaM2",
                   count(b.id)                           AS "boothCount"
            FROM booth b
            JOIN layout l ON l.id = b.layout_id
            WHERE l.event_id = #{eventId}
              AND b.assigned_company_name IS NOT NULL AND btrim(b.assigned_company_name) <> ''
            GROUP BY lower(btrim(b.assigned_company_name))
            """)
    List<Map<String, Object>> aggregateRentalByExhibitor(@Param("eventId") String eventId);

    /** 행사 참가업체별 유틸리티 매출 — utility_order.quote.total 합(숫자형만). */
    @Select("""
            SELECT lower(btrim(b.assigned_company_name)) AS "companyKey",
                   COALESCE(sum((uo.quote->>'total')::numeric), 0) AS "utility"
            FROM utility_order uo
            JOIN booth b ON b.id = uo.booth_id
            JOIN layout l ON l.id = b.layout_id
            WHERE l.event_id = #{eventId}
              AND b.assigned_company_name IS NOT NULL AND btrim(b.assigned_company_name) <> ''
              AND uo.quote->>'total' ~ '^[0-9]+(\\.[0-9]+)?$'
            GROUP BY lower(btrim(b.assigned_company_name))
            """)
    List<Map<String, Object>> aggregateUtilityByExhibitor(@Param("eventId") String eventId);

    /** 생성 멱등 — (event_id, exhibitor_name) 충돌 시 무시(기존 청구서 보존). */
    @Insert("""
            INSERT INTO invoice (id, event_id, exhibitor_name, invoice_no,
                                 subtotal, vat, total, status, line_items, created_by)
            VALUES (#{id}, #{eventId}, #{exhibitorName}, #{invoiceNo},
                    #{subtotal}, #{vat}, #{total}, 'pending', CAST(#{lineItems} AS jsonb), #{createdBy})
            ON CONFLICT (event_id, exhibitor_name) DO NOTHING
            """)
    int insertInvoice(Map<String, Object> p);

    /** 행사 청구서 번호 채번용 — 현재 최대 순번(YYYY 접두 무관 단순 시퀀스). */
    @Select("SELECT count(*) FROM invoice WHERE event_id = #{eventId}")
    int countInvoicesForSeq(@Param("eventId") String eventId);

    // ── F084 옥션 수수료 청구 연동 ────────────────────────────────────────────
    /** 옥션 수수료율(%) — master_data RATE.auctionFeeRate. 없으면 null(→ 기본 5%). */
    @Select("""
            SELECT (value_json #>> '{}')::numeric
            FROM master_data WHERE category = 'RATE' AND code = 'auctionFeeRate'
            """)
    Double findAuctionFeeRate();

    /** 멱등 판정 — 같은 낙찰(source_ref)로 이미 생성된 수수료 청구가 있는지. */
    @Select("SELECT count(*) FROM invoice WHERE source_ref = #{sourceRef}")
    int countInvoiceBySourceRef(@Param("sourceRef") String sourceRef);

    /**
     * 옥션 수수료 청구서 삽입(멱등) — category='auction_fee', source_ref=낙찰 id.
     * source_ref 부분 유니크 인덱스(uq_invoice_source_ref)로 중복 생성 원천 차단.
     */
    @Insert("""
            INSERT INTO invoice (id, event_id, exhibitor_name, invoice_no, category, source_ref,
                                 subtotal, vat, total, status, line_items, note, created_by)
            VALUES (#{id}, #{eventId}, #{exhibitorName}, #{invoiceNo}, 'auction_fee', #{sourceRef},
                    #{subtotal}, #{vat}, #{total}, 'pending', CAST(#{lineItems} AS jsonb), #{note}, #{createdBy})
            ON CONFLICT DO NOTHING
            """)
    int insertAuctionFeeInvoice(Map<String, Object> p);

    // ── F085 환불·취소 ────────────────────────────────────────────────────────
    /** 행사 개막일(YYYY-MM-DD) — 취소 시점 D-일 산정 원천. 없으면 null. */
    @Select("SELECT to_char(start_date, 'YYYY-MM-DD') FROM event WHERE id = #{eventId}")
    String findEventStartDate(@Param("eventId") String eventId);

    @Select("SELECT name FROM event WHERE id = #{eventId}")
    String findEventName(@Param("eventId") String eventId);

    /** 취소 전이 — pending/invoiced/overdue/paid → cancelled. 이미 취소면 미영향(멱등). */
    @Update("""
            UPDATE invoice
               SET status = 'cancelled', cancelled_at = now(), cancel_reason = #{reason}, updated_at = now()
             WHERE id = #{id} AND status <> 'cancelled'
            """)
    int cancelInvoice(@Param("id") String id, @Param("reason") String reason);

    @Insert("""
            INSERT INTO invoice_refund (id, invoice_id, event_id, base_amount, refund_rate_percent,
                                        refund_amount, forfeit_amount, days_before_event, bracket_label,
                                        reason, ruleset_version, created_by)
            VALUES (#{id}, #{invoiceId}, #{eventId}, #{baseAmount}, #{refundRatePercent},
                    #{refundAmount}, #{forfeitAmount}, #{daysBeforeEvent}, #{bracketLabel},
                    #{reason}, #{rulesetVersion}, #{createdBy})
            """)
    int insertRefund(Map<String, Object> p);

    // ── F083 세금계산서 ────────────────────────────────────────────────────────
    @Select("""
            SELECT id,
                   invoice_id      AS "invoiceId",
                   event_id        AS "eventId",
                   tax_invoice_no  AS "taxInvoiceNo",
                   supplier_name   AS "supplierName",
                   supplier_biz_no AS "supplierBizNo",
                   supplier_ceo    AS "supplierCeo",
                   supplier_addr   AS "supplierAddr",
                   buyer_name      AS "buyerName",
                   supply_amount   AS "supplyAmount",
                   vat, total,
                   item_summary    AS "itemSummary",
                   status,
                   to_char(issued_at AT TIME ZONE 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "issuedAt"
            FROM tax_invoice WHERE invoice_id = #{invoiceId}
            """)
    Map<String, Object> findTaxInvoiceByInvoice(@Param("invoiceId") String invoiceId);

    @Select("SELECT count(*) FROM tax_invoice WHERE event_id = #{eventId}")
    int countTaxInvoiceForSeq(@Param("eventId") String eventId);

    /** 세금계산서 발행(멱등) — 청구서당 1건(UNIQUE invoice_id). 재발행 시 미생성. */
    @Insert("""
            INSERT INTO tax_invoice (id, invoice_id, event_id, tax_invoice_no,
                                     supplier_name, supplier_biz_no, supplier_ceo, supplier_addr,
                                     buyer_name, supply_amount, vat, total, item_summary, issued_by)
            VALUES (#{id}, #{invoiceId}, #{eventId}, #{taxInvoiceNo},
                    #{supplierName}, #{supplierBizNo}, #{supplierCeo}, #{supplierAddr},
                    #{buyerName}, #{supplyAmount}, #{vat}, #{total}, #{itemSummary}, #{issuedBy})
            ON CONFLICT (invoice_id) DO NOTHING
            """)
    int insertTaxInvoice(Map<String, Object> p);

    // ── 정산 리포트 (취소분은 재무 합계에서 제외) ────────────────────────────────
    @Select("""
            SELECT COALESCE(sum(total), 0)                AS "billed",
                   COALESCE(sum(paid_amount), 0)          AS "collected",
                   COALESCE(sum(total - paid_amount), 0)  AS "outstanding",
                   count(*)                               AS "invoiceCount"
            FROM invoice WHERE event_id = #{eventId} AND status <> 'cancelled'
            """)
    Map<String, Object> reportTotals(@Param("eventId") String eventId);

    @Select("""
            SELECT COALESCE(category, 'rental')           AS "key",
                   count(*)                               AS "invoiceCount",
                   COALESCE(sum(total), 0)                AS "billed",
                   COALESCE(sum(paid_amount), 0)          AS "collected",
                   COALESCE(sum(total - paid_amount), 0)  AS "outstanding"
            FROM invoice WHERE event_id = #{eventId} AND status <> 'cancelled'
            GROUP BY COALESCE(category, 'rental')
            ORDER BY "billed" DESC
            """)
    List<Map<String, Object>> reportByCategory(@Param("eventId") String eventId);

    @Select("""
            SELECT status                                 AS "key",
                   count(*)                               AS "invoiceCount",
                   COALESCE(sum(total), 0)                AS "billed",
                   COALESCE(sum(paid_amount), 0)          AS "collected",
                   COALESCE(sum(total - paid_amount), 0)  AS "outstanding"
            FROM invoice WHERE event_id = #{eventId} AND status <> 'cancelled'
            GROUP BY status
            ORDER BY status
            """)
    List<Map<String, Object>> reportByStatus(@Param("eventId") String eventId);

    @Select("""
            SELECT invoice_no                 AS "invoiceNo",
                   exhibitor_name             AS "exhibitorName",
                   COALESCE(category,'rental') AS "category",
                   status                     AS "status",
                   subtotal, vat, total,
                   paid_amount                AS "paidAmount",
                   (total - paid_amount)      AS "outstanding",
                   to_char(due_date,'YYYY-MM-DD') AS "dueDate"
            FROM invoice WHERE event_id = #{eventId}
            ORDER BY invoice_no
            """)
    List<Map<String, Object>> reportRows(@Param("eventId") String eventId);
}
