/*
CREATE OR REPLACE PROCEDURE get_configurations_values_v1(
    IN request JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
    current_category_id TEXT;
    user_rec RECORD;
    config_map JSONB := '{}'::JSONB;
    categories JSONB;
    input_user_id INT;
    latest_updated TIMESTAMP := NULL;
    temp_updated TIMESTAMP;
BEGIN
    -- Initialize the result as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '{}'::JSONB;
    END IF;
 
    -- Extract categories and user_id from the request JSONB
    categories := (request->>'categories')::JSONB;
 
    -- Safely extract user_id, handle null case
    IF request ? 'user_id' AND request->>'user_id' != '' AND request->>'user_id' != 'null' THEN
        BEGIN
            input_user_id := (request->>'user_id')::INT;
        EXCEPTION 
            WHEN others THEN
                input_user_id := NULL;
        END;
    ELSE
        input_user_id := NULL;
    END IF;
 
    -- Check if categories exists
    IF categories IS NULL THEN
        result := '{"error": "categories parameter is required"}'::JSONB;
        RETURN;
    END IF;
 
    -- Loop through each value in the categories object
    FOR current_category_id IN 
        SELECT jsonb_each_text.value 
        FROM jsonb_each_text(categories)
    LOOP
        -- 1. Fetch all default configs for this category
        FOR rec IN 
            SELECT ac.config_key, ac.config_value, ac.updated_at
            FROM app_configurations ac
            WHERE ac.category_id = current_category_id 
            ORDER BY ac.order_no ASC
        LOOP
            config_map := jsonb_set(
                config_map,
                ARRAY[rec.config_key],
                COALESCE(to_jsonb(rec.config_value), 'null'::JSONB),
   
 
                TRUE
            );
            -- Track latest updated_at
            IF rec.updated_at IS NOT NULL THEN
                IF latest_updated IS NULL OR rec.updated_at > latest_updated THEN
                    latest_updated := rec.updated_at;
                END IF;
            END IF;
        END LOOP;
 
        -- 2. If user_id is provided, fetch user configs and override/add
        IF input_user_id IS NOT NULL THEN
            FOR user_rec IN 
                SELECT auc.config_key, auc.config_value, auc.updated_at
                FROM app_user_configurations auc
                WHERE auc.category_id = current_category_id
                  AND auc.user_id = input_user_id
            LOOP
                config_map := jsonb_set(
                    config_map,
                    ARRAY[user_rec.config_key],
                    COALESCE(to_jsonb(user_rec.config_value), 'null'::JSONB),
    
 
 
                    TRUE
                );
                -- Track latest updated_at
                IF user_rec.updated_at IS NOT NULL THEN
                    IF latest_updated IS NULL OR user_rec.updated_at > latest_updated THEN
                        latest_updated := user_rec.updated_at;
                    END IF;
                END IF;
            END LOOP;
        END IF;
    END LOOP;
 
    -- Final result
    result := jsonb_set(result, '{data}', config_map, TRUE);
    IF latest_updated IS NOT NULL THEN
        result := jsonb_set(result, '{latest_updated_at}', to_jsonb(latest_updated::TEXT), TRUE);
    END IF;
END;
$$;

*/

CREATE OR REPLACE PROCEDURE get_configurations_values_v1(
    IN request JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
    config_map JSONB := '{}'::JSONB;
    categories JSONB;
    input_user_id INT;
    latest_updated TIMESTAMP := NULL;
BEGIN
    -- Initialize the result as an empty JSON object if it's not provided
    IF result IS NULL THEN
        result := '{}'::JSONB;
    END IF;

    -- Extract categories and user_id from the request JSONB
    categories := (request->>'categories')::JSONB;

    -- Safely extract user_id, handle null case
    IF request ? 'user_id' AND request->>'user_id' != '' AND request->>'user_id' != 'null' THEN
        BEGIN
            input_user_id := (request->>'user_id')::INT;
        EXCEPTION 
            WHEN others THEN
                input_user_id := NULL;
        END;
    ELSE
        input_user_id := NULL;
    END IF;

    -- Check if categories exists
    IF categories IS NULL THEN
        result := '{"error": "categories parameter is required"}'::JSONB;
        RETURN;
    END IF;

    FOR rec IN 
        WITH category_list AS (
            SELECT jsonb_each_text.value AS category_id
            FROM jsonb_each_text(categories)
        ),
        -- All default configs with potential user overrides
        default_configs AS (
            SELECT 
                ac.config_key,
                -- User value takes priority if exists, else default
                CASE 
                    WHEN auc.config_key IS NOT NULL THEN auc.config_value
                    ELSE ac.config_value
                END AS config_value,
                GREATEST(ac.updated_at, auc.updated_at) AS updated_at
            FROM app_configurations ac
            INNER JOIN category_list cl ON ac.category_id = cl.category_id
            LEFT JOIN app_user_configurations auc 
                ON ac.config_key = auc.config_key 
                AND ac.category_id = auc.category_id
                AND auc.user_id = input_user_id
        ),
        -- User-only configs (not in defaults)
        user_only_configs AS (
            SELECT 
                auc.config_key,
                auc.config_value,
                auc.updated_at
            FROM app_user_configurations auc
            INNER JOIN category_list cl ON auc.category_id = cl.category_id
            WHERE auc.user_id = input_user_id
              AND NOT EXISTS (
                  SELECT 1 
                  FROM app_configurations ac 
                  WHERE ac.config_key = auc.config_key 
                    AND ac.category_id = auc.category_id
              )
        )
        SELECT config_key, config_value, updated_at FROM default_configs
        UNION ALL
        SELECT config_key, config_value, updated_at FROM user_only_configs
    LOOP
        config_map := jsonb_set(
            config_map,
            ARRAY[rec.config_key],
            COALESCE(to_jsonb(rec.config_value), 'null'::JSONB),
            TRUE
        );
        
        -- Track latest updated_at
        IF rec.updated_at IS NOT NULL THEN
            IF latest_updated IS NULL OR rec.updated_at > latest_updated THEN
                latest_updated := rec.updated_at;
            END IF;
        END IF;
    END LOOP;

    -- Final result
    result := jsonb_set(result, '{data}', config_map, TRUE);
    IF latest_updated IS NOT NULL THEN
        result := jsonb_set(result, '{latest_updated_at}', to_jsonb(latest_updated::TEXT), TRUE);
    END IF;
END;
$$;