-- V111: 환수채권 이월/미수금 관리 (Clawback Receivable Carry-forward)
--       P9 스펙 §2 전체 구현.
--       - clawback_receivable 테이블 (환수채권 마스터)
--       - clawback_recovery 테이블 (상계 회수 이력)
--       - settle_master 컬럼 3개 추가
--       - 메뉴 / 권한 / 공통코드 시드 (V95/V96 패턴 동일, ON CONFLICT DO NOTHING 멱등)

-- ============================================================
-- 1. clawback_receivable (환수채권 마스터)
-- ============================================================

CREATE TABLE clawback_receivable (
    receivable_id        BIGSERIAL                    PRIMARY KEY,
    agent_id             BIGINT          NOT NULL     REFERENCES agent(agent_id),
    origin_settle_month  CHAR(6)         NOT NULL,
    original_amount      NUMERIC(15,2)   NOT NULL,
    recovered_amount     NUMERIC(15,2)   NOT NULL     DEFAULT 0,
    balance              NUMERIC(15,2)   NOT NULL,
    status               VARCHAR(20)     NOT NULL     DEFAULT 'OUTSTANDING',
    last_recovery_month  CHAR(6),
    writeoff_reason      VARCHAR(300),
    written_off_at       TIMESTAMP,
    written_off_by       BIGINT,
    created_at           TIMESTAMP       NOT NULL     DEFAULT now(),
    created_by           BIGINT,
    updated_at           TIMESTAMP,
    CONSTRAINT uq_clawback_rcv_agent_month  UNIQUE (agent_id, origin_settle_month),
    CONSTRAINT chk_clawback_rcv_status      CHECK (status IN ('OUTSTANDING','RECOVERING','CLEARED','WRITTEN_OFF')),
    CONSTRAINT chk_clawback_rcv_orig_amt    CHECK (original_amount >= 0),
    CONSTRAINT chk_clawback_rcv_balance     CHECK (balance >= 0)
);

CREATE INDEX idx_clawback_rcv_agent_status  ON clawback_receivable (agent_id, status);
CREATE INDEX idx_clawback_rcv_status        ON clawback_receivable (status);
CREATE INDEX idx_clawback_rcv_origin_month  ON clawback_receivable (origin_settle_month);

-- ============================================================
-- 2. clawback_recovery (상계 회수 이력)
-- ============================================================

CREATE TABLE clawback_recovery (
    recovery_id       BIGSERIAL                   PRIMARY KEY,
    receivable_id     BIGINT        NOT NULL      REFERENCES clawback_receivable(receivable_id),
    settle_month      CHAR(6)       NOT NULL,
    recovered_amount  NUMERIC(15,2) NOT NULL,
    created_at        TIMESTAMP     NOT NULL      DEFAULT now()
);

CREATE INDEX idx_clawback_rec_receivable  ON clawback_recovery (receivable_id);
CREATE INDEX idx_clawback_rec_month       ON clawback_recovery (settle_month);

-- ============================================================
-- 3. settle_master 컬럼 3개 추가
-- ============================================================

ALTER TABLE settle_master
    ADD COLUMN clawback_recovered NUMERIC(15,2) NOT NULL DEFAULT 0,
    ADD COLUMN clawback_carryover NUMERIC(15,2) NOT NULL DEFAULT 0,
    ADD COLUMN payable_amount     NUMERIC(15,2) NOT NULL DEFAULT 0;

COMMENT ON COLUMN settle_master.clawback_recovered IS '당월 채권 상계 회수액';
COMMENT ON COLUMN settle_master.clawback_carryover IS '당월 발생→이월된 미수액';
COMMENT ON COLUMN settle_master.payable_amount     IS '실지급(이체대상) = max(0,net) − recovered';

-- ============================================================
-- 4. 메뉴 INSERT: CLAWBACK_RECEIVABLE (GRP_COMMISSION 하위 PAGE)
-- ============================================================

INSERT INTO menu (parent_menu_id, menu_code, menu_name, menu_type, menu_path, component_path, menu_level, sort_order)
SELECT p.menu_id, x.code, x.name, 'PAGE', x.path, x.cmpt, 2, x.sort
FROM menu p
JOIN (VALUES
    ('CLAWBACK_RECEIVABLE', '환수채권관리', '/commission/clawback-receivable', 'commission/ClawbackReceivable', 150)
) AS x(code, name, path, cmpt, sort)
ON p.menu_code = 'GRP_COMMISSION'
WHERE NOT EXISTS (SELECT 1 FROM menu WHERE menu_code = x.code);

-- ============================================================
-- 5. 메뉴 권한 항목 등록 (READ/UPDATE/EXPORT)
-- ============================================================

INSERT INTO menu_permission (menu_id, perm_code, perm_name, sort_order)
SELECT m.menu_id, p.code, p.name, p.sort
FROM menu m
CROSS JOIN (VALUES
    ('READ',   '조회', 10),
    ('UPDATE', '수정', 30),
    ('EXPORT', '엑셀', 60)
) AS p(code, name, sort)
WHERE m.menu_code = 'CLAWBACK_RECEIVABLE'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 6. 역할별 권한 부여
-- ============================================================

-- SUPER_ADMIN / ADMIN — 전체 권한 (READ/UPDATE/EXPORT)
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, mp.menu_id, mp.perm_code, 'Y'
FROM role r
CROSS JOIN menu_permission mp
JOIN menu m ON m.menu_id = mp.menu_id
WHERE r.role_code IN ('SUPER_ADMIN', 'ADMIN')
  AND m.menu_code = 'CLAWBACK_RECEIVABLE'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- MANAGER — READ 만
INSERT INTO role_menu_permission (role_id, menu_id, perm_code, is_granted)
SELECT r.role_id, mp.menu_id, mp.perm_code, 'Y'
FROM role r
CROSS JOIN menu_permission mp
JOIN menu m ON m.menu_id = mp.menu_id
WHERE r.role_code = 'MANAGER'
  AND mp.perm_code = 'READ'
  AND m.menu_code = 'CLAWBACK_RECEIVABLE'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 7. 공통코드: CLAWBACK_RCV_STATUS
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('CLAWBACK_RCV_STATUS', '환수채권상태', 'OUTSTANDING/RECOVERING/CLEARED/WRITTEN_OFF', 'Y', 440)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('CLAWBACK_RCV_STATUS', 'OUTSTANDING',  '미회수',   10),
    ('CLAWBACK_RCV_STATUS', 'RECOVERING',   '회수중',   20),
    ('CLAWBACK_RCV_STATUS', 'CLEARED',      '상환완료', 30),
    ('CLAWBACK_RCV_STATUS', 'WRITTEN_OFF',  '대손',     40)
ON CONFLICT (group_code, code) DO NOTHING;
