AIGC/demo/database_baota_deploy/init_database.sql

-- ============================================
-- 宝塔面板数据库初始化SQL文件
-- 适用于MySQL/MariaDB数据库
-- ============================================

-- 创建数据库（如果不存在，请根据实际情况修改数据库名）
-- CREATE DATABASE IF NOT EXISTS `aigc_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE `aigc_db`;

-- ============================================
-- 基础表结构
-- ============================================

-- 用户表
CREATE TABLE IF NOT EXISTS users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(100) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'ROLE_USER',
  points INT NOT NULL DEFAULT 50,
  frozen_points INT NOT NULL DEFAULT 0 COMMENT '冻结积分',
  phone VARCHAR(20),
  avatar TEXT COMMENT '头像URL',
  nickname VARCHAR(100),
  gender VARCHAR(10),
  birthday DATE,
  address TEXT,
  bio TEXT COMMENT '个人简介',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  last_login_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_username (username),
  INDEX idx_email (email),
  INDEX idx_role (role),
  INDEX idx_is_active (is_active),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

-- 支付表
CREATE TABLE IF NOT EXISTS payments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id VARCHAR(50) NOT NULL COMMENT '订单号（不唯一，一个订单可以有多次支付尝试）',
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) NOT NULL DEFAULT 'CNY',
  payment_method VARCHAR(20) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '支付状态：PENDING, PROCESSING, SUCCESS, FAILED, CANCELLED',
  description VARCHAR(500),
  external_transaction_id VARCHAR(100) COMMENT '外部交易号（支付宝交易号等）',
  callback_url VARCHAR(1000),
  return_url VARCHAR(1000),
  payment_url VARCHAR(2000) COMMENT '支付跳转URL',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  paid_at TIMESTAMP NULL,
  user_id BIGINT COMMENT '用户ID',
  order_id_ref BIGINT COMMENT '关联的订单ID（外键）',
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (order_id_ref) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_order_id (order_id),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_payment_method (payment_method),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='支付表';

-- 订单表
CREATE TABLE IF NOT EXISTS orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_number VARCHAR(50) NOT NULL UNIQUE,
  total_amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) NOT NULL DEFAULT 'CNY',
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  order_type VARCHAR(20) NOT NULL DEFAULT 'PRODUCT',
  description VARCHAR(500),
  notes TEXT,
  shipping_address TEXT,
  billing_address TEXT,
  contact_phone VARCHAR(20),
  contact_email VARCHAR(100),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  paid_at TIMESTAMP NULL,
  shipped_at TIMESTAMP NULL,
  delivered_at TIMESTAMP NULL,
  cancelled_at TIMESTAMP NULL,
  user_id BIGINT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_order_number (order_number),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

-- 订单项表
CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  product_description VARCHAR(500),
  product_sku VARCHAR(200),
  unit_price DECIMAL(10,2) NOT NULL,
  quantity INT NOT NULL,
  subtotal DECIMAL(10,2) NOT NULL,
  product_image VARCHAR(100),
  order_id BIGINT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单项表';

-- 会员等级表
CREATE TABLE IF NOT EXISTS membership_levels (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE COMMENT '等级名称（内部标识）',
  display_name VARCHAR(50) NOT NULL COMMENT '显示名称',
  description TEXT COMMENT '描述',
  price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '价格（元）',
  duration_days INT NOT NULL DEFAULT 30 COMMENT '时长（天）',
  points_bonus INT NOT NULL DEFAULT 0 COMMENT '积分奖励',
  resource_points INT NOT NULL DEFAULT 0 COMMENT '资源点数量',
  features JSON COMMENT '功能特性（JSON格式）',
  is_active BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否启用',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  INDEX idx_name (name),
  INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员等级表';

-- 用户会员信息表
CREATE TABLE IF NOT EXISTS user_memberships (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  membership_level_id BIGINT NOT NULL,
  start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_date TIMESTAMP NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  auto_renew BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (membership_level_id) REFERENCES membership_levels(id),
  UNIQUE KEY unique_active_membership (user_id, status),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_end_date (end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户会员信息表';

-- 视频生成任务表
CREATE TABLE IF NOT EXISTS video_tasks (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  task_id VARCHAR(100) NOT NULL UNIQUE,
  user_id BIGINT NOT NULL,
  task_type VARCHAR(50) NOT NULL COMMENT 'TEXT_TO_VIDEO, IMAGE_TO_VIDEO, STORYBOARD_VIDEO',
  title VARCHAR(200) NOT NULL,
  description TEXT,
  input_text TEXT,
  input_image_url VARCHAR(500),
  output_video_url VARCHAR(500),
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING, PROCESSING, COMPLETED, FAILED',
  progress INT NOT NULL DEFAULT 0,
  error_message TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completed_at TIMESTAMP NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_task_id (task_id),
  INDEX idx_user_id (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='视频生成任务表';

-- 系统配置表
CREATE TABLE IF NOT EXISTS system_configs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  config_key VARCHAR(100) NOT NULL UNIQUE,
  config_value TEXT,
  description VARCHAR(500),
  config_type VARCHAR(50) NOT NULL DEFAULT 'STRING' COMMENT 'STRING, NUMBER, BOOLEAN, JSON',
  is_public BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表';

-- 系统设置表（用于存储系统级别的设置）
CREATE TABLE IF NOT EXISTS system_settings (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  standard_price_cny INT NOT NULL DEFAULT 0 COMMENT '标准版价格（元）',
  pro_price_cny INT NOT NULL DEFAULT 0 COMMENT '专业版价格（元）',
  points_per_generation INT NOT NULL DEFAULT 1 COMMENT '每次生成消耗的资源点',
  site_name VARCHAR(100) NOT NULL DEFAULT 'AIGC Demo' COMMENT '站点名称',
  site_subtitle VARCHAR(150) NOT NULL DEFAULT '现代化的Spring Boot应用演示' COMMENT '站点副标题',
  registration_open BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否开放注册',
  maintenance_mode BOOLEAN NOT NULL DEFAULT FALSE COMMENT '维护模式',
  enable_alipay BOOLEAN NOT NULL DEFAULT TRUE COMMENT '启用支付宝',
  enable_paypal BOOLEAN NOT NULL DEFAULT TRUE COMMENT '启用PayPal',
  contact_email VARCHAR(120) DEFAULT 'support@example.com' COMMENT '联系邮箱'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统设置表';

-- 用户活跃度统计表
CREATE TABLE IF NOT EXISTS user_activity_stats (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '统计ID',
    activity_date DATE NOT NULL UNIQUE COMMENT '统计日期',
    daily_active_users INT NOT NULL DEFAULT 0 COMMENT '日活用户数',
    monthly_active_users INT NOT NULL DEFAULT 0 COMMENT '月活用户数',
    new_users INT NOT NULL DEFAULT 0 COMMENT '新增用户数',
    returning_users INT NOT NULL DEFAULT 0 COMMENT '回访用户数',
    session_count INT NOT NULL DEFAULT 0 COMMENT '会话数',
    avg_session_duration DECIMAL(10,2) DEFAULT 0 COMMENT '平均会话时长(分钟)',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_activity_date (activity_date),
    INDEX idx_activity_date (activity_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户活跃度统计表';

-- 文生视频任务表
CREATE TABLE IF NOT EXISTS text_to_video_tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '任务ID',
    task_id VARCHAR(50) NOT NULL UNIQUE COMMENT '任务唯一标识',
    username VARCHAR(100) NOT NULL COMMENT '用户名',
    prompt TEXT COMMENT '文本描述/提示词',
    aspect_ratio VARCHAR(10) NOT NULL DEFAULT '16:9' COMMENT '宽高比：16:9, 4:3, 1:1, 3:4, 9:16',
    duration INT NOT NULL DEFAULT 5 COMMENT '视频时长（秒）：5, 10, 15, 30',
    hd_mode BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否高清模式',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '任务状态：PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED',
    progress INT NOT NULL DEFAULT 0 COMMENT '进度（0-100）',
    result_url TEXT COMMENT '结果视频URL',
    real_task_id VARCHAR(100) COMMENT '外部API返回的真实任务ID',
    error_message TEXT COMMENT '错误信息',
    cost_points INT NOT NULL DEFAULT 0 COMMENT '消耗积分',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at TIMESTAMP NULL COMMENT '完成时间',
    INDEX idx_username (username),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_task_id (task_id),
    INDEX idx_real_task_id (real_task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='文生视频任务表';

-- 图生视频任务表
CREATE TABLE IF NOT EXISTS image_to_video_tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '任务ID',
    task_id VARCHAR(50) NOT NULL UNIQUE COMMENT '任务唯一标识',
    username VARCHAR(100) NOT NULL COMMENT '用户名',
    first_frame_url VARCHAR(500) NOT NULL COMMENT '首帧图片URL',
    last_frame_url VARCHAR(500) COMMENT '末帧图片URL',
    prompt TEXT COMMENT '文本描述/提示词',
    aspect_ratio VARCHAR(10) NOT NULL DEFAULT '16:9' COMMENT '宽高比：16:9, 4:3, 1:1, 3:4, 9:16',
    duration INT NOT NULL DEFAULT 5 COMMENT '视频时长（秒）：5, 10, 15, 30',
    hd_mode BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否高清模式',
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '任务状态：PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED',
    progress INT NOT NULL DEFAULT 0 COMMENT '进度（0-100）',
    result_url TEXT COMMENT '结果视频URL',
    real_task_id VARCHAR(100) COMMENT '外部API返回的真实任务ID',
    error_message TEXT COMMENT '错误信息',
    cost_points INT NOT NULL DEFAULT 0 COMMENT '消耗积分',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at TIMESTAMP NULL COMMENT '完成时间',
    INDEX idx_username (username),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_task_id (task_id),
    INDEX idx_real_task_id (real_task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='图生视频任务表';

-- ============================================
-- 任务队列相关表
-- ============================================

-- 任务队列表
CREATE TABLE IF NOT EXISTS task_queue (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL COMMENT '用户名',
    task_id VARCHAR(50) NOT NULL UNIQUE COMMENT '任务ID',
    task_type ENUM('TEXT_TO_VIDEO', 'IMAGE_TO_VIDEO', 'STORYBOARD_VIDEO') NOT NULL COMMENT '任务类型',
    status ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'CANCELLED', 'TIMEOUT') NOT NULL DEFAULT 'PENDING' COMMENT '队列状态',
    priority INT NOT NULL DEFAULT 0 COMMENT '优先级，数字越小优先级越高',
    real_task_id VARCHAR(100) COMMENT '外部API返回的真实任务ID',
    last_check_time DATETIME COMMENT '最后一次检查时间',
    check_count INT NOT NULL DEFAULT 0 COMMENT '检查次数',
    max_check_count INT NOT NULL DEFAULT 30 COMMENT '最大检查次数（30次 * 2分钟 = 60分钟）',
    error_message TEXT COMMENT '错误信息',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at DATETIME COMMENT '完成时间',
    
    INDEX idx_username_status (username, status),
    INDEX idx_status_priority (status, priority),
    INDEX idx_last_check_time (last_check_time),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务队列表';

-- 积分冻结记录表
CREATE TABLE IF NOT EXISTS points_freeze_records (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL COMMENT '用户名',
    task_id VARCHAR(50) NOT NULL UNIQUE COMMENT '任务ID',
    task_type ENUM('TEXT_TO_VIDEO', 'IMAGE_TO_VIDEO', 'STORYBOARD_VIDEO') NOT NULL COMMENT '任务类型',
    freeze_points INT NOT NULL COMMENT '冻结的积分数量',
    status ENUM('FROZEN', 'DEDUCTED', 'RETURNED', 'EXPIRED') NOT NULL DEFAULT 'FROZEN' COMMENT '冻结状态',
    freeze_reason VARCHAR(200) COMMENT '冻结原因',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at DATETIME COMMENT '完成时间',
    
    INDEX idx_username_status (username, status),
    INDEX idx_task_id (task_id),
    INDEX idx_created_at (created_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='积分冻结记录表';

-- 用户作品表
CREATE TABLE IF NOT EXISTS user_works (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL COMMENT '用户名',
    task_id VARCHAR(50) NOT NULL UNIQUE COMMENT '任务ID',
    work_type ENUM('TEXT_TO_VIDEO', 'IMAGE_TO_VIDEO', 'STORYBOARD_VIDEO') NOT NULL COMMENT '作品类型',
    title VARCHAR(200) COMMENT '作品标题',
    description TEXT COMMENT '作品描述',
    prompt TEXT COMMENT '生成提示词',
    result_url VARCHAR(500) COMMENT '结果视频URL',
    thumbnail_url VARCHAR(500) COMMENT '缩略图URL',
    duration VARCHAR(10) COMMENT '视频时长',
    aspect_ratio VARCHAR(10) COMMENT '宽高比',
    quality VARCHAR(20) COMMENT '画质',
    file_size VARCHAR(20) COMMENT '文件大小',
    points_cost INT NOT NULL DEFAULT 0 COMMENT '消耗积分',
    status ENUM('PROCESSING', 'COMPLETED', 'FAILED', 'DELETED') NOT NULL DEFAULT 'PROCESSING' COMMENT '作品状态',
    is_public BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否公开',
    view_count INT NOT NULL DEFAULT 0 COMMENT '浏览次数',
    like_count INT NOT NULL DEFAULT 0 COMMENT '点赞次数',
    download_count INT NOT NULL DEFAULT 0 COMMENT '下载次数',
    tags VARCHAR(500) COMMENT '标签',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at DATETIME COMMENT '完成时间',
    
    INDEX idx_username_status (username, status),
    INDEX idx_task_id (task_id),
    INDEX idx_work_type (work_type),
    INDEX idx_is_public_status (is_public, status),
    INDEX idx_created_at (created_at),
    INDEX idx_view_count (view_count),
    INDEX idx_like_count (like_count),
    INDEX idx_tags (tags),
    INDEX idx_prompt (prompt(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户作品表';

-- 任务状态表
CREATE TABLE IF NOT EXISTS task_status (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id VARCHAR(255) NOT NULL COMMENT '任务ID',
    username VARCHAR(255) NOT NULL COMMENT '用户名',
    task_type VARCHAR(50) NOT NULL COMMENT '任务类型',
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING' COMMENT '任务状态',
    progress INT DEFAULT 0 COMMENT '进度百分比',
    result_url TEXT COMMENT '结果URL',
    error_message TEXT COMMENT '错误信息',
    external_task_id VARCHAR(255) COMMENT '外部任务ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at TIMESTAMP NULL COMMENT '完成时间',
    last_polled_at TIMESTAMP NULL COMMENT '最后轮询时间',
    poll_count INT DEFAULT 0 COMMENT '轮询次数',
    max_polls INT DEFAULT 60 COMMENT '最大轮询次数(2小时)',
    
    INDEX idx_task_id (task_id),
    INDEX idx_username (username),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_last_polled (last_polled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务状态表';

-- 成功任务归档表
CREATE TABLE IF NOT EXISTS completed_tasks_archive (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    task_type VARCHAR(50) NOT NULL,
    prompt TEXT,
    aspect_ratio VARCHAR(20),
    duration INT,
    hd_mode BOOLEAN DEFAULT FALSE,
    result_url TEXT,
    real_task_id VARCHAR(255),
    progress INT DEFAULT 100,
    created_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP NOT NULL,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    points_cost INT DEFAULT 0,
    INDEX idx_username (username),
    INDEX idx_task_type (task_type),
    INDEX idx_created_at (created_at),
    INDEX idx_completed_at (completed_at),
    INDEX idx_archived_at (archived_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成功任务归档表';

-- 失败任务清理日志表
CREATE TABLE IF NOT EXISTS failed_tasks_cleanup_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    task_type VARCHAR(50) NOT NULL,
    error_message TEXT,
    created_at TIMESTAMP NOT NULL,
    failed_at TIMESTAMP NOT NULL,
    cleaned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_task_type (task_type),
    INDEX idx_cleaned_at (cleaned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='失败任务清理日志表';

-- 分镜视频任务表
CREATE TABLE IF NOT EXISTS storyboard_video_tasks (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    task_id VARCHAR(50) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL,
    prompt TEXT COMMENT '文本描述/提示词',
    image_url TEXT COMMENT '上传的参考图片URL（可选）',
    aspect_ratio VARCHAR(10) NOT NULL COMMENT '宽高比：16:9, 4:3, 1:1, 3:4, 9:16',
    hd_mode BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否高清模式',
    status VARCHAR(20) NOT NULL COMMENT '任务状态：PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED',
    progress INT NOT NULL DEFAULT 0 COMMENT '进度（0-100）',
    result_url LONGTEXT COMMENT '分镜图URL（Base64编码的图片，可能非常大）- 网格图',
    storyboard_images LONGTEXT COMMENT '单独的分镜图片（JSON数组，每张图片为Base64格式，带data URI前缀）',
    real_task_id VARCHAR(255) COMMENT '外部API返回的真实任务ID',
    video_task_ids TEXT COMMENT '多个视频任务ID（JSON数组，每张图片对应一个视频任务）',
    video_urls LONGTEXT COMMENT '多个视频URL（JSON数组，用于拼接）',
    error_message TEXT COMMENT '错误信息',
    cost_points INT NOT NULL DEFAULT 0 COMMENT '消耗积分',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    completed_at TIMESTAMP NULL COMMENT '完成时间',
    INDEX idx_username (username),
    INDEX idx_status (status),
    INDEX idx_task_id (task_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='分镜视频任务表';

-- ============================================
-- 初始化完成
-- ============================================
-- 所有表结构已创建完成
-- 注意：此文件仅创建表结构，不包含初始数据
-- 如需初始化数据，请通过系统管理界面或API进行
--
-- 宝塔面板使用说明：
-- 1. 登录宝塔面板，进入"数据库" -> "phpMyAdmin"
-- 2. 选择或创建数据库（建议数据库名：aigc_platform）
-- 3. 点击"导入" -> 选择此SQL文件 -> 执行
-- 4. 或者使用命令行：mysql -u用户名 -p数据库名 < init_database.sql
--
-- 数据库配置建议：
-- - 数据库名：aigc_platform
-- - 字符集：utf8mb4
-- - 排序规则：utf8mb4_unicode_ci

-- ============================================
-- 管理员权限设置
-- ============================================
-- 将 984523799@qq.com 设置为管理员
-- 如果该用户存在，则更新其角色为管理员
UPDATE users
SET role = 'ROLE_ADMIN',
    updated_at = CURRENT_TIMESTAMP
WHERE email = '984523799@qq.com';