WITH FAC AS 
(

		WITH LATEST AS (   -- last metrics day per facility
		    SELECT U.FACILITY_ID
		         , MAX(UM.TIME::DATE) AS SNAPSHOT_DATE
		    FROM UNITS U
		    JOIN UNIT_METRICS UM ON U.ID = UM.UNIT_ID
		    GROUP BY U.FACILITY_ID
		),
		
		FAC_METS AS (
		    SELECT F.CALL_ID
		         , F.ID                  AS FACILITY_ID
		         , F.NAME                AS FACILITY_NAME
		         , F.ACQUIRED_AT::DATE   AS FACILITY_ACQUIRED_AT
		         , F.SOLD_ON::DATE       AS FACILITY_SOLD_ON
				 , F.LIVE 				 AS FACILITY_LIVE 
		         , L.SNAPSHOT_DATE
		         , COUNT(UM.ID)          AS UNIT_COUNT
		         , CASE WHEN L.SNAPSHOT_DATE = CURRENT_DATE - 1
		                THEN 'ACTIVE' ELSE 'HISTORICAL' END AS FACILITY_STATUS
		    FROM FACILITIES F
		    LEFT JOIN LATEST L        ON F.ID = L.FACILITY_ID
		    LEFT JOIN UNITS U         ON F.ID = U.FACILITY_ID
		    LEFT JOIN UNIT_METRICS UM ON U.ID = UM.UNIT_ID
		                             AND UM.TIME::DATE = L.SNAPSHOT_DATE
		    GROUP BY F.CALL_ID, F.ID, F.NAME, F.ACQUIRED_AT, F.SOLD_ON, L.SNAPSHOT_DATE
		)
		
		SELECT * FROM FAC_METS
		
		WHERE CALL_ID IS NOT NULL
			AND FACILITY_NAME NOT ILIKE '%DELETION%'
			AND FACILITY_NAME NOT LIKE 'ZZZKO Storage%' 				
			AND FACILITY_NAME NOT LIKE 'KO Storage - Training DB%'
)

, AI_FAC AS (	
SELECT	
    location_id,	
    voice_ai_platform,	
    ai_enablement_date	
FROM (VALUES	
    -- Uniti: Round 1	
    ('11273', 'Uniti', DATE '2026-03-25'),	
    ('11448', 'Uniti', DATE '2026-03-25'),	
    ('11334', 'Uniti', DATE '2026-03-25'),	
	
    -- Uniti: Round 2	
    ('15373', 'Uniti', DATE '2026-06-09'),	
    ('11399', 'Uniti', DATE '2026-06-09'),	
    ('11418', 'Uniti', DATE '2026-06-09'),	
    ('17412', 'Uniti', DATE '2026-06-09'),	
    ('11300', 'Uniti', DATE '2026-06-09'),	
    ('11395', 'Uniti', DATE '2026-06-09'),	
    ('11317', 'Uniti', DATE '2026-06-09'),	
    ('11472', 'Uniti', DATE '2026-06-09'),	
    ('11376', 'Uniti', DATE '2026-06-09'),	
    ('11312', 'Uniti', DATE '2026-06-09'),	
    ('11428', 'Uniti', DATE '2026-06-09'),	
    ('11298', 'Uniti', DATE '2026-06-09'),	
	
    -- Lumio: Round 1	
    ('11311', 'Lumio', DATE '2026-03-11'),	
    ('11346', 'Lumio', DATE '2026-03-11'),	
    ('11406', 'Lumio', DATE '2026-03-11'),	
    ('11325', 'Lumio', DATE '2026-03-11'),	
    ('11359', 'Lumio', DATE '2026-03-11'),	
	
    -- Lumio: Round 2	
    ('11463', 'Lumio', DATE '2026-06-12'),	
    ('13668', 'Lumio', DATE '2026-06-12'),	
    ('17409', 'Lumio', DATE '2026-06-12'),	
    ('11424', 'Lumio', DATE '2026-06-12'),	
    ('11293', 'Lumio', DATE '2026-06-12'),	
    ('17425', 'Lumio', DATE '2026-06-12'),	
    ('11388', 'Lumio', DATE '2026-06-12'),	
    ('12440', 'Lumio', DATE '2026-06-12'),	
    ('11464', 'Lumio', DATE '2026-06-12'),	
    ('17430', 'Lumio', DATE '2026-06-12')	
) AS t(location_id, voice_ai_platform, ai_enablement_date)	
)	
	
SELECT FAC.*
	, COALESCE(AI_FAC.VOICE_AI_PLATFORM,'CONTROL') AS VOICE_AI_PLATFORM
	, AI_FAC.AI_ENABLEMENT_DATE
FROM FAC	
LEFT JOIN AI_FAC	
	ON FAC.CALL_ID = AI_FAC.LOCATION_ID