-- NGBVD API zero-trust security columns.
-- Run this only if you prefer SQL instead of Laravel migration.

ALTER TABLE `api_clients`
  ADD COLUMN `client_secret_encrypted` TEXT NULL AFTER `client_secret_hash`,
  ADD COLUMN `signing_secret_encrypted` TEXT NULL AFTER `client_secret_encrypted`,
  ADD COLUMN `encryption_key_encrypted` TEXT NULL AFTER `signing_secret_encrypted`,
  ADD COLUMN `allowed_district_ids` TEXT NULL AFTER `allowed_scopes`,
  ADD COLUMN `partner_id` BIGINT UNSIGNED NULL AFTER `allowed_district_ids`,
  ADD COLUMN `require_signed_requests` TINYINT(1) NOT NULL DEFAULT 0 AFTER `outbound_notes`,
  ADD COLUMN `require_payload_encryption` TINYINT(1) NOT NULL DEFAULT 0 AFTER `require_signed_requests`,
  ADD COLUMN `require_https` TINYINT(1) NOT NULL DEFAULT 1 AFTER `require_payload_encryption`,
  ADD COLUMN `rate_limit_per_minute` SMALLINT UNSIGNED NOT NULL DEFAULT 60 AFTER `require_https`,
  ADD COLUMN `signature_window_seconds` SMALLINT UNSIGNED NOT NULL DEFAULT 300 AFTER `rate_limit_per_minute`,
  ADD COLUMN `failed_attempts` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `signature_window_seconds`,
  ADD COLUMN `last_failed_at` TIMESTAMP NULL AFTER `failed_attempts`;
