hr-portal/database/schema.sql

-- ============================================================================
-- HR Portal Database Schema v2.0
-- 設計文件: 員工多身份設計文件.md
-- 創建日期: 2026-02-10
-- ============================================================================

-- 清理現有表格 (開發環境用)
DROP TABLE IF EXISTS audit_logs CASCADE;
DROP TABLE IF EXISTS network_drives CASCADE;
DROP TABLE IF EXISTS employee_identities CASCADE;
DROP TABLE IF EXISTS departments CASCADE;
DROP TABLE IF EXISTS business_units CASCADE;
DROP TABLE IF EXISTS employees CASCADE;

-- ============================================================================
-- 1. employees (員工基本資料)
-- ============================================================================
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    employee_id VARCHAR(20) UNIQUE NOT NULL,  -- EMP001
    username_base VARCHAR(50) UNIQUE NOT NULL, -- porsche.chen (全公司唯一)
    legal_name VARCHAR(100) NOT NULL,         -- 法定姓名: 陳保時
    english_name VARCHAR(100),                -- 英文名: Porsche Chen
    phone VARCHAR(20),
    mobile VARCHAR(20),
    hire_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'active',      -- active/inactive/terminated
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_employees_status ON employees(status);
CREATE INDEX idx_employees_username_base ON employees(username_base);

COMMENT ON TABLE employees IS '員工基本資料 - 一個員工的核心資訊';
COMMENT ON COLUMN employees.username_base IS '基礎帳號名稱,全公司唯一,用於生成各事業部 SSO 帳號';

-- ============================================================================
-- 2. business_units (事業部)
-- ============================================================================
CREATE TABLE business_units (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,               -- 業務發展部
    name_en VARCHAR(100),                     -- Business Development
    code VARCHAR(20) UNIQUE NOT NULL,         -- biz
    email_domain VARCHAR(100) UNIQUE NOT NULL, -- ease.taipei
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE business_units IS '事業部 - 公司的一級組織單位';
COMMENT ON COLUMN business_units.email_domain IS '該事業部的郵件網域 (用於生成 SSO 帳號和郵件地址)';

-- ============================================================================
-- 3. departments (部門)
-- ============================================================================
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    business_unit_id INTEGER NOT NULL REFERENCES business_units(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(business_unit_id, code)
);

CREATE INDEX idx_departments_business_unit ON departments(business_unit_id);

COMMENT ON TABLE departments IS '部門 - 隸屬於事業部的二級組織單位';
COMMENT ON COLUMN departments.code IS '部門代碼,在同一事業部內唯一';

-- ============================================================================
-- 4. employee_identities (員工身份)
-- ============================================================================
CREATE TABLE employee_identities (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,

    -- SSO 帳號 (= 郵件地址)
    username VARCHAR(100) UNIQUE NOT NULL,    -- porsche.chen@lab.taipei
    keycloak_id VARCHAR(100) UNIQUE NOT NULL, -- Keycloak UUID

    -- 組織與職務
    business_unit_id INTEGER NOT NULL REFERENCES business_units(id),
    department_id INTEGER REFERENCES departments(id),
    job_title VARCHAR(100) NOT NULL,          -- 技術總監
    job_level VARCHAR(20) NOT NULL,           -- Junior/Mid/Senior/Manager
    is_primary BOOLEAN DEFAULT FALSE,         -- 是否為主要身份

    -- 郵件配額
    email_quota_mb INTEGER NOT NULL,

    -- 時間記錄
    started_at DATE NOT NULL,
    ended_at DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 一個員工在一個事業部只能有一個身份
    UNIQUE(employee_id, business_unit_id)
);

CREATE INDEX idx_identities_employee ON employee_identities(employee_id);
CREATE INDEX idx_identities_keycloak ON employee_identities(keycloak_id);
CREATE INDEX idx_identities_username ON employee_identities(username);
CREATE INDEX idx_identities_business_unit ON employee_identities(business_unit_id);

COMMENT ON TABLE employee_identities IS '員工身份 - 一個員工在某個事業部的身份資訊';
COMMENT ON COLUMN employee_identities.username IS 'SSO 帳號 = 郵件地址 (格式: username_base@email_domain)';
COMMENT ON CONSTRAINT employee_identities_employee_id_business_unit_id_key ON employee_identities IS '一個員工在同一事業部只能有一個身份';

-- ============================================================================
-- 5. network_drives (網路硬碟)
-- ============================================================================
CREATE TABLE network_drives (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,

    -- 一個員工只有一個 NAS 帳號
    drive_name VARCHAR(100) UNIQUE NOT NULL,  -- porsche.chen (與 username_base 相同)
    quota_gb INTEGER NOT NULL,                -- 200 (依最高職級決定)
    webdav_url VARCHAR(255),
    smb_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(employee_id)  -- 一對一關係
);

CREATE INDEX idx_network_drives_employee ON network_drives(employee_id);

COMMENT ON TABLE network_drives IS '網路硬碟 - 一個員工對應一個 NAS 帳號';
COMMENT ON COLUMN network_drives.quota_gb IS 'NAS 配額 (GB),取該員工所有身份中的最高職級決定';

-- ============================================================================
-- 6. audit_logs (審計日誌)
-- ============================================================================
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    action VARCHAR(50) NOT NULL,              -- create/update/delete/login
    resource_type VARCHAR(50) NOT NULL,       -- employee/identity/department
    resource_id INTEGER,
    performed_by VARCHAR(100) NOT NULL,       -- 操作者的 SSO 帳號
    performed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    details JSONB,                            -- 詳細變更內容
    ip_address VARCHAR(45)                    -- IPv4/IPv6
);

CREATE INDEX idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_logs_performed_by ON audit_logs(performed_by);
CREATE INDEX idx_audit_logs_performed_at ON audit_logs(performed_at);

COMMENT ON TABLE audit_logs IS '審計日誌 - 記錄所有關鍵操作,符合 ISO 要求';

-- ============================================================================
-- 初始資料
-- ============================================================================

-- 事業部
INSERT INTO business_units (name, name_en, code, email_domain, description) VALUES
('業務發展部', 'Business Development', 'biz', 'ease.taipei', '碳權申請諮詢、碳足跡盤查、碳權交易媒合、業務拓展'),
('智能發展部', 'Smart Development', 'smart', 'lab.taipei', 'AI/ML 解決方案開發、IoT 智能監控、能源管理系統'),
('營運管理部', 'Operations Management', 'ops', 'porscheworld.tw', '行政管理、財務管理、人力資源、基礎設施');

-- 部門
INSERT INTO departments (business_unit_id, name, code, description) VALUES
-- 業務發展部 (id=1)
(1, '顧問部', 'consulting', '碳權顧問與技術諮詢'),
(1, '營運部', 'operations', '業務執行與客戶管理'),
(1, '認證部', 'certification', '國際碳權認證申請'),

-- 智能發展部 (id=2)
(2, '資訊部', 'it', '資訊系統開發與維運'),
(2, '研發部', 'research', 'AI/ML 技術研發'),
(2, '產品部', 'product', '產品設計與管理'),

-- 營運管理部 (id=3)
(3, '行政部', 'admin', '行政庶務與總務管理'),
(3, '財務部', 'finance', '財務會計與資金管理'),
(3, '人力資源部', 'hr', '人力資源與招募培訓');

-- ============================================================================
-- 視圖 (Views)
-- ============================================================================

-- 員工完整資訊視圖 (包含所有身份)
CREATE OR REPLACE VIEW v_employee_full_info AS
SELECT
    e.id AS employee_id,
    e.employee_id AS emp_no,
    e.username_base,
    e.legal_name,
    e.english_name,
    e.status AS employee_status,

    ei.id AS identity_id,
    ei.username AS sso_username,
    ei.job_title,
    ei.job_level,
    ei.is_primary,
    ei.email_quota_mb,
    ei.is_active AS identity_active,

    bu.name AS business_unit_name,
    bu.code AS business_unit_code,
    bu.email_domain,

    d.name AS department_name,
    d.code AS department_code,

    nd.drive_name,
    nd.quota_gb AS nas_quota_gb
FROM employees e
LEFT JOIN employee_identities ei ON e.id = ei.employee_id
LEFT JOIN business_units bu ON ei.business_unit_id = bu.id
LEFT JOIN departments d ON ei.department_id = d.id
LEFT JOIN network_drives nd ON e.id = nd.employee_id;

COMMENT ON VIEW v_employee_full_info IS '員工完整資訊視圖 - 包含所有身份、部門和資源資訊';

-- ============================================================================
-- 完成
-- ============================================================================
SELECT 'HR Portal Database Schema v2.0 created successfully!' AS status;