-- Records synced from external tap instance

CREATE TABLE IF NOT EXISTS repo_records (

  id INTEGER PRIMARY KEY AUTOINCREMENT,

  did TEXT NOT NULL,

  rkey TEXT NOT NULL,

  collection TEXT NOT NULL,

  cid TEXT,

  synced_at TEXT DEFAULT (datetime('now')),

  UNIQUE(did, collection, rkey)

);

CREATE INDEX IF NOT EXISTS idx_repo_records_collection ON repo_records(collection);

CREATE INDEX IF NOT EXISTS idx_repo_records_did ON repo_records(did);

CREATE INDEX IF NOT EXISTS idx_repo_records_rkey ON repo_records(rkey DESC);

-- Cache for resolved PDS endpoints

CREATE TABLE IF NOT EXISTS pds_cache (

  did TEXT PRIMARY KEY,

  pds_endpoint TEXT NOT NULL,

  cached_at TEXT DEFAULT (datetime('now'))

);

-- Sync metadata to track last sync

CREATE TABLE IF NOT EXISTS sync_metadata (

  key TEXT PRIMARY KEY,

  value TEXT NOT NULL,

  updated_at TEXT DEFAULT (datetime('now'))

);

-- Pre-resolved documents for fast feed serving

CREATE TABLE IF NOT EXISTS resolved_documents (

  uri TEXT PRIMARY KEY,

  did TEXT NOT NULL,

  rkey TEXT NOT NULL,

  -- Document fields

  title TEXT,

  description TEXT,

  path TEXT,

  site TEXT,

  content TEXT,  -- JSON blob for content union

  text_content TEXT,

  cover_image_cid TEXT,  -- CID for cover image blob

  cover_image_url TEXT,  -- Full URL: {pds}/xrpc/com.atproto.sync.getBlob?did={did}&cid={cid}

  bsky_post_ref TEXT,  -- JSON blob for strong reference {uri, cid}

  tags TEXT,  -- JSON array of strings

  published_at TEXT,

  updated_at TEXT,

  -- Publication fields (resolved from site at:// URI)

  pub_url TEXT,  -- Publication base URL

  pub_name TEXT,

  pub_description TEXT,

  pub_icon_cid TEXT,  -- CID for publication icon blob

  pub_icon_url TEXT,  -- Full URL to publication icon

  -- Metadata

  view_url TEXT,  -- Constructed canonical URL (pub_url + path)

  pds_endpoint TEXT,  -- Cached PDS endpoint for this DID

  resolved_at TEXT DEFAULT (datetime('now')),

  stale_at TEXT,  -- When this record should be re-resolved

  verified INTEGER DEFAULT 0  -- Whether the record has been verified via .well-known or link tag

);

CREATE INDEX IF NOT EXISTS idx_resolved_documents_rkey ON resolved_documents(rkey DESC);

CREATE INDEX IF NOT EXISTS idx_resolved_documents_stale ON resolved_documents(stale_at);

CREATE INDEX IF NOT EXISTS idx_resolved_documents_pub_url ON resolved_documents(pub_url);

CREATE INDEX IF NOT EXISTS idx_resolved_documents_verified ON resolved_documents(verified);