<?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 ensure_admin_rmp(){ if (!has_role(['admin','super_admin'])) json_response(['message'=>'Forbidden'], 403); }

// Add product_type_id to raw_material_products if missing
function ensure_rmp_type_migration(mysqli $db){
  $res = $db->query("SHOW COLUMNS FROM raw_material_products LIKE 'product_type_id'");
  if (!$res || !$res->fetch_assoc()) {
    @$db->query("ALTER TABLE raw_material_products ADD COLUMN product_type_id INT NULL AFTER po_id");
    // Add FK if possible (ignore if fails due to existing data)
    @$db->query("ALTER TABLE raw_material_products ADD CONSTRAINT fk_rmp_type FOREIGN KEY (product_type_id) REFERENCES product_types(id) ON DELETE SET NULL");
  }
}

// Add color_id to raw_material_products if missing
function ensure_rmp_color_migration(mysqli $db){
  $res = $db->query("SHOW COLUMNS FROM raw_material_products LIKE 'color_id'");
  if (!$res || !$res->fetch_assoc()) {
    @$db->query("ALTER TABLE raw_material_products ADD COLUMN color_id INT NULL AFTER po_id");
    // Add FK if possible (ignore if fails due to existing data)
    @$db->query("ALTER TABLE raw_material_products ADD CONSTRAINT fk_rmp_color FOREIGN KEY (color_id) REFERENCES colors(id) ON DELETE SET NULL");
  }
}

function csv_output($filename, $rows, $headers){
  header('Content-Type: text/csv');
  header('Content-Disposition: attachment; filename="'.$filename.'"');
  $out = fopen('php://output', 'w');
  fputcsv($out, $headers);
  foreach ($rows as $r) { fputcsv($out, $r); }
  fclose($out); exit;
}

switch ($action) {
  case 'list':
    ensure_rmp_type_migration($mysqli);
    $q = trim($_GET['q'] ?? '');
    $slab_q = trim($_GET['slab_q'] ?? '');
    $color_q = trim($_GET['color_q'] ?? '');
    $status = trim($_GET['status'] ?? '');
    $po_id = (int)($_GET['po_id'] ?? 0);
    $type_id = (int)($_GET['product_type_id'] ?? ($_GET['type_id'] ?? 0));
    $h_min = ($_GET['h_min'] ?? '') !== '' ? (float)$_GET['h_min'] : null;
    $h_max = ($_GET['h_max'] ?? '') !== '' ? (float)$_GET['h_max'] : null;
    $l_min = ($_GET['l_min'] ?? '') !== '' ? (float)$_GET['l_min'] : null;
    $l_max = ($_GET['l_max'] ?? '') !== '' ? (float)$_GET['l_max'] : null;
    $where=[]; $types=''; $params=[];
    if ($q !== '') { $where[] = '(p.order_no LIKE CONCAT("%", ?, "%") OR r.quality LIKE CONCAT("%", ?, "%"))'; $types.='ss'; $params[]=$q; $params[]=$q; }
    if ($slab_q !== '') { $where[] = 'r.slab_no LIKE CONCAT("%", ?, "%")'; $types.='s'; $params[]=$slab_q; }
    if ($color_q !== '') { $where[] = 'c.name LIKE CONCAT("%", ?, "%")'; $types.='s'; $params[]=$color_q; }
    if ($status !== '' && in_array($status, ['received','in_polishing','polished','finished','sold'], true)) { $where[]='r.status=?'; $types.='s'; $params[]=$status; }
    if ($po_id) { $where[]='r.po_id=?'; $types.='i'; $params[]=$po_id; }
    if ($type_id) { $where[]='r.product_type_id=?'; $types.='i'; $params[]=$type_id; }
    if ($h_min !== null) { $where[]='r.height >= ?'; $types.='d'; $params[]=$h_min; }
    if ($h_max !== null) { $where[]='r.height <= ?'; $types.='d'; $params[]=$h_max; }
    if ($l_min !== null) { $where[]='r.length >= ?'; $types.='d'; $params[]=$l_min; }
    if ($l_max !== null) { $where[]='r.length <= ?'; $types.='d'; $params[]=$l_max; }
    $sql = 'SELECT r.id, r.po_id, r.color_id, c.name AS color_name, r.product_type_id, t.name AS product_type_name, p.order_no, r.slab_no, r.height, r.length, r.sq_ft, r.sq_m2, r.quality, r.price, r.value, r.length_remark, r.status, r.created_at
            FROM raw_material_products r
            LEFT JOIN purchase_orders p ON p.id=r.po_id
            LEFT JOIN colors c ON c.id = r.color_id
            LEFT JOIN product_types t ON t.id = r.product_type_id';
    // By default, exclude 'finished' status unless specifically requested
    if ($status === '') {
      $where[] = 'r.status <> \'finished\'';
    }
    if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= ' ORDER BY CAST(r.slab_no AS UNSIGNED) ASC, r.slab_no ASC, r.id DESC';
    if ($where) { $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res = $mysqli->query($sql); }
    $data=[]; while($r=$res->fetch_assoc()){ $data[]=$r; }
    json_response(['data'=>$data]);
    break;

  case 'get':
    $id=(int)($_GET['id'] ?? 0); if(!$id) json_response(['message'=>'Missing id'],400);
    $stmt=$mysqli->prepare('SELECT id, po_id, color_id, product_type_id, slab_no, height, length, sq_ft, sq_m2, quality, price, value, length_remark, status FROM raw_material_products WHERE id=?');
    $stmt->bind_param('i',$id); $stmt->execute(); $row=$stmt->get_result()->fetch_assoc();
    if(!$row) json_response(['message'=>'Not found'],404);
    json_response(['data'=>$row]);
    break;

  case 'stats':
    ensure_rmp_type_migration($mysqli);
    $po_id = (int)($_GET['po_id'] ?? 0);
    $type_id = (int)($_GET['product_type_id'] ?? ($_GET['type_id'] ?? 0));
    $status = trim($_GET['status'] ?? '');
    $slab_q = trim($_GET['slab_q'] ?? '');
    $color_q = trim($_GET['color_q'] ?? '');
    $h_min = ($_GET['h_min'] ?? '') !== '' ? (float)$_GET['h_min'] : null;
    $h_max = ($_GET['h_max'] ?? '') !== '' ? (float)$_GET['h_max'] : null;
    $l_min = ($_GET['l_min'] ?? '') !== '' ? (float)$_GET['l_min'] : null;
    $l_max = ($_GET['l_max'] ?? '') !== '' ? (float)$_GET['l_max'] : null;
    $where=[]; $types=''; $params=[];
    if ($po_id) { $where[]='po_id=?'; $types.='i'; $params[]=$po_id; }
    if ($type_id) { $where[]='product_type_id=?'; $types.='i'; $params[]=$type_id; }
    if ($status !== '' && in_array($status, ['received','in_polishing','polished','finished','sold'], true)) { $where[]='status=?'; $types.='s'; $params[]=$status; }
    if ($slab_q !== '') { $where[]='slab_no LIKE CONCAT("%", ?, "%")'; $types.='s'; $params[]=$slab_q; }
    if ($color_q !== '') { $where[]='color_id IN (SELECT id FROM colors WHERE name LIKE CONCAT("%", ?, "%"))'; $types.='s'; $params[]=$color_q; }
    if ($h_min !== null) { $where[]='height >= ?'; $types.='d'; $params[]=$h_min; }
    if ($h_max !== null) { $where[]='height <= ?'; $types.='d'; $params[]=$h_max; }
    if ($l_min !== null) { $where[]='length >= ?'; $types.='d'; $params[]=$l_min; }
    if ($l_max !== null) { $where[]='length <= ?'; $types.='d'; $params[]=$l_max; }
    $sqlBase = 'FROM raw_material_products';
    if ($where) { $sqlBase .= ' WHERE ' . implode(' AND ', $where); }
    $total = $mysqli->prepare('SELECT COUNT(*) c '.$sqlBase);
    if ($where) { $total->bind_param($types, ...$params); }
    $total->execute(); $c = (int)($total->get_result()->fetch_assoc()['c'] ?? 0);
    $cnt = function($status) use ($mysqli,$sqlBase,$where,$types,$params){
      $sql = 'SELECT COUNT(*) c '.$sqlBase . ($where? ' AND ':' WHERE ') . 'status=?';
      $stmt = $mysqli->prepare($sql);
      $t=$types.'s'; $p=$params; $p[]=$status;
      if ($p) { $stmt->bind_param($t, ...$p); }
      $stmt->execute(); return (int)($stmt->get_result()->fetch_assoc()['c'] ?? 0);
    };
    $sumVal = $mysqli->prepare('SELECT COALESCE(SUM(value),0) s '.$sqlBase);
    if ($where) { $sumVal->bind_param($types, ...$params); }
    $sumVal->execute(); $totalValue = (float)($sumVal->get_result()->fetch_assoc()['s'] ?? 0);

    $sumArea = $mysqli->prepare(
      'SELECT 
        COALESCE(SUM(sq_m2),0) total_sq_m2,
        COALESCE(SUM(CASE WHEN status=\'received\' THEN sq_m2 ELSE 0 END),0) received_sq_m2,
        COALESCE(SUM(CASE WHEN status=\'in_polishing\' THEN sq_m2 ELSE 0 END),0) in_polishing_sq_m2,
        COALESCE(SUM(CASE WHEN status=\'finished\' THEN sq_m2 ELSE 0 END),0) finished_sq_m2
      FROM raw_material_products'
    );
    if ($where) { $sumArea = $mysqli->prepare(
      'SELECT 
        COALESCE(SUM(sq_m2),0) total_sq_m2,
        COALESCE(SUM(CASE WHEN status=\'received\' THEN sq_m2 ELSE 0 END),0) received_sq_m2,
        COALESCE(SUM(CASE WHEN status=\'in_polishing\' THEN sq_m2 ELSE 0 END),0) in_polishing_sq_m2,
        COALESCE(SUM(CASE WHEN status=\'finished\' THEN sq_m2 ELSE 0 END),0) finished_sq_m2
      FROM raw_material_products ' . ($where ? ' WHERE ' . implode(' AND ', $where) : '')
    ); }
    if ($where) { $sumArea->bind_param($types, ...$params); }
    $sumArea->execute();
    $ar = $sumArea->get_result()->fetch_assoc() ?: ['total_sq_m2'=>0,'received_sq_m2'=>0,'in_polishing_sq_m2'=>0,'finished_sq_m2'=>0];
    $conv = 10.76391041671;

    // If status filter is applied, use total_sq_m2 for that status's SQM
    if ($status !== '' && in_array($status, ['received','in_polishing','polished','finished','sold'], true)) {
      $sumAreaFiltered = $mysqli->prepare('SELECT COALESCE(SUM(sq_m2),0) s '.$sqlBase);
      if ($where) { $sumAreaFiltered->bind_param($types, ...$params); }
      $sumAreaFiltered->execute();
      $filteredSqM = (float)($sumAreaFiltered->get_result()->fetch_assoc()['s'] ?? 0);
      $ar[$status . '_sq_m2'] = $filteredSqM;
      $ar[$status . '_sq_ft'] = $filteredSqM * $conv;
      // Also update total to match filtered status
      $ar['total_sq_m2'] = $filteredSqM;
      $ar['total_sq_ft'] = $filteredSqM * $conv;
    }

    json_response([
      'total'=>$c,
      'available'=>$cnt('received'),
      'in_polishing'=>$cnt('in_polishing'),
      'polished'=>$cnt('polished'),
      'finished'=>$cnt('finished'),
      'sold'=>$cnt('sold'),
      'total_value'=>$totalValue,

      'total_sq_m2'=>(float)($ar['total_sq_m2'] ?? 0),
      'total_sq_ft'=>(float)($ar['total_sq_m2'] ?? 0) * $conv,

      'received_sq_m2'=>(float)($ar['received_sq_m2'] ?? 0),
      'received_sq_ft'=>(float)($ar['received_sq_m2'] ?? 0) * $conv,
      'in_polishing_sq_m2'=>(float)($ar['in_polishing_sq_m2'] ?? 0),
      'in_polishing_sq_ft'=>(float)($ar['in_polishing_sq_m2'] ?? 0) * $conv,
      'finished_sq_m2'=>(float)($ar['finished_sq_m2'] ?? 0),
      'finished_sq_ft'=>(float)($ar['finished_sq_m2'] ?? 0) * $conv
    ]);
    break;

  case 'update_status':
    ensure_admin_rmp();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0); $status=trim($_POST['status'] ?? '');
    if(!$id || !in_array($status, ['received','in_polishing','polished','finished','sold'], true)) json_response(['message'=>'Invalid input'],400);
    $stmt=$mysqli->prepare('UPDATE raw_material_products SET status=? WHERE id=?');
    $stmt->bind_param('si',$status,$id);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Updated']);
    break;

  case 'upload_csv':
    ensure_admin_rmp(); ensure_rmp_type_migration($mysqli); ensure_rmp_color_migration($mysqli);
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $po_id = (int)($_POST['po_id'] ?? 0);
    $type_id = (int)($_POST['product_type_id'] ?? 0);
    $color_id = (int)($_POST['color_id'] ?? 0);
    if (!$po_id) json_response(['message'=>'Missing PO'], 400);
    // ensure received PO
    $row = $mysqli->query('SELECT status FROM purchase_orders WHERE id='.(int)$po_id)->fetch_assoc();
    if (!$row || strtolower($row['status'])!=='received') json_response(['message'=>'PO is not received'], 400);
    if (!isset($_FILES['csv']) || !is_uploaded_file($_FILES['csv']['tmp_name'])) json_response(['message'=>'CSV file required'], 400);

    $fh = fopen($_FILES['csv']['tmp_name'], 'r');
    if (!$fh) json_response(['message'=>'Unable to read CSV'], 400);

    // read header
    $header = fgetcsv($fh);
    if (!$header) json_response(['message'=>'Empty CSV'], 400);
    // normalize header keys (lowercase, trim, remove dots/spaces) and strip BOM on first cell
    $norm = [];
    foreach ($header as $i=>$h) {
      $k = strtolower(trim($h));
      if ($i === 0) { $k = preg_replace('/^\xEF\xBB\xBF/', '', $k); }
      $k = str_replace(['.', ' '], ['', '_'], $k);
      $norm[$i]=$k;
    }

    // Build a set of existing slab numbers for this PO (+color if provided) to avoid duplicates
    $existing = [];
    if ($color_id) {
      $stmt = $mysqli->prepare('SELECT LOWER(TRIM(slab_no)) s FROM raw_material_products WHERE po_id=? AND color_id=? AND slab_no IS NOT NULL');
      $stmt->bind_param('ii', $po_id, $color_id);
    } else {
      $stmt = $mysqli->prepare('SELECT LOWER(TRIM(slab_no)) s FROM raw_material_products WHERE po_id=? AND slab_no IS NOT NULL');
      $stmt->bind_param('i', $po_id);
    }
    $stmt->execute(); $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) { if ($row['s']!=='') $existing[$row['s']] = true; }

    $ins = $mysqli->prepare('INSERT INTO raw_material_products (po_id, color_id, product_type_id, slab_no, height, length, sq_ft, sq_m2, quality, price, value, length_remark) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)');
    $inserted=0; $skipped=0; $seenInFile=[];
    while (($row = fgetcsv($fh)) !== false) {
      if (count($row)==1 && trim($row[0])==='') continue; // skip blank lines
      $rec = [];
      foreach ($row as $i=>$v) { $rec[$norm[$i] ?? ('col'.$i)] = trim($v); }
      // Support multiple header variants suppliers use for slab numbers
      // e.g., 'SLAB N', 'SLAB NO', 'SLAB', 'SL. NO'
      $slab_no = $rec['slab_n']
               ?? $rec['slab_no']
               ?? $rec['slab']
               ?? $rec['sl_no']
               ?? $rec['slno']
               ?? $rec['col0'] // fallback: first column if unmapped due to header issues
               ?? null;
      if ($slab_no !== null) { $slab_no = trim((string)$slab_no); if ($slab_no === '') { $slab_no = null; } }

      // If slab_no already exists for this PO (+ color), or repeated in same file, skip
      $slab_key = ($slab_no!==null) ? strtolower(trim($slab_no)) : '';
      if ($slab_key !== '' && (isset($existing[$slab_key]) || isset($seenInFile[$slab_key]))) { $skipped++; continue; }

      $height = isset($rec['height']) && $rec['height']!=='' ? (float)$rec['height'] : null;
      $length = isset($rec['length']) && $rec['length']!=='' ? (float)$rec['length'] : null;
      // Handle SQ FT variants: 'SQ. FT' -> 'sq_ft', or 'sqft'
      $sq_ft_val = $rec['sq_ft'] ?? $rec['sqft'] ?? null;
      $sq_ft = ($sq_ft_val!==null && $sq_ft_val!=='') ? (float)$sq_ft_val : null;
      // Handle SQ.M2 variant: 'SQ.M2' -> 'sqm2' (dot removed), or 'sq_m2'
      $sq_m2_val = $rec['sq_m2'] ?? $rec['sqm2'] ?? null;
      $sq_m2 = ($sq_m2_val!==null && $sq_m2_val!=='') ? (float)$sq_m2_val : null;
      $quality = $rec['quality'] ?? null;
      $price = isset($rec['price']) && $rec['price']!=='' ? (float)$rec['price'] : null;
      $value = isset($rec['value']) && $rec['value']!=='' ? (float)$rec['value'] : null;
      $len_rem = $rec['length_rem'] ?? $rec['length_remark'] ?? null;
      // Types: i (po_id), i (color_id), i (product_type_id), s (slab_no), d (height), d (length), d (sq_ft), d (sq_m2), s (quality), d (price), d (value), s (length_remark)
      $typeParam = $type_id ?: null;
      $colorParam = $color_id ?: null;
      $ins->bind_param('iiisddddsdds', $po_id, $colorParam, $typeParam, $slab_no, $height, $length, $sq_ft, $sq_m2, $quality, $price, $value, $len_rem);
      if (!$ins->execute()) json_response(['message'=>$ins->error], 500);
      if ($slab_key !== '') { $seenInFile[$slab_key]=true; }
      $inserted++;
    }
    fclose($fh);
    json_response(['message'=>'Imported', 'rows_inserted'=>$inserted, 'rows_skipped'=>$skipped]);
    break;

  case 'template':
    $headers = ['SL. NO','SLAB N','HEIGHT','LENGTH','SQ. FT','SQ.M2','Quality','Price','Value','length Rem'];
    csv_output('raw_material_template.csv', [], $headers);
    break;

  case 'delete':
    ensure_admin_rmp();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0); if(!$id) json_response(['message'=>'Missing id'],400);
    $stmt=$mysqli->prepare('DELETE FROM raw_material_products WHERE id=?');
    $stmt->bind_param('i',$id);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Deleted']);
    break;

  case 'bulk_delete':
    ensure_admin_rmp();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $ids_json = $_POST['ids_json'] ?? '[]';
    $ids = json_decode($ids_json, true);
    if (!is_array($ids) || empty($ids)) json_response(['message'=>'No ids provided'], 400);
    // sanitize to integers and unique
    $ids = array_values(array_unique(array_map('intval', $ids)));
    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $types = str_repeat('i', count($ids));
    $sql = 'DELETE FROM raw_material_products WHERE id IN (' . $placeholders . ')';
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param($types, ...$ids);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Bulk deleted', 'deleted_count' => $stmt->affected_rows]);
    break;

  case 'delete_by_po':
    ensure_admin_rmp();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $po_id = (int)($_POST['po_id'] ?? 0);
    $po_no = trim($_POST['po_no'] ?? '');
    if ($po_id <= 0 && $po_no === '') json_response(['message'=>'Provide po_id or po_no'], 400);
    if ($po_id > 0) {
      $stmt = $mysqli->prepare('DELETE FROM raw_material_products WHERE po_id = ?');
      $stmt->bind_param('i', $po_id);
    } else {
      $stmt = $mysqli->prepare('DELETE r FROM raw_material_products r INNER JOIN purchase_orders p ON p.id = r.po_id WHERE p.order_no = ?');
      $stmt->bind_param('s', $po_no);
    }
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'PO-wise deleted', 'deleted_count' => $stmt->affected_rows]);
    break;

  case 'update':
    ensure_admin_rmp(); ensure_rmp_type_migration($mysqli); ensure_rmp_color_migration($mysqli);
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0); if(!$id) json_response(['message'=>'Missing id'],400);
    // Build dynamic update only for fields provided in POST
    $typeMap = [
      'slab_no' => 's',
      'height' => 'd',
      'length' => 'd',
      'sq_ft' => 'd',
      'sq_m2' => 'd',
      'quality' => 's',
      'price' => 'd',
      'value' => 'd',
      'length_remark' => 's',
      'color_id' => 'i',
      'product_type_id' => 'i',
    ];
    $coerce = function($k, $v){
      if ($v === null) return null;
      switch($k){
        case 'height': case 'length': case 'sq_ft': case 'sq_m2': case 'price': case 'value':
          return ($v === '' ? null : (float)$v);
        case 'product_type_id': case 'color_id':
          return ($v === '' ? null : (int)$v);
        default:
          $s = trim((string)$v); return ($s === '' ? null : $s);
      }
    };
    $sets=[]; $types=''; $params=[];
    foreach (['slab_no','height','length','sq_ft','sq_m2','quality','price','value','length_remark','color_id','product_type_id'] as $k){
      if (array_key_exists($k, $_POST)){
        $val = $coerce($k, $_POST[$k]);
        $sets[] = "$k=?"; $types .= ($typeMap[$k] ?? 's'); $params[] = $val;
      }
    }
    if (!$sets) json_response(['message'=>'No changes'], 200);
    $types .= 'i'; $params[] = $id;
    $sql = 'UPDATE raw_material_products SET '.implode(',', $sets).' WHERE id=?';
    $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Updated']);
    break;

  case 'set_type':
    ensure_admin_rmp(); ensure_rmp_type_migration($mysqli);
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0);
    $type_id = isset($_POST['product_type_id']) && $_POST['product_type_id']!=='' ? (int)$_POST['product_type_id'] : null;
    if(!$id) json_response(['message'=>'Missing id'],400);
    $stmt=$mysqli->prepare('UPDATE raw_material_products SET product_type_id=? WHERE id=?');
    $stmt->bind_param('ii', $type_id, $id);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Type updated']);
    break;

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