-- ─────────────────────────────────────────────
--  SPB Drive — Personal Cloud Drive
-- ─────────────────────────────────────────────
--  Author  : Simon-Pierre Boucher
--  Contact : contact@spboucher.ai
--  File    : src/db/schema.sql
--  Purpose : Full SQLite schema — nodes tree, blobs, shares, sessions, FTS5
--  License : MIT © Simon-Pierre Boucher
-- ─────────────────────────────────────────────

PRAGMA journal_mode = WAL;

CREATE TABLE IF NOT EXISTS meta (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

-- Content-addressed blob registry. Path on disk: files/<sha[0:2]>/<sha[2:4]>/<sha>
CREATE TABLE IF NOT EXISTS blobs (
  sha      TEXT PRIMARY KEY,
  size     INTEGER NOT NULL,
  refcount INTEGER NOT NULL DEFAULT 0,
  created  INTEGER NOT NULL
);

-- Virtual folder tree. Root is id=1 (parent_id NULL, type 'folder').
CREATE TABLE IF NOT EXISTS nodes (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  parent_id   INTEGER REFERENCES nodes(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  type        TEXT NOT NULL CHECK (type IN ('file', 'folder')),
  blob_sha    TEXT REFERENCES blobs(sha),
  size        INTEGER NOT NULL DEFAULT 0,
  mime        TEXT,
  created     INTEGER NOT NULL,
  modified    INTEGER NOT NULL,
  starred     INTEGER NOT NULL DEFAULT 0,
  color       TEXT,
  emoji       TEXT,
  trashed_at  INTEGER,
  trash_orig_parent INTEGER
);

CREATE INDEX IF NOT EXISTS idx_nodes_parent  ON nodes(parent_id, trashed_at);
CREATE INDEX IF NOT EXISTS idx_nodes_blob    ON nodes(blob_sha);
CREATE INDEX IF NOT EXISTS idx_nodes_trashed ON nodes(trashed_at) WHERE trashed_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_nodes_starred ON nodes(starred) WHERE starred = 1;
CREATE INDEX IF NOT EXISTS idx_nodes_recent  ON nodes(modified DESC);

CREATE TABLE IF NOT EXISTS shares (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  token          TEXT NOT NULL UNIQUE,
  node_id        INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
  created        INTEGER NOT NULL,
  expires_at     INTEGER,
  password_hash  TEXT,
  max_downloads  INTEGER,
  downloads      INTEGER NOT NULL DEFAULT 0,
  visits         INTEGER NOT NULL DEFAULT 0,
  allow_download INTEGER NOT NULL DEFAULT 1,
  label          TEXT,
  revoked_at     INTEGER
);

CREATE INDEX IF NOT EXISTS idx_shares_node ON shares(node_id);

CREATE TABLE IF NOT EXISTS share_events (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
  ts       INTEGER NOT NULL,
  kind     TEXT NOT NULL CHECK (kind IN ('visit', 'download')),
  ip       TEXT,
  ua       TEXT
);

CREATE INDEX IF NOT EXISTS idx_share_events_share ON share_events(share_id, ts DESC);

CREATE TABLE IF NOT EXISTS tags (
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  TEXT NOT NULL UNIQUE,
  color TEXT NOT NULL DEFAULT '#4f8cff'
);

CREATE TABLE IF NOT EXISTS node_tags (
  node_id INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
  tag_id  INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (node_id, tag_id)
);

-- Session ids are stored hashed (sha256) — a DB leak never leaks live cookies.
CREATE TABLE IF NOT EXISTS sessions (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  token_hash TEXT NOT NULL UNIQUE,
  created    INTEGER NOT NULL,
  last_seen  INTEGER NOT NULL,
  expires_at INTEGER NOT NULL,
  remember   INTEGER NOT NULL DEFAULT 0,
  ip         TEXT,
  ua         TEXT
);

CREATE TABLE IF NOT EXISTS api_tokens (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL,
  token_hash TEXT NOT NULL UNIQUE,
  created    INTEGER NOT NULL,
  last_used  INTEGER
);

CREATE TABLE IF NOT EXISTS activity (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  ts      INTEGER NOT NULL,
  kind    TEXT NOT NULL,
  node_id INTEGER,
  detail  TEXT,
  ip      TEXT
);

CREATE INDEX IF NOT EXISTS idx_activity_ts ON activity(ts DESC);

CREATE TABLE IF NOT EXISTS login_attempts (
  ip           TEXT PRIMARY KEY,
  fail_count   INTEGER NOT NULL DEFAULT 0,
  last_fail    INTEGER,
  locked_until INTEGER
);

-- Resumable upload sessions survive server restarts.
CREATE TABLE IF NOT EXISTS uploads (
  id         TEXT PRIMARY KEY,
  parent_id  INTEGER NOT NULL,
  name       TEXT NOT NULL,
  size       INTEGER NOT NULL,
  chunk_size INTEGER NOT NULL,
  n_chunks   INTEGER NOT NULL,
  created    INTEGER NOT NULL
);

-- Version history: superseded blobs kept when a file is replaced or edited.
-- Each row holds one refcount on its blob (released when the row is pruned).
CREATE TABLE IF NOT EXISTS node_versions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  node_id     INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
  blob_sha    TEXT NOT NULL REFERENCES blobs(sha),
  size        INTEGER NOT NULL,
  mime        TEXT,
  created     INTEGER NOT NULL,
  replaced_at INTEGER NOT NULL,
  origin      TEXT NOT NULL DEFAULT 'replace' CHECK (origin IN ('replace', 'edit', 'restore'))
);

CREATE INDEX IF NOT EXISTS idx_versions_node ON node_versions(node_id, replaced_at DESC);

-- File requests: public links that let anyone upload INTO a chosen folder.
CREATE TABLE IF NOT EXISTS file_requests (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  token      TEXT NOT NULL UNIQUE,
  folder_id  INTEGER NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
  label      TEXT,
  created    INTEGER NOT NULL,
  expires_at INTEGER,
  max_files  INTEGER,
  received   INTEGER NOT NULL DEFAULT 0,
  closed_at  INTEGER
);

-- Full-text search over name, tags and extracted text content.
CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts5(
  name, tags, content,
  tokenize = 'unicode61 remove_diacritics 2'
);

-- Map fts rowid <-> node id explicitly (fts rowid == node id).
CREATE TABLE IF NOT EXISTS fts_state (
  node_id     INTEGER PRIMARY KEY,
  content_sha TEXT
);