
-- Create webhook event logs table for debugging payment webhook deliveries
CREATE TABLE public.webhook_event_logs (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  gateway TEXT NOT NULL,
  event_type TEXT NOT NULL,
  event_id TEXT,
  status TEXT NOT NULL DEFAULT 'received',
  payload JSONB DEFAULT '{}'::jsonb,
  response_status INTEGER,
  error_message TEXT,
  processing_time_ms INTEGER,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Index for efficient querying
CREATE INDEX idx_webhook_event_logs_gateway ON public.webhook_event_logs (gateway);
CREATE INDEX idx_webhook_event_logs_created_at ON public.webhook_event_logs (created_at DESC);
CREATE INDEX idx_webhook_event_logs_status ON public.webhook_event_logs (status);

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

-- Only admins can view webhook logs
CREATE POLICY "Admins can view webhook event logs"
  ON public.webhook_event_logs
  FOR SELECT
  USING (has_role(auth.uid(), 'admin'::app_role));

-- Service role inserts (edge functions use service role key)
-- No INSERT policy needed for authenticated users since edge functions use service role
CREATE POLICY "Admins can delete webhook event logs"
  ON public.webhook_event_logs
  FOR DELETE
  USING (has_role(auth.uid(), 'admin'::app_role));
