-- 完整的数据库初始化脚本
-- 使用前请先创建数据库: CREATE DATABASE Simple_CA DEFAULT CHARACTER SET = 'utf8mb4';

USE Simple_CA;

-- 如果表已存在则删除（谨慎使用）
-- DROP TABLE IF EXISTS `crls`;
-- DROP TABLE IF EXISTS `certificates`;
-- DROP TABLE IF EXISTS `ca_requests`;
-- DROP TABLE IF EXISTS `user_tokens`;
-- DROP TABLE IF EXISTS `users`;

-- 创建用户表
CREATE TABLE IF NOT EXISTS `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `username` varchar(16) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `authority` int DEFAULT NULL COMMENT '权限，1表示系统管理员',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_users_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

-- 创建用户Token表
CREATE TABLE IF NOT EXISTS `user_tokens` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `user_id` int NOT NULL,
  `token` varchar(512) NOT NULL COMMENT 'JWT Token',
  `expire_time` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_tokens_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户token表';

-- 创建CA请求表
CREATE TABLE IF NOT EXISTS `ca_requests` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL COMMENT '字段创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted_at` datetime DEFAULT NULL COMMENT '删除时间',
  `user_id` int NOT NULL COMMENT '申请证书的用户ID',
  `state` int unsigned NOT NULL COMMENT '证书状态（1：待审核， 2： 审核通过， 3：审核未通过）',
  `public_key` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '公钥',
  `csr_content` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'CSR文件内容',
  `country` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '国家',
  `province` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '州市',
  `locality` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地区',
  `organization` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '组织',
  `organization_unit_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门',
  `common_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `email_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `idx_ca_requests_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='请求列表';

-- 创建证书表
CREATE TABLE IF NOT EXISTS `certificates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `user_id` int NOT NULL COMMENT '证书拥有者ID',
  `state` int unsigned NOT NULL COMMENT '状态（1 代表在使用中，2代表已撤销或过期）',
  `request_id` int NOT NULL COMMENT '证书请求ID',
  `expire_time` datetime NOT NULL COMMENT '过期时间戳',
  `certificate_content` text CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '证书内容(PEM格式)',
  `serial_number` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '证书序列号',
  PRIMARY KEY (`id`),
  KEY `idx_certificates_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='证书列表';

-- 创建证书吊销列表表
CREATE TABLE IF NOT EXISTS `crls` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `certificate_id` int NOT NULL COMMENT '证书ID',
  `input_time` bigint NOT NULL COMMENT '加入时间戳',
  PRIMARY KEY (`id`),
  KEY `idx_crls_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='证书吊销列表';