Untitled

WITH dates AS (
  SELECT DATE, product_id, inventory_location_id
  FROM (
    SELECT product_id, inventory_location_id, GENERATE_DATE_ARRAY(MIN(DATE), CURRENT_DATE()-1) dates
    FROM `dw.product_inventory_snapshot_fact`
    GROUP BY product_id, inventory_location_id
  ), UNNEST(dates) DATE
)

SELECT
  dates.date,
  t2.forecast_product_id,
  t2.forecast_product_name,
  case when max(ifnull(t1.inventory,0)) > 0 then 0 else 1 end as days_out_stock,
  case when dates.DATE >= t3.stock_start and dates.DATE < t3.stock_end then 1 else 0 end as is_new

FROM dates

LEFT JOIN `dw.product_inventory_snapshot_fact` t1
USING(DATE, product_id, inventory_location_id)


--parent and child reference
LEFT JOIN (SELECT
  pd.product_id, 
  pd.product_name, 
  COALESCE(fp.forecast_product_id, pd1.product_id, pd.product_id) AS forecast_product_id,
  COALESCE(fp.forecast_product_name, pd1.product_name, pd.product_name) AS forecast_product_name,
  pd.operating_category,
  pd.spare_part,
  pd.clearance,
  pd.archived,
  pd.can_be_purchased,
  pd.relationship_code
  FROM `dw.product_dimension` pd
LEFT JOIN `forecasting.forecast_products` fp ON pd.product_id = fp.forecast_product_id
LEFT JOIN `dw.bom_dimension` bd
  ON fp.forecast_product_id IS NULL
  AND bd.product_id = pd.product_id
  AND bd.type IN UNNEST(['normal', 'phantom'])
LEFT JOIN `dw.bom_component_dimension` bcd USING (bom_id)
LEFT JOIN `dw.product_dimension` pd1 ON bcd.component_product_id = pd1.product_id) t2 on t2.product_id = dates.product_id
LEFT JOIN `imports.odoo_export_product_sales_channel_listing_data` scd on dates.product_id = substr(scd.product_id.name,2,5)

LEFT JOIN (--subquery to pull min PO receipt date per odoo id
  SELECT
    SUBSTR(pol.product_id.name,2,5) AS Product_ID,
    smd.min_rdate AS first_PO_receipt,
    DATE_ADD(DATE_TRUNC(smd.min_rdate,MONTH),INTERVAL 1 MONTH) AS stock_start,
    DATE_ADD(DATE_TRUNC(smd.min_rdate,MONTH),INTERVAL 13 MONTH) AS stock_end
  FROM
    `imports.odoo_export_purchase_order_line_data` pol
  LEFT JOIN
    `imports.odoo_export_purchase_order_data` poh
  ON
    poh.display_name = pol.order_id.name
  LEFT JOIN
    `dw.product_dimension` pd
  ON
    SUBSTR(pol.product_id.name,2,5) = pd.product_id
  LEFT JOIN (
    SELECT
      SUBSTR(product_id.name,2,5) AS Odoo_id,
      MIN(DATE(TIMESTAMP_MILLIS(date))) AS min_rdate
    FROM
      `spire-production.imports.odoo_export_stock_move_data`
    WHERE
      state = 'done'
      AND origin LIKE 'PO%'
    GROUP BY
      1 ) smd
  ON
    SUBSTR(pol.product_id.name,2,5) = smd.Odoo_id
  WHERE
    poh.picking_type_id.name NOT IN ('Dropship')
    AND poh.order_status NOT IN ('cancelled')
    AND (poh.payment_term_id.name IS NULL
      OR poh.payment_term_id.name NOT IN ('Blanket'))
    AND pd.product_series_name NOT IN ('Service')
    AND pd.operating_category IN ('Spire')
  GROUP BY
    1,
    2,
    3 ) t3 on t2.forecast_product_id = t3.Product_ID

where case
when scd.sales_channel in ('amazon_fba_us') and scd.status in ('active') then dates.inventory_location_id = 31
end
and t2.operating_category = 'Spire'
and t2.spare_part = false
and t2.clearance = false
and t2.archived = false
and date_trunc(dates.date, MONTH) >= DATE_ADD(current_date(), INTERVAL -18 MONTH) 


GROUP BY 1,2,3,5
order by 2 desc