-- Bidfood (fk_wholesaler=6) and Booker (fk_wholesaler=4) KP products whose
-- names lack the pack-size pattern used by extractUnitsPerCaseFromProduct,
-- so no units row can be derived.
-- Matches PHP logic:
--   Bidfood style : \(\d+[xX]\d+   e.g. (1X24X40G)
--   Generic style : \d+\s*[xX]     e.g. 12 x 140g

WITH latest_year AS (
  SELECT DATE_TRUNC('year', MAX(date))::date AS year_start
  FROM kp_columns
  WHERE fk_wholesaler IN (4, 6)
    AND report_type = 'kp'
),
dates AS (
  SELECT ARRAY(
    SELECT DISTINCT date
    FROM kp_columns
    WHERE fk_wholesaler IN (4, 6)
      AND report_type = 'kp'
      AND date >= (SELECT year_start FROM latest_year)
    ORDER BY date ASC
  ) AS latest_dates
),
product_rollup AS (
  SELECT
    product.id                AS product_id,
    product.name              AS product_name,
    product.manufacturer,
    product.catering_seg      AS segment,
    product.catering_brand    AS brand,
    CASE trans.fk_wholesaler WHEN 6 THEN 'Bidfood' ELSE 'Booker' END AS wholesaler,
    ROUND(COALESCE(SUM(CASE WHEN trans.unit_type = 'sales' THEN trans.value END), 0), 2) AS sales_ty,
    ROUND(COALESCE(SUM(CASE WHEN trans.unit_type = 'units' THEN trans.value END), 0), 2) AS units_ty,
    ROUND(COALESCE(SUM(CASE WHEN trans.unit_type = 'cases' THEN trans.value END), 0), 2) AS cases_ty
  FROM kp_columns trans
  JOIN public.products product ON trans.fk_product = product.id
  CROSS JOIN dates d
  WHERE trans.fk_wholesaler IN (4, 6)
    AND trans.report_type = 'kp'
    AND trans.date = ANY(d.latest_dates)
    AND trans.unit_type IN ('sales', 'units', 'cases')
    AND product.catering_seg <> 'Exclude'
  GROUP BY
    product.id,
    product.name,
    product.manufacturer,
    product.catering_seg,
    product.catering_brand,
    trans.fk_wholesaler
)
SELECT
  wholesaler,
  product_id,
  product_name,
  manufacturer,
  segment,
  brand,
  sales_ty,
  units_ty,
  cases_ty
FROM product_rollup
WHERE cases_ty > 0
  AND product_name !~* '\(\d+[xX]\d+'   -- no Bidfood bracket pattern e.g. (1X24X40G)
  AND product_name !~* '\d+\s*[xX]'     -- no generic NxN pattern e.g. 12 x 140g
ORDER BY wholesaler, cases_ty DESC;
