supabase-patch.sql

-- 1. Ensure profiles table has correct columns for users
CREATE TABLE IF NOT EXISTS profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  full_name TEXT,
  points_balance INTEGER DEFAULT 100,
  is_admin BOOLEAN DEFAULT false,
  referral_code TEXT UNIQUE,
  referred_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS on profiles
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Profiles policies
CREATE POLICY "Public profiles are viewable by everyone." ON profiles FOR SELECT USING (true);
CREATE POLICY "Users can update own profile." ON profiles FOR UPDATE USING (auth.uid() = id);

-- 2. Update food_items table
DO $$ 
BEGIN 
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='food_items' AND column_name='cuisine') THEN
        ALTER TABLE food_items ADD COLUMN cuisine TEXT;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='food_items' AND column_name='cuisine_type') THEN
        ALTER TABLE food_items ADD COLUMN cuisine_type TEXT;
    END IF;
END $$;

-- 3. Meal Plans relational structure
CREATE TABLE IF NOT EXISTS meal_plans (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  plan_type TEXT DEFAULT 'regular', -- regular, healthy, special
  day_number INTEGER NOT NULL,
  date DATE,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS meal_plan_items (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  meal_plan_id UUID REFERENCES meal_plans(id) ON DELETE CASCADE,
  food_item_id UUID REFERENCES food_items(id) ON DELETE CASCADE,
  sort_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 4. Single-row delivery settings
DROP TABLE IF EXISTS delivery_settings;
CREATE TABLE delivery_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  delivery_start_time TEXT DEFAULT '11:00',
  delivery_end_time TEXT DEFAULT '21:00',
  delivery_radius DECIMAL DEFAULT 5,
  restaurant_lat DECIMAL DEFAULT 19.0760,
  restaurant_lng DECIMAL DEFAULT 72.8777,
  minimum_order_amount DECIMAL DEFAULT 150,
  delivery_fee DECIMAL DEFAULT 0,
  points_per_rupee INTEGER DEFAULT 1,
  referral_points_award INTEGER DEFAULT 100,
  whatsapp_group_link TEXT,
  upi_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Insert default settings row if empty
INSERT INTO delivery_settings (id) VALUES ('00000000-0000-0000-0000-000000000000') ON CONFLICT DO NOTHING;

-- 5. Admin RLS Policies
ALTER TABLE food_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE meal_plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE meal_plan_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE delivery_settings ENABLE ROW LEVEL SECURITY;

-- Admins can do anything
CREATE POLICY "Admins manage food_items" ON food_items FOR ALL USING ((SELECT is_admin FROM profiles WHERE id = auth.uid()) = true);
CREATE POLICY "Admins manage meal_plans" ON meal_plans FOR ALL USING ((SELECT is_admin FROM profiles WHERE id = auth.uid()) = true);
CREATE POLICY "Admins manage meal_plan_items" ON meal_plan_items FOR ALL USING ((SELECT is_admin FROM profiles WHERE id = auth.uid()) = true);
CREATE POLICY "Admins manage delivery_settings" ON delivery_settings FOR ALL USING ((SELECT is_admin FROM profiles WHERE id = auth.uid()) = true);

-- Public can view
CREATE POLICY "Public view food_items" ON food_items FOR SELECT USING (true);
CREATE POLICY "Public view meal_plans" ON meal_plans FOR SELECT USING (true);
CREATE POLICY "Public view meal_plan_items" ON meal_plan_items FOR SELECT USING (true);
CREATE POLICY "Public view delivery_settings" ON delivery_settings FOR SELECT USING (true);

-- 6. Ensure orders are protected
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own orders" ON orders FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own orders" ON orders FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Admins can view all orders" ON orders FOR SELECT USING ((SELECT is_admin FROM profiles WHERE id = auth.uid()) = true);
CREATE POLICY "Admins can update orders" ON orders FOR UPDATE USING ((SELECT is_admin FROM profiles WHERE id = auth.uid()) = true);

-- 7. Add UPI payment fields to orders
ALTER TABLE orders ADD COLUMN IF NOT EXISTS payment_utr TEXT;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS upi_id_used TEXT;

-- 8. Add UPI settings to delivery_settings if not present
DO $$ 
BEGIN 
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='delivery_settings' AND column_name='upi_id') THEN
        ALTER TABLE delivery_settings ADD COLUMN upi_id TEXT;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='delivery_settings' AND column_name='upi_name') THEN
        ALTER TABLE delivery_settings ADD COLUMN upi_name TEXT DEFAULT 'VedaMeals';
    END IF;
END $$;

-- 9. Add payment screenshot field
ALTER TABLE orders ADD COLUMN IF NOT EXISTS payment_screenshot_url TEXT;

-- 10. Update profiles for Referral & Rewards
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS referral_code TEXT UNIQUE DEFAULT substring(gen_random_uuid()::text, 1, 8);
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS referred_by UUID REFERENCES profiles(id);
ALTER TABLE profiles ADD COLUMN IF NOT EXISTS points_balance INTEGER DEFAULT 0;

-- 11. Add referral tracking table
CREATE TABLE IF NOT EXISTS referrals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  referrer_id UUID REFERENCES profiles(id),
  referee_id UUID REFERENCES profiles(id),
  status TEXT DEFAULT 'pending', -- pending, completed
  reward_points INTEGER DEFAULT 50,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 12. RLS for referrals
ALTER TABLE referrals ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own referrals" ON referrals FOR SELECT USING (auth.uid() = referrer_id OR auth.uid() = referee_id);

-- 13. Live tracking for delivery boys
CREATE TABLE IF NOT EXISTS delivery_boy_locations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
  boy_name TEXT,
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- RLS for public viewing (or limited to the specific user's order)
ALTER TABLE delivery_boy_locations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Public can view delivery location" ON delivery_boy_locations FOR SELECT USING (true);