[Database]
├── schema: core        -- 核心業務資料表
├── schema: auth        -- 認證授權相關
├── schema: audit       -- 稽核軌跡
├── schema: staging     -- ETL 暫存區
└── schema: archive     -- 歷史歸檔

-- 建立資料表範例
CREATE TABLE [schema].[table_name] (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    [columns]   ...,
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by  VARCHAR(100) NOT NULL,
    updated_at  TIMESTAMP WITH TIME ZONE,
    updated_by  VARCHAR(100),
    is_deleted  BOOLEAN NOT NULL DEFAULT FALSE,
    version     INTEGER NOT NULL DEFAULT 1
);

-- 建立索引範例
CREATE INDEX ix_[table]_[column] ON [schema].[table_name] ([column]);

-- 建立外鍵範例
ALTER TABLE [child_table]
ADD CONSTRAINT fk_[child]_[parent]
FOREIGN KEY ([column]) REFERENCES [parent_table](id)
ON DELETE RESTRICT ON UPDATE CASCADE;

-- ====================================
-- HRMS Database Schema: core
-- Version: V1.0__initial_schema.sql
-- ====================================

CREATE SCHEMA IF NOT EXISTS core;

CREATE TABLE core.department (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dept_code       VARCHAR(20)  NOT NULL UNIQUE,
    dept_name       VARCHAR(100) NOT NULL,
    parent_dept_id  BIGINT       REFERENCES core.department(id),
    manager_id      BIGINT,      -- FK added after employee table created
    is_active       BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(100) NOT NULL,
    updated_at      TIMESTAMPTZ,
    updated_by      VARCHAR(100),
    is_deleted      BOOLEAN      NOT NULL DEFAULT FALSE,
    version         INTEGER      NOT NULL DEFAULT 1
);

CREATE TABLE core.employee (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_no     VARCHAR(20)  NOT NULL UNIQUE,
    first_name      VARCHAR(50)  NOT NULL,
    last_name       VARCHAR(50)  NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    phone           VARCHAR(100),             -- encrypted
    id_number       VARCHAR(100) NOT NULL,    -- encrypted
    department_id   BIGINT       NOT NULL REFERENCES core.department(id),
    position_id     BIGINT       NOT NULL,
    hire_date       DATE         NOT NULL,
    resignation_date DATE,
    status          VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE'
                    CHECK (status IN ('ACTIVE', 'ON_LEAVE', 'RESIGNED')),
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(100) NOT NULL,
    updated_at      TIMESTAMPTZ,
    updated_by      VARCHAR(100),
    is_deleted      BOOLEAN      NOT NULL DEFAULT FALSE,
    version         INTEGER      NOT NULL DEFAULT 1
);

-- 索引
CREATE INDEX ix_employee_department_id ON core.employee(department_id);
CREATE INDEX ix_employee_status ON core.employee(status, is_deleted);

-- 補建 department.manager_id FK
ALTER TABLE core.department
ADD CONSTRAINT fk_department_manager
FOREIGN KEY (manager_id) REFERENCES core.employee(id);

CREATE TABLE core.leave_request (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id     BIGINT       NOT NULL REFERENCES core.employee(id),
    leave_type      VARCHAR(30)  NOT NULL,
    start_date      DATE         NOT NULL,
    end_date        DATE         NOT NULL,
    total_days      DECIMAL(4,1) NOT NULL,
    status          VARCHAR(20)  NOT NULL DEFAULT 'PENDING'
                    CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'CANCELLED')),
    approver_id     BIGINT       REFERENCES core.employee(id),
    reason          TEXT,
    reject_reason   TEXT,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(100) NOT NULL,
    updated_at      TIMESTAMPTZ,
    updated_by      VARCHAR(100),
    is_deleted      BOOLEAN      NOT NULL DEFAULT FALSE,
    version         INTEGER      NOT NULL DEFAULT 1,
    CHECK (end_date >= start_date)
);

CREATE INDEX ix_leave_request_employee_date ON core.leave_request(employee_id, start_date);
CREATE INDEX ix_leave_request_approver ON core.leave_request(approver_id, status);