-- 部署 ak_training_projects 表 (使用UUID主键)
-- 创建日期: 2025-06-09
-- 说明: 标准化训练项目表，使用 ak_ 前缀命名规范和UUID主键

-- 1. 创建 ak_training_projects 表
CREATE TABLE IF NOT EXISTS ak_training_projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  sport_type VARCHAR(100) NOT NULL,
  difficulty_level VARCHAR(50) NOT NULL DEFAULT 'beginner',
  duration_minutes INTEGER NOT NULL DEFAULT 30,
  equipment_required TEXT[],
  target_age_group VARCHAR(50),
  objectives TEXT[],
  instructions TEXT NOT NULL,
  video_url VARCHAR(500),
  image_url VARCHAR(500),
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  
  -- 约束
  CONSTRAINT ak_training_projects_difficulty_check 
    CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced')),
  CONSTRAINT ak_training_projects_duration_check 
    CHECK (duration_minutes > 0 AND duration_minutes <= 480),
  CONSTRAINT ak_training_projects_title_length_check 
    CHECK (LENGTH(title) >= 3)
);

-- 2. 创建索引
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_sport_type 
  ON ak_training_projects(sport_type);
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_difficulty 
  ON ak_training_projects(difficulty_level);
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_active 
  ON ak_training_projects(is_active);
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_created_by 
  ON ak_training_projects(created_by);
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_created_at 
  ON ak_training_projects(created_at);

-- 3. 创建触发器更新 updated_at
CREATE OR REPLACE FUNCTION update_ak_training_projects_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_ak_training_projects_updated_at ON ak_training_projects;
CREATE TRIGGER trigger_ak_training_projects_updated_at
  BEFORE UPDATE ON ak_training_projects
  FOR EACH ROW
  EXECUTE FUNCTION update_ak_training_projects_updated_at();

-- 4. 设置 RLS (Row Level Security) 策略
ALTER TABLE ak_training_projects ENABLE ROW LEVEL SECURITY;

-- 查看策略（所有用户可查看激活的项目）
CREATE POLICY "ak_training_projects_select_policy" ON ak_training_projects
  FOR SELECT USING (is_active = true);

-- 插入策略（认证用户可创建）
CREATE POLICY "ak_training_projects_insert_policy" ON ak_training_projects
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

-- 更新策略（创建者可更新）
CREATE POLICY "ak_training_projects_update_policy" ON ak_training_projects
  FOR UPDATE USING (auth.uid() = created_by);

-- 删除策略（创建者可删除）
CREATE POLICY "ak_training_projects_delete_policy" ON ak_training_projects
  FOR DELETE USING (auth.uid() = created_by);

-- 5. 插入示例数据
INSERT INTO ak_training_projects (
  title, description, sport_type, difficulty_level, duration_minutes,
  equipment_required, target_age_group, objectives, instructions,
  video_url, image_url
) VALUES 
(
  '基础篮球运球训练',
  '适合初学者的篮球运球基础技能训练项目',
  'basketball',
  'beginner',
  30,
  ARRAY['篮球', '训练锥'],
  '8-16岁',
  ARRAY['掌握基础运球技巧', '提高手眼协调能力', '建立运球节奏感'],
  '1. 热身5分钟\n2. 原地运球练习10分钟\n3. 行进间运球10分钟\n4. 变向运球练习5分钟',
  'https://example.com/basketball_dribbling.mp4',
  'https://example.com/basketball_dribbling.jpg'
),
(
  '足球传球技术训练',
  '提高足球传球准确性和技术的专项训练',
  'football',
  'intermediate',
  45,
  ARRAY['足球', '标志桶', '球门'],
  '10-18岁',
  ARRAY['提高传球精度', '增强脚法技术', '培养团队配合意识'],
  '1. 热身跑步10分钟\n2. 短传练习15分钟\n3. 长传练习15分钟\n4. 实战传球演练5分钟',
  'https://example.com/football_passing.mp4',
  'https://example.com/football_passing.jpg'
),
(
  '网球正手击球训练',
  '网球正手击球技术的系统性训练课程',
  'tennis',
  'beginner',
  40,
  ARRAY['网球拍', '网球', '练习网'],
  '12岁以上',
  ARRAY['掌握正手击球姿势', '提高击球稳定性', '增强击球力量'],
  '1. 拉伸热身5分钟\n2. 挥拍动作练习10分钟\n3. 对墙击球15分钟\n4. 对打练习10分钟',
  'https://example.com/tennis_forehand.mp4',
  'https://example.com/tennis_forehand.jpg'
),
(
  '游泳自由泳技术',
  '自由泳游泳技术的完整训练方案',
  'swimming',
  'intermediate',
  60,
  ARRAY['游泳池', '浮板', '游泳眼镜'],
  '8岁以上',
  ARRAY['掌握自由泳动作要领', '提高游泳耐力', '改善游泳技术'],
  '1. 陆上热身10分钟\n2. 水中适应10分钟\n3. 分解动作练习20分钟\n4. 完整动作游泳20分钟',
  'https://example.com/swimming_freestyle.mp4',
  'https://example.com/swimming_freestyle.jpg'
),
(
  '乒乓球发球技术',
  '乒乓球各种发球技术的专项训练',
  'table_tennis',
  'advanced',
  35,
  ARRAY['乒乓球拍', '乒乓球', '球台'],
  '10岁以上',
  ARRAY['掌握多种发球技术', '提高发球质量', '增强比赛竞争力'],
  '1. 热身活动5分钟\n2. 基础发球练习15分钟\n3. 旋转发球练习10分钟\n4. 实战发球演练5分钟',
  'https://example.com/tabletennis_serve.mp4',
  'https://example.com/tabletennis_serve.jpg'
),
(
  '羽毛球步法训练',
  '羽毛球场地移动步法的系统训练',
  'badminton',
  'intermediate',
  50,
  ARRAY['羽毛球拍', '羽毛球', '标志桶'],
  '12岁以上',
  ARRAY['提高场地移动速度', '掌握正确步法', '增强场地覆盖能力'],
  '1. 热身跑步10分钟\n2. 基础步法练习20分钟\n3. 组合步法训练15分钟\n4. 实战步法应用5分钟',
  'https://example.com/badminton_footwork.mp4',
  'https://example.com/badminton_footwork.jpg'
),
(
  '排球垫球基础',
  '排球垫球技术的入门训练课程',
  'volleyball',
  'beginner',
  30,
  ARRAY['排球', '训练网'],
  '10-16岁',
  ARRAY['掌握垫球基本动作', '提高垫球稳定性', '培养球感'],
  '1. 热身运动5分钟\n2. 垫球动作练习10分钟\n3. 对墙垫球10分钟\n4. 配合垫球练习5分钟',
  'https://example.com/volleyball_bump.mp4',
  'https://example.com/volleyball_bump.jpg'
),
(
  '田径短跑技术',
  '100米短跑技术训练的专业课程',
  'track_and_field',
  'advanced',
  55,
  ARRAY['跑道', '起跑器', '秒表'],
  '14岁以上',
  ARRAY['改善起跑技术', '提高跑步效率', '增强爆发力'],
  '1. 动态热身15分钟\n2. 起跑技术练习15分钟\n3. 加速跑练习15分钟\n4. 冲刺跑训练10分钟',
  'https://example.com/sprint_technique.mp4',
  'https://example.com/sprint_technique.jpg'
);

-- 6. 创建兼容性视图（可选，如果需要支持遗留代码）
CREATE OR REPLACE VIEW training_projects AS
SELECT 
  id,
  title,
  description,
  sport_type,
  difficulty_level,
  duration_minutes,
  equipment_required,
  target_age_group,
  objectives,
  instructions,
  video_url,
  image_url,
  is_active,
  created_by,
  created_at,
  updated_at
FROM ak_training_projects;

-- 7. 验证数据
SELECT 
  COUNT(*) as total_projects,
  COUNT(CASE WHEN is_active THEN 1 END) as active_projects,
  COUNT(DISTINCT sport_type) as sport_types,
  COUNT(DISTINCT difficulty_level) as difficulty_levels
FROM ak_training_projects;

-- 部署完成提示
SELECT 'ak_training_projects 表已成功创建并插入示例数据' as deployment_status;