-- ============================================================
-- EcoLearn Platform - Complete Database Schema
-- For: Supabase PostgreSQL
-- ============================================================
-- COPY & PASTE ini ke Supabase SQL Editor, kemudian klik RUN
-- ============================================================

-- ============================================================
-- 1. PROFILES TABLE (User Master Data)
-- ============================================================

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(20) NOT NULL UNIQUE,
  avatar_url TEXT,
  bio TEXT,
  total_points INTEGER DEFAULT 0 CHECK (total_points >= 0),
  games_played INTEGER DEFAULT 0,
  modules_completed INTEGER DEFAULT 0,
  
  -- Daily Login Streak System
  last_login_date DATE,
  current_streak INTEGER DEFAULT 0,
  max_streak INTEGER DEFAULT 0,
  streak_multiplier DECIMAL(3,2) DEFAULT 1.0,
  
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT true,
  last_login TIMESTAMP WITH TIME ZONE
);

-- Indexes untuk performance
CREATE INDEX idx_profiles_username ON profiles(username);
CREATE INDEX idx_profiles_email ON profiles(email);
CREATE INDEX idx_profiles_total_points ON profiles(total_points DESC);
CREATE INDEX idx_profiles_created_at ON profiles(created_at DESC);
CREATE INDEX idx_profiles_streak ON profiles(current_streak DESC);

-- Trigger untuk auto-update updated_at
CREATE OR REPLACE FUNCTION update_profiles_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_profiles_updated_at
AFTER UPDATE ON profiles
FOR EACH ROW
EXECUTE FUNCTION update_profiles_updated_at();

-- ============================================================
-- 2. VIDEOS TABLE (Video Pembelajaran)
-- ============================================================

CREATE TABLE videos (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT NOT NULL,
  video_url TEXT NOT NULL,
  transcript TEXT,
  duration_seconds INTEGER,
  thumbnail_url TEXT,
  order_index INTEGER NOT NULL,
  difficulty_level VARCHAR(20),
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_videos_slug ON videos(slug);
CREATE INDEX idx_videos_published ON videos(is_published);
CREATE INDEX idx_videos_order ON videos(order_index);

-- ============================================================
-- 3. VIDEO PROGRESS TABLE (User Video Tracking)
-- ============================================================

CREATE TABLE video_progress (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  is_watched BOOLEAN DEFAULT false,
  last_position_seconds INTEGER DEFAULT 0,
  watch_count INTEGER DEFAULT 0,
  first_watched_at TIMESTAMP WITH TIME ZONE,
  last_watched_at TIMESTAMP WITH TIME ZONE,
  completed_at TIMESTAMP WITH TIME ZONE,
  UNIQUE(user_id, video_id)
);

CREATE INDEX idx_video_progress_user ON video_progress(user_id);
CREATE INDEX idx_video_progress_video ON video_progress(video_id);
CREATE INDEX idx_video_progress_watched ON video_progress(is_watched);

-- ============================================================
-- 4. QUIZZES TABLE (Quiz Definitions)
-- ============================================================

CREATE TABLE quizzes (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT,
  video_id INTEGER NOT NULL REFERENCES videos(id) ON DELETE CASCADE,
  question_count INTEGER NOT NULL,
  time_limit_minutes INTEGER,
  passing_score INTEGER DEFAULT 70,
  is_published BOOLEAN DEFAULT false,
  order_index INTEGER NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_quizzes_video ON quizzes(video_id);
CREATE INDEX idx_quizzes_published ON quizzes(is_published);

-- ============================================================
-- 5. QUIZ QUESTIONS TABLE
-- ============================================================

CREATE TABLE quiz_questions (
  id SERIAL PRIMARY KEY,
  quiz_id INTEGER NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
  question_text TEXT NOT NULL,
  question_type VARCHAR(50),
  question_number INTEGER NOT NULL,
  image_url TEXT,
  points INTEGER DEFAULT 10,
  is_required BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(quiz_id, question_number)
);

CREATE INDEX idx_quiz_questions_quiz ON quiz_questions(quiz_id);

-- ============================================================
-- 6. QUIZ OPTIONS TABLE
-- ============================================================

CREATE TABLE quiz_options (
  id SERIAL PRIMARY KEY,
  question_id INTEGER NOT NULL REFERENCES quiz_questions(id) ON DELETE CASCADE,
  option_text VARCHAR(500) NOT NULL,
  option_image_url TEXT,
  is_correct BOOLEAN NOT NULL DEFAULT false,
  explanation TEXT,
  display_order INTEGER NOT NULL,
  UNIQUE(question_id, display_order)
);

CREATE INDEX idx_quiz_options_question ON quiz_options(question_id);

-- ============================================================
-- 7. QUIZ ATTEMPTS TABLE
-- ============================================================

CREATE TABLE quiz_attempts (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  quiz_id INTEGER NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
  attempt_number INTEGER DEFAULT 1,
  score INTEGER,
  time_spent_seconds INTEGER,
  is_passed BOOLEAN,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP WITH TIME ZONE,
  UNIQUE(user_id, quiz_id, attempt_number)
);

CREATE INDEX idx_quiz_attempts_user ON quiz_attempts(user_id);
CREATE INDEX idx_quiz_attempts_quiz ON quiz_attempts(quiz_id);
CREATE INDEX idx_quiz_attempts_passed ON quiz_attempts(is_passed);

-- ============================================================
-- 8. QUIZ ANSWERS TABLE
-- ============================================================

CREATE TABLE quiz_answers (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  quiz_id INTEGER NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
  question_id INTEGER NOT NULL REFERENCES quiz_questions(id) ON DELETE CASCADE,
  selected_option_id INTEGER REFERENCES quiz_options(id) ON DELETE SET NULL,
  is_correct BOOLEAN NOT NULL,
  answered_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, question_id)
);

CREATE INDEX idx_quiz_answers_user ON quiz_answers(user_id);
CREATE INDEX idx_quiz_answers_quiz ON quiz_answers(quiz_id);
CREATE INDEX idx_quiz_answers_correct ON quiz_answers(is_correct);

-- ============================================================
-- 9. GAME SESSIONS TABLE (Game History)
-- ============================================================

CREATE TABLE game_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  game_type VARCHAR(50) NOT NULL,
  score INTEGER NOT NULL CHECK (score >= 0),
  max_score INTEGER DEFAULT 30,
  round_number INTEGER DEFAULT 1,
  total_rounds INTEGER DEFAULT 1,
  
  game_details JSONB,
  
  step1_score INTEGER DEFAULT 0,
  step2_score INTEGER DEFAULT 0,
  step3_score INTEGER DEFAULT 0,
  bonus_points INTEGER DEFAULT 0,
  penalty_points INTEGER DEFAULT 0,
  
  game_duration_seconds INTEGER,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  
  is_completed BOOLEAN DEFAULT true,
  has_bonus BOOLEAN DEFAULT false,
  water_efficiency_achieved BOOLEAN DEFAULT false,
  
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_game_sessions_user ON game_sessions(user_id);
CREATE INDEX idx_game_sessions_created ON game_sessions(created_at DESC);
CREATE INDEX idx_game_sessions_score ON game_sessions(score DESC);
CREATE INDEX idx_game_sessions_type ON game_sessions(game_type);

-- ============================================================
-- 10. LEARNING MODULES TABLE (Konten Pembelajaran)
-- ============================================================

CREATE TABLE learning_modules (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT NOT NULL,
  order_index INTEGER NOT NULL,
  content_type VARCHAR(50),
  content_html TEXT,
  video_url TEXT,
  difficulty_level VARCHAR(20),
  duration_minutes INTEGER,
  thumbnail_url TEXT,
  is_published BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_modules_slug ON learning_modules(slug);
CREATE INDEX idx_modules_published ON learning_modules(is_published);
CREATE INDEX idx_modules_order ON learning_modules(order_index);

-- ============================================================
-- 11. LEARNING PROGRESS TABLE (User Module Progress)
-- ============================================================

CREATE TABLE learning_progress (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  module_id INTEGER NOT NULL REFERENCES learning_modules(id) ON DELETE CASCADE,
  is_completed BOOLEAN DEFAULT false,
  completion_percentage INTEGER DEFAULT 0 CHECK (completion_percentage >= 0 AND completion_percentage <= 100),
  quiz_score INTEGER,
  started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  completed_at TIMESTAMP WITH TIME ZONE,
  time_spent_seconds INTEGER DEFAULT 0,
  UNIQUE(user_id, module_id)
);

CREATE INDEX idx_learning_progress_user ON learning_progress(user_id);
CREATE INDEX idx_learning_progress_module ON learning_progress(module_id);
CREATE INDEX idx_learning_progress_completed ON learning_progress(is_completed);

-- ============================================================
-- 12. BADGES TABLE (Achievement Definitions)
-- ============================================================

CREATE TABLE badges (
  id SERIAL PRIMARY KEY,
  slug VARCHAR(100) NOT NULL UNIQUE,
  title VARCHAR(100) NOT NULL,
  description TEXT,
  icon_url TEXT,
  badge_category VARCHAR(50),
  achievement_type VARCHAR(100),
  requirement_value INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- 13. USER BADGES TABLE (User Achievement Mapping)
-- ============================================================

CREATE TABLE user_badges (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  badge_id INTEGER NOT NULL REFERENCES badges(id) ON DELETE CASCADE,
  earned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, badge_id)
);

CREATE INDEX idx_user_badges_user ON user_badges(user_id);
CREATE INDEX idx_user_badges_earned ON user_badges(earned_at DESC);

-- ============================================================
-- 14. AUTO-CREATE PROFILE TRIGGER (When User Signs Up)
-- ============================================================

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, username)
  VALUES (
    NEW.id,
    NEW.email,
    'user_' || SUBSTR(MD5(NEW.email), 1, 8)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

-- Drop trigger jika sudah ada
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;

-- Create trigger
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- ============================================================
-- 15. ENABLE ROW LEVEL SECURITY (RLS) - KEAMANAN
-- ============================================================

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE video_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE quiz_attempts ENABLE ROW LEVEL SECURITY;
ALTER TABLE quiz_answers ENABLE ROW LEVEL SECURITY;
ALTER TABLE game_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE learning_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_badges ENABLE ROW LEVEL SECURITY;

-- ============================================================
-- 16. RLS POLICIES - PROFILES
-- ============================================================

CREATE POLICY "Users can read own profile"
  ON profiles
  FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Users can view all profiles"
  ON profiles
  FOR SELECT
  USING (true);

CREATE POLICY "Users can update own profile"
  ON profiles
  FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can insert own profile"
  ON profiles
  FOR INSERT
  WITH CHECK (auth.uid() = id);

-- ============================================================
-- 17. RLS POLICIES - GAME SESSIONS
-- ============================================================

CREATE POLICY "Users can read own game sessions"
  ON game_sessions
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own game sessions"
  ON game_sessions
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- ============================================================
-- 18. RLS POLICIES - LEARNING PROGRESS
-- ============================================================

CREATE POLICY "Users can read own learning progress"
  ON learning_progress
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own learning progress"
  ON learning_progress
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own learning progress"
  ON learning_progress
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- ============================================================
-- 19. RLS POLICIES - VIDEO PROGRESS
-- ============================================================

CREATE POLICY "Users can read own video progress"
  ON video_progress
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own video progress"
  ON video_progress
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own video progress"
  ON video_progress
  FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- ============================================================
-- 20. RLS POLICIES - QUIZ
-- ============================================================

CREATE POLICY "Users can read own quiz attempts"
  ON quiz_attempts
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own quiz attempts"
  ON quiz_attempts
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can read own quiz answers"
  ON quiz_answers
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own quiz answers"
  ON quiz_answers
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- ============================================================
-- 21. RLS POLICIES - BADGES
-- ============================================================

CREATE POLICY "Users can read own badges"
  ON user_badges
  FOR SELECT
  USING (auth.uid() = user_id);

-- ============================================================
-- 22. SEED DATA - BADGES (Sample Data)
-- ============================================================

INSERT INTO badges (slug, title, description, badge_category, achievement_type, requirement_value) VALUES
('first_steps', 'First Steps', 'Mainkan game pertama kali', 'gaming', 'first_game', 1),
('speed_demon', 'Speed Demon', 'Selesaikan round game < 20 detik', 'gaming', 'speed_challenge', 20),
('perfect_sort', 'Perfect Sort', 'Dapatkan 3 perfect round berturut-turut', 'gaming', 'perfect_rounds', 3),
('eco_scholar', 'Eco Scholar', 'Selesaikan Modul 1: Pengenalan Sampah', 'learning', 'module_completion', 1),
('waste_master', 'Waste Master', 'Selesaikan Modul 2: Pemilahan Dasar', 'learning', 'module_completion', 2),
('sustainability_expert', 'Sustainability Expert', 'Selesaikan semua modul pembelajaran', 'learning', 'module_completion', 4),
('top_10', 'Top 10', 'Masuk Top 10 global leaderboard', 'special', 'leaderboard_milestone', 10),
('legend', 'Legend', 'Kumpulkan 500+ poin total', 'special', 'points_milestone', 500)
ON CONFLICT (slug) DO NOTHING;

-- ============================================================
-- 23. SEED DATA - LEARNING MODULES (Sample)
-- ============================================================

INSERT INTO learning_modules (title, slug, description, order_index, content_type, difficulty_level, duration_minutes, is_published)
VALUES
('Pengenalan Sampah', 'intro-waste', 'Pelajari jenis-jenis sampah dan dampaknya terhadap lingkungan', 1, 'article', 'beginner', 10, true),
('Pemilahan Dasar', 'basic-sorting', 'Panduan lengkap pemilahan sampah organik vs anorganik', 2, 'article', 'beginner', 15, true),
('Tips Praktis', 'practical-tips', 'Cara efektif mengelola sampah di rumah', 3, 'video', 'intermediate', 12, true),
('Daur Ulang Lanjutan', 'advanced-recycling', 'Kreativitas dan ekonomi sirkular', 4, 'article', 'advanced', 20, true)
ON CONFLICT (slug) DO NOTHING;

-- ============================================================
-- 24. SEED DATA - VIDEOS (Sample)
-- ============================================================

INSERT INTO videos (title, slug, description, video_url, duration_seconds, order_index, difficulty_level, is_published)
VALUES
('Jenis-Jenis Sampah', 'types-of-waste', 'Pengenalan berbagai jenis sampah dan karakteristiknya', 'https://www.youtube.com/embed/dummyvideo', 600, 1, 'beginner', true),
('Pemilahan yang Benar', 'correct-sorting', 'Cara yang benar memilah sampah untuk didaur ulang', 'https://www.youtube.com/embed/dummyvideo', 480, 2, 'beginner', true),
('Program Daur Ulang', 'recycling-program', 'Ikuti program daur ulang di komunitas Anda', 'https://www.youtube.com/embed/dummyvideo', 720, 3, 'intermediate', true)
ON CONFLICT (slug) DO NOTHING;

-- ============================================================
-- ALL DONE! ✓
-- ============================================================
-- Schema sudah siap. Sekarang:
-- 1. Cek di "Database → Tables" untuk verifikasi
-- 2. Update .env.local dengan credentials Supabase
-- 3. Restart Next.js server
-- 4. Test signup/login
-- ============================================================