<?php
// ============================================================
//  MediCore — Dynamic Reporting Engine
//  File: includes/reporting.php
//
//  Lets an admin build arbitrary reports (pick a data source, pick
//  columns OR a group-by + aggregate combo, add build-time filters,
//  choose a sort/limit, optionally a chart) from a whitelisted
//  registry of tables/fields — never from raw user-supplied SQL.
//  Saved definitions live in the `custom_reports` table as JSON and
//  are re-compiled into SQL on every view via run_custom_report().
// ============================================================

// ── Schema (idempotent, safe on every request via a static guard) ──
function ensure_reporting_schema(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        db_run("CREATE TABLE IF NOT EXISTS custom_reports (
            id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name         VARCHAR(150) NOT NULL,
            icon         VARCHAR(10)  DEFAULT '📊',
            description  VARCHAR(255) DEFAULT '',
            data_source  VARCHAR(50)  NOT NULL,
            definition   TEXT         NOT NULL,
            is_active    TINYINT(1)   DEFAULT 1,
            sort_order   INT          DEFAULT 0,
            created_by   INT UNSIGNED DEFAULT NULL,
            created_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            updated_at   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB");

        // Seed a handful of ready-made starter reports the first time
        // this table is created, so the feature isn't an empty shell.
        // Admins can edit, duplicate, or delete any of these freely —
        // they're just regular rows, nothing hardcoded elsewhere.
        $seeded = (int) db_val("SELECT COUNT(*) FROM custom_reports");
        if ($seeded === 0) {
            seed_default_reports();
        }
    } catch (Throwable $e) {
        error_log('ensure_reporting_schema failed: ' . $e->getMessage());
    }
}

function seed_default_reports(): void {
    $defaults = [
        [
            'name' => 'Top Selling Items', 'icon' => '🏆',
            'description' => 'Best-selling products by quantity and revenue in the selected period.',
            'data_source' => 'sale_items',
            'definition' => [
                'group_by'   => 'product_name',
                'aggregates' => [
                    ['field' => 'qty',     'fn' => 'SUM'],
                    ['field' => 'revenue', 'fn' => 'SUM'],
                    ['field' => 'profit',  'fn' => 'SUM'],
                ],
                'filters'    => [],
                'sort_field' => 'qty_sum', 'sort_dir' => 'DESC',
                'limit_n'    => 20,
                'chart_type' => 'bar',
            ],
        ],
        [
            'name' => 'Monthly Sales Trend', 'icon' => '📈',
            'description' => 'Revenue and profit trend by month.',
            'data_source' => 'sales',
            'definition' => [
                'group_by'   => 'month',
                'aggregates' => [
                    ['field' => 'final_price',   'fn' => 'SUM'],
                    ['field' => 'approx_profit', 'fn' => 'SUM'],
                ],
                'filters'    => [],
                'sort_field' => 'month', 'sort_dir' => 'ASC',
                'limit_n'    => null,
                'chart_type' => 'line',
            ],
        ],
        [
            'name' => 'Sales by Company', 'icon' => '🏭',
            'description' => 'Revenue and profit grouped by product company/manufacturer.',
            'data_source' => 'sale_items',
            'definition' => [
                'group_by'   => 'company_name',
                'aggregates' => [
                    ['field' => 'revenue', 'fn' => 'SUM'],
                    ['field' => 'profit',  'fn' => 'SUM'],
                ],
                'filters'    => [],
                'sort_field' => 'revenue_sum', 'sort_dir' => 'DESC',
                'limit_n'    => null,
                'chart_type' => 'bar',
            ],
        ],
        [
            'name' => 'Stock Value by Company', 'icon' => '📦',
            'description' => 'Current stock quantity and value grouped by company.',
            'data_source' => 'products',
            'definition' => [
                'group_by'   => 'company_name',
                'aggregates' => [
                    ['field' => 'stock_pcs',   'fn' => 'SUM'],
                    ['field' => 'stock_value', 'fn' => 'SUM'],
                ],
                'filters'    => [],
                'sort_field' => 'stock_value_sum', 'sort_dir' => 'DESC',
                'limit_n'    => null,
                'chart_type' => 'bar',
            ],
        ],
        [
            'name' => 'Low Stock Items', 'icon' => '⚠',
            'description' => 'Active products at or below 10 pieces in stock.',
            'data_source' => 'products',
            'definition' => [
                'columns'    => ['name', 'company_name', 'stock_pcs', 'sale_price', 'stock_value'],
                'filters'    => [['field' => 'stock_pcs', 'op' => '<=', 'value' => '10']],
                'sort_field' => 'stock_pcs', 'sort_dir' => 'ASC',
                'limit_n'    => 100,
                'chart_type' => 'none',
            ],
        ],
        [
            'name' => 'Top Customers', 'icon' => '👤',
            'description' => 'Customers ranked by total purchase value in the selected period.',
            'data_source' => 'sales',
            'definition' => [
                'group_by'   => 'customer_name',
                'aggregates' => [
                    ['field' => 'final_price', 'fn' => 'SUM'],
                    ['field' => 'due',         'fn' => 'SUM'],
                ],
                'filters'    => [],
                'sort_field' => 'final_price_sum', 'sort_dir' => 'DESC',
                'limit_n'    => 20,
                'chart_type' => 'bar',
            ],
        ],
        [
            'name' => 'Staff Activity Summary', 'icon' => '🧑‍💼',
            'description' => 'All-time sales, purchases, and expenses recorded by each staff member.',
            'data_source' => 'users',
            'definition' => [
                'columns'    => ['name', 'role', 'sales_count', 'sales_amount', 'purchases_count', 'purchases_amount', 'expenses_count', 'expenses_amount'],
                'filters'    => [],
                'sort_field' => 'sales_amount', 'sort_dir' => 'DESC',
                'limit_n'    => null,
                'chart_type' => 'none',
            ],
        ],
    ];

    foreach ($defaults as $i => $d) {
        db_run(
            "INSERT INTO custom_reports (name, icon, description, data_source, definition, sort_order)
             VALUES (?,?,?,?,?,?)",
            [$d['name'], $d['icon'], $d['description'], $d['data_source'], json_encode($d['definition']), $i]
        );
    }
}

// ── Whitelisted data-source registry ────────────────────────────
// This is the ONLY place SQL fragments come from. Report definitions
// (built via the UI, stored as JSON) may only ever reference field
// KEYS from here — never raw expressions — so no amount of admin
// tinkering in the builder UI can produce arbitrary SQL. Values are
// always bound as parameters; the only things that get string-
// concatenated into a query are hardcoded `expr` strings below,
// whitelisted aggregate functions (SUM/AVG/COUNT/MIN/MAX), and
// whitelisted comparison operators.
function reporting_registry(): array {
    return [
        'sales' => [
            'label'      => '🧾 Sales (Invoices)',
            'from'       => 'sales s',
            'joins'      => ['LEFT JOIN users u ON u.id = s.created_by'],
            'base_where' => ['s.is_deleted = 0'],
            'date_field' => 'date',
            'fields'     => [
                'invoice'       => ['label' => 'Invoice #',  'expr' => 's.invoice',       'type' => 'text'],
                'date'          => ['label' => 'Date',       'expr' => 's.date',          'type' => 'date'],
                'month'         => ['label' => 'Month',      'expr' => "DATE_FORMAT(s.date,'%Y-%m')", 'type' => 'text', 'groupable' => true],
                'customer_name' => ['label' => 'Customer',   'expr' => 's.customer_name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'final_price'   => ['label' => 'Total Amount','expr' => 's.final_price',  'type' => 'currency', 'aggregatable' => true],
                'paid'          => ['label' => 'Paid',       'expr' => 's.paid',          'type' => 'currency', 'aggregatable' => true],
                'due'           => ['label' => 'Due',        'expr' => 's.due',           'type' => 'currency', 'aggregatable' => true],
                'approx_profit' => ['label' => 'Profit',     'expr' => 's.approx_profit', 'type' => 'currency', 'aggregatable' => true],
                'status'        => ['label' => 'Status',     'expr' => 's.status',        'type' => 'text', 'groupable' => true, 'filterable' => true],
                'payment_type'  => ['label' => 'Payment Type','expr' => 's.payment_type', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'created_by_name' => ['label' => 'Staff (Created By)', 'expr' => "COALESCE(u.name,'—')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
            ],
        ],
        'sale_items' => [
            'label'      => '💊 Sale Items (Product-wise Sales)',
            'from'       => 'sale_items si',
            'joins'      => [
                'JOIN sales s ON s.id = si.sale_id',
                'LEFT JOIN products p ON p.id = si.product_id',
                'LEFT JOIN companies c ON c.id = p.company_id',
                'LEFT JOIN generics g ON g.id = p.generic_id',
                'LEFT JOIN categories cat ON cat.id = p.category_id',
                'LEFT JOIN users u ON u.id = s.created_by',
            ],
            'base_where' => ['s.is_deleted = 0'],
            'date_field' => 'date',
            'fields'     => [
                'date'          => ['label' => 'Sale Date',  'expr' => 's.date', 'type' => 'date'],
                'month'         => ['label' => 'Month',      'expr' => "DATE_FORMAT(s.date,'%Y-%m')", 'type' => 'text', 'groupable' => true],
                'product_name'  => ['label' => 'Product',    'expr' => 'si.product_name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'company_name'  => ['label' => 'Company',    'expr' => "COALESCE(c.name,'(No Company)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'category_name' => ['label' => 'Category',   'expr' => "COALESCE(cat.name,'(Uncategorized)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'generic_name'  => ['label' => 'Generic',    'expr' => "COALESCE(g.name,'(No Generic)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'customer_name' => ['label' => 'Customer',   'expr' => 's.customer_name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'qty'           => ['label' => 'Qty Sold',   'expr' => 'si.qty', 'type' => 'number', 'aggregatable' => true],
                'revenue'       => ['label' => 'Revenue',    'expr' => 'si.after_disc', 'type' => 'currency', 'aggregatable' => true],
                'cost'          => ['label' => 'Cost',       'expr' => 'si.cost * si.qty', 'type' => 'currency', 'aggregatable' => true],
                'profit'        => ['label' => 'Profit',     'expr' => 'si.after_disc - si.cost * si.qty', 'type' => 'currency', 'aggregatable' => true],
                'created_by_name' => ['label' => 'Staff (Created By)', 'expr' => "COALESCE(u.name,'—')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
            ],
        ],
        'purchases' => [
            'label'      => '📥 Purchases (POs)',
            'from'       => 'purchases pu',
            'joins'      => ['LEFT JOIN users u ON u.id = pu.submitted_by'],
            'base_where' => ['pu.is_deleted = 0'],
            'date_field' => 'date',
            'fields'     => [
                'invoice'       => ['label' => 'PO Invoice #', 'expr' => 'pu.invoice',       'type' => 'text'],
                'date'          => ['label' => 'Date',         'expr' => 'pu.date',          'type' => 'date'],
                'month'         => ['label' => 'Month',        'expr' => "DATE_FORMAT(pu.date,'%Y-%m')", 'type' => 'text', 'groupable' => true],
                'supplier_name' => ['label' => 'Supplier',     'expr' => 'pu.supplier_name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'final_price'   => ['label' => 'Total Amount', 'expr' => 'pu.final_price',   'type' => 'currency', 'aggregatable' => true],
                'paid'          => ['label' => 'Paid',         'expr' => 'pu.paid',          'type' => 'currency', 'aggregatable' => true],
                'due'           => ['label' => 'Due',          'expr' => 'pu.due',           'type' => 'currency', 'aggregatable' => true],
                'created_by_name' => ['label' => 'Staff (Submitted By)', 'expr' => "COALESCE(u.name,'—')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
            ],
        ],
        'purchase_items' => [
            'label'      => '📦 Purchase Items (Product-wise Purchases)',
            'from'       => 'purchase_items pi',
            'joins'      => [
                'JOIN purchases pu ON pu.id = pi.purchase_id',
                'LEFT JOIN products p ON p.id = pi.product_id',
                'LEFT JOIN companies c ON c.id = p.company_id',
                'LEFT JOIN categories cat ON cat.id = p.category_id',
                'LEFT JOIN users u ON u.id = pu.submitted_by',
            ],
            'base_where' => ['pu.is_deleted = 0'],
            'date_field' => 'date',
            'fields'     => [
                'date'          => ['label' => 'Purchase Date', 'expr' => 'pu.date', 'type' => 'date'],
                'month'         => ['label' => 'Month',         'expr' => "DATE_FORMAT(pu.date,'%Y-%m')", 'type' => 'text', 'groupable' => true],
                'product_name'  => ['label' => 'Product',       'expr' => 'pi.product_name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'company_name'  => ['label' => 'Company',       'expr' => "COALESCE(c.name,'(No Company)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'category_name' => ['label' => 'Category',      'expr' => "COALESCE(cat.name,'(Uncategorized)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'supplier_name' => ['label' => 'Supplier',      'expr' => 'pu.supplier_name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'qty_pcs'       => ['label' => 'Qty Purchased', 'expr' => 'pi.qty_pcs', 'type' => 'number', 'aggregatable' => true],
                'total_price'   => ['label' => 'Total Cost',    'expr' => 'pi.total_price', 'type' => 'currency', 'aggregatable' => true],
                'avg_price'     => ['label' => 'Unit Price',    'expr' => 'pi.btp_vat', 'type' => 'currency', 'aggregatable' => true],
                'created_by_name' => ['label' => 'Staff (Submitted By)', 'expr' => "COALESCE(u.name,'—')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
            ],
        ],
        'products' => [
            'label'      => '📋 Products & Stock',
            'from'       => 'products p',
            'joins'      => [
                'LEFT JOIN companies c ON c.id = p.company_id',
                'LEFT JOIN generics g ON g.id = p.generic_id',
                'LEFT JOIN categories cat ON cat.id = p.category_id',
            ],
            'base_where' => ['p.is_active = 1', 'p.is_deleted = 0'],
            'date_field' => null,
            'fields'     => [
                'name'            => ['label' => 'Product Name', 'expr' => 'p.name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'form'            => ['label' => 'Form',         'expr' => 'p.form', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'company_name'    => ['label' => 'Company',      'expr' => "COALESCE(c.name,'(No Company)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'category_name'   => ['label' => 'Category',     'expr' => "COALESCE(cat.name,'(Uncategorized)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'generic_name'    => ['label' => 'Generic',      'expr' => "COALESCE(g.name,'(No Generic)')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'stock_pcs'       => ['label' => 'Stock (pcs)',  'expr' => 'p.stock_pcs', 'type' => 'number', 'aggregatable' => true, 'filterable' => true],
                'sale_price'      => ['label' => 'Sale Price',   'expr' => 'p.sale_price', 'type' => 'currency', 'aggregatable' => true],
                'stock_value'     => ['label' => 'Stock Value',  'expr' => 'p.stock_pcs * p.sale_price', 'type' => 'currency', 'aggregatable' => true],
                'low_stock_alert' => ['label' => 'Alert Qty',    'expr' => 'p.low_stock_alert', 'type' => 'number', 'filterable' => true],
            ],
        ],
        'customers' => [
            'label'      => '👤 Customers',
            'from'       => 'customers cu',
            'joins'      => [],
            'base_where' => ['cu.is_deleted = 0', 'cu.id > 1'],
            'date_field' => null,
            'fields'     => [
                'name'    => ['label' => 'Customer Name', 'expr' => 'cu.name',  'type' => 'text', 'filterable' => true],
                'phone'   => ['label' => 'Phone',         'expr' => 'cu.phone', 'type' => 'text'],
                'due'     => ['label' => 'Due Balance',   'expr' => 'cu.due',   'type' => 'currency', 'aggregatable' => true, 'filterable' => true],
                'advance' => ['label' => 'Advance Balance','expr' => 'cu.advance', 'type' => 'currency', 'aggregatable' => true, 'filterable' => true],
            ],
        ],
        'suppliers' => [
            'label'      => '🏢 Suppliers',
            'from'       => 'suppliers su',
            'joins'      => [],
            'base_where' => ['su.is_deleted = 0'],
            'date_field' => null,
            'fields'     => [
                'name'    => ['label' => 'Supplier Name', 'expr' => 'su.name',  'type' => 'text', 'filterable' => true],
                'phone'   => ['label' => 'Phone',         'expr' => 'su.phone', 'type' => 'text'],
                'balance' => ['label' => 'Balance Due',   'expr' => 'su.balance', 'type' => 'currency', 'aggregatable' => true, 'filterable' => true],
            ],
        ],
        'cash_ledger' => [
            'label'      => '📒 Cash Ledger',
            'from'       => 'cash_ledger cl',
            'joins'      => [],
            'base_where' => [],
            'date_field' => 'date',
            'fields'     => [
                'date'        => ['label' => 'Date',        'expr' => 'cl.date', 'type' => 'date'],
                'month'       => ['label' => 'Month',       'expr' => "DATE_FORMAT(cl.date,'%Y-%m')", 'type' => 'text', 'groupable' => true],
                'description' => ['label' => 'Description', 'expr' => 'cl.description', 'type' => 'text', 'filterable' => true],
                'account'     => ['label' => 'Account',     'expr' => 'cl.account', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'type'        => ['label' => 'Type',        'expr' => 'cl.type', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'amount'      => ['label' => 'Amount',      'expr' => 'cl.amount', 'type' => 'currency', 'aggregatable' => true],
            ],
        ],
        'expenses' => [
            'label'      => '💸 Expenses',
            'from'       => 'expenses ex',
            'joins'      => ['LEFT JOIN users u ON u.id = ex.created_by'],
            'base_where' => [],
            'date_field' => 'date',
            'fields'     => [
                'date'        => ['label' => 'Date',        'expr' => 'ex.date', 'type' => 'date'],
                'month'       => ['label' => 'Month',       'expr' => "DATE_FORMAT(ex.date,'%Y-%m')", 'type' => 'text', 'groupable' => true],
                'category'    => ['label' => 'Category',    'expr' => 'ex.category', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'description' => ['label' => 'Description', 'expr' => 'ex.description', 'type' => 'text', 'filterable' => true],
                'account'     => ['label' => 'Account',     'expr' => 'ex.account', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'amount'      => ['label' => 'Amount',      'expr' => 'ex.amount', 'type' => 'currency', 'aggregatable' => true],
                'created_by_name' => ['label' => 'Staff (Recorded By)', 'expr' => "COALESCE(u.name,'—')", 'type' => 'text', 'groupable' => true, 'filterable' => true],
            ],
        ],
        'users' => [
            'label'      => '🧑‍💼 Users (Staff Activity)',
            'from'       => 'users u',
            'joins'      => [],
            'base_where' => [],
            'date_field' => null,
            'fields'     => [
                'name'     => ['label' => 'Name',     'expr' => 'u.name', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'username' => ['label' => 'Username', 'expr' => 'u.username', 'type' => 'text', 'filterable' => true],
                'role'     => ['label' => 'Role',     'expr' => 'u.role', 'type' => 'text', 'groupable' => true, 'filterable' => true],
                'is_active'=> ['label' => 'Active?',  'expr' => 'u.is_active', 'type' => 'number', 'filterable' => true],
                // All-time totals via correlated subqueries — scoped to
                // this one user row, so they're safe to select alongside
                // plain columns without any join fan-out/duplication.
                'sales_count'      => ['label' => 'Sales Made (count)',    'expr' => '(SELECT COUNT(*) FROM sales WHERE created_by = u.id AND is_deleted = 0)', 'type' => 'number', 'aggregatable' => true],
                'sales_amount'     => ['label' => 'Sales Made (amount)',   'expr' => "(SELECT COALESCE(SUM(final_price),0) FROM sales WHERE created_by = u.id AND is_deleted = 0)", 'type' => 'currency', 'aggregatable' => true],
                'purchases_count'  => ['label' => 'Purchases Submitted (count)',  'expr' => '(SELECT COUNT(*) FROM purchases WHERE submitted_by = u.id AND is_deleted = 0)', 'type' => 'number', 'aggregatable' => true],
                'purchases_amount' => ['label' => 'Purchases Submitted (amount)', 'expr' => "(SELECT COALESCE(SUM(final_price),0) FROM purchases WHERE submitted_by = u.id AND is_deleted = 0)", 'type' => 'currency', 'aggregatable' => true],
                'expenses_count'   => ['label' => 'Expenses Recorded (count)',  'expr' => '(SELECT COUNT(*) FROM expenses WHERE created_by = u.id)', 'type' => 'number', 'aggregatable' => true],
                'expenses_amount'  => ['label' => 'Expenses Recorded (amount)', 'expr' => "(SELECT COALESCE(SUM(amount),0) FROM expenses WHERE created_by = u.id)", 'type' => 'currency', 'aggregatable' => true],
            ],
        ],
    ];
}

const REPORT_AGG_FNS  = ['SUM', 'AVG', 'COUNT', 'MIN', 'MAX'];
const REPORT_FILT_OPS = ['=', '!=', '>', '<', '>=', '<=', 'LIKE'];

// ── Safe SQL builder + executor ─────────────────────────────────
// $def   = decoded `definition` JSON from a custom_reports row
// $from/$to = runtime date-range override (applied only if the
//             data source has a date_field)
// Returns ['columns' => [...], 'rows' => [...]]
function run_custom_report(string $dataSource, array $def, ?string $from = null, ?string $to = null): array {
    $registry = reporting_registry();
    $src = $registry[$dataSource] ?? null;
    if (!$src) throw new Exception('Unknown data source: ' . $dataSource);
    $fields = $src['fields'];

    $selectParts = [];
    $groupParts  = [];
    $columns     = [];
    $isGrouped   = !empty($def['group_by']);

    if ($isGrouped) {
        $gKey = $def['group_by'];
        $gf   = $fields[$gKey] ?? null;
        if (!$gf || empty($gf['groupable'])) throw new Exception('Invalid group-by field');
        $selectParts[] = "{$gf['expr']} AS `{$gKey}`";
        $groupParts[]  = $gf['expr'];
        $columns[]     = ['key' => $gKey, 'label' => $gf['label'], 'type' => $gf['type']];

        foreach ($def['aggregates'] ?? [] as $agg) {
            $fKey = $agg['field'] ?? '';
            $fn   = strtoupper($agg['fn'] ?? 'SUM');
            $f    = $fields[$fKey] ?? null;
            if (!$f || empty($f['aggregatable']) || !in_array($fn, REPORT_AGG_FNS, true)) continue;
            $alias = $fKey . '_' . strtolower($fn);
            $selectParts[] = "{$fn}({$f['expr']}) AS `{$alias}`";
            $columns[]     = ['key' => $alias, 'label' => $fn . ' ' . $f['label'], 'type' => $f['type']];
        }
        if (count($selectParts) < 2) throw new Exception('Select at least one aggregate for a grouped report');
    } else {
        foreach ($def['columns'] ?? [] as $colKey) {
            $f = $fields[$colKey] ?? null;
            if (!$f) continue;
            $selectParts[] = "{$f['expr']} AS `{$colKey}`";
            $columns[]     = ['key' => $colKey, 'label' => $f['label'], 'type' => $f['type']];
        }
        if (!$selectParts) throw new Exception('No columns selected');
    }

    $sql = 'SELECT ' . implode(', ', $selectParts) . ' FROM ' . $src['from'];
    foreach ($src['joins'] as $j) $sql .= ' ' . $j;

    $where  = $src['base_where'] ?? [];
    $params = [];

    if ($src['date_field'] && $from && $to) {
        $df = $fields[$src['date_field']];
        $where[] = "{$df['expr']} BETWEEN ? AND ?";
        $params[] = $from;
        $params[] = $to;
    }

    foreach ($def['filters'] ?? [] as $flt) {
        $fKey = $flt['field'] ?? '';
        $op   = strtoupper($flt['op'] ?? '=');
        $f    = $fields[$fKey] ?? null;
        if (!$f || !in_array($op, REPORT_FILT_OPS, true)) continue;
        $val = $flt['value'] ?? '';
        if ($op === 'LIKE') {
            $where[] = "{$f['expr']} LIKE ?";
            $params[] = '%' . $val . '%';
        } else {
            $where[] = "{$f['expr']} {$op} ?";
            $params[] = $val;
        }
    }

    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    if ($groupParts) $sql .= ' GROUP BY ' . implode(', ', $groupParts);

    // sort_field must be one of the aliases we JUST generated above
    // (group-by key or an aggregate alias, or a selected detail
    // column) — never arbitrary input, so it's safe to splice in as
    // a backtick-quoted identifier.
    $validSortKeys = array_column($columns, 'key');
    if (!empty($def['sort_field']) && in_array($def['sort_field'], $validSortKeys, true)) {
        $dir = strtoupper($def['sort_dir'] ?? 'DESC') === 'ASC' ? 'ASC' : 'DESC';
        $sql .= " ORDER BY `{$def['sort_field']}` {$dir}";
    }

    if (!empty($def['limit_n'])) {
        $sql .= ' LIMIT ' . max(1, min(1000, (int) $def['limit_n']));
    } else {
        $sql .= ' LIMIT 5000'; // hard safety cap even on unbounded reports
    }

    $rows = db_rows($sql, $params);
    return ['columns' => $columns, 'rows' => $rows, 'sql' => $sql];
}
