-- Admin Audit Log migration
-- Run in Supabase SQL Editor

CREATE TABLE IF NOT EXISTS public.audit_log (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  admin_user_id integer NOT NULL,
  action varchar(80) NOT NULL,
  target_type varchar(40) NOT NULL,
  target_id varchar(64),
  target_name text,
  details jsonb,
  created_at timestamptz DEFAULT now(),
  CONSTRAINT fk_audit_log_admin FOREIGN KEY (admin_user_id) REFERENCES public.users(user_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_audit_log_admin_user_id ON public.audit_log(admin_user_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_action ON public.audit_log(action);
CREATE INDEX IF NOT EXISTS idx_audit_log_target ON public.audit_log(target_type, target_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_created_at ON public.audit_log(created_at DESC);

ALTER TABLE public.audit_log ENABLE ROW LEVEL SECURITY;

-- Only admins can insert (when they perform actions)
CREATE POLICY "Admins can insert audit log" ON public.audit_log
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.users
      WHERE users.user_id = audit_log.admin_user_id
      AND users.supabase_id::text = auth.uid()::text
      AND users.user_type = 'admin'
    )
  );

-- Only admins can read audit log
CREATE POLICY "Admins can select audit log" ON public.audit_log
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM public.users
      WHERE users.supabase_id::text = auth.uid()::text
      AND users.user_type = 'admin'
    )
  );

-- No update/delete on audit log (append-only)
COMMENT ON TABLE public.audit_log IS 'Admin action audit trail: who did what (delete user, update component, etc.)';
