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

// Migration: ensure final_products has pre size columns
function ensure_fp_pre_migration(mysqli $db){
  $c1 = $db->query("SHOW COLUMNS FROM final_products LIKE 'pre_height'");
  if (!$c1 || !$c1->fetch_assoc()) {
    @$db->query("ALTER TABLE final_products ADD COLUMN pre_height DOUBLE NULL AFTER polishing_job_id");
  }
  $c2 = $db->query("SHOW COLUMNS FROM final_products LIKE 'pre_length'");
  if (!$c2 || !$c2->fetch_assoc()) {
    @$db->query("ALTER TABLE final_products ADD COLUMN pre_length DOUBLE NULL AFTER pre_height");
  }
  $c3 = $db->query("SHOW COLUMNS FROM final_products LIKE 'pre_sq_ft'");
  if (!$c3 || !$c3->fetch_assoc()) {
    @$db->query("ALTER TABLE final_products ADD COLUMN pre_sq_ft DOUBLE NULL AFTER pre_length");
  }
  $c4 = $db->query("SHOW COLUMNS FROM final_products LIKE 'pre_sq_m2'");
  if (!$c4 || !$c4->fetch_assoc()) {
    @$db->query("ALTER TABLE final_products ADD COLUMN pre_sq_m2 DOUBLE NULL AFTER pre_sq_ft");
  }
}

switch ($action) {
  case 'start':
    ensure_admin_polish();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $rmp_id = (int)($_POST['rmp_id'] ?? 0);
    if (!$rmp_id) json_response(['message'=>'Missing rmp_id'], 400);
    $r = $mysqli->prepare('SELECT po_id,status FROM raw_material_products WHERE id=? FOR UPDATE');
    $r->bind_param('i',$rmp_id); $r->execute(); $row=$r->get_result()->fetch_assoc();
    if (!$row) json_response(['message'=>'RMP not found'],404);
    if ($row['status'] !== 'received') json_response(['message'=>'Invalid RMP status (already forwarded or processed)'],400);
    $po_id=(int)$row['po_id'];
    $pre_h = $_POST['pre_height']!=='' ? (float)$_POST['pre_height'] : null;
    $pre_l = $_POST['pre_length']!=='' ? (float)$_POST['pre_length'] : null;
    $worker_name = trim($_POST['worker_name'] ?? '');
    $worker_id = $_POST['worker_id']!=='' ? (int)$_POST['worker_id'] : null;
    $notes = trim($_POST['notes'] ?? '');

    // prevent duplicate start if a job already exists for this RMP
    $chk = $mysqli->prepare('SELECT id FROM polishing_jobs WHERE rmp_id=? AND status="in_progress"');
    $chk->bind_param('i',$rmp_id);
    $chk->execute();
    if ($chk->get_result()->fetch_assoc()) { json_response(['message'=>'Polishing already started for this slab'], 409); }

    $start_date_raw = trim($_POST['start_date'] ?? '');
    $start_at = null;
    if ($start_date_raw !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $start_date_raw)) {
      $start_at = $start_date_raw . ' 00:00:00';
    }
    $stmt = $mysqli->prepare('INSERT INTO polishing_jobs (rmp_id, po_id, pre_height, pre_length, worker_name, worker_id, notes, start_at, status) VALUES (?,?,?,?,?,?,?, COALESCE(?, NOW()), "in_progress")');
    $stmt->bind_param('iiddsiss', $rmp_id, $po_id, $pre_h, $pre_l, $worker_name, $worker_id, $notes, $start_at);
    if (!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    // update rmp status
    $mysqli->query('UPDATE raw_material_products SET status="in_polishing" WHERE id='.(int)$rmp_id);
    json_response(['message'=>'Started', 'id'=>$mysqli->insert_id]);
    break;

  case 'bulk_start':
    ensure_admin_polish();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $ids_json = (string)($_POST['ids_json'] ?? '[]');
    $ids = json_decode($ids_json, true);
    if (!is_array($ids) || !count($ids)) json_response(['message'=>'No ids selected'], 422);
    // sanitize to ints and unique
    $ids = array_values(array_unique(array_map('intval', $ids)));
    $ids = array_values(array_filter($ids, function($v){ return $v > 0; }));
    if (!count($ids)) json_response(['message'=>'No valid ids'], 422);

    $pre_h = ($_POST['pre_height'] ?? '') !== '' ? (float)$_POST['pre_height'] : null;
    $pre_l = ($_POST['pre_length'] ?? '') !== '' ? (float)$_POST['pre_length'] : null;
    $worker_name = trim($_POST['worker_name'] ?? '');
    $worker_id = ($_POST['worker_id'] ?? '') !== '' ? (int)$_POST['worker_id'] : null;
    $notes = trim($_POST['notes'] ?? '');
    $start_date_raw = trim($_POST['start_date'] ?? '');
    $start_at = null;
    if ($start_date_raw !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $start_date_raw)) {
      $start_at = $start_date_raw . ' 00:00:00';
    }

    $ok = 0;
    $fail = 0;
    $failed_ids = [];

    $mysqli->begin_transaction();
    try {
      $sel = $mysqli->prepare('SELECT id, po_id, status, height, length FROM raw_material_products WHERE id=?');
      $chk = $mysqli->prepare('SELECT id FROM polishing_jobs WHERE rmp_id=? AND status="in_progress"');
      $ins = $mysqli->prepare('INSERT INTO polishing_jobs (rmp_id, po_id, pre_height, pre_length, worker_name, worker_id, notes, start_at, status) VALUES (?,?,?,?,?,?,?, COALESCE(?, NOW()), "in_progress")');
      $upd = $mysqli->prepare('UPDATE raw_material_products SET status="in_polishing" WHERE id=?');

      foreach ($ids as $rmp_id) {
        $rmp_id = (int)$rmp_id;
        $sel->bind_param('i', $rmp_id);
        $sel->execute();
        $row = $sel->get_result()->fetch_assoc();
        if (!$row) { $fail++; $failed_ids[] = $rmp_id; continue; }
        if (($row['status'] ?? '') !== 'received') { $fail++; $failed_ids[] = $rmp_id; continue; }

        $chk->bind_param('i', $rmp_id);
        $chk->execute();
        if ($chk->get_result()->fetch_assoc()) { $fail++; $failed_ids[] = $rmp_id; continue; }

        $po_id = (int)($row['po_id'] ?? 0);
        if (!$po_id) { $fail++; $failed_ids[] = $rmp_id; continue; }

        $use_pre_h = $pre_h !== null ? $pre_h : (($_POST['pre_height'] ?? '') === '' ? (float)($row['height'] ?? 0) : null);
        $use_pre_l = $pre_l !== null ? $pre_l : (($_POST['pre_length'] ?? '') === '' ? (float)($row['length'] ?? 0) : null);

        $ins->bind_param('iiddsiss', $rmp_id, $po_id, $use_pre_h, $use_pre_l, $worker_name, $worker_id, $notes, $start_at);
        if (!$ins->execute()) { $fail++; $failed_ids[] = $rmp_id; continue; }

        $upd->bind_param('i', $rmp_id);
        if (!$upd->execute()) { $fail++; $failed_ids[] = $rmp_id; continue; }

        $ok++;
      }

      $mysqli->commit();
    } catch (Throwable $e) {
      $mysqli->rollback();
      json_response(['message' => $e->getMessage()], 500);
    }

    json_response(['ok' => $ok, 'fail' => $fail, 'failed_ids' => $failed_ids]);
    break;

  case 'update':
    ensure_admin_polish();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id = (int)($_POST['id'] ?? 0);
    if (!$id) json_response(['message'=>'Missing id'],400);
    $pre_h = $_POST['pre_height']!=='' ? (float)$_POST['pre_height'] : null;
    $pre_l = $_POST['pre_length']!=='' ? (float)$_POST['pre_length'] : null;
    $worker_name = trim($_POST['worker_name'] ?? '');
    $worker_id = $_POST['worker_id']!=='' ? (int)$_POST['worker_id'] : null;
    $notes = trim($_POST['notes'] ?? '');
    $start_date_raw = trim($_POST['start_date'] ?? '');
    $start_at = null;
    if ($start_date_raw !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $start_date_raw)) { $start_at = $start_date_raw.' 00:00:00'; }
    $stmt = $mysqli->prepare('UPDATE polishing_jobs SET pre_height=?, pre_length=?, worker_name=?, worker_id=?, notes=?, start_at=COALESCE(?, start_at) WHERE id=?');
    $stmt->bind_param('ddssisi', $pre_h, $pre_l, $worker_name, $worker_id, $notes, $start_at, $id);
    if (!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Updated']);
    break;

  case 'complete':
    ensure_admin_polish();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id = (int)($_POST['id'] ?? 0);
    // Inputs are provided in inches on the UI
    $final_h_in = (float)($_POST['final_height'] ?? 0);
    $final_l_in = (float)($_POST['final_length'] ?? 0);
    // Store inches as-is in DB fields; compute feet only for area calculations
    $final_h = $final_h_in;
    $final_l = $final_l_in;
    $final_h_ft = $final_h_in / 12.0;
    $final_l_ft = $final_l_in / 12.0;
    $finish_id = !empty($_POST['finish_id']) ? (int)$_POST['finish_id'] : null;
    if (!$id || $final_h<=0 || $final_l<=0) json_response(['message'=>'Missing/invalid final size'],400);

    // Guard: prevent completing the same job twice
    $st = $mysqli->prepare('SELECT status FROM polishing_jobs WHERE id=?');
    $st->bind_param('i',$id); $st->execute(); $jr = $st->get_result()->fetch_assoc();
    if (!$jr) json_response(['message'=>'Job not found'],404);
    if ($jr['status']==='completed') json_response(['message'=>'Job already completed'],409);

    // read job and rmp
    $stmt=$mysqli->prepare('SELECT j.rmp_id, j.po_id, j.pre_height, j.pre_length, r.quality FROM polishing_jobs j JOIN raw_material_products r ON r.id=j.rmp_id WHERE j.id=?');
    $stmt->bind_param('i',$id); $stmt->execute(); $job=$stmt->get_result()->fetch_assoc();
    if(!$job) json_response(['message'=>'Job not found'],404);
    // pre sizes (inches originally), assume stored in inches; convert to feet for area as with final sizes
    $pre_h = (float)($job['pre_height'] ?? 0);
    $pre_l = (float)($job['pre_length'] ?? 0);
    $pre_sq_ft = 0.0; $pre_sq_m2 = 0.0;
    if ($pre_h>0 && $pre_l>0) {
      // If pre sizes captured in inches, convert to feet
      $pre_sq_ft = ($pre_h/12.0) * ($pre_l/12.0);
      $pre_sq_m2 = $pre_sq_ft * 0.092903;
    }
    // Round pre areas to 3 decimals for storage (per requirement)
    $pre_sq_ft = round($pre_sq_ft, 3);
    $pre_sq_m2 = round($pre_sq_m2, 3);
    // Keep final areas at 2 decimals as before
    $final_sq_ft = round($final_h_ft * $final_l_ft, 3);
    $final_sq_m2 = round($final_sq_ft * 0.092903, 3);
    // mark job completed with optional provided complete_date
    $complete_date_raw = trim($_POST['complete_date'] ?? '');
    $completed_at = null;
    if ($complete_date_raw !== '' && preg_match('/^\d{4}-\d{2}-\d{2}$/', $complete_date_raw)) {
      $end_at = $complete_date_raw.' 00:00:00';
      $completed_at = $end_at;
      $up = $mysqli->prepare('UPDATE polishing_jobs SET status="completed", end_at=? WHERE id=?');
      $up->bind_param('si', $end_at, $id); if(!$up->execute()) json_response(['message'=>$up->error],500);
    } else {
      $mysqli->query('UPDATE polishing_jobs SET status="completed", end_at=NOW() WHERE id='.(int)$id);
    }
    // create final product (assign to current user's branch if exists)
    ensure_fp_pre_migration($mysqli);

    // Guard: prevent duplicate final product for the same polishing job
    $ex = $mysqli->prepare('SELECT id FROM final_products WHERE polishing_job_id=? LIMIT 1');
    $ex->bind_param('i', $id); $ex->execute(); if ($ex->get_result()->fetch_assoc()) { json_response(['message'=>'Final product already created for this job'], 409); }

    $cu = current_user(); $branch_id = (!empty($cu['branch_id']) ? (int)$cu['branch_id'] : null);
    $fp=$mysqli->prepare('INSERT INTO final_products (rmp_id, po_id, polishing_job_id, pre_height, pre_length, pre_sq_ft, pre_sq_m2, final_height, final_length, final_sq_ft, final_sq_m2, finish_id, quality, sale_status, completed_at, branch_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,"available", COALESCE(?, NOW()), ?)');
    $fp->bind_param('iiidddiddddissi', $job['rmp_id'], $job['po_id'], $id, $pre_h, $pre_l, $pre_sq_ft, $pre_sq_m2, $final_h, $final_l, $final_sq_ft, $final_sq_m2, $finish_id, $job['quality'], $completed_at, $branch_id);
    if(!$fp->execute()) json_response(['message'=>$fp->error],500);
    // update rmp status
    $mysqli->query('UPDATE raw_material_products SET status="finished" WHERE id='.(int)$job['rmp_id']);
    json_response(['message'=>'Completed', 'final_product_id'=>$mysqli->insert_id]);
    break;

  case 'delete':
    ensure_admin_polish();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'], 400);
    $id=(int)($_POST['id'] ?? 0); if(!$id) json_response(['message'=>'Missing id'],400);
    // only allow delete if not completed
    $row = $mysqli->query('SELECT status, rmp_id FROM polishing_jobs WHERE id='.(int)$id)->fetch_assoc();
    if(!$row) json_response(['message'=>'Not found'],404);
    if($row['status']==='completed') json_response(['message'=>'Cannot delete a completed job'],400);
    // revert RMP status to received if it was in_polishing
    $mysqli->query('UPDATE raw_material_products SET status="received" WHERE id='.(int)$row['rmp_id'].' AND status="in_polishing"');
    $stmt=$mysqli->prepare('DELETE FROM polishing_jobs WHERE id=?');
    $stmt->bind_param('i',$id);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Deleted']);
    break;

  case 'get':
    $id = (int)($_GET['id'] ?? 0);
    $stmt=$mysqli->prepare('SELECT j.*, r.slab_no, r.height, r.length, r.sq_ft, r.quality, c.name AS material_color, p.order_no FROM polishing_jobs j JOIN raw_material_products r ON r.id=j.rmp_id LEFT JOIN colors c ON c.id = r.color_id LEFT JOIN purchase_orders p ON p.id = r.po_id WHERE j.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 'list':
    $status = trim($_GET['status'] ?? '');
    $q = trim($_GET['q'] ?? '');
    $from = trim($_GET['from'] ?? '');
    $to = trim($_GET['to'] ?? '');
    $worker_id = isset($_GET['worker_id']) && $_GET['worker_id']!=='' ? (int)$_GET['worker_id'] : null;
    $color_id = isset($_GET['color_id']) && $_GET['color_id']!=='' ? (int)$_GET['color_id'] : null;
    $where=[]; $types=''; $params=[];
    if ($status!=='' && in_array($status,['pending','in_progress','completed','cancelled'],true)) { $where[]='j.status=?'; $types.='s'; $params[]=$status; }
    if ($q!=='') { $where[]='(r.slab_no LIKE CONCAT("%", ?, "%") OR r.quality LIKE CONCAT("%", ?, "%") OR j.worker_name LIKE CONCAT("%", ?, "%"))'; $types.='sss'; $params[]=$q; $params[]=$q; $params[]=$q; }
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $where[]='DATE(f.completed_at) >= ?'; $types.='s'; $params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $where[]='DATE(f.completed_at) <= ?'; $types.='s'; $params[]=$to; }
    if ($worker_id!==null) { $where[]='j.worker_id = ?'; $types.='i'; $params[]=$worker_id; }
    if ($color_id!==null) { $where[]='r.color_id = ?'; $types.='i'; $params[]=$color_id; }
    $sql='SELECT 
            j.*, 
            r.slab_no, r.quality, r.sq_ft AS raw_sq_ft, r.height AS raw_height, r.length AS raw_length,
            c.name AS material_color,
            p.order_no,
            f.final_height, f.final_length, f.final_sq_ft AS final_sq_ft,
            ROUND((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0), 3) AS pre_sq_ft,
            CASE WHEN r.sq_ft>0 AND f.final_sq_ft IS NOT NULL THEN GREATEST(r.sq_ft - f.final_sq_ft, 0) ELSE NULL END AS extra_sq_ft,
            CASE WHEN r.sq_ft>0 AND f.final_sq_ft IS NOT NULL THEN ROUND(GREATEST(r.sq_ft - f.final_sq_ft, 0) * 100.0 / r.sq_ft, 2) ELSE NULL END AS extra_pct,
            CASE WHEN f.final_sq_ft IS NOT NULL THEN ROUND(f.final_sq_ft - ROUND((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0), 3), 2) ELSE NULL END AS diff_sq_ft,
            CASE WHEN f.final_sq_ft IS NOT NULL THEN ROUND((f.final_sq_ft - ROUND((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0), 3)) * 100.0 / NULLIF(ROUND((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0), 3), 0), 2) ELSE NULL END AS diff_pct
          FROM polishing_jobs j 
          JOIN raw_material_products r ON r.id=j.rmp_id
          LEFT JOIN colors c ON c.id = r.color_id
          LEFT JOIN purchase_orders p ON p.id = r.po_id
          LEFT JOIN final_products f ON f.polishing_job_id=j.id';
    if ($where) $sql.=' WHERE '.implode(' AND ',$where);
    $sql.=' ORDER BY j.id DESC';
    if ($where){ $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res=$mysqli->query($sql); }
    $rows=[]; while($row=$res->fetch_assoc()){ $rows[]=$row; }
    json_response(['data'=>$rows]);
    break;

  case 'by_color':
    $status = trim($_GET['status'] ?? '');
    $from = trim($_GET['from'] ?? '');
    $to = trim($_GET['to'] ?? '');
    $where=[]; $types=''; $params=[];
    if ($status!=='' && in_array($status,['pending','in_progress','completed','cancelled'],true)) { $where[]='j.status=?'; $types.='s'; $params[]=$status; }
    if ($from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$from)) { $where[]='DATE(f.completed_at) >= ?'; $types.='s'; $params[]=$from; }
    if ($to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$to)) { $where[]='DATE(f.completed_at) <= ?'; $types.='s'; $params[]=$to; }
    $sql='SELECT 
            c.id AS color_id,
            COALESCE(c.name, "Unknown") AS color_name,
            COUNT(*) AS job_count,
            ROUND(SUM((COALESCE(j.pre_height, r.height)/12.0)*(COALESCE(j.pre_length, r.length)/12.0)), 3) AS total_pre_sq_ft,
            ROUND(SUM(COALESCE(f.final_sq_ft, 0)), 3) AS total_final_sq_ft
          FROM polishing_jobs j 
          JOIN raw_material_products r ON r.id=j.rmp_id
          LEFT JOIN colors c ON c.id = r.color_id
          LEFT JOIN final_products f ON f.polishing_job_id=j.id';
    if ($where) $sql.=' WHERE '.implode(' AND ',$where);
    $sql.=' GROUP BY c.id, c.name ORDER BY total_pre_sq_ft DESC';
    if ($where){ $stmt=$mysqli->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); $res=$stmt->get_result(); }
    else { $res=$mysqli->query($sql); }
    $rows=[]; while($row=$res->fetch_assoc()){ $rows[]=$row; }
    json_response(['data'=>$rows]);
    break;

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