-- V5: 원장 관리 (모집수수료 / 유지수수료 / 예외금액)

CREATE TABLE recruit_ledger (
    ledger_id         SERIAL PRIMARY KEY,
    contract_id       BIGINT        REFERENCES contract(contract_id),
    agent_id          BIGINT        NOT NULL REFERENCES agent(agent_id),
    policy_no         VARCHAR(50),
    company_code      VARCHAR(20),
    product_code      VARCHAR(50),
    insurance_type    VARCHAR(30),
    commission_year   INT,
    premium           DECIMAL(15,2),
    pay_method        VARCHAR(20),
    company_rate      DECIMAL(7,4),
    company_amount    DECIMAL(15,2),
    payout_rate       DECIMAL(7,4),
    agent_amount      DECIMAL(15,2),
    tax_amount        DECIMAL(15,2),
    reconcile_status  VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
    reconcile_diff    DECIMAL(15,2),
    advance_yn        CHAR(1)       NOT NULL DEFAULT 'N',
    advance_amount    DECIMAL(15,2),
    chargeback_yn     CHAR(1)       NOT NULL DEFAULT 'N',
    settle_month      CHAR(6)       NOT NULL,
    status            VARCHAR(20)   NOT NULL DEFAULT 'CALCULATED',
    version           INT           NOT NULL DEFAULT 1,
    created_at        TIMESTAMP     NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP
);
CREATE INDEX idx_rl_settle ON recruit_ledger(settle_month, agent_id);
CREATE INDEX idx_rl_policy ON recruit_ledger(policy_no);

CREATE TABLE maintain_ledger (
    ledger_id         SERIAL PRIMARY KEY,
    contract_id       BIGINT        REFERENCES contract(contract_id),
    agent_id          BIGINT        NOT NULL REFERENCES agent(agent_id),
    policy_no         VARCHAR(50),
    company_code      VARCHAR(20),
    product_code      VARCHAR(50),
    insurance_type    VARCHAR(30),
    commission_year   INT,
    premium           DECIMAL(15,2),
    pay_method        VARCHAR(20),
    company_rate      DECIMAL(7,4),
    company_amount    DECIMAL(15,2),
    payout_rate       DECIMAL(7,4),
    agent_amount      DECIMAL(15,2),
    tax_amount        DECIMAL(15,2),
    reconcile_status  VARCHAR(20)   NOT NULL DEFAULT 'PENDING',
    reconcile_diff    DECIMAL(15,2),
    advance_yn        CHAR(1)       NOT NULL DEFAULT 'N',
    advance_amount    DECIMAL(15,2),
    chargeback_yn     CHAR(1)       NOT NULL DEFAULT 'N',
    settle_month      CHAR(6)       NOT NULL,
    status            VARCHAR(20)   NOT NULL DEFAULT 'CALCULATED',
    version           INT           NOT NULL DEFAULT 1,
    created_at        TIMESTAMP     NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP
);
CREATE INDEX idx_ml_settle ON maintain_ledger(settle_month, agent_id);
CREATE INDEX idx_ml_policy ON maintain_ledger(policy_no);

CREATE TABLE exception_ledger (
    ledger_id         SERIAL PRIMARY KEY,
    contract_id       BIGINT        REFERENCES contract(contract_id),
    agent_id          BIGINT        NOT NULL REFERENCES agent(agent_id),
    exception_code    VARCHAR(20)   NOT NULL REFERENCES exception_type_code(exception_code),
    policy_no         VARCHAR(50),
    company_code      VARCHAR(20),
    insurance_type    VARCHAR(30),
    commission_year   INT,
    amount            DECIMAL(15,2) NOT NULL,
    tax_amount        DECIMAL(15,2),
    settle_month      CHAR(6)       NOT NULL,
    description       VARCHAR(500),
    approve_status    VARCHAR(20)   NOT NULL DEFAULT 'PENDING',  -- PENDING / APPROVED / REJECTED
    approver_id       BIGINT,
    approve_date      TIMESTAMP,
    recurring_yn      CHAR(1)       NOT NULL DEFAULT 'N',
    recurring_months  INT,
    recurring_left    INT,
    status            VARCHAR(20)   NOT NULL DEFAULT 'NEW',
    version           INT           NOT NULL DEFAULT 1,
    created_at        TIMESTAMP     NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP
);
CREATE INDEX idx_el_settle ON exception_ledger(settle_month, agent_id);
CREATE INDEX idx_el_status ON exception_ledger(approve_status);
