-- =====================================================
-- 幼儿园阅读平台数据库扩展脚本
-- 版本: V4
-- 创建时间: 2026-03-14
-- 描述: 添加资源库、主题、标签等其他表
-- =====================================================

-- -----------------------------------------------------
-- 表30: 资源库表 (resource_library)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `resource_library` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` VARCHAR(50) COMMENT '租户ID',
    `name` VARCHAR(200) NOT NULL COMMENT '资源库名称',
    `description` TEXT COMMENT '资源库描述',
    `type` VARCHAR(50) COMMENT '资源库类型',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='资源库表';

-- -----------------------------------------------------
-- 表31: 资源项表 (resource_item)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `resource_item` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `library_id` VARCHAR(50) COMMENT '资源库ID',
    `tenant_id` VARCHAR(50) COMMENT '租户ID',
    `type` VARCHAR(50) COMMENT '资源类型',
    `name` VARCHAR(200) NOT NULL COMMENT '资源名称',
    `code` VARCHAR(50) COMMENT '资源编码',
    `description` TEXT COMMENT '资源描述',
    `quantity` INT DEFAULT 0 COMMENT '数量',
    `available_quantity` INT DEFAULT 0 COMMENT '可用数量',
    `location` VARCHAR(200) COMMENT '存放位置',
    `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_library_id` (`library_id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_type` (`type`),
    KEY `idx_code` (`code`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='资源项表';

-- -----------------------------------------------------
-- 表32: 主题字典表 (theme)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `theme` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `name` VARCHAR(100) NOT NULL COMMENT '主题名称',
    `description` TEXT COMMENT '主题描述',
    `sort_order` INT DEFAULT 0 COMMENT '排序号',
    `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态:ACTIVE、INACTIVE',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_status` (`status`),
    KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='主题字典表';

-- -----------------------------------------------------
-- 表33: 标签表 (tag)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tag` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT COMMENT '租户ID',
    `name` VARCHAR(50) NOT NULL COMMENT '标签名称',
    `type` VARCHAR(50) COMMENT '标签类型',
    `color` VARCHAR(20) COMMENT '标签颜色',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='标签表';

-- -----------------------------------------------------
-- 表34: 租户课程关联表 (tenant_course)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tenant_course` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `course_id` BIGINT NOT NULL COMMENT '课程ID',
    `enabled` TINYINT DEFAULT 1 COMMENT '是否启用',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_course_id` (`course_id`),
    UNIQUE KEY `uk_tenant_course` (`tenant_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='租户课程关联表';

-- -----------------------------------------------------
-- 表35: 租户套餐关联表 (tenant_package)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tenant_package` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `package_id` BIGINT NOT NULL COMMENT '套餐ID',
    `start_date` DATE COMMENT '开始日期',
    `end_date` DATE COMMENT '结束日期',
    `price_paid` BIGINT COMMENT '实付价格',
    `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态:ACTIVE、EXPIRED',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_package_id` (`package_id`),
    KEY `idx_status` (`status`),
    KEY `idx_end_date` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='租户套餐关联表';

-- -----------------------------------------------------
-- 表36: 日程计划表 (schedule_plan)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `schedule_plan` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT NOT NULL COMMENT '租户ID',
    `name` VARCHAR(200) NOT NULL COMMENT '计划名称',
    `class_id` BIGINT COMMENT '班级ID',
    `start_date` DATE NOT NULL COMMENT '开始日期',
    `end_date` DATE NOT NULL COMMENT '结束日期',
    `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '状态',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_class_id` (`class_id`),
    KEY `idx_status` (`status`),
    KEY `idx_start_date` (`start_date`),
    KEY `idx_end_date` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='日程计划表';

-- -----------------------------------------------------
-- 表37: 日程模板表 (schedule_template)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `schedule_template` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT COMMENT '租户ID',
    `name` VARCHAR(200) NOT NULL COMMENT '模板名称',
    `description` TEXT COMMENT '模板描述',
    `content` TEXT COMMENT '模板内容',
    `is_public` TINYINT DEFAULT 0 COMMENT '是否公开',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_is_public` (`is_public`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='日程模板表';

-- -----------------------------------------------------
-- 表38: 系统设置表 (system_setting)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `system_setting` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT COMMENT '租户ID',
    `setting_key` VARCHAR(100) NOT NULL COMMENT '设置键',
    `setting_value` TEXT COMMENT '设置值',
    `description` VARCHAR(500) COMMENT '设置描述',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    UNIQUE KEY `uk_tenant_key` (`tenant_id`, `setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统设置表';

-- -----------------------------------------------------
-- 表39: 操作日志表 (operation_log)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `operation_log` (
    `id` BIGINT NOT NULL COMMENT '主键ID',
    `tenant_id` BIGINT COMMENT '租户ID',
    `user_id` BIGINT COMMENT '用户ID',
    `user_role` VARCHAR(50) COMMENT '用户角色',
    `action` VARCHAR(50) NOT NULL COMMENT '操作类型',
    `module` VARCHAR(50) COMMENT '操作模块',
    `target_type` VARCHAR(50) COMMENT '目标类型',
    `target_id` BIGINT COMMENT '目标ID',
    `details` TEXT COMMENT '操作详情',
    `ip_address` VARCHAR(50) COMMENT 'IP地址',
    `user_agent` VARCHAR(500) COMMENT '用户代理',
    `create_by` VARCHAR(50) COMMENT '创建人',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` VARCHAR(50) COMMENT '更新人',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted` TINYINT DEFAULT 0 COMMENT '删除标识(0-未删除,1-已删除)',
    PRIMARY KEY (`id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_user_role` (`user_role`),
    KEY `idx_action` (`action`),
    KEY `idx_module` (`module`),
    KEY `idx_target_type` (`target_type`),
    KEY `idx_target_id` (`target_id`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';

-- -----------------------------------------------------
-- 插入默认主题数据
-- -----------------------------------------------------
INSERT INTO `theme` (`id`, `name`, `description`, `sort_order`, `status`) VALUES
(1, '自然科学', '自然探索类主题课程', 1, 'ACTIVE'),
(2, '语言表达', '语言发展类主题课程', 2, 'ACTIVE'),
(3, '艺术创作', '艺术创作类主题课程', 3, 'ACTIVE'),
(4, '社会认知', '社会认知类主题课程', 4, 'ACTIVE'),
(5, '健康运动', '健康运动类主题课程', 5, 'ACTIVE');

-- -----------------------------------------------------
-- 插入默认标签数据
-- -----------------------------------------------------
INSERT INTO `tag` (`id`, `tenant_id`, `name`, `type`, `color`) VALUES
(1, NULL, '阅读', 'CATEGORY', '#1890ff'),
(2, NULL, '写作', 'CATEGORY', '#52c41a'),
(3, NULL, '数学', 'CATEGORY', '#faad14'),
(4, NULL, '科学', 'CATEGORY', '#13c2c2'),
(5, NULL, '艺术', 'CATEGORY', '#eb2f96');