<?php
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_login();
header('Content-Type: application/json');
$mysqli = db();
$action = $_GET['action'] ?? $_POST['action'] ?? 'list_pieces';

function ensure_cutting_schema(mysqli $db){
  // store location for cut slabs
  @$db->query("CREATE TABLE IF NOT EXISTS inventory_moves (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_location_id INT NULL,
    to_location_id INT NULL,
    ref_type VARCHAR(50) NOT NULL,
    ref_id INT NOT NULL,
    quantity DECIMAL(12,3) NOT NULL DEFAULT 0,
    unit VARCHAR(20) NOT NULL DEFAULT 'sqft',
    notes VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB");

  @$db->query("CREATE TABLE IF NOT EXISTS cutting_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    source_type ENUM('raw','finished') NOT NULL,
    source_id INT NOT NULL,
    from_location_id INT NULL,
    to_location_id INT NULL,
    operator_name VARCHAR(150) NULL,
    notes VARCHAR(255) NULL,
    status ENUM('pending','completed','cancelled') NOT NULL DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB");

  @$db->query("CREATE TABLE IF NOT EXISTS cut_pieces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT NOT NULL,
    parent_piece_id INT NULL,
    piece_no TINYINT NOT NULL,
    length_mm DECIMAL(12,2) NOT NULL,
    width_mm DECIMAL(12,2) NOT NULL,
    thickness_mm DECIMAL(12,2) NULL,
    area_sq_ft DECIMAL(12,3) NOT NULL,
    status ENUM('usable','unusable') NOT NULL DEFAULT 'usable',
    store_location_id INT NULL,
    is_available TINYINT(1) NOT NULL DEFAULT 1,
    reserved_invoice_item_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cut_job FOREIGN KEY (job_id) REFERENCES cutting_jobs(id) ON DELETE CASCADE
  ) ENGINE=InnoDB");

  // Add parent_piece_id column if not exists (for re-cut linkage)
  @$db->query("ALTER TABLE cut_pieces ADD COLUMN IF NOT EXISTS parent_piece_id INT NULL AFTER job_id");
  // Add status and location columns if not exists
  @$db->query("ALTER TABLE cut_pieces ADD COLUMN IF NOT EXISTS status ENUM('usable','unusable') NOT NULL DEFAULT 'usable' AFTER area_sq_ft");
  @$db->query("ALTER TABLE cut_pieces ADD COLUMN IF NOT EXISTS store_location_id INT NULL AFTER status");

  // Ensure a default Cut Slab Warehouse exists
  $res = $db->query("SELECT id FROM store_locations WHERE name='Cut Slab Warehouse' LIMIT 1");
  if (!$res || !$res->fetch_assoc()) {
    @$db->query("INSERT INTO store_locations (name, notes, is_active) VALUES ('Cut Slab Warehouse', 'Auto-created for cutting workflow', 1)");
  }
}

function json_ok($data){ echo json_encode($data); exit; }
function json_err($msg,$code=400){ http_response_code($code); echo json_encode(['message'=>$msg]); exit; }

function ensure_cutting_job_source_type(mysqli $db){
  // Older installs had cutting_jobs.source_type ENUM('raw','finished') which causes 'cut_piece' inserts to become ''
  $col = $db->query("SHOW COLUMNS FROM cutting_jobs LIKE 'source_type'");
  $row = $col ? $col->fetch_assoc() : null;
  $type = $row['Type'] ?? '';
  if ($type !== '' && stripos($type, "'cut_piece'") === false) {
    // Expand enum to include cut_piece
    @$db->query("ALTER TABLE cutting_jobs MODIFY source_type ENUM('raw','finished','cut_piece') NOT NULL");
  }
  // Migrate existing bad rows: when source_type is '' but source_id matches a cut piece
  @$db->query("UPDATE cutting_jobs cj SET cj.source_type='cut_piece'
               WHERE (cj.source_type='' OR cj.source_type IS NULL)
                 AND EXISTS (SELECT 1 FROM cut_pieces cp WHERE cp.id=cj.source_id)");
}

ensure_cutting_job_source_type($mysqli);

switch($action){
  case 'list_operators':
    $rows=[];
    $sql = "SELECT id, full_name FROM employees WHERE designation_id=2";
    $q = @$mysqli->query($sql);
    if ($q) { while($r=$q->fetch_assoc()){ $rows[]=$r; } }
    json_ok(['data'=>$rows]);
    break;

  case 'list_sources':
    $type = $_GET['type'] ?? 'raw';
    $q = trim($_GET['q'] ?? '');
    if ($type === 'finished') {
      // Treat finished like raw: return inches, and mirror inches into length_mm/width_mm for compatibility
      // Exclude finished products that have already been used in a cutting job
      $sql = "SELECT fp.id, CONCAT('FP-', fp.id) AS code,
                     fp.final_length AS length_mm, fp.final_height AS width_mm,
                     fp.final_length AS length_in, fp.final_height AS width_in,
                     rmp.slab_no AS slab_no, po.order_no AS po_no,
                     c.name AS color_name, f.name AS finish_name
              FROM final_products fp
              LEFT JOIN raw_material_products rmp ON rmp.id = fp.rmp_id
              LEFT JOIN purchase_orders po ON po.id = fp.po_id
              LEFT JOIN colors c ON c.id = fp.color_id
              LEFT JOIN finishes f ON f.id = fp.finish_id
              WHERE fp.id NOT IN (SELECT source_id FROM cutting_jobs WHERE source_type='finished')";
      if ($q !== '') { $sql .= " AND rmp.slab_no LIKE CONCAT('%', ?, '%')"; }
      $sql .= " ORDER BY fp.id DESC LIMIT 200";
      if ($q !== '') { $stmt=$mysqli->prepare($sql); $stmt->bind_param('s',$q); $stmt->execute(); $res=$stmt->get_result(); }
      else { $res = $mysqli->query($sql); }
    } else if ($type === 'raw') {
      // Raw: join purchase_orders for po_no; return both mm (from feet) and inches (from feet)
      $sql = "SELECT rmp.id, CONCAT('RM-', rmp.id) AS code,
                     (rmp.length) AS length_mm, (rmp.height) AS width_mm, NULL AS thickness_mm,
                     (rmp.length) AS length_in, (rmp.height) AS width_in,
                     rmp.slab_no AS slab_no, po.order_no AS po_no,
                     c.name AS color_name, NULL AS finish_name
              FROM raw_material_products rmp
              LEFT JOIN purchase_orders po ON po.id = rmp.po_id
              LEFT JOIN colors c ON c.id = rmp.color_id";
      $conds = ["rmp.status <> 'finished'"]; // exclude finished from raw sources
      if ($q !== '') { $conds[] = "rmp.slab_no LIKE CONCAT('%', ?, '%')"; }
      if ($conds) { $sql .= ' WHERE ' . implode(' AND ', $conds); }
      $sql .= " ORDER BY rmp.id DESC LIMIT 200";
      if ($q !== '') { $stmt=$mysqli->prepare($sql); $stmt->bind_param('s',$q); $stmt->execute(); $res=$stmt->get_result(); }
      else { $res = $mysqli->query($sql); }
    } else if ($type === 'cut_piece') {
      // From available cut pieces; include slab_no/po_no via source chain
      // Expose inches directly via length_in/width_in (we store inches in length_mm/width_mm columns by convention)
      $sql = "SELECT cp.id, cp.piece_no, cp.parent_piece_id, CONCAT('CP-', cp.id) AS code,
                     NULL AS length_mm, NULL AS width_mm, cp.thickness_mm AS thickness_mm,
                     cp.length_mm AS length_in, cp.width_mm AS width_in,
                     COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no) AS slab_no,
                     COALESCE(po0_fp.order_no, po0_raw.order_no, po1_fp.order_no, po1_raw.order_no, po2_fp.order_no, po2_raw.order_no) AS po_no,
                     COALESCE(c_fp.name, c_raw.name) AS color_name,
                     f.name AS finish_name
              FROM cut_pieces cp
              LEFT JOIN cutting_jobs cj ON cj.id = cp.job_id

              -- Current job source (raw/finished)
              LEFT JOIN final_products fp0 ON (cj.source_type='finished' AND fp0.id=cj.source_id)
              LEFT JOIN raw_material_products rmp0_fp ON rmp0_fp.id = fp0.rmp_id
              LEFT JOIN purchase_orders po0_fp ON po0_fp.id = fp0.po_id
              LEFT JOIN raw_material_products rmp0_raw ON (cj.source_type='raw' AND rmp0_raw.id=cj.source_id)
              LEFT JOIN purchase_orders po0_raw ON po0_raw.id = rmp0_raw.po_id

              -- If this cut piece itself is from a re-cut job, follow the consumed source cut piece's origin
              LEFT JOIN cut_pieces s1 ON ((cj.source_type='cut_piece' OR cj.source_type='' OR cj.source_type IS NULL) AND s1.id=cj.source_id)
              LEFT JOIN cutting_jobs cj1 ON cj1.id = s1.job_id
              LEFT JOIN final_products fp1 ON (cj1.source_type='finished' AND fp1.id=cj1.source_id)
              LEFT JOIN raw_material_products rmp1_fp ON rmp1_fp.id = fp1.rmp_id
              LEFT JOIN purchase_orders po1_fp ON po1_fp.id = fp1.po_id
              LEFT JOIN raw_material_products rmp1_raw ON (cj1.source_type='raw' AND rmp1_raw.id=cj1.source_id)
              LEFT JOIN purchase_orders po1_raw ON po1_raw.id = rmp1_raw.po_id

              -- One more level (re-cut of re-cut)
              LEFT JOIN cut_pieces s2 ON ((cj1.source_type='cut_piece' OR cj1.source_type='' OR cj1.source_type IS NULL) AND s2.id=cj1.source_id)
              LEFT JOIN cutting_jobs cj2 ON cj2.id = s2.job_id
              LEFT JOIN final_products fp2 ON (cj2.source_type='finished' AND fp2.id=cj2.source_id)
              LEFT JOIN raw_material_products rmp2_fp ON rmp2_fp.id = fp2.rmp_id
              LEFT JOIN purchase_orders po2_fp ON po2_fp.id = fp2.po_id
              LEFT JOIN raw_material_products rmp2_raw ON (cj2.source_type='raw' AND rmp2_raw.id=cj2.source_id)
              LEFT JOIN purchase_orders po2_raw ON po2_raw.id = rmp2_raw.po_id

              -- color/finish from the producing job when it was finished
              LEFT JOIN colors c_fp ON c_fp.id = fp0.color_id
              LEFT JOIN finishes f ON f.id = fp0.finish_id
              LEFT JOIN colors c_raw ON c_raw.id = rmp0_raw.color_id
              WHERE cp.is_available=1";
      if ($q !== '') { $sql .= " AND COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no) LIKE CONCAT('%', ?, '%')"; }
      $sql .= " ORDER BY cp.id DESC LIMIT 200";
      if ($q !== '') { $stmt=$mysqli->prepare($sql); $stmt->bind_param('s',$q); $stmt->execute(); $res=$stmt->get_result(); }
      else { $res = $mysqli->query($sql); }
      $rows=[]; if($res){ while($r=$res->fetch_assoc()){ $rows[]=$r; } }
      json_ok(['data'=>$rows]);
      break;
    } else {
      json_err('Invalid source type', 400);
    }
    $rows=[]; if($res){ while($r=$res->fetch_assoc()){ $rows[]=$r; } }
    json_ok(['data'=>$rows]);
    break;

  case 'create_job':
    if (!validate_csrf($_POST['csrf'] ?? '')) json_err('Invalid CSRF', 400);
    $st = trim($_POST['source_type'] ?? 'raw');
    $source_type = in_array($st, ['raw','finished','cut_piece'], true) ? $st : 'raw';
    $source_id = (int)($_POST['source_id'] ?? 0);
    if(!$source_id) json_err('Missing source');
    $operator = trim($_POST['operator_name'] ?? '');
    $operator_id = isset($_POST['operator_id']) ? (int)$_POST['operator_id'] : 0;
    if ($operator_id > 0) {
      $stmtOp = $mysqli->prepare("SELECT full_name FROM employees WHERE id=? AND designation_id=2 LIMIT 1");
      $stmtOp->bind_param('i', $operator_id);
      if ($stmtOp->execute()) {
        $resOp = $stmtOp->get_result();
        if ($rowOp = $resOp->fetch_assoc()) { $operator = $rowOp['full_name']; }
      }
    }
    if ($operator === '') { json_err('Operator is required', 400); }
    $notes = trim($_POST['notes'] ?? '');
    // get cut slab warehouse id
    $loc = $mysqli->query("SELECT id FROM store_locations WHERE name='Cut Slab Warehouse' LIMIT 1");
    $cutLoc = ($loc && ($x=$loc->fetch_assoc())) ? (int)$x['id'] : null;

    // Try to read thickness from source when possible
    $thk = null;
     if ($source_type==='cut_piece') {
      $q=$mysqli->query("SELECT thickness_mm FROM cut_pieces WHERE id=".(int)$source_id." LIMIT 1");
      if($q && ($r=$q->fetch_assoc())) $thk = $r['thickness_mm'];
    }

    $stmt = $mysqli->prepare("INSERT INTO cutting_jobs (job_date, source_type, source_id, from_location_id, to_location_id, operator_name, notes, status) VALUES (CURRENT_DATE,?,?,?,?,?,?, 'completed')");
    $fromLoc = null; // unknown for now
    $stmt->bind_param('siisss', $source_type, $source_id, $fromLoc, $cutLoc, $operator, $notes);
    if(!$stmt->execute()) json_err($stmt->error,500);
    $job_id = $mysqli->insert_id;

    // If re-cutting a cut piece, consume the source and log movement out
    $parent_piece_id = null;
    if ($source_type === 'cut_piece') {
      $parent_piece_id = $source_id;
      $px = $mysqli->query('SELECT area_sq_ft FROM cut_pieces WHERE id='.(int)$source_id.' LIMIT 1');
      $pArea = 0.0; if ($px && ($pr=$px->fetch_assoc())) $pArea = (float)$pr['area_sq_ft'];
      // mark parent as unavailable (consumed)
      @$mysqli->query('UPDATE cut_pieces SET is_available=0 WHERE id='.(int)$source_id);
      // movement out from Cut Slab Warehouse
      if ($cutLoc && $pArea>0) {
        $mv2 = $mysqli->prepare("INSERT INTO inventory_moves (from_location_id, to_location_id, ref_type, ref_id, quantity, unit, notes) VALUES (?, NULL, 'cutting_job', ?, ?, 'sqft', 'Cut piece consumed by re-cut')");
        $mv2->bind_param('iid', $cutLoc, $job_id, $pArea);
        @$mv2->execute();
      }
    }

    // If re-cutting a finished product, mark it as cut
    if ($source_type === 'finished' && $source_id) {
      @$mysqli->query("UPDATE final_products SET sale_status='cut' WHERE id=".(int)$source_id.' LIMIT 1');
    }

    // pieces: prefer dynamic JSON, fallback to legacy inputs. Support inches (length_in/width_in) or mm (length_mm/width_mm)
    $pieces = [];
    $total_area_sqft = 0.0;
    if (!empty($_POST['pieces_json'])) {
      $decoded = json_decode($_POST['pieces_json'], true);
      if (is_array($decoded)) {
        foreach ($decoded as $row) {
          $no = isset($row['piece_no']) ? (int)$row['piece_no'] : (count($pieces)+1);
          $Lmm = 0; $Wmm = 0; $area_sqft = 0.0;
          if (isset($row['length_in']) && isset($row['width_in'])) {
            $Lin = (float)$row['length_in'];
            $Win = (float)$row['width_in'];
            if ($Lin>0 && $Win>0) {
              // Store inches as-is (no conversion) for length_mm/width_mm columns by convention
              $Lmm = $Lin;
              $Wmm = $Win;
              $area_sqft = ($Lin/12.0) * ($Win/12.0);
            }
          } else if (isset($row['length_mm']) && isset($row['width_mm'])) {
            $Lmm = (float)$row['length_mm'];
            $Wmm = (float)$row['width_mm'];
            if ($Lmm>0 && $Wmm>0) {
              $area_sqft = ($Lmm/304.8) * ($Wmm/304.8);
            }
          }
          $pieces[] = [$no, $Lmm, $Wmm, $area_sqft];
          $total_area_sqft += $area_sqft;
        }
      }
    }
    if (empty($pieces)) {
      // Legacy form fields (mm)
      $p1L = (float)($_POST['p1_length_mm'] ?? 0); $p1W = (float)($_POST['p1_width_mm'] ?? 0);
      $p2L = (float)($_POST['p2_length_mm'] ?? 0); $p2W = (float)($_POST['p2_width_mm'] ?? 0);
      $pieces = [
        [1, $p1L, $p1W, ($p1L>0 && $p1W>0) ? ($p1L/304.8)*($p1W/304.8) : 0],
        [2, $p2L, $p2W, ($p2L>0 && $p2W>0) ? ($p2L/304.8)*($p2W/304.8) : 0],
      ];
    }
    // Validate at least one piece is provided
    $validCount = 0; foreach($pieces as $pc){ if (($pc[1]??0)>0 && ($pc[2]??0)>0) { $validCount++; } }
    if ($validCount === 0) { json_err('Please add at least one piece with valid Length and Width.', 400); }

    // Server-side parent area validation
    $parent_area_sqft = 0.0;
    if ($source_type === 'raw') {
      $q = $mysqli->query('SELECT length, height FROM raw_material_products WHERE id='.(int)$source_id.' LIMIT 1');
      if ($q && ($r=$q->fetch_assoc())) {
        $L = (float)$r['length']; $W = (float)$r['height'];
        if ($L>0 && $W>0) $parent_area_sqft = ($L/12.0)*($W/12.0);
      }
    } else if ($source_type === 'finished') {
      $q = $mysqli->query('SELECT final_length, final_height FROM final_products WHERE id='.(int)$source_id.' LIMIT 1');
      if ($q && ($r=$q->fetch_assoc())) {
        $Lmm = (float)$r['final_length']; $Wmm = (float)$r['final_height'];
        if ($Lmm>0 && $Wmm>0) { $L = $Lmm; $W = $Wmm; $parent_area_sqft = ($L/12.0)*($W/12.0); }
      }
    } else if ($source_type === 'cut_piece') {
      $q = $mysqli->query('SELECT area_sq_ft FROM cut_pieces WHERE id='.(int)$source_id.' LIMIT 1');
      if ($q && ($r=$q->fetch_assoc())) { $parent_area_sqft = (float)$r['area_sq_ft']; }
    }
    $tol = 0.01;
    if ($source_type === 'cut_piece') {
      if ($parent_area_sqft>0 && ($total_area_sqft - $parent_area_sqft) > $tol) {
        json_err('Total pieces area exceeds parent piece area', 400);
      }
    } else {
      if ($parent_area_sqft>0 && abs($total_area_sqft - $parent_area_sqft) > $tol) {
        json_err('Total pieces area must match source slab area', 400);
      }
    }

    $ins = $mysqli->prepare("INSERT INTO cut_pieces (job_id, parent_piece_id, piece_no, length_mm, width_mm, thickness_mm, area_sq_ft, status, store_location_id) VALUES (?,?,?,?,?,?,?,?,?)");
    foreach($pieces as $pc){
      [$no,$Lmm,$Wmm,$area_sqft] = $pc;
      if ($Lmm>0 && $Wmm>0){
        // parent_piece_id is set only for re-cut, else NULL
        $status = 'usable';
        $locId = $cutLoc ?: null;
        if ($parent_piece_id) {
          $ins->bind_param('iiiddddsi', $job_id, $parent_piece_id, $no, $Lmm, $Wmm, $thk, $area_sqft, $status, $locId);
        } else {
          $null = null;
          $ins->bind_param('iiiddddsi', $job_id, $null, $no, $Lmm, $Wmm, $thk, $area_sqft, $status, $locId);
        }
        if(!$ins->execute()) json_err($ins->error,500);
      }
    }

    // Mark raw slab as finished so it is not selectable again as a source
    if ($source_type === 'raw' && $source_id) {
      @$mysqli->query('UPDATE raw_material_products SET status=\'finished\' WHERE id='.(int)$source_id.' LIMIT 1');
    }

    // movement logs (simple)
    $total_area = 0.0;
    $rs = $mysqli->query("SELECT SUM(area_sq_ft) s FROM cut_pieces WHERE job_id=".(int)$job_id);
    if($rs && ($r=$rs->fetch_assoc())) $total_area = (float)$r['s'];
    if ($cutLoc) {
      $mv = $mysqli->prepare("INSERT INTO inventory_moves (from_location_id, to_location_id, ref_type, ref_id, quantity, unit, notes) VALUES (NULL, ?, 'cutting_job', ?, ?, 'sqft', 'Cut pieces created')");
      $mv->bind_param('iid', $cutLoc, $job_id, $total_area);
      @$mv->execute();
    }

    json_ok(['message'=>'Cutting job created','job_id'=>$job_id]);
    break;

  case 'list_jobs':
    $q = trim($_GET['q'] ?? '');
    $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 10;
    if ($limit <= 0) $limit = 10;
    if ($limit > 500) $limit = 500;
    $sql = "SELECT cj.id, cj.job_date, cj.source_type, cj.source_id, cj.operator_name, cj.status,
                   CASE
                     WHEN cj.source_type='raw' THEN CONCAT('RM-', cj.source_id, ' / Slab ', COALESCE(rmp.slab_no,''))
                     WHEN cj.source_type='finished' THEN CONCAT('FP-', cj.source_id, ' / Slab ', COALESCE(rmp_fp.slab_no,''))
                     WHEN (cj.source_type='cut_piece' OR cj.source_type='' OR cj.source_type IS NULL) THEN CONCAT('CP-', cj.source_id, ' / ',
                       COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no, ''),
                       '/', LPAD(cp.piece_no,3,'0')
                     )
                     ELSE CONCAT(cj.source_type,'-',cj.source_id)
                   END AS source_label
            FROM cutting_jobs cj
            LEFT JOIN raw_material_products rmp ON (cj.source_type='raw' AND rmp.id=cj.source_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 cut_pieces cp ON ((cj.source_type='cut_piece' OR cj.source_type='' OR cj.source_type IS NULL) AND cp.id=cj.source_id)
            LEFT JOIN cutting_jobs cj0 ON cj0.id = cp.job_id
            LEFT JOIN final_products fp0 ON (cj0.source_type='finished' AND fp0.id=cj0.source_id)
            LEFT JOIN raw_material_products rmp0_fp ON rmp0_fp.id = fp0.rmp_id
            LEFT JOIN raw_material_products rmp0_raw ON (cj0.source_type='raw' AND rmp0_raw.id=cj0.source_id)

            LEFT JOIN cut_pieces p1 ON p1.id = cp.parent_piece_id
            LEFT JOIN cutting_jobs cj1 ON cj1.id = p1.job_id
            LEFT JOIN final_products fp1 ON (cj1.source_type='finished' AND fp1.id=cj1.source_id)
            LEFT JOIN raw_material_products rmp1_fp ON rmp1_fp.id = fp1.rmp_id
            LEFT JOIN raw_material_products rmp1_raw ON (cj1.source_type='raw' AND rmp1_raw.id=cj1.source_id)

            LEFT JOIN cut_pieces p2 ON p2.id = p1.parent_piece_id
            LEFT JOIN cutting_jobs cj2 ON cj2.id = p2.job_id
            LEFT JOIN final_products fp2 ON (cj2.source_type='finished' AND fp2.id=cj2.source_id)
            LEFT JOIN raw_material_products rmp2_fp ON rmp2_fp.id = fp2.rmp_id
            LEFT JOIN raw_material_products rmp2_raw ON (cj2.source_type='raw' AND rmp2_raw.id=cj2.source_id)
            WHERE 1=1";
    $types = '';
    $params = [];
    if ($q !== '') {
      $like = '%'.$q.'%';
      $sql .= " AND (CAST(cj.id AS CHAR) LIKE ?
                    OR CAST(cj.source_id AS CHAR) LIKE ?
                    OR cj.operator_name LIKE ?
                    OR cj.job_date LIKE ?
                    OR COALESCE(rmp.slab_no, rmp_fp.slab_no, rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no) LIKE ?)";
      $types .= 'sssss';
      $params[] = $like;
      $params[] = $like;
      $params[] = $like;
      $params[] = $like;
      $params[] = $like;
    }
    $sql .= " ORDER BY cj.id DESC LIMIT ".$limit;
    if ($types !== '') {
      $stmt = $mysqli->prepare($sql);
      $stmt->bind_param($types, ...$params);
      $stmt->execute();
      $res = $stmt->get_result();
    } else {
      $res = $mysqli->query($sql);
    }
    $rows=[]; if($res){ while($r=$res->fetch_assoc()){ $rows[]=$r; } }
    json_ok(['data'=>$rows]);
    break;

  case 'delete_job':
    if (!validate_csrf($_POST['csrf'] ?? '')) json_err('Invalid CSRF', 400);
    $id = (int)($_POST['id'] ?? 0); if(!$id) json_err('Missing id');
    // Read job first
    $job = $mysqli->query('SELECT id, source_type, source_id FROM cutting_jobs WHERE id='.(int)$id.' LIMIT 1');
    $row = $job ? $job->fetch_assoc() : null; if(!$row) json_err('Not found', 404);
    // If re-cut, restore parent cut piece availability
    if (($row['source_type'] ?? '') === 'cut_piece') {
      @$mysqli->query('UPDATE cut_pieces SET is_available=1 WHERE id='.(int)$row['source_id']);
    }
    // If job consumed a raw slab, restore its status so it can be selected again
    if (($row['source_type'] ?? '') === 'raw') {
      @$mysqli->query('UPDATE raw_material_products SET status=\'received\' WHERE id='.(int)$row['source_id'].' LIMIT 1');
    }
    // If job consumed a finished product (recut), restore its sale_status from 'cut' to 'available'
    if (($row['source_type'] ?? '') === 'finished') {
      @$mysqli->query("UPDATE final_products SET sale_status='available' WHERE id=".(int)$row['source_id'].' LIMIT 1');
    }
    // Clean inventory moves for this job
    @$mysqli->query("DELETE FROM inventory_moves WHERE ref_type='cutting_job' AND ref_id=".(int)$id);
    // Delete job (FK will cascade delete cut_pieces)
    $mysqli->query('DELETE FROM cutting_jobs WHERE id='.(int)$id);
    json_ok(['message'=>'Deleted']);
    break;

  case 'list_pieces':
    // Expose inches and build piece_code using ultimate parent slab and PO, including for re-cuts
    // Also expose parent piece number (slab/NNN) for re-cuts so UI can display parent piece id if needed
    $q = trim($_GET['q'] ?? '');
    $limit = isset($_GET['limit']) ? (int)$_GET['limit'] : 10;
    if ($limit <= 0) $limit = 10;
    if ($limit > 500) $limit = 500;
    $sql = "SELECT cp.id, cp.job_id, cp.piece_no,
                   cp.length_mm AS length_in, cp.width_mm AS width_in, cp.thickness_mm, cp.area_sq_ft, cp.is_available,
                   -- Resolve slab_no and po_no via the best-known source chain:
                   -- 1) current job's source (raw/finished)
                   -- 2) parent piece's job source
                   -- 3) grandparent piece's job source
                   COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no) AS slab_no,
                   COALESCE(po0_fp.order_no, po0_raw.order_no, po1_fp.order_no, po1_raw.order_no, po2_fp.order_no, po2_raw.order_no) AS po_no,
                   -- Explicit lineage fields
                   CASE
                     WHEN cp.parent_piece_id IS NOT NULL THEN CONCAT(
                       COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no),
                       '/', LPAD(p1.piece_no,3,'0')
                     )
                     ELSE CONCAT(
                       COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no),
                       '/', LPAD(cp.piece_no,3,'0')
                     )
                   END AS cut_piece_no,
                   CASE
                     WHEN cp.parent_piece_id IS NOT NULL THEN CONCAT(
                       COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no),
                       '/', LPAD(cp.piece_no,3,'0')
                     )
                     ELSE NULL
                   END AS recut_piece_no,
                   CONCAT(
                     COALESCE(po0_fp.order_no, po0_raw.order_no, po1_fp.order_no, po1_raw.order_no, po2_fp.order_no, po2_raw.order_no),
                     ' ',
                     COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no),
                     '/',
                     LPAD(cp.piece_no,3,'0')
                   ) AS piece_code,
                   CASE WHEN cp.parent_piece_id IS NOT NULL THEN CONCAT(
                        COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no),
                        '/', LPAD(p1.piece_no,3,'0')
                   ) ELSE NULL END AS parent_piece_no
            FROM cut_pieces cp
            LEFT JOIN cutting_jobs cj ON cj.id = cp.job_id
            -- Current job source (raw/finished)
            LEFT JOIN final_products fp0 ON (cj.source_type='finished' AND fp0.id=cj.source_id)
            LEFT JOIN raw_material_products rmp0_fp ON rmp0_fp.id = fp0.rmp_id
            LEFT JOIN purchase_orders po0_fp ON po0_fp.id = fp0.po_id
            LEFT JOIN raw_material_products rmp0_raw ON (cj.source_type='raw' AND rmp0_raw.id=cj.source_id)
            LEFT JOIN purchase_orders po0_raw ON po0_raw.id = rmp0_raw.po_id

            -- Parent piece chain (re-cut)
            LEFT JOIN cut_pieces p1 ON p1.id = cp.parent_piece_id
            LEFT JOIN cutting_jobs cj1 ON cj1.id = p1.job_id
            LEFT JOIN final_products fp1 ON (cj1.source_type='finished' AND fp1.id=cj1.source_id)
            LEFT JOIN raw_material_products rmp1_fp ON rmp1_fp.id = fp1.rmp_id
            LEFT JOIN purchase_orders po1_fp ON po1_fp.id = fp1.po_id
            LEFT JOIN raw_material_products rmp1_raw ON (cj1.source_type='raw' AND rmp1_raw.id=cj1.source_id)
            LEFT JOIN purchase_orders po1_raw ON po1_raw.id = rmp1_raw.po_id

            -- Grandparent piece chain (re-cut of re-cut)
            LEFT JOIN cut_pieces p2 ON p2.id = p1.parent_piece_id
            LEFT JOIN cutting_jobs cj2 ON cj2.id = p2.job_id
            LEFT JOIN final_products fp2 ON (cj2.source_type='finished' AND fp2.id=cj2.source_id)
            LEFT JOIN raw_material_products rmp2_fp ON rmp2_fp.id = fp2.rmp_id
            LEFT JOIN purchase_orders po2_fp ON po2_fp.id = fp2.po_id
            LEFT JOIN raw_material_products rmp2_raw ON (cj2.source_type='raw' AND rmp2_raw.id=cj2.source_id)
            LEFT JOIN purchase_orders po2_raw ON po2_raw.id = rmp2_raw.po_id
            WHERE cp.is_available=1";
    $types='';
    $params=[];
    if ($q !== '') {
      $like = '%'.$q.'%';
      $sql .= " AND (
                  CAST(cp.id AS CHAR) LIKE ?
                  OR CAST(cp.job_id AS CHAR) LIKE ?
                  OR CAST(cp.piece_no AS CHAR) LIKE ?
                  OR COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no) LIKE ?
                  OR COALESCE(po0_fp.order_no, po0_raw.order_no, po1_fp.order_no, po1_raw.order_no, po2_fp.order_no, po2_raw.order_no) LIKE ?
                  OR CONCAT(
                    COALESCE(po0_fp.order_no, po0_raw.order_no, po1_fp.order_no, po1_raw.order_no, po2_fp.order_no, po2_raw.order_no),
                    ' ',
                    COALESCE(rmp0_fp.slab_no, rmp0_raw.slab_no, rmp1_fp.slab_no, rmp1_raw.slab_no, rmp2_fp.slab_no, rmp2_raw.slab_no),
                    '/',
                    LPAD(cp.piece_no,3,'0')
                  ) LIKE ?
                )";
      $types .= 'ssssss';
      $params[]=$like;
      $params[]=$like;
      $params[]=$like;
      $params[]=$like;
      $params[]=$like;
      $params[]=$like;
    }
    $sql .= " ORDER BY cp.id DESC LIMIT ".$limit;
    if ($types !== '') {
      $stmt = $mysqli->prepare($sql);
      $stmt->bind_param($types, ...$params);
      $stmt->execute();
      $res = $stmt->get_result();
    } else {
      $res = $mysqli->query($sql);
    }
    $rows=[]; if($res){ while($r=$res->fetch_assoc()){ $rows[]=$r; } }
    json_ok(['data'=>$rows]);
    break;

  case 'list_available_pieces':
    $res = $mysqli->query("SELECT id, job_id, piece_no, length_mm, width_mm, thickness_mm, area_sq_ft FROM cut_pieces WHERE is_available=1 AND status='usable' ORDER BY id DESC LIMIT 500");
    $rows=[]; if($res){ while($r=$res->fetch_assoc()){ $rows[]=$r; } }
    json_ok(['data'=>$rows]);
    break;

  case 'reserve_piece':
    if (!validate_csrf($_POST['csrf'] ?? '')) json_err('Invalid CSRF', 400);
    $id = (int)($_POST['id'] ?? 0); if(!$id) json_err('Missing id');
    $invoice_item_id = isset($_POST['invoice_item_id']) ? (int)$_POST['invoice_item_id'] : null;
    $stmt = $mysqli->prepare('UPDATE cut_pieces SET is_available=0, reserved_invoice_item_id = ? WHERE id=? AND is_available=1');
    $stmt->bind_param('ii', $invoice_item_id, $id);
    if(!$stmt->execute() || !$stmt->affected_rows) json_err('Unable to reserve', 400);
    json_ok(['message'=>'Reserved']);
    break;

  case 'unreserve_piece':
    if (!validate_csrf($_POST['csrf'] ?? '')) json_err('Invalid CSRF', 400);
    $id = (int)($_POST['id'] ?? 0); if(!$id) json_err('Missing id');
    $stmt = $mysqli->prepare('UPDATE cut_pieces SET is_available=1, reserved_invoice_item_id = NULL WHERE id=?');
    $stmt->bind_param('i', $id);
    if(!$stmt->execute()) json_err('Unable to unreserve', 400);
    json_ok(['message'=>'Unreserved']);
    break;

  default:
    json_err('Invalid action',400);
}
