database_sp

// Use these commands in supabases's sql editor to create database

1. Create User Table
create table public.users (
  id uuid not null,
  full_name text null,
  email text not null,
  role text not null default 'user'::text,
  phone text null,
  is_active boolean null default true,
  created_at timestamp with time zone null default now(),
  parkingadmin_id uuid null,
  constraint users_pkey primary key (id),
  constraint users_email_key unique (email),
  constraint users_id_fkey foreign KEY (id) references auth.users (id) on delete CASCADE,
  constraint users_parkingadmin_id_fkey foreign KEY (parkingadmin_id) references users (id),
  constraint users_role_check check (
    (
      role = any (
        array[
          'user'::text,
          'parking_admin'::text,
          'parking_manager'::text,
          'super_admin'::text
        ]
      )
    )
  )
) TABLESPACE pg_default;


2. Create Parkings Table
create table public.parkings (
  id uuid not null default gen_random_uuid (),
  name text not null,
  description text null,
  latitude double precision not null,
  longitude double precision not null,
  hourly_price numeric(10, 2) not null,
  owner_id uuid not null,
  is_active boolean null default true,
  created_at timestamp with time zone null default now(),
  updated_at timestamp with time zone null default now(),
  constraint parkings_pkey primary key (id),
  constraint parkings_owner_id_fkey foreign KEY (owner_id) references users (id)
) TABLESPACE pg_default;


3. Create parking slots table
create table public.parking_slots (
  id uuid not null default gen_random_uuid (),
  parking_id uuid not null,
  slot_code text not null,
  status text not null default 'free'::text,
  created_at timestamp with time zone null default now(),
  constraint parking_slots_pkey primary key (id),
  constraint parking_slots_parking_id_slot_code_key unique (parking_id, slot_code),
  constraint parking_slots_parking_id_fkey foreign KEY (parking_id) references parkings (id) on delete CASCADE,
  constraint parking_slots_status_check check (
    (
      status = any (
        array['free'::text, 'occupied'::text, 'reserved'::text]
      )
    )
  )
) TABLESPACE pg_default;


4. Create Bookings Table
create table public.bookings (
  id uuid not null default gen_random_uuid (),
  user_id uuid not null,
  parking_id uuid not null,
  slot_id uuid not null,
  start_time timestamp with time zone not null,
  end_time timestamp with time zone null,
  status text not null default 'pending'::text,
  qr_token uuid null,
  qr_expires_at timestamp with time zone null,
  created_at timestamp with time zone null default now(),
  constraint bookings_pkey primary key (id),
  constraint bookings_parking_id_fkey foreign KEY (parking_id) references parkings (id),
  constraint bookings_slot_id_fkey foreign KEY (slot_id) references parking_slots (id),
  constraint bookings_user_id_fkey foreign KEY (user_id) references users (id),
  constraint bookings_status_check check (
    (
      status = any (
        array[
          'pending'::text,
          'confirmed'::text,
          'cancelled'::text,
          'completed'::text
        ]
      )
    )
  )
) TABLESPACE pg_default;


5. Create payments Table
create table public.payments (
  id uuid not null default gen_random_uuid (),
  booking_id uuid not null,
  user_id uuid not null,
  amount numeric(10, 2) not null,
  payment_type text not null,
  payment_method text null,
  payment_status text not null default 'pending'::text,
  paid_at timestamp with time zone null,
  created_at timestamp with time zone null default now(),
  constraint payments_pkey primary key (id),
  constraint payments_booking_id_fkey foreign KEY (booking_id) references bookings (id),
  constraint payments_user_id_fkey foreign KEY (user_id) references users (id),
  constraint payments_payment_method_check check (
    (
      payment_method = any (array['upi'::text, 'card'::text, 'cash'::text])
    )
  ),
  constraint payments_payment_status_check check (
    (
      payment_status = any (
        array['pending'::text, 'paid'::text, 'failed'::text]
      )
    )
  ),
  constraint payments_payment_type_check check (
    (
      payment_type = any (array['advance'::text, 'balance'::text])
    )
  )
) TABLESPACE pg_default;




/// Change time zone 
ALTER DATABASE postgres SET timezone TO 'Asia/Kolkata';