-- create codeshares table
CREATE TABLE lh_operating_codeshares (
    operatingAirlineId UUID NOT NULL,
    operatingFlightNumber USMALLINT NOT NULL,
    operatingSuffix TEXT NOT NULL,
    departureAirportId UUID NOT NULL,
    departureDateLocal DATE NOT NULL,
    codeShares STRUCT(airline_id UUID, number USMALLINT, suffix TEXT)[] NOT NULL,
    CHECK ( TO_JSON(codeShares) = TO_JSON(LIST_SORT(LIST_DISTINCT(codeShares))) )
) ;

-- insert codeshares
INSERT INTO lh_operating_codeshares (
    operatingAirlineId,
    operatingFlightNumber,
    operatingSuffix,
    departureAirportId,
    departureDateLocal,
    codeShares
)
SELECT
    operatingAirlineId,
    operatingFlightNumber,
    operatingSuffix,
    departureAirportId,
    departureDateLocal,
    LIST_SORT(
        LIST_DISTINCT(
            COALESCE(
                ARRAY_AGG({
                    'airline_id': airlineId,
                    'number': flightNumber,
                    'suffix': suffix
                }) FILTER (
                    airlineId IS NOT NULL
                    AND ( airlineId != operatingAirlineId OR flightNumber != operatingFlightNumber OR suffix != operatingSuffix )
                ),
                []
            )
        )
    ) AS codeShares
FROM lh_all_flights_with_ids
GROUP BY
    operatingAirlineId,
    operatingFlightNumber,
    operatingSuffix,
    departureAirportId,
    departureDateLocal
;

FILTER (
                    airlineId IS NOT NULL
                    AND ( airlineId != operatingAirlineId OR flightNumber != operatingFlightNumber OR suffix != operatingSuffix )
                )

-- harden codeshares, for some reason the filter doesnt work on all platforms(?)
UPDATE lh_operating_codeshares
SET codeShares = LIST_SORT(LIST_DISTINCT(LIST_FILTER(
    codeShares,
    lambda cs: ( cs.airline_id != operatingAirlineId OR cs.number != operatingFlightNumber OR cs.suffix != operatingSuffix )
)))
WHERE LENGTH(LIST_FILTER(
    codeShares,
    lambda cs: ( cs.airline_id = operatingAirlineId AND cs.number = operatingFlightNumber AND cs.suffix = operatingSuffix )
)) > 0 ;

CREATE TABLE id_mapping (
  id UUID NOT NULL,
  child_id UUID NOT NULL
) ;

-- some initial data, mapping one id to 5 others
INSERT INTO id_mapping (id, child_id)
SELECT id, UNNEST(child_ids)
FROM (
  SELECT
    UUID() AS id,
    [UUID(), UUID(), UUID(), UUID(), UUID()] AS child_ids
  FROM generate_series(10000)
) ;

-- insert mappings from id to itself
INSERT INTO id_mapping (id, child_id)
SELECT id, id FROM id_mapping ;

-- each id should now have exactly 6 child_ids (unless the same uuid was generated twice somewhere)
SELECT MIN(c), MAX(c)
FROM (
  SELECT COUNT(DISTINCT child_id) AS c
  FROM id_mapping
  GROUP BY id
) ;

-- create the aggregated table
CREATE TABLE example AS
SELECT
  id,
  LIST_DISTINCT(
    COALESCE(
      ARRAY_AGG(child_id) FILTER ( child_id != id ),
      []
    )
  ) AS child_ids
FROM id_mapping
GROUP BY id ;

-- this query should return 0 rows, because:
-- (1) the aggregated list should never contain the id itself
-- (2) all values in the list should be unique
-- (3) there should be exactly 5 child_ids for every id
SELECT *
FROM example
WHERE LENGTH(LIST_FILTER(child_ids, lambda child_id: id = child_id)) > 0
OR LENGTH(child_ids) != LENGTH(LIST_DISTINCT(child_ids))
OR LENGTH(child_ids) != 5 ;