query_without_company_id.sql

WITH active_enforcement AS (
    SELECT DISTINCT ON (en.company_id, en.store_id, en.created_at)
           en.*
    FROM enforcements en
    WHERE en.enforcement_statuses_id IN (1, 2, 3, 4)
    ORDER BY en.created_at DESC
),
active_test_purchase AS (
    SELECT DISTINCT ON (tp.company_id, tp.store_id, tp.created_at)
           tp.*
    FROM test_purchases tp
    WHERE tp.status_id IN (1, 2)
    ORDER BY tp.created_at DESC
),
active_whitelisting AS (
    SELECT DISTINCT ON (wl.company_id, wl.store_id, wl.until)
           wl.*
    FROM whitelist wl
    WHERE wl.until >= CURRENT_DATE
),
available_stores_query AS (
    SELECT l.store_id, l.company_id
    FROM mv_listings_consolidated l
    GROUP BY l.store_id, l.company_id
),
store_status_query AS (
	SELECT DISTINCT store.id,
       CASE
           WHEN active_whitelisting.id IS NOT NULL THEN 'Allowed'
           WHEN active_enforcement.id IS NOT NULL OR active_test_purchase.id IS NOT NULL THEN 'Action in progress'
           ELSE 'Greylist'
       END AS status,
       available_stores.company_id
FROM stores store
JOIN available_stores_query available_stores
    ON available_stores.store_id = store.id
LEFT JOIN active_enforcement active_enforcement
    ON active_enforcement.company_id = available_stores.company_id
    AND active_enforcement.store_id = available_stores.store_id
LEFT JOIN active_test_purchase active_test_purchase
    ON active_test_purchase.company_id = available_stores.company_id
    AND active_test_purchase.store_id = available_stores.store_id
LEFT JOIN active_whitelisting active_whitelisting
    ON active_whitelisting.company_id = available_stores.company_id
    AND active_whitelisting.store_id = available_stores.store_id
)
SELECT count(listing.*)--, cs.status AS store_status
FROM mv_listings_consolidated listing
JOIN store_status_query cs
    ON listing.store_id = cs.id
    AND listing.company_id = cs.company_id;