apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_2025_monthly
  description: Total revenue per month 2025 in whole kEUR, actual (ac1) vs plan (pl1)
spec:
  dependencies: [sales_2025_csv]
  query: |
    -- kEUR in the data: ChartTime does not apply measureScale to variance bars
    -- rounded because ChartTime has no setting for label digits
    SELECT
      strftime(CAST(month AS DATE), '%Y-%m-%d') AS date,
      ROUND(SUM(revenue_actual_eur) / 1000.0) AS ac1,
      ROUND(SUM(revenue_plan_eur) / 1000.0) AS pl1
    FROM sales_2025_csv
    GROUP BY 1
    ORDER BY 1
---
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_2025_region
  description: Revenue 2025 per region in kEUR, actual (ac1) vs plan (pl1)
spec:
  dependencies: [sales_2025_csv]
  query: |
    SELECT
      region AS category,
      CASE region WHEN 'North' THEN 1 WHEN 'South' THEN 2 WHEN 'East' THEN 3
                  WHEN 'West' THEN 4 WHEN 'Export' THEN 5 END AS categoryIndex,
      SUM(revenue_actual_eur) / 1000.0 AS ac1,
      SUM(revenue_plan_eur) / 1000.0 AS pl1
    FROM sales_2025_csv
    GROUP BY region
    ORDER BY categoryIndex
---
apiVersion: bino.bi/v1alpha1
kind: DataSet
metadata:
  name: sales_2025_kpi
  description: One row of formatted key figures for the page title and commentary
spec:
  dependencies: [sales_2025_csv]
  query: |
    WITH r AS (
      SELECT region,
             SUM(revenue_actual_eur) AS ac,
             SUM(revenue_plan_eur) AS pl,
             SUM(revenue_actual_eur) - SUM(revenue_plan_eur) AS d
      FROM sales_2025_csv
      GROUP BY region
    ),
    t AS (SELECT SUM(ac) AS ac, SUM(pl) AS pl, SUM(d) AS d FROM r),
    h AS (
      SELECT
        SUM(CASE WHEN month(month) <= 6 THEN revenue_actual_eur - revenue_plan_eur END) AS d_h1,
        SUM(CASE WHEN month(month) > 6 THEN revenue_actual_eur - revenue_plan_eur END) AS d_h2
      FROM sales_2025_csv
    ),
    east_months AS (
      SELECT COUNT(*) FILTER (WHERE revenue_actual_eur < revenue_plan_eur) AS below
      FROM sales_2025_csv WHERE region = 'East'
    )
    SELECT
      printf('%.1f', t.ac / 1e6) AS ac_m,
      printf('%.1f', t.pl / 1e6) AS pl_m,
      printf('%+.0f', t.d / 1e3) AS d_k,
      printf('%+.1f', 100.0 * t.d / t.pl) AS dr_pct,
      (SELECT printf('%+.0f', d / 1e3) FROM r WHERE region = 'East') AS east_d_k,
      (SELECT printf('%+.1f', 100.0 * d / pl) FROM r WHERE region = 'East') AS east_dr_pct,
      (SELECT printf('%+.0f', d / 1e3) FROM r WHERE region = 'South') AS south_d_k,
      (SELECT printf('%+.0f', d / 1e3) FROM r WHERE region = 'Export') AS export_d_k,
      printf('%+.0f', h.d_h1 / 1e3) AS h1_d_k,
      printf('%+.0f', h.d_h2 / 1e3) AS h2_d_k,
      east_months.below AS east_months_below
    FROM t, h, east_months