-- V101: 보증보험 (agent_guarantee_insurance)
--       설계사가 가입하는 신원보증보험 가입현황 추적(관리 목적, 공식 없음).

CREATE TABLE agent_guarantee_insurance (
    guarantee_id      BIGSERIAL      PRIMARY KEY,
    agent_id          INT            NOT NULL REFERENCES agent(agent_id),
    policy_no         VARCHAR(50)    NOT NULL,
    carrier_id        INT            REFERENCES insurance_company(company_id),
    guarantee_amount  NUMERIC(15,2)  NOT NULL,
    start_date        DATE           NOT NULL,
    end_date          DATE           NOT NULL,
    status            VARCHAR(20)    NOT NULL DEFAULT 'ACTIVE',
    created_at        TIMESTAMP      NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMP,
    CHECK (status IN ('ACTIVE','EXPIRED','CANCELLED'))
);

COMMENT ON TABLE  agent_guarantee_insurance                IS '보증보험 — 설계사 신원보증보험 가입현황';
COMMENT ON COLUMN agent_guarantee_insurance.agent_id        IS '설계사 FK';
COMMENT ON COLUMN agent_guarantee_insurance.policy_no       IS '증권번호';
COMMENT ON COLUMN agent_guarantee_insurance.carrier_id      IS '보증보험사 FK(insurance_company)';
COMMENT ON COLUMN agent_guarantee_insurance.guarantee_amount IS '보증금액';
COMMENT ON COLUMN agent_guarantee_insurance.start_date       IS '보증 시작일';
COMMENT ON COLUMN agent_guarantee_insurance.end_date         IS '보증 종료일';
COMMENT ON COLUMN agent_guarantee_insurance.status           IS '상태 (ACTIVE=유효/EXPIRED=만료/CANCELLED=해지)';

CREATE INDEX idx_agi_agent_status ON agent_guarantee_insurance(agent_id, status);
CREATE INDEX idx_agi_end_date     ON agent_guarantee_insurance(end_date);
