-- Wiki schema. Run via migrate.py (which also CREATEs the database and imports
-- the existing work-notes.md). All statements are idempotent (IF NOT EXISTS).

CREATE TABLE IF NOT EXISTS notes (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(255) NOT NULL UNIQUE,
  title       VARCHAR(255) NOT NULL,
  body_md     MEDIUMTEXT   NOT NULL,
  archived    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FULLTEXT KEY ft_notes (title, body_md)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Snapshots written only when the user supplies a message on save
-- (mirrors the old "Create a new version?" git-commit behaviour).
CREATE TABLE IF NOT EXISTS note_versions (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  note_id     INT          NOT NULL,
  title       VARCHAR(255) NOT NULL,
  body_md     MEDIUMTEXT   NOT NULL,
  message     VARCHAR(500),
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ver_note (note_id),
  CONSTRAINT fk_ver_note FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attachments (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  note_id     INT          NULL,
  filename    VARCHAR(255) NOT NULL,
  mime_type   VARCHAR(128) NOT NULL,
  size        INT          NOT NULL,
  sha256      CHAR(64)     NOT NULL,
  data        LONGBLOB     NOT NULL,
  ocr_text    MEDIUMTEXT   NULL,
  ocr_status  ENUM('pending','done','failed','skipped') NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sha (sha256),
  KEY idx_ocr_status (ocr_status),
  CONSTRAINT fk_att_note FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE SET NULL,
  FULLTEXT KEY ft_ocr (ocr_text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per [[wiki link]] found in a note body. to_note_id is NULL while the
-- target note does not yet exist (broken / forward link). Powers backlinks.
CREATE TABLE IF NOT EXISTS links (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  from_note_id  INT          NOT NULL,
  to_slug       VARCHAR(255) NOT NULL,
  to_note_id    INT          NULL,
  KEY idx_link_from (from_note_id),
  KEY idx_link_to_slug (to_slug),
  KEY idx_link_to_note (to_note_id),
  CONSTRAINT fk_link_from FOREIGN KEY (from_note_id) REFERENCES notes(id) ON DELETE CASCADE,
  CONSTRAINT fk_link_to   FOREIGN KEY (to_note_id)   REFERENCES notes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;