-- V4: 데이터 수신 (보험사 프로파일 / 필드매핑 / 코드매핑 / 원본데이터 / 파싱에러)

CREATE TABLE company_profile (
    company_code      VARCHAR(20) PRIMARY KEY REFERENCES insurance_company(company_code),
    data_format       VARCHAR(20),                 -- EXCEL / CSV / EDI / API
    receive_method    VARCHAR(20),                 -- MANUAL / FTP / API / SFTP
    file_encoding     VARCHAR(20) DEFAULT 'UTF-8',
    delimiter         VARCHAR(5),
    header_row        INT         DEFAULT 1,
    data_start_row    INT         DEFAULT 2,
    sheet_name        VARCHAR(50),
    date_format       VARCHAR(30),
    amount_format     VARCHAR(30),
    api_url           VARCHAR(300),
    ftp_host          VARCHAR(100),
    ftp_path          VARCHAR(300),
    receive_schedule  VARCHAR(50),
    is_active         CHAR(1)     NOT NULL DEFAULT 'Y'
);

CREATE TABLE company_field_mapping (
    mapping_id         SERIAL PRIMARY KEY,
    company_code       VARCHAR(20)  NOT NULL REFERENCES company_profile(company_code),
    source_field       VARCHAR(100) NOT NULL,
    source_col_index   INT,
    target_table       VARCHAR(50)  NOT NULL,
    target_field       VARCHAR(50)  NOT NULL,
    data_type          VARCHAR(20),
    transform_rule     VARCHAR(50),
    default_value      VARCHAR(100),
    is_required        CHAR(1)      NOT NULL DEFAULT 'N',
    sort_order         INT          NOT NULL DEFAULT 0,
    version            INT          NOT NULL DEFAULT 1,
    effective_from     DATE         NOT NULL DEFAULT CURRENT_DATE
);
CREATE INDEX idx_cfm_company ON company_field_mapping(company_code, target_table);

CREATE TABLE code_mapping (
    code_id        SERIAL PRIMARY KEY,
    company_code   VARCHAR(20)  NOT NULL,
    code_type      VARCHAR(30)  NOT NULL,           -- INSURANCE_TYPE / PAY_METHOD ...
    source_code    VARCHAR(50)  NOT NULL,
    source_name    VARCHAR(100),
    target_code    VARCHAR(50)  NOT NULL,
    target_name    VARCHAR(100),
    is_active      CHAR(1)      NOT NULL DEFAULT 'Y',
    UNIQUE (company_code, code_type, source_code)
);

CREATE TABLE raw_commission_data (
    raw_id           SERIAL PRIMARY KEY,
    company_code     VARCHAR(20),
    batch_id         VARCHAR(50),
    settle_month     CHAR(6),
    data_type        VARCHAR(20),
    raw_content      TEXT,
    parse_status     VARCHAR(20)  NOT NULL DEFAULT 'PENDING',  -- PENDING / OK / ERROR
    mapped_ledger_id BIGINT,
    mapped_table     VARCHAR(50),
    file_name        VARCHAR(300),
    row_number       INT,
    received_at      TIMESTAMP    NOT NULL DEFAULT NOW(),
    parsed_at        TIMESTAMP
);
CREATE INDEX idx_raw_lookup ON raw_commission_data(company_code, settle_month, parse_status);

CREATE TABLE parse_error_log (
    error_id         SERIAL PRIMARY KEY,
    raw_id           BIGINT REFERENCES raw_commission_data(raw_id),
    company_code     VARCHAR(20),
    error_type       VARCHAR(30),
    error_field      VARCHAR(50),
    error_value      VARCHAR(500),
    error_message    VARCHAR(500),
    resolve_status   VARCHAR(20)  NOT NULL DEFAULT 'OPEN',
    resolved_by      BIGINT,
    resolve_note     VARCHAR(500),
    resolved_at      TIMESTAMP,
    created_at       TIMESTAMP    NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_pel_status ON parse_error_log(resolve_status, created_at);
