-- 008_connected_pages.sql — Notion-like upgrades phase 3.
--
-- Nested pages, resolved internal page links (backlinks), and live collection
-- views embedded in a page. Links and embeds are derived from Markdown on
-- every content save: the page body stays the single source of truth.
--
-- Access rules:
--   * Pages remain owner-scoped. A link to a page the author does not own is
--     ignored, so backlinks can never reveal a title across accounts.
--   * An embed is stored only when the author owns the collection. The live
--     view still re-checks bookmark visibility through the collection item
--     endpoints (N-08); embedding never publishes anything.
--
-- Deleting:
--   * Deleting a parent page promotes its children (SET NULL), never deletes
--     them silently.
--   * Deleting a page cascades its outgoing/incoming links and embeds.
--   * Deleting a collection cascades its embeds; deleting a view clears the
--     embed's viewId so it falls back to the collection's default view.

ALTER TABLE "Page" ADD COLUMN IF NOT EXISTS "parentId" text;

-- The named constraint matches schema.sql so the fresh and upgraded paths
-- converge. The guard makes a re-run against an already-current schema a
-- no-op (without it, schema.sql's inline FK would collide).
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
     WHERE conname = 'Page_parentId_fkey' AND conrelid = '"Page"'::regclass
  ) THEN
    ALTER TABLE "Page" ADD CONSTRAINT "Page_parentId_fkey"
      FOREIGN KEY ("parentId") REFERENCES "Page"(id) ON DELETE SET NULL;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS "Page_parentId_idx" ON "Page"("parentId");

CREATE TABLE IF NOT EXISTS "PageLink" (
  "sourcePageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
  "targetPageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("sourcePageId", "targetPageId"),
  CONSTRAINT "PageLink_no_self" CHECK ("sourcePageId" <> "targetPageId")
);
CREATE INDEX IF NOT EXISTS "PageLink_targetPageId_idx" ON "PageLink"("targetPageId");

CREATE TABLE IF NOT EXISTS "PageEmbed" (
  "pageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
  "collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
  "viewId" text REFERENCES "CollectionView"(id) ON DELETE SET NULL,
  position integer NOT NULL DEFAULT 0,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("pageId", "collectionId")
);
CREATE INDEX IF NOT EXISTS "PageEmbed_collectionId_idx" ON "PageEmbed"("collectionId");
CREATE INDEX IF NOT EXISTS "PageEmbed_viewId_idx" ON "PageEmbed"("viewId");