-- System Tool Service Database Schema
-- Run this script to create required tables

-- System Configs Table
CREATE TABLE IF NOT EXISTS `system_configs` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT NOT NULL DEFAULT 1,
    `config_group` VARCHAR(64) NOT NULL COMMENT 'Config group name',
    `config_key` VARCHAR(128) NOT NULL COMMENT 'Config key',
    `config_value` TEXT COMMENT 'Config value',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_tenant_group_key` (`tenant_id`, `config_group`, `config_key`),
    INDEX `idx_tenant_group` (`tenant_id`, `config_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='System configuration table';

-- Operation Logs Table
CREATE TABLE IF NOT EXISTS `operation_logs` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT NOT NULL DEFAULT 1,
    `user_id` INT COMMENT 'User ID',
    `user_name` VARCHAR(64) COMMENT 'User name',
    `module` VARCHAR(64) COMMENT 'Module name',
    `action` VARCHAR(64) COMMENT 'Action name',
    `method` VARCHAR(10) COMMENT 'HTTP method',
    `path` VARCHAR(255) COMMENT 'Request path',
    `ip` VARCHAR(45) COMMENT 'Client IP',
    `user_agent` VARCHAR(512) COMMENT 'User agent',
    `request_params` TEXT COMMENT 'Request parameters',
    `response_code` INT COMMENT 'HTTP response code',
    `execution_time` INT COMMENT 'Execution time in ms',
    `remark` VARCHAR(512) COMMENT 'Remark',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_module` (`module`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_user_module_action` (`user_id`, `module`, `action`),
    INDEX `idx_tenant_created` (`tenant_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Operation logs table';

-- Audit Logs Table
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT NOT NULL DEFAULT 1,
    `user_id` INT COMMENT 'User ID',
    `user_name` VARCHAR(64) COMMENT 'User name',
    `module` VARCHAR(64) COMMENT 'Module name',
    `action` VARCHAR(64) COMMENT 'Action name',
    `target_type` VARCHAR(64) COMMENT 'Target entity type',
    `target_id` BIGINT COMMENT 'Target entity ID',
    `old_value` TEXT COMMENT 'Old value (JSON)',
    `new_value` TEXT COMMENT 'New value (JSON)',
    `ip` VARCHAR(45) COMMENT 'Client IP',
    `user_agent` VARCHAR(512) COMMENT 'User agent',
    `result` VARCHAR(32) COMMENT 'Operation result',
    `error_message` VARCHAR(512) COMMENT 'Error message if failed',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_module_action` (`module`, `action`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_target` (`target_type`, `target_id`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_tenant_created` (`tenant_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Audit logs table';

-- Pull Records Table (for data cleanup)
CREATE TABLE IF NOT EXISTS `pull_records` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT NOT NULL DEFAULT 1,
    `platform` VARCHAR(32) COMMENT 'Platform name (pdd/jd/dy)',
    `pull_type` VARCHAR(32) COMMENT 'Pull type (order/product/waybill)',
    `status` VARCHAR(32) COMMENT 'Status (pending/success/failed)',
    `start_time` DATETIME COMMENT 'Pull start time',
    `end_time` DATETIME COMMENT 'Pull end time',
    `total_count` INT DEFAULT 0 COMMENT 'Total pulled count',
    `success_count` INT DEFAULT 0 COMMENT 'Success count',
    `fail_count` INT DEFAULT 0 COMMENT 'Failed count',
    `error_message` VARCHAR(1024) COMMENT 'Error message if failed',
    `request_params` TEXT COMMENT 'Request parameters',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_platform` (`platform`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_tenant_status` (`tenant_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Pull records table for tracking sync operations';

-- Insert default system configs
INSERT INTO `system_configs` (`tenant_id`, `config_group`, `config_key`, `config_value`) VALUES
(1, 'basic', 'site_name', 'ERP SaaS 系统'),
(1, 'basic', 'site_url', 'https://erp.example.com'),
(1, 'basic', 'site_logo', '/logo.png'),
(1, 'basic', 'copyright', '© 2024 ERP SaaS'),
(1, 'order', 'auto_approve', 'false'),
(1, 'order', 'approve_timeout_minutes', '30'),
(1, 'order', 'auto_reject_timeout_minutes', '60'),
(1, 'order', 'enable_stock_freeze', 'true'),
(1, 'order', 'enable_audit_log', 'true'),
(1, 'stock', 'enable_warning', 'true'),
(1, 'stock', 'default_warning_threshold', '10'),
(1, 'stock', 'enable_negative_stock', 'false'),
(1, 'stock', 'auto_adjust_warning', 'false'),
(1, 'express', 'default_express', 'SF'),
(1, 'express', 'enable_electronic_waybill', 'true'),
(1, 'express', 'waybill_cache_hours', '24'),
(1, 'notify', 'enable_dingtalk', 'false'),
(1, 'notify', 'dingtalk_webhook', ''),
(1, 'notify', 'enable_sms', 'false'),
(1, 'notify', 'sms_provider', 'aliyun'),
(1, 'notify', 'notify_audit_timeout', 'true'),
(1, 'notify', 'notify_stock_warning', 'true'),
(1, 'security', 'enable_mfa', 'true'),
(1, 'security', 'session_timeout_minutes', '120'),
(1, 'security', 'max_login_attempts', '5'),
(1, 'security', 'password_min_length', '8'),
(1, 'security', 'enable_captcha', 'true'),
(1, 'storage', 'driver', 'local'),
(1, 'storage', 'local_path', '/storage/uploads'),
(1, 'storage', 'cos_bucket', ''),
(1, 'storage', 'cos_region', 'ap-guangzhou'),
(1, 'storage', 'cos_secret_id', ''),
(1, 'storage', 'cos_secret_key', '')
ON DUPLICATE KEY UPDATE `config_value` = VALUES(`config_value`);