SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` char(36) NOT NULL COMMENT 'User UUID',
  `username` varchar(50) NOT NULL COMMENT 'Username',
  `password` varchar(255) NOT NULL COMMENT 'Password Hash',
  `email` varchar(100) DEFAULT NULL COMMENT 'Email',
  `phone` varchar(20) DEFAULT NULL COMMENT 'Phone Number',
  `balance` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Account Balance',
  `role` enum('user','admin') NOT NULL DEFAULT 'user' COMMENT 'User Role',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:Active, 0:Banned',
  `is_realname_verified` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1:Verified',
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `phone_verified_at` timestamp NULL DEFAULT NULL,
  `register_ip` varchar(45) DEFAULT NULL,
  `login_ip` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  UNIQUE KEY `uniq_email` (`email`),
  UNIQUE KEY `uniq_phone` (`phone`),
  UNIQUE KEY `uniq_uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Account Table';

-- ----------------------------
-- Table structure for user_profiles
-- ----------------------------
DROP TABLE IF EXISTS `user_profiles`;
CREATE TABLE `user_profiles` (
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `real_name` varchar(50) DEFAULT NULL,
  `id_card_no` varchar(30) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT 0 COMMENT '0:Unknown, 1:Male, 2:Female',
  `province` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_profile_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Profile Information';

-- ----------------------------
-- Table structure for social_accounts
-- ----------------------------
DROP TABLE IF EXISTS `social_accounts`;
CREATE TABLE `social_accounts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `provider` varchar(20) NOT NULL COMMENT 'qq, wx, github, etc.',
  `openid` varchar(100) NOT NULL,
  `unionid` varchar(100) DEFAULT NULL,
  `nickname` varchar(100) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `access_token` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_provider_openid` (`provider`, `openid`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_social_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Social Login Accounts';

-- ----------------------------
-- Table structure for domains
-- ----------------------------
DROP TABLE IF EXISTS `domains`;
CREATE TABLE `domains` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT 'Domain Name e.g., example.com',
  `description` text DEFAULT NULL,
  `price_year` decimal(10,2) NOT NULL DEFAULT 0.00,
  `price_month` decimal(10,2) NOT NULL DEFAULT 0.00,
  `stocks` int(11) NOT NULL DEFAULT 0 COMMENT '-1 for unlimited',
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `need_beian` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Need ICP Filing',
  `dns_provider` varchar(50) NOT NULL DEFAULT 'aliyun' COMMENT 'aliyun, dnspod, etc.',
  `dns_config` text DEFAULT NULL COMMENT 'JSON config for specific domain if unique',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:Active, 0:Inactive',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Domain Products';

-- ----------------------------
-- Table structure for domain_records
-- ----------------------------
DROP TABLE IF EXISTS `domain_records`;
CREATE TABLE `domain_records` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `domain_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(63) NOT NULL COMMENT 'Subdomain prefix',
  `type` varchar(10) NOT NULL DEFAULT 'A' COMMENT 'A, CNAME, TXT, etc.',
  `value` varchar(255) NOT NULL COMMENT 'Record Value',
  `line` varchar(20) DEFAULT 'default' COMMENT 'ISP Line',
  `mx` int(11) DEFAULT NULL,
  `ttl` int(11) DEFAULT 600,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `provider_record_id` varchar(100) DEFAULT NULL COMMENT 'ID from DNS Provider',
  `expire_at` timestamp NULL DEFAULT NULL COMMENT 'For rented subdomains',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_domain_name` (`domain_id`, `name`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_record_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_record_domain` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Subdomain Records';

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL COMMENT 'Order Number',
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `type` enum('recharge','buy_domain','renew_domain') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:Pending, 1:Paid, 2:Cancelled, 3:Refunded',
  `pay_method` varchar(20) DEFAULT NULL COMMENT 'alipay, wxpay, balance',
  `pay_time` timestamp NULL DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Order Records';

-- ----------------------------
-- Table structure for tickets
-- ----------------------------
DROP TABLE IF EXISTS `tickets`;
CREATE TABLE `tickets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `subject` varchar(200) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:Open, 1:Replied, 2:Closed',
  `priority` tinyint(1) DEFAULT 0 COMMENT '0:Low, 1:Normal, 2:High',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_ticket_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Support Tickets';

-- ----------------------------
-- Table structure for ticket_replies
-- ----------------------------
DROP TABLE IF EXISTS `ticket_replies`;
CREATE TABLE `ticket_replies` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `content` text NOT NULL,
  `is_admin` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ticket_id` (`ticket_id`),
  CONSTRAINT `fk_reply_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Ticket Replies';

-- ----------------------------
-- Table structure for ssl_certificates
-- ----------------------------
DROP TABLE IF EXISTS `ssl_certificates`;
CREATE TABLE `ssl_certificates` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `domain` varchar(100) NOT NULL,
  `cert_content` text,
  `key_content` text,
  `provider` varchar(50) DEFAULT 'letsencrypt',
  `status` tinyint(1) DEFAULT 0 COMMENT '0:Pending, 1:Issued, 2:Failed',
  `expire_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_ssl_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='SSL Certificates';

-- ----------------------------
-- Table structure for settings
-- ----------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `key` varchar(100) NOT NULL,
  `value` text,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='System Global Settings';

-- ----------------------------
-- Table structure for alerts
-- ----------------------------
DROP TABLE IF EXISTS `alerts`;
CREATE TABLE `alerts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'info',
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_alert_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Notifications';

-- ----------------------------
-- Table structure for sensitive_words
-- ----------------------------
DROP TABLE IF EXISTS `sensitive_words`;
CREATE TABLE `sensitive_words` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `word` varchar(50) NOT NULL,
  `type` enum('ban','replace') NOT NULL DEFAULT 'ban',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Sensitive Words Filter';

-- ----------------------------
-- Table structure for plugins
-- ----------------------------
DROP TABLE IF EXISTS `plugins`;
CREATE TABLE `plugins` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT 'Plugin Name/Identifier',
  `title` varchar(100) NOT NULL COMMENT 'Display Name',
  `version` varchar(20) NOT NULL DEFAULT '1.0.0',
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:Installed(Disabled), 1:Enabled',
  `config` text DEFAULT NULL COMMENT 'Plugin Configuration JSON',
  `expire_time` datetime DEFAULT NULL COMMENT 'Expiration Time, NULL = Permanent',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Installed Plugins';

-- ----------------------------
-- Table structure for login_logs
-- ----------------------------
DROP TABLE IF EXISTS `login_logs`;
CREATE TABLE `login_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `username` varchar(50) DEFAULT NULL COMMENT '用户名快照',
  `ip` varchar(45) NOT NULL,
  `location` varchar(100) DEFAULT NULL COMMENT 'IP归属地',
  `user_agent` varchar(500) DEFAULT NULL,
  `device` varchar(50) DEFAULT NULL COMMENT '设备类型',
  `browser` varchar(50) DEFAULT NULL COMMENT '浏览器',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:成功 0:失败',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注(失败原因等)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户登录日志';

-- ----------------------------
-- Table structure for operation_logs
-- ----------------------------
DROP TABLE IF EXISTS `operation_logs`;
CREATE TABLE `operation_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '管理员ID',
  `admin_name` varchar(50) DEFAULT NULL COMMENT '管理员名称快照',
  `module` varchar(50) DEFAULT NULL COMMENT '模块: user/dns/system/ticket/payment',
  `action` varchar(50) NOT NULL COMMENT '操作类型',
  `description` varchar(500) DEFAULT NULL COMMENT '操作描述',
  `method` varchar(10) NOT NULL COMMENT 'GET/POST/PUT/DELETE',
  `path` varchar(255) NOT NULL COMMENT '请求路径',
  `params` text DEFAULT NULL COMMENT '请求参数JSON',
  `response` text DEFAULT NULL COMMENT '响应数据JSON',
  `ip` varchar(45) NOT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL COMMENT '请求耗时(ms)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_module` (`module`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台操作日志';

-- ----------------------------
-- Table structure for payment_logs
-- ----------------------------
DROP TABLE IF EXISTS `payment_logs`;
CREATE TABLE `payment_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `gateway` varchar(20) NOT NULL,
  `data` text COMMENT 'Raw callback data',
  `status` tinyint(1) NOT NULL COMMENT '0:Fail, 1:Success',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Payment Callback Logs';

-- ----------------------------
-- Table structure for sms_logs
-- ----------------------------
DROP TABLE IF EXISTS `sms_logs`;
CREATE TABLE `sms_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `phone` varchar(20) NOT NULL COMMENT '手机号',
  `content` varchar(500) DEFAULT NULL COMMENT '短信内容',
  `template` varchar(50) DEFAULT NULL COMMENT '模板ID',
  `channel` varchar(30) DEFAULT NULL COMMENT '发送通道: aliyun/tencent',
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1:成功 0:失败',
  `result` text DEFAULT NULL COMMENT 'API返回结果',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信发送日志';

-- ----------------------------
-- Table structure for user_domains
-- ----------------------------
DROP TABLE IF EXISTS `user_domains`;
CREATE TABLE `user_domains` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `domain_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '关联系统域名ID',
  `domain` varchar(100) NOT NULL COMMENT '完整域名 e.g. test.example.com',
  `rr` varchar(63) DEFAULT NULL COMMENT '主机记录',
  `dns_provider` varchar(50) DEFAULT 'aliyun' COMMENT 'DNS服务商',
  `settings` json DEFAULT NULL COMMENT '解析设置JSON: {type, value, line, ttl}',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:正常, 2:暂停, 0:过期',
  `expired_at` datetime DEFAULT NULL COMMENT '到期时间',
  `suspended_at` datetime DEFAULT NULL COMMENT '暂停时间',
  `suspend_reason` varchar(255) DEFAULT NULL COMMENT '暂停原因',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_domain_id` (`domain_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_userdomain_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户域名/解析记录';

-- ----------------------------
-- Table structure for user_groups
-- ----------------------------
DROP TABLE IF EXISTS `user_groups`;
CREATE TABLE `user_groups` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL COMMENT '分组名称',
  `description` varchar(255) DEFAULT NULL COMMENT '分组描述',
  `discount` decimal(3,2) DEFAULT 1.00 COMMENT '折扣比例 0.00-1.00',
  `max_domains` int(11) DEFAULT 10 COMMENT '最大域名数',
  `max_records` int(11) DEFAULT 20 COMMENT '每域名最大解析数',
  `permissions` json DEFAULT NULL COMMENT '权限配置JSON',
  `is_default` tinyint(1) DEFAULT 0 COMMENT '是否默认分组',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户分组';

-- ----------------------------
-- Table structure for order_logs
-- ----------------------------
DROP TABLE IF EXISTS `order_logs`;
CREATE TABLE `order_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `action` varchar(50) NOT NULL COMMENT '操作类型',
  `description` text COMMENT '操作描述',
  `operator` varchar(50) DEFAULT NULL COMMENT '操作人',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  CONSTRAINT `fk_orderlog_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单操作日志';

-- ----------------------------
-- Table structure for cron_tasks
-- ----------------------------
DROP TABLE IF EXISTS `cron_tasks`;
CREATE TABLE `cron_tasks` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '任务名称',
  `type` varchar(50) NOT NULL COMMENT '任务类型',
  `command` varchar(255) NOT NULL COMMENT '执行命令',
  `schedule` varchar(50) NOT NULL COMMENT 'Cron表达式',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:启用, 0:禁用',
  `last_run_at` datetime DEFAULT NULL COMMENT '上次执行时间',
  `next_run_at` datetime DEFAULT NULL COMMENT '下次执行时间',
  `last_output` text COMMENT '上次执行输出',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='计划任务';

-- ----------------------------
-- Table structure for cards
-- ----------------------------
DROP TABLE IF EXISTS `cards`;
CREATE TABLE `cards` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL COMMENT '卡密',
  `type` varchar(20) NOT NULL DEFAULT 'recharge' COMMENT '类型: recharge/vip',
  `amount` decimal(10,2) DEFAULT 0.00 COMMENT '充值金额',
  `days` int(11) DEFAULT NULL COMMENT 'VIP天数',
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0:未使用, 1:已使用',
  `user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '使用者ID',
  `used_at` datetime DEFAULT NULL COMMENT '使用时间',
  `expired_at` datetime DEFAULT NULL COMMENT '过期时间',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_code` (`code`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值卡密';

-- ----------------------------
-- Table structure for keywords
-- ----------------------------
DROP TABLE IF EXISTS `keywords`;
CREATE TABLE `keywords` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `keyword` varchar(100) NOT NULL COMMENT '关键词',
  `category` varchar(20) DEFAULT 'default' COMMENT '分类: porn/gambling/fraud/other',
  `is_system` tinyint(1) DEFAULT 0 COMMENT '是否系统关键词',
  `user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '用户自定义时的用户ID',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='敏感关键词';

-- ----------------------------
-- Table structure for domain_violations
-- ----------------------------
DROP TABLE IF EXISTS `domain_violations`;
CREATE TABLE `domain_violations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_domain_id` bigint(20) UNSIGNED NOT NULL COMMENT '用户域名ID',
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `domain` varchar(100) NOT NULL COMMENT '违规域名',
  `record_value` varchar(255) DEFAULT NULL COMMENT '解析值',
  `scan_url` varchar(500) DEFAULT NULL COMMENT '扫描URL',
  `violation_type` varchar(20) NOT NULL DEFAULT 'other' COMMENT '违规类型: porn/gambling/fraud/other',
  `matched_keywords` json DEFAULT NULL COMMENT '匹配的关键词',
  `status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT 'pending/processed/ignored',
  `process_action` varchar(50) DEFAULT NULL COMMENT '处理动作',
  `process_remark` text COMMENT '处理备注',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_at` datetime DEFAULT NULL COMMENT '处理时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_domain_id` (`user_domain_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_violation_userdomain` FOREIGN KEY (`user_domain_id`) REFERENCES `user_domains` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_violation_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='域名违规记录';

-- ----------------------------
-- Table structure for domain_bindings
-- ----------------------------
DROP TABLE IF EXISTS `domain_bindings`;
CREATE TABLE `domain_bindings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain_id` bigint(20) UNSIGNED NOT NULL COMMENT '系统域名ID',
  `dns_channel_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'DNS渠道ID',
  `provider_domain_id` varchar(100) DEFAULT NULL COMMENT '服务商域名ID',
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1:正常, 0:异常',
  `last_sync_at` datetime DEFAULT NULL COMMENT '上次同步时间',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_domain_id` (`domain_id`),
  CONSTRAINT `fk_binding_domain` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='域名绑定关系';

-- ----------------------------
-- Alter orders table to add extra field
-- ----------------------------
ALTER TABLE `orders` ADD COLUMN `extra` json DEFAULT NULL COMMENT '附加信息JSON' AFTER `description`;

-- ----------------------------
-- Add group_id to users table
-- ----------------------------
ALTER TABLE `users` ADD COLUMN `group_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT '用户分组ID' AFTER `role`;

SET FOREIGN_KEY_CHECKS = 1;
