<?php
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_login();
if (!function_exists('sess_branch_id')) { function sess_branch_id(){ return $_SESSION['branch_id'] ?? null; } }
header('Content-Type: application/json');
$mysqli = db();
$action = $_GET['action'] ?? $_POST['action'] ?? 'sales_summary';

function ymd($s){ return preg_match('/^\d{4}-\d{2}-\d{2}$/',$s) ? $s : null; }

switch ($action) {
  case 'sales_kpis':
    $status = trim($_GET['status'] ?? '');
    $q = trim($_GET['q'] ?? '');
    $payment_term = trim($_GET['payment_term'] ?? '');
    $customer_id = isset($_GET['customer_id']) && $_GET['customer_id'] !== '' ? (int)$_GET['customer_id'] : null;
    $from = ymd($_GET['date_from'] ?? '');
    $to = ymd($_GET['date_to'] ?? '');
    $branch_id = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : null;

    $where = [];
    $types = '';
    $params = [];

    if ($status !== '') {
      $where[] = 'i.status=?';
      $types .= 's';
      $params[] = $status;
    } else {
      // By default, do not count canceled invoices in sales totals
      $where[] = "i.status NOT IN ('cancelled','canceled')";
    }
    if ($q !== '') { $where[] = 'i.invoice_no LIKE CONCAT("%", ?, "%")'; $types .= 's'; $params[] = $q; }
    if ($payment_term !== '') { $where[] = 'i.payment_term=?'; $types .= 's'; $params[] = $payment_term; }
    if ($customer_id !== null) { $where[] = 'i.customer_id=?'; $types .= 'i'; $params[] = $customer_id; }
    if ($from && $to) { $where[] = 'i.invoice_date BETWEEN ? AND ?'; $types .= 'ss'; $params[] = $from; $params[] = $to; }
    else if ($from) { $where[] = 'i.invoice_date >= ?'; $types .= 's'; $params[] = $from; }
    else if ($to) { $where[] = 'i.invoice_date <= ?'; $types .= 's'; $params[] = $to; }

    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (!empty($branch_id)) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$branch_id; }

    $sql = "SELECT
              COUNT(DISTINCT i.id) AS invoices,
              COALESCE(SUM(COALESCE(q.qty,0)),0) AS sales_qty,
              COALESCE(SUM(i.grand_total),0) AS sales_value,
              COALESCE(SUM(COALESCE(p.paid,0)),0) AS paid_amount,
              COALESCE(SUM(GREATEST(i.grand_total-COALESCE(p.paid,0),0)),0) AS outstanding
            FROM invoices i
            LEFT JOIN (
              SELECT invoice_id, SUM(GREATEST(qty-COALESCE(returned_qty,0),0)) qty
              FROM invoice_items
              GROUP BY invoice_id
            ) q ON q.invoice_id=i.id
            LEFT JOIN (
              SELECT invoice_id, SUM(amount) paid
              FROM payments
              GROUP BY invoice_id
            ) p ON p.invoice_id=i.id";
    if ($where) { $sql .= ' WHERE ' . implode(' AND ', $where); }

    $stmt = $mysqli->prepare($sql);
    if (!$stmt) json_response(['message'=>'SQL prepare failed (sales_kpis): '.$mysqli->error], 500);
    if ($types) { $stmt->bind_param($types, ...$params); }
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: [];

    json_response(['data'=>[
      'invoices'=>(int)($row['invoices'] ?? 0),
      'sales_qty'=>(float)($row['sales_qty'] ?? 0),
      'sales_value'=>(float)($row['sales_value'] ?? 0),
      'paid_amount'=>(float)($row['paid_amount'] ?? 0),
      'outstanding_amount'=>(float)($row['outstanding'] ?? 0),
    ]]);
    break;

  case 'stock_available_summary': {
    $finish_id = isset($_GET['finish_id']) && $_GET['finish_id'] !== '' ? (int)$_GET['finish_id'] : null;
    $color_id = isset($_GET['color_id']) && $_GET['color_id'] !== '' ? (int)$_GET['color_id'] : null;
    $po_number = trim($_GET['po_number'] ?? '');
    $supplier = trim($_GET['supplier'] ?? '');

    $branchWhereFp = [];
    $branchTypesFp = '';
    $branchParamsFp = [];

    $branchWhereCp = [];
    $branchTypesCp = '';
    $branchParamsCp = [];

    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) {
        $branchWhereFp[]='(f.branch_id=? OR f.branch_id IS NULL)';
        $branchTypesFp.='i';
        $branchParamsFp[]=$bid;
        $branchWhereCp[]='(cp.branch_id=? OR cp.branch_id IS NULL)';
        $branchTypesCp.='i';
        $branchParamsCp[]=$bid;
      }
    } else {
      $branch_id = isset($_GET['branch_id']) && $_GET['branch_id'] !== '' ? (int)$_GET['branch_id'] : null;
      if (!empty($branch_id)) {
        $branchWhereFp[]='f.branch_id=?';
        $branchTypesFp.='i';
        $branchParamsFp[]=$branch_id;
        $branchWhereCp[]='cp.branch_id=?';
        $branchTypesCp.='i';
        $branchParamsCp[]=$branch_id;
      }
    }

    $rows = [];

    // Raw materials: treat status=received as available stock
    // Default to yakkala branch (branch_id=2) when no branch filter, otherwise use selected branch
    $w = ["r.status='received'"];
    $t = '';
    $p = [];
    if (!has_role(['admin','super_admin'])){
      // Non-admin users: use their branch for raw materials
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $w[]='r.branch_id=?'; $t.='i'; $p[]=$bid; }
      else { $w[]='r.branch_id=2'; } // Default to yakkala if no branch
    } else if (!empty($branch_id)) {
      // Admin users with branch filter: use selected branch
      $w[]='r.branch_id=?'; $t.='i'; $p[]=$branch_id;
    } else {
      // Admin users without branch filter: default to yakkala
      $w[]='r.branch_id=2';
    }
    if ($color_id !== null) { $w[]='r.color_id=?'; $t.='i'; $p[]=$color_id; }
    if ($po_number !== '') { $w[]='po.order_no LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$po_number; }
    if ($supplier !== '') { $w[]='s.name LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$supplier; }
    $sql = "SELECT
              'Raw Material' AS category,
              r.color_id,
              c.name AS color_name,
              NULL AS finish_id,
              NULL AS finish_name,
              COUNT(*) AS item_count,
              ROUND(SUM(COALESCE(r.sq_ft,0)), 3) AS total_sq_ft
            FROM raw_material_products r
            LEFT JOIN colors c ON c.id=r.color_id
            LEFT JOIN purchase_orders po ON po.id=r.po_id
            LEFT JOIN suppliers s ON s.id=po.supplier_id";
    if ($w) $sql .= ' WHERE ' . implode(' AND ', $w);
    $sql .= ' GROUP BY r.color_id, c.name ORDER BY c.name';
    if ($t) {
      $stmt = $mysqli->prepare($sql);
      if (!$stmt) json_response(['message'=>'SQL prepare failed (stock_available_summary raw): '.$mysqli->error], 500);
      $stmt->bind_param($t, ...$p);
      $stmt->execute();
      $res = $stmt->get_result();
    } else {
      $res = $mysqli->query($sql);
    }
    while($r = $res->fetch_assoc()) { $rows[] = $r; }

    // Finished products: sale_status=available only
    $w = ["f.sale_status='available'"];
    $t = '';
    $p = [];
    if ($color_id !== null) { $w[]='f.color_id=?'; $t.='i'; $p[]=$color_id; }
    if ($finish_id !== null) { $w[]='f.finish_id=?'; $t.='i'; $p[]=$finish_id; }
    if ($branchWhereFp) { $w = array_merge($w, $branchWhereFp); $t .= $branchTypesFp; $p = array_merge($p, $branchParamsFp); }
    // Filter by PO/supplier through raw material source
    if ($po_number !== '' || $supplier !== '') {
      $w[]='f.rmp_id IN (SELECT rmp.id FROM raw_material_products rmp LEFT JOIN purchase_orders po ON po.id=rmp.po_id LEFT JOIN suppliers s ON s.id=po.supplier_id WHERE 1=1';
      if ($po_number !== '') { $w[count($w)-1] .= ' AND po.order_no LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$po_number; }
      if ($supplier !== '') { $w[count($w)-1] .= ' AND s.name LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$supplier; }
      $w[count($w)-1] .= ')';
    }

    $sql = "SELECT
              'Finished Product' AS category,
              f.color_id,
              c.name AS color_name,
              f.finish_id,
              fn.name AS finish_name,
              COUNT(*) AS item_count,
              ROUND(SUM(COALESCE(f.final_sq_ft,0)), 3) AS total_sq_ft
            FROM final_products f
            LEFT JOIN colors c ON c.id=f.color_id
            LEFT JOIN finishes fn ON fn.id=f.finish_id";
    if ($w) $sql .= ' WHERE ' . implode(' AND ', $w);
    $sql .= ' GROUP BY f.color_id, c.name, f.finish_id, fn.name ORDER BY c.name, fn.name';

    $stmt = $mysqli->prepare($sql);
    if (!$stmt) json_response(['message'=>'SQL prepare failed (stock_available_summary finished): '.$mysqli->error], 500);
    if ($t) { $stmt->bind_param($t, ...$p); }
    $stmt->execute();
    $res = $stmt->get_result();
    while($r = $res->fetch_assoc()) { $rows[] = $r; }

    // Cut pieces: usable + available. Resolve color/finish per cut piece first, then aggregate.
    $w = ["cp.is_available=1", "cp.status='usable'"];
    $t = '';
    $p = [];
    if ($branchWhereCp) { $w = array_merge($w, $branchWhereCp); $t .= $branchTypesCp; $p = array_merge($p, $branchParamsCp); }
    // Filter by PO/supplier through source chain
    if ($po_number !== '' || $supplier !== '') {
      $w[]='EXISTS (
        SELECT 1 FROM cutting_jobs cj
        LEFT JOIN raw_material_products rmp ON (cj.source_type="raw" AND rmp.id=cj.source_id)
        LEFT JOIN purchase_orders po ON po.id=rmp.po_id
        LEFT JOIN suppliers s ON s.id=po.supplier_id
        WHERE cj.id=cp.job_id AND 1=1';
      if ($po_number !== '') { $w[count($w)-1] .= ' AND po.order_no LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$po_number; }
      if ($supplier !== '') { $w[count($w)-1] .= ' AND s.name LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$supplier; }
      $w[count($w)-1] .= ')';
    }
    $innerWhere = $w ? (' WHERE ' . implode(' AND ', $w)) : '';

    $outerWhere = [];
    if ($finish_id !== null) { $outerWhere[] = 'x.finish_id=?'; $t.='i'; $p[]=$finish_id; }
    if ($color_id !== null) { $outerWhere[] = 'x.color_id=?'; $t.='i'; $p[]=$color_id; }
    $outerWhereSql = $outerWhere ? (' WHERE ' . implode(' AND ', $outerWhere)) : '';

    $sql = "SELECT
              'Cut Piece' AS category,
              x.color_id,
              c.name AS color_name,
              x.finish_id,
              fn.name AS finish_name,
              COUNT(*) AS item_count,
              ROUND(SUM(COALESCE(x.area_sq_ft,0)), 3) AS total_sq_ft
            FROM (
              SELECT
                cp.id,
                cp.area_sq_ft,
                COALESCE(fp_src.color_id, rmp_src.color_id) AS color_id,
                CASE WHEN cj.source_type='finished' THEN fp_src.finish_id ELSE NULL END AS finish_id
              FROM cut_pieces cp
              LEFT JOIN cutting_jobs cj ON cj.id=cp.job_id
              LEFT JOIN final_products fp_src ON (cj.source_type='finished' AND fp_src.id=cj.source_id)
              LEFT JOIN raw_material_products rmp_src ON (cj.source_type='raw' AND rmp_src.id=cj.source_id)
              $innerWhere
              GROUP BY cp.id
            ) x
            LEFT JOIN colors c ON c.id=x.color_id
            LEFT JOIN finishes fn ON fn.id=x.finish_id
            $outerWhereSql
            GROUP BY x.color_id, c.name, x.finish_id, fn.name
            ORDER BY c.name, fn.name";

    $stmt = $mysqli->prepare($sql);
    if (!$stmt) json_response(['message'=>'SQL prepare failed (stock_available_summary cut_pieces): '.$mysqli->error], 500);
    if ($t) { $stmt->bind_param($t, ...$p); }
    $stmt->execute();
    $res = $stmt->get_result();
    while($r = $res->fetch_assoc()) { $rows[] = $r; }

    json_response(['data'=>$rows]);
    }
    break;

  case 'stock_available_detail': {
    $category = trim($_GET['category'] ?? '');
    $finish_id = isset($_GET['finish_id']) && $_GET['finish_id'] !== '' ? (int)$_GET['finish_id'] : null;
    $color_id = isset($_GET['color_id']) && $_GET['color_id'] !== '' ? (int)$_GET['color_id'] : null;
    $po_number = trim($_GET['po_number'] ?? '');
    $supplier = trim($_GET['supplier'] ?? '');

    $branchWhere = [];
    $branchTypes = '';
    $branchParams = [];

    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) {
        $branchWhere[]='(branch_id=? OR branch_id IS NULL)';
        $branchTypes.='i';
        $branchParams[]=$bid;
      }
    } else {
      $branch_id = isset($_GET['branch_id']) && $_GET['branch_id'] !== '' ? (int)$_GET['branch_id'] : null;
      if (!empty($branch_id)) {
        $branchWhere[]='branch_id=?';
        $branchTypes.='i';
        $branchParams[]=$branch_id;
      }
    }

    $rows = [];

    // Raw materials: treat status=received as available stock
    // Default to yakkala branch (branch_id=2) when no branch filter, otherwise use selected branch
    if ($category === '' || $category === 'Raw Material') {
      $w = ["r.status='received'"];
      $t = '';
      $p = [];
      if (!has_role(['admin','super_admin'])){
        // Non-admin users: use their branch for raw materials
        $bid=(int)(sess_branch_id() ?? 0);
        if ($bid > 0) { $w[]='r.branch_id=?'; $t.='i'; $p[]=$bid; }
        else { $w[]='r.branch_id=2'; } // Default to yakkala if no branch
      } else if (!empty($branch_id)) {
        // Admin users with branch filter: use selected branch
        $w[]='r.branch_id=?'; $t.='i'; $p[]=$branch_id;
      } else {
        // Admin users without branch filter: default to yakkala
        $w[]='r.branch_id=2';
      }
      if ($color_id !== null) { $w[]='r.color_id=?'; $t.='i'; $p[]=$color_id; }
      if ($po_number !== '') { $w[]='po.order_no LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$po_number; }
      if ($supplier !== '') { $w[]='s.name LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$supplier; }
      $sql = "SELECT
                'Raw Material' AS category,
                r.id AS item_id,
                r.slab_no,
                po.order_no AS po_number,
                c.name AS color_name,
                NULL AS finish_name,
                r.height,
                r.length,
                r.sq_ft,
                r.quality,
                b.name AS branch_name,
                sl.name AS store_name
              FROM raw_material_products r
              LEFT JOIN colors c ON c.id=r.color_id
              LEFT JOIN purchase_orders po ON po.id=r.po_id
              LEFT JOIN suppliers s ON s.id=po.supplier_id
              LEFT JOIN branches b ON b.id=r.branch_id
              LEFT JOIN store_locations sl ON sl.id=r.store_location_id";
      if ($w) $sql .= ' WHERE ' . implode(' AND ', $w);
      $sql .= ' ORDER BY r.id DESC';
      if ($t) {
        $stmt = $mysqli->prepare($sql);
        if (!$stmt) json_response(['message'=>'SQL prepare failed (stock_available_detail raw): '.$mysqli->error], 500);
        $stmt->bind_param($t, ...$p);
        $stmt->execute();
        $res = $stmt->get_result();
      } else {
        $res = $mysqli->query($sql);
      }
      while($r = $res->fetch_assoc()) { $rows[] = $r; }
    }

    // Finished products: sale_status=available only
    if ($category === '' || $category === 'Finished Product') {
      $w = ["f.sale_status='available'"];
      $t = '';
      $p = [];
      if ($color_id !== null) { $w[]='f.color_id=?'; $t.='i'; $p[]=$color_id; }
      if ($finish_id !== null) { $w[]='f.finish_id=?'; $t.='i'; $p[]=$finish_id; }
      if ($branchWhere) { $w = array_merge($w, $branchWhere); $t .= $branchTypes; $p = array_merge($p, $branchParams); }
      if ($po_number !== '' || $supplier !== '') {
        $w[]='f.rmp_id IN (SELECT rmp.id FROM raw_material_products rmp LEFT JOIN purchase_orders po ON po.id=rmp.po_id LEFT JOIN suppliers s ON s.id=po.supplier_id WHERE 1=1';
        if ($po_number !== '') { $w[count($w)-1] .= ' AND po.order_no LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$po_number; }
        if ($supplier !== '') { $w[count($w)-1] .= ' AND s.name LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$supplier; }
        $w[count($w)-1] .= ')';
      }
      $sql = "SELECT
                'Finished Product' AS category,
                f.id AS item_id,
                r.slab_no,
                po.order_no AS po_number,
                c.name AS color_name,
                fn.name AS finish_name,
                f.final_height AS height,
                f.final_length AS length,
                f.final_sq_ft AS sq_ft,
                f.quality,
                b.name AS branch_name,
                sl.name AS store_name
              FROM final_products f
              LEFT JOIN raw_material_products r ON r.id=f.rmp_id
              LEFT JOIN colors c ON c.id=f.color_id
              LEFT JOIN finishes fn ON fn.id=f.finish_id
              LEFT JOIN purchase_orders po ON po.id=f.po_id
              LEFT JOIN branches b ON b.id=f.branch_id
              LEFT JOIN store_locations sl ON sl.id=f.store_location_id";
      if ($w) $sql .= ' WHERE ' . implode(' AND ', $w);
      $sql .= ' ORDER BY f.id DESC';
      $stmt = $mysqli->prepare($sql);
      if (!$stmt) json_response(['message'=>'SQL prepare failed (stock_available_detail finished): '.$mysqli->error], 500);
      if ($t) { $stmt->bind_param($t, ...$p); }
      $stmt->execute();
      $res = $stmt->get_result();
      while($r = $res->fetch_assoc()) { $rows[] = $r; }
    }

    // Cut pieces: usable + available
    if ($category === '' || $category === 'Cut Piece') {
      $w = ["cp.is_available=1", "cp.status='usable'"];
      $t = '';
      $p = [];
      if ($branchWhere) { $w = array_merge($w, $branchWhere); $t .= $branchTypes; $p = array_merge($p, $branchParams); }
      if ($finish_id !== null) {
        $w[]='EXISTS (
          SELECT 1 FROM cutting_jobs cj
          LEFT JOIN final_products fp ON (cj.source_type="finished" AND fp.id=cj.source_id)
          WHERE cj.id=cp.job_id AND fp.finish_id=?
        )';
        $t.='i'; $p[]=$finish_id;
      }
      if ($color_id !== null) {
        $w[]='EXISTS (
          SELECT 1 FROM cutting_jobs cj
          LEFT JOIN final_products fp ON (cj.source_type="finished" AND fp.id=cj.source_id)
          LEFT JOIN raw_material_products rmp ON (cj.source_type="raw" AND rmp.id=cj.source_id)
          WHERE cj.id=cp.job_id AND COALESCE(fp.color_id, rmp.color_id)=?
        )';
        $t.='i'; $p[]=$color_id;
      }
      if ($po_number !== '' || $supplier !== '') {
        $w[]='EXISTS (
          SELECT 1 FROM cutting_jobs cj
          LEFT JOIN raw_material_products rmp ON (cj.source_type="raw" AND rmp.id=cj.source_id)
          LEFT JOIN purchase_orders po ON po.id=rmp.po_id
          LEFT JOIN suppliers s ON s.id=po.supplier_id
          WHERE cj.id=cp.job_id AND 1=1';
        if ($po_number !== '') { $w[count($w)-1] .= ' AND po.order_no LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$po_number; }
        if ($supplier !== '') { $w[count($w)-1] .= ' AND s.name LIKE CONCAT("%", ?, "%")'; $t.='s'; $p[]=$supplier; }
        $w[count($w)-1] .= ')';
      }
      $sql = "SELECT
                'Cut Piece' AS category,
                cp.id AS item_id,
                COALESCE(rmp_fp.slab_no, rmp_raw.slab_no) AS slab_no,
                COALESCE(po_fp.order_no, po_raw.order_no) AS po_number,
                COALESCE(c_fp.name, c_raw.name) AS color_name,
                fn.name AS finish_name,
                cp.width_mm AS height,
                cp.length_mm AS length,
                cp.area_sq_ft AS sq_ft,
                NULL AS quality,
                b.name AS branch_name,
                sl.name AS store_name
              FROM cut_pieces cp
              LEFT JOIN cutting_jobs cj ON cj.id=cp.job_id
              LEFT JOIN final_products fp ON (cj.source_type='finished' AND fp.id=cj.source_id)
              LEFT JOIN raw_material_products rmp_fp ON rmp_fp.id=fp.rmp_id
              LEFT JOIN purchase_orders po_fp ON po_fp.id=fp.po_id
              LEFT JOIN colors c_fp ON c_fp.id=fp.color_id
              LEFT JOIN finishes fn ON fn.id=fp.finish_id
              LEFT JOIN raw_material_products rmp_raw ON (cj.source_type='raw' AND rmp_raw.id=cj.source_id)
              LEFT JOIN purchase_orders po_raw ON po_raw.id=rmp_raw.po_id
              LEFT JOIN colors c_raw ON c_raw.id=rmp_raw.color_id
              LEFT JOIN branches b ON b.id=cp.branch_id
              LEFT JOIN store_locations sl ON sl.id=cp.store_location_id";
      if ($w) $sql .= ' WHERE ' . implode(' AND ', $w);
      $sql .= ' ORDER BY cp.id DESC';
      $stmt = $mysqli->prepare($sql);
      if (!$stmt) json_response(['message'=>'SQL prepare failed (stock_available_detail cut_pieces): '.$mysqli->error], 500);
      if ($t) { $stmt->bind_param($t, ...$p); }
      $stmt->execute();
      $res = $stmt->get_result();
      while($r = $res->fetch_assoc()) { $rows[] = $r; }
    }

    json_response(['data'=>$rows]);
    }
    break;

  case 'sales_summary':
    $period = $_GET['period'] ?? 'day'; // day|week|month
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');
    $grpExpr = 'DATE(invoice_date)';
    if ($period === 'week') $grpExpr = 'YEARWEEK(invoice_date, 1)';
    if ($period === 'month') $grpExpr = 'DATE_FORMAT(invoice_date, "%Y-%m")';
    $stmt = $mysqli->prepare(
      "SELECT $grpExpr grp, COUNT(*) invoices, SUM(grand_total) total
       FROM invoices
       WHERE status IN ('issued','paid') AND invoice_date BETWEEN ? AND ?
       GROUP BY grp ORDER BY grp"
    );
    $stmt->bind_param('ss', $from, $to);
    $stmt->execute();
    $res = $stmt->get_result(); $rows=[]; while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    break;

  case 'sales_summary_ext': {
    $period = $_GET['period'] ?? 'month'; // day|month|year
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');
    $finish_id = isset($_GET['finish_id']) && $_GET['finish_id'] !== '' ? (int)$_GET['finish_id'] : null;
    $color_id = isset($_GET['color_id']) && $_GET['color_id'] !== '' ? (int)$_GET['color_id'] : null;

    $grpExpr = 'DATE(i.invoice_date)';
    if ($period === 'year') $grpExpr = 'YEAR(i.invoice_date)';
    if ($period === 'month') $grpExpr = 'DATE_FORMAT(i.invoice_date, "%Y-%m")';

    $where = ["i.status IN ('issued','paid')", 'i.invoice_date BETWEEN ? AND ?'];
    $types = 'ss';
    $params = [$from, $to];

    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else {
      $branch_id = isset($_GET['branch_id']) && $_GET['branch_id'] !== '' ? (int)$_GET['branch_id'] : null;
      if (!empty($branch_id)) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    }

    if ($finish_id !== null) {
      $where[]='COALESCE(fp.finish_id, fp_cp0.finish_id, fp_cp1.finish_id) = ?';
      $types.='i';
      $params[]=$finish_id;
    }
    if ($color_id !== null) {
      $where[]='COALESCE(fp.color_id, fp_cp0.color_id, rmp_cp0.color_id, fp_cp1.color_id, rmp_cp1.color_id) = ?';
      $types.='i';
      $params[]=$color_id;
    }

    $sql = "SELECT $grpExpr grp,
                   COUNT(DISTINCT i.id) AS invoices,
                   SUM(GREATEST(ii.qty-COALESCE(ii.returned_qty,0),0)) AS qty,
                   SUM(ii.line_total) AS total,
                   SUM(
                     CASE
                       WHEN ii.final_product_id IS NOT NULL THEN COALESCE(fp.final_sq_ft,0) * GREATEST(ii.qty-COALESCE(ii.returned_qty,0),0)
                       WHEN ii.cut_piece_id IS NOT NULL THEN COALESCE(cp.area_sq_ft,0) * GREATEST(ii.qty-COALESCE(ii.returned_qty,0),0)
                       ELSE 0
                     END
                   ) AS total_sq_ft
            FROM invoice_items ii
            JOIN invoices i ON i.id=ii.invoice_id
            LEFT JOIN final_products fp ON fp.id=ii.final_product_id
            LEFT JOIN cut_pieces cp ON cp.id = ii.cut_piece_id
            LEFT JOIN cutting_jobs cj_cp ON cj_cp.id = cp.job_id
            LEFT JOIN final_products fp_cp0 ON (cj_cp.source_type='finished' AND fp_cp0.id=cj_cp.source_id)
            LEFT JOIN raw_material_products rmp_cp0 ON (cj_cp.source_type='raw' AND rmp_cp0.id=cj_cp.source_id)
            LEFT JOIN cut_pieces cp_src1 ON ((cj_cp.source_type='cut_piece' OR cj_cp.source_type='' OR cj_cp.source_type IS NULL) AND cp_src1.id=cj_cp.source_id)
            LEFT JOIN cutting_jobs cj_cp1 ON cj_cp1.id = cp_src1.job_id
            LEFT JOIN final_products fp_cp1 ON (cj_cp1.source_type='finished' AND fp_cp1.id=cj_cp1.source_id)
            LEFT JOIN raw_material_products rmp_cp1 ON (cj_cp1.source_type='raw' AND rmp_cp1.id=cj_cp1.source_id)";
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= " GROUP BY grp ORDER BY grp";

    $stmt = $mysqli->prepare($sql);
    if (!$stmt) json_response(['message'=>'SQL prepare failed (sales_summary_ext): '.$mysqli->error], 500);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows=[];
    while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    }
    break;

  case 'stock_summary': {
    $period = $_GET['period'] ?? 'month'; // day|month|year
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');
    $finish_id = isset($_GET['finish_id']) && $_GET['finish_id'] !== '' ? (int)$_GET['finish_id'] : null;
    $color_id = isset($_GET['color_id']) && $_GET['color_id'] !== '' ? (int)$_GET['color_id'] : null;

    $grpProd = 'DATE(COALESCE(f.completed_at, f.ready_at))';
    $grpSold = 'DATE(i.invoice_date)';
    if ($period === 'year') {
      $grpProd = 'YEAR(COALESCE(f.completed_at, f.ready_at))';
      $grpSold = 'YEAR(i.invoice_date)';
    }
    if ($period === 'month') {
      $grpProd = 'DATE_FORMAT(COALESCE(f.completed_at, f.ready_at), "%Y-%m")';
      $grpSold = 'DATE_FORMAT(i.invoice_date, "%Y-%m")';
    }

    $prodWhere = ['COALESCE(f.completed_at, f.ready_at) BETWEEN ? AND ?'];
    $prodTypes = 'ss';
    $prodParams = [$from, $to];

    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $prodWhere[]='(f.branch_id=? OR f.branch_id IS NULL)'; $prodTypes.='i'; $prodParams[]=$bid; }
    } else {
      $branch_id = isset($_GET['branch_id']) && $_GET['branch_id'] !== '' ? (int)$_GET['branch_id'] : null;
      if (!empty($branch_id)) { $prodWhere[]='f.branch_id=?'; $prodTypes.='i'; $prodParams[]=$branch_id; }
    }
    if ($finish_id !== null) { $prodWhere[]='f.finish_id=?'; $prodTypes.='i'; $prodParams[]=$finish_id; }
    if ($color_id !== null) { $prodWhere[]='f.color_id=?'; $prodTypes.='i'; $prodParams[]=$color_id; }

    $prodSql = "SELECT $grpProd grp,
                       COUNT(*) produced_items,
                       ROUND(SUM(COALESCE(f.final_sq_ft,0)),3) produced_sq_ft
                FROM final_products f";
    if ($prodWhere) $prodSql .= ' WHERE ' . implode(' AND ', $prodWhere);
    $prodSql .= ' GROUP BY grp';
    $prodStmt = $mysqli->prepare($prodSql);
    if (!$prodStmt) json_response(['message'=>'SQL prepare failed (stock_summary produced): '.$mysqli->error], 500);
    $prodStmt->bind_param($prodTypes, ...$prodParams);
    $prodStmt->execute();
    $prodRes = $prodStmt->get_result();
    $prodMap = [];
    while($r=$prodRes->fetch_assoc()){ $prodMap[(string)$r['grp']] = $r; }

    $soldWhere = ["i.status IN ('issued','paid')", 'i.invoice_date BETWEEN ? AND ?'];
    $soldTypes = 'ss';
    $soldParams = [$from, $to];
    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $soldWhere[]='i.branch_id=?'; $soldTypes.='i'; $soldParams[]=$bid; }
    } else {
      $branch_id = isset($_GET['branch_id']) && $_GET['branch_id'] !== '' ? (int)$_GET['branch_id'] : null;
      if (!empty($branch_id)) { $soldWhere[]='i.branch_id=?'; $soldTypes.='i'; $soldParams[]=$branch_id; }
    }
    if ($finish_id !== null) { $soldWhere[]='COALESCE(fp.finish_id, fp_cp0.finish_id, fp_cp1.finish_id) = ?'; $soldTypes.='i'; $soldParams[]=$finish_id; }
    if ($color_id !== null) { $soldWhere[]='COALESCE(fp.color_id, fp_cp0.color_id, rmp_cp0.color_id, fp_cp1.color_id, rmp_cp1.color_id) = ?'; $soldTypes.='i'; $soldParams[]=$color_id; }

    $soldSql = "SELECT $grpSold grp,
                       SUM(GREATEST(ii.qty-COALESCE(ii.returned_qty,0),0)) sold_qty,
                       SUM(ii.line_total) sold_total,
                       ROUND(SUM(
                         CASE
                           WHEN ii.final_product_id IS NOT NULL THEN COALESCE(fp.final_sq_ft,0) * GREATEST(ii.qty-COALESCE(ii.returned_qty,0),0)
                           WHEN ii.cut_piece_id IS NOT NULL THEN COALESCE(cp.area_sq_ft,0) * GREATEST(ii.qty-COALESCE(ii.returned_qty,0),0)
                           ELSE 0
                         END
                       ),3) sold_sq_ft
                FROM invoice_items ii
                JOIN invoices i ON i.id=ii.invoice_id
                LEFT JOIN final_products fp ON fp.id=ii.final_product_id
                LEFT JOIN cut_pieces cp ON cp.id = ii.cut_piece_id
                LEFT JOIN cutting_jobs cj_cp ON cj_cp.id = cp.job_id
                LEFT JOIN final_products fp_cp0 ON (cj_cp.source_type='finished' AND fp_cp0.id=cj_cp.source_id)
                LEFT JOIN raw_material_products rmp_cp0 ON (cj_cp.source_type='raw' AND rmp_cp0.id=cj_cp.source_id)
                LEFT JOIN cut_pieces cp_src1 ON ((cj_cp.source_type='cut_piece' OR cj_cp.source_type='' OR cj_cp.source_type IS NULL) AND cp_src1.id=cj_cp.source_id)
                LEFT JOIN cutting_jobs cj_cp1 ON cj_cp1.id = cp_src1.job_id
                LEFT JOIN final_products fp_cp1 ON (cj_cp1.source_type='finished' AND fp_cp1.id=cj_cp1.source_id)
                LEFT JOIN raw_material_products rmp_cp1 ON (cj_cp1.source_type='raw' AND rmp_cp1.id=cj_cp1.source_id)";
    if ($soldWhere) $soldSql .= ' WHERE ' . implode(' AND ', $soldWhere);
    $soldSql .= ' GROUP BY grp';
    $soldStmt = $mysqli->prepare($soldSql);
    if (!$soldStmt) json_response(['message'=>'SQL prepare failed (stock_summary sold): '.$mysqli->error], 500);
    $soldStmt->bind_param($soldTypes, ...$soldParams);
    $soldStmt->execute();
    $soldRes = $soldStmt->get_result();
    $soldMap = [];
    while($r=$soldRes->fetch_assoc()){ $soldMap[(string)$r['grp']] = $r; }

    $keys = array_unique(array_merge(array_keys($prodMap), array_keys($soldMap)));
    sort($keys);
    $out=[];
    foreach($keys as $k){
      $p = $prodMap[$k] ?? null;
      $s = $soldMap[$k] ?? null;
      $out[] = [
        'grp' => $k,
        'produced_items' => (int)($p['produced_items'] ?? 0),
        'produced_sq_ft' => (float)($p['produced_sq_ft'] ?? 0),
        'sold_qty' => (float)($s['sold_qty'] ?? 0),
        'sold_sq_ft' => (float)($s['sold_sq_ft'] ?? 0),
        'sold_total' => (float)($s['sold_total'] ?? 0),
      ];
    }
    json_response(['data'=>$out]);
    }
    break;

  case 'sales_by_type':
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');
    $type_id = isset($_GET['type_id']) && $_GET['type_id'] !== '' ? (int)$_GET['type_id'] : null;
    $branch_id = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : null;

    $where = ["i.status IN ('issued','paid')", 'i.invoice_date BETWEEN ? AND ?'];
    $types = 'ss'; $params = [$from, $to];
    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (!empty($branch_id)) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    if (!empty($type_id)) { $where[] = 'fp.type_id = ?'; $types.='i'; $params[] = $type_id; }

    $sql = "SELECT pt.id AS type_id, COALESCE(pt.name,'Unknown') AS type_name, SUM(ii.line_total) AS total
            FROM invoice_items ii
            JOIN invoices i ON i.id = ii.invoice_id
            LEFT JOIN final_products fp ON fp.id = ii.final_product_id
            LEFT JOIN product_types pt ON pt.id = fp.type_id";
    if ($where) $sql .= ' WHERE '.implode(' AND ', $where);
    $sql .= ' GROUP BY pt.id, pt.name ORDER BY total DESC';
    $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute();
    $res=$stmt->get_result(); $rows=[]; while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    break;

  case 'sales_detail':
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');
    $type_id = isset($_GET['type_id']) && $_GET['type_id'] !== '' ? (int)$_GET['type_id'] : null;
    $finish_id = isset($_GET['finish_id']) && $_GET['finish_id'] !== '' ? (int)$_GET['finish_id'] : null;
    $color_id = isset($_GET['color_id']) && $_GET['color_id'] !== '' ? (int)$_GET['color_id'] : null;
    $len_from = $_GET['len_from'] !== null && $_GET['len_from'] !== '' ? (float)$_GET['len_from'] : null;
    $len_to = $_GET['len_to'] !== null && $_GET['len_to'] !== '' ? (float)$_GET['len_to'] : null;
    $wid_from = $_GET['wid_from'] !== null && $_GET['wid_from'] !== '' ? (float)$_GET['wid_from'] : null;
    $wid_to = $_GET['wid_to'] !== null && $_GET['wid_to'] !== '' ? (float)$_GET['wid_to'] : null;
    $branch_id = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : null;

    $where = ['i.status IN (\'issued\',\'paid\')','i.invoice_date BETWEEN ? AND ?'];
    $types = 'ss'; $params = [$from,$to];
    if (!has_role(['admin','super_admin'])){
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$bid; }
    } else if (!empty($branch_id)) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    if ($type_id !== null) { $where[]='(ii.final_product_id IS NOT NULL AND fp.type_id=?)'; $types.='i'; $params[]=$type_id; }

    // Apply attribute filters for final products OR cut pieces (resolved via cutting source chain)
    if ($finish_id !== null) {
      $where[]='((ii.final_product_id IS NOT NULL AND fp.finish_id=?) OR (ii.cut_piece_id IS NOT NULL AND COALESCE(fp_cp0.finish_id, fp_cp1.finish_id)=?))';
      $types.='ii';
      $params[]=$finish_id;
      $params[]=$finish_id;
    }
    if ($color_id !== null) {
      $where[]='((ii.final_product_id IS NOT NULL AND fp.color_id=?) OR (ii.cut_piece_id IS NOT NULL AND COALESCE(fp_cp0.color_id, rmp_cp0.color_id, fp_cp1.color_id, rmp_cp1.color_id)=?))';
      $types.='ii';
      $params[]=$color_id;
      $params[]=$color_id;
    }
    if ($len_from !== null) {
      // UI inputs are in mm; final_products are stored in inches -> convert to mm
      $where[]='((ii.final_product_id IS NOT NULL AND (fp.final_length*25.4)>=?) OR (ii.cut_piece_id IS NOT NULL AND cp.length_mm>=?))';
      $types.='dd';
      $params[]=$len_from;
      $params[]=$len_from;
    }
    if ($len_to !== null) {
      $where[]='((ii.final_product_id IS NOT NULL AND (fp.final_length*25.4)<=?) OR (ii.cut_piece_id IS NOT NULL AND cp.length_mm<=?))';
      $types.='dd';
      $params[]=$len_to;
      $params[]=$len_to;
    }
    if ($wid_from !== null) {
      $where[]='((ii.final_product_id IS NOT NULL AND (fp.final_height*25.4)>=?) OR (ii.cut_piece_id IS NOT NULL AND cp.width_mm>=?))';
      $types.='dd';
      $params[]=$wid_from;
      $params[]=$wid_from;
    }
    if ($wid_to !== null) {
      $where[]='((ii.final_product_id IS NOT NULL AND (fp.final_height*25.4)<=?) OR (ii.cut_piece_id IS NOT NULL AND cp.width_mm<=?))';
      $types.='dd';
      $params[]=$wid_to;
      $params[]=$wid_to;
    }

    $sql = "SELECT i.invoice_no, i.invoice_date, i.customer_id, c.name AS customer_name,
                   ii.description, ii.qty, ii.unit_price, ii.line_total,
                   COALESCE(fp.finish_id, fp_cp0.finish_id, fp_cp1.finish_id) AS finish_id,
                   COALESCE(fp.color_id, fp_cp0.color_id, rmp_cp0.color_id, fp_cp1.color_id, rmp_cp1.color_id) AS color_id,
                   COALESCE(fp.final_length, cp.length_mm) AS length,
                   COALESCE(fp.final_height, cp.width_mm) AS width,
                   COALESCE(f.name, f_cp0.name, f_cp1.name) AS finish_name,
                   COALESCE(co.name, co_cp0.name, co_cp1.name) AS color_name
            FROM invoice_items ii
            JOIN invoices i ON i.id=ii.invoice_id
            JOIN customers c ON c.id=i.customer_id
            LEFT JOIN final_products fp ON fp.id=ii.final_product_id
            LEFT JOIN finishes f ON f.id=fp.finish_id
            LEFT JOIN colors co ON co.id=fp.color_id

            LEFT JOIN cut_pieces cp ON cp.id = ii.cut_piece_id
            LEFT JOIN cutting_jobs cj_cp ON cj_cp.id = cp.job_id
            LEFT JOIN final_products fp_cp0 ON (cj_cp.source_type='finished' AND fp_cp0.id=cj_cp.source_id)
            LEFT JOIN raw_material_products rmp_cp0 ON (cj_cp.source_type='raw' AND rmp_cp0.id=cj_cp.source_id)
            LEFT JOIN finishes f_cp0 ON f_cp0.id = fp_cp0.finish_id
            LEFT JOIN colors co_cp0 ON co_cp0.id = COALESCE(fp_cp0.color_id, rmp_cp0.color_id)

            LEFT JOIN cut_pieces cp_src1 ON ((cj_cp.source_type='cut_piece' OR cj_cp.source_type='' OR cj_cp.source_type IS NULL) AND cp_src1.id=cj_cp.source_id)
            LEFT JOIN cutting_jobs cj_cp1 ON cj_cp1.id = cp_src1.job_id
            LEFT JOIN final_products fp_cp1 ON (cj_cp1.source_type='finished' AND fp_cp1.id=cj_cp1.source_id)
            LEFT JOIN raw_material_products rmp_cp1 ON (cj_cp1.source_type='raw' AND rmp_cp1.id=cj_cp1.source_id)
            LEFT JOIN finishes f_cp1 ON f_cp1.id = fp_cp1.finish_id
            LEFT JOIN colors co_cp1 ON co_cp1.id = COALESCE(fp_cp1.color_id, rmp_cp1.color_id)";
    if ($where) $sql .= ' WHERE '.implode(' AND ',$where);
    $sql .= ' ORDER BY i.invoice_date, i.id, ii.id';
    $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute();
    $res=$stmt->get_result(); $rows=[]; while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    break;

  case 'outstanding':
    $from = ymd($_GET['from'] ?? '') ?: '1970-01-01';
    $to = ymd($_GET['to'] ?? '') ?: '2999-12-31';
    $next_week = isset($_GET['next_week']) && (int)$_GET['next_week']===1;
    $overdue_days = isset($_GET['overdue_days']) ? (int)$_GET['overdue_days'] : 0; // due-date overdue by N days
    $overdue_invoice_days = isset($_GET['overdue_invoice_days']) ? (int)$_GET['overdue_invoice_days'] : 0; // invoice older than N days
    $branch_id = isset($_GET['branch_id']) ? (int)$_GET['branch_id'] : null;

    // Basic due date logic: Cash/Cheque due same day; Credit assumed 30 days.
    $dueDaysExpr = "CASE WHEN i.payment_term='Credit' THEN 30 ELSE 0 END";
    $dueDateExpr = "DATE_ADD(i.invoice_date, INTERVAL $dueDaysExpr DAY)";
    $where = ["i.status IN ('issued','paid')","i.invoice_date BETWEEN ? AND ?"]; $types='ss'; $params=[$from,$to];
    if (!has_role(['admin','super_admin'])){
      $bid=(int)(sess_branch_id() ?? 0); $where[]='i.branch_id=?'; $types.='i'; $params[]=$bid;
    } else if (!empty($branch_id)) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$branch_id; }
    if ($next_week) { $where[] = $dueDateExpr." BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)"; }
    if ($overdue_days > 0) { $where[] = $dueDateExpr." < DATE_SUB(CURDATE(), INTERVAL ".$overdue_days." DAY)"; }
    if ($overdue_invoice_days > 0) { $where[] = "i.invoice_date < DATE_SUB(CURDATE(), INTERVAL ".$overdue_invoice_days." DAY)"; }

    $sql = "SELECT i.id, i.invoice_no, i.invoice_date, i.payment_term, c.name AS customer_name,
                   i.grand_total,
                   COALESCE(p.paid,0) AS total_paid,
                   GREATEST(i.grand_total - COALESCE(p.paid,0), 0) AS balance,
                   $dueDateExpr AS due_date
            FROM invoices i
            JOIN customers c ON c.id=i.customer_id
            LEFT JOIN (SELECT invoice_id, SUM(amount) paid FROM payments GROUP BY invoice_id) p ON p.invoice_id=i.id";
    if ($where) $sql .= ' WHERE '.implode(' AND ',$where);
    $sql .= ' HAVING balance > 0';
    $sql .= ' ORDER BY due_date, i.invoice_date';
    $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute();
    $res=$stmt->get_result(); $rows=[]; while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    break;

  case 'sales_by_quality':
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');
    $sql = "SELECT COALESCE(fp.quality,'Unknown') quality, SUM(ii.line_total) total
            FROM invoice_items ii
            JOIN invoices i ON i.id=ii.invoice_id
            LEFT JOIN final_products fp ON fp.id=ii.final_product_id
            WHERE i.status IN ('issued','paid') AND i.invoice_date BETWEEN ? AND ?
            GROUP BY COALESCE(fp.quality,'Unknown')
            ORDER BY total DESC";
    $stmt=$mysqli->prepare($sql); $stmt->bind_param('ss',$from,$to); $stmt->execute();
    $res=$stmt->get_result(); $rows=[]; while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    break;

  case 'sales_by_color': {
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');

    $where = ["i.status IN ('issued','paid')", 'i.invoice_date BETWEEN ? AND ?'];
    $types = 'ss';
    $params = [$from, $to];

    if (!has_role(['admin','super_admin'])) {
      $bid=(int)(sess_branch_id() ?? 0);
      if ($bid > 0) { $where[]='i.branch_id=?'; $types.='i'; $params[]=$bid; }
    }

    $sql = "SELECT
              COALESCE(fp.color_id, fp_cp0.color_id, rmp_cp0.color_id, fp_cp1.color_id, rmp_cp1.color_id) AS color_id,
              COALESCE(co.name, co_cp0.name, co_cp1.name, 'Unknown') AS color_name,
              SUM(ii.line_total) AS total
            FROM invoice_items ii
            JOIN invoices i ON i.id=ii.invoice_id
            LEFT JOIN final_products fp ON fp.id=ii.final_product_id
            LEFT JOIN colors co ON co.id=fp.color_id

            LEFT JOIN cut_pieces cp ON cp.id = ii.cut_piece_id
            LEFT JOIN cutting_jobs cj_cp ON cj_cp.id = cp.job_id
            LEFT JOIN final_products fp_cp0 ON (cj_cp.source_type='finished' AND fp_cp0.id=cj_cp.source_id)
            LEFT JOIN raw_material_products rmp_cp0 ON (cj_cp.source_type='raw' AND rmp_cp0.id=cj_cp.source_id)
            LEFT JOIN colors co_cp0 ON co_cp0.id = COALESCE(fp_cp0.color_id, rmp_cp0.color_id)

            LEFT JOIN cut_pieces cp_src1 ON ((cj_cp.source_type='cut_piece' OR cj_cp.source_type='' OR cj_cp.source_type IS NULL) AND cp_src1.id=cj_cp.source_id)
            LEFT JOIN cutting_jobs cj_cp1 ON cj_cp1.id = cp_src1.job_id
            LEFT JOIN final_products fp_cp1 ON (cj_cp1.source_type='finished' AND fp_cp1.id=cj_cp1.source_id)
            LEFT JOIN raw_material_products rmp_cp1 ON (cj_cp1.source_type='raw' AND rmp_cp1.id=cj_cp1.source_id)
            LEFT JOIN colors co_cp1 ON co_cp1.id = COALESCE(fp_cp1.color_id, rmp_cp1.color_id)";
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= ' GROUP BY color_id, color_name ORDER BY total DESC';

    $stmt=$mysqli->prepare($sql);
    if (!$stmt) json_response(['message'=>'SQL prepare failed (sales_by_color): '.$mysqli->error], 500);
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $res=$stmt->get_result();
    $rows=[];
    while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    }
    break;

  case 'profit_summary':
    $from = ymd($_GET['from'] ?? '') ?: date('Y-m-01');
    $to = ymd($_GET['to'] ?? '') ?: date('Y-m-d');
    // Profit per invoice: compare sold amount vs raw value of sold finished products
    // Raw value is summed per (invoice_id, final_product_id) to avoid multiplying the same slab value
    $sql = "SELECT
              t.invoice_no,
              SUM(t.sold_total) sold_total,
              SUM(t.raw_value) raw_value,
              SUM(t.sold_total) - SUM(t.raw_value) profit
            FROM (
              SELECT
                i.id AS invoice_id,
                i.invoice_no,
                ii.final_product_id,
                SUM(ii.line_total) sold_total,
                COALESCE(MAX(rmp.value),0) raw_value
              FROM invoice_items ii
              JOIN invoices i ON i.id=ii.invoice_id
              LEFT JOIN final_products fp ON fp.id=ii.final_product_id
              LEFT JOIN raw_material_products rmp ON rmp.id=fp.rmp_id
              WHERE i.status IN ('issued','paid')
                AND i.invoice_date BETWEEN ? AND ?
                AND ii.final_product_id IS NOT NULL
              GROUP BY i.id, i.invoice_no, ii.final_product_id
            ) t
            GROUP BY t.invoice_id, t.invoice_no
            ORDER BY t.invoice_no";
    $stmt=$mysqli->prepare($sql);
    if (!$stmt) json_response(['message'=>'SQL prepare failed (profit_summary): '.$mysqli->error], 500);
    $stmt->bind_param('ss',$from,$to);
    $stmt->execute();
    $rows=[]; $sold_sum=0; $raw_sum=0; $profit_sum=0; $res=$stmt->get_result();
    while($r=$res->fetch_assoc()){
      $rows[]=$r;
      $sold_sum+=(float)$r['sold_total'];
      $raw_sum+=(float)$r['raw_value'];
      $profit_sum+=(float)$r['profit'];
    }
    json_response(['data'=>$rows, 'totals'=>['sold_total'=>$sold_sum,'raw_value'=>$raw_sum,'profit'=>$profit_sum]]);
    break;

  default:
    json_response(['message'=>'Invalid action'],400);
}
