-- Create referral codes table
CREATE TABLE IF NOT EXISTS public.referral_codes (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  code text NOT NULL UNIQUE,
  name text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  uses_count integer NOT NULL DEFAULT 0,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now(),
  CONSTRAINT referral_codes_pkey PRIMARY KEY (id)
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_referral_codes_code ON public.referral_codes USING btree (code);
CREATE INDEX IF NOT EXISTS idx_referral_codes_active ON public.referral_codes USING btree (is_active);

-- Add referral_code column to enrollments table
ALTER TABLE public.enrollments 
ADD COLUMN IF NOT EXISTS referral_code text;

-- Create index for referral code in enrollments
CREATE INDEX IF NOT EXISTS idx_enrollments_referral_code ON public.enrollments USING btree (referral_code);

-- Enable RLS
ALTER TABLE public.referral_codes ENABLE ROW LEVEL SECURITY;

-- Policy: Anyone can read active referral codes
CREATE POLICY "Anyone can read active referral codes"
ON public.referral_codes
FOR SELECT
USING (is_active = true);

-- Policy: Only authenticated users can manage referral codes
CREATE POLICY "Authenticated users can manage referral codes"
ON public.referral_codes
FOR ALL
TO authenticated
USING (true)
WITH CHECK (true);

-- Add comment
COMMENT ON TABLE public.referral_codes IS 'Stores referral codes for tracking enrollment sources';
