raw_sql_ecto.sql

WITH active_enforcement AS (
    SELECT DISTINCT ON (en.company_id, en.store_id, en.created_at)
           en.id,
           en.company_id,
           en.store_id,
           en.enforcement_statuses_id
    FROM enforcements en
    WHERE en.enforcement_statuses_id IN (1, 2, 3, 4)
    ORDER BY en.company_id, en.store_id, en.created_at DESC
),
active_test_purchase AS (
    SELECT DISTINCT ON (tp.company_id, tp.store_id, tp.created_at)
           tp.id,
           tp.company_id,
           tp.store_id,
           tp.status_id
    FROM test_purchases tp
    WHERE tp.status_id IN (1, 2)
    ORDER BY tp.company_id, tp.store_id, tp.created_at DESC
),
active_whitelisting AS (
    SELECT DISTINCT ON (wl.company_id, wl.store_id, wl.until)
           wl.id,
           wl.until,
           wl.company_id,
           wl.store_id
    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 AS store_id,
           available_stores.company_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
    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(m0.*)
--    m0.id,
--    m0.item_number,
--    m0.company_id,
--    m0.url,
--    m0.channel,
--    m0.product_name,
--    m0.price,
--    m0.currency_symbol,
--    m0.date_created,
--    m0.age,
--    m0.image_url,
--    m0.country_id,
--    m0.country_name,
--    m0.store_id,
--    m0.reseller_id,
--    m0.subdomain_id
    --s1.status AS store_status
FROM mv_listings_consolidated m0
INNER JOIN store_status_query s1
    ON m0.store_id = s1.store_id
    AND m0.company_id = s1.company_id;