-- eduSign 學生線上簽署系統 - 資料庫更新腳本 v2
-- 建立時間: 2025-12-27
-- 功能: 新增簽署文件相關資料表

USE `eduSign`;

-- =====================================================
-- 簽署文件資料表
-- =====================================================
CREATE TABLE IF NOT EXISTS `t_document` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL COMMENT '文件標題',
    `description` TEXT COMMENT '文件說明',
    `file_type` ENUM('pdf', 'online') NOT NULL DEFAULT 'online' COMMENT '文件類型',
    `file_path` VARCHAR(500) DEFAULT NULL COMMENT 'PDF檔案路徑',
    `content` LONGTEXT COMMENT '線上編輯內容(HTML)',
    `created_by` VARCHAR(100) NOT NULL COMMENT '建立者uid',
    `created_by_type` ENUM('admin', 'teacher') NOT NULL COMMENT '建立者類型',
    `created_by_name` VARCHAR(100) NOT NULL COMMENT '建立者姓名',
    `target_type` SET('teacher', 'student', 'guest') NOT NULL COMMENT '簽署對象類型',
    `target_class` VARCHAR(200) DEFAULT NULL COMMENT '指定班級(逗號分隔,空白=全部)',
    `start_date` DATE NOT NULL COMMENT '開始簽署日期',
    `end_date` DATE NOT NULL COMMENT '結束簽署日期',
    `status` ENUM('draft', 'active', 'closed') NOT NULL DEFAULT 'draft' COMMENT '文件狀態',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    INDEX `idx_created_by` (`created_by`),
    INDEX `idx_status` (`status`),
    INDEX `idx_date_range` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='簽署文件表';

-- =====================================================
-- 簽署記錄資料表
-- =====================================================
CREATE TABLE IF NOT EXISTS `t_signature` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `document_id` INT NOT NULL COMMENT '文件ID',
    `uid` VARCHAR(100) NOT NULL COMMENT '簽署者uid',
    `user_type` ENUM('teacher', 'student', 'guest') NOT NULL COMMENT '簽署者類型',
    `user_name` VARCHAR(100) NOT NULL COMMENT '簽署者姓名',
    `user_class` VARCHAR(50) DEFAULT NULL COMMENT '簽署者班級',
    `user_seatno` VARCHAR(10) DEFAULT NULL COMMENT '簽署者座號',
    `signature_image` LONGTEXT NOT NULL COMMENT '簽名圖片(Base64)',
    `signed_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '簽署時間',
    `ip_address` VARCHAR(45) DEFAULT NULL COMMENT '簽署IP',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_document_user` (`document_id`, `uid`),
    INDEX `idx_document_id` (`document_id`),
    INDEX `idx_uid` (`uid`),
    INDEX `idx_signed_at` (`signed_at`),
    CONSTRAINT `fk_signature_document` FOREIGN KEY (`document_id`) REFERENCES `t_document` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='簽署記錄表';

-- =====================================================
-- 文件管理者資料表 (授權教師管理文件)
-- =====================================================
CREATE TABLE IF NOT EXISTS `t_document_manager` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `teacher_uid` VARCHAR(100) NOT NULL COMMENT '教師uid',
    `teacher_name` VARCHAR(100) NOT NULL COMMENT '教師姓名',
    `granted_by` VARCHAR(100) NOT NULL COMMENT '授權者uid',
    `granted_by_name` VARCHAR(100) NOT NULL COMMENT '授權者姓名',
    `granted_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '授權時間',
    `status` ENUM('active', 'revoked') NOT NULL DEFAULT 'active' COMMENT '狀態',
    `revoked_at` DATETIME DEFAULT NULL COMMENT '撤銷時間',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_teacher` (`teacher_uid`),
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文件管理者授權表';

-- =====================================================
-- 系統日誌資料表 (記錄重要操作)
-- =====================================================
CREATE TABLE IF NOT EXISTS `t_system_log` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `action` VARCHAR(50) NOT NULL COMMENT '動作類型',
    `uid` VARCHAR(100) NOT NULL COMMENT '操作者uid',
    `user_type` VARCHAR(20) NOT NULL COMMENT '操作者類型',
    `target_type` VARCHAR(50) DEFAULT NULL COMMENT '目標類型',
    `target_id` VARCHAR(100) DEFAULT NULL COMMENT '目標ID',
    `detail` TEXT COMMENT '詳細資訊(JSON)',
    `ip_address` VARCHAR(45) DEFAULT NULL COMMENT 'IP位址',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '記錄時間',
    PRIMARY KEY (`id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_uid` (`uid`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系統日誌表';
