-- :principal is the one authenticated caller; NULL is anonymous.
-- A stale cache is not a permission grant. Freshness here is local revision consistency.
SELECT EXISTS (
  SELECT 1 FROM resources r
  WHERE r.id=:resource AND r.trust_state='trusted' AND (
    r.public_read=1 OR EXISTS (
      SELECT 1 FROM authority_cache a
      WHERE a.resource=r.id AND a.principal=:principal
        AND a.authority_revision=r.authority_revision AND (a.rights & 1)=1
    )
  )
) AS may_read_current_resource;

-- Membership is filtered BEFORE building counts, lists or RBSR fingerprints.
-- rbsr_item is only candidate membership, never an authorization cache.
SELECT DISTINCT b.id,b.cid,0 AS sort_time
FROM rbsr_item ri JOIN blobs b ON b.id=ri.blob
LEFT JOIN structural_blobs sb ON sb.id=b.id
WHERE ri.scope=:scope AND b.validation='verified' AND b.size>=0 AND (
  b.public_disclosed=1 OR EXISTS (
    SELECT 1 FROM blob_resources br JOIN resources r ON r.id=br.resource
    WHERE br.blob=b.id AND br.active=1 AND br.origin_verified=1 AND r.trust_state='trusted'
      AND (br.purpose<>'control' OR EXISTS (
        SELECT 1 FROM authority_cache admin
        WHERE admin.resource=r.id AND admin.principal=:principal
          AND admin.authority_revision=r.authority_revision AND (admin.rights & 4)=4
      ) OR EXISTS (
        SELECT 1 FROM capabilities own
        WHERE own.id=br.basis_blob AND own.resource=r.id AND own.delegate=:principal AND own.valid=1
      ) OR EXISTS (
        SELECT 1 FROM node_versions nv
        WHERE nv.id=br.basis_blob AND nv.resource=r.id AND nv.eligible=1
      ))
      AND (r.public_read=1 OR EXISTS (
        SELECT 1 FROM authority_cache a
        WHERE a.resource=r.id AND a.principal=:principal
          AND a.authority_revision=r.authority_revision AND (a.rights & 1)=1
      ))
  )
)
ORDER BY b.cid;

-- Tentative standalone HM26 model, not a Seed migration.
PRAGMA foreign_keys = ON;
CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT NOT NULL) WITHOUT ROWID;
CREATE TABLE public_keys (id INTEGER PRIMARY KEY, principal BLOB UNIQUE NOT NULL);
CREATE TABLE peers (id INTEGER PRIMARY KEY, pid TEXT UNIQUE NOT NULL, addresses TEXT NOT NULL DEFAULT '');
CREATE TABLE blobs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  cid BLOB UNIQUE NOT NULL,
  multihash BLOB NOT NULL,
  codec INTEGER NOT NULL,
  size INTEGER NOT NULL DEFAULT -1 CHECK (size >= -1),
  validation TEXT NOT NULL DEFAULT 'missing' CHECK (validation IN ('missing','verified','invalid')),
  public_disclosed INTEGER NOT NULL DEFAULT 0 CHECK (public_disclosed IN (0,1)),
  insert_time INTEGER NOT NULL DEFAULT (unixepoch()),
  data BLOB
);
CREATE INDEX blobs_by_hash ON blobs(multihash, codec);
CREATE INDEX blobs_metadata ON blobs(id, codec, size, validation, public_disclosed);
CREATE TABLE resources (
  id INTEGER PRIMARY KEY,
  initial_blob INTEGER UNIQUE NOT NULL REFERENCES blobs(id),
  root_principal INTEGER REFERENCES public_keys(id),
  root_binding INTEGER REFERENCES blobs(id),
  policy_blob INTEGER REFERENCES blobs(id),
  trust_state TEXT NOT NULL DEFAULT 'unknown' CHECK (trust_state IN ('unknown','trusted','rejected')),
  public_read INTEGER NOT NULL DEFAULT 0 CHECK (public_read IN (0,1)),
  authority_revision INTEGER NOT NULL DEFAULT 0,
  content_revision INTEGER NOT NULL DEFAULT 0,
  selected_node INTEGER REFERENCES node_versions(id),
  computed_state INTEGER REFERENCES blobs(id),
  state_status TEXT NOT NULL DEFAULT 'missing' CHECK (state_status IN ('missing','ready','unsupported','invalid')),
  FOREIGN KEY (selected_node,id) REFERENCES node_versions(id,resource)
);
CREATE INDEX resources_by_root ON resources(root_principal);
CREATE TABLE structural_blobs (
  id INTEGER PRIMARY KEY REFERENCES blobs(id),
  type TEXT NOT NULL,
  ts INTEGER,
  author INTEGER REFERENCES public_keys(id),
  resource INTEGER REFERENCES resources(id),
  extra_attrs TEXT NOT NULL DEFAULT '{}' CHECK (json_valid(extra_attrs)),
  UNIQUE (id,resource)
);
CREATE INDEX structural_blobs_by_resource ON structural_blobs(resource,type,ts,id);
CREATE INDEX structural_blobs_by_author ON structural_blobs(author,resource);
CREATE TABLE node_versions (
  id INTEGER PRIMARY KEY REFERENCES structural_blobs(id),
  resource INTEGER NOT NULL REFERENCES resources(id),
  kind TEXT NOT NULL CHECK (kind IN ('snapshot','changes','changes-snapshot','tombstone','redirect','republish')),
  parent INTEGER REFERENCES resources(id),
  placement_enabled INTEGER NOT NULL DEFAULT 1 CHECK (placement_enabled IN (0,1)),
  name TEXT,
  apply_blob INTEGER REFERENCES blobs(id),
  state_status TEXT NOT NULL DEFAULT 'missing' CHECK (state_status IN ('missing','ready','unsupported','invalid')),
  eligible INTEGER NOT NULL DEFAULT 0 CHECK (eligible IN (0,1)),
  evaluated_authority_revision INTEGER NOT NULL DEFAULT -1,
  UNIQUE (id,resource),
  FOREIGN KEY (id,resource) REFERENCES structural_blobs(id,resource)
);
CREATE INDEX node_versions_by_resource ON node_versions(resource,eligible,id);
CREATE INDEX node_versions_by_parent ON node_versions(parent,name,resource);
CREATE TABLE blob_links (
  source INTEGER NOT NULL REFERENCES blobs(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  target INTEGER NOT NULL REFERENCES blobs(id),
  PRIMARY KEY (source,type,target)
) WITHOUT ROWID;
CREATE INDEX blob_backlinks ON blob_links(target,type,source);
CREATE VIEW node_heads AS SELECT source AS node_version,target AS head FROM blob_links WHERE type='node/head';
CREATE VIEW change_deps AS SELECT source AS change_blob,target AS dependency FROM blob_links WHERE type='change/dep';
CREATE TABLE blob_resources (
  resource INTEGER NOT NULL REFERENCES resources(id),
  blob INTEGER NOT NULL REFERENCES blobs(id),
  basis_blob INTEGER NOT NULL REFERENCES blobs(id),
  purpose TEXT NOT NULL CHECK (purpose IN ('state','content','control','metadata')),
  origin_verified INTEGER NOT NULL CHECK (origin_verified IN (0,1)),
  active INTEGER NOT NULL DEFAULT 0 CHECK (active IN (0,1)),
  PRIMARY KEY (resource,blob,basis_blob,purpose)
) WITHOUT ROWID;
CREATE INDEX blob_resources_by_blob ON blob_resources(blob,active,resource);
CREATE INDEX blob_resources_by_basis ON blob_resources(basis_blob,resource);
CREATE TABLE capabilities (
  id INTEGER PRIMARY KEY REFERENCES structural_blobs(id),
  resource INTEGER NOT NULL REFERENCES resources(id),
  issuer INTEGER NOT NULL REFERENCES public_keys(id),
  delegate INTEGER NOT NULL REFERENCES public_keys(id),
  role TEXT NOT NULL CHECK (role IN ('reader','writer','irrevocable-admin')),
  proof_blob INTEGER REFERENCES blobs(id),
  valid INTEGER NOT NULL DEFAULT 0 CHECK (valid IN (0,1))
);
CREATE INDEX capabilities_by_delegate ON capabilities(resource,delegate,role);
CREATE INDEX capabilities_by_proof ON capabilities(proof_blob,resource);
CREATE TABLE revocations (
  id INTEGER PRIMARY KEY REFERENCES structural_blobs(id),
  resource INTEGER REFERENCES resources(id),
  target_grant INTEGER NOT NULL REFERENCES blobs(id),
  admin_proof INTEGER NOT NULL REFERENCES blobs(id),
  valid INTEGER NOT NULL DEFAULT 0 CHECK (valid IN (0,1))
);
CREATE INDEX revocations_by_target ON revocations(target_grant,valid);
CREATE INDEX revocations_by_resource ON revocations(resource,valid);
CREATE TABLE authority_cache (
  resource INTEGER NOT NULL REFERENCES resources(id),
  principal INTEGER NOT NULL REFERENCES public_keys(id),
  rights INTEGER NOT NULL CHECK (rights IN (0,1,3,7)),
  authority_revision INTEGER NOT NULL,
  PRIMARY KEY (resource,principal)
) WITHOUT ROWID;
CREATE TABLE authority_dependencies (
  resource INTEGER NOT NULL,
  principal INTEGER NOT NULL,
  evidence_blob INTEGER NOT NULL REFERENCES blobs(id),
  PRIMARY KEY (resource,principal,evidence_blob),
  FOREIGN KEY (resource,principal) REFERENCES authority_cache(resource,principal) ON DELETE CASCADE
) WITHOUT ROWID;
CREATE INDEX authority_dependencies_by_evidence ON authority_dependencies(evidence_blob,resource,principal);
CREATE TABLE peer_accounts (
  peer INTEGER NOT NULL REFERENCES peers(id),
  principal INTEGER NOT NULL REFERENCES public_keys(id),
  connection_nonce BLOB NOT NULL,
  auth_expires_at INTEGER NOT NULL,
  PRIMARY KEY (peer,principal,connection_nonce)
) WITHOUT ROWID;
CREATE TABLE stashed_blobs (
  id INTEGER NOT NULL REFERENCES blobs(id),
  missing_blob INTEGER NOT NULL REFERENCES blobs(id),
  reason TEXT NOT NULL CHECK (reason IN ('dependency','authority','origin','apply')),
  resource INTEGER REFERENCES resources(id),
  retry_after INTEGER,
  attempts INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (id,missing_blob,reason)
) WITHOUT ROWID;
CREATE INDEX stashed_by_missing ON stashed_blobs(missing_blob,id);
CREATE TABLE rbsr_scope (
  id INTEGER PRIMARY KEY,
  resource INTEGER NOT NULL REFERENCES resources(id),
  depth TEXT NOT NULL CHECK (depth IN ('exact','children','recursive')),
  facets INTEGER NOT NULL,
  materialized INTEGER NOT NULL DEFAULT 0 CHECK (materialized IN (0,1)),
  content_revision INTEGER NOT NULL DEFAULT -1,
  last_access INTEGER NOT NULL DEFAULT 0,
  UNIQUE (resource,depth,facets)
);
CREATE TABLE rbsr_item (
  scope INTEGER NOT NULL REFERENCES rbsr_scope(id) ON DELETE CASCADE,
  blob INTEGER NOT NULL REFERENCES blobs(id),
  PRIMARY KEY (scope,blob)
) WITHOUT ROWID;
CREATE INDEX rbsr_item_by_blob ON rbsr_item(blob,scope);
CREATE TABLE subscriptions (
  principal INTEGER NOT NULL REFERENCES public_keys(id),
  scope INTEGER NOT NULL REFERENCES rbsr_scope(id),
  mode TEXT NOT NULL CHECK (mode IN ('follow','pin','demand','ignore')),
  priority INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (principal,scope)
) WITHOUT ROWID;
CREATE TABLE sync_sessions (
  id BLOB PRIMARY KEY,
  peer INTEGER NOT NULL REFERENCES peers(id),
  viewer INTEGER REFERENCES public_keys(id),
  scope INTEGER REFERENCES rbsr_scope(id),
  purpose TEXT NOT NULL CHECK (purpose IN ('scope','control','offer','watch')),
  expires_at INTEGER NOT NULL,
  snapshot_sequence INTEGER NOT NULL,
  policy_stamp BLOB NOT NULL,
  resume_sequence INTEGER NOT NULL DEFAULT 0,
  invalidated INTEGER NOT NULL DEFAULT 0 CHECK (invalidated IN (0,1))
) WITHOUT ROWID;
CREATE INDEX sync_sessions_by_scope ON sync_sessions(scope,invalidated);
CREATE TABLE sync_session_items (
  session BLOB NOT NULL REFERENCES sync_sessions(id) ON DELETE CASCADE,
  blob INTEGER NOT NULL REFERENCES blobs(id),
  order_key BLOB NOT NULL,
  PRIMARY KEY (session,blob)
) WITHOUT ROWID;
CREATE INDEX session_items_by_order ON sync_session_items(session,order_key);
CREATE TABLE transfer_queue (
  id INTEGER PRIMARY KEY,
  peer INTEGER NOT NULL REFERENCES peers(id),
  resource INTEGER NOT NULL REFERENCES resources(id),
  blob INTEGER NOT NULL REFERENCES blobs(id),
  direction TEXT NOT NULL CHECK (direction IN ('fetch','offer')),
  status TEXT NOT NULL CHECK (status IN ('queued','running','waiting','stored','indexed','rejected')),
  attempt INTEGER NOT NULL DEFAULT 0,
  request_key BLOB NOT NULL,
  request_digest BLOB NOT NULL,
  next_retry INTEGER NOT NULL DEFAULT 0,
  receipt TEXT CHECK (receipt IS NULL OR json_valid(receipt)),
  UNIQUE (peer,direction,request_key,blob)
);
CREATE INDEX transfer_queue_due ON transfer_queue(status,next_retry,peer);
CREATE TABLE index_events (
  sequence INTEGER PRIMARY KEY AUTOINCREMENT,
  resource INTEGER NOT NULL REFERENCES resources(id),
  event_kind TEXT NOT NULL CHECK (event_kind IN ('state','authority','policy','materialization')),
  source_blob INTEGER NOT NULL REFERENCES blobs(id),
  authority_revision INTEGER NOT NULL,
  content_revision INTEGER NOT NULL
);
CREATE INDEX index_events_by_resource ON index_events(resource,sequence);
CREATE TABLE resource_metadata (
  resource INTEGER NOT NULL REFERENCES resources(id),
  field TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('n','s','b','i','f','t','r')),
  value,
  source_node INTEGER NOT NULL REFERENCES node_versions(id),
  PRIMARY KEY (resource,field),
  FOREIGN KEY (source_node,resource) REFERENCES node_versions(id,resource)
) WITHOUT ROWID;
CREATE INDEX metadata_query ON resource_metadata(field,kind,value,resource);
CREATE TABLE retention_roots (
  principal INTEGER NOT NULL REFERENCES public_keys(id),
  blob INTEGER NOT NULL REFERENCES blobs(id),
  reason TEXT NOT NULL CHECK (reason IN ('pin','draft','outbox','authority','audit')),
  PRIMARY KEY (principal,blob,reason)
) WITHOUT ROWID;

-- Tentative evaluator AFTER hash/signature/subject validation, within the index transaction.
-- Bind :resource to the local integer resource id; cryptography is not implemented here.
WITH RECURSIVE admins(id,delegate) AS (
  SELECT c.id,c.delegate
  FROM capabilities c JOIN resources r ON r.id=c.resource
  WHERE c.resource=:resource AND r.trust_state='trusted'
    AND c.role='irrevocable-admin' AND c.issuer=r.root_principal AND c.proof_blob IS NULL
  UNION
  SELECT c.id,c.delegate
  FROM capabilities c JOIN admins a ON a.id=c.proof_blob AND a.delegate=c.issuer
  WHERE c.resource=:resource AND c.role='irrevocable-admin'
)
UPDATE capabilities
SET valid = CASE
  WHEN role='irrevocable-admin' THEN id IN (SELECT id FROM admins)
  ELSE EXISTS (
    SELECT 1 FROM resources r
    WHERE r.id=:resource AND r.trust_state='trusted' AND (
      (capabilities.issuer=r.root_principal AND capabilities.proof_blob IS NULL)
      OR EXISTS (SELECT 1 FROM admins a WHERE a.id=capabilities.proof_blob AND a.delegate=capabilities.issuer)
    )
  ) END
WHERE resource=:resource;

UPDATE revocations
SET valid = EXISTS (
  SELECT 1 FROM capabilities target
  JOIN capabilities admin ON admin.id=revocations.admin_proof
  JOIN structural_blobs sb ON sb.id=revocations.id
  WHERE target.id=revocations.target_grant AND target.resource=:resource
    AND target.role IN ('reader','writer')
    AND admin.resource=target.resource AND admin.role='irrevocable-admin' AND admin.valid=1
    AND admin.delegate=sb.author
)
WHERE resource=:resource;

DELETE FROM authority_cache WHERE resource=:resource;
INSERT INTO authority_cache(resource,principal,rights,authority_revision)
WITH rights(principal,mask) AS (
  SELECT root_principal,7 FROM resources WHERE id=:resource AND trust_state='trusted'
  UNION ALL
  SELECT c.delegate, CASE WHEN c.role='irrevocable-admin' THEN 7 WHEN c.role='writer' THEN 3 ELSE 1 END
  FROM capabilities c
  WHERE c.resource=:resource AND c.valid=1 AND (
    c.role='irrevocable-admin' OR NOT EXISTS (
      SELECT 1 FROM revocations rev WHERE rev.target_grant=c.id AND rev.valid=1
    )
  )
)
SELECT :resource,principal,MAX(mask),(SELECT authority_revision FROM resources WHERE id=:resource)
FROM rights GROUP BY principal;