-- V80: 정산/지급 영역 데이터 구성
--
-- MONTHLY_SETTLEMENT 배치(2026-02~04)로 settle_master / 원장 / 명세서 / 분개는 적재 완료.
-- payment(지급) 는 settle_master 에서 파생 생성하고, 배치 파이프라인이 없는
-- 운영성 도메인(공제/마감/세금계산서/이의신청/시책/정정)은 대표 샘플을 시드한다.
-- 모든 블록은 멱등 (NOT EXISTS 가드).

-- ============================================================
-- 1. payment — settle_master 파생 (정산 1건 → 지급 1건)
--    202602 = DONE(지급완료), 202603/202604 = PENDING(지급대기)
-- ============================================================
INSERT INTO payment (settle_id, agent_id, pay_amount, bank_code, account_no,
                     pay_status, pay_date, income_tax_amount, local_tax_amount,
                     vat_amount, deduction_amount, net_amount, created_at)
SELECT sm.settle_id, sm.agent_id, sm.gross_amount, a.bank_code, a.account_no,
       CASE WHEN sm.settle_month = '202602' THEN 'DONE' ELSE 'PENDING' END,
       CASE WHEN sm.settle_month = '202602' THEN DATE '2026-03-10' END,
       ROUND(sm.tax_amount * 0.909, 0),          -- 소득세분 (지방세 제외 근사)
       ROUND(sm.tax_amount * 0.091, 0),          -- 지방소득세분
       0, 0, sm.net_amount, now()
FROM settle_master sm
JOIN agent a ON a.agent_id = sm.agent_id
WHERE NOT EXISTS (SELECT 1 FROM payment p WHERE p.settle_id = sm.settle_id);

-- ============================================================
-- 2. agent_deduction — 공제 샘플 (settle_master 설계사 기준 12건)
-- ============================================================
INSERT INTO agent_deduction (agent_id, settle_month, deduction_type, amount, description, status, created_by)
SELECT r.agent_id, '202604', t.dtype, t.amt, t.descr, t.st, 1
FROM (
    SELECT agent_id, row_number() OVER (ORDER BY agent_id) AS rn
    FROM settle_master WHERE settle_month = '202604'
) r
JOIN (VALUES
    (1,'LOAN',     800000, '단기 대출 상환분',      'APPLIED'),
    (2,'ADVANCE',  500000, '선지급 수수료 환수',    'APPLIED'),
    (3,'GUARANTEE',300000, '영업보증금 분할 납입',  'PENDING'),
    (4,'DUES',     50000,  '협회비',                'APPLIED'),
    (5,'AD',       120000, '공동 광고비 분담',      'PENDING'),
    (6,'CHARGEBACK_PENDING', 650000, '환수 보류분',  'PENDING'),
    (7,'LOAN',     400000, '대출 상환분',           'APPLIED'),
    (8,'OTHER',    90000,  '기타 공제',             'PENDING'),
    (9,'ADVANCE',  350000, '선지급 환수',           'APPLIED'),
    (10,'DUES',    50000,  '협회비',                'APPLIED'),
    (11,'GUARANTEE',300000,'보증금 납입',           'PENDING'),
    (12,'LOAN',    220000, '대출 상환분',           'APPLIED')
) AS t(rn, dtype, amt, descr, st) ON t.rn = r.rn
WHERE NOT EXISTS (SELECT 1 FROM agent_deduction);

-- ============================================================
-- 3. period_close — 마감 (2026-01 ~ 2026-03 마감 완료)
-- ============================================================
INSERT INTO period_close (close_month, closed_at, closed_by, note)
SELECT m, ts, 1, note FROM (VALUES
    ('202601', TIMESTAMP '2026-02-05 18:00:00', '2026년 1월 정산 마감'),
    ('202602', TIMESTAMP '2026-03-05 18:00:00', '2026년 2월 정산 마감'),
    ('202603', TIMESTAMP '2026-04-06 18:00:00', '2026년 3월 정산 마감')
) AS v(m, ts, note)
WHERE NOT EXISTS (SELECT 1 FROM period_close);

-- ============================================================
-- 4. tax_invoice — 세금계산서 (보험사 대상 매출 5건)
-- ============================================================
INSERT INTO tax_invoice (invoice_no, invoice_type, issue_date, supply_amount, tax_amount,
                         total_amount, counterpart_name, counterpart_biz_no, counterpart_rep,
                         settle_month, description, issued_by, created_at)
SELECT v.no, 'SUPPLY', v.dt, v.supply, ROUND(v.supply*0.1,0), v.supply+ROUND(v.supply*0.1,0),
       v.name, v.bizno, v.rep, v.sm, v.descr, 1, now()
FROM (VALUES
    ('2026-S-0001', DATE '2026-03-10', 11968527::numeric, '삼성생명',  '124-81-00001', '홍길동', '202602', '2026년 2월 수수료 세금계산서'),
    ('2026-S-0002', DATE '2026-04-10', 16019916::numeric, '한화생명',  '124-81-00002', '김영수', '202603', '2026년 3월 수수료 세금계산서'),
    ('2026-S-0003', DATE '2026-05-10', 18944495::numeric, '교보생명',  '124-81-00003', '이철수', '202604', '2026년 4월 수수료 세금계산서'),
    ('2026-S-0004', DATE '2026-04-10', 4200000::numeric,  '메리츠화재','124-81-00004', '박민지', '202603', '2026년 3월 손보 수수료'),
    ('2026-S-0005', DATE '2026-05-10', 3850000::numeric,  'DB손해보험','124-81-00005', '최지훈', '202604', '2026년 4월 손보 수수료')
) AS v(no, dt, supply, name, bizno, rep, sm, descr)
WHERE NOT EXISTS (SELECT 1 FROM tax_invoice);

-- ============================================================
-- 5. commission_dispute — 수수료 이의신청 (6건)
-- ============================================================
INSERT INTO commission_dispute (agent_id, settle_month, settle_id, status,
                                disputed_amount, claim_amount, reason, created_at)
SELECT r.agent_id, r.settle_month, r.settle_id, t.st, t.damt, t.camt, t.reason, now()
FROM (
    SELECT settle_id, agent_id, settle_month,
           row_number() OVER (ORDER BY settle_id) AS rn
    FROM settle_master WHERE settle_month = '202604'
) r
JOIN (VALUES
    (1,'OPEN',      300000, 300000, '모집수수료 누락 — 3월 신계약 2건 미반영 주장'),
    (2,'IN_REVIEW', 150000, 150000, '유지수수료 요율 오적용 이의'),
    (3,'OPEN',      500000, 450000, '환수 금액 과다 산정 이의'),
    (4,'APPROVED',  220000, 220000, '오버라이드 누락분 — 검토 후 인정'),
    (5,'REJECTED',  100000, 0,      '예외 차감 이의 — 근거 부족으로 기각'),
    (6,'IN_REVIEW', 280000, 280000, '세금 공제액 과다 이의')
) AS t(rn, st, damt, camt, reason) ON t.rn = r.rn
WHERE NOT EXISTS (SELECT 1 FROM commission_dispute);

-- ============================================================
-- 6. incentive_program — 시책 프로그램 (4건)
-- ============================================================
INSERT INTO incentive_program (program_name, program_type, target_type, start_month, end_month,
                               condition_desc, pay_rate, pay_amount_fixed, pay_type,
                               budget_amount, is_active, created_by, created_at)
SELECT v.name, v.ptype, 'AGENT', v.sm, v.em, v.cond, v.rate, v.fixed, v.paytype,
       v.budget, v.active, 1, now()
FROM (VALUES
    ('2026 상반기 신인 모집왕', 'RECRUIT_BONUS', '202601','202606','월 신계약 5건 이상 시 모집수수료의 추가 지급', 0.10::numeric, NULL::numeric, 'RATE', 50000000::numeric, TRUE),
    ('2분기 유지율 우수상',     'MAINTAIN_BONUS','202604','202606','13M 유지율 90% 이상 달성',                NULL::numeric, 1000000::numeric, 'FIXED_AMOUNT', 30000000::numeric, TRUE),
    ('봄맞이 특별 콘테스트',    'CONTEST',       '202603','202605','기간 내 보장성 신계약 환산 300%P 달성',     NULL::numeric, 2000000::numeric, 'FIXED_AMOUNT', 40000000::numeric, TRUE),
    ('2025 하반기 정착 지원',   'SPECIAL',       '202507','202512','신인 정착 지원금 (종료)',                  NULL::numeric, 500000::numeric,  'FIXED_AMOUNT', 20000000::numeric, FALSE)
) AS v(name, ptype, sm, em, cond, rate, fixed, paytype, budget, active)
WHERE NOT EXISTS (SELECT 1 FROM incentive_program);

-- ============================================================
-- 7. settle_correction — 정산 정정 (4건, 실제 settle_master 참조)
-- ============================================================
INSERT INTO settle_correction (original_settle_id, agent_id, settle_month, diff_amount,
                               before_amount, after_amount, reason_code, reason_detail,
                               status, requested_by, requested_at)
SELECT r.settle_id, r.agent_id, r.settle_month, t.diff,
       r.net_amount, r.net_amount + t.diff, t.rcode, t.detail, t.st, 1, now()
FROM (
    SELECT settle_id, agent_id, settle_month, net_amount,
           row_number() OVER (ORDER BY settle_id) AS rn
    FROM settle_master WHERE settle_month = '202603'
) r
JOIN (VALUES
    (1, 200000::numeric,  'DATA_ERROR',   '신계약 데이터 누락 — 모집수수료 추가 반영',  'APPROVED'),
    (2, -150000::numeric, 'POLICY_CHANGE','요율 정책 변경 소급 적용',                   'APPROVED'),
    (3, 80000::numeric,   'LATE_DATA',    '지연 수신 데이터 반영',                      'DRAFT'),
    (4, -50000::numeric,  'MANUAL_ADJ',   '수기 조정 — 중복 지급분 회수',               'DRAFT')
) AS t(rn, diff, rcode, detail, st) ON t.rn = r.rn
WHERE NOT EXISTS (SELECT 1 FROM settle_correction);
