317 lines
11 KiB
PL/PgSQL
317 lines
11 KiB
PL/PgSQL
-- Extensions
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Enums
|
|
DO $$ BEGIN
|
|
CREATE TYPE routestatus AS ENUM ('ACTIVE', 'INACTIVE', 'MAINTENANCE');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE stoptype AS ENUM ('TERMINAL', 'REGULAR', 'EXPRESS_ONLY');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE busscheduletype AS ENUM ('WEEKDAY', 'WEEKEND', 'HOLIDAY');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE userrole AS ENUM ('ADMIN', 'PASSENGER', 'DRIVER', 'PROMOTER');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE vehicletype AS ENUM ('taxi', 'bus');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE usercouponstatus AS ENUM ('claimed', 'redeemed', 'expired');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- 1. Users
|
|
CREATE TABLE IF NOT EXISTS public.users (
|
|
id UUID PRIMARY KEY, -- Will link to auth.users.id
|
|
email TEXT UNIQUE NOT NULL,
|
|
full_name TEXT NOT NULL,
|
|
role userrole DEFAULT 'PASSENGER'::userrole,
|
|
is_active BOOLEAN DEFAULT true,
|
|
is_verified BOOLEAN DEFAULT false,
|
|
profile_photo_url TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 2. Driver Profiles
|
|
CREATE TABLE IF NOT EXISTS public.driver_profiles (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
|
|
cedula TEXT NOT NULL,
|
|
vehicle_type vehicletype NOT NULL,
|
|
license_plate TEXT NOT NULL,
|
|
photo_url TEXT,
|
|
vehicle_photo_url TEXT,
|
|
cooperative_name TEXT,
|
|
shift TEXT,
|
|
payment_methods TEXT,
|
|
speaks_english BOOLEAN DEFAULT false
|
|
);
|
|
|
|
-- 3. Bus Stops
|
|
CREATE TABLE IF NOT EXISTS public.bus_stops (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name TEXT NOT NULL,
|
|
latitude FLOAT NOT NULL,
|
|
longitude FLOAT NOT NULL,
|
|
city TEXT NOT NULL,
|
|
address TEXT,
|
|
stop_type stoptype NOT NULL,
|
|
has_shelter BOOLEAN DEFAULT false,
|
|
has_seating BOOLEAN DEFAULT false,
|
|
is_accessible BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 4. Routes
|
|
CREATE TABLE IF NOT EXISTS public.routes (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name TEXT UNIQUE NOT NULL,
|
|
description TEXT,
|
|
origin_city TEXT NOT NULL,
|
|
destination_city TEXT NOT NULL,
|
|
distance_km FLOAT,
|
|
estimated_duration_minutes INTEGER,
|
|
average_speed_kmh FLOAT,
|
|
color TEXT DEFAULT '#FEE715',
|
|
direction TEXT DEFAULT 'outbound',
|
|
status routestatus DEFAULT 'ACTIVE'::routestatus,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 5. Route Stops
|
|
CREATE TABLE IF NOT EXISTS public.route_stops (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
route_id UUID REFERENCES public.routes(id) ON DELETE CASCADE,
|
|
stop_id UUID REFERENCES public.bus_stops(id) ON DELETE CASCADE,
|
|
stop_order INTEGER NOT NULL,
|
|
travel_time_minutes INTEGER,
|
|
stop_delay_minutes INTEGER DEFAULT 0,
|
|
is_pickup_point BOOLEAN DEFAULT false,
|
|
is_dropoff_point BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 6. Bus Schedules
|
|
CREATE TABLE IF NOT EXISTS public.bus_schedules (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
route_id UUID REFERENCES public.routes(id) ON DELETE CASCADE,
|
|
departure_time TIME NOT NULL,
|
|
frequency_minutes INTEGER,
|
|
schedule_type busscheduletype NOT NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
is_published BOOLEAN DEFAULT true,
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 7. Businesses
|
|
CREATE TABLE IF NOT EXISTS public.businesses (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name TEXT NOT NULL,
|
|
address TEXT,
|
|
phone TEXT,
|
|
image_url TEXT,
|
|
social_media TEXT,
|
|
category TEXT,
|
|
latitude FLOAT,
|
|
longitude FLOAT,
|
|
area TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 8. Coupons
|
|
CREATE TABLE IF NOT EXISTS public.coupons (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
business_id UUID REFERENCES public.businesses(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
image_url TEXT,
|
|
social_media TEXT,
|
|
terms TEXT,
|
|
discount_percentage FLOAT,
|
|
discount_amount FLOAT,
|
|
category TEXT,
|
|
valid_from TIMESTAMPTZ,
|
|
valid_until TIMESTAMPTZ,
|
|
is_active BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 9. Shuttles
|
|
CREATE TABLE IF NOT EXISTS public.shuttles (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
route_name TEXT NOT NULL,
|
|
description TEXT,
|
|
origin TEXT NOT NULL,
|
|
destination TEXT NOT NULL,
|
|
vehicle_type TEXT NOT NULL,
|
|
company_name TEXT,
|
|
trip_type TEXT DEFAULT 'one_way',
|
|
price_per_person FLOAT,
|
|
price_private_trip FLOAT,
|
|
estimated_duration TEXT,
|
|
departure_times TEXT,
|
|
contact_whatsapp TEXT NOT NULL,
|
|
phone_number TEXT,
|
|
english_speaking BOOLEAN DEFAULT false,
|
|
image_url TEXT,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 10. Taxis
|
|
CREATE TABLE IF NOT EXISTS public.taxis (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
owner_name TEXT NOT NULL,
|
|
phone_number TEXT NOT NULL,
|
|
license_plate TEXT NOT NULL,
|
|
cooperative TEXT,
|
|
corregimiento TEXT NOT NULL,
|
|
shift TEXT NOT NULL,
|
|
rating FLOAT,
|
|
english_speaking BOOLEAN DEFAULT false,
|
|
image_url TEXT,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 11. Favorites
|
|
CREATE TABLE IF NOT EXISTS public.favorites (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
|
|
item_type TEXT NOT NULL,
|
|
item_id UUID NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 12. User Coupons
|
|
CREATE TABLE IF NOT EXISTS public.user_coupons (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
|
|
coupon_id UUID REFERENCES public.coupons(id) ON DELETE CASCADE,
|
|
status usercouponstatus DEFAULT 'claimed'::usercouponstatus,
|
|
redemption_code TEXT NOT NULL,
|
|
claimed_at TIMESTAMPTZ DEFAULT NOW(),
|
|
redeemed_at TIMESTAMPTZ,
|
|
UNIQUE(user_id, coupon_id)
|
|
);
|
|
|
|
-- TRIGGER FOR AUTH.USERS -> PUBLIC.USERS
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
INSERT INTO public.users (id, email, full_name, role)
|
|
VALUES (
|
|
new.id,
|
|
new.email,
|
|
COALESCE(new.raw_user_meta_data->>'full_name', new.email),
|
|
COALESCE((new.raw_user_meta_data->>'role')::userrole, 'PASSENGER'::userrole)
|
|
);
|
|
RETURN new;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
-- Trigger firing when a new user signs up
|
|
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
|
|
CREATE TRIGGER on_auth_user_created
|
|
AFTER INSERT ON auth.users
|
|
FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();
|
|
|
|
-- SET DEFAULT RLS (Row Level Security) TO EVERY TABLE
|
|
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.routes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.bus_stops ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.route_stops ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.bus_schedules ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.shuttles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.businesses ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.coupons ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.user_coupons ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.taxis ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.favorites ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.driver_profiles ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- SIMPLE POLICIES (Read for all, Insert/Update/Delete requires auth)
|
|
-- You can tighten these up later, this gets you going securely.
|
|
|
|
-- Routes, Stops, Shuttles, Taxis, Businesses, Coupons: public read
|
|
CREATE POLICY "Public Read" ON public.routes FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.bus_stops FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.route_stops FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.bus_schedules FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.shuttles FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.businesses FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.coupons FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.taxis FOR SELECT USING (true);
|
|
CREATE POLICY "Public Read" ON public.driver_profiles FOR SELECT USING (true);
|
|
|
|
-- Users can read their own data, Admins can read everything
|
|
CREATE POLICY "Users can view own data" ON public.users FOR SELECT USING (auth.uid() = id);
|
|
CREATE POLICY "Users can update own data" ON public.users FOR UPDATE USING (auth.uid() = id);
|
|
|
|
-- Allow admins to manage (This policy assumes admins are users with role = 'ADMIN')
|
|
CREATE OR REPLACE FUNCTION is_admin() RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
RETURN EXISTS (SELECT 1 FROM public.users WHERE id = auth.uid() AND role = 'ADMIN');
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
CREATE POLICY "Admins full access" ON public.routes FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.bus_stops FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.route_stops FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.bus_schedules FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.shuttles FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.businesses FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.coupons FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.taxis FOR ALL USING (is_admin());
|
|
CREATE POLICY "Admins full access" ON public.users FOR ALL USING (is_admin());
|
|
|
|
-- Favorites and User Coupons
|
|
CREATE POLICY "Users can view own favorites" ON public.favorites FOR SELECT USING (auth.uid() = user_id);
|
|
CREATE POLICY "Users can manage own favorites" ON public.favorites FOR ALL USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can view own coupons" ON public.user_coupons FOR SELECT USING (auth.uid() = user_id);
|
|
CREATE POLICY "Users can manage own coupons" ON public.user_coupons FOR ALL USING (auth.uid() = user_id);
|
|
|
|
-- 13. Analytics Events
|
|
CREATE TABLE IF NOT EXISTS public.analytics_events (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
event_name TEXT NOT NULL,
|
|
entity_type TEXT NOT NULL,
|
|
entity_id TEXT NOT NULL,
|
|
entity_name TEXT,
|
|
user_id UUID REFERENCES public.users(id) ON DELETE SET NULL,
|
|
properties JSONB DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Analytics events RLS
|
|
ALTER TABLE public.analytics_events ENABLE ROW LEVEL SECURITY;
|
|
CREATE POLICY "Public can insert analytics events" ON public.analytics_events FOR INSERT WITH CHECK (true);
|
|
CREATE POLICY "Admins can view all analytics events" ON public.analytics_events FOR SELECT USING (is_admin());
|