ERROR:  23505: duplicate key value violates unique constraint "buckets_pkey"
DETAIL:  Key (id)=(avatars) already exists.-- Supabase AI is experimental and may produce incorrect answers
-- Always verify the output before executing

-- File: src/database/setup.sql
-- Enable necessary extensions
create extension if not exists "uuid-ossp";

create extension if not exists "pgcrypto";

-- ENUM types
do $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_role') THEN
        CREATE TYPE user_role AS ENUM('user', 'admin', 'super_admin');
    END IF;
END $$;

do $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'booking_status') THEN
        CREATE TYPE booking_status AS ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled');
    END IF;
END $$;

do $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'payment_status') THEN
        CREATE TYPE payment_status AS ENUM('pending', 'paid', 'failed', 'refunded');
    END IF;
END $$;

do $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'vehicle_size') THEN
        CREATE TYPE vehicle_size AS ENUM('small', 'medium', 'large');
    END IF;
END $$;

do $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'tow_truck_type') THEN
        CREATE TYPE tow_truck_type AS ENUM('flatbed', 'wheel_lift', 'integrated', 'heavy_duty');
    END IF;
END $$;

-- Drop existing tables and their dependencies
do $$

BEGIN

DROP TABLE IF EXISTS public.bookings CASCADE;

DROP TABLE IF EXISTS public.profiles CASCADE;

DROP TABLE IF EXISTS public.services CASCADE;

DROP TABLE IF EXISTS public.smtp_settings CASCADE;

DROP TABLE IF EXISTS public.users CASCADE;

DROP TABLE IF EXISTS public.password_failed_verification_attempts CASCADE;

EXCEPTION

WHEN OTHERS THEN null;

END $$;

-- Create tables
create table if not exists
  public.users (
    id uuid primary key default uuid_generate_v4 (),
    email text unique not null,
    encrypted_password text not null,
    role user_role not null default 'user',
    created_at TIMESTAMPTZ not null default now(),
    updated_at TIMESTAMPTZ not null default now(),
    constraint email_format check (
      email ~* '^[A-Za-z0-9._+%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'
    )
  );

-- Create public services table
create table if not exists
  public.services (
    id uuid primary key default uuid_generate_v4 (),
    name text not null,
    description text null,
    base_price numeric(10, 2) not null,
    price_per_km numeric(10, 2) not null,
    maneuver_charge numeric(10, 2) not null,
    tow_truck_type tow_truck_type not null,
    created_at TIMESTAMPTZ not null default now(),
    updated_at TIMESTAMPTZ not null default now(),
    constraint services_base_price_check check ((base_price >= (0)::numeric)),
    constraint services_maneuver_charge_check check ((maneuver_charge >= (0)::numeric)),
    constraint services_price_per_km_check check ((price_per_km >= (0)::numeric))
  );

-- Create bookings table
create table if not exists
  public.bookings (
    id uuid primary key default uuid_generate_v4 (),
    user_id uuid not null,
    service_id uuid not null,
    status booking_status not null default 'pending',
    payment_status payment_status not null default 'pending',
    pickup_location text not null,
    dropoff_location text not null,
    vehicle_details jsonb not null,
    distance numeric(10, 2) not null,
    total_cost numeric(10, 2) not null,
    pickup_datetime TIMESTAMPTZ not null,
    additional_details text,
    created_at TIMESTAMPTZ not null default now(),
    updated_at TIMESTAMPTZ not null default now(),
    constraint bookings_user_id_fkey foreign key (user_id) references public.users (id) on delete cascade,
    constraint bookings_service_id_fkey foreign key (service_id) references public.services (id) on delete restrict
  );

-- Create public profiles table
create table if not exists
  public.profiles (
    id uuid primary key references auth.users on delete cascade,
    updated_at TIMESTAMPTZ,
    username text unique,
    full_name text,
    avatar_url text,
    website text,
    constraint username_length check (char_length(username) >= 3)
  );

-- Create password failed verification attempts table
create table if not exists
  public.password_failed_verification_attempts (
    user_id uuid not null primary key,
    last_failed_at TIMESTAMPTZ not null
  );

-- Indexes
create index if not exists idx_users_email on public.users (email);

create index if not exists idx_bookings_user_id on public.bookings (user_id);

create index if not exists idx_bookings_service_id on public.bookings (service_id);

create index if not exists idx_bookings_status on public.bookings (status);

create index if not exists idx_bookings_pickup_datetime on public.bookings (pickup_datetime);

-- Triggers
create
or replace function update_updated_at () returns trigger as $$

BEGIN

NEW.updated_at = NOW();

RETURN NEW;

END;

$$ language plpgsql security definer
set
  search_path = public;

-- ... keep existing code for trigger creations
-- Functions
create
or replace function public.handle_new_user () returns trigger as $$

BEGIN

INSERT INTO public.profiles (id, full_name, avatar_url)

VALUES (NEW.id, NEW.raw_user_meta_data->>'full_name', NEW.raw_user_meta_data->>'avatar_url');

RETURN NEW;

END;

$$ language plpgsql security definer;

create
or replace function public.hook_password_verification_attempt (event JSONB) returns JSONB as $$

DECLARE

last_failed_at TIMESTAMPTZ;

BEGIN

-- ... keep existing code for hook_password_verification_attempt

END;

$$ language plpgsql;

-- New custom access token hook function
create
or replace function public.custom_access_token_hook (event jsonb) returns jsonb language plpgsql as $$

declare

-- Insert variables here

begin

-- Insert logic here

return event;

end;

$$;

-- Set up Storage
insert into
  storage.buckets (id, name)
values
  ('avatars', 'avatars');

-- Policies
alter table public.profiles enable row level security;

-- ... keep existing code for policy creations
-- Permissions
grant
execute on function public.hook_password_verification_attempt to supabase_auth_admin;

grant all on table public.password_failed_verification_attempts to supabase_auth_admin;

revoke
execute on function public.hook_password_verification_attempt
from
  authenticated,
  anon,
  public;

revoke all on table public.password_failed_verification_attempts
from
  authenticated,
  anon,
  public;

grant USAGE on schema public to supabase_auth_admin;

-- Permissions for the new custom access token hook
grant
execute on function public.custom_access_token_hook to supabase_auth_admin;

revoke
execute on function public.custom_access_token_hook
from
  authenticated,
  anon,
  public;

-- Sample data insertions (optional, can be removed for production)
-- ... keep existing code for sample data insertions;correct wehit -- File: src/database/setup.sql

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ENUM types
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_role') THEN
        CREATE TYPE user_role AS ENUM('user', 'admin', 'super_admin');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'booking_status') THEN
        CREATE TYPE booking_status AS ENUM('pending', 'confirmed', 'in_progress', 'completed', 'cancelled');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'payment_status') THEN
        CREATE TYPE payment_status AS ENUM('pending', 'paid', 'failed', 'refunded');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'vehicle_size') THEN
        CREATE TYPE vehicle_size AS ENUM('small', 'medium', 'large');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'tow_truck_type') THEN
        CREATE TYPE tow_truck_type AS ENUM('flatbed', 'wheel_lift', 'integrated', 'heavy_duty');
    END IF;
END $$;

-- Drop existing tables and their dependencies
DO $$
BEGIN
    DROP TABLE IF EXISTS public.bookings CASCADE;
    DROP TABLE IF EXISTS public.profiles CASCADE;
    DROP TABLE IF EXISTS public.services CASCADE;
    DROP TABLE IF EXISTS public.smtp_settings CASCADE;
    DROP TABLE IF EXISTS public.users CASCADE;
    DROP TABLE IF EXISTS public.password_failed_verification_attempts CASCADE;
EXCEPTION
    WHEN OTHERS THEN null;
END $$;

-- Create tables
CREATE TABLE IF NOT EXISTS public.users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT UNIQUE NOT NULL,
    encrypted_password TEXT NOT NULL,
    role user_role NOT NULL DEFAULT 'user',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._+%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);

-- Create public services table
CREATE TABLE IF NOT EXISTS public.services (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    description TEXT,
    base_price NUMERIC(10, 2) NOT NULL,
    price_per_km NUMERIC(10, 2) NOT NULL,
    maneuver_charge NUMERIC(10, 2) NOT NULL,
    tow_truck_type tow_truck_type NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT services_base_price_check CHECK (base_price >= 0),
    CONSTRAINT services_maneuver_charge_check CHECK (maneuver_charge >= 0),
    CONSTRAINT services_price_per_km_check CHECK (price_per_km >= 0)
);

-- Create bookings table
CREATE TABLE IF NOT EXISTS public.bookings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL,
    service_id UUID NOT NULL,
    status booking_status NOT NULL DEFAULT 'pending',
    payment_status payment_status NOT NULL DEFAULT 'pending',
    pickup_location TEXT NOT NULL,
    dropoff_location TEXT NOT NULL,
    vehicle_details JSONB NOT NULL,
    distance NUMERIC(10, 2) NOT NULL,
    total_cost NUMERIC(10, 2) NOT NULL,
    pickup_datetime TIMESTAMPTZ NOT NULL,
    additional_details TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT bookings_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users (id) ON DELETE CASCADE,
    CONSTRAINT bookings_service_id_fkey FOREIGN KEY (service_id) REFERENCES public.services (id) ON DELETE RESTRICT
);

-- Create public profiles table
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID