-- Create table for payment gateway configurations (stores credentials securely)
CREATE TABLE public.payment_gateway_configs (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  gateway TEXT NOT NULL UNIQUE,
  is_enabled BOOLEAN DEFAULT false,
  is_test_mode BOOLEAN DEFAULT true,
  sandbox_config JSONB DEFAULT '{}'::jsonb,
  live_config JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

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

-- Only admins can read/write gateway configs
CREATE POLICY "Admins can manage gateway configs"
  ON public.payment_gateway_configs
  FOR ALL
  USING (public.has_role(auth.uid(), 'admin'))
  WITH CHECK (public.has_role(auth.uid(), 'admin'));

-- Trigger for updated_at
CREATE TRIGGER update_payment_gateway_configs_updated_at
  BEFORE UPDATE ON public.payment_gateway_configs
  FOR EACH ROW
  EXECUTE FUNCTION public.update_updated_at_column();

-- Insert default PayPal config row
INSERT INTO public.payment_gateway_configs (gateway, is_enabled, is_test_mode)
VALUES ('paypal', false, true);

-- Add paypal_order_id to payment_transactions for tracking
ALTER TABLE public.payment_transactions 
ADD COLUMN IF NOT EXISTS paypal_order_id TEXT,
ADD COLUMN IF NOT EXISTS paypal_subscription_id TEXT;