CREATE FUNCTION public.list_container_by_parent(in_parent_id uuid, in_locale text, in_sort_key text, in_custom_init_key text) RETURNS SETOF public.node
    LANGUAGE sql STABLE COST 30 ROWS 20
    AS $$
-- Returns the child containers of the specified parent container in the
-- sort order specified by the sort key and locale.
-- If sort key is not specified, the sort key of the parent container,
-- or default for parent container type, is used.
-- If custom_init key is specified, containers that have this key set are
-- excluded from output. The value of the custom_init key doesn't matter.
WITH
container_default_sort_key(container_type, default_sort_key) AS (
    VALUES
        ('folder', 'index_asc'),
        ('album',  'index_asc'),
        ('blog',   'updated_desc')
),
parent_container AS (
    SELECT
     c.container_id,
     COALESCE(
        in_sort_key,
        c.sort_key,
        cdsk.default_sort_key
     ) AS parent_sort_key
    FROM container c
     JOIN container_default_sort_key cdsk USING(container_type)
    WHERE c.container_id = in_parent_id
),
child_containers AS (
    SELECT
     ct.child_container_id AS container_id,
     -- Only extract custom_init value if key was specified
     CASE
     WHEN in_custom_init_key IS NOT NULL
     THEN extract_custom_init_value(c.custom_init, in_custom_init_key)
     ELSE NULL
     END AS _extracted_custom_init_value
    FROM container_tree ct
     JOIN container c ON ct.child_container_id = c.container_id
     JOIN parent_container pc ON ct.parent_container_id = pc.container_id
    -- Sort based on specified or calculated sort key
    -- Using COALESCE(in_sort_key, ...) here ensures constant folding
    -- of the entire sort expression
    ORDER BY
      CASE COALESCE(in_sort_key, pc.parent_sort_key)
        WHEN 'index_asc'    THEN c.sort_number
        WHEN 'size_asc'     THEN c.sort_number
      END ASC NULLS LAST,
      CASE COALESCE(in_sort_key, pc.parent_sort_key)
        WHEN 'updated_asc'  THEN c.updated
        WHEN 'created_asc'  THEN c.created
      END ASC NULLS FIRST,
      CASE COALESCE(in_sort_key, pc.parent_sort_key)
        WHEN 'index_desc'    THEN c.sort_number
        WHEN 'size_desc'     THEN c.sort_number
      END DESC NULLS FIRST,
      CASE COALESCE(in_sort_key, pc.parent_sort_key)
        WHEN 'updated_desc'  THEN c.updated
        WHEN 'created_desc'  THEN c.created
      END DESC NULLS LAST,
      CASE COALESCE(in_sort_key, pc.parent_sort_key)
        WHEN 'index_asc'    THEN collkey(c.title, in_locale)
        WHEN 'title_asc'    THEN collkey(c.title, in_locale)
        WHEN 'size_asc'     THEN collkey(c.title, in_locale)
        WHEN 'updated_asc'  THEN collkey(c.title, in_locale)
        WHEN 'created_asc'  THEN collkey(c.title, in_locale)
        WHEN 'updated_desc' THEN collkey(c.title, in_locale)
        WHEN 'created_desc' THEN collkey(c.title, in_locale)
      END ASC,
      CASE COALESCE(in_sort_key, pc.parent_sort_key)
        WHEN 'index_desc' THEN collkey(c.title, in_locale)
        WHEN 'title_desc' THEN collkey(c.title, in_locale)
        WHEN 'size_desc'  THEN collkey(c.title, in_locale)
      END DESC
)
SELECT
 cc.container_id,
 'container'::text,
 row_number() OVER ()::integer,
 NULL::integer
FROM child_containers cc
-- Exclude containers where custom_init value is present
WHERE cc._extracted_custom_init_value IS NULL
$$;