kknongchang_2506/app/Module/ThirdParty/Databases/GenerateSql/thirdparty_tables.sql

-- ThirdParty模块数据库表结构
-- 创建时间: 2025-06-13
-- 说明: 第三方服务管理模块的数据库表

-- 第三方服务配置表
CREATE TABLE `kku_thirdparty_services` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL COMMENT '服务名称',
  `code` varchar(50) NOT NULL COMMENT '服务代码（唯一标识）',
  `type` varchar(20) NOT NULL COMMENT '服务类型',
  `provider` varchar(50) NOT NULL COMMENT '服务提供商',
  `description` text COMMENT '服务描述',
  `base_url` varchar(255) COMMENT '基础URL',
  `version` varchar(20) DEFAULT 'v1' COMMENT 'API版本',
  `auth_type` varchar(20) NOT NULL COMMENT '认证类型',
  `status` varchar(20) NOT NULL DEFAULT 'INACTIVE' COMMENT '服务状态',
  `priority` int(11) DEFAULT 0 COMMENT '优先级（数字越小优先级越高）',
  `timeout` int(11) DEFAULT 30 COMMENT '超时时间（秒）',
  `retry_times` int(11) DEFAULT 3 COMMENT '重试次数',
  `retry_delay` int(11) DEFAULT 1000 COMMENT '重试延迟（毫秒）',
  `config` json COMMENT '服务配置信息',
  `headers` json COMMENT '默认请求头',
  `params` json COMMENT '默认参数',
  `webhook_url` varchar(255) COMMENT 'Webhook回调地址',
  `webhook_secret` varchar(100) COMMENT 'Webhook密钥',
  `health_check_url` varchar(255) COMMENT '健康检查URL',
  `health_check_interval` int(11) DEFAULT 300 COMMENT '健康检查间隔（秒）',
  `last_health_check` timestamp NULL COMMENT '最后健康检查时间',
  `health_status` varchar(20) DEFAULT 'UNKNOWN' COMMENT '健康状态',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_type` (`type`),
  KEY `idx_provider` (`provider`),
  KEY `idx_status` (`status`),
  KEY `idx_priority` (`priority`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='第三方服务配置表';

-- 认证凭证表
CREATE TABLE `kku_thirdparty_credentials` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `service_id` bigint(20) unsigned NOT NULL COMMENT '服务ID',
  `name` varchar(100) NOT NULL COMMENT '凭证名称',
  `type` varchar(20) NOT NULL COMMENT '凭证类型',
  `credentials` json NOT NULL COMMENT '凭证信息（加密存储）',
  `environment` varchar(20) DEFAULT 'production' COMMENT '环境（production/staging/development）',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '是否激活',
  `expires_at` timestamp NULL COMMENT '过期时间',
  `last_used_at` timestamp NULL COMMENT '最后使用时间',
  `usage_count` bigint(20) DEFAULT 0 COMMENT '使用次数',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_service_id` (`service_id`),
  KEY `idx_type` (`type`),
  KEY `idx_environment` (`environment`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_expires_at` (`expires_at`),
  CONSTRAINT `fk_credentials_service` FOREIGN KEY (`service_id`) REFERENCES `kku_thirdparty_services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='认证凭证表';

-- 调用日志表
CREATE TABLE `kku_thirdparty_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `service_id` bigint(20) unsigned NOT NULL COMMENT '服务ID',
  `credential_id` bigint(20) unsigned COMMENT '凭证ID',
  `request_id` varchar(50) NOT NULL COMMENT '请求ID（用于追踪）',
  `method` varchar(10) NOT NULL COMMENT '请求方法',
  `url` varchar(500) NOT NULL COMMENT '请求URL',
  `headers` json COMMENT '请求头',
  `params` json COMMENT '请求参数',
  `body` text COMMENT '请求体',
  `response_status` int(11) COMMENT '响应状态码',
  `response_headers` json COMMENT '响应头',
  `response_body` text COMMENT '响应体',
  `response_time` int(11) COMMENT '响应时间（毫秒）',
  `error_message` text COMMENT '错误信息',
  `level` varchar(20) DEFAULT 'INFO' COMMENT '日志级别',
  `user_id` bigint(20) unsigned COMMENT '用户ID',
  `ip_address` varchar(45) COMMENT 'IP地址',
  `user_agent` varchar(500) COMMENT 'User Agent',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_service_id` (`service_id`),
  KEY `idx_credential_id` (`credential_id`),
  KEY `idx_request_id` (`request_id`),
  KEY `idx_method` (`method`),
  KEY `idx_response_status` (`response_status`),
  KEY `idx_level` (`level`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_logs_service` FOREIGN KEY (`service_id`) REFERENCES `kku_thirdparty_services` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_logs_credential` FOREIGN KEY (`credential_id`) REFERENCES `kku_thirdparty_credentials` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='调用日志表';

-- 配额管理表
CREATE TABLE `kku_thirdparty_quotas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `service_id` bigint(20) unsigned NOT NULL COMMENT '服务ID',
  `type` varchar(20) NOT NULL COMMENT '配额类型',
  `limit_value` bigint(20) NOT NULL COMMENT '限制值',
  `used_value` bigint(20) DEFAULT 0 COMMENT '已使用值',
  `reset_at` timestamp NULL COMMENT '重置时间',
  `window_start` timestamp NULL COMMENT '时间窗口开始',
  `window_end` timestamp NULL COMMENT '时间窗口结束',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '是否激活',
  `alert_threshold` decimal(5,2) DEFAULT 80.00 COMMENT '告警阈值（百分比）',
  `is_exceeded` tinyint(1) DEFAULT 0 COMMENT '是否已超限',
  `exceeded_at` timestamp NULL COMMENT '超限时间',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_service_type` (`service_id`, `type`),
  KEY `idx_type` (`type`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_is_exceeded` (`is_exceeded`),
  KEY `idx_reset_at` (`reset_at`),
  CONSTRAINT `fk_quotas_service` FOREIGN KEY (`service_id`) REFERENCES `kku_thirdparty_services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配额管理表';

-- 监控记录表
CREATE TABLE `kku_thirdparty_monitors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `service_id` bigint(20) unsigned NOT NULL COMMENT '服务ID',
  `check_type` varchar(20) NOT NULL COMMENT '检查类型（health/performance/availability）',
  `status` varchar(20) NOT NULL COMMENT '检查状态',
  `response_time` int(11) COMMENT '响应时间（毫秒）',
  `status_code` int(11) COMMENT 'HTTP状态码',
  `error_message` text COMMENT '错误信息',
  `details` json COMMENT '详细信息',
  `checked_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '检查时间',
  PRIMARY KEY (`id`),
  KEY `idx_service_id` (`service_id`),
  KEY `idx_check_type` (`check_type`),
  KEY `idx_status` (`status`),
  KEY `idx_checked_at` (`checked_at`),
  CONSTRAINT `fk_monitors_service` FOREIGN KEY (`service_id`) REFERENCES `kku_thirdparty_services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='监控记录表';

-- 插入默认数据
INSERT INTO `kku_thirdparty_services` (`name`, `code`, `type`, `provider`, `description`, `auth_type`, `status`) VALUES
('阿里云短信服务', 'aliyun_sms', 'SMS', 'ALIYUN', '阿里云短信发送服务', 'API_KEY', 'INACTIVE'),
('腾讯云短信服务', 'tencent_sms', 'SMS', 'TENCENT', '腾讯云短信发送服务', 'API_KEY', 'INACTIVE'),
('阿里云邮件推送', 'aliyun_email', 'EMAIL', 'ALIYUN', '阿里云邮件推送服务', 'API_KEY', 'INACTIVE'),
('极光推送', 'jpush', 'PUSH', 'JPUSH', '极光移动推送服务', 'API_KEY', 'INACTIVE'),
('支付宝支付', 'alipay', 'PAYMENT', 'ALIPAY', '支付宝开放平台支付服务', 'SIGNATURE', 'INACTIVE'),
('微信支付', 'wechat_pay', 'PAYMENT', 'WECHAT', '微信支付API服务', 'SIGNATURE', 'INACTIVE'),
('阿里云OSS', 'aliyun_oss', 'STORAGE', 'ALIYUN', '阿里云对象存储服务', 'API_KEY', 'INACTIVE'),
('高德地图', 'amap', 'MAP', 'AMAP', '高德地图API服务', 'API_KEY', 'INACTIVE'),
('百度AI', 'baidu_ai', 'AI', 'BAIDU', '百度人工智能服务', 'API_KEY', 'INACTIVE'),
('腾讯验证码', 'tencent_captcha', 'CAPTCHA', 'TENCENT', '腾讯天御验证码服务', 'API_KEY', 'INACTIVE');