/*
For a given id there are multiple linked values of differing types.
Types "A" and "B" are important and, if present, should be explicitly assigned.
It is possible that more than one link is associated with a given type.
If either A or B is lacking an explicit value it is assigned a value
from:
1. any extra As that are present
2. any extra Bs that are present
3. any extra non-A/B values that are present

The final result contains values for A and B and
and array of values for whatever links went unused.
*/
WITH demo AS (
    -- A sample record where B needs to be assigned and ends
    -- up using the excess A
    SELECT * FROM (
    VALUES (1,1,'A'), (1,2,'A'), (1,3,'C'), (1,4,'D')
    ) vals (id, link, type)
)
, link_allocation_1 AS (
    SELECT id,

    -- Grab the first A
    (SELECT link
       FROM demo
      WHERE demo.id = master.id
        AND type = 'A' LIMIT 1
    )                                             AS type_a_first_link,

    -- Grab the first B
    (SELECT link
       FROM demo
      WHERE demo.id = master.id
        AND type = 'B' LIMIT 1
    )                                             AS type_b_first_link,

    -- Any additional As and Bs are placed into an array
    -- and appended to an array constructed from all of the non-A/B
    ARRAY(
        SELECT link
          FROM demo
         WHERE demo.id = master.id
           AND type = 'A'
      ORDER BY link
        OFFSET 1) ||
    ARRAY(
        SELECT link
          FROM demo
         WHERE demo.id = master.id
           AND type = 'B'
      ORDER BY link
        OFFSET 1) ||
    ARRAY(
        SELECT link
          FROM demo
         WHERE demo.id = master.id
           AND type NOT  IN ('A','B')
      ORDER BY link)                              AS unassigned_links
    FROM (SELECT DISTINCT id FROM demo) master
)
, allocate_unassigned_links AS (
    SELECT *,
    -- Determine how many allocations from the "extra" array are required
    -- so that we can trim slice them out of the final result
        CASE WHEN type_a_first_link IS NULL
             THEN 1
             ELSE 0 END +
        CASE WHEN type_b_first_link IS NULL
             THEN 1
             ELSE 0
        END                                   AS reassign_count,
    -- A always gets the first extra if needed
        CASE WHEN type_a_first_link IS NULL
            THEN unassigned_links[1]
            ELSE type_a_first_link
        END                                   AS actual_a_link,
    -- B gets the first extra unless A took it in which case it gets the second one
        CASE WHEN type_b_first_link IS NULL THEN
             CASE WHEN type_a_first_link IS NOT NULL
                  THEN unassigned_links[1]
                  ELSE unassigned_links[2]
             END
             ELSE type_b_first_link
        END                                          AS actual_b_link
    FROM link_allocation_1
)
SELECT id,
-- For A and B flag is the value was pulled from the extras
       type_a_first_link IS NULL AS a_link_is_missing,
       actual_a_link,
       type_b_first_link IS NULL AS b_link_is_missing,
       actual_b_link,
-- Now slice off the first portion of the extras array based upon the assignment count
       unassigned_links[1+reassign_count:array_length(unassigned_links,1)] AS final_unassigned_links,
-- output the unsliced array for visual comparison
       unassigned_links AS pre_allocation_unassigned_links
  FROM allocate_unassigned_links