-- 킨텍스 — M9 정산·결제 스키마 + 시드 (멱등)
-- PLANNING §5 M9: 행사×참가업체 청구서(invoice) 집계·상태 전이(pending→invoiced→paid/overdue).
--   임대료 = booth ST_Area × 요율(master_data RATE.exhibitionHallPerM2, rates-v1.0) + 유틸리티(utility_order).
--   결제는 PG 위임 고지 패턴 — 실 PG 연동 없음. 수납은 수동 기록(invoice_payment) 누적 → 상태 파생.
-- 보안 불변: 본 마이그레이션은 V24 단독. V1~V23 및 타 팀 마이그레이션을 변경하지 않는다.

-- 청구서(Invoice) — 행사×참가업체 1행. line_items 는 산출 스냅샷(jsonb) ------------
CREATE TABLE IF NOT EXISTS invoice (
    id             varchar(40)  PRIMARY KEY,
    event_id       varchar(40)  NOT NULL REFERENCES event(id) ON DELETE CASCADE,
    exhibitor_id   varchar(40),                              -- company 소프트 참조(선택)
    exhibitor_name varchar(200) NOT NULL,                    -- 표시용 참가업체명(민감정보 아님)
    invoice_no     varchar(40)  NOT NULL UNIQUE,
    subtotal       bigint       NOT NULL DEFAULT 0,          -- 공급가액(임대+유틸+기타)
    vat            bigint       NOT NULL DEFAULT 0,          -- 부가세(10%)
    total          bigint       NOT NULL DEFAULT 0,          -- 청구 총액 = subtotal + vat
    paid_amount    bigint       NOT NULL DEFAULT 0,          -- 누적 수납액(= Σ invoice_payment.amount)
    status         varchar(20)  NOT NULL DEFAULT 'pending',  -- pending|invoiced|paid|overdue
    due_date       date,
    issued_at      timestamptz,
    paid_at        timestamptz,
    line_items     jsonb,                                    -- [{category,description,quantity,unitPrice,amount}]
    note           text,
    created_by     varchar(40),
    created_at     timestamptz  NOT NULL DEFAULT now(),
    updated_at     timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (event_id, exhibitor_name)                        -- 생성 멱등 키(참가업체당 1청구서)
);
CREATE INDEX IF NOT EXISTS idx_invoice_event  ON invoice(event_id);
CREATE INDEX IF NOT EXISTS idx_invoice_status ON invoice(event_id, status);

-- 수납 기록(Payment) — 계약금/중도금/잔금 등 분납 이력. 매니저 수동 기록 -------------
CREATE TABLE IF NOT EXISTS invoice_payment (
    id          varchar(40)  PRIMARY KEY,
    invoice_id  varchar(40)  NOT NULL REFERENCES invoice(id) ON DELETE CASCADE,
    amount      bigint       NOT NULL,
    method      varchar(30)  NOT NULL DEFAULT 'manual',      -- manual|transfer|card (PG 위임 고지)
    memo        varchar(200),
    recorded_by varchar(40),
    recorded_at timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_invoice_payment_invoice ON invoice_payment(invoice_id);

-- ===== 시드(멱등) — 기존 event(e-2026-smf) 참조. 데모 참가업체 4사 청구서 ==========
-- 상태 분포 시연: paid(완납)·invoiced(부분수납)·overdue(연체)·pending(미발행).
INSERT INTO invoice (id, event_id, exhibitor_id, exhibitor_name, invoice_no,
                     subtotal, vat, total, paid_amount, status, due_date, issued_at, paid_at,
                     line_items, note, created_by) VALUES
  ('inv-smf-0001', 'e-2026-smf', NULL, '(주)스마트오토메이션', 'SMF-2026-0001',
     18000000, 1800000, 19800000, 19800000, 'paid',
     DATE '2026-07-25', now() - interval '20 day', now() - interval '5 day',
     '[{"category":"RENTAL","description":"부스 임대료 (108㎡ · A-101)","quantity":108,"unitPrice":145000,"amount":15660000},{"category":"UTILITY","description":"전기·네트워크 유틸리티","quantity":1,"unitPrice":2340000,"amount":2340000}]'::jsonb,
     '완납 처리(계약금+중도금+잔금).', 'system-seed'),
  ('inv-smf-0002', 'e-2026-smf', NULL, '델타로보틱스', 'SMF-2026-0002',
     9000000, 900000, 9900000, 3960000, 'invoiced',
     DATE '2026-07-25', now() - interval '18 day', NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (72㎡ · B-204)","quantity":72,"unitPrice":112500,"amount":8100000},{"category":"UTILITY","description":"급배수 유틸리티","quantity":1,"unitPrice":900000,"amount":900000}]'::jsonb,
     '계약금 40% 수납.', 'system-seed'),
  ('inv-smf-0003', 'e-2026-smf', NULL, '한국센서테크', 'SMF-2026-0003',
     5400000, 540000, 5940000, 0, 'overdue',
     DATE '2026-07-10', now() - interval '30 day', NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (54㎡ · C-310)","quantity":54,"unitPrice":100000,"amount":5400000}]'::jsonb,
     '납부 기한 경과 — 리마인더 발송 대상.', 'system-seed'),
  ('inv-smf-0004', 'e-2026-smf', NULL, '비전인더스트리', 'SMF-2026-0004',
     13500000, 1350000, 14850000, 0, 'pending',
     DATE '2026-08-01', NULL, NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (144㎡ · D-402)","quantity":144,"unitPrice":93750,"amount":13500000}]'::jsonb,
     '발행 대기(초안).', 'system-seed')
ON CONFLICT (event_id, exhibitor_name) DO NOTHING;

INSERT INTO invoice_payment (id, invoice_id, amount, method, memo, recorded_by, recorded_at) VALUES
  ('pay-smf-0001-1', 'inv-smf-0001', 3960000,  'transfer', '계약금 20%', 'system-seed', now() - interval '18 day'),
  ('pay-smf-0001-2', 'inv-smf-0001', 9900000,  'transfer', '중도금 50%', 'system-seed', now() - interval '10 day'),
  ('pay-smf-0001-3', 'inv-smf-0001', 5940000,  'transfer', '잔금 30%',   'system-seed', now() - interval '5 day'),
  ('pay-smf-0002-1', 'inv-smf-0002', 3960000,  'transfer', '계약금 40%', 'system-seed', now() - interval '8 day')
ON CONFLICT (id) DO NOTHING;
