thrilltrack-explorer/supabase/migrations/20251104015548_c737afe7-657c-4169-a5f6-5782881625da.sql

-- Fix update_content_submissions_updated_at trigger to work with relational data model
-- Removes reference to non-existent NEW.content column
-- Uses CASCADE to handle dependent triggers on other tables

DROP TRIGGER IF EXISTS update_content_submissions_updated_at ON public.content_submissions;
DROP TRIGGER IF EXISTS update_timeline_event_submissions_updated_at ON public.timeline_event_submissions;
DROP FUNCTION IF EXISTS public.update_content_submissions_updated_at() CASCADE;

CREATE OR REPLACE FUNCTION public.update_content_submissions_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  -- Only update updated_at if actual content has changed
  -- Ignore changes to: updated_at, assigned_to, assigned_at, locked_until, priority, review_count, first_reviewed_at, resolved_at, submitted_at
  IF (
    NEW.status IS DISTINCT FROM OLD.status OR
    NEW.reviewer_id IS DISTINCT FROM OLD.reviewer_id OR
    NEW.reviewer_notes IS DISTINCT FROM OLD.reviewer_notes OR
    NEW.escalated IS DISTINCT FROM OLD.escalated OR
    NEW.escalation_reason IS DISTINCT FROM OLD.escalation_reason OR
    NEW.approval_mode IS DISTINCT FROM OLD.approval_mode OR
    NEW.user_id IS DISTINCT FROM OLD.user_id OR
    NEW.submission_type IS DISTINCT FROM OLD.submission_type OR
    NEW.escalated_by IS DISTINCT FROM OLD.escalated_by OR
    NEW.escalated_at IS DISTINCT FROM OLD.escalated_at OR
    NEW.original_submission_id IS DISTINCT FROM OLD.original_submission_id
  ) THEN
    NEW.updated_at = NOW();
  ELSE
    -- Keep the old updated_at timestamp if only metadata changed
    NEW.updated_at = OLD.updated_at;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Recreate trigger for content_submissions
CREATE TRIGGER update_content_submissions_updated_at
  BEFORE UPDATE ON public.content_submissions
  FOR EACH ROW
  EXECUTE FUNCTION public.update_content_submissions_updated_at();

-- Recreate trigger for timeline_event_submissions (if this table exists)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'timeline_event_submissions') THEN
    EXECUTE 'CREATE TRIGGER update_timeline_event_submissions_updated_at
      BEFORE UPDATE ON public.timeline_event_submissions
      FOR EACH ROW
      EXECUTE FUNCTION public.update_content_submissions_updated_at()';
  END IF;
END $$;

COMMENT ON FUNCTION public.update_content_submissions_updated_at() IS 
  'Trigger function to update updated_at timestamp only when meaningful fields change, not just metadata like lock status or priority. Fixed to work with relational data model without JSONB content column.';