-- Catering wholesalers MAT TY/LY sales and units comparison
-- Plain PostgreSQL version: MAT uses a 52-week window.

WITH params AS (
  SELECT 52::int AS weeks
),
report_wholesalers AS (
  SELECT *
  FROM (VALUES
    ('Booker C&C Catering', 4),
    ('Bidfood', 6),
    ('Brakes', 7)
  ) AS v(channel, wholesaler_id)
),
date_sets AS (
  SELECT
    rw.channel,
    rw.wholesaler_id,
    ARRAY(
      SELECT d.date
      FROM (
        SELECT DISTINCT date
        FROM kp_columns
        WHERE fk_wholesaler = rw.wholesaler_id
          AND report_type = 'kp'
        ORDER BY date DESC
        LIMIT (SELECT weeks FROM params)
      ) d
    ) AS latest_dates,
    ARRAY(
      SELECT d.date
      FROM (
        SELECT DISTINCT date
        FROM kp_columns
        WHERE fk_wholesaler = rw.wholesaler_id
          AND report_type = 'kp'
        ORDER BY date DESC
        OFFSET 52
        LIMIT (SELECT weeks FROM params)
      ) d
    ) AS previous_dates
  FROM report_wholesalers rw
),
catering_union AS (
  SELECT
    unit_type,
    ds.channel AS wholesaler,
    SUM(trans.value) AS ty_value,
    0::numeric AS ly_value,
    0::bigint AS product_count_ty,
    0::bigint AS product_count_ly
  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 unit_type IN ('sales','units')
    AND trans.report_type = 'kp'
    AND product.catering_seg <> 'Exclude'
  GROUP BY ds.channel, unit_type

  UNION ALL

  SELECT
    unit_type,
    ds.channel AS wholesaler,
    0::numeric AS ty_value,
    SUM(trans.value) AS ly_value,
    0::bigint AS product_count_ty,
    0::bigint AS product_count_ly
  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.previous_dates)
    AND unit_type IN ('sales','units')
    AND trans.report_type = 'kp'
    AND product.catering_seg <> 'Exclude'
  GROUP BY ds.channel, unit_type
),
product_totals AS (
  SELECT
    ds.channel AS wholesaler,
    COUNT(DISTINCT trans.fk_product) FILTER (WHERE trans.date = ANY(ds.latest_dates)) AS product_count_ty,
    COUNT(DISTINCT trans.fk_product) FILTER (WHERE trans.date = ANY(ds.previous_dates)) AS product_count_ly,
    COUNT(*) FILTER (WHERE trans.date = ANY(ds.latest_dates)) AS products_total_ty,
    COUNT(*) FILTER (WHERE trans.date = ANY(ds.previous_dates)) AS products_total_ly
  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
  WHERE trans.unit_type IN ('sales', 'units')
    AND trans.report_type = 'kp'
    AND product.catering_seg <> 'Exclude'
  GROUP BY ds.channel
)
SELECT
  wholesaler,
  ROUND(COALESCE(SUM(ty_value) FILTER (WHERE unit_type = 'sales'), 0), 2) AS sales_ty_unit,
  ROUND(COALESCE(SUM(ly_value) FILTER (WHERE unit_type = 'sales'), 0), 2) AS sales_ly_unit,
  ROUND(COALESCE(SUM(ty_value) FILTER (WHERE unit_type = 'units'), 0), 2) AS units_ty,
  ROUND(COALESCE(SUM(ly_value) FILTER (WHERE unit_type = 'units'), 0), 2) AS units_ly,
  COALESCE(MAX(product_totals.product_count_ty), 0) AS products_distinct_ty,
  COALESCE(MAX(product_totals.product_count_ly), 0) AS products_distinct_ly,
  COALESCE(MAX(product_totals.products_total_ty), 0) AS products_total_ty,
  COALESCE(MAX(product_totals.products_total_ly), 0) AS products_total_ly
FROM catering_union
JOIN product_totals USING (wholesaler)
GROUP BY wholesaler
ORDER BY wholesaler;
