WITH

-- find the id of the desired parent record if it exists
-- if it doesn't exist, generate a new id to be used in a subsequent creation query
NewOrExisting AS (
    SELECT id
    FROM omicron.public.address_lot
    WHERE (name, kind) = ($1, $2)
    UNION ALL
    SELECT gen_random_uuid()
    WHERE NOT EXISTS (
        SELECT 1
        FROM omicron.public.address_lot
        WHERE (name, kind) = ($1, $2)
    )
    LIMIT 1
),

-- if a parent record wasn't found, insert a new one
InsertedLot AS (
    INSERT INTO omicron.public.address_lot (id, name, kind)
    SELECT id, $1, $2
    FROM NewOrExisting
    WHERE NOT EXISTS (
        SELECT 1
        FROM omicron.public.address_lot
        WHERE (name, kind) = ($1, $2)
    )
    RETURNING id
),

-- accept arrays of parameters and unnest them, allowing us to create multiple child records
-- in a single query
PreparedBlocks AS (
    SELECT id, first_address, last_address
    FROM InsertedLot, UNNEST($3::inet[], $4::inet[]) AS params(first_address, last_address)
),

-- if any of the child records already exist, filter them from the PreparedBlocks
FilteredBlocks AS (
    SELECT *
    FROM PreparedBlocks pb
    WHERE NOT EXISTS (
        SELECT 1
        FROM omicron.public.address_lot_block alb
        WHERE alb.address_lot_id = pb.id
          AND alb.first_address = pb.first_address
          AND alb.last_address = pb.last_address
    )
)

-- insert the child records
INSERT INTO omicron.public.address_lot_block (address_lot_id, first_address, last_address)
SELECT id, first_address, last_address
FROM FilteredBlocks;