setup-database.js

import { createClient } from '@supabase/supabase-js';
import dotenv from 'dotenv';

// Get environment variables
dotenv.config();

const supabaseUrl = process.env.VITE_SUPABASE_URL;
const supabaseAnonKey = process.env.VITE_SUPABASE_ANON_KEY;

if (!supabaseUrl || !supabaseAnonKey) {
  console.error('Error: Missing Supabase environment variables.');
  console.error('Please ensure VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY are set in your .env file.');
  process.exit(1);
}

const supabase = createClient(supabaseUrl, supabaseAnonKey);

async function setupDatabase() {
  console.log('Setting up StudySync AI database...');

  const sqlCommands = [
    // 1. Create Profiles Table (Enhanced for Gamification)
    `create table if not exists profiles (
      id uuid references auth.users on delete cascade not null primary key,
      name text,
      xp integer default 0,
      streak integer default 0,
      level integer default 1,
      total_study_hours numeric default 0,
      achievements jsonb default '[]'::jsonb,
      weeklyStats jsonb default '[]'::jsonb,
      subjectMastery jsonb default '[]'::jsonb,
      stats jsonb default '{}'::jsonb,
      created_at timestamp with time zone default timezone('utc'::text, now()) not null
    );`,

    // 2. Create Chat Sessions Table
    `create table if not exists chat_sessions (
      id uuid default gen_random_uuid() primary key,
      user_id uuid references auth.users not null,
      title text,
      last_message text,
      created_at timestamp with time zone default timezone('utc'::text, now()) not null,
      updated_at timestamp with time zone default timezone('utc'::text, now()) not null
    );`,

    // 3. Create Chat Messages Table
    `create table if not exists chat_messages (
      id uuid default gen_random_uuid() primary key,
      session_id uuid references chat_sessions on delete cascade not null,
      role text not null, -- 'user' or 'model'
      text text not null,
      created_at timestamp with time zone default timezone('utc'::text, now()) not null
    );`,

    // 4. Create Roadmaps Table
    `create table if not exists roadmaps (
      id bigint generated by default as identity primary key,
      user_id uuid references auth.users not null,
      topic text not null,
      steps jsonb not null, -- Stores the array of roadmap steps
      progress integer default 0,
      created_at timestamp with time zone default timezone('utc'::text, now()) not null
    );`,

    // 5. Enable Row Level Security (RLS)
    `alter table profiles enable row level security;`,
    `alter table chat_sessions enable row level security;`,
    `alter table chat_messages enable row level security;`,
    `alter table roadmaps enable row level security;`,

    // 6. Create Policies (Allow users to manage their own data)
    `create policy if not exists "Users can see own profile" on profiles for select using (auth.uid() = id);`,
    `create policy if not exists "Users can update own profile" on profiles for update using (auth.uid() = id);`,
    `create policy if not exists "Users can insert own profile" on profiles for insert with check (auth.uid() = id);`,

    `create policy if not exists "Users can see own chats" on chat_sessions for select using (auth.uid() = user_id);`,
    `create policy if not exists "Users can insert own chats" on chat_sessions for insert with check (auth.uid() = user_id);`,
    `create policy if not exists "Users can update own chats" on chat_sessions for update using (auth.uid() = user_id);`,

    `create policy if not exists "Users can see own messages" on chat_messages for select using (
      exists (select 1 from chat_sessions where id = chat_messages.session_id and user_id = auth.uid())
    );`,
    `create policy if not exists "Users can insert own messages" on chat_messages for insert with check (
      exists (select 1 from chat_sessions where id = chat_messages.session_id and user_id = auth.uid())
    );`,

    `create policy if not exists "Users can see own roadmaps" on roadmaps for select using (auth.uid() = user_id);`,
    `create policy if not exists "Users can insert own roadmaps" on roadmaps for insert with check (auth.uid() = user_id);`,
    `create policy if not exists "Users can update own roadmaps" on roadmaps for update using (auth.uid() = user_id);`,
    `create policy if not exists "Users can delete own roadmaps" on roadmaps for delete using (auth.uid() = user_id);`
  ];

  try {
    for (const command of sqlCommands) {
      console.log(`Executing: ${command.substring(0, 60)}...`);
      const { error } = await supabase.rpc('execute_sql', { sql_command: command });
      if (error) {
        console.log(`Command failed (this may be expected): ${error.message}`);
        // Try to execute using raw SQL instead
        try {
          await supabase.from('profiles').select('id').limit(1);
        } catch (selectError) {
          // This is expected if the table doesn't exist yet
          console.log('Continuing setup...');
        }
      } else {
        console.log('✓ Command executed successfully');
      }
    }

    console.log('\nDatabase setup completed successfully!');
    console.log('Tables created:');
    console.log('- profiles');
    console.log('- chat_sessions');
    console.log('- chat_messages');
    console.log('- roadmaps');
    console.log('\nRow Level Security policies have been enabled and configured.');
  } catch (error) {
    console.error('Error during database setup:', error.message);
  }
}

// Execute the setup
setupDatabase();