-- M12 — Idea entity + Grill/Plan Claude jobs
-- See docs/plans/M12-ideas.md

-- 1. New enums
CREATE TYPE "IdeaStatus" AS ENUM ('DRAFT', 'GRILLING', 'GRILL_FAILED', 'GRILLED', 'PLANNING', 'PLAN_FAILED', 'PLAN_READY', 'PLANNED');
CREATE TYPE "ClaudeJobKind" AS ENUM ('TASK_IMPLEMENTATION', 'IDEA_GRILL', 'IDEA_MAKE_PLAN');
CREATE TYPE "IdeaLogType" AS ENUM ('DECISION', 'NOTE', 'GRILL_RESULT', 'PLAN_RESULT', 'STATUS_CHANGE', 'JOB_EVENT');

-- 2. User.idea_code_counter
ALTER TABLE "users" ADD COLUMN "idea_code_counter" INTEGER NOT NULL DEFAULT 0;

-- 3. ideas table
CREATE TABLE "ideas" (
    "id"          TEXT NOT NULL,
    "user_id"     TEXT NOT NULL,
    "product_id"  TEXT,
    "code"        VARCHAR(30) NOT NULL,
    "title"       TEXT NOT NULL,
    "description" VARCHAR(4000),
    "grill_md"    TEXT,
    "plan_md"     TEXT,
    "pbi_id"      TEXT,
    "status"      "IdeaStatus" NOT NULL DEFAULT 'DRAFT',
    "archived"    BOOLEAN NOT NULL DEFAULT false,
    "created_at"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at"  TIMESTAMP(3) NOT NULL,
    CONSTRAINT "ideas_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "ideas_pbi_id_key"                    ON "ideas"("pbi_id");
CREATE UNIQUE INDEX "ideas_user_id_code_key"              ON "ideas"("user_id", "code");
CREATE INDEX        "ideas_user_id_archived_status_idx"   ON "ideas"("user_id", "archived", "status");
CREATE INDEX        "ideas_user_id_product_id_idx"        ON "ideas"("user_id", "product_id");

ALTER TABLE "ideas" ADD CONSTRAINT "ideas_user_id_fkey"
  FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "ideas" ADD CONSTRAINT "ideas_product_id_fkey"
  FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "ideas" ADD CONSTRAINT "ideas_pbi_id_fkey"
  FOREIGN KEY ("pbi_id") REFERENCES "pbis"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- 4. idea_logs table
CREATE TABLE "idea_logs" (
    "id"         TEXT NOT NULL,
    "idea_id"    TEXT NOT NULL,
    "type"       "IdeaLogType" NOT NULL,
    "content"    TEXT NOT NULL,
    "metadata"   JSONB,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "idea_logs_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "idea_logs_idea_id_created_at_idx" ON "idea_logs"("idea_id", "created_at");

ALTER TABLE "idea_logs" ADD CONSTRAINT "idea_logs_idea_id_fkey"
  FOREIGN KEY ("idea_id") REFERENCES "ideas"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- 5. ClaudeJob: nullable task_id, new idea_id + kind
ALTER TABLE "claude_jobs" DROP CONSTRAINT "claude_jobs_task_id_fkey";
ALTER TABLE "claude_jobs" ALTER COLUMN "task_id" DROP NOT NULL;
ALTER TABLE "claude_jobs" ADD COLUMN "idea_id" TEXT;
ALTER TABLE "claude_jobs" ADD COLUMN "kind" "ClaudeJobKind" NOT NULL DEFAULT 'TASK_IMPLEMENTATION';
ALTER TABLE "claude_jobs" ADD CONSTRAINT "claude_jobs_task_id_fkey"
  FOREIGN KEY ("task_id") REFERENCES "tasks"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "claude_jobs" ADD CONSTRAINT "claude_jobs_idea_id_fkey"
  FOREIGN KEY ("idea_id") REFERENCES "ideas"("id") ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX "claude_jobs_idea_id_status_idx" ON "claude_jobs"("idea_id", "status");

-- Check-constraint: exactly one of task_id, idea_id
ALTER TABLE "claude_jobs" ADD CONSTRAINT "claude_jobs_one_of_task_or_idea"
  CHECK (("task_id" IS NOT NULL) <> ("idea_id" IS NOT NULL));

-- 6. ClaudeQuestion: nullable story_id, new idea_id
ALTER TABLE "claude_questions" DROP CONSTRAINT "claude_questions_story_id_fkey";
ALTER TABLE "claude_questions" ALTER COLUMN "story_id" DROP NOT NULL;
ALTER TABLE "claude_questions" ADD COLUMN "idea_id" TEXT;
ALTER TABLE "claude_questions" ADD CONSTRAINT "claude_questions_story_id_fkey"
  FOREIGN KEY ("story_id") REFERENCES "stories"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "claude_questions" ADD CONSTRAINT "claude_questions_idea_id_fkey"
  FOREIGN KEY ("idea_id") REFERENCES "ideas"("id") ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX "claude_questions_idea_id_status_idx" ON "claude_questions"("idea_id", "status");

-- Check-constraint: exactly one of story_id, idea_id
ALTER TABLE "claude_questions" ADD CONSTRAINT "claude_questions_one_of_story_or_idea"
  CHECK (("story_id" IS NOT NULL) <> ("idea_id" IS NOT NULL));

-- 7. pg_notify-trigger update: handle null story_id + emit idea_id
-- Replaces notify_question_change from 20260427224849_add_claude_questions.
-- New payload shape:
--   { op: 'I' | 'U',
--     entity: 'question',
--     id: text,
--     product_id: text,
--     story_id: text|null,
--     task_id: text|null,
--     idea_id: text|null,
--     assignee_id: text|null,   // story.assignee_id, null voor idea-questions (privé)
--     status: 'open'|'answered'|'cancelled'|'expired' }

CREATE OR REPLACE FUNCTION notify_question_change() RETURNS trigger AS $$
DECLARE
  story_assignee TEXT;
  payload jsonb;
BEGIN
  IF NEW.story_id IS NOT NULL THEN
    SELECT assignee_id INTO story_assignee FROM stories WHERE id = NEW.story_id;
  ELSE
    story_assignee := NULL;
  END IF;

  payload := jsonb_build_object(
    'op', CASE TG_OP
            WHEN 'INSERT' THEN 'I'
            WHEN 'UPDATE' THEN 'U'
          END,
    'entity', 'question',
    'id', NEW.id,
    'product_id', NEW.product_id,
    'story_id', NEW.story_id,
    'task_id', NEW.task_id,
    'idea_id', NEW.idea_id,
    'assignee_id', story_assignee,
    'status', NEW.status
  );

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