-- V117: MOD-2 선지급/차익정산 (advance payment recovery + delta settlement)
--       advance_payment / advance_recovery_ledger 테이블
--       메뉴 ADVANCE_PAYMENT (GRP_SETTLE 하위)
--       공통코드 ADVANCE_STATUS

-- ============================================================
-- 1. advance_payment (선지급금 마스터)
-- ============================================================

CREATE TABLE advance_payment (
    advance_id        BIGSERIAL      PRIMARY KEY,
    agent_id          BIGINT         NOT NULL REFERENCES agent(agent_id),
    advance_month     CHAR(6)        NOT NULL,
    principal_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',
    memo              VARCHAR(200),
    created_at        TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by        BIGINT,
    updated_at        TIMESTAMP,
    CONSTRAINT chk_adv_principal_pos CHECK (principal_amount > 0),
    CONSTRAINT chk_adv_balance_nn    CHECK (balance >= 0),
    CONSTRAINT chk_adv_status        CHECK (status IN ('OUTSTANDING','PARTIAL','CLEARED','WAIVED'))
);

COMMENT ON TABLE  advance_payment IS '선지급금 마스터';
COMMENT ON COLUMN advance_payment.advance_id       IS 'PK';
COMMENT ON COLUMN advance_payment.agent_id         IS '설계사 ID';
COMMENT ON COLUMN advance_payment.advance_month    IS '선지급 발생월 YYYYMM';
COMMENT ON COLUMN advance_payment.principal_amount IS '원금';
COMMENT ON COLUMN advance_payment.recovered_amount IS '회수 누계';
COMMENT ON COLUMN advance_payment.balance          IS '잔액 (principal - recovered)';
COMMENT ON COLUMN advance_payment.status           IS '상태: OUTSTANDING/PARTIAL/CLEARED/WAIVED';
COMMENT ON COLUMN advance_payment.memo             IS '메모';

CREATE INDEX idx_adv_agent_status ON advance_payment(agent_id, status);
CREATE INDEX idx_adv_month        ON advance_payment(advance_month);

-- ============================================================
-- 2. advance_recovery_ledger (선지급 상계 이력)
-- ============================================================

CREATE TABLE advance_recovery_ledger (
    recovery_id      BIGSERIAL      PRIMARY KEY,
    advance_id       BIGINT         NOT NULL REFERENCES advance_payment(advance_id),
    settle_month     CHAR(6)        NOT NULL,
    recovery_amount  NUMERIC(15,2)  NOT NULL,
    created_at       TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by       BIGINT,
    CONSTRAINT chk_arl_recovery_pos CHECK (recovery_amount > 0)
);

COMMENT ON TABLE  advance_recovery_ledger IS '선지급 상계 이력';
COMMENT ON COLUMN advance_recovery_ledger.recovery_id     IS 'PK';
COMMENT ON COLUMN advance_recovery_ledger.advance_id      IS '선지급금 ID';
COMMENT ON COLUMN advance_recovery_ledger.settle_month    IS '상계 정산월 YYYYMM';
COMMENT ON COLUMN advance_recovery_ledger.recovery_amount IS '당월 상계 금액';

CREATE INDEX idx_arl_advance     ON advance_recovery_ledger(advance_id);
CREATE INDEX idx_arl_settle_month ON advance_recovery_ledger(settle_month);

-- ============================================================
-- 3. 메뉴 INSERT: ADVANCE_PAYMENT (GRP_SETTLE 하위 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
    ('ADVANCE_PAYMENT', '선지급정산', '/settle/advance-payment', 'settle/AdvancePayment', 130)
) AS x(code, name, path, cmpt, sort)
ON p.menu_code = 'GRP_SETTLE'
WHERE NOT EXISTS (SELECT 1 FROM menu WHERE menu_code = x.code);

-- ============================================================
-- 4. 메뉴 권한 항목 등록 (READ/CREATE/UPDATE/DELETE/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),
    ('CREATE', '등록', 20),
    ('UPDATE', '수정', 30),
    ('DELETE', '삭제', 40),
    ('EXPORT', '엑셀', 60)
) AS p(code, name, sort)
WHERE m.menu_code = 'ADVANCE_PAYMENT'
ON CONFLICT (menu_id, perm_code) DO NOTHING;

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

-- SUPER_ADMIN / ADMIN — 전체 권한
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 = 'ADVANCE_PAYMENT'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- MANAGER — READ/CREATE/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 = 'MANAGER'
  AND mp.perm_code IN ('READ','CREATE','UPDATE','EXPORT')
  AND m.menu_code = 'ADVANCE_PAYMENT'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- AGENT — 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 = 'AGENT'
  AND mp.perm_code = 'READ'
  AND m.menu_code = 'ADVANCE_PAYMENT'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

-- ============================================================
-- 6. 공통코드: ADVANCE_STATUS
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('ADVANCE_STATUS', '선지급상태', 'OUTSTANDING=미상환/PARTIAL=부분/CLEARED=완료/WAIVED=면제', 'Y', 470)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('ADVANCE_STATUS', 'OUTSTANDING', '미상환',   10),
    ('ADVANCE_STATUS', 'PARTIAL',     '부분상환', 20),
    ('ADVANCE_STATUS', 'CLEARED',     '상환완료', 30),
    ('ADVANCE_STATUS', 'WAIVED',      '면제',     40)
ON CONFLICT (group_code, code) DO NOTHING;
