-- Frozen legacy baseline (0001). Do not edit after release.
-- New DDL belongs in the next numbered SQL migration; see docs/database/schema-migrations.md.
-- config/schema.sql is a compatibility alias for older fixtures and operator scripts.
--
-- This baseline retains the existing additive retrofits: a CREATE TABLE IF
-- NOT EXISTS does not add columns to an existing instance. Later upgrades
-- belong in new numbered files and must be tested on a populated old shape.

-- Create pods table
CREATE TABLE IF NOT EXISTS pods (
  id VARCHAR(24) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  type VARCHAR(50) NOT NULL,
  created_by VARCHAR(24) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create pod_members table for many-to-many relationship
CREATE TABLE IF NOT EXISTS pod_members (
  id SERIAL PRIMARY KEY,
  pod_id VARCHAR(24) REFERENCES pods(id) ON DELETE CASCADE,
  user_id VARCHAR(24) NOT NULL,
  joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(pod_id, user_id)
);

-- Create messages table
CREATE TABLE IF NOT EXISTS messages (
  id SERIAL PRIMARY KEY,
  pod_id VARCHAR(24) REFERENCES pods(id) ON DELETE CASCADE,
  user_id VARCHAR(24) NOT NULL,
  content TEXT NOT NULL,
  message_type VARCHAR(20) DEFAULT 'text' NOT NULL,
  -- Kernel-authored seat posts (install intros and stalled-connect nudges)
  -- speak beside an agent without pretending to be that agent. NULL keeps
  -- legacy human and agent rows unclassified until a trusted server path opts
  -- into the marker.
  author_kind VARCHAR(20),
  reply_to_message_id INTEGER REFERENCES messages(id) ON DELETE SET NULL,
  -- Threading (W-T, TASK-029). DELIBERATELY separate from reply_to_message_id,
  -- which is an ADDRESSING edge: it feeds `isRouted` and the implicit-reply
  -- chat.mention (agentMentionService :1025 / :1356), so a human replying to an
  -- agent addresses that agent. If thread membership rode the same column,
  -- joining a thread would ping the parent's author — the opposite of what
  -- ambient scoping is for (fable's #1045 ruling). thread_root_id carries no
  -- addressing. NULL means "not in a thread"; a root is an ordinary message
  -- other messages point at, and a root's own thread_root_id stays NULL.
  thread_root_id INTEGER REFERENCES messages(id) ON DELETE SET NULL,
  -- Per-pod commit-order fence for agent send freshness. Existing rows stay 0.
  freshness_revision BIGINT NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create users table for storing MongoDB user references
CREATE TABLE IF NOT EXISTS users (
  _id VARCHAR(24) PRIMARY KEY,
  username VARCHAR(100) NOT NULL,
  profile_picture TEXT,
  is_bot BOOLEAN DEFAULT false,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Migration: add is_bot column to existing tables (safe to run repeatedly)
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_bot BOOLEAN DEFAULT false;

-- ADR-020 D3: structured message payload (approval cards and future
-- server-defined message components). Self-applies at boot, same pattern
-- as is_bot above. NULL for ordinary messages.
ALTER TABLE messages ADD COLUMN IF NOT EXISTS payload JSONB;

-- FUNNEL-5: the CREATE TABLE declaration above reaches fresh databases; this
-- retrofit reaches every instance whose messages table already exists.
ALTER TABLE messages ADD COLUMN IF NOT EXISTS author_kind VARCHAR(20);

-- Threading (W-T, TASK-029). MUST be retrofitted, not only declared in the
-- CREATE TABLE above — `CREATE TABLE IF NOT EXISTS` is a no-op on an existing
-- table, so a column declared only there NEVER reaches an instance that
-- already has a messages table. Verified on the live dev instance 2026-08-22:
-- 7,001 rows, oldest 2026-07-07, and thread_root_id absent while `payload`
-- (which has this retrofit) is present.
--
-- Without this line the failure is not subtle: the index below throws at boot
-- and every PGMessage.create fails, because the INSERT names a column that
-- does not exist. Chat posting stops on every existing instance.
--
-- The tier-1 test suite could not catch it — CI provisions a FRESH postgres:16
-- per run, where the CREATE TABLE path is the one exercised. Any new column on
-- an existing table needs BOTH declarations, and a test that starts from the
-- old shape (see threadingSchemaRetrofit.test.js). Caught by @sprint-review's
-- caveat on #1106, not by anything I ran.
ALTER TABLE messages
  ADD COLUMN IF NOT EXISTS thread_root_id INTEGER REFERENCES messages(id) ON DELETE SET NULL;

ALTER TABLE messages ADD COLUMN IF NOT EXISTS freshness_revision BIGINT NOT NULL DEFAULT 0;

-- TASK-313 part 2. The Mongo id of a fallback message this row was reconciled
-- from. Nullable: ordinary rows carry NULL, and several NULLs never collide.
-- The UNIQUE index is NOT declared here: CONCURRENTLY cannot run inside this
-- multi-statement script, which executes as one transaction. The reconciler
-- builds it (services/pgMessageReconcileService.ensureLegacyIdIndex) before
-- its first insert, so it never blocks writes on messages.
ALTER TABLE messages ADD COLUMN IF NOT EXISTS legacy_mongo_id TEXT;

CREATE TABLE IF NOT EXISTS pod_message_revisions (
  pod_id VARCHAR(24) PRIMARY KEY REFERENCES pods(id) ON DELETE CASCADE,
  revision BIGINT NOT NULL DEFAULT 0
);

-- The counter update and message insert commit together. Concurrent inserts
-- into one pod serialize on this row, so revision order is commit order even
-- when an INSERT began before a reader and committed after it.
CREATE OR REPLACE FUNCTION assign_message_freshness_revision()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO pod_message_revisions (pod_id, revision)
  VALUES (NEW.pod_id, 1)
  ON CONFLICT (pod_id) DO UPDATE
    SET revision = pod_message_revisions.revision + 1
  RETURNING revision INTO NEW.freshness_revision;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS messages_freshness_revision ON messages;
CREATE TRIGGER messages_freshness_revision
  BEFORE INSERT ON messages
  FOR EACH ROW EXECUTE FUNCTION assign_message_freshness_revision();

CREATE INDEX IF NOT EXISTS idx_messages_pod_freshness_revision
  ON messages(pod_id, freshness_revision);

-- Migration records. There was no such table anywhere under backend/ before
-- this (@sprint-review, 56859, checked with a positive control): schema.sql is
-- idempotent boot DDL, which says what the shape IS and never when it changed.
-- That is fine until a RULE needs to reference the change — the threading
-- surface ruling (#1115) says pre-cutoff thread roots default to expanded,
-- "cutoff = the migration timestamp, read from the migration record", and that
-- record did not exist.
--
-- Deliberately general rather than a threading-specific row. A one-off
-- `threading_migration` table is the thing that has to be generalised the
-- second time someone needs this, and the second time is when it gets done
-- wrong. `details` is JSONB so a migration can record what only IT knew — for
-- the threading backfill, the boundary timestamp it would otherwise discard.
--
-- NOT a migration RUNNER. These are application/backfill records, not the
-- runner's schema_migrations table. A missing backfill row does not prove
-- that work never ran on an instance predating this application ledger.
CREATE TABLE IF NOT EXISTS migration_records (
  name VARCHAR(255) PRIMARY KEY,
  applied_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  details JSONB
);

-- Retention execution ledger. This is deliberately NOT `migration_records`:
-- migration records describe one-off changes, while this records every run of
-- a destructive recurring job. A backend restart discards pod logs, so the
-- deletion count and the reasons no deletion occurred must live with the data.
-- A `running` row left behind is evidence of an interrupted run, not success.
CREATE TABLE IF NOT EXISTS pg_retention_runs (
  id BIGSERIAL PRIMARY KEY,
  status VARCHAR(16) NOT NULL DEFAULT 'running'
    CHECK (status IN ('running', 'completed', 'aborted', 'failed', 'skipped')),
  configured_retention_days INTEGER,
  final_retention_days INTEGER,
  protected_pod_count INTEGER,
  deleted_message_count BIGINT NOT NULL DEFAULT 0,
  re_rooted_count BIGINT,
  target_bytes BIGINT,
  initial_size_bytes BIGINT,
  final_size_bytes BIGINT,
  detail TEXT,
  started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at TIMESTAMP WITH TIME ZONE
);
-- `CREATE TABLE IF NOT EXISTS` does not add fields to an already-created
-- ledger, so keep this additive form for instances that booted an earlier DDL.
ALTER TABLE pg_retention_runs ADD COLUMN IF NOT EXISTS re_rooted_count BIGINT;
CREATE INDEX IF NOT EXISTS idx_pg_retention_runs_started_at ON pg_retention_runs(started_at DESC);

-- Sprint B5: message reactions. One row per (message, user, emoji) — a user
-- can stack different emojis on the same message but each emoji is binary
-- (toggle on/off). PG-only; Mongo fallback path doesn't get reactions in v1.
CREATE TABLE IF NOT EXISTS message_reactions (
  id SERIAL PRIMARY KEY,
  message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
  user_id VARCHAR(24) NOT NULL,
  emoji VARCHAR(32) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (message_id, user_id, emoji)
);
CREATE INDEX IF NOT EXISTS idx_message_reactions_message_id ON message_reactions(message_id);

-- Room-grant broker audit and budget ledger. The budget counter is separate
-- from Mongo's grant document so every reservation is a single Postgres
-- conditional write; concurrent broker calls cannot read the same remaining
-- slot and both spend it. `window_started_at` is retained for windowed caps;
-- a NULL window on the grant is represented by a lifetime counter here.
CREATE TABLE IF NOT EXISTS tool_call_budgets (
  grant_id VARCHAR(255) PRIMARY KEY,
  calls_used INTEGER NOT NULL DEFAULT 0,
  window_started_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tool_calls (
  call_id VARCHAR(255) PRIMARY KEY,
  grant_id VARCHAR(255) NOT NULL,
  pod_id VARCHAR(255),
  installation_id VARCHAR(255),
  agent_user_id VARCHAR(255) NOT NULL,
  tool VARCHAR(255) NOT NULL,
  args_digest CHAR(64) NOT NULL,
  occurred_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  outcome VARCHAR(32) NOT NULL CHECK (outcome IN ('ok', 'refused', 'pending_approval', 'failed')),
  reason VARCHAR(255),
  approval_id VARCHAR(255),
  duration_ms INTEGER,
  -- Whose credential ran (plan §8, §10 step 5): the Connection's `createdBy` at
  -- call time. Copied, not joined — removal ends by deleting the Connection and
  -- the trail outlives it. Nullable, and NULL means "no owner was recorded",
  -- which is what a call refused before a connection resolved reports.
  credential_owner_id VARCHAR(255),
  vendor_status INTEGER,
  vendor_error VARCHAR(200)
);
CREATE INDEX IF NOT EXISTS idx_tool_calls_grant_at ON tool_calls(grant_id, occurred_at DESC);
-- Per-user, per-thread state (W-T, TASK-029). ONE record carrying both
-- booleans, per ux-lead's ruling in docs/design/threading-surface-ruling.md
-- ("One state record, two booleans"): persisted collapse and follow share the
-- key (user, pod, thread root), so two tables would mean two writes for one
-- gesture. The collapsed column is here at birth rather than in the render PR.
--
-- NOT a widened User.followedThreads: that field is typed
-- `postId: ObjectId ref 'Post', required` and cannot hold a Postgres
-- messages.id. Widening it would silently drop chat rows from every consumer
-- doing Post.find({_id: {$in: postIds}}) -- postController 457/513/548,
-- activityService 614/712, ActivityFeedPage 584.
--
-- A row means "this user has non-default state on this thread". Row PRESENCE
-- carries no meaning on its own, which is why `following` is a column and not
-- row-existence: `collapsed` writes create rows for users who are not
-- following, and if presence meant following, expanding a thread would
-- subscribe you to it.
CREATE TABLE IF NOT EXISTS thread_user_state (
  id SERIAL PRIMARY KEY,
  thread_root_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
  user_id VARCHAR(255) NOT NULL,
  pod_id VARCHAR(255) NOT NULL,
  -- TRI-STATE, deliberately nullable. NULL = "no explicit choice, defer to the
  -- participation default" (posted in the thread or was @-mentioned => following).
  -- TRUE = explicitly followed. FALSE = explicitly unfollowed, which for a
  -- participant is a MUTE and must outrank the participation default.
  -- A NOT NULL DEFAULT false here would be a live bug: a collapse-only row
  -- would silently unfollow a participant who had never touched the toggle.
  following BOOLEAN,
  -- Defaults TRUE for everyone including followers -- following never implies
  -- expanded. Only the expand/collapse gesture writes it.
  collapsed BOOLEAN NOT NULL DEFAULT TRUE,
  followed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (thread_root_id, user_id)
);
-- The wake path asks "who follows this thread" on every threaded reply, so the
-- root is the hot key. The user index serves the per-pod render read.
CREATE INDEX IF NOT EXISTS idx_thread_user_state_root ON thread_user_state(thread_root_id);
CREATE INDEX IF NOT EXISTS idx_thread_user_state_user_pod ON thread_user_state(user_id, pod_id);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_messages_pod_id ON messages(pod_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);
-- Threading reads are "give me this thread" and "how many replies has it", both
-- keyed on the root. reply_to_message_id has no index, which is why the root is
-- stored rather than walked: deriving it per read is a seq-scan per level.
CREATE INDEX IF NOT EXISTS idx_messages_thread_root_id ON messages(thread_root_id);
CREATE INDEX IF NOT EXISTS idx_pod_members_pod_id ON pod_members(pod_id);
CREATE INDEX IF NOT EXISTS idx_pod_members_user_id ON pod_members(user_id);
CREATE INDEX IF NOT EXISTS idx_pods_created_by ON pods(created_by);
CREATE INDEX IF NOT EXISTS idx_pods_type ON pods(type);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);