-- PROCEDURE: public.get_key_menu_target(jsonb, jsonb)

-- DROP PROCEDURE IF EXISTS public.get_key_menu_target(jsonb, jsonb);

CREATE OR REPLACE PROCEDURE public.get_key_menu_target(
	IN json_input jsonb,
	INOUT result jsonb)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    v_company_id INT;
BEGIN
    v_company_id := COALESCE((json_input->>'company_id')::INT, 0);

    -- Initialize result
    IF result IS NULL THEN
        result := '[]'::jsonb;
    END IF;

    result := (
        SELECT jsonb_agg(
            jsonb_build_object(
                'key1', ek.key1,
                'is_target_keyword_updated', ek.is_target_keyword_updated,
                'status_id', ek.status_id,
                'target_keywords_embeddings', ek.target_keywords_embeddings,
                'language_content', (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'language_id', l.id,
                            'language_name', l.name,
                            'name_in_english', l.name_in_english,
                            'description', COALESCE(rke.target_keywords, NULL)
                        )
                        ORDER BY l.id ASC
                    )
                    FROM languages l
                    LEFT JOIN route_keywords_embeddings rke
                        ON rke.language_id = l.id
						AND ek.key1 = rke.target
                        AND rke.status_id = 1
                    WHERE
                        l.company_id = v_company_id
                        AND l.status_id = 1
                )
            )
            ORDER BY ek.key1 ASC
        )
        FROM (
    SELECT DISTINCT ON (key1) *
    FROM (
        -- embeddings FIRST (priority)
        SELECT 
            target AS key1,
            is_target_keyword_updated,
            status_id,
            target_keywords_embeddings
        FROM route_keywords_embeddings 
        WHERE company_id = v_company_id AND status_id = 1

        UNION ALL

        -- fallback ONLY if NOT EXISTS in embeddings
        SELECT 
            mi.target AS key1,
            NULL::INT,
            1,
            NULL::vector
        FROM menu_items mi
        WHERE company_id = v_company_id 
          AND status_id = 1
          AND NOT EXISTS (
              SELECT 1 
              FROM route_keywords_embeddings rke
              WHERE rke.target = mi.target
              AND rke.company_id = mi.company_id
              AND rke.status_id = 1
          )
    ) t
    ORDER BY key1
) ek
        WHERE ek.key1 IS NOT NULL
    );

END;
$BODY$;
ALTER PROCEDURE public.get_key_menu_target(jsonb, jsonb)
    OWNER TO postgres;

