-- =============================================================================
-- 社会保険管理システム（サブシステムD）DBスキーマ
-- 対象DB2: DB2 for z/OS（区切り識別子使用）
-- ローカル開発: SQLite（型宣言はDB2準拠、SQLiteが許容する範囲で記述）
-- DB名称: INSURANCEDB
-- 注: テーブル名・カラム名は全角ハイフンを含むため二重引用符で囲む
-- =============================================================================

-- 1. INSURED-MASTER（被保険者マスタ）
CREATE TABLE "INSURED-MASTER" (
    "EMP-ID"          CHAR(8)      NOT NULL,
    "INSURED-NO"      CHAR(10)     NOT NULL,
    "OFFICE-NO"       CHAR(4)      NOT NULL,
    "INSURER-CODE"    CHAR(4)      NOT NULL,
    "HEALTH-INS-TYPE" CHAR(1)      NOT NULL,
    "PENSION-TYPE"    CHAR(1)      NOT NULL,
    "BASE-MONTHLY"    DECIMAL(9,0) NOT NULL,
    "GRADE-CODE"      CHAR(2)      NOT NULL,
    "STATUS"          CHAR(1)      NOT NULL,
    "UPDATED-AT"      TIMESTAMP    NOT NULL,
    PRIMARY KEY ("EMP-ID")
);

-- 2. INSURANCE-RATES（保険料率テーブル）
CREATE TABLE "INSURANCE-RATES" (
    "GRADE-CODE"      CHAR(2)      NOT NULL,
    "MONTHLY-FROM"    DECIMAL(9,0) NOT NULL,
    "MONTHLY-TO"      DECIMAL(9,0) NOT NULL,
    "HEALTH-RATE"     DECIMAL(7,6) NOT NULL,
    "PENSION-RATE"    DECIMAL(7,6) NOT NULL,
    "EFFECTIVE-FROM"  CHAR(6)      NOT NULL,
    "EFFECTIVE-TO"    CHAR(6)      NOT NULL,
    "UPDATED-AT"      TIMESTAMP    NOT NULL,
    PRIMARY KEY ("GRADE-CODE", "EFFECTIVE-FROM", "EFFECTIVE-TO")
);

-- 3. GRADE-HISTORY（標準報酬等級履歴）
CREATE TABLE "GRADE-HISTORY" (
    "EMP-ID"          CHAR(8)      NOT NULL,
    "YEAR-MONTH"      CHAR(6)      NOT NULL,
    "GRADE-CODE"      CHAR(2)      NOT NULL,
    "MONTHLY-AMOUNT"  DECIMAL(9,0) NOT NULL,
    "CHANGE-TYPE"     CHAR(1)      NOT NULL,
    "PREV-GRADE"      CHAR(2)      NOT NULL,
    "UPDATED-AT"      TIMESTAMP    NOT NULL,
    PRIMARY KEY ("EMP-ID", "YEAR-MONTH")
);

-- 4. QUALIFICATION-CHANGES（資格異動テーブル）
CREATE TABLE "QUALIFICATION-CHANGES" (
    "CHG-ID"          INTEGER      NOT NULL,
    "EMP-ID"          CHAR(8)      NOT NULL,
    "CHG-DATE"        CHAR(8)      NOT NULL,
    "CHG-TYPE"        CHAR(2)      NOT NULL,
    "INSURER-CODE"    CHAR(4)      NOT NULL,
    "PREV-INSURER"    CHAR(4)      NOT NULL,
    "REASON"          VARCHAR(100) NOT NULL,
    "UPDATED-AT"      TIMESTAMP    NOT NULL,
    PRIMARY KEY ("CHG-ID")
);

-- 5. INSURER-MASTER（保険者マスタ）
CREATE TABLE "INSURER-MASTER" (
    "INSURER-CODE"    CHAR(4)      NOT NULL,
    "INSURER-NAME"    VARCHAR(60)  NOT NULL,
    "PREF-CODE"       CHAR(2)      NOT NULL,
    "INSURER-TYPE"    CHAR(1)      NOT NULL,
    "UPDATED-AT"      TIMESTAMP    NOT NULL,
    PRIMARY KEY ("INSURER-CODE")
);