-- GamerComp Full Platform Migration
-- Run with: psql -U gamearc -d game_arcade -f 002_full_platform.sql

BEGIN;

-- ============================================
-- UPDATE EXISTING TABLES
-- ============================================

-- Add missing columns to users table
ALTER TABLE users ADD COLUMN IF NOT EXISTS xp_total INTEGER DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS creator_level INTEGER DEFAULT 1;
ALTER TABLE users ADD COLUMN IF NOT EXISTS creator_badge VARCHAR(50) DEFAULT 'Newbie';
ALTER TABLE users ADD COLUMN IF NOT EXISTS daily_login_streak INTEGER DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS weekly_creation_streak INTEGER DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_date DATE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_creation_date DATE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_verified BOOLEAN DEFAULT false;
ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar_data JSONB;

-- Add missing columns to games table
ALTER TABLE games ADD COLUMN IF NOT EXISTS creation_prompt TEXT;
ALTER TABLE games ADD COLUMN IF NOT EXISTS prompt_answers JSONB;
ALTER TABLE games ADD COLUMN IF NOT EXISTS game_style VARCHAR(50);
ALTER TABLE games ADD COLUMN IF NOT EXISTS difficulty_tags VARCHAR(50)[];
ALTER TABLE games ADD COLUMN IF NOT EXISTS theme_tags VARCHAR(50)[];
ALTER TABLE games ADD COLUMN IF NOT EXISTS accessibility_features VARCHAR(50)[];
ALTER TABLE games ADD COLUMN IF NOT EXISTS is_mobile_friendly BOOLEAN DEFAULT true;
ALTER TABLE games ADD COLUMN IF NOT EXISTS favorite_count INTEGER DEFAULT 0;
ALTER TABLE games ADD COLUMN IF NOT EXISTS remix_count INTEGER DEFAULT 0;
ALTER TABLE games ADD COLUMN IF NOT EXISTS remixed_from_id INTEGER REFERENCES games(id);
ALTER TABLE games ADD COLUMN IF NOT EXISTS allow_remix BOOLEAN DEFAULT true;
ALTER TABLE games ADD COLUMN IF NOT EXISTS creator_played BOOLEAN DEFAULT false;
ALTER TABLE games ADD COLUMN IF NOT EXISTS creator_play_duration INTEGER DEFAULT 0;
ALTER TABLE games ADD COLUMN IF NOT EXISTS ai_review_passed BOOLEAN DEFAULT false;
ALTER TABLE games ADD COLUMN IF NOT EXISTS ai_review_notes TEXT;
ALTER TABLE games ADD COLUMN IF NOT EXISTS license VARCHAR(20) DEFAULT 'GPL-3.0';
ALTER TABLE games ADD COLUMN IF NOT EXISTS thumbnail_data TEXT;

-- Add missing columns to plays table
ALTER TABLE plays ADD COLUMN IF NOT EXISTS levels_completed INTEGER DEFAULT 0;
ALTER TABLE plays ADD COLUMN IF NOT EXISTS difficulty_played VARCHAR(20);
ALTER TABLE plays ADD COLUMN IF NOT EXISTS is_creator_play BOOLEAN DEFAULT false;

-- Update high_scores table
ALTER TABLE high_scores ADD COLUMN IF NOT EXISTS difficulty VARCHAR(20);

-- ============================================
-- CREATE NEW TABLES
-- ============================================

-- FAVORITES
CREATE TABLE IF NOT EXISTS favorites (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  game_id INTEGER REFERENCES games(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(user_id, game_id)
);

-- FOLLOWS (follow creators)
CREATE TABLE IF NOT EXISTS follows (
  id SERIAL PRIMARY KEY,
  follower_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  following_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(follower_id, following_id),
  CHECK (follower_id != following_id)
);

-- NOTIFICATIONS
CREATE TABLE IF NOT EXISTS notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  type VARCHAR(50) NOT NULL,
  title VARCHAR(100),
  message TEXT,
  link VARCHAR(255),
  is_read BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

-- ACHIEVEMENTS
CREATE TABLE IF NOT EXISTS achievements (
  id SERIAL PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  icon VARCHAR(50),
  xp_reward INTEGER DEFAULT 0,
  category VARCHAR(50)
);

-- USER ACHIEVEMENTS
CREATE TABLE IF NOT EXISTS user_achievements (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
  earned_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(user_id, achievement_id)
);

-- COLLECTIONS (user playlists)
CREATE TABLE IF NOT EXISTS collections (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  is_public BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW()
);

-- COLLECTION GAMES
CREATE TABLE IF NOT EXISTS collection_games (
  id SERIAL PRIMARY KEY,
  collection_id INTEGER REFERENCES collections(id) ON DELETE CASCADE,
  game_id INTEGER REFERENCES games(id) ON DELETE CASCADE,
  added_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(collection_id, game_id)
);

-- XP TRANSACTIONS (track all XP gains)
CREATE TABLE IF NOT EXISTS xp_transactions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  amount INTEGER NOT NULL,
  reason VARCHAR(100),
  reference_id INTEGER,
  created_at TIMESTAMP DEFAULT NOW()
);

-- WEEKLY THEMES
CREATE TABLE IF NOT EXISTS weekly_themes (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  bonus_xp_multiplier DECIMAL(3,2) DEFAULT 1.5,
  is_active BOOLEAN DEFAULT false
);

-- CLASSROOMS (teacher mode)
CREATE TABLE IF NOT EXISTS classrooms (
  id SERIAL PRIMARY KEY,
  teacher_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  join_code VARCHAR(10) UNIQUE NOT NULL,
  is_public_publishing_allowed BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

-- CLASSROOM STUDENTS
CREATE TABLE IF NOT EXISTS classroom_students (
  id SERIAL PRIMARY KEY,
  classroom_id INTEGER REFERENCES classrooms(id) ON DELETE CASCADE,
  student_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  joined_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(classroom_id, student_id)
);

-- CLASSROOM ASSIGNMENTS
CREATE TABLE IF NOT EXISTS classroom_assignments (
  id SERIAL PRIMARY KEY,
  classroom_id INTEGER REFERENCES classrooms(id) ON DELETE CASCADE,
  title VARCHAR(100) NOT NULL,
  description TEXT,
  required_game_style VARCHAR(50),
  due_date TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- CREATE INDEXES
-- ============================================

CREATE INDEX IF NOT EXISTS idx_games_style ON games(game_style);
CREATE INDEX IF NOT EXISTS idx_games_difficulty ON games USING GIN(difficulty_tags);
CREATE INDEX IF NOT EXISTS idx_games_themes ON games USING GIN(theme_tags);
CREATE INDEX IF NOT EXISTS idx_favorites_user ON favorites(user_id);
CREATE INDEX IF NOT EXISTS idx_favorites_game ON favorites(game_id);
CREATE INDEX IF NOT EXISTS idx_follows_follower ON follows(follower_id);
CREATE INDEX IF NOT EXISTS idx_follows_following ON follows(following_id);
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_notifications_unread ON notifications(user_id) WHERE is_read = false;
CREATE INDEX IF NOT EXISTS idx_user_achievements_user ON user_achievements(user_id);
CREATE INDEX IF NOT EXISTS idx_collections_user ON collections(user_id);
CREATE INDEX IF NOT EXISTS idx_xp_transactions_user ON xp_transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_classrooms_teacher ON classrooms(teacher_id);
CREATE INDEX IF NOT EXISTS idx_classroom_students_classroom ON classroom_students(classroom_id);
CREATE INDEX IF NOT EXISTS idx_classroom_students_student ON classroom_students(student_id);

-- ============================================
-- SEED ACHIEVEMENTS
-- ============================================

INSERT INTO achievements (code, name, description, icon, xp_reward, category) VALUES
-- Creator achievements
('first_game', 'First Game', 'Published your first game', '🎮', 100, 'creator'),
('ten_games', 'Prolific Creator', 'Published 10 games', '🏭', 500, 'creator'),
('hundred_plays', 'Getting Popular', 'One of your games reached 100 plays', '📈', 200, 'creator'),
('thousand_plays', 'Viral Hit', 'One of your games reached 1000 plays', '🔥', 1000, 'creator'),
('five_stars', 'Five Star Developer', 'Got a 5-star average rating on a game', '⭐', 300, 'creator'),
('remixed', 'Inspirational', 'Someone remixed your game', '🔄', 150, 'creator'),
('ten_remixes', 'Remix Master', '10 games were remixed from yours', '👑', 750, 'creator'),

-- Player achievements
('first_play', 'Player One', 'Played your first game', '🕹️', 25, 'player'),
('fifty_plays', 'Dedicated Gamer', 'Played 50 games', '🎯', 200, 'player'),
('high_scorer', 'High Scorer', 'Got #1 on any leaderboard', '🏆', 300, 'player'),
('all_styles', 'Genre Explorer', 'Played every game style', '🗺️', 400, 'player'),

-- Social achievements
('first_follow', 'Fan Club', 'Followed your first creator', '👥', 25, 'social'),
('ten_favorites', 'Collector', 'Favorited 10 games', '❤️', 100, 'social'),
('first_collection', 'Curator', 'Created your first collection', '📚', 50, 'social'),

-- Prompt engineering achievements
('no_edits', 'Prompt Pro', 'Created a game that needed zero edits', '✨', 250, 'creator'),
('detailed_prompt', 'Detail Detective', 'Used 5+ specific details in your prompt', '🔍', 100, 'creator'),
('accessibility', 'Inclusive Designer', 'Added accessibility features to a game', '♿', 200, 'creator'),

-- Streak achievements
('week_streak', 'Week Warrior', '7-day login streak', '📅', 150, 'special'),
('month_streak', 'Monthly Master', '30-day login streak', '🗓️', 500, 'special'),
('creation_streak', 'Creative Machine', 'Created games 4 weeks in a row', '🏃', 400, 'special'),

-- Special
('beta_tester', 'Beta Tester', 'Joined during beta', '🧪', 500, 'special'),
('bug_reporter', 'Bug Hunter', 'Reported 5 bugs that were fixed', '🐛', 200, 'special')
ON CONFLICT (code) DO NOTHING;

-- ============================================
-- CREATE HELPER FUNCTIONS
-- ============================================

-- Function to calculate creator level from XP
CREATE OR REPLACE FUNCTION get_creator_level(xp INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN CASE
    WHEN xp >= 10000 THEN 10
    WHEN xp >= 6000 THEN 9
    WHEN xp >= 4000 THEN 8
    WHEN xp >= 2500 THEN 7
    WHEN xp >= 1500 THEN 6
    WHEN xp >= 1000 THEN 5
    WHEN xp >= 600 THEN 4
    WHEN xp >= 300 THEN 3
    WHEN xp >= 100 THEN 2
    ELSE 1
  END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Function to get creator badge from level
CREATE OR REPLACE FUNCTION get_creator_badge(level INTEGER)
RETURNS VARCHAR AS $$
BEGIN
  RETURN CASE level
    WHEN 10 THEN 'Grandmaster'
    WHEN 9 THEN 'Legend'
    WHEN 8 THEN 'Master'
    WHEN 7 THEN 'Expert'
    WHEN 6 THEN 'Pro'
    WHEN 5 THEN 'Game Dev'
    WHEN 4 THEN 'Creator'
    WHEN 3 THEN 'Apprentice'
    WHEN 2 THEN 'Beginner'
    ELSE 'Newbie'
  END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Function to award XP and update level
CREATE OR REPLACE FUNCTION award_xp(p_user_id INTEGER, p_amount INTEGER, p_reason VARCHAR, p_reference_id INTEGER DEFAULT NULL)
RETURNS TABLE(new_xp INTEGER, new_level INTEGER, new_badge VARCHAR, leveled_up BOOLEAN) AS $$
DECLARE
  old_level INTEGER;
  current_xp INTEGER;
  calculated_level INTEGER;
  calculated_badge VARCHAR;
BEGIN
  -- Get current XP and level
  SELECT xp_total, creator_level INTO current_xp, old_level FROM users WHERE id = p_user_id;

  -- Calculate new XP
  current_xp := COALESCE(current_xp, 0) + p_amount;

  -- Calculate new level and badge
  calculated_level := get_creator_level(current_xp);
  calculated_badge := get_creator_badge(calculated_level);

  -- Update user
  UPDATE users
  SET xp_total = current_xp,
      creator_level = calculated_level,
      creator_badge = calculated_badge
  WHERE id = p_user_id;

  -- Log XP transaction
  INSERT INTO xp_transactions (user_id, amount, reason, reference_id)
  VALUES (p_user_id, p_amount, p_reason, p_reference_id);

  -- Return results
  RETURN QUERY SELECT current_xp, calculated_level, calculated_badge, (calculated_level > COALESCE(old_level, 1));
END;
$$ LANGUAGE plpgsql;

COMMIT;

-- Show summary
SELECT 'Migration complete!' as status;
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;