-- 创建客户资料表
CREATE TABLE IF NOT EXISTS `customers` (
  `Id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID，自增',
  `CustomerCode` VARCHAR(50) NOT NULL UNIQUE COMMENT '客户代码（唯一标识）',
  `CustomerName` VARCHAR(100) NOT NULL COMMENT '客户名称',
  `ContactName` VARCHAR(50) NULL COMMENT '联系人姓名',
  `ContactPhone` VARCHAR(20) NULL COMMENT '联系电话',
  `ContactEmail` VARCHAR(100) NULL COMMENT '联系邮箱',
  `Status` CHAR(1) NOT NULL DEFAULT 'Y' COMMENT '客户状态（Y: 启用, N: 禁用）',
  `CreatedAt` DATETIME NOT NULL COMMENT '创建时间',
  `UpdatedAt` DATETIME NOT NULL COMMENT '更新时间',
  `Remark` VARCHAR(500) NULL COMMENT '备注信息',
  INDEX `idx_customer_code` (`CustomerCode`),
  INDEX `idx_status` (`Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建客户API信息表
CREATE TABLE IF NOT EXISTS `customer_apis` (
  `Id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID，自增',
  `CustomerId` INT UNSIGNED NOT NULL COMMENT '客户ID（关联customers表）',
  `CustomerCode` VARCHAR(50) NOT NULL COMMENT '客户代码（与customers表关联）',
  `ApiKey` VARCHAR(100) NOT NULL COMMENT 'API密钥',
  `Status` CHAR(1) NOT NULL DEFAULT 'Y' COMMENT 'API密钥状态（Y: 启用, N: 禁用）',
  `ExpireDate` DATETIME NULL COMMENT 'API密钥过期时间',
  `CreatedAt` DATETIME NOT NULL COMMENT '创建时间',
  `UpdatedAt` DATETIME NOT NULL COMMENT '更新时间',
  `Remark` VARCHAR(500) NULL COMMENT '备注信息',
  INDEX `idx_customer_id` (`CustomerId`),
  INDEX `idx_customer_code` (`CustomerCode`),
  INDEX `idx_api_key` (`ApiKey`),
  INDEX `idx_status` (`Status`),
  INDEX `idx_expire_date` (`ExpireDate`)
  -- 添加外键约束（可选，如果需要严格的参照完整性）
  -- FOREIGN KEY (`CustomerId`) REFERENCES `customers` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  -- FOREIGN KEY (`CustomerCode`) REFERENCES `customers` (`CustomerCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建示例数据（可选）
-- INSERT INTO `customers` (`CustomerCode`, `CustomerName`, `ContactName`, `ContactPhone`, `ContactEmail`, `Status`, `CreatedAt`, `UpdatedAt`) VALUES
-- ('CUSTOMER001', '示例客户1', '张三', '13800138001', 'zhangsan@example.com', 'Y', NOW(), NOW()),
-- ('CUSTOMER002', '示例客户2', '李四', '13800138002', 'lisi@example.com', 'Y', NOW(), NOW());

-- INSERT INTO `customer_apis` (`CustomerId`, `CustomerCode`, `ApiKey`, `Status`, `ExpireDate`, `CreatedAt`, `UpdatedAt`) VALUES
-- (1, 'CUSTOMER001', 'apikey_1234567890', 'Y', DATE_ADD(NOW(), INTERVAL 1 YEAR), NOW(), NOW()),
-- (2, 'CUSTOMER002', 'apikey_0987654321', 'Y', DATE_ADD(NOW(), INTERVAL 1 YEAR), NOW(), NOW());