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

// Ensure purchase_order_items has a separate 'note' column (top-level)
function ensure_po_item_note_migration(mysqli $db){
  $res = $db->query("SHOW COLUMNS FROM purchase_order_items LIKE 'note'");
  if ($res && $res->num_rows > 0) return;
  // Add note as TEXT to allow longer notes
  @$db->query("ALTER TABLE purchase_order_items ADD COLUMN note TEXT NULL AFTER description");
}

// Ensure table has required status columns and enum
function ensure_po_status_migration(mysqli $mysqli) {
  // add columns if missing
  $cols = [];
  $res = $mysqli->query("SHOW COLUMNS FROM purchase_orders");
  if ($res) { while ($r = $res->fetch_assoc()) { $cols[$r['Field']] = true; } }
  $alters = [];
  if (empty($cols['confirmed_at'])) $alters[] = "ADD COLUMN confirmed_at DATE NULL AFTER expected_date";
  if (empty($cols['shipped_at']))   $alters[] = "ADD COLUMN shipped_at DATE NULL AFTER confirmed_at";
  if (empty($cols['received_at']))  $alters[] = "ADD COLUMN received_at DATE NULL AFTER shipped_at";
  if (empty($cols['cancelled_at'])) $alters[] = "ADD COLUMN cancelled_at DATE NULL AFTER received_at";
  if (!empty($alters)) {
    $sql = "ALTER TABLE purchase_orders " . implode(', ', $alters);
    @$mysqli->query($sql);
  }
  // ensure enum includes new statuses
  $res2 = $mysqli->query("SHOW COLUMNS FROM purchase_orders LIKE 'status'");
  if ($res2 && ($row = $res2->fetch_assoc())) {
    $type = $row['Type'] ?? '';
    if (stripos($type, "'confirmed'") === false || stripos($type, "'shipped'") === false || stripos($type, "'received'") === false || stripos($type, "'cancelled'") === false) {
      @$mysqli->query("ALTER TABLE purchase_orders MODIFY COLUMN status ENUM('pending','confirmed','shipped','received','cancelled') NOT NULL DEFAULT 'pending'");
    }
  }
}

function ensure_admin_po() {
  if (!has_role(['admin','super_admin'])) {
    json_response(['message' => 'Forbidden'], 403);
  }
}

function ensure_po_invoice_schema(mysqli $db){
  // Create only if absent to avoid FK name conflicts (errno 121)
  $exists = $db->query("SHOW TABLES LIKE 'purchase_order_invoices'");
  if ($exists && $exists->num_rows > 0) return;
  // Do not name the FK explicitly; let MySQL auto-name it to avoid duplicate key on update/write
  $sql = "CREATE TABLE purchase_order_invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    po_id INT NOT NULL,
    invoice_no VARCHAR(100) NULL,
    invoice_date DATE NULL,
    amount DECIMAL(12,2) NULL,
    currency VARCHAR(10) NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE
  ) ENGINE=InnoDB";
  @$db->query($sql);
}

function generate_order_no($mysqli) {
  // Format: PO-YYYYMM-####
  $prefix = 'PO-' . date('Ym') . '-';
  $like = $prefix . '%';
  $stmt = $mysqli->prepare("SELECT order_no FROM purchase_orders WHERE order_no LIKE ? ORDER BY order_no DESC LIMIT 1");
  $stmt->bind_param('s', $like);
  $stmt->execute();
  $res = $stmt->get_result()->fetch_assoc();
  $next = 1;
  if ($res && preg_match('/^(?:PO-\d{6}-)(\d{4})$/', $res['order_no'], $m)) {
    $next = (int)$m[1] + 1;
  }
  return $prefix . str_pad((string)$next, 4, '0', STR_PAD_LEFT);
}

switch ($action) {
  case 'list':
    ensure_po_status_migration($mysqli);
    ensure_po_invoice_schema($mysqli);
    // Optional filters: q (search), status
    $where = [];
    $params = [];
    $types = '';
    $q = trim($_GET['q'] ?? '');
    $status = strtolower(trim($_GET['status'] ?? ''));
    if ($status === 'approved') { $status = 'confirmed'; }
    if ($q !== '') {
      $where[] = '(po.order_no LIKE CONCAT("%", ?, "%") OR s.name LIKE CONCAT("%", ?, "%"))';
      $params[] = $q; $params[] = $q; $types .= 'ss';
    }
    if ($status !== '' && in_array($status, ['pending','confirmed','shipped','received','cancelled'], true)) {
      $where[] = 'po.status = ?';
      $params[] = $status; $types .= 's';
    }
    $sql = 'SELECT po.id, po.order_no, po.order_date, po.expected_date, po.status, po.total_amount, po.confirmed_at, po.shipped_at, po.received_at, po.cancelled_at, s.name AS supplier_name, s.contact_person AS supplier_contact, s.currency AS supplier_currency FROM purchase_orders po LEFT JOIN suppliers s ON s.id = po.supplier_id';
    if (!empty($where)) { $sql .= ' WHERE ' . implode(' AND ', $where); }
    $sql .= ' ORDER BY po.id DESC';
    if (!empty($where)) {
      $stmt = $mysqli->prepare($sql);
      $stmt->bind_param($types, ...$params);
      $stmt->execute();
      $res = $stmt->get_result();
    } else {
      $res = $mysqli->query($sql);
    }
    $data = [];
    while ($row = $res->fetch_assoc()) { $data[] = $row; }
    json_response(['data' => $data]);
    break;

  case 'get':
    ensure_po_invoice_schema($mysqli);
    ensure_po_item_note_migration($mysqli);
    $id = (int)($_GET['id'] ?? 0);
    $stmt = $mysqli->prepare('SELECT id, order_no, supplier_id, order_date, expected_date, status, ship_mode, delivery_term, payment_term, supplier_contact, supplier_email, notes, total_amount FROM purchase_orders WHERE id = ?');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    if (!$row) json_response(['message' => 'Not found'], 404);
    // fetch items
    $it = $mysqli->prepare('SELECT id, line_no, description, note, hs_code, quantity, unit, unit_price, line_total FROM purchase_order_items WHERE po_id = ? ORDER BY line_no ASC');
    $it->bind_param('i', $id);
    $it->execute();
    $items = [];
    $r = $it->get_result();
    while ($i = $r->fetch_assoc()) { $items[] = $i; }
    $row['items'] = $items;
    json_response(['data' => $row]);
    break;

  case 'create':
    ensure_admin_po();
    ensure_po_invoice_schema($mysqli);
    ensure_po_item_note_migration($mysqli);
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message' => 'Invalid CSRF'], 400);
    $order_no = trim($_POST['order_no'] ?? '');
    $supplier_id = (int)($_POST['supplier_id'] ?? 0);
    $order_date = $_POST['order_date'] ?? '';
    $expected_date = $_POST['expected_date'] ?? null;
    $status = strtolower($_POST['status'] ?? 'pending');
    if ($status === 'approved') { $status = 'confirmed'; }
    $ship_mode = trim($_POST['ship_mode'] ?? '');
    $delivery_term = trim($_POST['delivery_term'] ?? '');
    $payment_term = trim($_POST['payment_term'] ?? '');
    $supplier_contact = trim($_POST['supplier_contact'] ?? '');
    $supplier_email = trim($_POST['supplier_email'] ?? '');
    $notes = trim($_POST['notes'] ?? '');
    $items_json = $_POST['items_json'] ?? '[]';
    $items = json_decode($items_json, true);
    if (!is_array($items)) $items = [];
    // total from items
    $total_amount = 0.0;
    foreach ($items as $ix) {
      $total_amount += (float)($ix['line_total'] ?? 0);
    }
    if ($order_no === '') {
      $order_no = generate_order_no($mysqli);
    }
    if (!$order_no || !$supplier_id || !$order_date) json_response(['message' => 'Missing fields'], 400);
    if (!in_array($status, ['pending','confirmed','shipped','received','cancelled'], true)) json_response(['message' => 'Invalid status'], 400);
    $stmt = $mysqli->prepare('INSERT INTO purchase_orders (order_no, supplier_id, order_date, expected_date, status, ship_mode, delivery_term, payment_term, supplier_contact, supplier_email, notes, total_amount) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)');
    $stmt->bind_param('sisssssssssd', $order_no, $supplier_id, $order_date, $expected_date, $status, $ship_mode, $delivery_term, $payment_term, $supplier_contact, $supplier_email, $notes, $total_amount);
    if (!$stmt->execute()) json_response(['message' => $stmt->error], 500);
    $po_id = $stmt->insert_id;
    // insert items
    if (!empty($items)) {
      $pi = $mysqli->prepare('INSERT INTO purchase_order_items (po_id, line_no, description, note, hs_code, quantity, unit, unit_price, line_total) VALUES (?,?,?,?,?,?,?,?,?)');
      foreach ($items as $idx => $ix) {
        $line_no = (int)($ix['line_no'] ?? ($idx + 1));
        $description = (string)($ix['description'] ?? '');
        $note = (string)($ix['note'] ?? '');
        $hs_code = (string)($ix['hs_code'] ?? '');
        $quantity = (float)($ix['quantity'] ?? 0);
        $unit = (string)($ix['unit'] ?? '');
        $unit_price = (float)($ix['unit_price'] ?? 0);
        $line_total = (float)($ix['line_total'] ?? ($quantity * $unit_price));
        $pi->bind_param('iisssdsdd', $po_id, $line_no, $description, $note, $hs_code, $quantity, $unit, $unit_price, $line_total);
        if (!$pi->execute()) json_response(['message' => $pi->error], 500);
      }
    }
    json_response(['message' => 'Created']);
    break;

  case 'update':
    ensure_admin_po();
    ensure_po_invoice_schema($mysqli);
    ensure_po_item_note_migration($mysqli);
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message' => 'Invalid CSRF'], 400);
    $id = (int)($_POST['id'] ?? 0);
    $order_no = trim($_POST['order_no'] ?? '');
    $supplier_id = (int)($_POST['supplier_id'] ?? 0);
    $order_date = $_POST['order_date'] ?? '';
    $expected_date = $_POST['expected_date'] ?? null;
    $status = strtolower($_POST['status'] ?? 'pending');
    if ($status === 'approved') { $status = 'confirmed'; }
    $ship_mode = trim($_POST['ship_mode'] ?? '');
    $delivery_term = trim($_POST['delivery_term'] ?? '');
    $payment_term = trim($_POST['payment_term'] ?? '');
    $supplier_contact = trim($_POST['supplier_contact'] ?? '');
    $supplier_email = trim($_POST['supplier_email'] ?? '');
    $notes = trim($_POST['notes'] ?? '');
    $total_amount = (float)($_POST['total_amount'] ?? 0);
    $items_json = $_POST['items_json'] ?? '[]';
    $items = json_decode($items_json, true);
    if (!is_array($items)) $items = [];
    if (!$id || !$order_no || !$supplier_id || !$order_date) json_response(['message' => 'Missing fields'], 400);
    if (!in_array($status, ['pending','confirmed','shipped','received','cancelled'], true)) json_response(['message' => 'Invalid status'], 400);
    $stmt = $mysqli->prepare('UPDATE purchase_orders SET order_no=?, supplier_id=?, order_date=?, expected_date=?, status=?, ship_mode=?, delivery_term=?, payment_term=?, supplier_contact=?, supplier_email=?, notes=?, total_amount=? WHERE id=?');
    $stmt->bind_param('sisssssssssdi', $order_no, $supplier_id, $order_date, $expected_date, $status, $ship_mode, $delivery_term, $payment_term, $supplier_contact, $supplier_email, $notes, $total_amount, $id);
    if (!$stmt->execute()) json_response(['message' => $stmt->error], 500);
    // replace items
    $del = $mysqli->prepare('DELETE FROM purchase_order_items WHERE po_id = ?');
    $del->bind_param('i', $id);
    if (!$del->execute()) json_response(['message' => $del->error], 500);
    if (!empty($items)) {
      $pi = $mysqli->prepare('INSERT INTO purchase_order_items (po_id, line_no, description, note, hs_code, quantity, unit, unit_price, line_total) VALUES (?,?,?,?,?,?,?,?,?)');
      foreach ($items as $idx => $ix) {
        $line_no = (int)($ix['line_no'] ?? ($idx + 1));
        $description = (string)($ix['description'] ?? '');
        $note = (string)($ix['note'] ?? '');
        $hs_code = (string)($ix['hs_code'] ?? '');
        $quantity = (float)($ix['quantity'] ?? 0);
        $unit = (string)($ix['unit'] ?? '');
        $unit_price = (float)($ix['unit_price'] ?? 0);
        $line_total = (float)($ix['line_total'] ?? ($quantity * $unit_price));
        $pi->bind_param('iisssdsdd', $id, $line_no, $description, $note, $hs_code, $quantity, $unit, $unit_price, $line_total);
        if (!$pi->execute()) json_response(['message' => $pi->error], 500);
      }
    }
    json_response(['message' => 'Updated']);
    break;

  case 'delete':
    ensure_admin_po();
    ensure_po_invoice_schema($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);
    // Block deletion if already shipped or received
    $chk = $mysqli->prepare('SELECT status FROM purchase_orders WHERE id = ?');
    $chk->bind_param('i', $id);
    $chk->execute();
    $row = $chk->get_result()->fetch_assoc();
    if (!$row) json_response(['message' => 'Not found'], 404);
    $st = strtolower($row['status'] ?? '');
    if (in_array($st, ['shipped','received'], true)) {
      json_response(['message' => 'Cannot delete an order that is shipped or received'], 400);
    }
    $stmt = $mysqli->prepare('DELETE FROM purchase_orders WHERE id = ?');
    $stmt->bind_param('i', $id);
    if (!$stmt->execute()) json_response(['message' => $stmt->error], 500);
    json_response(['message' => 'Deleted']);
    break;

  case 'update_status':
    ensure_po_status_migration($mysqli);
    ensure_admin_po();
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message' => 'Invalid CSRF'], 400);
    $id = (int)($_POST['id'] ?? 0);
    $status = strtolower(trim($_POST['status'] ?? ''));
    if ($status === 'approved') { $status = 'confirmed'; }
    if (!$id) json_response(['message' => 'Missing id'], 400);
    if (!in_array($status, ['pending','confirmed','shipped','received','cancelled'], true)) json_response(['message' => 'Invalid status'], 400);
    // Map to date column
    $dateCol = null;
    if ($status === 'confirmed') $dateCol = 'confirmed_at';
    if ($status === 'shipped')   $dateCol = 'shipped_at';
    if ($status === 'received')  $dateCol = 'received_at';
    if ($status === 'cancelled') $dateCol = 'cancelled_at';
    if ($status === 'pending')   $dateCol = null; // keep others as-is
    if ($dateCol) {
      $sql = "UPDATE purchase_orders SET status=?, $dateCol = IFNULL($dateCol, CURDATE()) WHERE id = ?";
      $stmt = $mysqli->prepare($sql);
      $stmt->bind_param('si', $status, $id);
    } else {
      $stmt = $mysqli->prepare('UPDATE purchase_orders SET status=? WHERE id = ?');
      $stmt->bind_param('si', $status, $id);
    }
    if (!$stmt->execute()) json_response(['message'=>$stmt->error], 500);
    json_response(['message'=>'Status updated']);
    break;

  case 'generate_no':
    ensure_admin_po();
    $no = generate_order_no($mysqli);
    json_response(['order_no' => $no]);
    break;

  case 'list_invoices':
    ensure_po_invoice_schema($mysqli);
    $po_id = (int)($_GET['po_id'] ?? 0); if(!$po_id) json_response(['message'=>'Missing po_id'],400);
    $stmt = $mysqli->prepare('SELECT id, invoice_no, invoice_date, amount, currency, file_name, file_path, uploaded_at FROM purchase_order_invoices WHERE po_id=? ORDER BY id DESC');
    $stmt->bind_param('i',$po_id); $stmt->execute(); $res=$stmt->get_result();
    $rows=[]; while($r=$res->fetch_assoc()){ $rows[]=$r; }
    json_response(['data'=>$rows]);
    break;

  case 'upload_invoice':
    ensure_admin_po(); ensure_po_invoice_schema($mysqli);
    if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message'=>'Invalid CSRF'],400);
    $po_id = (int)($_POST['po_id'] ?? 0); if(!$po_id) json_response(['message'=>'Missing po_id'],400);
    $invoice_no = trim($_POST['invoice_no'] ?? '');
    $invoice_date = ($_POST['invoice_date'] ?? '') ?: null;
    $amount = ($_POST['amount'] ?? '') !== '' ? (float)$_POST['amount'] : null;
    $currency = trim($_POST['currency'] ?? ''); if($currency==='') $currency=null;
    if (!isset($_FILES['file']) || !is_uploaded_file($_FILES['file']['tmp_name'])) json_response(['message'=>'Missing file'],400);
    $baseDir = realpath(__DIR__ . '/../uploads'); if($baseDir===false) $baseDir = __DIR__ . '/../uploads';
    $targetDir = $baseDir . '/po_invoices'; if(!is_dir($targetDir)) @mkdir($targetDir, 0777, true);
    $ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);
    $safeName = 'PO'.$po_id.'-'.date('Ymd-His').'-'.bin2hex(random_bytes(4)).($ext?('.'.preg_replace('/[^a-zA-Z0-9]/','', $ext)):'');
    $targetPath = $targetDir . '/' . $safeName;
    if (!move_uploaded_file($_FILES['file']['tmp_name'], $targetPath)) json_response(['message'=>'Failed to save file'],500);
    $relPath = 'uploads/po_invoices/' . $safeName;
    $stmt=$mysqli->prepare('INSERT INTO purchase_order_invoices (po_id, invoice_no, invoice_date, amount, currency, file_name, file_path) VALUES (?,?,?,?,?,?,?)');
    $origName = $_FILES['file']['name'];
    $stmt->bind_param('issdsss', $po_id, $invoice_no, $invoice_date, $amount, $currency, $origName, $relPath);
    if(!$stmt->execute()) json_response(['message'=>$stmt->error],500);
    json_response(['message'=>'Uploaded']);
    break;

  case 'delete_invoice':
    ensure_admin_po(); ensure_po_invoice_schema($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);
    $row = $mysqli->query('SELECT file_path FROM purchase_order_invoices WHERE id='.(int)$id.' LIMIT 1');
    $fp = $row && ($r=$row->fetch_assoc()) ? $r['file_path'] : null;
    $mysqli->query('DELETE FROM purchase_order_invoices WHERE id='.(int)$id);
    if ($fp) { $full = realpath(__DIR__ . '/../' . $fp) ?: (__DIR__ . '/../' . $fp); @unlink($full); }
    json_response(['message'=>'Deleted']);
    break;

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