<?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';

function ok($d){ echo json_encode($d); exit; }
function err($m,$c=400){ http_response_code($c); echo json_encode(['message'=>$m]); exit; }

// Branch helpers and schema ensure
if (!function_exists('sess_branch_id')) { function sess_branch_id(){ return $_SESSION['branch_id'] ?? null; } }
if (!function_exists('is_admin_user')) { function is_admin_user(){ return has_role(['admin','super_admin']); } }
// @$mysqli->query("ALTER TABLE cut_pieces ADD COLUMN branch_id INT NULL");

switch ($action) {
  case 'list': {
    $q = trim($_GET['q'] ?? '');
    $status = trim($_GET['status'] ?? ''); // usable|unusable|''
    $avail = trim($_GET['avail'] ?? ''); // available|reserved|consumed|'' -> map from is_available/reserved_invoice_item_id
    $loc = isset($_GET['location_id']) && $_GET['location_id'] !== '' ? (int)$_GET['location_id'] : null;
    $branch_id = isset($_GET['branch_id']) && $_GET['branch_id'] !== '' ? (int)$_GET['branch_id'] : null;
    $slab_q = trim($_GET['slab_q'] ?? '');
    $color_id = isset($_GET['color_id']) && $_GET['color_id'] !== '' ? (int)$_GET['color_id'] : null;
    $len_min = ($_GET['len_min'] ?? '') !== '' ? (float)$_GET['len_min'] : null;
    $len_max = ($_GET['len_max'] ?? '') !== '' ? (float)$_GET['len_max'] : null;
    $wid_min = ($_GET['wid_min'] ?? '') !== '' ? (float)$_GET['wid_min'] : null;
    $wid_max = ($_GET['wid_max'] ?? '') !== '' ? (float)$_GET['wid_max'] : null;
    $where = [];$types='';$params=[];
    if ($q !== '') {
      // Numeric search: allow searching by Cut Piece ID / Job ID / Piece No
      $where[] = '(cp.id = ? OR cp.job_id = ? OR cp.piece_no = ?)';
      $types.='iii';
      $params[]=(int)$q;
      $params[]=(int)$q;
      $params[]=(int)$q;
    }
    if ($status !== '' && in_array($status, ['usable','unusable'], true)) { $where[]='cp.status=?'; $types.='s'; $params[]=$status; }
    if ($loc !== null) { $where[]='cp.store_location_id=?'; $types.='i'; $params[]=$loc; }
    if ($avail !== '') {
      if ($avail === 'available') { $where[]='cp.is_available=1'; }
      else if ($avail === 'reserved') { $where[]="cp.is_available=0 AND cp.reserved_invoice_item_id IS NOT NULL AND inv.status='draft'"; }
      else if ($avail === 'consumed') { $where[]="cp.is_available=0 AND (cp.reserved_invoice_item_id IS NULL OR inv.status <> 'draft')"; }
    }
    if ($slab_q !== '') {
      $where[] = '(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("%", ?, "%"))';
      $types.='s';
      $params[]=$slab_q;
    }
    if ($color_id !== null) {
      $where[] = 'col.id = ?';
      $types.='i';
      $params[]=$color_id;
    }
    if ($len_min !== null) { $where[]='cp.length_mm >= ?'; $types.='d'; $params[]=$len_min; }
    if ($len_max !== null) { $where[]='cp.length_mm <= ?'; $types.='d'; $params[]=$len_max; }
    if ($wid_min !== null) { $where[]='cp.width_mm >= ?'; $types.='d'; $params[]=$wid_min; }
    if ($wid_max !== null) { $where[]='cp.width_mm <= ?'; $types.='d'; $params[]=$wid_max; }
    // Branch scope
    if (!is_admin_user()){
      $bid = (int)(sess_branch_id() ?? 0); $where[]='(cp.branch_id = ? OR cp.branch_id IS NULL)'; $types.='i'; $params[]=$bid;
    } else if (!empty($branch_id)) { $where[]='cp.branch_id = ?'; $types.='i'; $params[]=$branch_id; }
    $sql = "SELECT cp.*, sl.name AS location_name,
                   -- Resolve slab_no/po_no via best-known source chain (supports re-cut of re-cut)
                   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,
                   col.id AS color_id,
                   col.name AS color_name,
                   -- 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,
                   inv.id AS invoice_id,
                   inv.invoice_no AS invoice_no,
                   inv.status AS invoice_status
            FROM cut_pieces cp
            LEFT JOIN store_locations sl ON sl.id=cp.store_location_id
            LEFT JOIN invoice_items it ON it.id = cp.reserved_invoice_item_id
            LEFT JOIN invoices inv ON inv.id = it.invoice_id
            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
            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
            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";
    $sql .= "\n            LEFT JOIN colors col ON col.id = COALESCE(fp0.color_id, rmp0_raw.color_id, fp1.color_id, rmp1_raw.color_id, fp2.color_id, rmp2_raw.color_id)";
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= ' ORDER BY cp.id DESC LIMIT 1000';
    if ($where) {
      $stmt = $mysqli->prepare($sql);
      if ($types !== '') { $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; } }
    ok(['data'=>$rows]);
  } break;

  case 'get': {
    $id=(int)($_GET['id'] ?? 0); if(!$id) err('Missing id',400);
    $sql = "SELECT cp.*, sl.name AS location_name,
                   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,
                   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,
                   inv.id AS invoice_id,
                   inv.invoice_no AS invoice_no,
                   inv.status AS invoice_status
            FROM cut_pieces cp
            LEFT JOIN store_locations sl ON sl.id=cp.store_location_id
            LEFT JOIN invoice_items it ON it.id = cp.reserved_invoice_item_id
            LEFT JOIN invoices inv ON inv.id = it.invoice_id
            LEFT JOIN cutting_jobs cj ON cj.id = cp.job_id
            -- Current job source
            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
            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
            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.id=? LIMIT 1";
    $stmt=$mysqli->prepare($sql); $stmt->bind_param('i',$id); $stmt->execute(); $row=$stmt->get_result()->fetch_assoc();
    if(!$row) err('Not found',404);
    if (!is_admin_user()){
      $bid=(int)(sess_branch_id() ?? 0); if ((int)($row['branch_id'] ?? 0) !== $bid) err('Forbidden',403);
    }
    ok(['data'=>$row]);
  } break;

  case 'update': {
    if (!validate_csrf($_POST['csrf'] ?? '')) err('Invalid CSRF', 400);
    $id=(int)($_POST['id'] ?? 0); if(!$id) err('Missing id');
    if (!is_admin_user()){
      $cur = $mysqli->query('SELECT branch_id FROM cut_pieces WHERE id='.(int)$id.' LIMIT 1');
      $cr = $cur ? $cur->fetch_assoc() : null; if(!$cr) err('Not found',404);
      if ((int)($cr['branch_id'] ?? 0) !== (int)(sess_branch_id() ?? 0)) err('Forbidden',403);
    }
    $status = ($_POST['status'] ?? '') ?: null; if ($status && !in_array($status,['usable','unusable'], true)) err('Bad status');
    if (array_key_exists('store_location_id', $_POST)) {
      $loc = ($_POST['store_location_id'] === '' ? null : (int)$_POST['store_location_id']);
    } else {
      $loc = null;
    }
    // Admin-only branch change
    $branch_new = null;
    if (is_admin_user() && array_key_exists('branch_id', $_POST)) {
      $branch_new = ($_POST['branch_id'] === '' ? null : (int)$_POST['branch_id']);
    }
    $length_mm = ($_POST['length_mm'] ?? '') !== '' ? (float)$_POST['length_mm'] : null;
    $width_mm =  ($_POST['width_mm'] ?? '') !== '' ? (float)$_POST['width_mm']  : null;
    $thickness_mm = ($_POST['thickness_mm'] ?? '') !== '' ? (float)$_POST['thickness_mm'] : null;
    if ($length_mm !== null && $length_mm <= 0) err('Invalid length');
    if ($width_mm !== null && $width_mm <= 0) err('Invalid width');
    // recompute area when size changes
    $set = [];$types='';$params=[];
    if ($status !== null){ $set[]='status=?'; $types.='s'; $params[]=$status; }
    if ($loc !== null || isset($_POST['store_location_id'])){ $set[]='store_location_id=?'; $types.='i'; $params[]=$loc; }
    if (is_admin_user() && array_key_exists('branch_id', $_POST)) { $set[]='branch_id=?'; $types.='i'; $params[]=$branch_new; }
    if ($length_mm !== null){ $set[]='length_mm=?'; $types.='d'; $params[]=$length_mm; }
    if ($width_mm !== null){ $set[]='width_mm=?'; $types.='d'; $params[]=$width_mm; }
    if ($thickness_mm !== null){ $set[]='thickness_mm=?'; $types.='d'; $params[]=$thickness_mm; }
    if ($length_mm !== null || $width_mm !== null){ $set[]='area_sq_ft = (length_mm/304.8)*(width_mm/304.8)'; }
    if (!$set) ok(['message'=>'No changes']);
    $sql='UPDATE cut_pieces SET '.implode(',', $set).' WHERE id=?'; $types.='i'; $params[]=$id;
    $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params);
    if(!$stmt->execute()) err($stmt->error,500);
    ok(['message'=>'Updated']);
  } break;

  case 'delete': {
    if (!validate_csrf($_POST['csrf'] ?? '')) err('Invalid CSRF', 400);
    $id=(int)($_POST['id'] ?? 0); if(!$id) err('Missing id');
    if (!is_admin_user()){
      $cur = $mysqli->query('SELECT branch_id FROM cut_pieces WHERE id='.(int)$id.' LIMIT 1');
      $cr = $cur ? $cur->fetch_assoc() : null; if(!$cr) err('Not found',404);
      if ((int)($cr['branch_id'] ?? 0) !== (int)(sess_branch_id() ?? 0)) err('Forbidden',403);
    }
    // Prevent deleting if reserved
    $chk = $mysqli->query('SELECT is_available, reserved_invoice_item_id FROM cut_pieces WHERE id='.(int)$id.' LIMIT 1');
    $row = $chk ? $chk->fetch_assoc() : null; if(!$row) err('Not found',404);
    if (!$row['is_available'] && !empty($row['reserved_invoice_item_id'])) err('Cannot delete a reserved piece', 400);
    $mysqli->query('DELETE FROM cut_pieces WHERE id='.(int)$id);
    ok(['message'=>'Deleted']);
  } break;

  case 'locations': {
    $res=$mysqli->query('SELECT id, name FROM store_locations WHERE is_active=1 ORDER BY name');
    $rows=[]; while($r=$res->fetch_assoc()) $rows[]=$r; ok(['data'=>$rows]);
  } break;

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