-- V6: 정산 마스터 / 오버라이드 / 환수 / 지급

CREATE TABLE settle_master (
    settle_id         SERIAL PRIMARY KEY,
    agent_id          BIGINT         NOT NULL REFERENCES agent(agent_id),
    settle_month      CHAR(6)        NOT NULL,
    recruit_total     DECIMAL(15,2)  NOT NULL DEFAULT 0,
    maintain_total    DECIMAL(15,2)  NOT NULL DEFAULT 0,
    exception_plus    DECIMAL(15,2)  NOT NULL DEFAULT 0,
    exception_minus   DECIMAL(15,2)  NOT NULL DEFAULT 0,
    override_total    DECIMAL(15,2)  NOT NULL DEFAULT 0,
    chargeback_total  DECIMAL(15,2)  NOT NULL DEFAULT 0,
    gross_amount      DECIMAL(15,2)  NOT NULL DEFAULT 0,
    tax_amount        DECIMAL(15,2)  NOT NULL DEFAULT 0,
    net_amount        DECIMAL(15,2)  NOT NULL DEFAULT 0,
    status            VARCHAR(20)    NOT NULL DEFAULT 'CALCULATED',  -- CALCULATED / CONFIRMED / HOLD / PAID
    calc_date         TIMESTAMP,
    confirmed_by      BIGINT,
    confirmed_at      TIMESTAMP,
    hold_reason       VARCHAR(500),
    UNIQUE (agent_id, settle_month)
);
CREATE INDEX idx_sm_month ON settle_master(settle_month, status);

CREATE TABLE override_settle (
    os_id             SERIAL PRIMARY KEY,
    settle_id         BIGINT         NOT NULL REFERENCES settle_master(settle_id),
    upper_agent_id    BIGINT         NOT NULL REFERENCES agent(agent_id),
    lower_agent_id    BIGINT         NOT NULL REFERENCES agent(agent_id),
    settle_month      CHAR(6)        NOT NULL,
    base_amount       DECIMAL(15,2)  NOT NULL,
    override_pct      DECIMAL(7,4)   NOT NULL,
    override_amount   DECIMAL(15,2)  NOT NULL,
    calc_date         TIMESTAMP      NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_os_settle ON override_settle(settle_id);
CREATE INDEX idx_os_upper  ON override_settle(upper_agent_id, settle_month);

CREATE TABLE chargeback (
    cb_id             SERIAL PRIMARY KEY,
    contract_id       BIGINT         REFERENCES contract(contract_id),
    agent_id          BIGINT         NOT NULL REFERENCES agent(agent_id),
    policy_no         VARCHAR(50),
    lapse_month       INT,
    cb_rate           DECIMAL(7,4),
    cb_amount         DECIMAL(15,2)  NOT NULL,
    paid_amount       DECIMAL(15,2)  NOT NULL DEFAULT 0,
    remain_amount     DECIMAL(15,2)  NOT NULL,
    settle_month      CHAR(6)        NOT NULL,
    installment_no    INT,
    max_installments  INT,
    status            VARCHAR(20)    NOT NULL DEFAULT 'NEW',
    created_at        TIMESTAMP      NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_cb_agent ON chargeback(agent_id, settle_month);

CREATE TABLE payment (
    payment_id        SERIAL PRIMARY KEY,
    settle_id         BIGINT         NOT NULL REFERENCES settle_master(settle_id),
    agent_id          BIGINT         NOT NULL REFERENCES agent(agent_id),
    pay_amount        DECIMAL(15,2)  NOT NULL,
    bank_code         VARCHAR(10),
    account_no        VARCHAR(200),
    pay_date          DATE,
    pay_status        VARCHAR(20)    NOT NULL DEFAULT 'PENDING',  -- PENDING / SENT / DONE / FAIL
    transfer_file_id  BIGINT,
    fail_reason       VARCHAR(500),
    created_at        TIMESTAMP      NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP
);
CREATE INDEX idx_pay_status ON payment(pay_status, created_at);
CREATE INDEX idx_pay_agent  ON payment(agent_id, pay_date);
