-- Catering YTD product outlier report for Bidfood only
-- Top 20 product outliers by unit-to-sales ratio.
-- Units are derived from cases * units_per_case (no raw units rows for Bidfood).
-- Products with no units_per_case are excluded from unit calculations.

WITH params AS (
  SELECT 20::int AS top_n
),
latest_year AS (
  SELECT DATE_TRUNC('year', MAX(date))::date AS year_start
  FROM kp_columns
  WHERE fk_wholesaler = 6
    AND report_type = 'kp'
),
date_sets AS (
  SELECT
    'Bidfood'::text AS channel,
    6::int AS wholesaler_id,
    ARRAY(
      SELECT DISTINCT date
      FROM kp_columns
      WHERE fk_wholesaler = 6
        AND report_type = 'kp'
        AND date >= (SELECT year_start FROM latest_year)
      ORDER BY date ASC
    ) AS latest_dates
  FROM latest_year
),
product_rollup AS (
  SELECT
    ds.channel AS wholesaler,
    product.manufacturer,
    product.catering_seg AS segment,
    product.catering_brand AS brand,
    product.name AS product_name,
    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 = 'cases' AND product.units_per_case IS NOT NULL THEN trans.value * product.units_per_case END), 0), 2) AS units_ty,
    ROUND(COALESCE(SUM(CASE WHEN trans.unit_type = 'cases' THEN trans.value END), 0), 2) AS cases_ty,
    MAX(product.units_per_case) AS units_per_case
  FROM kp_columns trans
  JOIN date_sets ds ON ds.wholesaler_id = trans.fk_wholesaler
  JOIN public.products product ON trans.fk_product = product.id
  JOIN public.brands brand ON product.fk_brand = brand.id
  WHERE trans.date = ANY(ds.latest_dates)
    AND trans.unit_type IN ('sales', 'cases')
    AND NOT (trans.unit_type = 'cases' AND product.units_per_case IS NULL)
    AND trans.report_type = 'kp'
    AND product.catering_seg <> 'Exclude'
  GROUP BY
    ds.channel,
    product.manufacturer,
    product.catering_seg,
    product.catering_brand,
    product.name
),
ranked AS (
  SELECT
    wholesaler,
    manufacturer,
    segment,
    brand,
    product_name,
    sales_ty,
    units_ty,
    cases_ty,
    units_per_case,
    ROUND(units_ty / NULLIF(sales_ty, 0), 4) AS units_per_sales_ratio,
    ROW_NUMBER() OVER (
      ORDER BY
        units_ty / NULLIF(sales_ty, 0) DESC NULLS LAST,
        units_ty DESC,
        product_name
    ) AS rn
  FROM product_rollup
)
SELECT
  rn AS rank,
  wholesaler,
  manufacturer,
  segment,
  brand,
  product_name,
  sales_ty,
  units_ty,
  cases_ty,
  units_per_case,
  units_per_sales_ratio
FROM ranked
WHERE rn <= (SELECT top_n FROM params)
ORDER BY rn;