CREATE TABLE public.course_purchase_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  course_id uuid NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  amount_kes integer NOT NULL DEFAULT 0,
  payment_method text NOT NULL,
  payment_reference text NOT NULL,
  contact_email text,
  contact_phone text,
  notes text,
  status text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.course_purchase_requests TO authenticated;
GRANT ALL ON public.course_purchase_requests TO service_role;
ALTER TABLE public.course_purchase_requests ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users create own course purchase requests"
ON public.course_purchase_requests
FOR INSERT TO authenticated
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users read own course purchase requests"
ON public.course_purchase_requests
FOR SELECT TO authenticated
USING (auth.uid() = user_id);
CREATE TRIGGER course_purchase_requests_updated_at
BEFORE UPDATE ON public.course_purchase_requests
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

CREATE TABLE public.course_entitlements (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  course_id uuid NOT NULL REFERENCES public.courses(id) ON DELETE CASCADE,
  source text NOT NULL DEFAULT 'purchase',
  granted_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz,
  UNIQUE (user_id, course_id)
);
GRANT SELECT ON public.course_entitlements TO authenticated;
GRANT ALL ON public.course_entitlements TO service_role;
ALTER TABLE public.course_entitlements ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users read own course access"
ON public.course_entitlements
FOR SELECT TO authenticated
USING (auth.uid() = user_id);

CREATE OR REPLACE FUNCTION public.grant_approved_course_purchase()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF NEW.status = 'approved' AND OLD.status IS DISTINCT FROM 'approved' THEN
    INSERT INTO public.course_entitlements (user_id, course_id, source)
    VALUES (NEW.user_id, NEW.course_id, 'purchase')
    ON CONFLICT (user_id, course_id)
    DO UPDATE SET source = EXCLUDED.source, granted_at = now(), expires_at = NULL;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER grant_course_access_on_approval
AFTER UPDATE OF status ON public.course_purchase_requests
FOR EACH ROW EXECUTE FUNCTION public.grant_approved_course_purchase();