-- CmsPro v5 核心系统表结构
-- 字符集: utf8mb4 / 排序规则: utf8mb4_unicode_ci / 引擎: InnoDB

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ==================== Laravel 迁移追踪表 ====================
CREATE TABLE IF NOT EXISTS `migrations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `migration` VARCHAR(255) NOT NULL,
    `batch` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==================== 系统核心表 ====================

-- 前台用户表
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL DEFAULT '',
    `email` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `password` VARCHAR(255) NOT NULL DEFAULT '',
    `nickname` VARCHAR(50) DEFAULT NULL,
    `avatar` VARCHAR(255) DEFAULT NULL,
    `gender` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-未知 1-男 2-女',
    `birthday` DATE DEFAULT NULL,
    `signature` VARCHAR(255) DEFAULT NULL,
    `group_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '关联user_groups表',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0-禁用 1-启用',
    `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `phone_verified_at` TIMESTAMP NULL DEFAULT NULL,
    `last_login_at` TIMESTAMP NULL DEFAULT NULL,
    `last_login_ip` VARCHAR(45) DEFAULT NULL,
    `login_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `remember_token` VARCHAR(100) DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `users_username_unique` (`username`),
    UNIQUE KEY `users_email_unique` (`email`),
    KEY `users_status_index` (`status`),
    KEY `users_group_id_index` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='前台用户表';

-- 后台管理员表
CREATE TABLE IF NOT EXISTS `admin_users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `name` VARCHAR(50) DEFAULT NULL,
    `email` VARCHAR(100) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `avatar` VARCHAR(255) DEFAULT NULL,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0-禁用 1-启用',
    `last_login_at` TIMESTAMP NULL DEFAULT NULL,
    `last_login_ip` VARCHAR(45) DEFAULT NULL,
    `login_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `password_changed_at` TIMESTAMP NULL DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `admin_users_username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台管理员表';

-- 后台角色表
CREATE TABLE IF NOT EXISTS `admin_roles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `parent_id` BIGINT UNSIGNED DEFAULT NULL,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `admin_roles_code_unique` (`code`),
    KEY `admin_roles_parent_id_index` (`parent_id`),
    KEY `admin_roles_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台角色表';

-- 预置角色数据
INSERT INTO `admin_roles` (`id`, `name`, `code`, `description`, `parent_id`, `sort`, `status`, `create_time`, `update_time`) VALUES
(1, '超级管理员', 'super_admin', '拥有所有权限', NULL, 0, 1, NOW(), NOW()),
(2, '普通管理员', 'admin', '基础管理权限', 1, 1, 1, NOW(), NOW());

-- 后台权限表
CREATE TABLE IF NOT EXISTS `admin_permissions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `code` VARCHAR(100) NOT NULL,
    `module` VARCHAR(50) DEFAULT NULL,
    `app_id` VARCHAR(50) DEFAULT NULL COMMENT 'NULL表示系统权限',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `admin_permissions_code_unique` (`code`),
    KEY `admin_permissions_module_index` (`module`),
    KEY `admin_permissions_app_id_index` (`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台权限表';

-- 预置系统权限数据（app_id 为 NULL 的系统权限）
INSERT INTO `admin_permissions` (`id`, `name`, `code`, `module`, `app_id`, `status`, `create_time`, `update_time`) VALUES
(1, '查看用户列表', 'admin.user.index', 'user', NULL, 1, NOW(), NOW()),
(2, '新增用户', 'admin.user.store', 'user', NULL, 1, NOW(), NOW()),
(3, '查看用户详情', 'admin.user.show', 'user', NULL, 1, NOW(), NOW()),
(4, '更新用户', 'admin.user.update', 'user', NULL, 1, NOW(), NOW()),
(5, '删除用户', 'admin.user.destroy', 'user', NULL, 1, NOW(), NOW()),
(6, '启用/禁用用户', 'admin.user.status', 'user', NULL, 1, NOW(), NOW()),
(7, '重置密码', 'admin.user.reset-password', 'user', NULL, 1, NOW(), NOW()),
(8, '查看角色列表', 'admin.role.index', 'role', NULL, 1, NOW(), NOW()),
(9, '新增角色', 'admin.role.store', 'role', NULL, 1, NOW(), NOW()),
(10, '查看角色详情', 'admin.role.show', 'role', NULL, 1, NOW(), NOW()),
(11, '更新角色', 'admin.role.update', 'role', NULL, 1, NOW(), NOW()),
(12, '删除角色', 'admin.role.destroy', 'role', NULL, 1, NOW(), NOW()),
(13, '查看角色权限', 'admin.role.permissions', 'role', NULL, 1, NOW(), NOW()),
(14, '更新角色权限', 'admin.role.permissions.update', 'role', NULL, 1, NOW(), NOW()),
(15, '查看角色菜单', 'admin.role.menus', 'role', NULL, 1, NOW(), NOW()),
(16, '更新角色菜单', 'admin.role.menus.update', 'role', NULL, 1, NOW(), NOW()),
(17, '角色树', 'admin.role.tree', 'role', NULL, 1, NOW(), NOW()),
(18, '查看菜单列表', 'admin.menu.index', 'menu', NULL, 1, NOW(), NOW()),
(19, '新增菜单', 'admin.menu.store', 'menu', NULL, 1, NOW(), NOW()),
(20, '查看菜单详情', 'admin.menu.show', 'menu', NULL, 1, NOW(), NOW()),
(21, '更新菜单', 'admin.menu.update', 'menu', NULL, 1, NOW(), NOW()),
(22, '删除菜单', 'admin.menu.destroy', 'menu', NULL, 1, NOW(), NOW()),
(23, '菜单树', 'admin.menu.tree', 'menu', NULL, 1, NOW(), NOW()),
(24, '查看权限列表', 'admin.permission.index', 'permission', NULL, 1, NOW(), NOW()),
(25, '权限树', 'admin.permission.tree', 'permission', NULL, 1, NOW(), NOW()),
(26, '查看配置分组', 'admin.config-group.index', 'config', NULL, 1, NOW(), NOW()),
(27, '新增配置分组', 'admin.config-group.store', 'config', NULL, 1, NOW(), NOW()),
(28, '更新配置分组', 'admin.config-group.update', 'config', NULL, 1, NOW(), NOW()),
(29, '删除配置分组', 'admin.config-group.destroy', 'config', NULL, 1, NOW(), NOW()),
(30, '查看配置项', 'admin.config-item.index', 'config', NULL, 1, NOW(), NOW()),
(31, '新增配置项', 'admin.config-item.store', 'config', NULL, 1, NOW(), NOW()),
(32, '更新配置项', 'admin.config-item.update', 'config', NULL, 1, NOW(), NOW()),
(33, '删除配置项', 'admin.config-item.destroy', 'config', NULL, 1, NOW(), NOW()),
(34, '批量更新配置项', 'admin.config-item.batch', 'config', NULL, 1, NOW(), NOW()),
(35, '查看系统设置', 'admin.setting.index', 'setting', NULL, 1, NOW(), NOW()),
(36, '查看附件列表', 'admin.attachment.index', 'attachment', NULL, 1, NOW(), NOW()),
(37, '上传附件', 'admin.attachment.upload', 'attachment', NULL, 1, NOW(), NOW()),
(38, '查看附件详情', 'admin.attachment.show', 'attachment', NULL, 1, NOW(), NOW()),
(39, '删除附件', 'admin.attachment.destroy', 'attachment', NULL, 1, NOW(), NOW()),
(40, '批量删除附件', 'admin.attachment.batch-delete', 'attachment', NULL, 1, NOW(), NOW()),
(41, '查看附件分组', 'admin.attachment-group.index', 'attachment', NULL, 1, NOW(), NOW()),
(42, '新增附件分组', 'admin.attachment-group.store', 'attachment', NULL, 1, NOW(), NOW()),
(43, '查看系统日志', 'admin.log.index', 'log', NULL, 1, NOW(), NOW()),
(44, '查看系统日志详情', 'admin.log.show', 'log', NULL, 1, NOW(), NOW()),
(45, '下载系统日志', 'admin.log.download', 'log', NULL, 1, NOW(), NOW()),
(46, '删除系统日志', 'admin.log.destroy', 'log', NULL, 1, NOW(), NOW()),
(47, '清空系统日志', 'admin.log.clear', 'log', NULL, 1, NOW(), NOW()),
(48, '查看系统日志统计', 'admin.log.stats', 'log', NULL, 1, NOW(), NOW()),
(49, '查看操作日志', 'admin.operation-log.index', 'operation-log', NULL, 1, NOW(), NOW()),
(50, '查看操作日志详情', 'admin.operation-log.show', 'operation-log', NULL, 1, NOW(), NOW()),
(51, '删除操作日志', 'admin.operation-log.destroy', 'operation-log', NULL, 1, NOW(), NOW()),
(52, '清理操作日志', 'admin.operation-log.clear', 'operation-log', NULL, 1, NOW(), NOW()),
(53, '查看数据表列表', 'admin.database.index', 'database', NULL, 1, NOW(), NOW()),
(54, '查看表结构', 'admin.database.structure', 'database', NULL, 1, NOW(), NOW()),
(55, '创建数据表', 'admin.database.add-table', 'database', NULL, 1, NOW(), NOW()),
(56, '编辑数据表', 'admin.database.edit-table', 'database', NULL, 1, NOW(), NOW()),
(57, '复制数据表', 'admin.database.copy-table', 'database', NULL, 1, NOW(), NOW()),
(58, '删除数据表', 'admin.database.delete-table', 'database', NULL, 1, NOW(), NOW()),
(59, '优化数据表', 'admin.database.optimize', 'database', NULL, 1, NOW(), NOW()),
(60, '修复数据表', 'admin.database.repair', 'database', NULL, 1, NOW(), NOW()),
(61, '备份数据表', 'admin.database.backup', 'database', NULL, 1, NOW(), NOW()),
(62, '恢复数据表', 'admin.database.recover', 'database', NULL, 1, NOW(), NOW()),
(63, '查看表数据', 'admin.database.check', 'database', NULL, 1, NOW(), NOW()),
(64, '添加表数据', 'admin.database.table-add', 'database', NULL, 1, NOW(), NOW()),
(65, '编辑表数据', 'admin.database.table-edit', 'database', NULL, 1, NOW(), NOW()),
(66, '删除表数据', 'admin.database.table-delete', 'database', NULL, 1, NOW(), NOW()),
(67, '添加字段', 'admin.database.add-fields', 'database', NULL, 1, NOW(), NOW()),
(68, '编辑字段', 'admin.database.edit-field', 'database', NULL, 1, NOW(), NOW()),
(69, '删除字段', 'admin.database.delete-field', 'database', NULL, 1, NOW(), NOW()),
(70, '添加字段索引', 'admin.database.field-index', 'database', NULL, 1, NOW(), NOW()),
(71, '查看前台用户列表', 'admin.front-user.index', 'front-user', NULL, 1, NOW(), NOW()),
(72, '新增前台用户', 'admin.front-user.store', 'front-user', NULL, 1, NOW(), NOW()),
(73, '查看前台用户详情', 'admin.front-user.show', 'front-user', NULL, 1, NOW(), NOW()),
(74, '更新前台用户', 'admin.front-user.update', 'front-user', NULL, 1, NOW(), NOW()),
(75, '删除前台用户', 'admin.front-user.destroy', 'front-user', NULL, 1, NOW(), NOW()),
(76, '启用/禁用前台用户', 'admin.front-user.status', 'front-user', NULL, 1, NOW(), NOW()),
(77, '重置前台用户密码', 'admin.front-user.reset-password', 'front-user', NULL, 1, NOW(), NOW()),
(78, '批量删除前台用户', 'admin.front-user.batch-delete', 'front-user', NULL, 1, NOW(), NOW()),
(79, '查看用户组列表', 'admin.user-group.index', 'user-group', NULL, 1, NOW(), NOW()),
(80, '新增用户组', 'admin.user-group.store', 'user-group', NULL, 1, NOW(), NOW()),
(81, '查看用户组详情', 'admin.user-group.show', 'user-group', NULL, 1, NOW(), NOW()),
(82, '更新用户组', 'admin.user-group.update', 'user-group', NULL, 1, NOW(), NOW()),
(83, '删除用户组', 'admin.user-group.destroy', 'user-group', NULL, 1, NOW(), NOW());

-- 后台菜单表
CREATE TABLE IF NOT EXISTS `admin_menus` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `parent_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `name` VARCHAR(50) NOT NULL,
    `icon` VARCHAR(100) DEFAULT NULL,
    `path` VARCHAR(255) DEFAULT NULL,
    `code` VARCHAR(100) DEFAULT NULL COMMENT '菜单编码（幂等标识）',
    `component` VARCHAR(255) DEFAULT NULL,
    `permission_code` VARCHAR(100) DEFAULT NULL,
    `type` TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT '1-目录 2-菜单 3-按钮',
    `visible` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `terminal_type` VARCHAR(20) NOT NULL DEFAULT 'admin',
    `app_id` VARCHAR(50) DEFAULT NULL COMMENT 'NULL表示系统菜单',
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `admin_menus_parent_id_index` (`parent_id`),
    KEY `admin_menus_terminal_type_index` (`terminal_type`),
    KEY `admin_menus_app_id_index` (`app_id`),
    KEY `idx_admin_menus_app_code` (`app_id`, `code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台菜单表';

-- 菜单终端类型表
CREATE TABLE IF NOT EXISTS `admin_menu_terminals` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `type` VARCHAR(20) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `admin_menu_terminals_type_unique` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='菜单终端类型表';

-- 管理员-角色关联表
CREATE TABLE IF NOT EXISTS `admin_user_roles` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '关联admin_users表',
    `role_id` BIGINT UNSIGNED NOT NULL COMMENT '关联admin_roles表',
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `admin_user_roles_user_role_unique` (`user_id`, `role_id`),
    KEY `admin_user_roles_role_id_index` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理员-角色关联表';

-- 角色-权限关联表
CREATE TABLE IF NOT EXISTS `admin_role_permissions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role_id` BIGINT UNSIGNED NOT NULL COMMENT '关联admin_roles表',
    `permission_id` BIGINT UNSIGNED NOT NULL COMMENT '关联admin_permissions表',
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `admin_role_permissions_role_permission_unique` (`role_id`, `permission_id`),
    KEY `admin_role_permissions_permission_id_index` (`permission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色-权限关联表';

-- 预置角色-权限关联：超级管理员(super_admin, role_id=1)拥有所有系统权限
INSERT INTO `admin_role_permissions` (`role_id`, `permission_id`, `create_time`, `update_time`) VALUES
(1, 1, NOW(), NOW()),
(1, 2, NOW(), NOW()),
(1, 3, NOW(), NOW()),
(1, 4, NOW(), NOW()),
(1, 5, NOW(), NOW()),
(1, 6, NOW(), NOW()),
(1, 7, NOW(), NOW()),
(1, 8, NOW(), NOW()),
(1, 9, NOW(), NOW()),
(1, 10, NOW(), NOW()),
(1, 11, NOW(), NOW()),
(1, 12, NOW(), NOW()),
(1, 13, NOW(), NOW()),
(1, 14, NOW(), NOW()),
(1, 15, NOW(), NOW()),
(1, 16, NOW(), NOW()),
(1, 17, NOW(), NOW()),
(1, 18, NOW(), NOW()),
(1, 19, NOW(), NOW()),
(1, 20, NOW(), NOW()),
(1, 21, NOW(), NOW()),
(1, 22, NOW(), NOW()),
(1, 23, NOW(), NOW()),
(1, 24, NOW(), NOW()),
(1, 25, NOW(), NOW()),
(1, 26, NOW(), NOW()),
(1, 27, NOW(), NOW()),
(1, 28, NOW(), NOW()),
(1, 29, NOW(), NOW()),
(1, 30, NOW(), NOW()),
(1, 31, NOW(), NOW()),
(1, 32, NOW(), NOW()),
(1, 33, NOW(), NOW()),
(1, 34, NOW(), NOW()),
(1, 35, NOW(), NOW()),
(1, 36, NOW(), NOW()),
(1, 37, NOW(), NOW()),
(1, 38, NOW(), NOW()),
(1, 39, NOW(), NOW()),
(1, 40, NOW(), NOW()),
(1, 41, NOW(), NOW()),
(1, 42, NOW(), NOW()),
(1, 43, NOW(), NOW()),
(1, 44, NOW(), NOW()),
(1, 45, NOW(), NOW()),
(1, 46, NOW(), NOW()),
(1, 47, NOW(), NOW()),
(1, 48, NOW(), NOW()),
(1, 49, NOW(), NOW()),
(1, 50, NOW(), NOW()),
(1, 51, NOW(), NOW()),
(1, 52, NOW(), NOW()),
(1, 53, NOW(), NOW()),
(1, 54, NOW(), NOW()),
(1, 55, NOW(), NOW()),
(1, 56, NOW(), NOW()),
(1, 57, NOW(), NOW()),
(1, 58, NOW(), NOW()),
(1, 59, NOW(), NOW()),
(1, 60, NOW(), NOW()),
(1, 61, NOW(), NOW()),
(1, 62, NOW(), NOW()),
(1, 63, NOW(), NOW()),
(1, 64, NOW(), NOW()),
(1, 65, NOW(), NOW()),
(1, 66, NOW(), NOW()),
(1, 67, NOW(), NOW()),
(1, 68, NOW(), NOW()),
(1, 69, NOW(), NOW()),
(1, 70, NOW(), NOW()),
(1, 71, NOW(), NOW()),
(1, 72, NOW(), NOW()),
(1, 73, NOW(), NOW()),
(1, 74, NOW(), NOW()),
(1, 75, NOW(), NOW()),
(1, 76, NOW(), NOW()),
(1, 77, NOW(), NOW()),
(1, 78, NOW(), NOW()),
(1, 79, NOW(), NOW()),
(1, 80, NOW(), NOW()),
(1, 81, NOW(), NOW()),
(1, 82, NOW(), NOW()),
(1, 83, NOW(), NOW());

-- 角色-菜单关联表
CREATE TABLE IF NOT EXISTS `admin_role_menus` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role_id` BIGINT UNSIGNED NOT NULL COMMENT '关联admin_roles表',
    `menu_id` BIGINT UNSIGNED NOT NULL COMMENT '关联admin_menus表',
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `admin_role_menus_role_menu_unique` (`role_id`, `menu_id`),
    KEY `admin_role_menus_menu_id_index` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色-菜单关联表';

-- 操作日志表
CREATE TABLE IF NOT EXISTS `admin_operation_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '关联admin_users表',
    `module` VARCHAR(50) DEFAULT NULL,
    `action` VARCHAR(50) DEFAULT NULL,
    `method` VARCHAR(10) DEFAULT NULL,
    `path` VARCHAR(500) DEFAULT NULL,
    `params` TEXT DEFAULT NULL,
    `ip` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(500) DEFAULT NULL,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `error_message` TEXT DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `admin_operation_logs_user_id_index` (`user_id`),
    KEY `admin_operation_logs_module_index` (`module`),
    KEY `admin_operation_logs_create_time_index` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- ==================== 配置模块 ====================

-- 配置分组表
CREATE TABLE IF NOT EXISTS `config_groups` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `app_id` VARCHAR(50) DEFAULT NULL COMMENT 'NULL表示系统配置组',
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `config_groups_code_unique` (`code`),
    KEY `config_groups_app_id_index` (`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置分组表';

-- 配置项表
CREATE TABLE IF NOT EXISTS `config_items` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `group_id` BIGINT UNSIGNED NOT NULL COMMENT '关联config_groups表',
    `name` VARCHAR(100) NOT NULL,
    `code` VARCHAR(150) NOT NULL,
    `value` TEXT NULL,
    `type` VARCHAR(20) NOT NULL DEFAULT 'text' COMMENT 'text/switch/select/image/number/textarea/password',
    `options` JSON DEFAULT NULL,
    `tips` VARCHAR(255) DEFAULT NULL,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `config_items_group_code_unique` (`group_id`, `code`),
    KEY `config_items_group_id_index` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置项表';

-- ==================== 附件模块 ====================

-- 附件表
CREATE TABLE IF NOT EXISTS `attachments` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(255) NOT NULL,
    `path` VARCHAR(500) NOT NULL,
    `url` VARCHAR(500) DEFAULT NULL,
    `mime_type` VARCHAR(100) DEFAULT NULL,
    `extension` VARCHAR(10) DEFAULT NULL,
    `size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `hash` VARCHAR(64) DEFAULT NULL,
    `disk` VARCHAR(20) NOT NULL DEFAULT 'local',
    `category` VARCHAR(50) DEFAULT NULL,
    `group_id` BIGINT UNSIGNED DEFAULT NULL,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `attachments_user_id_index` (`user_id`),
    KEY `attachments_hash_index` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='附件表';

-- 附件分组表
CREATE TABLE IF NOT EXISTS `attachment_groups` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `attachment_groups_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='附件分组表';

-- ==================== 内容管理模块 ====================

-- 内容模型表
CREATE TABLE IF NOT EXISTS `content_models` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `alias` VARCHAR(50) DEFAULT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `table_name` VARCHAR(50) DEFAULT NULL,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `is_default` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `field_config` JSON DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='内容模型表';

-- 内容模型字段表
CREATE TABLE IF NOT EXISTS `content_model_fields` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `model_id` BIGINT UNSIGNED NOT NULL COMMENT '关联content_models表',
    `name` VARCHAR(50) NOT NULL,
    `field` VARCHAR(50) NOT NULL,
    `type` VARCHAR(20) NOT NULL DEFAULT 'varchar',
    `length` INT UNSIGNED DEFAULT NULL,
    `decimal_precision` INT UNSIGNED DEFAULT NULL,
    `default_value` VARCHAR(255) DEFAULT NULL,
    `form_type` VARCHAR(30) DEFAULT NULL,
    `form_config` JSON DEFAULT NULL,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `content_model_fields_model_id_index` (`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='内容模型字段表';

-- 内容分类表
CREATE TABLE IF NOT EXISTS `content_categories` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `parent_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `model_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '关联content_models表',
    `name` VARCHAR(100) NOT NULL,
    `icon` VARCHAR(100) DEFAULT NULL,
    `image` VARCHAR(255) DEFAULT NULL,
    `description` VARCHAR(500) DEFAULT NULL,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `content_categories_parent_id_index` (`parent_id`),
    KEY `content_categories_model_id_index` (`model_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='内容分类表';

-- 内容表
CREATE TABLE IF NOT EXISTS `contents` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `category_id` BIGINT UNSIGNED DEFAULT NULL,
    `model_id` BIGINT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `content` LONGTEXT DEFAULT NULL,
    `summary` VARCHAR(500) DEFAULT NULL,
    `image` VARCHAR(255) DEFAULT NULL,
    `author` VARCHAR(50) DEFAULT NULL,
    `source` VARCHAR(100) DEFAULT NULL,
    `publish_at` TIMESTAMP NULL DEFAULT NULL,
    `pv` INT UNSIGNED NOT NULL DEFAULT 0,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0-草稿 1-已发布 2-已下架',
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `contents_category_id_index` (`category_id`),
    KEY `contents_status_index` (`status`),
    KEY `contents_create_time_index` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='内容表';

-- ==================== 用户模块 ====================

-- 前台用户组表
CREATE TABLE IF NOT EXISTS `user_groups` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `icon` VARCHAR(50) DEFAULT NULL,
    `color` VARCHAR(20) DEFAULT NULL,
    `is_default` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `sort` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `user_groups_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='前台用户组表';

-- 默认用户组
INSERT INTO `user_groups` (`name`, `code`, `description`, `is_default`, `sort`, `status`) VALUES
('默认用户组', 'default', '新注册用户的默认用户组', 1, 0, 1);

-- 第三方登录绑定表
CREATE TABLE IF NOT EXISTS `user_third_party` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL COMMENT '关联users表',
    `platform` VARCHAR(20) NOT NULL COMMENT 'github/wechat等',
    `open_id` VARCHAR(100) NOT NULL,
    `union_id` VARCHAR(100) DEFAULT NULL,
    `nickname` VARCHAR(50) DEFAULT NULL,
    `avatar` VARCHAR(255) DEFAULT NULL,
    `access_token` VARCHAR(500) DEFAULT NULL,
    `refresh_token` VARCHAR(500) DEFAULT NULL,
    `expires_at` TIMESTAMP NULL DEFAULT NULL,
    `raw_data` JSON DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `user_third_party_platform_openid_unique` (`platform`, `open_id`),
    KEY `user_third_party_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='第三方登录绑定表';

-- ==================== 应用管理模块 ====================

-- 应用表
CREATE TABLE IF NOT EXISTS `apps` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `app_id` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `version` VARCHAR(20) NOT NULL DEFAULT '1.0.0',
    `author` VARCHAR(100) DEFAULT NULL,
    `icon` VARCHAR(255) DEFAULT NULL,
    `path` VARCHAR(255) DEFAULT NULL,
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-已安装 1-已启用 2-已禁用',
    `is_system` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `manifest` JSON DEFAULT NULL,
    `install_time` TIMESTAMP NULL DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `apps_app_id_unique` (`app_id`),
    KEY `apps_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用表';

-- 应用依赖关系表
CREATE TABLE IF NOT EXISTS `app_dependencies` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `app_id` VARCHAR(50) NOT NULL,
    `depend_app_id` VARCHAR(50) NOT NULL,
    `depend_version` VARCHAR(20) DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `app_dependencies_app_depend_unique` (`app_id`, `depend_app_id`),
    KEY `app_dependencies_depend_app_id_index` (`depend_app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用依赖关系表';

-- 应用操作日志表
CREATE TABLE IF NOT EXISTS `app_operation_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `app_id` VARCHAR(50) NOT NULL,
    `operation` VARCHAR(20) NOT NULL COMMENT 'install/uninstall/enable/disable/upgrade',
    `version_from` VARCHAR(20) DEFAULT NULL,
    `version_to` VARCHAR(20) DEFAULT NULL,
    `operator_id` BIGINT UNSIGNED DEFAULT NULL,
    `result` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'success/failed',
    `error_message` TEXT DEFAULT NULL,
    `detail` JSON DEFAULT NULL,
    `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY `app_operation_logs_app_id_index` (`app_id`),
    KEY `app_operation_logs_create_time_index` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用操作日志表';

-- 应用备份表
CREATE TABLE IF NOT EXISTS `app_backups` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `app_id` VARCHAR(100) NOT NULL COMMENT '应用ID',
    `app_name` VARCHAR(100) NOT NULL COMMENT '应用名称',
    `app_version` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '备份时应用版本',
    `backup_type` VARCHAR(20) NOT NULL DEFAULT 'full' COMMENT '备份类型：full=完整备份',
    `file_path` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备份文件路径',
    `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '备份文件大小(字节)',
    `db_tables` JSON DEFAULT NULL COMMENT '备份的数据库表列表',
    `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：0=失败 1=成功',
    `operator_id` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '操作人ID',
    `error_message` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '错误信息',
    `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',
    `update_time` DATETIME DEFAULT NULL COMMENT '更新时间',
    KEY `app_backups_app_id_index` (`app_id`),
    KEY `app_backups_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应用备份表';

SET FOREIGN_KEY_CHECKS = 1;

-- 预插入迁移记录：install.sql 已包含的表/列变更，避免 migrate 重复执行冲突
INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2026_06_01_000001_add_status_to_admin_permissions', 1),
('2026_06_16_000001_add_remember_token_to_users_table', 1),
('2026_06_18_000001_create_app_backups_table', 1),
('2026_07_25_000002_extend_config_items_code_length', 1),
('2026_07_26_000000_add_code_to_admin_menus', 1),
('2026_07_26_000001_backfill_admin_menu_code', 1);
