-- Sequence table: single-row counter for short link code generation
CREATE TABLE IF NOT EXISTS link_seq (
  id      INTEGER PRIMARY KEY CHECK (id = 1),
  next_id INTEGER NOT NULL DEFAULT 0
);
INSERT OR IGNORE INTO link_seq (id, next_id) VALUES (1, 0);

-- Short links table
CREATE TABLE IF NOT EXISTS short_links (
  id            INTEGER  PRIMARY KEY AUTOINCREMENT,
  code          TEXT     UNIQUE NOT NULL,
  target_url    TEXT     NOT NULL,
  expires_at    INTEGER  NULL,       -- Unix seconds, NULL = never expires
  max_clicks    INTEGER  NULL,       -- NULL = unlimited
  clicks        INTEGER  NOT NULL DEFAULT 0,
  password_hash TEXT     NULL,       -- NULL = no password; format: "pbkdf2:<salt>:<hash>"
  deleted       INTEGER  NOT NULL DEFAULT 0,
  created_at    INTEGER  NOT NULL,
  updated_at    INTEGER  NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_short_links_code ON short_links (code);
CREATE INDEX IF NOT EXISTS idx_short_links_expires ON short_links (expires_at) WHERE expires_at IS NOT NULL;