-- ============================================================
-- 智慧知識管理系統 (IKMS) - MySQL 資料庫 Schema
-- Intelligent Knowledge Management System
-- 版本: 1.0
-- 建立日期: 2026-01-12
-- ============================================================

-- 設定字元集

-- ============================================================
-- 1. 使用者與權限相關資料表
-- ============================================================

-- 部門資料表
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部門ID，自動遞增的主鍵',
    deptCode VARCHAR(20) NOT NULL UNIQUE COMMENT '部門代碼，需唯一',
    deptName VARCHAR(100) NOT NULL COMMENT '部門名稱',
    parentDeptId INT DEFAULT NULL COMMENT '上層部門ID（支援階層結構）',
    deptLevel INT DEFAULT 1 COMMENT '部門層級（1為最上層）',
    deptManager VARCHAR(100) COMMENT '部門主管姓名',
    deptStatus CHAR(1) DEFAULT 'Y' COMMENT '啟用狀態（Y啟用, N停用）',
    sortOrder INT DEFAULT 0 COMMENT '排序順序',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_parent_dept (parentDeptId),
    INDEX idx_dept_status (deptStatus)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部門資料表';

-- 角色資料表
CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID，自動遞增的主鍵',
    roleCode VARCHAR(50) NOT NULL UNIQUE COMMENT '角色代碼（如：admin, manager, km_admin, user）',
    roleName VARCHAR(100) NOT NULL COMMENT '角色顯示名稱',
    roleDesc TEXT COMMENT '角色說明',
    permissions JSON COMMENT '權限設定JSON（功能權限陣列）',
    roleLevel INT DEFAULT 10 COMMENT '角色等級（數字越小權限越高）',
    roleStatus CHAR(1) DEFAULT 'Y' COMMENT '啟用狀態（Y啟用, N停用）',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_role_status (roleStatus)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色資料表';

-- ============================================================
-- 2. 文件管理相關資料表
-- ============================================================

-- 文件分類資料表
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分類ID，自動遞增的主鍵',
    categoryCode VARCHAR(50) NOT NULL UNIQUE COMMENT '分類代碼，需唯一',
    categoryName VARCHAR(100) NOT NULL COMMENT '分類名稱',
    parentCategoryId INT DEFAULT NULL COMMENT '上層分類ID（支援階層結構）',
    categoryLevel INT DEFAULT 1 COMMENT '分類層級',
    categoryDesc TEXT COMMENT '分類說明',
    iconClass VARCHAR(50) COMMENT '圖示CSS類別（如：bi-file-text）',
    sortOrder INT DEFAULT 0 COMMENT '排序順序',
    categoryStatus CHAR(1) DEFAULT 'Y' COMMENT '啟用狀態（Y啟用, N停用）',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_parent_category (parentCategoryId),
    INDEX idx_category_status (categoryStatus)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件分類資料表';

-- 文件資料表
CREATE TABLE documents (
    doc_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '文件ID，自動遞增的主鍵',
    docUuid CHAR(36) NOT NULL UNIQUE COMMENT 'UUID唯一識別碼',
    docNumber VARCHAR(50) UNIQUE COMMENT '文件編號（如：DOC-2026-0001）',
    docTitle VARCHAR(255) NOT NULL COMMENT '文件標題',
    docDesc TEXT COMMENT '文件摘要說明',
    category_id INT COMMENT '分類ID',
    dept_id INT COMMENT '所屬部門ID',
    author_id INT COMMENT '作者使用者ID',
    filePath VARCHAR(500) NOT NULL COMMENT '檔案儲存路徑',
    fileName VARCHAR(255) NOT NULL COMMENT '原始檔案名稱',
    fileType VARCHAR(20) NOT NULL COMMENT '檔案類型（pdf, docx, xlsx, txt, pptx）',
    fileSize BIGINT DEFAULT 0 COMMENT '檔案大小（bytes）',
    fileMd5 VARCHAR(32) COMMENT '檔案MD5雜湊值（用於重複檢測）',
    docVersion VARCHAR(20) DEFAULT 'v1.0' COMMENT '文件版本號',
    docStatus CHAR(1) DEFAULT 'D' COMMENT '文件狀態（D草稿, P待審核, A生效中, R已撤銷, X已刪除）',
    accessLevel VARCHAR(20) DEFAULT 'department' COMMENT '存取層級（public公開, department部門, private私人, custom自訂）',
    qdrantSynced CHAR(1) DEFAULT 'N' COMMENT '是否已同步至Qdrant向量庫（Y/N）',
    qdrantSyncedAt DATETIME COMMENT '向量同步時間',
    chunkCount INT DEFAULT 0 COMMENT '文件分塊數量',
    tokenCount INT DEFAULT 0 COMMENT '文件總Token數',
    viewCount INT DEFAULT 0 COMMENT '查看次數',
    downloadCount INT DEFAULT 0 COMMENT '下載次數',
    tags JSON COMMENT '標籤陣列JSON',
    metadata JSON COMMENT '額外元資料JSON（頁數、作者等）',
    publishedAt DATETIME COMMENT '發布日期',
    expiredAt DATETIME COMMENT '文件到期日',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_doc_status (docStatus),
    INDEX idx_category (category_id),
    INDEX idx_dept (dept_id),
    INDEX idx_author (author_id),
    INDEX idx_file_type (fileType),
    INDEX idx_qdrant_synced (qdrantSynced),
    INDEX idx_access_level (accessLevel),
    FULLTEXT INDEX ft_doc_title (docTitle),
    CONSTRAINT fk_doc_category FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    CONSTRAINT fk_doc_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL,
    CONSTRAINT fk_doc_author FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件資料表';

-- 文件分塊資料表
CREATE TABLE document_chunks (
    chunk_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分塊ID，自動遞增的主鍵',
    doc_id INT NOT NULL COMMENT '所屬文件ID',
    chunkIndex INT NOT NULL COMMENT '分塊序號（從0開始）',
    chunkContent TEXT NOT NULL COMMENT '分塊文字內容',
    qdrantPointId CHAR(36) COMMENT 'Qdrant向量點UUID',
    tokenCount INT DEFAULT 0 COMMENT '此分塊的Token數量',
    pageNumber INT COMMENT '來源頁碼（如適用）',
    sectionTitle VARCHAR(255) COMMENT '段落標題（如適用）',
    chunkMetadata JSON COMMENT '額外元資料JSON',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_doc_id (doc_id),
    INDEX idx_qdrant_point (qdrantPointId),
    UNIQUE INDEX idx_doc_chunk (doc_id, chunkIndex),
    CONSTRAINT fk_chunk_doc FOREIGN KEY (doc_id) REFERENCES documents(doc_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件分塊資料表';

-- 文件版本歷史資料表
CREATE TABLE document_versions (
    version_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '版本ID，自動遞增的主鍵',
    doc_id INT NOT NULL COMMENT '文件ID',
    versionNumber VARCHAR(20) NOT NULL COMMENT '版本號',
    filePath VARCHAR(500) NOT NULL COMMENT '此版本檔案路徑',
    fileSize BIGINT DEFAULT 0 COMMENT '檔案大小（bytes）',
    changeLog TEXT COMMENT '版本變更說明',
    changedBy INT COMMENT '變更人員ID',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_doc_version (doc_id, versionNumber),
    CONSTRAINT fk_version_doc FOREIGN KEY (doc_id) REFERENCES documents(doc_id) ON DELETE CASCADE,
    CONSTRAINT fk_version_user FOREIGN KEY (changedBy) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件版本歷史資料表';

-- 文件權限資料表
CREATE TABLE document_permissions (
    perm_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '權限ID，自動遞增的主鍵',
    doc_id INT NOT NULL COMMENT '文件ID',
    targetType VARCHAR(20) NOT NULL COMMENT '權限對象類型（user使用者, role角色, department部門）',
    target_id INT NOT NULL COMMENT '權限對象ID（對應user_id/role_id/dept_id）',
    permissionLevel VARCHAR(20) DEFAULT 'read' COMMENT '權限等級（read讀取, write編輯, admin管理）',
    grantedBy INT COMMENT '授權人員ID',
    expiredAt DATETIME COMMENT '權限到期日',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_doc_perm (doc_id),
    INDEX idx_target (targetType, target_id),
    UNIQUE INDEX idx_unique_perm (doc_id, targetType, target_id),
    CONSTRAINT fk_perm_doc FOREIGN KEY (doc_id) REFERENCES documents(doc_id) ON DELETE CASCADE,
    CONSTRAINT fk_perm_granted FOREIGN KEY (grantedBy) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件權限資料表';

-- ============================================================
-- 3. ETL 處理相關資料表
-- ============================================================

-- ETL 任務資料表
CREATE TABLE etl_jobs (
    job_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '任務ID，自動遞增的主鍵',
    jobUuid CHAR(36) NOT NULL UNIQUE COMMENT '任務UUID',
    doc_id INT COMMENT '關聯文件ID',
    jobType VARCHAR(50) NOT NULL COMMENT '任務類型（upload上傳, reindex重建索引, delete刪除）',
    jobStatus VARCHAR(20) DEFAULT 'pending' COMMENT '任務狀態（pending待處理, processing處理中, completed完成, failed失敗）',
    jobPriority INT DEFAULT 5 COMMENT '優先順序（1最高, 10最低）',
    totalSteps INT DEFAULT 6 COMMENT '總步驟數',
    currentStep INT DEFAULT 0 COMMENT '目前步驟',
    currentStepName VARCHAR(100) COMMENT '目前步驟名稱',
    progressPercent INT DEFAULT 0 COMMENT '進度百分比（0-100）',
    startedAt DATETIME COMMENT '開始處理時間',
    completedAt DATETIME COMMENT '完成時間',
    errorMessage TEXT COMMENT '錯誤訊息',
    retryCount INT DEFAULT 0 COMMENT '重試次數',
    jobMetadata JSON COMMENT '任務相關資料JSON',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_job_status (jobStatus),
    INDEX idx_doc_id (doc_id),
    INDEX idx_job_type (jobType),
    INDEX idx_priority_status (jobPriority, jobStatus),
    CONSTRAINT fk_job_doc FOREIGN KEY (doc_id) REFERENCES documents(doc_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ETL任務資料表';

-- ETL 任務日誌資料表
CREATE TABLE etl_job_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日誌ID，自動遞增的主鍵',
    job_id INT NOT NULL COMMENT '任務ID',
    stepNumber INT COMMENT '步驟編號',
    stepName VARCHAR(100) COMMENT '步驟名稱',
    logLevel VARCHAR(20) DEFAULT 'info' COMMENT '日誌等級（debug, info, warning, error）',
    logMessage TEXT COMMENT '日誌訊息',
    logData JSON COMMENT '額外日誌資料JSON',
    executionTime INT COMMENT '執行時間（毫秒）',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_job_id (job_id),
    INDEX idx_log_level (logLevel),
    CONSTRAINT fk_log_job FOREIGN KEY (job_id) REFERENCES etl_jobs(job_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ETL任務日誌資料表';

-- 文件查看日誌資料表
CREATE TABLE document_view_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日誌ID，自動遞增的主鍵',
    doc_id INT NOT NULL COMMENT '文件ID',
    user_id INT COMMENT '使用者ID',
    viewType VARCHAR(20) DEFAULT 'view' COMMENT '操作類型（view查看, download下載, preview預覽）',
    viewDuration INT COMMENT '瀏覽時長（秒）',
    fromSource VARCHAR(50) COMMENT '來源（search搜尋, rag智能查詢, browse瀏覽, link連結）',
    fromQueryId INT COMMENT '來源查詢ID（如從RAG查詢點擊）',
    clientIp VARCHAR(50) COMMENT '客戶端IP',
    userAgent VARCHAR(500) COMMENT '瀏覽器User-Agent',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_doc_id (doc_id),
    INDEX idx_user_id (user_id),
    INDEX idx_view_type (viewType),
    INDEX idx_create_date (create_date),
    CONSTRAINT fk_view_doc FOREIGN KEY (doc_id) REFERENCES documents(doc_id) ON DELETE CASCADE,
    CONSTRAINT fk_view_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件查看日誌資料表';

-- 系統操作日誌資料表
CREATE TABLE operation_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日誌ID，自動遞增的主鍵',
    user_id INT COMMENT '使用者ID',
    userNo VARCHAR(50) COMMENT '使用者帳號',
    moduleName VARCHAR(50) COMMENT '模組名稱（user, document, category, role, system）',
    actionType VARCHAR(50) NOT NULL COMMENT '操作類型（create, update, delete, export, import）',
    targetType VARCHAR(50) COMMENT '操作對象類型',
    target_id INT COMMENT '操作對象ID',
    targetName VARCHAR(255) COMMENT '操作對象名稱',
    actionDesc TEXT COMMENT '操作描述',
    beforeData JSON COMMENT '變更前資料JSON',
    afterData JSON COMMENT '變更後資料JSON',
    clientIp VARCHAR(50) COMMENT '客戶端IP',
    userAgent VARCHAR(500) COMMENT '瀏覽器User-Agent',
    resultStatus CHAR(1) DEFAULT 'Y' COMMENT '操作結果（Y成功, N失敗）',
    errorMessage TEXT COMMENT '錯誤訊息',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    INDEX idx_user_id (user_id),
    INDEX idx_module (moduleName),
    INDEX idx_action (actionType),
    INDEX idx_target (targetType, target_id),
    INDEX idx_create_date (create_date),
    INDEX idx_result (resultStatus)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系統操作日誌資料表';

-- ============================================================
-- 5. 系統設定相關資料表
-- ============================================================

-- 系統參數設定資料表
CREATE TABLE system_configs (
    config_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '設定ID，自動遞增的主鍵',
    configGroup VARCHAR(50) NOT NULL COMMENT '設定群組（system, rag, upload, security）',
    configKey VARCHAR(100) NOT NULL COMMENT '設定鍵值',
    configValue TEXT COMMENT '設定值',
    configType VARCHAR(20) DEFAULT 'string' COMMENT '資料類型（string, number, boolean, json）',
    configDesc VARCHAR(255) COMMENT '設定說明',
    isEncrypted CHAR(1) DEFAULT 'N' COMMENT '是否加密儲存（Y/N）',
    isEditable CHAR(1) DEFAULT 'Y' COMMENT '是否可編輯（Y/N）',
    create_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
    update_date DATETIME COMMENT '更新日期',
    updated_by VARCHAR(100) COMMENT '更新人員',
    UNIQUE INDEX idx_config_key (configGroup, configKey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系統參數設定資料表';

-- ============================================================
-- 6. 初始資料
-- ============================================================

-- 預設角色
INSERT INTO roles (roleCode, roleName, roleDesc, permissions, roleLevel, roleStatus, create_date, updated_by) VALUES
('admin', '系統管理員', '擁有系統所有功能的完整權限', '["user:*","document:*","category:*","role:*","system:*","rag:*","report:*"]', 1, 'Y', NOW(), 'system'),
('manager', '部門主管', '部門內文件與人員管理權限', '["user:read","user:dept","document:*","category:read","rag:*","report:dept"]', 3, 'Y', NOW(), 'system'),
('km_admin', '知識管理員', '知識庫與文件管理權限', '["document:*","category:*","rag:*","report:read"]', 5, 'Y', NOW(), 'system'),
('user', '一般使用者', '基本文件查詢與RAG使用權限', '["document:read","document:upload:dept","rag:query","report:personal"]', 10, 'Y', NOW(), 'system');

-- 預設部門
INSERT INTO departments (deptCode, deptName, deptLevel, sortOrder, deptStatus, create_date, updated_by) VALUES
('HQ', '總公司', 1, 1, 'Y', NOW(), 'system'),
('IT', '資訊部', 2, 2, 'Y', NOW(), 'system'),
('HR', '人力資源部', 2, 3, 'Y', NOW(), 'system'),
('RD', '研發部', 2, 4, 'Y', NOW(), 'system'),
('QA', '品質保證部', 2, 5, 'Y', NOW(), 'system'),
('SALES', '業務部', 2, 6, 'Y', NOW(), 'system');

-- 預設文件分類
INSERT INTO categories (categoryCode, categoryName, categoryLevel, iconClass, sortOrder, categoryStatus, create_date, updated_by) VALUES
('POLICY', '政策文件', 1, 'bi-file-earmark-ruled', 1, 'Y', NOW(), 'system'),
('TECH', '技術規範', 1, 'bi-file-earmark-code', 2, 'Y', NOW(), 'system'),
('MANUAL', '操作手冊', 1, 'bi-book', 3, 'Y', NOW(), 'system'),
('TRAINING', '培訓資料', 1, 'bi-mortarboard', 4, 'Y', NOW(), 'system'),
('MEETING', '會議紀錄', 1, 'bi-calendar-event', 5, 'Y', NOW(), 'system'),
('PROJECT', '專案文件', 1, 'bi-folder', 6, 'Y', NOW(), 'system');

-- 預設系統設定
INSERT INTO system_configs (configGroup, configKey, configValue, configType, configDesc, create_date, updated_by) VALUES
-- RAG 設定
('rag', 'embedding_model', 'text-embedding-3-large', 'string', 'OpenAI Embedding模型', NOW(), 'system'),
('rag', 'embedding_dimensions', '3072', 'number', '向量維度', NOW(), 'system'),
('rag', 'llm_model', 'gpt-4-turbo', 'string', 'LLM對話模型', NOW(), 'system'),
('rag', 'top_k', '5', 'number', '檢索Top K數量', NOW(), 'system'),
('rag', 'similarity_threshold', '0.7', 'number', '相似度閾值', NOW(), 'system'),
('rag', 'max_context_tokens', '4000', 'number', '最大上下文Token數', NOW(), 'system'),
-- 文件上傳設定
('upload', 'max_file_size', '52428800', 'number', '最大檔案大小（50MB）', NOW(), 'system'),
('upload', 'allowed_extensions', '["pdf","docx","xlsx","pptx","txt","md"]', 'json', '允許的副檔名', NOW(), 'system'),
('upload', 'chunk_size', '500', 'number', '分塊Token數', NOW(), 'system'),
('upload', 'chunk_overlap', '100', 'number', '分塊重疊Token數', NOW(), 'system'),
-- 安全設定
('security', 'password_min_length', '8', 'number', '密碼最小長度', NOW(), 'system'),
('security', 'login_fail_limit', '5', 'number', '登入失敗鎖定次數', NOW(), 'system'),
('security', 'session_timeout', '3600', 'number', 'Session逾時秒數', NOW(), 'system'),
('security', 'jwt_expire_hours', '24', 'number', 'JWT Token有效時數', NOW(), 'system');


-- ============================================================
-- 完成
-- ============================================================
