-- =============================================================================
-- 勤怠休暇管理システム（サブシステムA）DBスキーマ
-- 対象DB2: DB2 for z/OS
-- ローカル開発: SQLite（型宣言はDB2準拠、SQLiteが許容する範囲で記述）
-- =============================================================================

-- 1. EMP_MASTER（社員マスタ）
-- 全社員の基本情報。サブシステムAでは参照のみ。
CREATE TABLE EMP_MASTER (
    EMP_ID    CHAR(8)    NOT NULL PRIMARY KEY,
    DEPT_ID   CHAR(4)    NOT NULL,
    EMP_NAME  VARCHAR(50) NOT NULL,
    STATUS    CHAR(1)    NOT NULL
);

INSERT INTO EMP_MASTER VALUES ('EMP00001', 'D001', '山田太郎', '1');
INSERT INTO EMP_MASTER VALUES ('EMP00002', 'D002', '鈴木花子', '1');
INSERT INTO EMP_MASTER VALUES ('EMP00009', 'D003', '佐藤健一', '9');
INSERT INTO EMP_MASTER VALUES ('EMP00010', 'D001', '田中実',   '1');

-- 2. LEAVE_RECORDS（休暇申請記録）
-- 休暇申請の生データ。取消はDELETEで物理削除。
CREATE TABLE LEAVE_RECORDS (
    APPLICATION_ID INTEGER    NOT NULL PRIMARY KEY,
    EMP_ID         CHAR(8)    NOT NULL,
    LEAVE_TYPE     CHAR(2)    NOT NULL,
    START_DATE     CHAR(8)    NOT NULL,
    START_TIME     CHAR(4)    NOT NULL,
    END_DATE       CHAR(8)    NOT NULL,
    END_TIME       CHAR(4)    NOT NULL,
    STATUS         CHAR(1)    NOT NULL
);

INSERT INTO LEAVE_RECORDS VALUES (1, 'EMP00001', '01', '20260615', '0900', '20260616', '1800', '1');
INSERT INTO LEAVE_RECORDS VALUES (2, 'EMP00002', '02', '20260617', '0900', '20260617', '1200', '1');
INSERT INTO LEAVE_RECORDS VALUES (3, 'EMP00001', '04', '20260620', '0900', '20260620', '1800', '9');

-- 3. HOLIDAY_CALENDAR（休日カレンダー）
-- 祝日のみ列挙。土日は曜日判定で処理。
CREATE TABLE HOLIDAY_CALENDAR (
    HOLIDAY_DATE  CHAR(8)     NOT NULL PRIMARY KEY,
    DESCRIPTION   VARCHAR(50)
);

INSERT INTO HOLIDAY_CALENDAR VALUES ('20260101', '元日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260112', '成人の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260211', '建国記念の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260320', '春分の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260429', '昭和の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260503', '憲法記念日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260504', 'みどりの日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260505', 'こどもの日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260720', '海の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260811', '山の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260921', '敬老の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260922', '国民の休日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20260923', '秋分の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20261012', '体育の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20261103', '文化の日');
INSERT INTO HOLIDAY_CALENDAR VALUES ('20261123', '勤労感謝の日');

-- 4. SICK_LEAVE_RATE（病欠控除率）
-- 全社員共通の病欠控除率。
CREATE TABLE SICK_LEAVE_RATE (
    LEAVE_TYPE      CHAR(2)      NOT NULL PRIMARY KEY,
    DEDUCTION_RATE  DECIMAL(3,2) NOT NULL
);

-- 5. DAILY_RECORDS（日別勤怠記録）
-- 1社員1日の勤怠情報。出勤日のみレコード存在。
CREATE TABLE DAILY_RECORDS (
    EMP_ID               CHAR(8)      NOT NULL,
    TARGET_DATE          CHAR(8)      NOT NULL,
    TIME_IN              CHAR(4)      NOT NULL,
    TIME_OUT             CHAR(4)      NOT NULL,
    ANNUAL_LEAVE_H       DECIMAL(6,1) NOT NULL,
    PERSONAL_LEAVE_H     DECIMAL(6,1) NOT NULL,
    OFFICIAL_LEAVE_H     DECIMAL(6,1) NOT NULL,
    SICK_LEAVE_H         DECIMAL(6,1) NOT NULL,
    UNAPPROVED_ABSENT_H  DECIMAL(6,1) NOT NULL,
    UPDATED_AT           TIMESTAMP    NOT NULL,
    PRIMARY KEY (EMP_ID, TARGET_DATE)
);

-- 6. MONTHLY_ABSENCE（月次統計）
-- DAILY_RECORDSの月次集計サマリ。
CREATE TABLE MONTHLY_ABSENCE (
    EMP_ID               CHAR(8)      NOT NULL,
    YEAR_MONTH           CHAR(6)      NOT NULL,
    ANNUAL_LEAVE_H       DECIMAL(6,1) NOT NULL,
    PERSONAL_LEAVE_H     DECIMAL(6,1) NOT NULL,
    OFFICIAL_LEAVE_H     DECIMAL(6,1) NOT NULL,
    SICK_LEAVE_H         DECIMAL(6,1) NOT NULL,
    UNAPPROVED_ABSENT_H  DECIMAL(6,1) NOT NULL,
    UPDATED_AT           TIMESTAMP    NOT NULL,
    PRIMARY KEY (EMP_ID, YEAR_MONTH)
);