SET NAMES utf8mb4;

ALTER TABLE structured_log_events ADD COLUMN IF NOT EXISTS correlation_id VARCHAR(80) NULL;
ALTER TABLE integration_http_logs ADD COLUMN IF NOT EXISTS correlation_id VARCHAR(80) NULL;

CREATE INDEX IF NOT EXISTS idx_log_correlation ON structured_log_events(correlation_id,created_at);
CREATE INDEX IF NOT EXISTS idx_http_correlation ON integration_http_logs(correlation_id,created_at);

CREATE TABLE IF NOT EXISTS observability_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  active_incidents INT UNSIGNED NOT NULL DEFAULT 0,
  failed_jobs_24h INT UNSIGNED NOT NULL DEFAULT 0,
  integration_issues INT UNSIGNED NOT NULL DEFAULT 0,
  avg_adapter_latency_ms DECIMAL(12,2) NOT NULL DEFAULT 0,
  unread_notifications INT UNSIGNED NOT NULL DEFAULT 0,
  INDEX idx_obs_snapshot_ws_time(workspace_id,captured_at),
  CONSTRAINT fk_obs_snapshot_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
