sql
-- start a transaction
begin;

-- drop the public schema and recreate it
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

-- enable extensions
create extension if not exists "uuid-ossp" with schema extensions;
create extension if not exists moddatetime schema extensions;


-- create custom types
create type public.gender as enum('male', 'female');
create type public.appointment_type as enum('drive_through', 'drive_over');
create type public.appointment_status as enum('pending', 'assigned', 'completed' );

-- create tables with comments
-- PROFILES
create table if not exists public.profiles(
  id uuid references auth.users  primary key,
  username text unique not null,
  avatar_url text,
  first_name text,
  last_name text,
  other_names text,
  gender gender,
  email text,
  phone text,
  created_at timestamp with time zone default now() not null,
  updated_at timestamp with time zone default now() not null
);
comment on table public.profiles is 'all profiles for the users';
comment on column public.profiles.id is 'references the private auth user';
comment on column public.profiles.username is 'a unique name to identify the user';

{
flutter: │ 💡   "data": null,
flutter: │ 💡   "status": 403,
flutter: │ 💡   "error": {
flutter: │ 💡     "message": "permission denied for table products",
flutter: │ 💡     "details": null,
flutter: │ 💡     "hint": null,
flutter: │ 💡     "code": "42501"
flutter: │ 💡   },
flutter: │ 💡   "count": null
flutter: │ 💡 }