CREATE OR REPLACE VIEW OCI_COMPUTE_CAPACITY_RESERVATIONS_VW AS
SELECT
    SRC.ID,
    SRC.COMPARTMENT_ID,
    SRC.DISPLAY_NAME,
    SRC.DEFINED_TAGS,
	SRC.FREEFORM_TAGS,
	SRC.LIFECYCLE_STATE,
	SRC.AVAILABILITY_DOMAIN,
	SRC.RESERVED_INSTANCE_COUNT,
	SRC.USED_INSTANCE_COUNT,
	SRC.IS_DEFAULT_RESERVATION,
	SRC.TIME_CREATED,	
	SRC.REGION,
	SRC.LAST_UPDATED,

    -- Fault Domain 1
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-1' THEN CFG.fault_domain END) AS FD1_NAME,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-1' THEN CFG.instance_shape END) AS FD1_INSTANCE_SHAPE,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-1' THEN CFG.ocpus END) AS FD1_OCPUS,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-1' THEN CFG.memory_in_gbs END) AS FD1_MEMORY_IN_GBS,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-1' THEN CFG.reserved_count END) AS FD1_RESERVED_COUNT,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-1' THEN CFG.used_count END) AS FD1_USED_COUNT,

    -- Fault Domain 2
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-2' THEN CFG.fault_domain END) AS FD2_NAME,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-2' THEN CFG.instance_shape END) AS FD2_INSTANCE_SHAPE,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-2' THEN CFG.ocpus END) AS FD2_OCPUS,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-2' THEN CFG.memory_in_gbs END) AS FD2_MEMORY_IN_GBS,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-2' THEN CFG.reserved_count END) AS FD2_RESERVED_COUNT,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-2' THEN CFG.used_count END) AS FD2_USED_COUNT,

    -- Fault Domain 3
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-3' THEN CFG.fault_domain END) AS FD3_NAME,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-3' THEN CFG.instance_shape END) AS FD3_INSTANCE_SHAPE,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-3' THEN CFG.ocpus END) AS FD3_OCPUS,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-3' THEN CFG.memory_in_gbs END) AS FD3_MEMORY_IN_GBS,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-3' THEN CFG.reserved_count END) AS FD3_RESERVED_COUNT,
    MAX(CASE WHEN CFG.fault_domain = 'FAULT-DOMAIN-3' THEN CFG.used_count END) AS FD3_USED_COUNT

FROM SRC_OCI_COMPUTE_CAPACITY_RESERVATIONS_TB SRC
-- Expand the JSON array into rows
LEFT JOIN
    JSON_TABLE(
        SRC.INSTANCE_RESERVATION_CONFIGS,
        '$[*]'
        COLUMNS (
            fault_domain VARCHAR2(32) PATH '$.fault_domain',
            instance_shape VARCHAR2(32) PATH '$.instance_shape',
            ocpus NUMBER PATH '$.instance_shape_config.ocpus',
            memory_in_gbs NUMBER PATH '$.instance_shape_config.memory_in_gbs',
            reserved_count NUMBER PATH '$.reserved_count',
            used_count NUMBER PATH '$.used_count'
        )
    ) CFG
ON 1=1
GROUP BY
    SRC.ID,
    SRC.COMPARTMENT_ID,
    SRC.DISPLAY_NAME,
    SRC.DEFINED_TAGS,
	SRC.FREEFORM_TAGS,
	SRC.LIFECYCLE_STATE,
	SRC.AVAILABILITY_DOMAIN,
	SRC.RESERVED_INSTANCE_COUNT,
	SRC.USED_INSTANCE_COUNT,
	SRC.IS_DEFAULT_RESERVATION,
	SRC.TIME_CREATED,	
	SRC.REGION,
	SRC.LAST_UPDATED
;
