GOWILDSafari & Tour Platform

CREATE TABLE quotes ( id bigserial PRIMARY KEY, created_at timestamptz DEFAULT now(), client text, destination text, guests int, days int, currency text, total_label text, total_raw numeric, guest_mode text, breakdown jsonb ); CREATE TABLE vehicle_library ( id bigserial PRIMARY KEY, name text NOT NULL, rate numeric NOT NULL DEFAULT 0, image_url text DEFAULT '' ); CREATE TABLE park_library ( id bigserial PRIMARY KEY, name text NOT NULL, location text, seasons jsonb NOT NULL DEFAULT '[]', website text DEFAULT '', image_url text DEFAULT '', description text DEFAULT '' ); CREATE TABLE accom_library ( id bigserial PRIMARY KEY, name text NOT NULL, location text, seasons jsonb NOT NULL DEFAULT '[]', website text DEFAULT '', image_url text DEFAULT '', description text DEFAULT '' ); CREATE TABLE activity_library ( id bigserial PRIMARY KEY, name text NOT NULL, category text NOT NULL DEFAULT 'Other', cost numeric NOT NULL DEFAULT 0 ); -- Allow public read/write (fine for private use) ALTER TABLE quotes ENABLE ROW LEVEL SECURITY; ALTER TABLE vehicle_library ENABLE ROW LEVEL SECURITY; ALTER TABLE park_library ENABLE ROW LEVEL SECURITY; ALTER TABLE accom_library ENABLE ROW LEVEL SECURITY; ALTER TABLE activity_library ENABLE ROW LEVEL SECURITY; CREATE POLICY "Allow all" ON quotes FOR ALL USING (true) WITH CHECK (true); CREATE POLICY "Allow all" ON vehicle_library FOR ALL USING (true) WITH CHECK (true); CREATE POLICY "Allow all" ON park_library FOR ALL USING (true) WITH CHECK (true); CREATE POLICY "Allow all" ON accom_library FOR ALL USING (true) WITH CHECK (true); CREATE POLICY "Allow all" ON activity_library FOR ALL USING (true) WITH CHECK (true); CREATE TABLE crm_leads ( id bigserial PRIMARY KEY, created_at timestamptz DEFAULT now(), name text NOT NULL, phone text DEFAULT '', email text DEFAULT '', destination text DEFAULT '', travel_date date, group_size int, budget numeric, source text DEFAULT '', stage text NOT NULL DEFAULT 'New Lead', followup_date date, followup_note text DEFAULT '', notes jsonb DEFAULT '[]', linked_quote jsonb, currency text DEFAULT 'USD' ); ALTER TABLE crm_leads ENABLE ROW LEVEL SECURITY; CREATE POLICY "Allow all" ON crm_leads FOR ALL USING (true) WITH CHECK (true); -- ══════════════════════════════════════════════ -- MULTI-TENANT SCHEMA (run once in Supabase SQL editor) -- ══════════════════════════════════════════════ -- 1. Companies table CREATE TABLE IF NOT EXISTS companies ( id bigserial PRIMARY KEY, created_at timestamptz DEFAULT now(), name text NOT NULL, pin text NOT NULL UNIQUE, email text DEFAULT '', phone text DEFAULT '', website text DEFAULT '', address text DEFAULT '', logo text DEFAULT '', primary_color text DEFAULT '#c8933a', plan text DEFAULT 'starter', active boolean DEFAULT true ); -- Insert your own company first (admin plan shows Admin panel) INSERT INTO companies (name, pin, plan, active) VALUES ('My Safari Company', '1234', 'admin', true) ON CONFLICT (pin) DO NOTHING; -- 2. Add company_id to all tables ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); ALTER TABLE guides ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); ALTER TABLE reservations ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); ALTER TABLE quotes ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); ALTER TABLE vehicle_library ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); ALTER TABLE park_library ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); ALTER TABLE accom_library ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); ALTER TABLE activity_library ADD COLUMN IF NOT EXISTS company_id bigint REFERENCES companies(id); -- 3. RLS policies (one per table — filters by company_id) ALTER TABLE crm_leads ENABLE ROW LEVEL SECURITY; ALTER TABLE guides ENABLE ROW LEVEL SECURITY; ALTER TABLE reservations ENABLE ROW LEVEL SECURITY; ALTER TABLE quotes ENABLE ROW LEVEL SECURITY; ALTER TABLE vehicle_library ENABLE ROW LEVEL SECURITY; ALTER TABLE park_library ENABLE ROW LEVEL SECURITY; ALTER TABLE accom_library ENABLE ROW LEVEL SECURITY; ALTER TABLE activity_library ENABLE ROW LEVEL SECURITY; -- Allow all for now (app-level filtering handles isolation) -- Tighten with JWT claims for production CREATE POLICY IF NOT EXISTS "company_access" ON crm_leads FOR ALL USING (true) WITH CHECK (true); CREATE POLICY IF NOT EXISTS "company_access" ON guides FOR ALL USING (true) WITH CHECK (true); CREATE POLICY IF NOT EXISTS "company_access" ON reservations FOR ALL USING (true) WITH CHECK (true); CREATE POLICY IF NOT EXISTS "company_access" ON quotes FOR ALL USING (true) WITH CHECK (true); CREATE POLICY IF NOT EXISTS "company_access" ON vehicle_library FOR ALL USING (true) WITH CHECK (true); CREATE POLICY IF NOT EXISTS "company_access" ON park_library FOR ALL USING (true) WITH CHECK (true); CREATE POLICY IF NOT EXISTS "company_access" ON accom_library FOR ALL USING (true) WITH CHECK (true); CREATE POLICY IF NOT EXISTS "company_access" ON activity_library FOR ALL USING (true) WITH CHECK (true); -- Add deposit & hotel tracking columns (run if table already exists) ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS total_cost numeric; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS deposit_amt numeric; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS deposit_date date; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS payment_note text DEFAULT ''; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS payment_method text DEFAULT 'Bank Transfer'; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS surcharge_pct numeric DEFAULT 0; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS paid_by text DEFAULT ''; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS pay_ref text DEFAULT ''; ALTER TABLE crm_leads ADD COLUMN IF NOT EXISTS hotels jsonb DEFAULT '[]'; CREATE TABLE IF NOT EXISTS guides ( id bigserial PRIMARY KEY, created_at timestamptz DEFAULT now(), name text NOT NULL, status text DEFAULT 'Active', years int DEFAULT 0, languages text[] DEFAULT '{}', specialities text[] DEFAULT '{}', region text DEFAULT '', parks text DEFAULT '', phone text DEFAULT '', email text DEFAULT '', bio text DEFAULT '', rating int DEFAULT 5, photo text DEFAULT '' ); ALTER TABLE guides ENABLE ROW LEVEL SECURITY; CREATE POLICY "Allow all" ON guides FOR ALL USING (true) WITH CHECK (true); CREATE TABLE IF NOT EXISTS reservations ( id bigserial PRIMARY KEY, created_at timestamptz DEFAULT now(), group_name text NOT NULL, trip_ref text DEFAULT '', arrival_date date, departure_date date, vehicle text DEFAULT '', agent text DEFAULT '', flight_in text DEFAULT '', flight_out text DEFAULT '', status text DEFAULT 'Planning', hotel_bookings jsonb DEFAULT '[]', transport text DEFAULT '', transfers text DEFAULT '', balloon text DEFAULT '', meals text DEFAULT '', remarks text DEFAULT '' ); ALTER TABLE reservations ENABLE ROW LEVEL SECURITY; CREATE POLICY "Allow all" ON reservations FOR ALL USING (true) WITH CHECK (true); -- Guide dispatch columns ALTER TABLE reservations ADD COLUMN IF NOT EXISTS assigned_guide text; ALTER TABLE reservations ADD COLUMN IF NOT EXISTS assigned_guide_id text; ALTER TABLE reservations ADD COLUMN IF NOT EXISTS guide_legs jsonb DEFAULT '[]'; -- Storage bucket for lodge & park photos INSERT INTO storage.buckets (id, name, public) VALUES ('safari-photos', 'safari-photos', true) ON CONFLICT DO NOTHING; CREATE POLICY "Allow all safari-photos" ON storage.objects FOR ALL USING (bucket_id = 'safari-photos') WITH CHECK (bucket_id = 'safari-photos');