-- External API keys — allow third-party systems to push evidence programmatically.
-- Key format: grc_<64-char hex>  (prefix "grc_" shown in UI, full key hashed for storage)

CREATE TABLE IF NOT EXISTS external_api_keys (
  id            CHAR(36)      NOT NULL PRIMARY KEY DEFAULT (UUID()),
  tenant_id     CHAR(36)      NOT NULL,
  created_by    CHAR(36)      DEFAULT NULL,
  name          VARCHAR(255)  NOT NULL,
  key_prefix    VARCHAR(12)   NOT NULL,
  key_hash      VARCHAR(64)   NOT NULL,
  scopes        JSON          NOT NULL COMMENT 'e.g. ["evidence:read","evidence:write"]',
  description   TEXT          DEFAULT NULL,
  last_used_at  DATETIME      DEFAULT NULL,
  last_used_ip  VARCHAR(45)   DEFAULT NULL,
  expires_at    DATETIME      DEFAULT NULL,
  is_active     BOOLEAN       NOT NULL DEFAULT TRUE,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_key_hash (key_hash),
  INDEX idx_eak_tenant (tenant_id),
  INDEX idx_eak_active (tenant_id, is_active),
  CONSTRAINT fk_eak_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_eak_user   FOREIGN KEY (created_by) REFERENCES users(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extend evidence table to track external submission metadata
ALTER TABLE evidence
  ADD COLUMN IF NOT EXISTS source_url   VARCHAR(2048) DEFAULT NULL AFTER hash_sha256,
  ADD COLUMN IF NOT EXISTS source_name  VARCHAR(255)  DEFAULT NULL AFTER source_url,
  ADD COLUMN IF NOT EXISTS api_key_id   CHAR(36)      DEFAULT NULL AFTER source_name,
  ADD COLUMN IF NOT EXISTS content_text MEDIUMTEXT    DEFAULT NULL AFTER api_key_id;

ALTER TABLE evidence
  ADD INDEX IF NOT EXISTS idx_ev_api_key (api_key_id),
  ADD INDEX IF NOT EXISTS idx_ev_source (source_name);
