<?php
header('Content-Type: application/json');
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/security.php';

function getTableColumns($mysqli, $table) {
    $columns = [];
    $result = $mysqli->query("SHOW COLUMNS FROM {$table}");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $columns[] = $row['Field'];
        }
    }
    return $columns;
}

$fields = [
    'full_name' => getSafeString('name', getSafeString('full_name')),
    'department' => getSafeString('department'),
    'position' => getSafeString('position'),
    'email' => getSafeString('email'),
    'phone' => getSafeString('phone'),
    'status' => getSafeString('status'),
    'employee_no' => getSafeString('employee_no'),
    'pin' => getSafeString('pin'),
    'password' => getSafeString('password')
];

if (!$fields['full_name']) {
    echo json_encode(['success' => false, 'error' => 'Name required']);
    exit;
}

if ($fields['employee_no']) {
    $check_stmt = $mysqli->prepare("SELECT id FROM personnel WHERE employee_no = ? LIMIT 1");
    $check_stmt->bind_param('s', $fields['employee_no']);
    $check_stmt->execute();
    $result = $check_stmt->get_result();
    if ($result->num_rows > 0) {
        echo json_encode(['success' => false, 'error' => 'Employee number already exists']);
        exit;
    }
}

$existing = getTableColumns($mysqli, 'personnel');
$columns = [];
$placeholders = [];
$types = '';
$values = [];

foreach ($fields as $column => $value) {
    if (!in_array($column, $existing, true)) {
        continue;
    }
    $columns[] = $column;
    $placeholders[] = '?';
    $types .= 's';
    $values[] = $value;
}

if (empty($columns)) {
    echo json_encode(['success' => false, 'error' => 'No valid personnel fields found']);
    exit;
}

$query = "INSERT INTO personnel (" . implode(',', $columns) . ") VALUES (" . implode(',', $placeholders) . ")";
$stmt = $mysqli->prepare($query);
$stmt->bind_param($types, ...$values);
$ok = $stmt->execute();
$insert_id = $stmt->insert_id;

echo json_encode(['success' => $ok, 'insert_id' => $insert_id]);
?>
