-- 进销存数据开放平台 - 数据库表结构
-- 数据库: jc_open_platform

-- ========== 客户表 ==========
CREATE TABLE IF NOT EXISTS `clients` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `client_no` varchar(50) DEFAULT NULL COMMENT '客户序号',
  `name` varchar(200) NOT NULL COMMENT '客户名称',
  `client_type` varchar(20) NOT NULL COMMENT '客户类型: supplier/distributor/hospital/other',
  `contact_person` varchar(100) DEFAULT NULL COMMENT '联系人',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `status` tinyint(1) DEFAULT '1' COMMENT '1=启用 0=禁用',
  `remark` text COMMENT '备注',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_client_no` (`client_no`),
  KEY `idx_client_type` (`client_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='上下游客户表';

-- ========== API密钥表 ==========
CREATE TABLE IF NOT EXISTS `api_keys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `client_id` bigint(20) unsigned NOT NULL COMMENT '客户ID',
  `api_key` varchar(64) NOT NULL COMMENT 'API Key 公开标识',
  `api_secret_hash` varchar(256) NOT NULL COMMENT 'API Secret 原始值（用于HMAC验证）',
  `status` tinyint(1) DEFAULT '1' COMMENT '1=启用 0=禁用',
  `rate_limit_per_minute` int(11) DEFAULT '60' COMMENT '每分钟限流次数',
  `rate_limit_per_day` int(11) DEFAULT '5000' COMMENT '每天限流次数',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_api_key` (`api_key`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API密钥表';

-- ========== 权限配置表 ==========
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `api_key_id` bigint(20) unsigned NOT NULL COMMENT 'API Key ID',
  `allow_chuku` tinyint(1) DEFAULT '0' COMMENT '是否允许销售接口 1=允许 0=禁止',
  `allow_ruku` tinyint(1) DEFAULT '0' COMMENT '是否允许入库接口 1=允许 0=禁止',
  `allow_full_data` tinyint(1) DEFAULT '0' COMMENT '是否允许全量数据 1=允许 0=过滤',
  `data_mode` varchar(20) DEFAULT 'filter' COMMENT '数据过滤模式: filter=按接口权限过滤, custom=自定义过滤规则, full=全量数据',
  `filter_field` varchar(50) DEFAULT NULL COMMENT '过滤字段: 销方全称/供方全称/销方序号/供方序号',
  `filter_values` json DEFAULT NULL COMMENT '过滤值 JSON 数组',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_api_key_id` (`api_key_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限配置表';

-- ========== 管理员表 ==========
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码（bcrypt加密）',
  `status` tinyint(1) DEFAULT '1' COMMENT '1=启用 0=禁用',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';

-- ========== API调用日志表 ==========
CREATE TABLE IF NOT EXISTS `api_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `api_key_id` bigint(20) unsigned NOT NULL COMMENT 'API Key ID',
  `client_id` bigint(20) unsigned NOT NULL COMMENT '客户ID',
  `endpoint` varchar(100) NOT NULL COMMENT '接口路径',
  `request_params` json DEFAULT NULL COMMENT '请求参数',
  `response_code` int(11) DEFAULT NULL COMMENT '响应状态码',
  `response_body` json DEFAULT NULL COMMENT '响应体摘要',
  `record_count` int(11) DEFAULT '0' COMMENT '返回记录数',
  `sql_query` text COMMENT '执行的SQL语句',
  `duration_ms` int(11) DEFAULT NULL COMMENT '执行耗时（毫秒）',
  `ip_address` varchar(50) DEFAULT NULL COMMENT '客户端IP',
  `error_message` varchar(500) DEFAULT NULL COMMENT '错误信息',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_api_key_id` (`api_key_id`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_endpoint` (`endpoint`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_response_code` (`response_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='API调用日志表';

-- ========== 初始化数据 ==========
-- 插入默认管理员（用户名: admin，密码: admin123）
INSERT INTO `admin_users` (`username`, `password`, `status`) VALUES
('admin', '$2y$10$Kds/RYu23SNPlroW3NnyveSP5Yd2JiYWklgx5zLE5Y/ORYlzi8F52', 1)
ON DUPLICATE KEY UPDATE `id` = `id`;

-- ========== 外键约束 ==========
ALTER TABLE `api_keys`
ADD CONSTRAINT `fk_api_keys_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;

ALTER TABLE `permissions`
ADD CONSTRAINT `fk_permissions_api_key` FOREIGN KEY (`api_key_id`) REFERENCES `api_keys` (`id`) ON DELETE CASCADE;

ALTER TABLE `api_logs`
ADD CONSTRAINT `fk_api_logs_api_key` FOREIGN KEY (`api_key_id`) REFERENCES `api_keys` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_api_logs_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;