-- Audit log table
CREATE TABLE public.audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  action text NOT NULL,
  entity text NOT NULL,
  entity_id uuid,
  delta integer,
  from_state text,
  to_state text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT ON public.audit_log TO authenticated;
GRANT ALL ON public.audit_log TO service_role;

ALTER TABLE public.audit_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users read own audit log"
  ON public.audit_log FOR SELECT
  TO authenticated
  USING (auth.uid() = user_id);

CREATE INDEX audit_log_user_created_idx ON public.audit_log (user_id, created_at DESC);

-- Tighten campaign state transitions
CREATE OR REPLACE FUNCTION public.protect_campaign_state()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  allowed boolean := false;
BEGIN
  IF auth.uid() IS NOT NULL THEN
    -- Client caller: force safe defaults, ignore stat writes
    IF TG_OP = 'INSERT' THEN
      NEW.status := 'draft';
      NEW.delivered_count := 0;
      NEW.failed_count := 0;
      NEW.recipients_count := 0;
      NEW.sent_at := NULL;
    ELSIF TG_OP = 'UPDATE' THEN
      NEW.status := OLD.status;
      NEW.delivered_count := OLD.delivered_count;
      NEW.failed_count := OLD.failed_count;
      NEW.recipients_count := OLD.recipients_count;
      NEW.sent_at := OLD.sent_at;
    END IF;
    RETURN NEW;
  END IF;

  -- Server (service_role) caller: enforce allowed status transitions
  IF TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status THEN
    allowed := (OLD.status, NEW.status::text) IN (
      ('draft','queued'),
      ('draft','sending'),
      ('draft','sent'),
      ('queued','sending'),
      ('queued','failed'),
      ('sending','sent'),
      ('sending','failed')
    );
    IF NOT allowed THEN
      RAISE EXCEPTION 'Invalid campaign status transition: % -> %', OLD.status, NEW.status;
    END IF;
  END IF;

  IF NEW.delivered_count < 0 OR NEW.failed_count < 0 OR NEW.recipients_count < 0 THEN
    RAISE EXCEPTION 'Campaign counts must be non-negative';
  END IF;
  IF (NEW.delivered_count + NEW.failed_count) > NEW.recipients_count THEN
    RAISE EXCEPTION 'delivered + failed cannot exceed recipients_count';
  END IF;

  RETURN NEW;
END;
$$;

REVOKE EXECUTE ON FUNCTION public.protect_campaign_state() FROM PUBLIC, anon, authenticated;