-- =========================================================================
-- SQL Fix Script for Morizon SourceHtml Configuration
-- =========================================================================
-- Problem: The actions/selectors/inactive/raw_data_setup columns have nested
-- structure with _comment/_usage fields, but they should contain ONLY the
-- actual data arrays/objects.
-- =========================================================================

-- Fix actions column: Extract the nested "actions" array from the wrapper object
UPDATE extractly_sourcehtml
SET actions = actions->'actions'
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949'
AND jsonb_typeof(actions) = 'object'
AND actions ? 'actions';

-- Fix selectors column: Remove _comment/_usage/_instructions fields
UPDATE extractly_sourcehtml
SET selectors = selectors - '_comment' - '_usage' - '_instructions'
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949'
AND selectors ? '_comment';

-- Fix inactive column: Remove _comment/_usage/_instructions fields
UPDATE extractly_sourcehtml
SET inactive = inactive - '_comment' - '_usage' - '_instructions'
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949'
AND inactive ? '_comment';

-- Fix raw_data_setup column: Remove _comment/_usage/_instructions fields
UPDATE extractly_sourcehtml
SET raw_data_setup = raw_data_setup - '_comment' - '_usage' - '_instructions' - '_table' - '_row_id'
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949'
AND raw_data_setup ? '_comment';

-- =========================================================================
-- Verification Queries
-- =========================================================================

-- Check actions is now an array with 7 elements
SELECT 
    id,
    name,
    jsonb_typeof(actions) as actions_type,
    jsonb_array_length(actions) as actions_count,
    actions->0->>'name' as first_action_name
FROM extractly_sourcehtml
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949';

-- Expected output:
-- actions_type = 'array'
-- actions_count = 7
-- first_action_name = 'accept_cookies_if_present'

-- Check selectors structure (should have "list", "detail", "sliced_html" keys, no "_comment")
SELECT 
    jsonb_object_keys(selectors) as selector_keys
FROM extractly_sourcehtml
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949';

-- Expected keys: list, detail, sliced_html (NO _comment, _usage, _instructions)

-- Check inactive structure (should have "css_exists_any", "http_status_in", "text_contains_any" keys)
SELECT 
    jsonb_object_keys(inactive) as inactive_keys
FROM extractly_sourcehtml
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949';

-- Expected keys: css_exists_any, http_status_in, text_contains_any (NO _comment)

-- Check raw_data_setup structure (should have "meta_dump", "jsonld_dump", "additional_metadata" keys)
SELECT 
    jsonb_object_keys(raw_data_setup) as raw_data_keys
FROM extractly_sourcehtml
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949';

-- Expected keys: meta_dump, jsonld_dump, additional_metadata (NO _comment, _table, _row_id)

-- =========================================================================
-- Full Verification - Show cleaned structure
-- =========================================================================

SELECT 
    name,
    jsonb_pretty(actions) as actions_preview,
    jsonb_pretty(selectors) as selectors_preview,
    jsonb_pretty(inactive) as inactive_preview,
    jsonb_pretty(raw_data_setup) as raw_data_preview
FROM extractly_sourcehtml
WHERE source_id = '779dc8fa-ef0b-4627-8cf6-755f3375d949';
