Angular Embedded AnalyticsUsing Data

import { Component } from "@angular/core";
import { AgStudio } from "ag-studio-angular";
import {
  AgDataEngine,
  AgDataSourcesDefinition,
  AgPanelConfig,
  AgReportState,
  AgStudioApi,
  AgStudioApiReadyEvent,
  AgStudioMode,
  AgStudioProperties,
  enableStudioDevValidations,
} from "ag-studio";
import { getMainDemoData } from "./data.ts";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableStudioDevValidations();
}

@Component({
  selector: "my-app",
  standalone: true,
  imports: [AgStudio],
  template: `<div style="display: flex; flex-direction: column; height: 100%">
    <ag-studio
      style="width: 100%; height: 100%;"
      class="my-studio-container"
      [initialState]="initialState"
      [mode]="mode"
      [panels]="panels"
      [data]="data"
    />
  </div> `,
})
export class AppComponent {
  initialState: AgReportState = {
    pages: [
      {
        id: "page1",
        widgets: {
          "1": {
            type: "grid",
            dataMapping: {
              cols: [
                { id: "stores.region" },
                { id: "line_net", aggregation: "avg" },
                { id: "line_net", aggregation: "sum" },
              ],
            },
            format: {
              title: {
                enabled: true,
                text: "Line Net by Region",
              },
            },
          },
          "2": {
            type: "grid",
            dataMapping: {
              cols: [
                { id: "customers.segment" },
                { id: "line_net", aggregation: "avg" },
                { id: "line_net", aggregation: "sum" },
              ],
            },
            format: {
              title: {
                enabled: true,
                text: "Line Net by Customer Segment",
              },
            },
          },
        },
        widgetLayout: {
          "1": {
            xTrack: 0,
            yTrack: 0,
            xSpan: 24,
            ySpan: 16,
          },
          "2": {
            xTrack: 0,
            yTrack: 16,
            xSpan: 24,
            ySpan: 16,
          },
        },
      },
    ],
    selectedPageId: "page1",
  };
  mode: AgStudioMode = "edit";
  panels: AgPanelConfig = {
    edit: {
      right: ["edit", "data"],
    },
  };
  data: AgDataSourcesDefinition | AgDataEngine = getMainDemoData(
    "https://www.ag-grid.com/studio/archive/3.0.0/example-assets",
  );
}

import '@angular/compiler';
import { provideHttpClient } from '@angular/common/http';
import { bootstrapApplication } from '@angular/platform-browser';

import { AppComponent } from './app.component.ts';

const app = bootstrapApplication(AppComponent, {
    providers: [provideHttpClient()],
});

import type { AgDataSourcesDefinition, AgExpressionFieldDefinition, AgFieldDefinition } from 'ag-studio';

// =============================================================================
// Field Definitions
// =============================================================================

const storesFields: AgFieldDefinition[] = [
    {
        id: 'store_id',
        name: 'Store ID',
        format: 'textFormat',
    },
    { id: 'store_name', name: 'Store', format: 'textFormat' },
    { id: 'region', name: 'Region', format: 'textFormat' },
    { id: 'city', name: 'City', format: 'textFormat' },
    {
        id: 'opened_date',
        name: 'Opened Date',
        format: 'dateFormat',
    },
    { id: 'store_type', name: 'Store Type', format: 'textFormat' },
];

const productsFields: AgFieldDefinition[] = [
    {
        id: 'product_id',
        name: 'Product ID',
        format: 'textFormat',
        hide: false,
    },
    {
        id: 'product_name',
        name: 'Product',
        format: 'textFormat',
    },
    { id: 'category', name: 'Category', format: 'textFormat' },
    {
        id: 'subcategory',
        name: 'Subcategory',
        format: 'textFormat',
    },
    { id: 'brand', name: 'Brand', format: 'textFormat' },
    { id: 'launch_date', name: 'Launch Date', format: 'dateFormat' },
    {
        id: 'list_price',
        name: 'List Price',
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
    },
    {
        id: 'unit_cost',
        name: 'Unit Cost',
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
    },
    {
        id: 'is_discontinued',
        name: 'Discontinued',
        format: 'booleanFormat',
    },
];

const customersFields: AgFieldDefinition[] = [
    {
        id: 'customer_id',
        name: 'Customer ID',
        format: 'textFormat',
        hide: false,
    },
    {
        id: 'customer_name',
        name: 'Customer',
        format: 'textFormat',
    },
    { id: 'signup_date', name: 'Signup Date', format: 'dateFormat' },
    { id: 'region', name: 'Region', format: 'textFormat' },
    { id: 'segment', name: 'Segment', format: 'textFormat' },
    { id: 'is_active', name: 'Active', format: 'booleanFormat' },
    {
        id: 'marketing_opt_in',
        name: 'Marketing Opt-in',
        format: 'booleanFormat',
    },
    {
        id: 'lifetime_orders',
        name: 'Lifetime Orders',
        format: 'integerFormat',
        formatOptions: { format: '#,##0' },
    },
];

const ordersFields: AgFieldDefinition[] = [
    {
        id: 'order_id',
        name: 'Order ID',
        format: 'textFormat',
        hide: false,
    },
    {
        id: 'customer_id',
        name: 'Customer ID',
        format: 'textFormat',
    },
    {
        id: 'store_id',
        name: 'Store ID',
        format: 'textFormat',
    },
    {
        id: 'order_datetime',
        name: 'Order Date/Time',
        format: 'dateTimeFormat',
    },
    { id: 'channel', name: 'Channel', format: 'textFormat' },
    { id: 'status', name: 'Status', format: 'textFormat' },
    {
        id: 'payment_method',
        name: 'Payment Method',
        format: 'textFormat',
    },
    {
        id: 'currency',
        name: 'Currency',
        format: 'textFormat',
        hide: false,
    },
    {
        id: 'promo_code',
        name: 'Promo Code',
        format: 'textFormat',
        hide: false,
    },
    { id: 'notes', name: 'Notes', format: 'textFormat', hide: false },
    {
        id: 'order_month',
        name: 'Order Month',
        format: 'textFormat',
        hide: false,
        accessor: (row: any) => {
            const d = new Date(row.order_datetime);
            if (Number.isNaN(d.getTime())) return null;
            return `${String(d.getUTCFullYear())}-${String(d.getUTCMonth() + 1).padStart(2, '0')}`;
        },
    },
];

const orderItemsFields: AgFieldDefinition[] = [
    {
        id: 'order_item_id',
        name: 'Order Item ID',
        format: 'textFormat',
    },
    {
        id: 'order_id',
        name: 'Order ID',
        format: 'textFormat',
    },
    {
        id: 'product_id',
        name: 'Product ID',
        format: 'textFormat',
    },
    {
        id: 'quantity',
        name: 'Qty',
        format: 'integerFormat',

        formatOptions: { format: '#,##0' },
    },
    {
        id: 'unit_price',
        name: 'Unit Price',
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
    },
    {
        id: 'discount_pct',
        name: 'Discount',
        format: 'percentageFormat',

        formatOptions: { format: '#,##0.0%' },
    },
    {
        id: 'tax_rate',
        name: 'Tax Rate',
        format: 'percentageFormat',

        formatOptions: { format: '#,##0%' },
    },
    { id: 'returned', name: 'Returned', format: 'booleanFormat' },
    {
        id: 'return_reason',
        name: 'Return Reason',
        format: 'textFormat',
    },
];

const shipmentsFields: AgFieldDefinition[] = [
    {
        id: 'shipment_id',
        name: 'Shipment ID',
        format: 'textFormat',
    },
    {
        id: 'order_id',
        name: 'Order ID',
        format: 'textFormat',
    },
    {
        id: 'ship_datetime',
        name: 'Shipped Date/Time',
        format: 'dateTimeFormat',
    },
    {
        id: 'delivery_datetime',
        name: 'Delivered Date/Time',
        format: 'dateTimeFormat',
    },
    { id: 'carrier', name: 'Carrier', format: 'textFormat' },
    { id: 'delayed', name: 'Delayed', format: 'booleanFormat' },
];

// =============================================================================
// JSON Loading & Per-Source Caches
// =============================================================================

async function loadJson(baseUrl: string, filename: string): Promise<any[]> {
    const url = `${baseUrl}/${filename}`;
    const response = await fetch(url);
    if (!response.ok) {
        console.error(`Failed to load ${filename}: ${response.status}`);
        return [];
    }
    const data = await response.json();
    return data;
}

// =============================================================================
// Data Parsing & Cached Loaders
// =============================================================================

const parseBool = (v: unknown): boolean | undefined =>
    v === true || v === 'True' ? true : v === false || v === 'False' ? false : undefined;

// Each loader caches its promise so the file is fetched and parsed at most once.

let storesCache: Promise<any[]> | null = null;
const getStores = (baseUrl: string) => (storesCache ??= loadJson(baseUrl, 'stores.json'));

let productsCache: Promise<any[]> | null = null;
const getProducts = (baseUrl: string) =>
    (productsCache ??= loadJson(baseUrl, 'products.json').then((rows) =>
        rows.map((row) => ({
            ...row,
            list_price: Number(row.list_price),
            unit_cost: Number(row.unit_cost),
            is_discontinued: parseBool(row.is_discontinued),
        }))
    ));

let customersCache: Promise<any[]> | null = null;
const getCustomers = (baseUrl: string) =>
    (customersCache ??= loadJson(baseUrl, 'customers.json').then((rows) =>
        rows.map((row) => ({
            ...row,
            lifetime_orders: row.lifetime_orders !== '' ? Number(row.lifetime_orders) : null,
            is_active: parseBool(row.is_active),
            marketing_opt_in: parseBool(row.marketing_opt_in),
        }))
    ));

let ordersCache: Promise<any[]> | null = null;
const getOrders = (baseUrl: string) => (ordersCache ??= loadJson(baseUrl, 'orders.json'));

let orderItemsCache: Promise<any[]> | null = null;
const getOrderItems = (baseUrl: string) =>
    (orderItemsCache ??= loadJson(baseUrl, 'order_items.json').then((rows) =>
        rows.map((row) => ({
            ...row,
            quantity: Number(row.quantity),
            unit_price: Number(row.unit_price),
            discount_pct: Number(row.discount_pct),
            tax_rate: Number(row.tax_rate),
            returned: parseBool(row.returned),
        }))
    ));

let shipmentsCache: Promise<any[]> | null = null;
const getShipments = (baseUrl: string) =>
    (shipmentsCache ??= loadJson(baseUrl, 'shipments.json').then((rows) =>
        rows.map((row) => {
            const ship_datetime = row.ship_datetime == null || row.ship_datetime === '' ? null : row.ship_datetime;
            const delivery_datetime =
                row.delivery_datetime == null || row.delivery_datetime === '' ? null : row.delivery_datetime;

            // Always guarantee boolean: default to false if not true.
            const delayed =
                row.delayed === true || row.delayed === 'True'
                    ? true
                    : row.delayed === false || row.delayed === 'False'
                      ? false
                      : false;

            return { ...row, ship_datetime, delivery_datetime, delayed };
        })
    ));

// =============================================================================
// Expressions (Calculated Columns & Measures)
// =============================================================================

const expressions: AgExpressionFieldDefinition[] = [
    // -------------------------------------------------------------------------
    // Pre-aggregation expressions (row-level calculations on order_items)
    // -------------------------------------------------------------------------

    // line_gross = quantity * unit_price
    {
        id: 'line_gross',
        isMeasure: false,
        name: 'Line Gross',
        hide: false,
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
        expression: {
            operator: 'multiply',
            inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
        },
    },

    // line_discount_amount = line_gross * discount_pct
    {
        id: 'line_discount_amount',
        isMeasure: false,
        name: 'Discount Amount',
        hide: false,
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
        expression: {
            operator: 'multiply',
            inputs: [
                { operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
                { id: 'order_items.discount_pct' },
            ],
        },
    },

    // line_net = line_gross - line_discount_amount
    {
        id: 'line_net',
        isMeasure: false,
        name: 'Line Net',
        hide: false,
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
        expression: {
            operator: 'subtract',
            inputs: [
                { operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }] },
                {
                    operator: 'multiply',
                    inputs: [
                        {
                            operator: 'multiply',
                            inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
                        },
                        { id: 'order_items.discount_pct' },
                    ],
                },
            ],
        },
    },

    // line_cogs = quantity * unit_cost (from products via join)
    {
        id: 'line_cogs',
        isMeasure: false,
        name: 'Line COGS',
        hide: false,
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
        expression: {
            operator: 'multiply',
            inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }],
        },
    },

    // line_margin = line_net - line_cogs
    {
        id: 'line_margin',
        isMeasure: false,
        name: 'Line Margin',
        hide: false,
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
        expression: {
            operator: 'subtract',
            inputs: [
                // line_net
                {
                    operator: 'subtract',
                    inputs: [
                        {
                            operator: 'multiply',
                            inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
                        },
                        {
                            operator: 'multiply',
                            inputs: [
                                {
                                    operator: 'multiply',
                                    inputs: [{ id: 'order_items.quantity' }, { id: 'order_items.unit_price' }],
                                },
                                { id: 'order_items.discount_pct' },
                            ],
                        },
                    ],
                },
                // line_cogs
                { operator: 'multiply', inputs: [{ id: 'order_items.quantity' }, { id: 'products.unit_cost' }] },
            ],
        },
    },

    // return_flag = returned IS TRUE (for filtering/counting)
    {
        id: 'return_flag',
        isMeasure: false,
        name: 'Return Flag',
        hide: true,
        expression: {
            operator: 'isTrue',
            inputs: [{ id: 'order_items.returned' }],
        },
    },

    // returned_line_flag = IF(return_flag, 1, 0) - numeric flag for summing
    {
        id: 'returned_line_flag',
        isMeasure: false,
        name: 'Returned Line Flag',
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'return_flag' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
        },
    },

    // returned_line_net = IF(return_flag, line_net, 0) - line net only for returned items
    {
        id: 'returned_line_net',
        isMeasure: false,
        name: 'Returned Line Net',
        hide: true,
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
        expression: {
            operator: 'if',
            inputs: [{ id: 'return_flag' }, { id: 'line_net' }, { type: 'number', value: 0 }],
        },
    },

    // returned_line_margin = IF(return_flag, line_margin, 0) - margin only for returned items
    {
        id: 'returned_line_margin',
        isMeasure: false,
        name: 'Returned Line Margin',
        hide: true,
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
        expression: {
            operator: 'if',
            inputs: [{ id: 'return_flag' }, { id: 'line_margin' }, { type: 'number', value: 0 }],
        },
    },

    // -------------------------------------------------------------------------
    // Post-aggregation expressions (measures for KPIs)
    // For now, use pre-agg expressions with aggregation in widgets directly
    // -------------------------------------------------------------------------

    // Gross Margin % = (SUM(line_net) - SUM(line_cogs)) / SUM(line_net)
    {
        id: 'gross_margin_pct',
        isMeasure: true,
        name: 'Gross Margin %',
        hide: false,
        expression: {
            operator: 'divide',
            inputs: [
                {
                    operator: 'subtract',
                    inputs: [
                        { id: 'line_net', aggregation: 'sum' },
                        { id: 'line_cogs', aggregation: 'sum' },
                    ],
                },
                { id: 'line_net', aggregation: 'sum' },
            ],
        },
        format: 'percentageFormat',
        formatOptions: { format: '#,##0.0%' },
    },

    // order_status_group = IF(status = "Processing", "Open", "Closed")
    {
        id: 'order_status_group',
        isMeasure: false,
        name: 'Status Group',
        expression: {
            operator: 'if',
            inputs: [
                { operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Processing' }] },
                { type: 'string', value: 'Open' },
                { type: 'string', value: 'Closed' },
            ],
        },
    },

    // is_closed = status IN ("Completed", "Returned", "Cancelled") via chained OR
    {
        id: 'is_closed',
        isMeasure: false,
        name: 'Is Closed',
        expression: {
            operator: 'or',
            inputs: [
                { operator: 'equals', inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Completed' }] },
                {
                    operator: 'or',
                    inputs: [
                        {
                            operator: 'equals',
                            inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Returned' }],
                        },
                        {
                            operator: 'equals',
                            inputs: [{ id: 'orders.status' }, { type: 'string', value: 'Cancelled' }],
                        },
                    ],
                },
            ],
        },
    },

    // ship_to_delivery_days = DATEDIFF(day, ship_datetime, delivery_datetime)
    {
        id: 'ship_to_delivery_days',
        isMeasure: false,
        name: 'Ship to Delivery Days',
        expression: {
            operator: 'datediff',
            inputs: [
                { type: 'string', value: 'day' },
                { id: 'shipments.ship_datetime' },
                { id: 'shipments.delivery_datetime' },
            ],
        },
    },

    // order_to_ship_hours = DATEDIFF(hour, order_datetime, ship_datetime)
    {
        id: 'order_to_ship_hours',
        isMeasure: false,
        name: 'Order to Ship Hours',
        expression: {
            operator: 'datediff',
            inputs: [
                { type: 'string', value: 'hour' },
                { id: 'orders.order_datetime' },
                { id: 'shipments.ship_datetime' },
            ],
        },
    },

    // is_shipped = ship_datetime IS NOT NULL
    {
        id: 'is_shipped',
        isMeasure: false,
        name: 'Is Shipped',
        hide: true,
        expression: {
            operator: 'isNotNull',
            inputs: [{ id: 'shipments.ship_datetime' }],
        },
    },

    // is_delivered = delivery_datetime IS NOT NULL
    {
        id: 'is_delivered',
        isMeasure: false,
        name: 'Is Delivered',
        hide: true,
        expression: {
            operator: 'isNotNull',
            inputs: [{ id: 'shipments.delivery_datetime' }],
        },
    },

    // is_on_time = IF(is_delivered, delayed = FALSE, FALSE)
    // Use IF+EQUALS to guarantee a boolean result.
    {
        id: 'is_on_time',
        isMeasure: false,
        name: 'Is On Time',
        hide: true,
        expression: {
            operator: 'if',
            inputs: [
                { id: 'is_delivered' },
                { operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: false }] },
                { type: 'boolean', value: false },
            ],
        },
    },

    // is_delayed = IF(is_delivered, delayed = TRUE, FALSE)
    // Use IF+EQUALS to guarantee a boolean result.
    {
        id: 'is_delayed',
        isMeasure: false,
        name: 'Is Delayed',
        hide: true,
        expression: {
            operator: 'if',
            inputs: [
                { id: 'is_delivered' },
                { operator: 'equals', inputs: [{ id: 'shipments.delayed' }, { type: 'boolean', value: true }] },
                { type: 'boolean', value: false },
            ],
        },
    },

    // delayed_shipments = IF(is_delayed, 1, 0) - numeric flag for stacking
    {
        id: 'delayed_shipments',
        isMeasure: false,
        name: 'Delayed Shipments',
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'is_delayed' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
        },
    },

    // on_time_shipments = IF(is_on_time, 1, 0) - numeric flag for stacking
    {
        id: 'on_time_shipments',
        isMeasure: false,
        name: 'On-time Shipments',
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'is_on_time' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
        },
    },

    // delivered_shipments = IF(is_delivered, 1, 0) - numeric flag for rate denominators
    {
        id: 'delivered_shipments',
        isMeasure: false,
        name: 'Delivered Shipments',
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'is_delivered' }, { type: 'number', value: 1 }, { type: 'number', value: 0 }],
        },
    },

    // delay_rate = SUM(delayed_shipments) / SUM(delivered_shipments)
    // Use delivered shipments as the denominator so pending (not delivered) rows don't dilute the rate.
    {
        id: 'delay_rate',
        isMeasure: true,
        name: 'Delay Rate',
        hide: false,
        expression: {
            operator: 'divide',
            inputs: [
                { id: 'delayed_shipments', aggregation: 'sum' },
                { id: 'delivered_shipments', aggregation: 'sum' },
            ],
        },
        format: 'percentageFormat',
        formatOptions: { format: '#,##0.0%' },
    },

    // shipped_order_id = IF(is_shipped, order_id, NULL)
    {
        id: 'shipped_order_id',
        isMeasure: false,
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'is_shipped' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
        },
    },

    // on_time_order_id = IF(is_on_time, order_id, NULL)
    {
        id: 'on_time_order_id',
        isMeasure: false,
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'is_on_time' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
        },
    },

    // delivered_order_id = IF(is_delivered, order_id, NULL)
    {
        id: 'delivered_order_id',
        isMeasure: false,
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'is_delivered' }, { id: 'shipments.order_id' }, { type: 'string', value: null }],
        },
    },

    // delivery_status = IF(NOT is_shipped, "Not shipped", IF(delayed = true, "Delayed", "On time"))
    {
        id: 'delivery_status',
        isMeasure: false,
        name: 'Delivery Status',
        expression: {
            operator: 'if',
            inputs: [
                { operator: 'not', inputs: [{ id: 'is_shipped' }] },
                { type: 'string', value: 'Not shipped' },
                {
                    operator: 'if',
                    inputs: [
                        { id: 'shipments.delayed' },
                        { type: 'string', value: 'Delayed' },
                        { type: 'string', value: 'On time' },
                    ],
                },
            ],
        },
    },

    // returned_order_id = IF(return_flag, order_id, NULL)
    // Used for counting orders that have at least one returned line.
    {
        id: 'returned_order_id',
        isMeasure: false,
        hide: true,
        expression: {
            operator: 'if',
            inputs: [{ id: 'return_flag' }, { id: 'order_items.order_id' }, { type: 'string', value: null }],
        },
    },

    // Aggregated calculations for KPIs (using pre-aggregation expressions as inputs)
    {
        id: 'net_sales',
        isMeasure: true,
        name: 'Net Sales',
        expression: {
            id: 'line_net',
            aggregation: 'sum',
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.0,,\\M',
        },
    },
    {
        id: 'gross_sales',
        isMeasure: true,
        name: 'Gross Sales',
        expression: {
            id: 'line_gross',
            aggregation: 'sum',
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.0,,\\M',
        },
    },
    {
        id: 'discount_amount',
        isMeasure: true,
        name: 'Discount Amount',
        expression: {
            id: 'line_discount_amount',
            aggregation: 'sum',
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.0,K',
        },
    },
    {
        id: 'COGS',
        isMeasure: true,
        name: 'COGS',
        expression: {
            id: 'line_cogs',
            aggregation: 'sum',
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.0,,\\M',
        },
    },
    {
        id: 'gross_margin',
        isMeasure: true,
        name: 'Gross Margin',
        expression: {
            id: 'line_margin',
            aggregation: 'sum',
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.0,,\\M',
        },
    },
    {
        id: 'gross_margin_percentage',
        isMeasure: true,
        name: 'Gross Margin %',
        expression: {
            operator: 'divide',
            inputs: [{ id: 'gross_margin' }, { id: 'net_sales' }],
        },
        format: 'percentageFormat',
        formatOptions: { format: '#,##0.0%' },
    },

    // --- Orders ---
    {
        id: 'order_count',
        isMeasure: true,
        name: 'Order Count',
        expression: {
            id: 'orders.order_id',
            aggregation: 'countd',
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0,K',
        },
    },
    {
        id: 'average_order_value',
        isMeasure: true,
        name: 'Average Order Value',
        expression: {
            operator: 'divide',
            inputs: [{ id: 'net_sales' }, { id: 'order_count' }],
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.00',
        },
    },
    {
        id: 'active_customers',
        isMeasure: true,
        name: 'Active Customers',
        expression: {
            id: 'orders.customer_id',
            aggregation: 'countd',
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0,K',
        },
    },

    // --- Returns ---
    {
        id: 'returned_lines',
        isMeasure: true,
        name: 'Returned Lines',
        expression: {
            id: 'returned_line_flag',
            aggregation: 'sum',
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0',
        },
    },
    {
        id: 'returned_orders',
        isMeasure: true,
        name: 'Returned Orders',
        expression: {
            id: 'returned_order_id',
            aggregation: 'countd',
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0',
        },
    },
    {
        id: 'return_rate',
        isMeasure: true,
        name: 'Return Rate (Lines)',
        expression: {
            operator: 'divide',
            inputs: [{ id: 'returned_lines' }, { id: 'order_items.order_item_id', aggregation: 'count' }],
        },
        format: 'percentageFormat',
        formatOptions: { format: '#,##0.0%' },
    },
    {
        id: 'return_value',
        isMeasure: true,
        name: 'Return Value',
        expression: {
            id: 'returned_line_net',
            aggregation: 'sum',
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.0,K',
        },
    },

    // return_margin_impact = -SUM(returned_line_margin)
    // Display as a negative number to represent margin lost due to returns.
    {
        id: 'return_margin_impact',
        isMeasure: true,
        name: 'Return Margin Impact',
        expression: {
            operator: 'subtract',
            inputs: [
                { type: 'number', value: 0 },
                { id: 'returned_line_margin', aggregation: 'sum' },
            ],
        },
        format: 'currencyFormat',
        formatOptions: {
            format: '£#,##0.0,K',
        },
    },

    // --- Delivery ---
    {
        id: 'shipped_orders',
        isMeasure: true,
        name: 'Shipped Orders',
        expression: {
            id: 'shipped_order_id',
            aggregation: 'countd',
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0,K',
        },
    },
    {
        id: 'delivered_orders',
        isMeasure: true,
        name: 'Delivered Orders',
        expression: {
            id: 'delivered_order_id',
            aggregation: 'countd',
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0,K',
        },
    },
    {
        id: 'delivered_not_returned_orders',
        isMeasure: true,
        name: 'Delivered (Not Returned)',
        expression: {
            operator: 'subtract',
            inputs: [{ id: 'delivered_orders' }, { id: 'returned_orders' }],
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0,K',
        },
    },
    {
        id: 'on_time_deliveries',
        isMeasure: true,
        name: 'On-time Deliveries',
        expression: {
            id: 'on_time_order_id',
            aggregation: 'countd',
        },
        format: 'integerFormat',
        formatOptions: {
            format: '#,##0,K',
        },
    },
    {
        id: 'on_time_rate',
        isMeasure: true,
        name: 'On-time Rate',
        expression: {
            operator: 'divide',
            inputs: [{ id: 'on_time_deliveries' }, { id: 'delivered_orders' }],
        },
        format: 'percentageFormat',
        formatOptions: { format: '#,##0.0%' },
    },
    {
        id: 'avg_ship_to_delivery_days',
        isMeasure: true,
        name: 'Avg Ship to Delivery Days',
        expression: {
            id: 'ship_to_delivery_days',
            aggregation: 'avg',
        },
        format: 'decimalFormat',
        formatOptions: { format: '#,##0.0' },
    },
];

// =============================================================================
// Data Source Definition
// =============================================================================

export function getMainDemoData(baseUrl: string): AgDataSourcesDefinition {
    const url = `${baseUrl}/main-demo`;
    return {
        sources: [
            {
                id: 'stores',
                name: 'Stores',
                dataShape: 'row',
                tables: [{ id: 'stores', name: 'Stores', fields: storesFields }],
                getData: async () => ({ data: await getStores(url) }),
            },
            {
                id: 'products',
                name: 'Products',
                dataShape: 'row',
                tables: [{ id: 'products', name: 'Products', fields: productsFields }],
                getData: async () => ({ data: await getProducts(url) }),
            },
            {
                id: 'customers',
                name: 'Customers',
                dataShape: 'row',
                tables: [{ id: 'customers', name: 'Customers', fields: customersFields }],
                getData: async () => ({ data: await getCustomers(url) }),
            },
            {
                id: 'orders',
                name: 'Orders',
                dataShape: 'row',
                tables: [{ id: 'orders', name: 'Orders', fields: ordersFields }],
                getData: async () => ({ data: await getOrders(url) }),
            },
            {
                id: 'order_items',
                name: 'Order Items',
                dataShape: 'row',
                tables: [{ id: 'order_items', name: 'Order Items', fields: orderItemsFields }],
                getData: async () => ({ data: await getOrderItems(url) }),
            },
            {
                id: 'shipments',
                name: 'Shipments',
                dataShape: 'row',
                tables: [{ id: 'shipments', name: 'Shipments', fields: shipmentsFields }],
                getData: async () => ({ data: await getShipments(url) }),
            },
        ],
        relationships: [
            {
                id: 'orders-customers',
                source: { tableId: 'orders', fieldId: 'customer_id' },
                target: { tableId: 'customers', fieldId: 'customer_id' },
                type: 'many-to-one',
            },
            {
                id: 'orders-stores',
                source: { tableId: 'orders', fieldId: 'store_id' },
                target: { tableId: 'stores', fieldId: 'store_id' },
                type: 'many-to-one',
            },
            {
                id: 'order_items-orders',
                source: { tableId: 'order_items', fieldId: 'order_id' },
                target: { tableId: 'orders', fieldId: 'order_id' },
                type: 'many-to-one',
            },
            {
                id: 'order_items-products',
                source: { tableId: 'order_items', fieldId: 'product_id' },
                target: { tableId: 'products', fieldId: 'product_id' },
                type: 'many-to-one',
                // line_cogs/line_margin multiply order_items.quantity (many-side, varies per row) by
                // products.unit_cost (one-side) before summing - grain-invariant-safe, not a real fan-out.
                acceptFanout: true,
            },
            {
                id: 'shipments-orders',
                source: { tableId: 'shipments', fieldId: 'order_id' },
                target: { tableId: 'orders', fieldId: 'order_id' },
                type: 'many-to-one',
            },
        ],
        expressions,
    };
}