-- Create enrollments table to store student enrollment data
CREATE TABLE IF NOT EXISTS public.enrollments (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  full_name TEXT NOT NULL,
  cpf TEXT NOT NULL,
  birth_date DATE NOT NULL,
  email TEXT NOT NULL,
  phone TEXT NOT NULL,
  course_id UUID NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  modality TEXT NOT NULL,
  shift TEXT NOT NULL,
  campus TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  CONSTRAINT enrollments_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_enrollments_email ON public.enrollments USING btree (email) TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_enrollments_cpf ON public.enrollments USING btree (cpf) TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_enrollments_status ON public.enrollments USING btree (status) TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS idx_enrollments_created_at ON public.enrollments USING btree (created_at DESC) TABLESPACE pg_default;

-- Enable Row Level Security
ALTER TABLE public.enrollments ENABLE ROW LEVEL SECURITY;

-- Policy: Public can insert enrollments (submit applications)
CREATE POLICY "Anyone can submit enrollments"
  ON public.enrollments
  FOR INSERT
  TO public
  WITH CHECK (true);

-- Policy: Only authenticated users can view enrollments
CREATE POLICY "Authenticated users can view enrollments"
  ON public.enrollments
  FOR SELECT
  TO authenticated
  USING (true);

-- Policy: Only authenticated users can update enrollments
CREATE POLICY "Authenticated users can update enrollments"
  ON public.enrollments
  FOR UPDATE
  TO authenticated
  USING (true);

-- Policy: Only authenticated users can delete enrollments
CREATE POLICY "Authenticated users can delete enrollments"
  ON public.enrollments
  FOR DELETE
  TO authenticated
  USING (true);
