-- Active: 1770616142450@@turntable.proxy.rlwy.net@45598@railway
-- Migration to add error, message, and cache_key columns to gateway_operation_log
-- Run this on existing databases to add the new columns

-- Add error column (boolean, default false)
ALTER TABLE gateway_operation_log 
ADD COLUMN IF NOT EXISTS error boolean DEFAULT false;

-- Add message column (text, nullable)
ALTER TABLE gateway_operation_log 
ADD COLUMN IF NOT EXISTS message text;

-- Add cache_key column (text, nullable)
ALTER TABLE gateway_operation_log 
ADD COLUMN IF NOT EXISTS cache_key text;

-- Optionally create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_gateway_operation_log_error 
ON gateway_operation_log(error) WHERE error = true;

CREATE INDEX IF NOT EXISTS idx_gateway_operation_log_cache_key 
ON gateway_operation_log(cache_key) WHERE cache_key IS NOT NULL;

-- Update existing records to set error=true for error status codes
UPDATE gateway_operation_log 
SET error = true 
WHERE status_code >= 400;

-- Ensure rows that reference an error message in details are marked as errors
UPDATE gateway_operation_log
SET error = true
WHERE details->>'error' IS NOT NULL;

-- Backfill the message column from existing details metadata (message → error → error_code)
UPDATE gateway_operation_log
SET message = details->>'message'
WHERE message IS NULL
  AND details->>'message' IS NOT NULL;

UPDATE gateway_operation_log
SET message = details->>'error'
WHERE message IS NULL
  AND details->>'error' IS NOT NULL;

UPDATE gateway_operation_log
SET message = details->>'error_code'
WHERE message IS NULL
  AND details->>'error_code' IS NOT NULL;

-- Backfill cache_key from the details payload when present
UPDATE gateway_operation_log
SET cache_key = details->>'cache_key'
WHERE cache_key IS NULL
  AND details->>'cache_key' IS NOT NULL;
