-- Editable text and rules: UI wording, canned replies, system prompts and keyword rules.
-- Filled by `npm run db:seed` from db/seeds/*.json; edit rows here (e.g. in MySQL Workbench) to change the wording
-- without a deploy. The app picks changes up within CONTENT_CACHE_MS (30s) and ignores any row that fails validation.

CREATE TABLE content_entries (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entry_key   VARCHAR(128)    NOT NULL,                       -- e.g. 'prompt.persona', 'rule.greeting', 'copy.signIn'
  scope       ENUM('client', 'server') NOT NULL,              -- client entries are also sent to the browser; server ones never leave the server
  value_type  ENUM('text', 'regex', 'regexList', 'stringMap', 'numberMap') NOT NULL,
  value       MEDIUMTEXT      NOT NULL,                       -- text as is; a pattern as is; patterns one per line; maps as JSON
  flags       VARCHAR(8)      NULL,                           -- regex flags (i, m, s, u)
  description VARCHAR(500)    NULL,                           -- what it is for, and what {{blanks}} it can use
  seed_hash   CHAR(64)        NULL,                           -- SHA-256 of what the seeder last wrote; tells the seeder whether you have edited the row
  created_at  DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at  DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_content_entries_key (entry_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;