-- =====================================================
-- schedule-embedding-api 数据库初始化脚本
-- 数据库: schedule_embedding
-- =====================================================

-- 创建数据库
CREATE DATABASE IF NOT EXISTS schedule_embedding DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE schedule_embedding;

-- =====================================================
-- 1. 操作日志表 (HTTP API 调用审计)
-- =====================================================
CREATE TABLE IF NOT EXISTS operation_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    operation_type VARCHAR(50) NOT NULL COMMENT '操作类型 (INDEX/BATCH_INDEX/DELETE/SEARCH/HYBRID_SEARCH/GET_CHUNK/DELETE_CHUNK)',
    doc_id VARCHAR(255) COMMENT '文档ID',
    chunk_id VARCHAR(255) COMMENT 'chunk ID',
    operator VARCHAR(100) COMMENT '操作人/调用方',
    request_params TEXT COMMENT '请求参数 (JSON格式)',
    response_result TEXT COMMENT '响应结果 (JSON格式)',
    status VARCHAR(20) NOT NULL DEFAULT 'SUCCESS' COMMENT '执行状态 (SUCCESS/FAILED)',
    error_message VARCHAR(1000) COMMENT '错误信息',
    duration_ms BIGINT COMMENT '执行耗时(毫秒)',
    ip_address VARCHAR(50) COMMENT 'IP地址',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    INDEX idx_operation_type (operation_type),
    INDEX idx_doc_id (doc_id),
    INDEX idx_status (status),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表 - 记录HTTP API调用';

-- =====================================================
-- 2. Kafka处理日志表 (Kafka消息处理)
-- =====================================================
CREATE TABLE IF NOT EXISTS kafka_processing_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    message_id VARCHAR(255) COMMENT 'Kafka消息ID/任务ID',
    doc_id VARCHAR(255) COMMENT '文档ID',
    task_type VARCHAR(50) COMMENT '任务类型 (INDEX/UPDATE/DELETE)',
    processing_stage VARCHAR(50) NOT NULL COMMENT '处理阶段 (RECEIVED/PROCESSING/EMBEDDING/STORING/COMPLETED/FAILED)',
    status VARCHAR(20) NOT NULL DEFAULT 'PROCESSING' COMMENT '执行状态 (PROCESSING/SUCCESS/FAILED)',
    error_message VARCHAR(1000) COMMENT '错误信息',
    duration_ms BIGINT COMMENT '执行耗时(毫秒)',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_message_id (message_id),
    INDEX idx_doc_id (doc_id),
    INDEX idx_processing_stage (processing_stage),
    INDEX idx_status (status),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Kafka处理日志表 - 记录Kafka消息处理过程';

-- =====================================================
-- 表说明
-- =====================================================
-- operation_log: 记录所有HTTP API调用（入库、搜索、删除、查询）
-- kafka_processing_log: 记录Kafka消息处理过程（需要启用Kafka）

-- =====================================================
-- 示例查询
-- =====================================================
-- 查看最近的操作日志
-- SELECT * FROM operation_log ORDER BY create_time DESC LIMIT 10;

-- 按操作类型统计
-- SELECT operation_type, COUNT(*) as count FROM operation_log GROUP BY operation_type;

-- 查看某个文档的操作记录
-- SELECT * FROM operation_log WHERE doc_id = 'contract-001';

-- 查看Kafka处理日志（需要启用Kafka）
-- SELECT * FROM kafka_processing_log ORDER BY create_time DESC LIMIT 10;