-- ST-801: Postgres LISTEN/NOTIFY infrastructure for realtime Solo Paneel.
--
-- Adds two row-level AFTER triggers that emit a JSON payload on the
-- `scrum4me_changes` channel for every INSERT/UPDATE/DELETE on tasks and
-- stories. The SSE handler at /api/realtime/solo subscribes to that channel
-- and fans out per-user.
--
-- Payload shape:
--   { op: 'I'|'U'|'D',
--     entity: 'task'|'story',
--     id: text,
--     story_id?: text,        // task only
--     product_id: text,
--     sprint_id: text|null,
--     assignee_id: text|null,
--     changed_fields?: text[] // UPDATE only
--   }
--
-- Channel name is hardcoded to keep the contract simple. Update both this
-- migration and the SSE handler if it ever changes.

CREATE OR REPLACE FUNCTION notify_task_change() RETURNS trigger AS $$
DECLARE
  rec record;
  story_row record;
  payload jsonb;
BEGIN
  IF TG_OP = 'DELETE' THEN
    rec := OLD;
  ELSE
    rec := NEW;
  END IF;

  SELECT product_id, sprint_id, assignee_id
    INTO story_row
    FROM stories
    WHERE id = rec.story_id;

  IF NOT FOUND THEN
    -- Parent story already gone (cascading delete); skip the notify.
    RETURN rec;
  END IF;

  payload := jsonb_build_object(
    'op', CASE TG_OP
            WHEN 'INSERT' THEN 'I'
            WHEN 'UPDATE' THEN 'U'
            WHEN 'DELETE' THEN 'D'
          END,
    'entity', 'task',
    'id', rec.id,
    'story_id', rec.story_id,
    'product_id', story_row.product_id,
    'sprint_id', story_row.sprint_id,
    'assignee_id', story_row.assignee_id
  );

  IF TG_OP = 'UPDATE' THEN
    payload := payload || jsonb_build_object(
      'changed_fields',
      COALESCE((
        SELECT jsonb_agg(n.key)
        FROM jsonb_each(to_jsonb(NEW)) n
        JOIN jsonb_each(to_jsonb(OLD)) o USING (key)
        WHERE n.value IS DISTINCT FROM o.value
      ), '[]'::jsonb)
    );
  END IF;

  PERFORM pg_notify('scrum4me_changes', payload::text);
  RETURN rec;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION notify_story_change() RETURNS trigger AS $$
DECLARE
  rec record;
  payload jsonb;
BEGIN
  IF TG_OP = 'DELETE' THEN
    rec := OLD;
  ELSE
    rec := NEW;
  END IF;

  payload := jsonb_build_object(
    'op', CASE TG_OP
            WHEN 'INSERT' THEN 'I'
            WHEN 'UPDATE' THEN 'U'
            WHEN 'DELETE' THEN 'D'
          END,
    'entity', 'story',
    'id', rec.id,
    'product_id', rec.product_id,
    'sprint_id', rec.sprint_id,
    'assignee_id', rec.assignee_id
  );

  IF TG_OP = 'UPDATE' THEN
    payload := payload || jsonb_build_object(
      'changed_fields',
      COALESCE((
        SELECT jsonb_agg(n.key)
        FROM jsonb_each(to_jsonb(NEW)) n
        JOIN jsonb_each(to_jsonb(OLD)) o USING (key)
        WHERE n.value IS DISTINCT FROM o.value
      ), '[]'::jsonb)
    );
  END IF;

  PERFORM pg_notify('scrum4me_changes', payload::text);
  RETURN rec;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tasks_notify_change ON tasks;
CREATE TRIGGER tasks_notify_change
  AFTER INSERT OR UPDATE OR DELETE ON tasks
  FOR EACH ROW EXECUTE FUNCTION notify_task_change();

DROP TRIGGER IF EXISTS stories_notify_change ON stories;
CREATE TRIGGER stories_notify_change
  AFTER INSERT OR UPDATE OR DELETE ON stories
  FOR EACH ROW EXECUTE FUNCTION notify_story_change();
