-- V2: 보험사 / 상품 / 계약

CREATE TABLE insurance_company (
    company_id     SERIAL PRIMARY KEY,
    company_code   VARCHAR(20)  NOT NULL UNIQUE,
    company_name   VARCHAR(100) NOT NULL,
    company_type   VARCHAR(20),                     -- LIFE / NONLIFE / GA
    biz_no         VARCHAR(20),
    contact_name   VARCHAR(50),
    contact_phone  VARCHAR(20),
    contact_email  VARCHAR(100),
    is_active      CHAR(1)      NOT NULL DEFAULT 'Y'
);
COMMENT ON TABLE insurance_company IS '보험사';

CREATE TABLE product (
    product_id      SERIAL PRIMARY KEY,
    company_id      BIGINT       NOT NULL REFERENCES insurance_company(company_id),
    product_code    VARCHAR(50)  NOT NULL,
    product_name    VARCHAR(200) NOT NULL,
    insurance_type  VARCHAR(30)  NOT NULL,          -- WHOLE_LIFE / TERM / SAVINGS / HEALTH ...
    product_group   VARCHAR(30),
    pay_period_type VARCHAR(20),                    -- MONTHLY / QUARTERLY / ANNUAL / LUMP
    is_active       CHAR(1)      NOT NULL DEFAULT 'Y',
    launch_date     DATE,
    end_date        DATE,
    UNIQUE (company_id, product_code)
);
COMMENT ON TABLE product IS '상품';
CREATE INDEX idx_product_company ON product(company_id, is_active);
CREATE INDEX idx_product_type    ON product(insurance_type);

CREATE TABLE contract (
    contract_id      SERIAL PRIMARY KEY,
    agent_id         BIGINT         NOT NULL REFERENCES agent(agent_id),
    product_id       BIGINT         NOT NULL REFERENCES product(product_id),
    policy_no        VARCHAR(50)    NOT NULL UNIQUE,
    contractor_name  VARCHAR(100),
    insured_name     VARCHAR(100),
    premium          DECIMAL(15,2)  NOT NULL,
    pay_cycle        VARCHAR(10),                   -- MONTHLY / QUARTERLY / ANNUAL / LUMP
    pay_period       INT,
    coverage_period  INT,
    status           VARCHAR(20)    NOT NULL DEFAULT 'ACTIVE',  -- ACTIVE / LAPSE / REVIVE / TERMINATE
    contract_date    DATE           NOT NULL,
    effective_date   DATE,
    lapse_date       DATE,
    revival_date     DATE,
    created_at       TIMESTAMP      NOT NULL DEFAULT NOW(),
    updated_at       TIMESTAMP
);
COMMENT ON TABLE contract IS '계약';
CREATE INDEX idx_contract_agent  ON contract(agent_id, status);
CREATE INDEX idx_contract_policy ON contract(policy_no);
CREATE INDEX idx_contract_date   ON contract(contract_date);
