<?php
require 'db.php';

$id = isset($_POST['id']) ? (int)$_POST['id'] : 0;
$newDepartment = isset($_POST['department']) ? trim($_POST['department']) : '';

if (!$id || !$newDepartment) {
    echo 'Error: Missing ID or department';
    exit;
}

try {
    // 0️⃣ Check if new department name already exists (excluding current ID)
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM departments WHERE TRIM(name) = ? AND id != ?");
    $stmt->execute([$newDepartment, $id]);
    if ($stmt->fetchColumn() > 0) {
        echo 'duplicate'; // special keyword for JS
        exit;
    }

    $pdo->beginTransaction();

    // 1️⃣ Get old department name
    $stmt = $pdo->prepare("SELECT name FROM departments WHERE id = ?");
    $stmt->execute([$id]);
    $oldDepartment = $stmt->fetchColumn();

    if (!$oldDepartment) {
        throw new Exception("Department ID $id not found");
    }

    $oldDepartment = trim($oldDepartment);

    // 2️⃣ Update departments table
    $stmt = $pdo->prepare("UPDATE departments SET name = ? WHERE id = ?");
    $stmt->execute([$newDepartment, $id]);

    // 3️⃣ Update transactions table (all rows with the old department)
    $stmt = $pdo->prepare("UPDATE transactions SET department = ? WHERE TRIM(department) = ?");
    $stmt->execute([$newDepartment, $oldDepartment]);

    $pdo->commit();
    echo "success";

} catch (Exception $e) {
    $pdo->rollBack();
    echo 'Error: ' . $e->getMessage();
}
?>
