require('dotenv').config();
const { createClient } = require('@supabase/supabase-js');

const supabaseUrl = process.env.SUPABASE_URL;
const supabaseServiceKey = process.env.SUPABASE_SERVICE_KEY;

if (!supabaseUrl || !supabaseServiceKey) {
  console.error('❌ Missing Supabase credentials in .env file');
  process.exit(1);
}

const supabase = createClient(supabaseUrl, supabaseServiceKey);

async function setupDatabase() {
  console.log('🚀 Setting up Bade Canteen database...\n');

  try {
    // Create tables using Supabase SQL
    console.log('📋 Creating tables...');
    
    const { error: sqlError } = await supabase.rpc('exec_sql', {
      sql: `
        -- Users table
        CREATE TABLE IF NOT EXISTS users (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          student_id VARCHAR(50) UNIQUE,
          name VARCHAR(255) NOT NULL,
          email VARCHAR(255),
          mobile VARCHAR(15),
          role VARCHAR(20) DEFAULT 'student' CHECK (role IN ('student', 'kitchen', 'admin')),
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
          updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );

        -- Food items table
        CREATE TABLE IF NOT EXISTS food_items (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          name VARCHAR(255) NOT NULL,
          category VARCHAR(50) NOT NULL CHECK (category IN ('breakfast', 'lunch', 'snacks', 'beverages')),
          price DECIMAL(10, 2) NOT NULL,
          image_url TEXT,
          prep_time INTEGER DEFAULT 15,
          availability VARCHAR(20) DEFAULT 'available' CHECK (availability IN ('available', 'not_available', 'out_of_order')),
          description TEXT,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
          updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );

        -- Orders table
        CREATE TABLE IF NOT EXISTS orders (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          order_number VARCHAR(20) UNIQUE NOT NULL,
          student_id UUID REFERENCES users(id) ON DELETE CASCADE,
          status VARCHAR(20) DEFAULT 'ordered' CHECK (status IN ('ordered', 'preparing', 'ready', 'completed', 'cancelled')),
          total DECIMAL(10, 2) NOT NULL,
          payment_method VARCHAR(20),
          estimated_time INTEGER,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
          updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );

        -- Order items table
        CREATE TABLE IF NOT EXISTS order_items (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
          food_id UUID REFERENCES food_items(id) ON DELETE CASCADE,
          quantity INTEGER NOT NULL DEFAULT 1,
          price DECIMAL(10, 2) NOT NULL,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );

        -- Payments table (saved payment details)
        CREATE TABLE IF NOT EXISTS payments (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          student_id UUID REFERENCES users(id) ON DELETE CASCADE,
          payment_method VARCHAR(20) NOT NULL,
          name VARCHAR(255),
          mobile VARCHAR(15),
          upi_id VARCHAR(100),
          card_last4 VARCHAR(4),
          is_default BOOLEAN DEFAULT true,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
          updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );

        -- Notifications table
        CREATE TABLE IF NOT EXISTS notifications (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          user_id UUID REFERENCES users(id) ON DELETE CASCADE,
          order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
          message TEXT NOT NULL,
          read_status BOOLEAN DEFAULT false,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        );

        -- Push subscriptions table
        CREATE TABLE IF NOT EXISTS push_subscriptions (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          user_id UUID REFERENCES users(id) ON DELETE CASCADE,
          endpoint TEXT NOT NULL,
          p256dh TEXT NOT NULL,
          auth TEXT NOT NULL,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
          UNIQUE(user_id, endpoint)
        );

        -- Create indexes
        CREATE INDEX IF NOT EXISTS idx_orders_student ON orders(student_id);
        CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
        CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);
        CREATE INDEX IF NOT EXISTS idx_food_items_category ON food_items(category);
        CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id);

        -- Enable Row Level Security
        ALTER TABLE users ENABLE ROW LEVEL SECURITY;
        ALTER TABLE food_items ENABLE ROW LEVEL SECURITY;
        ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
        ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
        ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
        ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
        ALTER TABLE push_subscriptions ENABLE ROW LEVEL SECURITY;
      `
    });

    if (sqlError) {
      console.log('ℹ️  Tables may already exist, continuing with RLS policies...');
    } else {
      console.log('✅ Tables created successfully');
    }

    console.log('\n🔒 Setting up Row Level Security policies...');
    
    // Note: RLS policies should be created in Supabase dashboard or via SQL
    // Here's a simplified version - you'll need to customize based on your auth setup
    
    console.log('\n📝 Creating sample data...');
    
    // Insert sample food items
    const sampleFoods = [
      { name: 'Masala Dosa', category: 'breakfast', price: 40, prep_time: 15, description: 'Crispy dosa with potato filling' },
      { name: 'Idli Sambar', category: 'breakfast', price: 30, prep_time: 10, description: 'Steamed rice cakes with sambar' },
      { name: 'Poha', category: 'breakfast', price: 25, prep_time: 10, description: 'Flattened rice with spices' },
      { name: 'Veg Thali', category: 'lunch', price: 60, prep_time: 20, description: 'Complete vegetarian meal' },
      { name: 'Dal Rice', category: 'lunch', price: 50, prep_time: 15, description: 'Lentils with steamed rice' },
      { name: 'Chole Bhature', category: 'lunch', price: 55, prep_time: 20, description: 'Chickpea curry with fried bread' },
      { name: 'Samosa', category: 'snacks', price: 15, prep_time: 5, description: 'Crispy fried pastry with filling' },
      { name: 'Vada Pav', category: 'snacks', price: 20, prep_time: 5, description: 'Spiced potato fritter in bun' },
      { name: 'Pav Bhaji', category: 'snacks', price: 45, prep_time: 15, description: 'Vegetable curry with bread' },
      { name: 'Chai', category: 'beverages', price: 10, prep_time: 5, description: 'Indian spiced tea' },
      { name: 'Coffee', category: 'beverages', price: 15, prep_time: 5, description: 'Hot filter coffee' },
      { name: 'Cold Coffee', category: 'beverages', price: 30, prep_time: 5, description: 'Chilled coffee with ice cream' }
    ];

    for (const food of sampleFoods) {
      const { error } = await supabase
        .from('food_items')
        .insert(food)
        .select();
      
      if (error && !error.message.includes('duplicate')) {
        console.log(`⚠️  Warning: Could not insert ${food.name}`);
      }
    }

    console.log('✅ Sample food items created');

    console.log('\n✨ Database setup complete!');
    console.log('\n📌 Next steps:');
    console.log('1. Configure RLS policies in Supabase dashboard');
    console.log('2. Create a storage bucket named "food-images" for food item images');
    console.log('3. Enable Realtime for the "orders" table (optional)');
    console.log('4. Run: npm run dev');

  } catch (error) {
    console.error('❌ Error setting up database:', error.message);
    process.exit(1);
  }
}

setupDatabase();
