Ogbada Stitches

-- ====================================================================
-- OGBADA STITCHES - SUPABASE DATABASE INITIALIZATION SCRIPT
-- Paste and Run in Supabase SQL Editor (Dashboard -> SQL Editor -> New Query -> Run)
-- ====================================================================

-- 1. Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 2. CREATE ORDERS TABLE (Client Orders & Tracking)
CREATE TABLE IF NOT EXISTS public.orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    phone TEXT NOT NULL,
    venue TEXT,
    type TEXT NOT NULL,
    date TEXT,
    status TEXT NOT NULL DEFAULT 'Order Received',
    payment_status TEXT NOT NULL DEFAULT 'Pay Later',
    eta TEXT,
    courier TEXT,
    notes TEXT,
    media_url TEXT,
    is_video BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Index on code for fast customer tracking lookups
CREATE INDEX IF NOT EXISTS idx_orders_code ON public.orders (code);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON public.orders (created_at DESC);

-- Enable Row Level Security (RLS)
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

-- Drop existing policies if re-running
DROP POLICY IF EXISTS "Allow public insert on orders" ON public.orders;
DROP POLICY IF EXISTS "Allow public read on orders" ON public.orders;
DROP POLICY IF EXISTS "Allow public update on orders" ON public.orders;
DROP POLICY IF EXISTS "Allow public delete on orders" ON public.orders;

-- RLS Policies for orders
CREATE POLICY "Allow public insert on orders" 
ON public.orders FOR INSERT 
TO public, anon, authenticated 
WITH CHECK (true);

CREATE POLICY "Allow public read on orders" 
ON public.orders FOR SELECT 
TO public, anon, authenticated 
USING (true);

CREATE POLICY "Allow public update on orders" 
ON public.orders FOR UPDATE 
TO public, anon, authenticated 
USING (true)
WITH CHECK (true);

CREATE POLICY "Allow public delete on orders" 
ON public.orders FOR DELETE 
TO public, anon, authenticated 
USING (true);


-- 3. CREATE PACKAGES TABLE (Boutique Catalog & Garments)
CREATE TABLE IF NOT EXISTS public.packages (
    id TEXT PRIMARY KEY,
    category TEXT NOT NULL DEFAULT 'Agbada',
    title TEXT NOT NULL,
    desc_text TEXT,
    price TEXT,
    media TEXT,
    is_video BOOLEAN DEFAULT false,
    display_order INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_packages_category ON public.packages (category);

-- Enable RLS for packages
ALTER TABLE public.packages ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow public select on packages" ON public.packages;
DROP POLICY IF EXISTS "Allow public insert on packages" ON public.packages;
DROP POLICY IF EXISTS "Allow public update on packages" ON public.packages;
DROP POLICY IF EXISTS "Allow public delete on packages" ON public.packages;

CREATE POLICY "Allow public select on packages" 
ON public.packages FOR SELECT 
TO public, anon, authenticated 
USING (true);

CREATE POLICY "Allow public insert on packages" 
ON public.packages FOR INSERT 
TO public, anon, authenticated 
WITH CHECK (true);

CREATE POLICY "Allow public update on packages" 
ON public.packages FOR UPDATE 
TO public, anon, authenticated 
USING (true)
WITH CHECK (true);

CREATE POLICY "Allow public delete on packages" 
ON public.packages FOR DELETE 
TO public, anon, authenticated 
USING (true);


-- 4. CREATE SITE_SETTINGS TABLE (Hero Video, Global Show Prices, CMS configs)
CREATE TABLE IF NOT EXISTS public.site_settings (
    key TEXT PRIMARY KEY,
    value JSONB NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT timezone('utc'::text, now()) NOT NULL
);

ALTER TABLE public.site_settings ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow public read on site_settings" ON public.site_settings;
DROP POLICY IF EXISTS "Allow public write on site_settings" ON public.site_settings;

CREATE POLICY "Allow public read on site_settings" 
ON public.site_settings FOR SELECT 
TO public, anon, authenticated 
USING (true);

CREATE POLICY "Allow public write on site_settings" 
ON public.site_settings FOR ALL 
TO public, anon, authenticated 
USING (true)
WITH CHECK (true);


-- 5. INITIAL SEED DATA (Default Boutique Catalog & Settings)
INSERT INTO public.site_settings (key, value)
VALUES 
    ('hero_video', '"https://res.cloudinary.com/reprbj4a/video/upload/v1790171334/Mannequin_robot_wearing_Agbada_o__20260923144751.mp4"'::jsonb),
    ('show_prices', 'true'::jsonb)
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

INSERT INTO public.packages (id, category, title, desc_text, price, media, is_video, display_order)
VALUES 
    ('p3', 'Agbada', 'Royal Agbada Regalia', 'Exquisitely hand-embroidered regal attire with gold threading.', '₦150,000+', 'https://i.ibb.co/Gvy39sf9/IMG-20260904-WA0084.jpg', false, 1),
    ('p2', 'Senator Suits', 'Executive Senator Wear', 'Sleek traditional suit alternative with structured shoulder drape.', '₦65,000+', 'https://i.ibb.co/xK7sHSWP/IMG-20260904-WA0058.jpg', false, 2),
    ('p4', 'Kaftans', 'Luxury Minimalist Kaftan', 'Elegant and comfortable tunic sets in breathable linen and wool.', '₦55,000+', 'https://i.ibb.co/QvnqSg20/IMG-20260904-WA0101.jpg,https://i.ibb.co/tPNhVPFv/IMG-20260904-WA0095.jpg,https://i.ibb.co/Wv96Sbh6/IMG-20260904-WA0099.jpg', false, 3),
    ('p1', 'Native Sets', 'Bespoke Traditional Agbada Set', 'Authentic Nigerian ensembles with modern anatomical taper.', '₦80,000+', 'https://i.ibb.co/gbQ7bMzm/IMG-20260904-WA0051.jpg', false, 4),
    ('p5', 'Native Sets', 'Ankara Royal Cut', 'Vibrant patterned casual & smart sets drafted to bespoke dimensions.', '₦45,000+', 'https://i.ibb.co/wZLS9RBK/IMG-20260904-WA0055.jpg', false, 5),
    ('p6', 'Shirts', 'Ogbada Architectural Shirt', 'Crisp, tailored shirts with spread collar and French cuffs.', '₦25,000+', 'https://i.ibb.co/4RsKnqgn/IMG-20260904-WA0046.jpg,https://i.ibb.co/whmN7Bnh/IMG-20260904-WA0085.jpg', false, 6),
    ('p7', 'Trousers', 'Hand-Drafted Sartorial Trousers', 'Perfect waist fit with side adjusters and weighted architectural break.', '₦30,000+', 'https://i.ibb.co/5XKmNsrr/IMG-20260904-WA0096.jpg,https://i.ibb.co/6JDbYfzc/IMG-20260904-WA0093.jpg', false, 7),
    ('p8', 'Casual', 'Weekend Luxury Casual Set', 'Elevated luxury casuals for discerning gentlemen on leisure.', '₦40,000+', 'https://i.ibb.co/h1M6BS4X/IMG-20260904-WA0143.jpg', false, 8),
    ('p9', 'Corporate', 'Double-Breasted Bespoke Suit', 'Immaculately tailored corporate suits crafted with Italian wool.', '₦120,000+', 'https://i.ibb.co/KjpXXTCT/IMG-20260904-WA0070.jpg', false, 9),
    ('p10', 'Alterations', 'Master Atelier Alterations', 'Precision reshaping to give existing garments flawless drape and fit.', '₦10,000+', 'https://images.unsplash.com/photo-1628102491629-778571d893a3?auto=format&fit=crop&w=600&q=80', false, 10)
ON CONFLICT (id) DO NOTHING;

-- Verification
SELECT 'Database initialized successfully!' as status, count(*) as total_packages FROM public.packages;

Paste Supabase Credentials to Connect Website

Find these in your Supabase Dashboard under Project Settings → API → Project URL and Project API Keys (anon public).