-- Active: 1772496994088@@metro.proxy.rlwy.net@13239@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;