diferencias entrre -- Supabase AI is experimental and may produce incorrect answers
-- Always verify the output before executing

-- Supabase AI is experimental and may produce incorrect answers
-- Always verify the output before executing
-- PostgreSQL setup script
-- Drop tables if they exist
drop table if exists public.smtp_settings;

drop table if exists public.profiles;

drop table if exists public.bookings;

drop table if exists public.services;

drop table if exists public.users;

create table
  public.users (
    id uuid not null default gen_random_uuid (),
    email text unique not null,
    encrypted_password text not null,
    role text not null default 'user',
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint CK_email check (email like '%@%'),
    constraint users_pkey primary key (id)
  ) tablespace pg_default;

drop table if exists public.services;

create table
  public.services (
    id uuid not null default gen_random_uuid (),
    name text not null,
    description text,
    base_price numeric(10, 2) not null check (base_price >= 0),
    price_per_km numeric(10, 2) not null check (price_per_km >= 0),
    maneuver_charge numeric(10, 2) not null check (maneuver_charge >= 0),
    tow_truck_type text not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint services_pkey primary key (id)
  ) tablespace pg_default;

drop table if exists public.bookings;

create table
  public.bookings (
    id uuid not null default gen_random_uuid (),
    user_id uuid not null,
    service_id uuid not null,
    status text not null default 'pending',
    payment_status text 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 check (distance > 0),
    total_cost numeric(10, 2) not null check (total_cost > 0),
    pickup_datetime timestamp with time zone not null,
    additional_details text,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint bookings_pkey primary key (id),
    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
  ) tablespace pg_default;

drop table if exists public.profiles;

create table
  public.profiles (
    id uuid not null default gen_random_uuid (),
    user_id uuid not null,
    full_name text not null,
    phone_number text,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint uc_user_profile unique (user_id),
    constraint CK_phone check (phone_number ~ '^
+?[1-9][0-9]*$'),
    constraint profiles_pkey primary key (id)
  ) tablespace pg_default;

drop table if exists public.smtp_settings;

create table
  public.smtp_settings (
    id uuid not null default gen_random_uuid (),
    user_id uuid not null,
    host text not null,
    port integer not null,
    username text not null,
    password text not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint smtp_settings_pkey primary key (id),
    constraint smtp_settings_user_id_fkey foreign key (user_id) references public.users (id) on delete cascade
  ) tablespace pg_default;

-- Create lookup tables for ENUMs
create table if not exists
  public.user_roles (role text primary key);

insert into
  public.user_roles (role)
values
  ('user'),
  ('admin'),
  ('super_admin');

create table if not exists
  public.booking_statuses (status text primary key);

insert into
  public.booking_statuses (status)
values
  ('pending'),
  ('confirmed'),
  ('in_progress'),
  ('completed'),
  ('cancelled');

create table if not exists
  public.payment_statuses (payment_status text primary key);

insert into
  public.payment_statuses (payment_status)
values
  ('pending'),
  ('paid'),
  ('failed'),
  ('refunded');

create table if not exists
  public.vehicle_sizes (size text primary key);

insert into
  public.vehicle_sizes (size)
values
  ('small'),
  ('medium'),
  ('large');

create table if not exists
  public.tow_truck_types (tow_truck_type text primary key);

insert into
  public.tow_truck_types (tow_truck_type)
values
  ('flatbed'),
  ('wheel_lift'),
  ('integrated'),
  ('heavy_duty');     y 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]{2,}$')
);

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

CREATE TABLE IF NOT EXISTS public.bookings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    service_id UUID NOT NULL REFERENCES public.services(id) ON DELETE RESTRICT,
    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 CHECK (distance > 0),
    total_cost NUMERIC(10, 2) NOT NULL CHECK (total_cost > 0),
    pickup_datetime TIMESTAMPTZ NOT NULL,
    additional_details TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    full_name TEXT NOT NULL,
    phone_number TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT unique_user_profile UNIQUE (user_id),
    CONSTRAINT phone_number_format CHECK (phone_number ~ '^\+?[1-9]\d{1,14}$')
);

CREATE TABLE IF NOT EXISTS public.smtp_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    host TEXT NOT NULL,
    port INTEGER NOT NULL,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
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 $$;