thrilltrack-explorer/supabase/migrations/20251006143537_97282625-f6ca-4414-93ff-8d024f374214.sql

-- Function to release submission lock
CREATE OR REPLACE FUNCTION release_submission_lock(
  submission_id UUID,
  moderator_id UUID
) RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  UPDATE content_submissions
  SET 
    assigned_to = NULL,
    assigned_at = NULL,
    locked_until = NULL
  WHERE id = submission_id
    AND assigned_to = moderator_id;
  
  RETURN FOUND;
END;
$$;

-- Function to extend submission lock
CREATE OR REPLACE FUNCTION extend_submission_lock(
  submission_id UUID,
  moderator_id UUID,
  extension_duration INTERVAL DEFAULT '15 minutes'
) RETURNS TIMESTAMP WITH TIME ZONE
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  new_lock_time TIMESTAMP WITH TIME ZONE;
BEGIN
  UPDATE content_submissions
  SET locked_until = NOW() + extension_duration
  WHERE id = submission_id
    AND assigned_to = moderator_id
  RETURNING locked_until INTO new_lock_time;
  
  RETURN new_lock_time;
END;
$$;

-- Function to auto-release expired locks
CREATE OR REPLACE FUNCTION release_expired_locks()
RETURNS INTEGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  released_count INTEGER;
BEGIN
  UPDATE content_submissions
  SET 
    assigned_to = NULL,
    assigned_at = NULL,
    locked_until = NULL,
    review_count = review_count + 1
  WHERE locked_until < NOW()
    AND assigned_to IS NOT NULL
    AND status IN ('pending', 'partially_approved');
  
  GET DIAGNOSTICS released_count = ROW_COUNT;
  RETURN released_count;
END;
$$;

-- View for SLA monitoring and queue metrics
CREATE OR REPLACE VIEW moderation_sla_metrics AS
SELECT
  submission_type,
  COUNT(*) FILTER (WHERE status IN ('pending', 'partially_approved')) as pending_count,
  AVG(EXTRACT(EPOCH FROM (NOW() - submitted_at))/3600) FILTER (WHERE status IN ('pending', 'partially_approved')) as avg_wait_hours,
  MAX(EXTRACT(EPOCH FROM (NOW() - submitted_at))/3600) FILTER (WHERE status IN ('pending', 'partially_approved')) as max_wait_hours,
  AVG(EXTRACT(EPOCH FROM (resolved_at - submitted_at))/3600) FILTER (WHERE status IN ('approved', 'rejected')) as avg_resolution_hours,
  COUNT(*) FILTER (WHERE escalated = true AND status IN ('pending', 'partially_approved')) as escalated_count,
  COUNT(*) FILTER (WHERE review_count > 1) as reassigned_count,
  COUNT(*) FILTER (WHERE assigned_to IS NOT NULL AND locked_until > NOW()) as currently_locked
FROM content_submissions
GROUP BY submission_type;

-- Grant necessary permissions
GRANT SELECT ON moderation_sla_metrics TO authenticated;