systeme.io

-- users CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email TEXT UNIQUE NOT NULL, name TEXT, role TEXT CHECK (role IN ('hunter','outfitter','admin')), phone TEXT, created_at timestamptz DEFAULT now() ); -- outfitters CREATE TABLE outfitters ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES users(id), business_name TEXT, country TEXT, license_docs jsonb, verified BOOLEAN DEFAULT false, verified_at timestamptz, created_at timestamptz DEFAULT now() ); -- listings CREATE TABLE listings ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), outfitter_id UUID REFERENCES outfitters(id), title TEXT, description TEXT, species TEXT[], country TEXT, region TEXT, price_per_person NUMERIC, currency TEXT, min_group_size INT, max_group_size INT, availability JSONB, -- e.g. array of available date ranges images TEXT[], -- s3 URLs video_urls TEXT[], featured_until timestamptz, created_at timestamptz DEFAULT now() ); -- bookings CREATE TABLE bookings ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), listing_id UUID REFERENCES listings(id), hunter_id UUID REFERENCES users(id), start_date DATE, end_date DATE, total_price NUMERIC, currency TEXT, status TEXT CHECK (status IN ('pending','confirmed','checked_in','completed','cancelled','refunded')), stripe_payment_intent TEXT, created_at timestamptz DEFAULT now() ); -- reviews CREATE TABLE reviews ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), booking_id UUID REFERENCES bookings(id), rating INT, comment TEXT, created_at timestamptz DEFAULT now() );