-- 高句丽视频平台数据库初始化脚本

-- 用户表
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  email VARCHAR(100),
  avatar VARCHAR(255),
  role VARCHAR(20) DEFAULT 'user',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 视频表
CREATE TABLE IF NOT EXISTS videos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  video_url VARCHAR(500) NOT NULL,
  file_path VARCHAR(500),
  cover_url VARCHAR(500),
  cover_image VARCHAR(500),
  duration INTEGER DEFAULT 0,
  file_size INTEGER DEFAULT 0,
  views INTEGER DEFAULT 0,
  likes INTEGER DEFAULT 0,
  category INTEGER,
  tags TEXT,
  status VARCHAR(20) DEFAULT 'active',
  user_id INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (category) REFERENCES categories(id)
);

-- 视频分类表
CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(50) UNIQUE NOT NULL,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 视频标签表
CREATE TABLE IF NOT EXISTS tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(50) UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 视频标签关联表
CREATE TABLE IF NOT EXISTS video_tags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  video_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
  UNIQUE(video_id, tag_id)
);

-- 用户点赞表
CREATE TABLE IF NOT EXISTS user_likes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  video_id INTEGER NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
  UNIQUE(user_id, video_id)
);

-- 播放历史表
CREATE TABLE IF NOT EXISTS play_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  video_id INTEGER NOT NULL,
  play_time INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
);

-- 视频统计表
CREATE TABLE IF NOT EXISTS video_stats (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  video_id INTEGER UNIQUE NOT NULL,
  views INTEGER DEFAULT 0,
  likes INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
);

-- 插入默认管理员用户
INSERT OR IGNORE INTO users (username, password, email, role) 
VALUES ('admin', '$2b$10$rQZ8kHWKtGOZvlKJ5mXzKOqGYvKqGYvKqGYvKqGYvKqGYvKqGYvKq', 'admin@goguryeo.com', 'admin');

-- 插入默认分类
INSERT OR IGNORE INTO categories (name, description) VALUES 
('历史文化', '高句丽历史文化相关视频'),
('传统艺术', '传统艺术表演和展示'),
('语言学习', '高句丽语言学习教程'),
('考古发现', '考古发现和文物介绍'),
('民俗风情', '传统民俗和风土人情'),
('其他', '其他相关内容');

-- 插入默认标签
INSERT OR IGNORE INTO tags (name) VALUES 
('高句丽'),
('历史'),
('文化'),
('传统'),
('艺术'),
('语言'),
('考古'),
('民俗'),
('教育'),
('纪录片');

-- 插入示例视频数据
INSERT OR IGNORE INTO videos (title, description, video_url, file_path, cover_url, cover_image, duration, file_size, views, likes, category, user_id) VALUES 
('高句丽历史概述', '介绍高句丽王朝的兴起与发展历程', '/videos/sample1.mp4', '/videos/sample1.mp4', '/covers/sample1.jpg', '/covers/sample1.jpg', 1800, 52428800, 1250, 89, 1, 1),
('传统舞蹈表演', '展示高句丽传统舞蹈的魅力', '/videos/sample2.mp4', '/videos/sample2.mp4', '/covers/sample2.jpg', '/covers/sample2.jpg', 900, 31457280, 856, 67, 2, 1),
('古代建筑艺术', '探索高句丽古代建筑的独特风格', '/videos/sample3.mp4', '/videos/sample3.mp4', '/covers/sample3.jpg', '/covers/sample3.jpg', 1200, 41943040, 743, 45, 1, 1),
('语言学习入门', '高句丽语言基础教程第一课', '/videos/sample4.mp4', '/videos/sample4.mp4', '/covers/sample4.jpg', '/covers/sample4.jpg', 1500, 52428800, 432, 23, 3, 1),
('考古新发现', '最新的高句丽考古发现解读', '/videos/sample5.mp4', '/videos/sample5.mp4', '/covers/sample5.jpg', '/covers/sample5.jpg', 2100, 73400320, 321, 18, 4, 1);