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_v2(
    IN request JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    rec RECORD;
    current_category_id TEXT;
    user_rec RECORD;
    config_data JSONB := '{}'::JSONB;
    category_configs JSONB := '[]'::JSONB;
    categories JSONB;
    input_org_code TEXT;
    input_user_id INT;
    org_id INT;
    latest_updated TIMESTAMP := NULL;
    temp_updated TIMESTAMP;
    config_item JSONB;
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 org_code from the request JSONB
    categories := request->'categories';
    input_org_code := request->>'org_code';

    -- If org_code is not provided, return an error
    IF input_org_code IS NULL OR input_org_code = '' THEN
        result := '{"error": "org_code parameter is required"}'::JSONB;
        RETURN;
    END IF;

    -- Fetch the organization_id using the org_code
    SELECT o.id INTO org_id
    FROM organizations o
    WHERE o.code = input_org_code
    LIMIT 1;

    IF org_id IS NULL THEN
        result := '{"error": "Invalid org_code"}'::JSONB;
        RETURN;
    END IF;

    -- Get user_id from user_details using the organization_id
    SELECT u.user_id INTO input_user_id
    FROM user_details u
    WHERE u.organization_id = org_id
    LIMIT 1;

    IF input_user_id IS NULL THEN
        result := '{"error": "No user found for the provided org_code"}'::JSONB;
        RETURN;
    END IF;

    -- Check if categories exists and is an array
    IF categories IS NULL OR jsonb_typeof(categories) <> 'array' THEN
        result := '{"error": "categories parameter must be a JSON array"}'::JSONB;
        RETURN;
    END IF;

    -- Loop through each category id in the array
    FOR current_category_id IN
        SELECT jsonb_array_elements_text(categories)
    LOOP
        -- Reset category_configs for each category
        category_configs := '[]'::JSONB;
        
        -- 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
            -- Create config item object
            config_item := jsonb_build_object(
                'key', rec.config_key,
                'value', COALESCE(rec.config_value, null)
            );
            
            -- Add to category_configs array
            category_configs := category_configs || config_item;
            
            -- Track latest update timestamp
            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
                -- Check if this config_key already exists in category_configs
                -- If it exists, update it; if not, add it
                DECLARE
                    found_index INT := -1;
                    i INT := 0;
                    temp_item JSONB;
                BEGIN
                    -- Find if the key already exists
                    FOR i IN 0..jsonb_array_length(category_configs) - 1 LOOP
                        temp_item := category_configs->i;
                        IF temp_item->>'key' = user_rec.config_key THEN
                            found_index := i;
                            EXIT;
                        END IF;
                    END LOOP;
                    
                    -- Create new config item
                    config_item := jsonb_build_object(
                        'key', user_rec.config_key,
                        'value', COALESCE(user_rec.config_value, null)
                    );
                    
                    IF found_index >= 0 THEN
                        -- Update existing item
                        category_configs := jsonb_set(
                            category_configs,
                            ARRAY[found_index::TEXT],
                            config_item,
                            FALSE
                        );
                    ELSE
                        -- Add new item
                        category_configs := category_configs || config_item;
                    END IF;
                END;
                
                -- Track latest update timestamp
                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;
        
        -- Add category configs to main config_data object
        config_data := jsonb_set(
            config_data,
            ARRAY[current_category_id],
            category_configs,
            TRUE
        );
    END LOOP;

    -- Final result with "config-data" instead of "data"
    result := jsonb_set(result, '{config-data}', config_data, 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 compare_last_updated_at(
    IN request JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql AS $$
DECLARE
    input_org_code TEXT;
    input_last_updated_at TIMESTAMP;
    org_id INT;  -- Renamed to avoid ambiguity
    input_user_id INT;
    user_config_last_updated TIMESTAMP;
BEGIN
    -- Extract the org_code and last_updated_at from the request JSONB
    input_org_code := request->>'org_code';
    input_last_updated_at := (request->>'last_updated_at')::TIMESTAMP;

    -- If org_code or last_updated_at is not provided, return an error
    IF input_org_code IS NULL OR input_last_updated_at IS NULL THEN
        result := '{"error": "org_code and last_updated_at parameters are required"}'::JSONB;
        RETURN;
    END IF;

    -- Fetch the org_id using the org_code
    SELECT o.id INTO org_id
    FROM organizations o
    WHERE o.code = input_org_code
    LIMIT 1;

    -- If no organization found, return an error
    IF org_id IS NULL THEN
        result := '{"error": "Invalid org_code"}'::JSONB;
        RETURN;
    END IF;

    -- Get the user_id from user_details using the org_id
    SELECT u.user_id INTO input_user_id
    FROM user_details u
    WHERE u.organization_id = org_id  -- Ensure the use of alias 'u.organization_id' to avoid ambiguity
    LIMIT 1;

    -- If no user found for the organization, return an error
    IF input_user_id IS NULL THEN
        result := '{"error": "No user found for the provided org_code"}'::JSONB;
        RETURN;
    END IF;

    -- Get the latest updated_at value from app_user_configurations for the user_id
    SELECT auc.updated_at INTO user_config_last_updated
    FROM app_user_configurations auc
    WHERE auc.user_id = input_user_id
    ORDER BY auc.updated_at DESC  -- Order by latest updated_at
    LIMIT 1;  -- Get the row with the latest updated_at

    -- If no last_updated_at value found, return an error
    IF user_config_last_updated IS NULL THEN
        result := '{"error": "No configuration found for the user"}'::JSONB;
        RETURN;
    END IF;

    -- Compare the provided last_updated_at with the value from the table
    -- DIRECTLY set result to true/false without nesting
IF input_last_updated_at < user_config_last_updated THEN
    result := 'true'::JSONB;
ELSE
    result := 'false'::JSONB;
END IF;

    
END;
$$;
