-- computer database schema
-- Derived from computer_porting_guide.md section 2 and prisma/schema.prisma.
-- IDs are text (CUID/UUID). CASCADE / SetNull per the guide.

-- Run against an EMPTY database. Never drops or recreates a database.
BEGIN;

-- Trigram indexes back the page search substring predicate (review PERF-2).
-- pg_trgm is a trusted extension, so the database owner can enable it.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 2.1 legacy enum
CREATE TYPE "KanbanStatus" AS ENUM ('INBOX','TODO','IN_PROGRESS','DONE','ARCHIVED');

-- 2.2 User
CREATE TABLE "User" (
  id text PRIMARY KEY,
  name text,
  email text UNIQUE,
  "emailVerified" timestamptz,
  image text,
  password text,
  role text NOT NULL DEFAULT 'USER' CHECK (role IN ('USER','ADMIN')),
  "tokenVersion" integer NOT NULL DEFAULT 1,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);

-- 2.3 Account, Session, VerificationToken
CREATE TABLE "Account" (
  id text PRIMARY KEY,
  "userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  type text NOT NULL,
  provider text NOT NULL,
  "providerAccountId" text NOT NULL,
  "refresh_token" text,
  "access_token" text,
  "expires_at" int,
  "token_type" text,
  scope text,
  "id_token" text,
  "session_state" text,
  UNIQUE(provider, "providerAccountId")
);
CREATE INDEX "Account_userId_idx" ON "Account"("userId");

CREATE TABLE "Session" (
  id text PRIMARY KEY,
  "sessionToken" text NOT NULL UNIQUE,
  "userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  expires timestamptz NOT NULL
);
CREATE INDEX "Session_userId_idx" ON "Session"("userId");

CREATE TABLE "VerificationToken" (
  identifier text NOT NULL,
  token text NOT NULL UNIQUE,
  expires timestamptz NOT NULL,
  UNIQUE(identifier, token)
);

-- 2.4 Bookmark
CREATE TABLE "Bookmark" (
  id text PRIMARY KEY,
  url text NOT NULL,
  title text,
  description text,
  favicon text,
  "ogImage" text,
  "ogTitle" text,
  "ogDescription" text,
  notes text,
  "dueDate" timestamptz,
  "alertAt" timestamptz,
  "alertSent" boolean NOT NULL DEFAULT false,
  "kanbanStatus" "KanbanStatus" NOT NULL DEFAULT 'INBOX',
  "kanbanColumnId" text,
  -- Board ordering within a column. Server-normalized on every card move;
  -- legacy rows default to 0 and fall back to updatedAt-desc ordering.
  "kanbanPosition" integer NOT NULL DEFAULT 0,
  "imapWatchEnabled" boolean NOT NULL DEFAULT false,
  "imapQuery" text,
  "isPublic" boolean NOT NULL DEFAULT false,
  "ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "contactId" text,
  -- Optimistic concurrency revision (review CR-04): every owner-driven PUT
  -- (fields and tag replacement) requires the expected version and bumps it;
  -- the column-deletion move bumps it too. Sharing and server-driven flags
  -- (alertSent) are outside the contract — see API.md.
  version integer NOT NULL DEFAULT 0,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX "Bookmark_ownerId_idx" ON "Bookmark"("ownerId");
CREATE INDEX "Bookmark_ownerId_updatedAt_id_idx" ON "Bookmark"("ownerId","updatedAt" DESC,id DESC);
CREATE INDEX "Bookmark_contactId_idx" ON "Bookmark"("contactId");
CREATE INDEX "Bookmark_kanbanStatus_idx" ON "Bookmark"("kanbanStatus");
CREATE INDEX "Bookmark_kanbanColumnId_idx" ON "Bookmark"("kanbanColumnId");
CREATE INDEX "Bookmark_ownerId_kanbanColumnId_kanbanPosition_idx" ON "Bookmark"("ownerId","kanbanColumnId","kanbanPosition",id);
CREATE INDEX "Bookmark_dueDate_idx" ON "Bookmark"("dueDate");
CREATE INDEX "Bookmark_alertAt_idx" ON "Bookmark"("alertAt");

-- 2.5 Tag
CREATE TABLE "Tag" (
  id text PRIMARY KEY,
  name text NOT NULL,
  color text NOT NULL DEFAULT '#6366f1',
  "ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE(name, "ownerId")
);
CREATE INDEX "Tag_ownerId_idx" ON "Tag"("ownerId");

-- 2.6 BookmarkTag
CREATE TABLE "BookmarkTag" (
  "bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
  "tagId" text NOT NULL REFERENCES "Tag"(id) ON DELETE CASCADE,
  PRIMARY KEY ("bookmarkId", "tagId")
);
CREATE INDEX "BookmarkTag_tagId_idx" ON "BookmarkTag"("tagId");

-- 2.7 BookmarkShare
CREATE TABLE "BookmarkShare" (
  "bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
  "userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("bookmarkId", "userId")
);
CREATE INDEX "BookmarkShare_userId_idx" ON "BookmarkShare"("userId");

-- 2.8 TagShare
CREATE TABLE "TagShare" (
  "tagId" text NOT NULL REFERENCES "Tag"(id) ON DELETE CASCADE,
  "userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("tagId", "userId")
);
CREATE INDEX "TagShare_userId_idx" ON "TagShare"("userId");

-- 2.9 Contact
CREATE TABLE "Contact" (
  id text PRIMARY KEY,
  uid text NOT NULL,
  "displayName" text NOT NULL,
  email text,
  phone text,
  "cardDavUrl" text,
  -- CardDAV provenance (review CR-07): the address-book URL this contact was
  -- imported from. Deletion reconciliation is scoped to it, so manually
  -- created contacts (NULL) and other address books' imports are never
  -- touched by a sync.
  source text,
  "userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE(uid, "userId")
);
CREATE INDEX "Contact_userId_idx" ON "Contact"("userId");
ALTER TABLE "Bookmark" ADD CONSTRAINT "Bookmark_contactId_fkey"
  FOREIGN KEY ("contactId") REFERENCES "Contact"(id) ON DELETE SET NULL;

-- 2.10 ImapConfig
CREATE TABLE "ImapConfig" (
  id text PRIMARY KEY,
  host text NOT NULL,
  port int NOT NULL DEFAULT 993,
  tls boolean NOT NULL DEFAULT true,
  username text NOT NULL,
  password text NOT NULL,
  folder text NOT NULL DEFAULT 'INBOX',
  "userId" text UNIQUE NOT NULL REFERENCES "User"(id) ON DELETE CASCADE
);

-- 2.11 KanbanColumn
CREATE TABLE "KanbanColumn" (
  id text PRIMARY KEY,
  label text NOT NULL,
  color text NOT NULL DEFAULT '#64748b',
  position int NOT NULL DEFAULT 0,
  "key" text,
  "userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX "KanbanColumn_userId_position_idx" ON "KanbanColumn"("userId", position);
-- Legacy status keys are unique per user (review L-12): ResolveColumnID picks
-- the first match, so duplicates would make the legacy fallback ambiguous.
CREATE UNIQUE INDEX "KanbanColumn_userId_key_idx" ON "KanbanColumn"("userId", "key") WHERE "key" IS NOT NULL;

-- Bookmark.kanbanColumnId -> KanbanColumn (SetNull)
ALTER TABLE "Bookmark" ADD CONSTRAINT "Bookmark_kanbanColumnId_fkey"
  FOREIGN KEY ("kanbanColumnId") REFERENCES "KanbanColumn"(id) ON DELETE SET NULL;

-- 2.12 Page
CREATE TABLE "Page" (
  id text PRIMARY KEY,
  title text NOT NULL,
  content text NOT NULL DEFAULT '',
  icon text,
  pinned boolean NOT NULL DEFAULT false,
  position int NOT NULL DEFAULT 0,
  -- Connected pages (phase 3): a page may live under another page. Deleting
  -- the parent promotes its children to the top level instead of deleting
  -- them; cycle rejection is enforced by the API.
  "parentId" text REFERENCES "Page"(id) ON DELETE SET NULL,
  -- Optimistic concurrency revision (review DATA-01): content saves bump it;
  -- a stale base version in PATCH is rejected with 409.
  version integer NOT NULL DEFAULT 1,
  "ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX "Page_ownerId_position_idx" ON "Page"("ownerId", position);
CREATE INDEX "Page_parentId_idx" ON "Page"("parentId");
-- Index-backed substring search over the two searchable fields.
CREATE INDEX "Page_title_trgm_idx" ON "Page" USING gin (title gin_trgm_ops);
CREATE INDEX "Page_content_trgm_idx" ON "Page" USING gin (content gin_trgm_ops);

-- 2.13 PageBookmark
CREATE TABLE "PageBookmark" (
  "pageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
  "bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
  position int NOT NULL DEFAULT 0,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("pageId", "bookmarkId")
);
CREATE INDEX "PageBookmark_bookmarkId_idx" ON "PageBookmark"("bookmarkId");

-- 2.13b PageLink: resolved internal page links, rebuilt from page content on
-- every content save. Targets are only stored when the author owns the target
-- page, so a backlink can never reveal an inaccessible title. Self-links are
-- ignored by the writer and refused here as defense in depth.
CREATE TABLE "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 "PageLink_targetPageId_idx" ON "PageLink"("targetPageId");

-- 2.13c PageRevision: server-side content history. A snapshot is recorded for
-- every committed content version (including restore, which is a new version)
-- so an old draft can be inspected and restored without ever rewriting the
-- past. Revision rows cascade with their page.
CREATE TABLE "PageRevision" (
  id text PRIMARY KEY,
  "pageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
  version integer NOT NULL,
  title text NOT NULL,
  content text NOT NULL,
  icon text,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE ("pageId", version)
);
CREATE INDEX "PageRevision_pageId_version_idx" ON "PageRevision"("pageId", version DESC);

-- 2.14 SignalSource
CREATE TABLE "SignalSource" (
  id text PRIMARY KEY,
  type text NOT NULL,
  config jsonb NOT NULL DEFAULT '{}',
  enabled boolean NOT NULL DEFAULT true,
  "lastCheckedAt" timestamptz,
  "lastStatus" text,
  "bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
  "ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX "SignalSource_bookmarkId_idx" ON "SignalSource"("bookmarkId");
CREATE INDEX "SignalSource_ownerId_idx" ON "SignalSource"("ownerId");

-- 2.14b SignalCheckpoint (review SYNC-02): durable per-source mailbox
-- ingestion state. A poll processes only UIDs newer than lastUid while
-- uidValidity matches; the checkpoint advances only after the batch's
-- signals are durably inserted. A uidValidity change resets ingestion.
CREATE TABLE "SignalCheckpoint" (
  "sourceId" text PRIMARY KEY REFERENCES "SignalSource"(id) ON DELETE CASCADE,
  "uidValidity" bigint NOT NULL,
  "lastUid" bigint NOT NULL,
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);

-- 2.15 Signal
CREATE TABLE "Signal" (
  id text PRIMARY KEY,
  "sourceType" text NOT NULL,
  title text NOT NULL,
  summary text,
  url text,
  "externalId" text,
  "occurredAt" timestamptz NOT NULL DEFAULT now(),
  read boolean NOT NULL DEFAULT false,
  "sourceId" text,
  "bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
  "ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE("sourceId", "externalId")
);
CREATE INDEX "Signal_bookmarkId_occurredAt_idx" ON "Signal"("bookmarkId", "occurredAt");
CREATE INDEX "Signal_ownerId_occurredAt_idx" ON "Signal"("ownerId", "occurredAt");
ALTER TABLE "Signal" ADD CONSTRAINT "Signal_sourceId_fkey"
  FOREIGN KEY ("sourceId") REFERENCES "SignalSource"(id) ON DELETE SET NULL;

-- 2.16 AppSetting: server-owned singleton settings rows. 'adminBootstrap'
-- records that initial provisioning completed (review AUTH-06), so the
-- absence of a qualifying administrator never re-opens first-signup
-- promotion after a demotion or removal.
CREATE TABLE "AppSetting" (
  key text PRIMARY KEY,
  value jsonb NOT NULL DEFAULT '{}',
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);

-- 2.16b Trash: recoverable deletions (trust phase). The row is a private
-- per-owner snapshot taken in the same transaction as the hard delete. It
-- keeps intrinsic data and owner-scoped relationships, but deliberately not
-- access grants (bookmark shares, public flags, collection shares): restoring
-- an item never resurrects a grant that was revoked while it sat in the bin.
CREATE TABLE "Trash" (
  id text PRIMARY KEY,
  "ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  type text NOT NULL CHECK (type IN ('bookmark','page','collection')),
  "originalId" text NOT NULL,
  data jsonb NOT NULL,
  "deletedAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE (type, "originalId")
);
CREATE INDEX "Trash_ownerId_deletedAt_idx" ON "Trash"("ownerId", "deletedAt" DESC);

-- 2.16 SharedDate
CREATE TABLE "SharedDate" (
  id text PRIMARY KEY,
  date date NOT NULL,
  note text,
  "creatorId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "recipientId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX "SharedDate_recipientId_idx" ON "SharedDate"("recipientId");
CREATE INDEX "SharedDate_creatorId_idx" ON "SharedDate"("creatorId");
CREATE INDEX "SharedDate_date_idx" ON "SharedDate"(date);
-- One row per creator/recipient/date (review L-3): re-sharing the same date
-- updates the note instead of duplicating calendar rows and notifications.
CREATE UNIQUE INDEX "SharedDate_creatorId_recipientId_date_idx" ON "SharedDate"("creatorId","recipientId",date);
CREATE INDEX "Bookmark_ownerId_alertAt_unsent_idx" ON "Bookmark"("ownerId","alertAt") WHERE NOT "alertSent";

-- 2.17 Collections (Notion-like upgrades phase 2; see 002_collections.sql for
-- the design decisions). Contextual workflow state: the same bookmark can
-- live in many collections with independent property values; membership
-- never broadens access and never owns the bookmark.
CREATE TABLE "Collection" (
  id text PRIMARY KEY,
  name text NOT NULL,
  description text,
  icon text,
  "ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  version integer NOT NULL DEFAULT 1,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX "Collection_ownerId_idx" ON "Collection"("ownerId");

-- 2.17b CollectionShare: explicit, revocable read access to a collection.
-- Recipients get the narrowed bookmark projection for member bookmarks (via
-- BookmarkVisible); sharing therefore widens access only by the owner's
-- deliberate action, is disclosed in the share UI, and revocation removes
-- access immediately. Mutations remain owner-only.
CREATE TABLE "CollectionShare" (
  "collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
  "userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY ("collectionId", "userId")
);
CREATE INDEX "CollectionShare_userId_idx" ON "CollectionShare"("userId");

CREATE TABLE "CollectionProperty" (
  id text PRIMARY KEY,
  "collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
  name text NOT NULL,
  type text NOT NULL CHECK (type IN ('TEXT','NUMBER','SELECT','MULTI_SELECT','DATE','CHECKBOX','URL','RELATION')),
  position integer NOT NULL DEFAULT 0,
  config jsonb NOT NULL DEFAULT '{}',
  version integer NOT NULL DEFAULT 1,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE ("collectionId", name)
);

CREATE TABLE "CollectionItem" (
  id text PRIMARY KEY,
  "collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
  "bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
  "propertyValues" jsonb NOT NULL DEFAULT '{}',
  position integer NOT NULL DEFAULT 0,
  version integer NOT NULL DEFAULT 1,
  "addedAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now(),
  UNIQUE ("collectionId", "bookmarkId")
);
CREATE INDEX "CollectionItem_bookmarkId_idx" ON "CollectionItem"("bookmarkId");

CREATE TABLE "CollectionView" (
  id text PRIMARY KEY,
  "collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
  name text NOT NULL,
  type text NOT NULL DEFAULT 'TABLE' CHECK (type IN ('TABLE','BOARD','CALENDAR','LIST')),
  position integer NOT NULL DEFAULT 0,
  filters jsonb NOT NULL DEFAULT '{"operator":"AND","clauses":[]}',
  sorts jsonb NOT NULL DEFAULT '[]',
  "groupByPropertyId" text REFERENCES "CollectionProperty"(id) ON DELETE SET NULL,
  "displaySettings" jsonb NOT NULL DEFAULT '{}',
  version integer NOT NULL DEFAULT 1,
  "createdAt" timestamptz NOT NULL DEFAULT now(),
  "updatedAt" timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX "CollectionView_collectionId_idx" ON "CollectionView"("collectionId");

-- 2.18 PageEmbed (Notion-like upgrades phase 3): a live collection view
-- embedded in a page. The reference is resolved from the page's Markdown
-- (`{{collection:<id>|view=<id>}}`) on every content save. Only collections
-- owned by the page's author are stored; embedding displays data and never
-- publishes it or widens bookmark access. Deleting the collection or the
-- chosen view removes/falls back rather than leaving a dangling embed.
CREATE TABLE "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 "PageEmbed_collectionId_idx" ON "PageEmbed"("collectionId");
CREATE INDEX "PageEmbed_viewId_idx" ON "PageEmbed"("viewId");
COMMIT;