-- Migration 024: Cron Execution Logs
-- Tracks cron job execution history for monitoring

CREATE TABLE IF NOT EXISTS public.cron_execution_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  job_name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'running' CHECK (status IN ('running', 'completed', 'failed')),
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  duration_ms INTEGER,
  result JSONB,
  error_message TEXT,
  triggered_by TEXT DEFAULT 'cron'
);

-- Index for quick lookups
CREATE INDEX IF NOT EXISTS idx_cron_logs_job_name ON public.cron_execution_logs (job_name, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_cron_logs_started ON public.cron_execution_logs (started_at DESC);

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

-- Only admins can read cron logs
CREATE POLICY "Admins can read cron logs"
  ON public.cron_execution_logs
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.members
      WHERE members.user_id = auth.uid()
      AND members.role = 'admin'
    )
  );

-- Service role can insert/update (for cron endpoints)
CREATE POLICY "Service role can manage cron logs"
  ON public.cron_execution_logs
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

-- Auto-cleanup: keep only 90 days of logs
COMMENT ON TABLE public.cron_execution_logs IS 'Tracks cron job execution history. Entries older than 90 days should be periodically purged.';