<?php
include("../../../include/startup.php");
header('Content-Type: application/json');
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', 0);

$response = [
    'success'    => false,
    'data'       => [],
    'message'    => '',
    'pagination' => [],
    'summary'    => []
];

$action = $_REQUEST['action'] ?? '';

if (!function_exists('getBoughtInCost')) {
    function getBoughtInCost($conn, $partId, $multiplier = 1, &$bop = [])
    {
        $totalCost = 0;
        $partId    = intval($partId);

        $sql = "
            SELECT
                b.sub_part_id,
                b.quantity_required,
                im.usual_source,
                im.standard_cost_ebq,
                im.part_number
            FROM bom b
            JOIN item_management im ON im.id = b.sub_part_id
            WHERE b.main_product_id = $partId
        ";
        $result = mysqli_query($conn, $sql);
        if (!$result) return 0;

        while ($row = mysqli_fetch_assoc($result)) {
            $qty = $multiplier * (float)$row['quantity_required'];
            if ($row['usual_source'] === 'Bought In') {
                $totalCost += $qty * (float)$row['standard_cost_ebq'];
                $bop[$row['part_number']] = true;
            } else {
                $totalCost += getBoughtInCost($conn, $row['sub_part_id'], $qty, $bop);
            }
        }
        return $totalCost;
    }
}

if (!function_exists('getRouteCost')) {
    function getRouteCost($conn, $partId)
    {
        $partId = intval($partId);
        $sql    = "
            SELECT
                SUM((TIME_TO_SEC(operation_time)/3600)
                    * IFNULL(labour_rate,0)
                    * IFNULL(workers_needed,1)) AS labour_cost,
                SUM((TIME_TO_SEC(operation_time)/3600)
                    * IFNULL(machine_rate,0))   AS machine_cost
            FROM route_details
            WHERE part_no = $partId
        ";
        $res = mysqli_query($conn, $sql);
        $row = $res ? mysqli_fetch_assoc($res) : [];
        return [
            'labour'  => (float)($row['labour_cost']  ?? 0),
            'machine' => (float)($row['machine_cost'] ?? 0),
        ];
    }
}

if ($action === 'get_std_costs') {

    $page     = max(1, intval($_GET['current_page'] ?? 1));
    $per_page = max(1, intval($_GET['per_page']     ?? 25));
    $offset   = ($page - 1) * $per_page;

    $part_search = trim($_GET['part']     ?? '');
    $min_cost    = trim($_GET['min_cost'] ?? '');
    $max_cost    = trim($_GET['max_cost'] ?? '');

    /* ── Fetch all manufactured final parts ── */
    $where_part = '';
    if ($part_search !== '') {
        $s          = $conn->real_escape_string($part_search);
        $where_part = "AND im.part_number LIKE '%$s%'";
    }

    $partsSQL = "
        SELECT im.id, im.part_number
        FROM   item_management im
        WHERE  im.usual_source = 'Manufactured'
          AND  im.id NOT IN (
              SELECT DISTINCT sub_part_id FROM bom WHERE sub_part_id IS NOT NULL
          )
          $where_part
        ORDER BY im.part_number ASC
    ";
    $partsRes = mysqli_query($conn, $partsSQL);
    if (!$partsRes) {
        $err = is_object($conn) ? mysqli_error($conn) : 'query error';
        $response['message'] = 'Query failed: ' . $err . ' | SQL: ' . preg_replace('/\s+/', ' ', trim($partsSQL));
        ob_end_clean();
        echo json_encode($response);
        exit;
    }

    $all = [];
    while ($part = mysqli_fetch_assoc($partsRes)) {
        $bop          = [];
        $materialCost = getBoughtInCost($conn, $part['id'], 1, $bop);
        $routeCost    = getRouteCost($conn, $part['id']);
        $totalCost    = $materialCost + $routeCost['labour'] + $routeCost['machine'];

        if ($min_cost !== '' && $totalCost < (float)$min_cost) continue;
        if ($max_cost !== '' && $totalCost > (float)$max_cost) continue;

        $all[] = [
            'id'            => (int)$part['id'],
            'part_number'   => $part['part_number'],
            'material_cost' => round($materialCost,        4),
            'labour_cost'   => round($routeCost['labour'], 4),
            'machine_cost'  => round($routeCost['machine'],4),
            'total_cost'    => round($totalCost,           4),
            'bop_parts'     => array_keys($bop),
        ];
    }

    /* ── Summary stats (full filtered set, before slice) ── */
    $total_records = count($all);
    $avg_mat       = $total_records > 0 ? array_sum(array_column($all, 'material_cost')) / $total_records : 0;
    $avg_total     = $total_records > 0 ? array_sum(array_column($all, 'total_cost'))    / $total_records : 0;

    /* ── Paginate ── */
    $total_pages = max(1, (int)ceil($total_records / $per_page));
    $page_data   = array_slice($all, $offset, $per_page);

    $response['success']    = true;
    $response['data']       = $page_data;
    $response['pagination'] = [
        'current_page'  => $page,
        'per_page'      => $per_page,
        'total_records' => $total_records,
        'total_pages'   => $total_pages,
        'showing_from'  => $total_records ? $offset + 1                  : 0,
        'showing_to'    => min($offset + $per_page, $total_records),
    ];
    $response['summary'] = [
        'avg_material' => round($avg_mat,   2),
        'avg_total'    => round($avg_total, 2),
    ];

    ob_end_clean();         
    echo json_encode($response);
    exit;
}
if ($action === 'get_stock_valuation') {

    $page     = max(1, intval($_GET['current_page'] ?? 1));
    $per_page = max(1, intval($_GET['per_page']     ?? 25));
    $offset   = ($page - 1) * $per_page;

    $part_search = trim($_GET['part']      ?? '');
    $type_filter = trim($_GET['item_type'] ?? '');
    $min_val     = trim($_GET['min_val']   ?? '');
    $max_val     = trim($_GET['max_val']   ?? '');

    $where = ['1=1'];
    if ($part_search !== '') {
        $s       = $conn->real_escape_string($part_search);
        $where[] = "im.part_number LIKE '%$s%'";
    }
    if ($type_filter !== '' && $type_filter !== 'all') {
        $t       = $conn->real_escape_string($type_filter);
        $where[] = "im.usual_source = '$t'";
    }
    $whereSQL = implode(' AND ', $where);

    $sql = "
        SELECT
            im.id            AS item_id,
            im.part_number,
            im.usual_source  AS item_type,
            CASE
                WHEN im.usual_source = 'Manufactured' THEN wom.work_order_name
                ELSE po.order_ref
            END AS batch_ref,
            CASE
                WHEN im.usual_source = 'Manufactured' THEN wod.last_used_date
                ELSE poi.last_used_date
            END AS latest_used_date,
            COALESCE(inv.quantity_in_stock, 0)  AS stock_quantity,
            COALESCE(im.standard_cost_ebq, 0)   AS unit_value,
            (COALESCE(inv.quantity_in_stock, 0) * COALESCE(im.standard_cost_ebq, 0)) AS extended_value
        FROM item_management im
        LEFT JOIN inventory inv
            ON inv.item_id = im.id
        LEFT JOIN (
            SELECT product_id, work_order_id, MAX(created_on) AS last_used_date
            FROM work_order_detail
            GROUP BY product_id
        ) wod ON wod.product_id = im.id
        LEFT JOIN work_order_name wom
            ON wom.work_order_id = wod.work_order_id
           AND wom.part_id = im.id
        LEFT JOIN (
            SELECT part_number, MAX(created_on) AS last_used_date, MAX(po_id) AS po_id
            FROM purchase_order_items_mapping
            GROUP BY part_number
        ) poi ON poi.part_number = im.id
        LEFT JOIN purchase_order po
            ON po.id = poi.po_id
        WHERE $whereSQL
        ORDER BY im.part_number ASC
    ";

    $result = mysqli_query($conn, $sql);
    if (!$result) {
        $response['message'] = 'Query failed: ' . mysqli_error($conn);
        ob_end_clean();
        echo json_encode($response);
        exit;
    }

    $all = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $extVal = (float)$row['extended_value'];
        if ($min_val !== '' && $extVal < (float)$min_val) continue;
        if ($max_val !== '' && $extVal > (float)$max_val) continue;
        $all[] = [
            'item_id'          => (int)$row['item_id'],
            'part_number'      => $row['part_number'],
            'item_type'        => $row['item_type'],
            'batch_ref'        => $row['batch_ref']        ?? null,
            'latest_used_date' => $row['latest_used_date'] ?? null,
            'stock_quantity'   => round((float)$row['stock_quantity'], 4),
            'unit_value'       => round((float)$row['unit_value'],     4),
            'extended_value'   => round($extVal,                       4),
        ];
    }

    $total_records  = count($all);
    $grand_total    = array_sum(array_column($all, 'extended_value'));
    $avg_unit       = $total_records > 0 ? array_sum(array_column($all, 'unit_value'))     / $total_records : 0;
    $avg_extended   = $total_records > 0 ? $grand_total / $total_records : 0;

    $total_pages = max(1, (int)ceil($total_records / $per_page));
    $page_data   = array_slice($all, $offset, $per_page);

    $response['success'] = true;
    $response['data']    = $page_data;
    $response['pagination'] = [
        'current_page'  => $page,
        'per_page'      => $per_page,
        'total_records' => $total_records,
        'total_pages'   => $total_pages,
        'showing_from'  => $total_records ? $offset + 1                 : 0,
        'showing_to'    => min($offset + $per_page, $total_records),
    ];
    $response['summary'] = [
        'grand_total'  => round($grand_total,  2),
        'avg_unit'     => round($avg_unit,     2),
        'avg_extended' => round($avg_extended, 2),
    ];

    ob_end_clean();
    echo json_encode($response);
    exit;
}
if ($action === 'get_delivery_report') {

    $page     = max(1, intval($_GET['current_page'] ?? 1));
    $per_page = max(1, intval($_GET['per_page']     ?? 25));
    $offset   = ($page - 1) * $per_page;

    $part_number      = trim($_GET['part_number']      ?? '');
    $customer_order   = trim($_GET['customer_order']   ?? '');
    $invoice_number   = trim($_GET['invoice_number']   ?? '');
    $customer_id      = trim($_GET['customer_id']      ?? '');
    $from_date        = trim($_GET['from_date']        ?? '');
    $to_date          = trim($_GET['to_date']          ?? '');

    $where = "WHERE 1=1";

    if ($from_date && $to_date) {
        $fd = $conn->real_escape_string($from_date);
        $td = $conn->real_escape_string($to_date);
        $where .= " AND si.delivery_date BETWEEN '$fd 00:00:00' AND '$td 23:59:59'";
    } elseif ($from_date) {
        $fd = $conn->real_escape_string($from_date);
        $where .= " AND si.delivery_date >= '$fd 00:00:00'";
    } elseif ($to_date) {
        $td = $conn->real_escape_string($to_date);
        $where .= " AND si.delivery_date <= '$td 23:59:59'";
    }

    if ($part_number !== '') {
        $s = $conn->real_escape_string($part_number);
        $where .= " AND im.part_number LIKE '%$s%'";
    }
    if ($customer_order !== '') {
        $s = $conn->real_escape_string($customer_order);
        $where .= " AND si.order_number LIKE '%$s%'";
    }
    if ($invoice_number !== '') {
        $s = $conn->real_escape_string($invoice_number);
        $where .= " AND si.invoice_number LIKE '%$s%'";
    }
    if ($customer_id !== '') {
        $where .= " AND si.customer_id = " . intval($customer_id);
    }

    /* ── Count ── */
    $countSQL = "
        SELECT COUNT(*) AS total FROM (
            SELECT si.id
            FROM sales_invoices si
            INNER JOIN sales_invoice_mapping sim ON si.id = sim.sales_id
            INNER JOIN client_management cm      ON cm.id = si.customer_id
            LEFT  JOIN item_management im        ON im.id = sim.part_number
            LEFT  JOIN delivery_notes dn         ON dn.id = si.delivery_note_no
            $where
            GROUP BY
                si.invoice_number, si.invoice_date, si.order_number,
                si.delivery_note_no, dn.note_number, si.customer_id,
                si.delivery_date, cm.customer_code, cm.customer_name,
                sim.part_number, im.part_number, sim.description,
                sim.unit_price, sim.total
        ) AS counted
    ";
    $countRes      = mysqli_query($conn, $countSQL);
    $total_records = $countRes ? (int)mysqli_fetch_assoc($countRes)['total'] : 0;

    /* ── Main query ── */
    $dataSQL = "
        SELECT
            si.invoice_number,
            si.invoice_date        AS date_required,
            si.order_number,
            dn.note_number         AS delivery_note_number,
            si.delivery_date       AS date_delivered,
            cm.customer_code,
            cm.customer_name,
            im.part_number         AS item_part_number,
            sim.part_number        AS part_id,
            sim.description        AS part_description,
            SUM(sim.quantity)      AS quantity_delivered,
            sim.unit_price,
            sim.total
        FROM sales_invoices si
        INNER JOIN sales_invoice_mapping sim ON si.id = sim.sales_id
        INNER JOIN client_management cm      ON cm.id = si.customer_id
        LEFT  JOIN item_management im        ON im.id = sim.part_number
        LEFT  JOIN delivery_notes dn         ON dn.id = si.delivery_note_no
        $where
        GROUP BY
            si.invoice_number, si.invoice_date, si.order_number,
            si.delivery_note_no, dn.note_number, si.customer_id,
            si.delivery_date, cm.customer_code, cm.customer_name,
            sim.part_number, im.part_number, sim.description,
            sim.unit_price, sim.total
        ORDER BY si.delivery_date DESC, si.invoice_number DESC
        LIMIT $offset, $per_page
    ";

    $dataRes = mysqli_query($conn, $dataSQL);
    if (!$dataRes) {
        $response['message'] = 'Query failed: ' . mysqli_error($conn);
        ob_end_clean();
        echo json_encode($response);
        exit;
    }

    $rows = [];
    $page_total_qty   = 0;
    $page_total_value = 0;

    while ($row = mysqli_fetch_assoc($dataRes)) {
        $qty   = (float)$row['quantity_delivered'];
        $total = (float)$row['total'];
        $page_total_qty   += $qty;
        $page_total_value += $total;
        $rows[] = [
            'invoice_number'      => $row['invoice_number'],
            'date_required'       => $row['date_required']  ? substr($row['date_required'],  0, 10) : null,
            'date_delivered'      => $row['date_delivered']  ? substr($row['date_delivered'],  0, 10) : null,
            'order_number'        => $row['order_number'],
            'delivery_note'       => $row['delivery_note_number'],
            'customer_code'       => $row['customer_code'],
            'customer_name'       => $row['customer_name'],
            'part_number'         => !empty($row['item_part_number']) ? $row['item_part_number'] : $row['part_id'],
            'part_description'    => $row['part_description'],
            'quantity_delivered'  => round($qty,   4),
            'unit_price'          => round((float)$row['unit_price'], 4),
            'total'               => round($total, 4),
        ];
    }

    /* ── Summary totals across full filtered set ── */
    $sumSQL = "
        SELECT
            SUM(sub.quantity_delivered) AS total_qty,
            SUM(sub.total)              AS total_value
        FROM (
            SELECT SUM(sim.quantity) AS quantity_delivered, sim.total
            FROM sales_invoices si
            INNER JOIN sales_invoice_mapping sim ON si.id = sim.sales_id
            INNER JOIN client_management cm      ON cm.id = si.customer_id
            LEFT  JOIN item_management im        ON im.id = sim.part_number
            LEFT  JOIN delivery_notes dn         ON dn.id = si.delivery_note_no
            $where
            GROUP BY
                si.invoice_number, si.invoice_date, si.order_number,
                si.delivery_note_no, dn.note_number, si.customer_id,
                si.delivery_date, cm.customer_code, cm.customer_name,
                sim.part_number, im.part_number, sim.description,
                sim.unit_price, sim.total
        ) AS sub
    ";
    $sumRes = mysqli_query($conn, $sumSQL);
    $sumRow = $sumRes ? mysqli_fetch_assoc($sumRes) : [];

    $total_pages = max(1, (int)ceil($total_records / $per_page));

    $response['success'] = true;
    $response['data']    = $rows;
    $response['pagination'] = [
        'current_page'  => $page,
        'per_page'      => $per_page,
        'total_records' => $total_records,
        'total_pages'   => $total_pages,
        'showing_from'  => $total_records ? $offset + 1                 : 0,
        'showing_to'    => min($offset + $per_page, $total_records),
    ];
    $response['summary'] = [
        'total_qty'   => round((float)($sumRow['total_qty']   ?? 0), 2),
        'total_value' => round((float)($sumRow['total_value'] ?? 0), 2),
    ];

    ob_end_clean();
    echo json_encode($response);
    exit;
}
if ($action === 'get_sales_order_stock') {

    $page     = max(1, intval($_GET['current_page'] ?? 1));
    $per_page = max(1, intval($_GET['per_page']     ?? 25));
    $offset   = ($page - 1) * $per_page;

    $part_number    = trim($_GET['part_number']    ?? '');
    $customer_order = trim($_GET['customer_order'] ?? '');
    $from_date      = trim($_GET['from_date']      ?? '');
    $to_date        = trim($_GET['to_date']        ?? '');

    $where = "WHERE 1=1";

    if ($from_date && $to_date) {
        $fd = $conn->real_escape_string($from_date);
        $td = $conn->real_escape_string($to_date);
        $where .= " AND pp.req_date BETWEEN '$fd 00:00:00' AND '$td 23:59:59'";
    } elseif ($from_date) {
        $fd = $conn->real_escape_string($from_date);
        $where .= " AND pp.req_date >= '$fd 00:00:00'";
    } elseif ($to_date) {
        $td = $conn->real_escape_string($to_date);
        $where .= " AND pp.req_date <= '$td 23:59:59'";
    }

    if ($part_number !== '') {
        $s = $conn->real_escape_string($part_number);
        $where .= " AND im.part_number LIKE '%$s%'";
    }

    if ($customer_order !== '') {
        $s = $conn->real_escape_string($customer_order);
        $where .= " AND pp.RefSalesCode LIKE '%$s%'";
    }

    /* ── Count ── */
    $countSQL = "
    SELECT COUNT(*) AS total
    FROM production_planning pp
    LEFT JOIN item_management im  ON im.id = pp.part_id
    LEFT JOIN inventory inv       ON inv.item_id = pp.part_id
    $where
";
    $countRes      = mysqli_query($conn, $countSQL);
    $total_records = $countRes ? (int)mysqli_fetch_assoc($countRes)['total'] : 0;

    /* ── Main query ── */

$dataSQL = "
    SELECT
        pp.id,
        pp.req_date,
        pp.RefSalesCode,
        pp.req_qty,
        COALESCE(inv.quantity_in_stock, 0) AS current_stock,
        im.part_number,
        im.product_description
    FROM production_planning pp
    LEFT JOIN item_management im  ON im.id = pp.part_id
    LEFT JOIN inventory inv       ON inv.item_id = pp.part_id
    $where
    ORDER BY pp.req_date ASC, im.part_number ASC
    LIMIT $offset, $per_page
";
    $dataRes = mysqli_query($conn, $dataSQL);
    if (!$dataRes) {
        $response['message'] = 'Query failed: ' . mysqli_error($conn);
        ob_end_clean();
        echo json_encode($response);
        exit;
    }

    $rows = [];
    while ($row = mysqli_fetch_assoc($dataRes)) {
        $rows[] = [
            'id'                  => (int)$row['id'],
            'req_date'            => $row['req_date'] ? substr($row['req_date'], 0, 10) : null,
            'part_number'         => $row['part_number']        ?? '',
            'product_description' => $row['product_description'] ?? '',
            'ref_sales_code'      => $row['RefSalesCode']        ?? '',
            'current_stock'       => round((float)($row['current_stock'] ?? 0), 4),
            'req_qty'             => round((float)($row['req_qty']        ?? 0), 4),
        ];
    }

    /* ── Summary ── */
    $sumSQL = "
    SELECT
        SUM(pp.req_qty)                    AS total_qty,
        SUM(COALESCE(inv.quantity_in_stock, 0)) AS total_stock
    FROM production_planning pp
    LEFT JOIN item_management im  ON im.id = pp.part_id
    LEFT JOIN inventory inv       ON inv.item_id = pp.part_id
    $where
";
    $sumRes = mysqli_query($conn, $sumSQL);
    $sumRow = $sumRes ? mysqli_fetch_assoc($sumRes) : [];

    $total_pages = max(1, (int)ceil($total_records / $per_page));

    $response['success'] = true;
    $response['data']    = $rows;
    $response['pagination'] = [
        'current_page'  => $page,
        'per_page'      => $per_page,
        'total_records' => $total_records,
        'total_pages'   => $total_pages,
        'showing_from'  => $total_records ? $offset + 1                : 0,
        'showing_to'    => min($offset + $per_page, $total_records),
    ];
    $response['summary'] = [
        'total_qty'   => round((float)($sumRow['total_qty']    ?? 0), 2),
        'total_stock' => round((float)($sumRow['total_stock']  ?? 0), 2),
    ];

    ob_end_clean();
    echo json_encode($response);
    exit;
}
if ($action === 'get_deliveries_awaiting_invoice') {

    $page     = max(1, intval($_GET['current_page'] ?? 1));
    $per_page = max(1, intval($_GET['per_page']     ?? 25));
    $offset   = ($page - 1) * $per_page;

    $part_number    = trim($_GET['part_number']    ?? '');
    $customer_order = trim($_GET['customer_order'] ?? '');
    $invoice_number = trim($_GET['invoice_number'] ?? '');
    $customer_id    = trim($_GET['customer_id']    ?? '');
    $from_date      = trim($_GET['from_date']      ?? '');
    $to_date        = trim($_GET['to_date']        ?? '');

    $where = "WHERE dn.id IS NOT NULL";

    if ($from_date && $to_date) {
        $fd = $conn->real_escape_string($from_date);
        $td = $conn->real_escape_string($to_date);
        $where .= " AND si.delivery_date BETWEEN '$fd 00:00:00' AND '$td 23:59:59'";
    } elseif ($from_date) {
        $fd = $conn->real_escape_string($from_date);
        $where .= " AND si.delivery_date >= '$fd 00:00:00'";
    } elseif ($to_date) {
        $td = $conn->real_escape_string($to_date);
        $where .= " AND si.delivery_date <= '$td 23:59:59'";
    }

    if ($part_number !== '') {
        $s = $conn->real_escape_string($part_number);
        $where .= " AND im.part_number LIKE '%$s%'";
    }
    if ($customer_order !== '') {
        $s = $conn->real_escape_string($customer_order);
        $where .= " AND pp.RefSalesCode LIKE '%$s%'";
    }
    if ($invoice_number !== '') {
        $s = $conn->real_escape_string($invoice_number);
        $where .= " AND si.invoice_number LIKE '%$s%'";
    }
    if ($customer_id !== '') {
        $where .= " AND si.customer_id = " . intval($customer_id);
    }

    $joins = "
        FROM sales_invoices si
        INNER JOIN sales_invoice_mapping sim ON si.id = sim.sales_id
        INNER JOIN client_management cm      ON cm.id = si.customer_id
        LEFT  JOIN item_management im        ON im.id = sim.part_number
        INNER JOIN delivery_notes dn         ON dn.id = si.delivery_note_no
        LEFT  JOIN production_planning pp    ON pp.id = si.order_number
    ";

    // ── Fixed: removed duplicate comma and duplicate RefSalesCode ──
    $groupBy = "
        GROUP BY
            si.invoice_number,
            si.invoice_date,
            pp.RefSalesCode,
            si.delivery_note_no,
            dn.note_number,
            si.customer_id,
            si.delivery_date,
            cm.customer_code,
            cm.customer_name,
            sim.part_number,
            im.part_number,
            sim.description,
            sim.unit_price,
            sim.total
    ";

    /* ── Count ── */
    $countSQL = "
        SELECT COUNT(*) AS total FROM (
            SELECT si.id
            $joins
            $where
            $groupBy
        ) AS counted
    ";
    $countRes      = mysqli_query($conn, $countSQL);
    $total_records = $countRes ? (int)mysqli_fetch_assoc($countRes)['total'] : 0;

    /* ── Main query ── */
    // ── Fixed: removed duplicate RefSalesCode column ──
    $dataSQL = "
        SELECT
            si.invoice_number,
            si.invoice_date           AS date_required,
            pp.RefSalesCode           AS sales_order_number,
            dn.note_number            AS delivery_note_number,
            si.delivery_date          AS date_delivered,
            cm.customer_code,
            cm.customer_name,
            im.part_number            AS item_part_number,
            sim.part_number           AS part_id,
            sim.description           AS part_description,
            SUM(sim.quantity)         AS quantity_delivered,
            sim.unit_price,
            sim.total
        $joins
        $where
        $groupBy
        ORDER BY si.delivery_date DESC, si.invoice_number DESC
        LIMIT $offset, $per_page
    ";

    $dataRes = mysqli_query($conn, $dataSQL);
    if (!$dataRes) {
        $response['message'] = 'Query failed: ' . mysqli_error($conn);
        ob_end_clean();
        echo json_encode($response);
        exit;
    }

    $rows = [];
    while ($row = mysqli_fetch_assoc($dataRes)) {
        $rows[] = [
            'invoice_number'     => $row['invoice_number']      ?? '',
            'date_required'      => $row['date_required']        ? substr($row['date_required'],  0, 10) : null,
            'date_delivered'     => $row['date_delivered']       ? substr($row['date_delivered'], 0, 10) : null,
            'sales_order_number' => $row['sales_order_number']   ?? '',
            'delivery_note'      => $row['delivery_note_number'] ?? '',
            'customer_code'      => $row['customer_code']        ?? '',
            'customer_name'      => $row['customer_name']        ?? '',
            'part_number'        => !empty($row['item_part_number']) ? $row['item_part_number'] : ($row['part_id'] ?? ''),
            'part_description'   => $row['part_description']     ?? '',
            'quantity_delivered' => round((float)($row['quantity_delivered'] ?? 0), 4),
            'unit_price'         => round((float)($row['unit_price']         ?? 0), 4),
            'total'              => round((float)($row['total']              ?? 0), 4),
        ];
    }

    /* ── Summary ── */
    $sumSQL = "
        SELECT
            SUM(sub.quantity_delivered) AS total_qty,
            SUM(sub.total)              AS total_value
        FROM (
            SELECT SUM(sim.quantity) AS quantity_delivered, sim.total
            $joins
            $where
            $groupBy
        ) AS sub
    ";
    $sumRes = mysqli_query($conn, $sumSQL);
    $sumRow = $sumRes ? mysqli_fetch_assoc($sumRes) : [];

    $total_pages = max(1, (int)ceil($total_records / $per_page));

    $response['success'] = true;
    $response['data']    = $rows;
    $response['pagination'] = [
        'current_page'  => $page,
        'per_page'      => $per_page,
        'total_records' => $total_records,
        'total_pages'   => $total_pages,
        'showing_from'  => $total_records ? $offset + 1             : 0,
        'showing_to'    => min($offset + $per_page, $total_records),
    ];
    $response['summary'] = [
        'total_qty'   => round((float)($sumRow['total_qty']   ?? 0), 2),
        'total_value' => round((float)($sumRow['total_value'] ?? 0), 2),
    ];

    ob_end_clean();
    echo json_encode($response);
    exit;
}
if ($action === 'get_customer_delivery') {

    $page     = max(1, intval($_GET['current_page'] ?? 1));
    $per_page = max(1, intval($_GET['per_page']     ?? 25));
    $offset   = ($page - 1) * $per_page;

    $part_number    = trim($_GET['part_number']    ?? '');
    $customer_order = trim($_GET['customer_order'] ?? '');
    $from_date      = trim($_GET['from_date']      ?? '');
    $to_date        = trim($_GET['to_date']        ?? '');

    $where = "WHERE 1=1";

    if ($from_date && $to_date) {
        $fd = $conn->real_escape_string($from_date);
        $td = $conn->real_escape_string($to_date);
        $where .= " AND dn.delivery_date BETWEEN '$fd 00:00:00' AND '$td 23:59:59'";
    } elseif ($from_date) {
        $fd = $conn->real_escape_string($from_date);
        $where .= " AND dn.delivery_date >= '$fd 00:00:00'";
    } elseif ($to_date) {
        $td = $conn->real_escape_string($to_date);
        $where .= " AND dn.delivery_date <= '$td 23:59:59'";
    }

    if ($part_number !== '') {
        $s = $conn->real_escape_string($part_number);
        $where .= " AND im.part_number LIKE '%$s%'";
    }
    if ($customer_order !== '') {
        $s = $conn->real_escape_string($customer_order);
        $where .= " AND pp.RefSalesCode LIKE '%$s%'";
    }

    $joins = "
        FROM delivery_notes dn
        INNER JOIN delivery_note_mapping dnm ON dnm.delivery_note_id = dn.id
        LEFT  JOIN client_management cm      ON cm.id = dn.customer_id
        LEFT  JOIN production_planning pp    ON pp.id = dnm.order_number
        LEFT  JOIN item_management im        ON im.id = dnm.part_number
    ";

    /* ── Count ── */
    $countSQL = "
        SELECT COUNT(*) AS total
        FROM delivery_notes dn
        INNER JOIN delivery_note_mapping dnm ON dnm.delivery_note_id = dn.id
        LEFT  JOIN client_management cm      ON cm.id = dn.customer_id
        LEFT  JOIN production_planning pp    ON pp.id = dnm.order_number
        LEFT  JOIN item_management im        ON im.id = dnm.part_number
        $where
    ";
    $countRes      = mysqli_query($conn, $countSQL);
    $total_records = $countRes ? (int)mysqli_fetch_assoc($countRes)['total'] : 0;

    /* ── Main query ── */
    $dataSQL = "
        SELECT
            dn.id                    AS delivery_id,
            dn.note_number           AS delivery_note_number,
            pp.RefSalesCode          AS sales_order_number,
            cm.customer_code,
            im.part_number           AS item_part_number,
            dnm.part_number          AS part_id,
            dnm.description          AS part_description,
            dnm.quantity             AS qty_delivered,
            dn.delivery_date         AS date_delivered
        $joins
        $where
        ORDER BY dn.delivery_date DESC, dn.note_number ASC
        LIMIT $offset, $per_page
    ";

    $dataRes = mysqli_query($conn, $dataSQL);
    if (!$dataRes) {
        $response['message'] = 'Query failed: ' . mysqli_error($conn);
        ob_end_clean();
        echo json_encode($response);
        exit;
    }

    $rows = [];
    $page_total_qty = 0;
    while ($row = mysqli_fetch_assoc($dataRes)) {
        $qty             = (float)($row['qty_delivered'] ?? 0);
        $page_total_qty += $qty;
        $rows[] = [
            'delivery_note_number' => $row['delivery_note_number'] ?? '',
            'sales_order_number'   => $row['sales_order_number']   ?? '',
            'customer_code'        => $row['customer_code']        ?? '',
            'part_number'          => !empty($row['item_part_number']) ? $row['item_part_number'] : ($row['part_id'] ?? ''),
            'part_description'     => $row['part_description']     ?? '',
            'qty_delivered'        => round($qty, 4),
            'date_delivered'       => $row['date_delivered'] ? substr($row['date_delivered'], 0, 10) : null,
        ];
    }

    /* ── Summary ── */
    $sumSQL = "
        SELECT SUM(dnm.quantity) AS total_qty
        FROM delivery_notes dn
        INNER JOIN delivery_note_mapping dnm ON dnm.delivery_note_id = dn.id
        LEFT  JOIN client_management cm      ON cm.id = dn.customer_id
        LEFT  JOIN production_planning pp    ON pp.id = dnm.order_number
        LEFT  JOIN item_management im        ON im.id = dnm.part_number
        $where
    ";
    $sumRes = mysqli_query($conn, $sumSQL);
    $sumRow = $sumRes ? mysqli_fetch_assoc($sumRes) : [];

    $total_pages = max(1, (int)ceil($total_records / $per_page));

    $response['success'] = true;
    $response['data']    = $rows;
    $response['pagination'] = [
        'current_page'  => $page,
        'per_page'      => $per_page,
        'total_records' => $total_records,
        'total_pages'   => $total_pages,
        'showing_from'  => $total_records ? $offset + 1                : 0,
        'showing_to'    => min($offset + $per_page, $total_records),
    ];
    $response['summary'] = [
        'total_qty' => round((float)($sumRow['total_qty'] ?? 0), 2),
    ];

    ob_end_clean();
    echo json_encode($response);
    exit;
}
if ($action === 'get_sales_forecast') {

    $year        = isset($_GET['year'])          ? intval($_GET['year'])                        : (int)date('Y');
    $customer    = trim($_GET['customer_code']   ?? '');

    $where = "WHERE YEAR(pp.req_date) = $year";

    if ($customer !== '') {
        $s      = $conn->real_escape_string($customer);
        $where .= " AND cm.customer_code LIKE '%$s%'";
    }

    /* reusable price expression */
    $price = "
        (CASE
            WHEN pp.item_price IS NOT NULL
             AND pp.item_price <> ''
             AND pp.item_price > 0
            THEN pp.item_price
            ELSE IFNULL(im.standard_cost_ebq, 0)
        END)
    ";

    /* reusable qty expression — note Nov typo in original: pp.shiped_qty fixed to pp.shipped_qty */
    $qty = "(IFNULL(pp.req_qty,0) + IFNULL(pp.shipped_qty,0))";

    $months = ['Jan'=>1,'Feb'=>2,'Mar'=>3,'Apr'=>4,'May'=>5,'Jun'=>6,
               'Jul'=>7,'Aug'=>8,'Sep'=>9,'Oct'=>10,'Nov'=>11,'Dec'=>12];

    $monthCols = '';
    foreach ($months as $label => $num) {
        $monthCols .= "
        SUM(CASE WHEN MONTH(pp.req_date) = $num
            THEN $qty * $price
            ELSE 0 END) AS `$label`,";
    }
    $monthCols = rtrim($monthCols, ',');

    $dataSQL = "
        SELECT
            cm.customer_code,
            $monthCols
        FROM production_planning pp
        LEFT JOIN item_management im  ON im.id = pp.part_id
        LEFT JOIN client_management cm ON cm.id = pp.customer_reff
        $where
        GROUP BY cm.customer_code
        ORDER BY cm.customer_code ASC
    ";

    $dataRes = mysqli_query($conn, $dataSQL);
    if (!$dataRes) {
        $response['message'] = 'Query failed: ' . mysqli_error($conn);
        ob_end_clean();
        echo json_encode($response);
        exit;
    }

    $rows        = [];
    $totals      = array_fill_keys(array_keys($months), 0);
    $grandTotal  = 0;

    while ($row = mysqli_fetch_assoc($dataRes)) {
        $rowTotal = 0;
        $built    = ['customer_code' => $row['customer_code']];
        foreach ($months as $label => $num) {
            $val            = (float)($row[$label] ?? 0);
            $built[$label]  = round($val, 2);
            $totals[$label] += $val;
            $rowTotal       += $val;
            $grandTotal     += $val;
        }
        $built['row_total'] = round($rowTotal, 2);
        $rows[]             = $built;
    }

    /* round totals */
    foreach ($totals as $k => $v) { $totals[$k] = round($v, 2); }

    $response['success'] = true;
    $response['data']    = $rows;
    $response['totals']  = $totals;
    $response['summary'] = [
        'grand_total'    => round($grandTotal, 2),
        'customer_count' => count($rows),
        'year'           => $year,
    ];

    ob_end_clean();
    echo json_encode($response);
    exit;
}
ob_end_clean();
$response['message'] = 'Unknown action: ' . htmlspecialchars($action);
echo json_encode($response);
exit;
?>