-- V120: MOD-5 정산대사 입수 (carrier reconciliation IN)
--       carrier_reconciliation_in / reconciliation_variance 테이블
--       메뉴 CARRIER_RECON_IN (GRP_SETTLE 하위)
--       공통코드 RECON_IN_STATUS
--       insurance_company PK: company_id (SERIAL, V2 확인)

-- ============================================================
-- 1. carrier_reconciliation_in (보험사 정산대사 입수 마스터)
-- ============================================================

CREATE TABLE carrier_reconciliation_in (
    recon_id         BIGSERIAL      PRIMARY KEY,
    company_id       BIGINT         NOT NULL REFERENCES insurance_company(company_id),
    settle_month     CHAR(6)        NOT NULL,
    file_name        VARCHAR(200),
    record_count     INT            NOT NULL DEFAULT 0,
    reported_total   NUMERIC(15,2)  NOT NULL DEFAULT 0,
    system_total     NUMERIC(15,2)  NOT NULL DEFAULT 0,
    variance_amount  NUMERIC(15,2)  NOT NULL DEFAULT 0,
    status           VARCHAR(20)    NOT NULL DEFAULT 'RECEIVED',
    created_at       TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by       BIGINT,
    updated_at       TIMESTAMP,
    CONSTRAINT chk_cri_status  CHECK (status IN ('RECEIVED','MATCHED','VARIANCE','APPROVED')),
    CONSTRAINT uq_cri_company_month UNIQUE (company_id, settle_month)
);

COMMENT ON TABLE  carrier_reconciliation_in IS '보험사 정산대사 입수 마스터';
COMMENT ON COLUMN carrier_reconciliation_in.recon_id        IS 'PK';
COMMENT ON COLUMN carrier_reconciliation_in.company_id      IS '보험사 ID';
COMMENT ON COLUMN carrier_reconciliation_in.settle_month    IS '정산월 YYYYMM';
COMMENT ON COLUMN carrier_reconciliation_in.file_name       IS '입수 파일명';
COMMENT ON COLUMN carrier_reconciliation_in.record_count    IS '보험사 통보 건수';
COMMENT ON COLUMN carrier_reconciliation_in.reported_total  IS '보험사 통보 금액 합계';
COMMENT ON COLUMN carrier_reconciliation_in.system_total    IS 'GA 시스템 집계 금액';
COMMENT ON COLUMN carrier_reconciliation_in.variance_amount IS '차이 금액 (reported - system)';
COMMENT ON COLUMN carrier_reconciliation_in.status          IS '상태: RECEIVED/MATCHED/VARIANCE/APPROVED';

CREATE INDEX idx_cri_settle_month ON carrier_reconciliation_in(settle_month);
CREATE INDEX idx_cri_company_month ON carrier_reconciliation_in(company_id, settle_month);

-- ============================================================
-- 2. reconciliation_variance (대사 차이 상세)
-- ============================================================

CREATE TABLE reconciliation_variance (
    variance_id      BIGSERIAL      PRIMARY KEY,
    recon_id         BIGINT         NOT NULL REFERENCES carrier_reconciliation_in(recon_id),
    policy_no        VARCHAR(50),
    expected_amount  NUMERIC(15,2),
    reported_amount  NUMERIC(15,2),
    variance_reason  VARCHAR(100),
    created_at       TIMESTAMP      NOT NULL DEFAULT NOW(),
    created_by       BIGINT
);

COMMENT ON TABLE  reconciliation_variance IS '대사 차이 상세';
COMMENT ON COLUMN reconciliation_variance.variance_id     IS 'PK';
COMMENT ON COLUMN reconciliation_variance.recon_id        IS '대사 입수 ID';
COMMENT ON COLUMN reconciliation_variance.policy_no       IS '증권번호';
COMMENT ON COLUMN reconciliation_variance.expected_amount IS 'GA 시스템 예상 금액';
COMMENT ON COLUMN reconciliation_variance.reported_amount IS '보험사 통보 금액';
COMMENT ON COLUMN reconciliation_variance.variance_reason IS '차이 사유';

CREATE INDEX idx_rv_recon_id ON reconciliation_variance(recon_id);

-- ============================================================
-- 3. 메뉴 INSERT: CARRIER_RECON_IN (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
    ('CARRIER_RECON_IN', '정산대사입수', '/settle/carrier-recon-in', 'settle/CarrierReconIn', 140)
) 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 = 'CARRIER_RECON_IN'
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 = 'CARRIER_RECON_IN'
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 = 'CARRIER_RECON_IN'
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 = 'CARRIER_RECON_IN'
ON CONFLICT (role_id, menu_id, perm_code) DO NOTHING;

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

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('RECON_IN_STATUS', '정산대사상태', 'RECEIVED=입수/MATCHED=일치/VARIANCE=차이/APPROVED=승인', 'Y', 500)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, sort_order) VALUES
    ('RECON_IN_STATUS', 'RECEIVED',  '입수', 10),
    ('RECON_IN_STATUS', 'MATCHED',   '일치', 20),
    ('RECON_IN_STATUS', 'VARIANCE',  '차이', 30),
    ('RECON_IN_STATUS', 'APPROVED',  '승인', 40)
ON CONFLICT (group_code, code) DO NOTHING;
