CREATE TABLE IF NOT EXISTS auditor_links (
  id              CHAR(36)     NOT NULL DEFAULT (UUID()),
  tenant_id       CHAR(36)     NOT NULL,
  token_hash      VARCHAR(64)  NOT NULL UNIQUE,
  name            VARCHAR(200) NOT NULL,
  created_by      CHAR(36)     NOT NULL,
  expires_at      DATETIME     NOT NULL,
  scope           JSON         NULL COMMENT 'e.g. {audit_ids:[], framework_ids:[], include_evidence: true}',
  is_active       BOOLEAN      NOT NULL DEFAULT TRUE,
  last_accessed_at DATETIME    NULL,
  access_count    INT          NOT NULL DEFAULT 0,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_al_tenant (tenant_id),
  INDEX idx_al_token  (token_hash),
  CONSTRAINT fk_al_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_al_user   FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
