ExecuBoard/scripts/create-database-schema.sql

-- 創建 MySQL 資料庫 Schema
-- 執行管理績效儀表板系統

-- 用戶表
CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  role ENUM('executive', 'manager', 'hr') NOT NULL,
  avatar_url VARCHAR(500),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role (role)
);

-- KPI 表
CREATE TABLE IF NOT EXISTS kpi (
  id VARCHAR(255) PRIMARY KEY,
  user_id VARCHAR(255) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  category ENUM('financial', 'operational', 'team', 'innovation', 'strategy') NOT NULL,
  weight INT NOT NULL DEFAULT 1,
  target_value DECIMAL(15,2) NOT NULL,
  current_value DECIMAL(15,2) DEFAULT 0,
  unit VARCHAR(50),
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  approved_by VARCHAR(255),
  status ENUM('pending', 'approved', 'rejected', 'active', 'completed', 'deleted') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_category (category),
  INDEX idx_date_range (start_date, end_date)
);

-- KPI 進度追蹤表
CREATE TABLE IF NOT EXISTS kpi_progress (
  id VARCHAR(255) PRIMARY KEY,
  kpi_id VARCHAR(255) NOT NULL,
  progress DECIMAL(15,2) NOT NULL,
  comment TEXT,
  attachment_url VARCHAR(500),
  blockers TEXT,
  created_by VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (kpi_id) REFERENCES kpi(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_kpi_id (kpi_id),
  INDEX idx_created_by (created_by),
  INDEX idx_created_at (created_at)
);

-- 評審表
CREATE TABLE IF NOT EXISTS reviews (
  id VARCHAR(255) PRIMARY KEY,
  reviewer_id VARCHAR(255) NOT NULL,
  reviewee_id VARCHAR(255) NOT NULL,
  review_type ENUM('quarterly', 'annual', 'one_on_one', 'goal_setting') NOT NULL,
  scheduled_date DATETIME NOT NULL,
  completed_date DATETIME,
  status ENUM('scheduled', 'in_progress', 'completed', 'cancelled') DEFAULT 'scheduled',
  summary TEXT,
  overall_rating DECIMAL(3,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewee_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_reviewer_id (reviewer_id),
  INDEX idx_reviewee_id (reviewee_id),
  INDEX idx_scheduled_date (scheduled_date),
  INDEX idx_status (status),
  INDEX idx_review_type (review_type)
);

-- 評審回應表
CREATE TABLE IF NOT EXISTS review_responses (
  id VARCHAR(255) PRIMARY KEY,
  review_id VARCHAR(255) NOT NULL,
  question_id VARCHAR(255) NOT NULL,
  question_text TEXT NOT NULL,
  response_text TEXT,
  score INT CHECK(score >= 1 AND score <= 5),
  question_type ENUM('text', 'rating', 'multiple_choice') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE,
  INDEX idx_review_id (review_id)
);

-- KPI 模板表（供 HR 管理）
CREATE TABLE IF NOT EXISTS kpi_templates (
  id VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category ENUM('financial', 'operational', 'team', 'innovation', 'strategy'),
  default_weight INT,
  smart_criteria JSON,
  created_by VARCHAR(255),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_category (category),
  INDEX idx_is_active (is_active)
);

-- 評審問題模板
CREATE TABLE IF NOT EXISTS review_question_templates (
  id VARCHAR(255) PRIMARY KEY,
  question_text TEXT NOT NULL,
  question_type ENUM('text', 'rating', 'multiple_choice') NOT NULL,
  category VARCHAR(100),
  is_required BOOLEAN DEFAULT FALSE,
  options JSON,
  created_by VARCHAR(255),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_category (category),
  INDEX idx_is_active (is_active)
);

-- 初始化示例數據
INSERT IGNORE INTO users (id, name, email, role) VALUES 
('user_admin', '系統管理員', 'admin@company.com', 'executive'),
('user_hr_1', 'HR 經理', 'hr@company.com', 'hr'),
('user_mgr_1', '部門經理', 'manager@company.com', 'manager'),
('user_exec_1', '執行長', 'ceo@company.com', 'executive'),
('user_exec_2', '營運長', 'coo@company.com', 'executive');

-- 插入示例 KPI 模板
INSERT IGNORE INTO kpi_templates (id, name, description, category, default_weight) VALUES
('template_1', '營收成長', '年對年營收成長百分比', 'financial', 30),
('template_2', '團隊滿意度', '員工滿意度調查分數', 'team', 25),
('template_3', '市場佔有率', '公司市場佔有率百分比', 'operational', 20),
('template_4', '創新指數', '推出的新計畫數量', 'innovation', 25);

-- 插入示例評審問題模板
INSERT IGNORE INTO review_question_templates (id, question_text, question_type, category, is_required) VALUES
('q1', '您如何評價這個季度的整體表現？', 'rating', 'performance', TRUE),
('q2', '這個期間您的主要成就是什麼？', 'text', 'performance', TRUE),
('q3', '您面臨哪些挑戰，如何克服？', 'text', 'challenges', FALSE),
('q4', '您領導團隊的效率如何？', 'rating', 'leadership', TRUE),
('q5', '下個季度您的目標是什麼？', 'text', 'goals', TRUE);

-- 創建視圖以便更容易查詢
CREATE OR REPLACE VIEW kpi_with_user AS
SELECT 
  k.*,
  u.name as user_name,
  u.email as user_email,
  u.role as user_role,
  approver.name as approver_name
FROM kpi k
LEFT JOIN users u ON k.user_id = u.id
LEFT JOIN users approver ON k.approved_by = approver.id;

CREATE OR REPLACE VIEW reviews_with_users AS
SELECT 
  r.*,
  reviewer.name as reviewer_name,
  reviewer.email as reviewer_email,
  reviewee.name as reviewee_name,
  reviewee.email as reviewee_email
FROM reviews r
LEFT JOIN users reviewer ON r.reviewer_id = reviewer.id
LEFT JOIN users reviewee ON r.reviewee_id = reviewee.id;