CREATE TABLE IF NOT EXISTS api_keys (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     INT UNSIGNED NOT NULL,
  name          VARCHAR(255) NOT NULL,
  key_prefix    VARCHAR(32)  NOT NULL,
  key_hash      CHAR(64)     NOT NULL,
  scopes        JSON         NOT NULL,
  created_by_id INT UNSIGNED NOT NULL,
  expires_at    DATETIME     DEFAULT NULL,
  last_used_at  DATETIME     DEFAULT NULL,
  is_active     TINYINT(1)   DEFAULT 1,
  created_at    DATETIME     DEFAULT NOW(),
  UNIQUE KEY uq_api_keys_hash (key_hash),
  INDEX idx_api_keys_tenant (tenant_id),
  INDEX idx_api_keys_active (tenant_id, is_active),
  CONSTRAINT fk_api_keys_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_api_keys_user   FOREIGN KEY (created_by_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
