/**
 * @fileoverview Schema V1 DDL for the SQLite metadata index.
 */
export const SCHEMA_V1 = `
CREATE TABLE IF NOT EXISTS responses (
  response_id TEXT PRIMARY KEY,
  url TEXT NOT NULL,
  url_normalized TEXT NOT NULL,
  final_url TEXT,
  content_hash TEXT NOT NULL,
  content_type TEXT,
  status INTEGER,
  mode TEXT,
  format TEXT,
  byte_length INTEGER,
  stored_at TEXT NOT NULL,
  expires_at TEXT,
  metadata_json TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_responses_url_storedat ON responses(url_normalized, stored_at DESC);
CREATE INDEX IF NOT EXISTS idx_responses_hash ON responses(content_hash);
CREATE INDEX IF NOT EXISTS idx_responses_expires ON responses(expires_at);

CREATE TABLE IF NOT EXISTS fetched_responses (
  url_normalized TEXT NOT NULL,
  final_url TEXT,
  status INTEGER NOT NULL,
  content_type TEXT,
  content_hash TEXT NOT NULL,
  byte_length INTEGER NOT NULL,
  headers_json TEXT NOT NULL,
  fetched_at TEXT NOT NULL,
  expires_at TEXT,
  etag TEXT,
  last_modified TEXT,
  PRIMARY KEY (url_normalized, fetched_at)
);
CREATE INDEX IF NOT EXISTS idx_fetched_url_freshness ON fetched_responses(url_normalized, fetched_at DESC);
CREATE INDEX IF NOT EXISTS idx_fetched_expires ON fetched_responses(expires_at);

CREATE TABLE IF NOT EXISTS crawl_metadata (
  crawl_id TEXT PRIMARY KEY,
  seed_url TEXT NOT NULL,
  status TEXT NOT NULL,
  visited_count INTEGER NOT NULL DEFAULT 0,
  frontier_count INTEGER NOT NULL DEFAULT 0,
  succeeded_count INTEGER NOT NULL DEFAULT 0,
  failed_count INTEGER NOT NULL DEFAULT 0,
  current_depth INTEGER,
  max_depth_visited INTEGER,
  response_id TEXT REFERENCES responses(response_id) ON DELETE SET NULL,
  last_error_json TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS crawl_frontier (
  crawl_id TEXT NOT NULL REFERENCES crawl_metadata(crawl_id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  depth INTEGER NOT NULL,
  parent_url TEXT,
  enqueued_at TEXT NOT NULL,
  PRIMARY KEY (crawl_id, url)
);
CREATE INDEX IF NOT EXISTS idx_frontier_pop ON crawl_frontier(crawl_id, depth, enqueued_at);

CREATE TABLE IF NOT EXISTS crawl_visited (
  crawl_id TEXT NOT NULL REFERENCES crawl_metadata(crawl_id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  response_id TEXT REFERENCES responses(response_id) ON DELETE SET NULL,
  visited_at TEXT NOT NULL,
  PRIMARY KEY (crawl_id, url)
);

CREATE TABLE IF NOT EXISTS crawl_results (
  crawl_id TEXT NOT NULL REFERENCES crawl_metadata(crawl_id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  position INTEGER NOT NULL,
  PRIMARY KEY (crawl_id, url)
);

CREATE TABLE IF NOT EXISTS snapshots (
  url TEXT NOT NULL,
  snapshot_name TEXT NOT NULL,
  response_id TEXT NOT NULL REFERENCES responses(response_id) ON DELETE CASCADE,
  taken_at TEXT NOT NULL,
  PRIMARY KEY (url, snapshot_name, taken_at)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_snapshots_named ON snapshots(url, snapshot_name) WHERE snapshot_name <> '';
PRAGMA user_version = 1;
`;