SELECT 
    soguid
FROM (
    SELECT 
        json_extract_scalar(message, '$.eventType') AS eventType,
        json_extract_scalar(message, '$.payload.result.reference') AS ref,
        json_extract_scalar(message, '$.eventVersion') AS eventVersion,
        json_extract_scalar(message, '$.payload.result.salesOrderVersionId') AS versionId,
        json_extract_scalar(message, '$.businessProcessId') AS bpid,
        json_extract_scalar(message, '$.payload.result.salesOrderGuid') AS soguid,
        json_extract_scalar(message, '$.payload.result.bookDate') AS bookDate,
        json_extract_scalar(message, '$.payload.result.state') AS state,
        json_extract_scalar(component, '$.sourcedComponent.category') AS category,
        json_extract_scalar(component, '$.sourcedComponent.instance') AS sourcedComponentInstance,
        json_extract_scalar(message, '$.payload.result.departureDate') AS departureDate,
        json_extract_scalar(message, '$.payload.result.salesParties[0].lastName') AS paxName,
        json_extract_scalar(externalReference, '$.identifier') AS pnr,
        ROW_NUMBER() OVER (PARTITION BY json_extract_scalar(message, '$.payload.result.reference') ORDER BY CAST(json_extract_scalar(message, '$.payload.result.salesOrderVersionId') AS integer) DESC) AS rn
    FROM "wr_prod_athenadb_eventarchive"."archive_query_prod"
    CROSS JOIN UNNEST(
        CAST(json_extract(message, '$.payload.result.reservations') AS array(json))
    ) AS t(reservation)
    CROSS JOIN UNNEST(
        CAST(json_extract(reservation, '$.components') AS array(json))
    ) AS y(component)
    CROSS JOIN UNNEST(
        CAST(json_extract(component, '$.sourcedComponent.externalReferences') AS array(json))
    )AS e(externalReference)
    WHERE json_extract_scalar(message, '$.eventType') = 'SalesOrderCommitted'
      AND json_extract_scalar(component, '$.sourcedComponent.category') = 'Flight'
      AND LENGTH(json_extract_scalar(component, '$.sourcedComponent.instance')) != 22
      AND day >= '2024/09/01'
      AND json_extract_scalar(message, '$.payload.result.salesParties[0].lastName') <> 'xxx'
      AND json_extract_scalar(message, '$.eventVersion') = '14.0'
      -- AND json_extract_scalar(message, '$.payload.result.state') <> 'cancelled'
      -- AND substr(json_extract_scalar(message, '$.payload.result.bookDate'), 1, 10) = '2024-09-04'
      and json_extract_scalar(externalReference, '$.kind') = 'pnr'
      --and json_extract_scalar(message, '$.payload.result.reference') = '200027799808'
) AS latest_version
WHERE rn = 1  -- Keep only the latest version (first row per ref)
--and departureDate in ('2024-09-04')
-- AND DATE_DIFF('day', CAST(departureDate AS date), DATE '2024-09-04') BETWEEN 0 AND 7 -- filter on next 7 days departure

ORDER BY
    departureDate, ref ASC
--filtered most duplicates out
--duplicates where there because of 
--2 legs with wrong instance_length -> solved this with 
--gdpr reduced event -> solved this with where clause
--amend done -> create 2nd version  -> need to filter this 1 out 
    ;