database.sql

-- WARNING: This schema is for context only and is not meant to be run.
-- Table order and constraints may not be valid for execution.

CREATE TABLE public.attendee_preferences (
  pref_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL UNIQUE,
  pref_name character varying NOT NULL DEFAULT ''::character varying,
  CONSTRAINT attendee_preferences_pkey PRIMARY KEY (pref_id)
);
CREATE TABLE public.attendee_profiles (
  user_id uuid NOT NULL UNIQUE,
  first_name text NOT NULL,
  last_name text NOT NULL,
  phone_number text,
  age_bracket text,
  gender text,
  marital_status text,
  bio text,
  social_links jsonb,
  profile_image_url text,
  preferences jsonb,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  email text,
  CONSTRAINT attendee_profiles_pkey PRIMARY KEY (user_id),
  CONSTRAINT attendee_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
);
CREATE TABLE public.event_attendance (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  event_id bigint NOT NULL,
  user_id uuid NOT NULL,
  status USER-DEFINED NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  CONSTRAINT event_attendance_pkey PRIMARY KEY (id),
  CONSTRAINT event_attendance_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
  CONSTRAINT event_attendance_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.events(id)
);
CREATE TABLE public.event_comments (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  commentor_id uuid DEFAULT auth.uid(),
  event_id bigint,
  comment_text character varying DEFAULT ''::character varying,
  commentor_name character varying,
  CONSTRAINT event_comments_pkey PRIMARY KEY (id),
  CONSTRAINT event_comments_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.events(id)
);
CREATE TABLE public.event_types (
  type_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  type_name character varying NOT NULL DEFAULT ''::character varying,
  type_image_url character varying NOT NULL DEFAULT ''::character varying,
  CONSTRAINT event_types_pkey PRIMARY KEY (type_id)
);
CREATE TABLE public.events (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  name character varying DEFAULT ''::character varying,
  description character varying DEFAULT ''::character varying,
  type_id bigint,
  organizer_email character varying DEFAULT ''::character varying,
  organizer_contact_num character varying DEFAULT ''::character varying,
  location USER-DEFINED,
  starting_timestamp timestamp without time zone,
  street_address character varying DEFAULT ''::character varying,
  media jsonb DEFAULT '"[]"'::jsonb,
  whos_allowed character varying DEFAULT '"Everyone"'::character varying,
  organizer_id uuid DEFAULT auth.uid(),
  type_name character varying DEFAULT ''::character varying,
  ticket_price bigint,
  ticket_link character varying,
  organizer_name character varying DEFAULT ''::character varying,
  CONSTRAINT events_pkey PRIMARY KEY (id),
  CONSTRAINT events_type_id_fkey FOREIGN KEY (type_id) REFERENCES public.event_types(type_id)
);
CREATE TABLE public.follows (
  id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  followed_at timestamp with time zone NOT NULL DEFAULT now(),
  follower_id uuid NOT NULL DEFAULT auth.uid(),
  followee_id character varying DEFAULT ''::character varying,
  CONSTRAINT follows_pkey PRIMARY KEY (id),
  CONSTRAINT follows_follower_id_fkey FOREIGN KEY (follower_id) REFERENCES public.attendee_profiles(user_id)
);
CREATE TABLE public.notifications (
  id integer NOT NULL DEFAULT nextval('notifications_id_seq'::regclass),
  created_at timestamp with time zone DEFAULT now(),
  title character varying NOT NULL,
  message text NOT NULL,
  audience character varying NOT NULL,
  event_id integer,
  user_ids ARRAY,
  action_url text,
  status character varying DEFAULT 'pending'::character varying,
  sent_at timestamp with time zone,
  read_count integer DEFAULT 0,
  sender_id uuid,
  metadata jsonb,
  CONSTRAINT notifications_pkey PRIMARY KEY (id),
  CONSTRAINT notifications_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.events(id),
  CONSTRAINT notifications_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES auth.users(id)
);
CREATE TABLE public.organizer_company_profiles (
  user_id uuid NOT NULL,
  company_name text NOT NULL,
  company_email text NOT NULL UNIQUE,
  phone_number text NOT NULL,
  contact_person_first_name text NOT NULL,
  contact_person_last_name text NOT NULL,
  contact_person_email text NOT NULL,
  contact_person_phone_number text NOT NULL,
  contact_person_designation text NOT NULL,
  company_state text NOT NULL,
  company_city text NOT NULL,
  company_address text NOT NULL,
  company_logo_url text,
  company_bio text,
  event_types jsonb DEFAULT '[]'::jsonb,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  social_links jsonb DEFAULT '[]'::jsonb,
  CONSTRAINT organizer_company_profiles_pkey PRIMARY KEY (user_id),
  CONSTRAINT organizer_company_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
);
CREATE TABLE public.organizer_individual_profiles (
  user_id uuid NOT NULL,
  first_name text NOT NULL,
  last_name text NOT NULL,
  email text NOT NULL UNIQUE,
  phone_number text NOT NULL,
  age_bracket text,
  event_types jsonb DEFAULT '[]'::jsonb,
  bio text,
  profile_image_url text,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  social_links jsonb DEFAULT '[]'::jsonb,
  CONSTRAINT organizer_individual_profiles_pkey PRIMARY KEY (user_id),
  CONSTRAINT organizer_individual_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
);
CREATE TABLE public.spatial_ref_sys (
  srid integer NOT NULL CHECK (srid > 0 AND srid <= 998999),
  auth_name character varying,
  auth_srid integer,
  srtext character varying,
  proj4text character varying,
  CONSTRAINT spatial_ref_sys_pkey PRIMARY KEY (srid)
);
CREATE TABLE public.user_fcm_tokens (
  user_id uuid NOT NULL,
  fcm_token text NOT NULL,
  updated_at timestamp without time zone DEFAULT now(),
  CONSTRAINT user_fcm_tokens_pkey PRIMARY KEY (user_id),
  CONSTRAINT user_fcm_tokens_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
);
CREATE TABLE public.user_notification_preferences (
  user_id uuid NOT NULL,
  new_events boolean DEFAULT true,
  event_reminders boolean DEFAULT true,
  event_updates boolean DEFAULT true,
  event_cancellations boolean DEFAULT true,
  organizer_updates boolean DEFAULT false,
  updated_at timestamp without time zone DEFAULT now(),
  CONSTRAINT user_notification_preferences_pkey PRIMARY KEY (user_id),
  CONSTRAINT user_notification_preferences_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
);
CREATE TABLE public.user_notifications (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid,
  title text NOT NULL,
  body text NOT NULL,
  type text NOT NULL,
  priority text DEFAULT 'normal'::text,
  event_id bigint,
  organizer_id text,
  image_url text,
  data jsonb,
  is_read boolean DEFAULT false,
  is_actionable boolean DEFAULT false,
  action_url text,
  action_text text,
  created_at timestamp without time zone DEFAULT now(),
  CONSTRAINT user_notifications_pkey PRIMARY KEY (id),
  CONSTRAINT user_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
  CONSTRAINT user_notifications_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.events(id)
);