supabase_schema.sql

-- Supabase Database Schema for SF Interior App

-- 1. Create Services Table
CREATE TABLE IF NOT EXISTS services (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  price NUMERIC NOT NULL,
  category TEXT NOT NULL,
  icon_emoji TEXT NOT NULL
);

-- 2. Create Service Sub Options Table
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE IF NOT EXISTS service_sub_options (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  service_id TEXT REFERENCES services(id) ON DELETE CASCADE,
  option_name TEXT NOT NULL
);

-- 3. Create Bookings Table
CREATE TABLE IF NOT EXISTS bookings (
  id TEXT PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  customer_name TEXT NOT NULL,
  phone TEXT NOT NULL,
  address TEXT NOT NULL,
  area TEXT NOT NULL,
  booking_date TEXT NOT NULL,
  time_slot TEXT NOT NULL,
  items TEXT NOT NULL,
  total_cost NUMERIC NOT NULL,
  status_step INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 4. Enable Row Level Security (RLS)
ALTER TABLE services ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Services are viewable by everyone" ON services FOR SELECT USING (true);

ALTER TABLE service_sub_options ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Sub options are viewable by everyone" ON service_sub_options FOR SELECT USING (true);

ALTER TABLE bookings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view their own bookings" ON bookings FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own bookings" ON bookings FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own bookings" ON bookings FOR UPDATE USING (auth.uid() = user_id);

-- 5. Insert Initial Data
INSERT INTO services (id, name, description, price, category, icon_emoji) VALUES
('int-kitchen-design', 'Modular Kitchen', 'Premium waterproof plywood with acrylic finish', 45000, 'interior', '🍳'),
('int-false-ceiling', 'False Ceiling', 'Gypsum board ceiling with cove lighting', 15000, 'interior', '💡'),
('int-tv-wardrobe', 'TV Unit & Wardrobe', 'Custom woodwork for living and bedroom', 99000, 'interior', '📺'),
('carp-door', 'Door Installation', 'Solid wood frame and flush door fitting', 2499, 'carpentry', '🚪'),
('carp-sofa', 'Sofa Repair', 'Foam replacement and upholstery', 4999, 'carpentry', '🛋️'),
('paint-2bhk', '2 BHK Painting', 'Asian Paints Royale complete home painting', 14500, 'painting', '🎨'),
('paint-texture', 'Texture Wall', 'Custom wall textures and stencils', 5999, 'painting', '🧱'),
('elec-wiring', 'Full House Wiring', 'Concealed wiring with Finolex cables', 8500, 'electrical', '⚡')
ON CONFLICT (id) DO NOTHING;

INSERT INTO service_sub_options (service_id, option_name) VALUES
('int-kitchen-design', 'L-Shape Kitchen'),
('int-kitchen-design', 'U-Shape Kitchen'),
('int-kitchen-design', 'Parallel Kitchen'),
('int-false-ceiling', 'Living Room Only'),
('int-false-ceiling', 'Living + Bedroom'),
('int-tv-wardrobe', 'Standard TV Unit'),
('int-tv-wardrobe', 'Sliding Wardrobe'),
('carp-door', 'Main Door Fitting'),
('carp-door', 'Internal Door Fitting'),
('carp-sofa', '3 Seater Sofa'),
('carp-sofa', '5 Seater L-Shape'),
('paint-2bhk', 'With Putty'),
('paint-2bhk', 'Without Putty'),
('paint-texture', 'Single Wall'),
('paint-texture', 'Double Wall'),
('elec-wiring', 'Switchboard Repair'),
('elec-wiring', 'New Wiring Setup');