-- V27: 분급 계획 (installment_ratio, installment_plan, 공통코드)

-- ============================================================
-- 1. installment_ratio — 분급 비율 마스터 (개정 이력 추적)
-- ============================================================

CREATE TABLE installment_ratio (
    ratio_id            BIGSERIAL       PRIMARY KEY,
    product_category    VARCHAR(30),                            -- NULL = ALL 상품
    plan_year           INT             NOT NULL,               -- 1~7
    ratio_percent       NUMERIC(5,2)    NOT NULL,
    effective_from      DATE            NOT NULL,
    effective_to        DATE,                                   -- NULL = 현재 유효
    is_active           BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMP       NOT NULL DEFAULT NOW(),
    created_by          BIGINT,
    updated_at          TIMESTAMP,
    updated_by          BIGINT,
    UNIQUE (product_category, plan_year, effective_from)
);

CREATE INDEX idx_ir_category_year ON installment_ratio(product_category, plan_year, effective_from DESC);

-- ============================================================
-- 2. installment_plan — 계약별 분급 계획
-- ============================================================

CREATE TABLE installment_plan (
    plan_id             BIGSERIAL       PRIMARY KEY,
    contract_id         BIGINT          NOT NULL REFERENCES contract(contract_id),
    plan_year           INT             NOT NULL,               -- 1~7
    plan_month          INT             NOT NULL,               -- 1~12
    settle_month        CHAR(6),                                -- YYYYMM, 지급 예정월
    expected_amount     NUMERIC(15,2)   NOT NULL,
    paid_amount         NUMERIC(15,2)   NOT NULL DEFAULT 0,
    paid_at             TIMESTAMP,
    payment_id          BIGINT          REFERENCES payment(payment_id),
    status              VARCHAR(20)     NOT NULL DEFAULT 'SCHEDULED',
    created_at          TIMESTAMP       NOT NULL DEFAULT NOW(),
    created_by          BIGINT,
    updated_at          TIMESTAMP,
    updated_by          BIGINT,
    UNIQUE (contract_id, plan_year, plan_month)
);

CREATE INDEX idx_ip_contract    ON installment_plan(contract_id, plan_year, plan_month);
CREATE INDEX idx_ip_settle      ON installment_plan(settle_month, status);
CREATE INDEX idx_ip_payment     ON installment_plan(payment_id);

-- ============================================================
-- 3. 기본 분급 비율 데이터 (2024-01-01, NULL=ALL 상품)
-- ============================================================

INSERT INTO installment_ratio (product_category, plan_year, ratio_percent, effective_from) VALUES
    (NULL, 1, 35, '2024-01-01'),
    (NULL, 2, 25, '2024-01-01'),
    (NULL, 3, 15, '2024-01-01'),
    (NULL, 4, 10, '2024-01-01'),
    (NULL, 5,  7, '2024-01-01'),
    (NULL, 6,  5, '2024-01-01'),
    (NULL, 7,  3, '2024-01-01')
ON CONFLICT (product_category, plan_year, effective_from) DO NOTHING;

-- ============================================================
-- 4. 공통코드 INSTALLMENT_STATUS
-- ============================================================

INSERT INTO common_code_group (group_code, group_name, description, is_system, sort_order) VALUES
    ('INSTALLMENT_STATUS', '분급상태', 'SCHEDULED/PAID/DEFERRED/CANCELLED', 'Y', 280)
ON CONFLICT (group_code) DO NOTHING;

INSERT INTO common_code (group_code, code, code_name, code_desc, sort_order) VALUES
    ('INSTALLMENT_STATUS', 'SCHEDULED',  '지급예정', '분급 지급 예정 상태',         10),
    ('INSTALLMENT_STATUS', 'PAID',       '지급완료', '분급 지급 완료',               20),
    ('INSTALLMENT_STATUS', 'DEFERRED',   '이월',     '당월 미지급 — 다음 월로 이월', 30),
    ('INSTALLMENT_STATUS', 'CANCELLED',  '취소',     '분급 계획 취소',               40)
ON CONFLICT (group_code, code) DO NOTHING;
