KPI-management/ddl/01_create_tables.sql

-- KPI 管理系統 - 資料庫建立腳本
-- 版本: 1.0
-- 建立日期: 2024-12

-- ============================================
-- 1. 部門表 (departments)
-- ============================================
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    level VARCHAR(20) NOT NULL DEFAULT 'DEPT',  -- COMPANY, BU, DEPT, TEAM
    parent_id INTEGER REFERENCES departments(id),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_departments_parent ON departments(parent_id);
CREATE INDEX idx_departments_code ON departments(code);

COMMENT ON TABLE departments IS '部門組織表';
COMMENT ON COLUMN departments.level IS '層級: COMPANY=公司, BU=事業單位, DEPT=部門, TEAM=團隊';

-- ============================================
-- 2. 員工表 (employees)
-- ============================================
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    employee_no VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL UNIQUE,
    password_hash VARCHAR(200) NOT NULL,
    department_id INTEGER NOT NULL REFERENCES departments(id),
    manager_id INTEGER REFERENCES employees(id),
    job_title VARCHAR(100),
    role VARCHAR(20) NOT NULL DEFAULT 'employee',  -- employee, manager, admin, hr
    status VARCHAR(20) NOT NULL DEFAULT 'active',  -- active, inactive, resigned
    hire_date DATE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_employees_department ON employees(department_id);
CREATE INDEX idx_employees_manager ON employees(manager_id);
CREATE INDEX idx_employees_status ON employees(status);

COMMENT ON TABLE employees IS '員工表';
COMMENT ON COLUMN employees.role IS '角色: employee=員工, manager=主管, admin=管理員, hr=人資';

-- ============================================
-- 3. KPI 期間表 (kpi_periods)
-- ============================================
CREATE TABLE kpi_periods (
    id SERIAL PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,  -- 例: 2024H1, 2024H2
    name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    setting_start DATE NOT NULL,       -- KPI 設定開始日
    setting_end DATE NOT NULL,         -- KPI 設定截止日
    self_eval_start DATE,              -- 自評開始日
    self_eval_end DATE,                -- 自評截止日
    manager_eval_start DATE,           -- 主管評核開始日
    manager_eval_end DATE,             -- 主管評核截止日
    status VARCHAR(20) NOT NULL DEFAULT 'draft',  -- draft, setting, approved, self_eval, manager_eval, completed
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_kpi_periods_status ON kpi_periods(status);

COMMENT ON TABLE kpi_periods IS 'KPI 期間表';
COMMENT ON COLUMN kpi_periods.status IS '狀態: draft=草稿, setting=設定中, approved=已核准, self_eval=自評中, manager_eval=主管評核中, completed=已完成';

-- ============================================
-- 4. KPI 範本表 (kpi_templates)
-- ============================================
CREATE TABLE kpi_templates (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50) NOT NULL,  -- financial, customer, internal, learning
    description TEXT,
    default_weight INTEGER DEFAULT 20,
    level0_desc TEXT NOT NULL,  -- 等級 0 說明
    level1_desc TEXT NOT NULL,  -- 等級 1 說明
    level2_desc TEXT NOT NULL,  -- 等級 2 說明
    level3_desc TEXT NOT NULL,  -- 等級 3 說明
    level4_desc TEXT NOT NULL,  -- 等級 4 說明
    applicable_roles TEXT[],    -- 適用角色
    applicable_depts INTEGER[], -- 適用部門 ID
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_kpi_templates_category ON kpi_templates(category);
CREATE INDEX idx_kpi_templates_active ON kpi_templates(is_active);

COMMENT ON TABLE kpi_templates IS 'KPI 範本表';
COMMENT ON COLUMN kpi_templates.category IS '類別: financial=財務, customer=客戶, internal=內部流程, learning=學習成長';

-- ============================================
-- 5. KPI 預設組合表 (kpi_presets)
-- ============================================
CREATE TABLE kpi_presets (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    applicable_roles TEXT[],
    applicable_depts INTEGER[],
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE kpi_presets IS 'KPI 預設組合表';

-- ============================================
-- 6. KPI 預設項目表 (kpi_preset_items)
-- ============================================
CREATE TABLE kpi_preset_items (
    id SERIAL PRIMARY KEY,
    preset_id INTEGER NOT NULL REFERENCES kpi_presets(id) ON DELETE CASCADE,
    template_id INTEGER NOT NULL REFERENCES kpi_templates(id),
    default_weight INTEGER NOT NULL,
    is_mandatory BOOLEAN NOT NULL DEFAULT FALSE,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_kpi_preset_items_preset ON kpi_preset_items(preset_id);

COMMENT ON TABLE kpi_preset_items IS 'KPI 預設項目表';

-- ============================================
-- 7. KPI 表單表 (kpi_sheets)
-- ============================================
CREATE TABLE kpi_sheets (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(id),
    period_id INTEGER NOT NULL REFERENCES kpi_periods(id),
    department_id INTEGER NOT NULL REFERENCES departments(id),
    status VARCHAR(20) NOT NULL DEFAULT 'draft',  -- draft, pending, approved, self_eval, manager_eval, completed, settled

    -- 提交資訊
    submitted_at TIMESTAMP,

    -- 審核資訊
    approved_by INTEGER REFERENCES employees(id),
    approved_at TIMESTAMP,
    approve_comment TEXT,

    -- 退回資訊
    rejected_by INTEGER REFERENCES employees(id),
    rejected_at TIMESTAMP,
    reject_reason TEXT,

    -- 自評資訊
    self_eval_at TIMESTAMP,

    -- 主管評核資訊
    manager_eval_by INTEGER REFERENCES employees(id),
    manager_eval_at TIMESTAMP,
    manager_eval_comment TEXT,

    -- 分數
    total_score DECIMAL(5,4),  -- 總獎金月數 (0~1)

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(employee_id, period_id)
);

CREATE INDEX idx_kpi_sheets_employee ON kpi_sheets(employee_id);
CREATE INDEX idx_kpi_sheets_period ON kpi_sheets(period_id);
CREATE INDEX idx_kpi_sheets_department ON kpi_sheets(department_id);
CREATE INDEX idx_kpi_sheets_status ON kpi_sheets(status);

COMMENT ON TABLE kpi_sheets IS 'KPI 表單表';
COMMENT ON COLUMN kpi_sheets.status IS '狀態: draft=草稿, pending=待審核, approved=已核准, self_eval=自評中, manager_eval=主管評核中, completed=已完成, settled=已結算';

-- ============================================
-- 8. KPI 項目表 (kpi_items)
-- ============================================
CREATE TABLE kpi_items (
    id SERIAL PRIMARY KEY,
    sheet_id INTEGER NOT NULL REFERENCES kpi_sheets(id) ON DELETE CASCADE,
    template_id INTEGER REFERENCES kpi_templates(id),
    sort_order INTEGER NOT NULL DEFAULT 0,

    -- 項目資訊
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50) NOT NULL,
    weight INTEGER NOT NULL,  -- 權重百分比 (1-100)

    -- 等級標準
    level0_criteria TEXT,
    level1_criteria TEXT,
    level2_criteria TEXT,
    level3_criteria TEXT,
    level4_criteria TEXT,

    -- 自評
    self_eval_level INTEGER,  -- 0-4
    self_eval_note TEXT,

    -- 主管評核
    final_level INTEGER,      -- 0-4 (最終等級)
    final_note TEXT,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_kpi_items_sheet ON kpi_items(sheet_id);

COMMENT ON TABLE kpi_items IS 'KPI 項目表';
COMMENT ON COLUMN kpi_items.weight IS '權重百分比，同一 sheet 的所有項目權重總和必須等於 100';

-- ============================================
-- 9. KPI 指派表 (kpi_assignments)
-- ============================================
CREATE TABLE kpi_assignments (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(id),
    period_id INTEGER NOT NULL REFERENCES kpi_periods(id),
    assigned_by INTEGER NOT NULL REFERENCES employees(id),
    items JSONB NOT NULL,  -- 指派的 KPI 項目 JSON
    note TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(employee_id, period_id)
);

CREATE INDEX idx_kpi_assignments_employee ON kpi_assignments(employee_id);

COMMENT ON TABLE kpi_assignments IS '主管指派 KPI 表';

-- ============================================
-- 10. KPI 審核紀錄表 (kpi_review_logs)
-- ============================================
CREATE TABLE kpi_review_logs (
    id SERIAL PRIMARY KEY,
    sheet_id INTEGER NOT NULL REFERENCES kpi_sheets(id) ON DELETE CASCADE,
    action VARCHAR(50) NOT NULL,  -- submit, approve, reject, self_eval, manager_eval
    actor_id INTEGER NOT NULL REFERENCES employees(id),
    from_status VARCHAR(20),
    to_status VARCHAR(20),
    comment TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_kpi_review_logs_sheet ON kpi_review_logs(sheet_id);
CREATE INDEX idx_kpi_review_logs_actor ON kpi_review_logs(actor_id);

COMMENT ON TABLE kpi_review_logs IS 'KPI 審核紀錄表';

-- ============================================
-- 11. 通知表 (notifications)
-- ============================================
CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    recipient_id INTEGER NOT NULL REFERENCES employees(id),
    type VARCHAR(50) NOT NULL,  -- kpi_submitted, kpi_approved, kpi_rejected, eval_reminder, etc.
    title VARCHAR(200) NOT NULL,
    content TEXT,
    related_sheet_id INTEGER REFERENCES kpi_sheets(id) ON DELETE SET NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    read_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notifications_recipient ON notifications(recipient_id);
CREATE INDEX idx_notifications_unread ON notifications(recipient_id, is_read) WHERE is_read = FALSE;

COMMENT ON TABLE notifications IS '通知表';

-- ============================================
-- 12. 通知偏好表 (notification_preferences)
-- ============================================
CREATE TABLE notification_preferences (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL UNIQUE REFERENCES employees(id),
    email_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    in_app_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    reminder_days_before INTEGER NOT NULL DEFAULT 3,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE notification_preferences IS '通知偏好表';

-- ============================================
-- 13. 儀表板警示表 (dashboard_alerts)
-- ============================================
CREATE TABLE dashboard_alerts (
    id SERIAL PRIMARY KEY,
    alert_type VARCHAR(50) NOT NULL,  -- deadline_approaching, overdue, weight_invalid
    severity VARCHAR(20) NOT NULL DEFAULT 'warning',  -- info, warning, error
    title VARCHAR(200) NOT NULL,
    description TEXT,
    related_sheet_id INTEGER REFERENCES kpi_sheets(id) ON DELETE CASCADE,
    related_employee_id INTEGER REFERENCES employees(id),
    is_resolved BOOLEAN NOT NULL DEFAULT FALSE,
    resolved_at TIMESTAMP,
    resolved_by INTEGER REFERENCES employees(id),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_dashboard_alerts_unresolved ON dashboard_alerts(is_resolved) WHERE is_resolved = FALSE;

COMMENT ON TABLE dashboard_alerts IS '儀表板警示表';

-- ============================================
-- 更新時間觸發器
-- ============================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 為需要 updated_at 的表建立觸發器
CREATE TRIGGER update_departments_updated_at BEFORE UPDATE ON departments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_employees_updated_at BEFORE UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_kpi_periods_updated_at BEFORE UPDATE ON kpi_periods FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_kpi_templates_updated_at BEFORE UPDATE ON kpi_templates FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_kpi_presets_updated_at BEFORE UPDATE ON kpi_presets FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_kpi_sheets_updated_at BEFORE UPDATE ON kpi_sheets FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_kpi_items_updated_at BEFORE UPDATE ON kpi_items FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_notification_preferences_updated_at BEFORE UPDATE ON notification_preferences FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();