CREATE TABLE IF NOT EXISTS contact_notes (
  id CHAR(16) PRIMARY KEY,
  contact_id CHAR(36) NOT NULL,
  author_id CHAR(16) NOT NULL,
  author_name VARCHAR(120) NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX ix_contact_notes_contact (contact_id, created_at),
  CONSTRAINT fk_contact_notes_contact
    FOREIGN KEY (contact_id) REFERENCES contact_messages(id)
    ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_activity (
  id CHAR(16) PRIMARY KEY,
  actor_id CHAR(16) NULL,
  actor_name VARCHAR(120) NULL,
  action VARCHAR(80) NOT NULL,
  detail TEXT NULL,
  created_at DATETIME(3) NOT NULL,
  INDEX ix_admin_activity_created (created_at)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE contact_messages
  ADD COLUMN IF NOT EXISTS follow_up_at DATETIME(3) NULL;