Export item pandas data for recommendations system

recommendation-item.sql

WITH
-- product with taxons
-- product_id, taxon_ids
-- 12611, 19|20
product_with_taxon_ids AS (

    SELECT
        pt.product_id,
        st.taxon_type,
        STRING_AGG( pt.taxon_id::varchar, '|'  ORDER BY pt.taxon_id ASC ) AS taxon_ids,
        STRING_AGG( ttran.name::varchar, '|'  ORDER BY pt.taxon_id ASC ) AS taxon_names

    FROM spree_products_taxons AS pt
    
    INNER JOIN spree_taxon_translations as ttran ON ttran.spree_taxon_id = pt.taxon_id
    INNER JOIN spree_taxons st ON st.id = pt.taxon_id
    
    WHERE ttran.locale = 'en'
    
    GROUP BY pt.product_id, st.taxon_type
),

date_in_50yrs AS (
    SELECT (extract(epoch FROM now()) * 1000)::BIGINT + (365::BIGINT * 50 * 24 * 3600 * 1000)
    as date_in_50yrs
),

available_products AS (
    SELECT p.id,
    SUM(s.backorderable::int) AS total_backorderable,
    SUM(s.count_on_hand) AS total_count_on_hand
    
    FROM spree_products p
    INNER JOIN spree_variants v ON p.id = v.product_id
    INNER JOIN spree_stock_items s on v.id = s.variant_id
    
    GROUP BY p.id
),

product_items AS (
    SELECT
        p.id,
        ptran.description,
        ptran.name,
        ptran.slug,
        pt.taxon_type,
        sp.amount,
        vd.vendor_type,
        p.vendor_id,
        pt.taxon_ids,
        pt.taxon_names,
        ap.total_backorderable,
        ap.total_count_on_hand,
        (extract(epoch FROM p.available_on ) * 1000)::BIGINT AS available_on,
        (extract(epoch FROM p.discontinue_on ) * 1000)::BIGINT AS discontinue_on,
        (extract(epoch FROM p.created_at ) * 1000)::BIGINT AS created_at


    FROM spree_products AS p

    INNER JOIN product_with_taxon_ids AS pt ON p.id = pt.product_id
    INNER JOIN spree_product_translations AS ptran ON ptran.spree_product_id = p.id
    INNER JOIN available_products AS ap ON ap.id = p.id
    INNER JOIN spree_variants AS v ON v.product_id = p.id
    INNER JOIN spree_prices AS sp ON sp.variant_id = v.id
    INNER JOIN spree_vendors as vd ON vd.id = p.vendor_id

    WHERE ptran.locale = 'en' AND sp.currency = 'USD' AND v.is_master = true
),

items AS (
SELECT
    p.id AS "ITEM_ID",
    p.name AS "NAME",
    p.slug AS "SLUG",
    p.amount AS "PRICE",
    -- regexp_replace(p.description, E'[\\n\\r]+', ' ', 'g' ) as "PRODUCT_DESCRIPTION",
    p.taxon_ids AS "CATEGORY_L1",
    p.taxon_names as "TAXON_NAMES",
    p.available_on AS "AVAILABLE_ON",
    p.created_at AS "CREATION_TIMESTAMP",
    p.vendor_type AS "VENDOR_TYPE",
    p.taxon_type AS "TAXON_TYPE",
    'none' AS "AGE_GROUP",
    'none' AS "ADULT",
    'none' AS "GENDER",
    
    CASE 
        WHEN p.vendor_id IS NULL
        THEN 0 ELSE p.vendor_id
    END "VENDOR_ID",

    -- p.total_backorderable AS "BACKORDERABLE",
    -- p.total_count_on_hand AS "COUNT_ON_HAND",
    CASE 
        WHEN total_backorderable + p.total_count_on_hand > 0 
        THEN 1 ELSE 0
    END "AVAILABILITY",
    
    -- p.discontinue_on AS "DISCONTINUE_ON"
    CASE 
        WHEN p.discontinue_on IS NULL 
        THEN d.date_in_50yrs ELSE p.discontinue_on
    END "DISCONTINUE_ON"
    
    FROM product_items as p, date_in_50yrs as d
    ORDER BY p.id
)

SELECT * FROM items