-- Create referrals table (Indique Amigos)
CREATE TABLE IF NOT EXISTS public.referrals (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  your_name TEXT NOT NULL,
  your_email TEXT NOT NULL,
  your_phone TEXT NOT NULL,
  friend_name TEXT NOT NULL,
  friend_email TEXT NOT NULL,
  friend_phone TEXT NOT NULL,
  message TEXT,
  status TEXT NOT NULL DEFAULT 'pending', -- pending, contacted, converted
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT referrals_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

-- Create job applications table (Trabalhe Conosco)
CREATE TABLE IF NOT EXISTS public.job_applications (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT NOT NULL,
  position TEXT NOT NULL,
  cv_url TEXT,
  cover_letter TEXT,
  status TEXT NOT NULL DEFAULT 'pending', -- pending, reviewing, approved, rejected
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT job_applications_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

-- Create gallery images table
CREATE TABLE IF NOT EXISTS public.gallery_images (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT,
  image_url TEXT NOT NULL,
  category TEXT NOT NULL, -- campus, events, students, infrastructure
  display_order INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT gallery_images_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_referrals_status ON public.referrals USING btree (status);
CREATE INDEX IF NOT EXISTS idx_job_applications_status ON public.job_applications USING btree (status);
CREATE INDEX IF NOT EXISTS idx_gallery_category ON public.gallery_images USING btree (category);
CREATE INDEX IF NOT EXISTS idx_gallery_display_order ON public.gallery_images USING btree (display_order);

-- Enable RLS
ALTER TABLE public.referrals ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.job_applications ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.gallery_images ENABLE ROW LEVEL SECURITY;

-- RLS Policies for referrals
CREATE POLICY "Anyone can submit referrals" ON public.referrals
  FOR INSERT WITH CHECK (true);

CREATE POLICY "Only admins can view referrals" ON public.referrals
  FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "Only admins can update referrals" ON public.referrals
  FOR UPDATE USING (auth.role() = 'authenticated');

CREATE POLICY "Only admins can delete referrals" ON public.referrals
  FOR DELETE USING (auth.role() = 'authenticated');

-- RLS Policies for job applications
CREATE POLICY "Anyone can submit job applications" ON public.job_applications
  FOR INSERT WITH CHECK (true);

CREATE POLICY "Only admins can view job applications" ON public.job_applications
  FOR SELECT USING (auth.role() = 'authenticated');

CREATE POLICY "Only admins can update job applications" ON public.job_applications
  FOR UPDATE USING (auth.role() = 'authenticated');

CREATE POLICY "Only admins can delete job applications" ON public.job_applications
  FOR DELETE USING (auth.role() = 'authenticated');

-- RLS Policies for gallery
CREATE POLICY "Anyone can view active gallery images" ON public.gallery_images
  FOR SELECT USING (is_active = true);

CREATE POLICY "Only admins can insert gallery images" ON public.gallery_images
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

CREATE POLICY "Only admins can update gallery images" ON public.gallery_images
  FOR UPDATE USING (auth.role() = 'authenticated');

CREATE POLICY "Only admins can delete gallery images" ON public.gallery_images
  FOR DELETE USING (auth.role() = 'authenticated');
