<?php 
$time = time();

include('funtions.php');

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;

require '../assets\vendors\php-email-form\vendor\autoload.php';

// Define this function globally so it's reusable
function sendPHPMail($toEmail, $toName, $subject, $htmlContent) {
    $mail = new PHPMailer(true);
    try {
        $mail->isSMTP();
        $mail->Host       = 'smtp.gmail.com'; 
        $mail->SMTPAuth   = true;
        $mail->Username   = 'bactrizcode@gmail.com';
        $mail->Password   = 'gvjfsljthxuqvbkz'; // App Password
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS; 
        $mail->Port       = 587;

        $mail->setFrom('no-reply@yourlogistics.com', 'Logistics Pro');
        $mail->addAddress($toEmail, $toName);

        $mail->isHTML(true);
        $mail->Subject = $subject;
        $mail->Body    = $htmlContent;
        $mail->AltBody = strip_tags($htmlContent);

        return $mail->send();
    } catch (Exception $e) {
        error_log("Mailer Error: {$mail->ErrorInfo}");
        return false;
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'add_business') {
    // Sanitize Inputs
    $biz_name = mysqli_real_escape_string($conn, $_POST['biz_name']);
    $biz_email = mysqli_real_escape_string($conn, $_POST['biz_email']);
    $owner_name = mysqli_real_escape_string($conn, $_POST['owner_name']);
    $owner_phone = mysqli_real_escape_string($conn, $_POST['owner_phone']);
    $owner_address = mysqli_real_escape_string($conn, $_POST['owner_address']);
    
    // Generate Security Token for activation
    $token = bin2hex(random_bytes(32));
    $setup_link = "http://192.168.0.177/bactriz_crm/activate.php?token=$token";
    $expiry = date('Y-m-d H:i:s', strtotime('+24 hours'));
    $date = date('Y-m-d H:i:s');
    $roleDisplay = 'Business Owner';

    mysqli_begin_transaction($conn);

    try {
        // 1. Insert Business
        $sql_biz = "INSERT INTO businesses (business_name, business_email, business_owner, business_phone, business_address, business_status) 
                    VALUES ('$biz_name', '$biz_email', '$owner_name', '$owner_phone', '$owner_address', 'active')";
        if (!mysqli_query($conn, $sql_biz)) throw new Exception("Error creating business profile");
        
        $business_id = mysqli_insert_id($conn);

        // 2. Insert User with Activation Token (Leave password blank or temporary)
        $sql_user = "INSERT INTO users (business_id, first_name, email, phone_no, role_id, is_active, create_at) 
                     VALUES ('$business_id', '$owner_name', '$biz_email', '$owner_phone', '1', '0', TIME())";
        if (!mysqli_query($conn, $sql_user)) throw new Exception("Error creating owner account");
        
        $new_user_id = mysqli_insert_id($conn);

        $sql_invite_log = "INSERT INTO user_log (user_id, email, role_id, invite_token, token_expiry, business_id, is_active) 
                           VALUES ('$new_user_id', '$biz_email', '1', '$token', '$expiry', '$business_id', 0)";
        
        if (!mysqli_query($conn, $sql_invite_log)) throw new Exception("Failed to log invitation.");

        // 3. Prepare the HTML Email Template
        $htmlBody = "
        <!DOCTYPE html>
        <html>
        <head>
            <style>
                .email-container { font-family: 'Segoe UI', Arial, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; border: 1px solid #eee; border-radius: 8px; overflow: hidden; }
                .header { background-color: #334155; padding: 30px; text-align: center; color: #ffffff; }
                .content { padding: 30px; background-color: #ffffff; }
                .button-wrapper { text-align: center; margin: 30px 0; }
                .btn { background-color: #334155; color: #ffffff !important; padding: 14px 30px; text-decoration: none; border-radius: 8px; font-weight: bold; display: inline-block; font-size: 16px; }
                .footer { background-color: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #777; }
                .badge { background: #e7f1ff; color: #334155; padding: 4px 10px; border-radius: 4px; font-size: 13px; font-weight: 600; }
            </style>
        </head>
        <body>
            <div class='email-container'>
                <div class='header'>
                    <h1 style='margin:0; font-size: 24px; color: #fff;'>Bactriz CRM</h1>
                </div>
                <div class='content'>
                    <p>Hello <strong>$owner_name</strong>,</p>
                    <p>Congratulations! Your business, <strong>$biz_name</strong>, has been successfully onboarded to Bactriz CRM.</p>
                    <p>Your access level: <span class='badge'>$roleDisplay</span></p>
                    <p>To start managing your fleet and customers, please click the button below to set up your password and activate your account.</p>
                    
                    <div class='button-wrapper'>
                        <a href='$setup_link' class='btn'>Activate My Account</a>
                    </div>
                    
                    <p style='color: #dc3545; font-size: 13px;'><strong>Note:</strong> This link is valid for 24 hours. For security, do not share this email with anyone.</p>
                </div>
                <div class='footer'>
                    <p>&copy; " . date('Y') . " Bactriz Logistics CRM. All rights reserved.</p>
                </div>
            </div>
        </body>
        </html>";

        // 4. Send the Email
        if (sendPHPMail($biz_email, $owner_name, "Welcome to Bactriz CRM - Activate Account", $htmlBody)) {
            mysqli_commit($conn);
            echo "success"; 
        } else {
            throw new Exception("Business created, but welcome email failed to send.");
        }

    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo $e->getMessage();
    }
}

// Create a New User
if(isset($_POST['action']) && $_POST['action'] == 'create_customer') {
    $email = $_POST['email'];
    $firstName = $_POST['firstname'];
    $lastName = $_POST['lastname'];
    $middleName = $_POST['middlename'];
    $phone = $_POST['phone'];
    $username = $_POST['username'];
    $password = $_POST['password'];
    $pass = password_hash($password, PASSWORD_DEFAULT);
    $role = $_POST['role'];
    $newFileName = 'default_pic.png';
    $business_id = $_SESSION['business_id'];
    
    if (isset($_FILES['profilePic']) && $_FILES['profilePic']['error'] == 0) {
        $profilePic = $_FILES['profilePic'];
        $file_tmp = $_FILES['profilePic']['tmp_name'];
        $file_name = basename($_FILES['profilePic']['name']);       
        $extension = pathinfo($file_name, PATHINFO_EXTENSION); 
        $fullName = strtolower($firstName.$lastName);
        $cleanName = preg_replace('/[^a-z0-9\s-]/', '', $fullName);
        $newFileName = $cleanName. '-' . date('Y-m-d') . '.' . $extension;
         $upload_dir = '../assets/images/avatar/'; 
        $destination = $upload_dir . $newFileName;

        if (move_uploaded_file($file_tmp, $destination)) {
            $profilePicPath = $destination; 
        } else {
            echo "Error uploading file.";
            exit;
        }
    } else {
        $profilePicPath = null;
    }
    $query = Querydb("SELECT * from users WHERE `email` = '$email' and username = '$username'");
    if(mysqli_num_rows($query) ==  0) {
        $query = "INSERT INTO `users`(`profile_img`, `first_name`, `last_name`, `middle_name`, `username`, `email`, `phone_no`, `role_id`, `password_hash`, `business_id`, `create_at`) VALUES ('$newFileName','$firstName','$lastName','$middleName','$username','$email','$phone','$role','$pass', `$business_id`, '$time')";
        if(Querydb($query)) {
            echo 1;
        } else {
            echo mysqli_error($conn);
        } 
    } else {
        echo "The Email given already exists in the Database";
    }    
}

if(isset($_POST['action']) && $_POST['action'] == 'create_user') {
    // 1. Sanitize Inputs
    $email = $_POST['email'];
    $firstName = $_POST['firstname'];
    $lastName = $_POST['lastname'];
    $middleName = $_POST['middlename'];
    $phone = $_POST['phone'];
    $role = $_POST['role']; 
    $business_id = $_SESSION['business_id'];
    
    $token = bin2hex(random_bytes(32));
    $expiry = date('Y-m-d H:i:s', strtotime('+24 hours'));
    $date = date('Y-m-d H:i:s');

    // 2. Profile Image Logic
    $newFileName = 'default_pic.png';
    if (isset($_FILES['profilePic']) && $_FILES['profilePic']['error'] == 0) {
        $extension = pathinfo($_FILES['profilePic']['name'], PATHINFO_EXTENSION); 
        $newFileName = strtolower($firstName.$lastName) . '-' . date('Y-m-d') . '.' . $extension;
        move_uploaded_file($_FILES['profilePic']['tmp_name'], '../assets/images/avatar/' . $newFileName);
    }

    // 3. Check for duplicates in the central log
    $check = Querydb("SELECT id FROM user_log WHERE email = '$email' AND business_id = '$business_id'");
    if($check->num_rows == 0) {
        
        mysqli_begin_transaction($conn);
        try {
            // A. Role-Based Table Routing
            if($role == 5) {
                $profile_query = "INSERT INTO `customers` (`name`, `email`, `phone`, `profile_img`, `business_id`, `create_at`) 
                                  VALUES ('$firstName $lastName', '$email', '$phone', '$newFileName', '$business_id', '$date')";
            } else {
                // Admin, Manager, Vendor, etc.
                $profile_query = "INSERT INTO `users` (`first_name`, `last_name`, `email`, `phone_no`, `profile_img`, `role_id`, `business_id`, `create_at`) 
                                  VALUES ('$firstName', '$lastName', '$email', '$phone', '$newFileName', '$role', '$business_id', '$date')";
            }
            
            Querydb($profile_query);
            $profile_id = $conn->insert_id; 

            // B. Insert into Central Auth (user_log)
            Querydb("INSERT INTO `user_log` (`user_id`, `email`, `role_id`, `invite_token`, `token_expiry`, `business_id`, `is_active`) 
                          VALUES ('$profile_id', '$email', '$role', '$token', '$expiry', '$business_id', 0)");

            // C. Send Invitation
            // Define the dynamic variables
            $setup_link = "192.168.0.177/bactriz_crm/activate.php?token=$token";
            $roleNames = [1 => 'Administrator', 2 => 'Manager', 3 => 'Staff', 4 => 'Vendor', 5 => 'Customer'];
            $roleDisplay = $roleNames[$role] ?? 'User';

            // The HTML Template
            $htmlBody = "
            <!DOCTYPE html>
            <html>
            <head>
                <style>
                    .email-container { font-family: 'Segoe UI', Arial, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; border: 1px solid #eee; border-radius: 8px; overflow: hidden; }
                    .header { background-color: #0d6efd; padding: 30px; text-align: center; color: #ffffff; }
                    .content { padding: 30px; background-color: #ffffff; }
                    .button-wrapper { text-align: center; margin: 30px 0; }
                    .btn { background-color: #0d6efd; color: #ffffff !important; padding: 14px 30px; text-decoration: none; border-radius: 50px; font-weight: bold; display: inline-block; font-size: 16px; box-shadow: 0 4px 6px rgba(13, 110, 253, 0.2); }
                    .footer { background-color: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #777; }
                    .badge { background: #e7f1ff; color: #0d6efd; padding: 4px 10px; border-radius: 4px; font-size: 13px; font-weight: 600; }
                </style>
            </head>
            <body>
                <div class='email-container'>
                    <div class='header'>
                        <h1 style='margin:0; font-size: 24px;'>Logistics Pro Portal</h1>
                    </div>
                    <div class='content'>
                        <p>Hello <strong>$firstName</strong>,</p>
                        <p>Welcome to the team! A new account has been prepared for you with the following role access:</p>
                        <p><span class='badge'>$roleDisplay</span></p>
                        <p>To access your dashboard and protect your account, please click the button below to set your unique <strong>Username</strong> and <strong>Password</strong>.</p>
                        
                        <div class='button-wrapper'>
                            <a href='$setup_link' class='btn'>Setup My Credentials</a>
                        </div>
                        
                        <p style='color: #dc3545; font-size: 13px;'><strong>Note:</strong> This invitation link is valid for 24 hours. If it expires, please contact your administrator for a new invite.</p>
                    </div>
                    <div class='footer'>
                        <p>&copy; " . date('Y') . " Your Logistics Company. All rights reserved.</p>
                        <p>This is an automated message, please do not reply directly to this email.</p>
                    </div>
                </div>
            </body>
            </html>";

            if(sendPHPMail($email, "$firstName $lastName", "Complete Your Registration", $htmlBody)) {
                mysqli_commit($conn);
                echo 1; 
            } else {
                echo ("Mail delivery failed.");
            }

        } catch (Exception $e) {
            mysqli_rollback($conn);
            echo "System Error: " . $e->getMessage();
        }
    } else {
        echo "The Email address already has an account.";
    }    
}

if(isset($_POST['action']) && $_POST['action'] == 'finalize_setup') {
    $token = mysqli_real_escape_string($conn, $_POST['token']);
    $username = mysqli_real_escape_string($conn, $_POST['username']);
    $password = $_POST['password'];

    // 1. Basic Validation
    if(strlen($password) < 8) {
        echo "Password must be at least 8 characters long.";
        exit;
    }

    // 2. Check if username is already taken
    $check = Querydb("SELECT id FROM user_log WHERE username = '$username'");
    if($check->num_rows > 0) {
        echo "This username is already taken. Please choose another.";
        exit;
    }

    // 3. Hash Password and Update
    $hashed_password = password_hash($password, PASSWORD_BCRYPT);
    
    $update = "UPDATE user_log SET 
               username = '$username', 
               password = '$hashed_password', 
               is_active = 1, 
               invite_token = NULL, 
               token_expiry = NULL 
               WHERE invite_token = '$token'";

    if(Querydb($update)) {
        echo 1;
    } else {
        echo "Error: " . $conn->error;
    }
}

// Edit a User
if(isset($_POST['action']) && $_POST['action'] == 'edit_user') {
    $user_id = $_POST['User_id'];
    $email = $_POST['email'];
    $firstName = $_POST['firstname'];
    $lastName = $_POST['lastname'];
    $middleName = $_POST['middlename'];
    $phone = $_POST['phone'];
    $preview = $_POST['preview'];
    $newFileName = $preview;
    
    if (isset($_FILES['profilePic']) && $_FILES['profilePic']['error'] == 0) {        
        $file_name = basename($_FILES['profilePic']['name']);
        if ($file_name != '') {
            $file_tmp = $_FILES['profilePic']['tmp_name'];
            $extension = pathinfo($file_name, PATHINFO_EXTENSION); 
            $fullName = strtolower($firstName.$lastName);
            $cleanName = preg_replace('/[^a-z0-9\s-]/', '', $fullName);
            $newFileName = $cleanName . '.' . $extension;
            $upload_dir = '../assets/images/avatar/'; 
            $destination = $upload_dir . $newFileName;      
            if (move_uploaded_file($file_tmp, $destination)) {
                $profilePicPath = $destination; 
            } else {
                echo "Error uploading file.";
                exit;
            }
        } 
    } 
    $check = Querydb("SELECT id FROM user_log WHERE email = '$email'");
    if($check) {

        $sql = Querydb("UPDATE `users` SET `profile_img` = '$newFileName', `first_name` = '$firstName', `last_name` = '$lastName', `middle_name` = '$middleName', `email`= '$email', `phone_no` = '$phone'  WHERE user_id = '$user_id'");
        if($sql) {
            echo 1;
        } else {
            echo mysqli_error($conn);
        }   
    } else {
        echo "The Email address already has an account.";
    }  
}

if(isset($_POST['action']) && $_POST['action'] == 'Update_Profile') {
    $user_id = $_SESSION['user_id'];
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];
    $middlename = $_POST['middlename'];
    $phone = $_POST['phone'];
    $email = $_POST['email'];
    $dob = $_POST['dob'];
    $gender = $_POST['gender'];
    $address = $_POST['address'];
    $bio = $_POST['bio'];
    $preview = $_POST['preview'];
    $newFileName = $preview;
    
    if (isset($_FILES['profilePic']) && $_FILES['profilePic']['error'] == 0) {        
        $file_name = basename($_FILES['profilePic']['name']);
        if ($file_name != '') {
            $file_tmp = $_FILES['profilePic']['tmp_name'];
            $extension = pathinfo($file_name, PATHINFO_EXTENSION); 
            $fullName = strtolower($firstname.$lastname);
            $cleanName = preg_replace('/[^a-z0-9\s-]/', '', $fullName);
            $newFileName = $cleanName . '.' . $extension;
            $upload_dir = '../assets/images/avatar/'; 
            $destination = $upload_dir . $newFileName;      
            if (move_uploaded_file($file_tmp, $destination)) {
                $profilePicPath = $destination; 
            } else {
                echo "Error uploading file.";
                exit;
            }
        } 
    } 
    
    $sql = Querydb("UPDATE `users` SET `profile_img` = '$newFileName', `first_name` = '$firstname', `last_name` = '$lastname', `middle_name` = '$middlename', `phone_no` = '$phone', `dob` = '$dob', `gender` = '$gender', `address` = '$address', `bio` = '$bio' WHERE user_id = '$user_id'");       
    if($sql) {
        // Querydb("UPDATE `user_log` SET `email` = '$email' WHERE user_id = '$user_id'");
        echo 1;
    } else {
        echo mysqli_error($conn);
    }          
}

// login query 
if (isset($_POST['action']) && $_POST['action'] == 'Authorize_signin') {
    $username = $_POST['username']; 
    $password_input = $_POST['password'];

    // SECURITY: Use mysqli_real_escape_string if not using prepared statements
    // However, I've used standard query structure to match your 'Querydb' function style
    $sql = "SELECT * FROM user_log WHERE (username = '$username' OR email = '$username') LIMIT 1";
    $result = Querydb($sql);
    $row = mysqli_fetch_assoc($result);
        
    if ($row) {
        if ($row['is_active'] == 0) {
            echo "BLOCKED"; 
            exit;
        }

        if (password_verify($password_input, $row['password'])) {
            $role_id = (int)$row['role_id'];
            
            // 1. ADDED SUPER ADMIN (Case 0)
            switch ($role_id) {
                case 0: $user_type = 'Super Admin'; break; // New Role
                case 1: $user_type = 'Admin'; break;
                case 2: $user_type = 'Manager'; break;
                case 3: $user_type = 'Vendor'; break;
                case 4: $user_type = 'User'; break;
                case 5: $user_type = 'Customer'; break;
                default: $user_type = 'Unknown';
            }

            $user_id = $row['user_id']; 

            // 2. Profile Fetching Logic
            if ($role_id == 0) {
                // Super Admins usually live in the 'users' table or a dedicated 'staff' table
                $profile_res = Querydb("SELECT 'System Master' as name, 'sup_admin.png' as profile_img");
            } elseif ($role_id == 5) {
                $profile_res = Querydb("SELECT name, profile_img FROM customers WHERE customer_id = '$user_id'");
            } else {
                $profile_res = Querydb("SELECT CONCAT(first_name, ' ', last_name) as name, profile_img FROM users WHERE user_id = '$user_id'");
            }
            
            $profile = mysqli_fetch_assoc($profile_res);

            // 3. Set Session Variables
            $_SESSION['user_id'] = $user_id;
            $_SESSION['log_id']  = $row['id']; 
            $_SESSION['role_id'] = $role_id;
            $_SESSION['business_id'] = $row['business_id'];
            $_SESSION['email']   = $row['email'];
            $_SESSION['full_name'] = $profile['name'] ?? 'User';
            $_SESSION['profile_img'] = $profile['profile_img'] ?? 'default_pic.png';
            $_SESSION['user_type'] = $user_type;

            // Trigger for both Admin and Super Admin
            if ($role_id == 1 || $role_id == 0) {
                $_SESSION['login_success_trigger'] = true;
            }
        
            Querydb("UPDATE user_log SET last_login = NOW() WHERE id = " . $row['id']);
            
            // Return the role_id to the AJAX caller for redirection
            echo $role_id;                      
        } else {
            echo "Login Failed: Invalid email or password";
        }
    } else {
        echo "Login Failed: Account not found";
    }
}

// User Password Reset by admin
if (isset($_POST['action']) && $_POST['action'] == 'Authorize_Reset_user_pass') {
    $user_id = $_POST['user_id']; 
    $password_input = '12345';
    $password_hash = password_hash($password_input, PASSWORD_DEFAULT);
    $sql = Querydb("UPDATE user_log set password = '$password_hash' where user_id = '$user_id' and role_id != 5");   
    if ($sql) {
        echo 1;
    } else {
        echo "password Updating failed";
    }
}

// User Password Reset by user
if (isset($_POST['action']) && $_POST['action'] == 'reset_password') {
    $forgotEmail = $_POST['email'];
    $query = Querydb("SELECT * FROM user_log WHERE email = '$forgotEmail'"); 
    if (mysqli_num_rows($query) > 0) {
        $otp = rand(100000, 999999);
        $expiry = date("Y-m-d H:i:s", strtotime("+15 minutes"));

        // 4. Update the database
        // Fixed: Ensure table name 'users' matches your SELECT query
        Querydb("UPDATE user_log SET otp_code = '$otp', otp_expiry = '$expiry' WHERE email = '$forgotEmail'");

        $mail = new PHPMailer(true);

        try {
            $mail->isSMTP();
            $mail->Host       = 'smtp.gmail.com'; 
            $mail->SMTPAuth   = true;
            $mail->Username   = 'bactrizcode@gmail.com'; 
            $mail->Password   = 'gvjfsljthxuqvbkz'; // Keep this safe! Use Environment Variables in production
            $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
            $mail->Port       = 587;

            // --- Email Content ---
            $mail->setFrom('bactrizcode@gmail.com', 'Bactriz CRM');
            $mail->addAddress($forgotEmail); 

            $mail->isHTML(true);
            $mail->Subject = 'Your Password Reset Code';
            
            $mail->Body = "
                <div style='font-family: Arial, sans-serif; border: 1px solid #ddd; padding: 20px; border-radius: 10px;'>
                    <h2 style='color: #4e73df;'>Password Reset Request</h2>
                    <p>Hello,</p>
                    <p>You requested a password reset. Use the code below to proceed. This code is valid for 15 minutes.</p>
                    <div style='background: #f8f9fc; padding: 15px; text-align: center; font-size: 24px; font-weight: bold; letter-spacing: 5px; color: #333; border: 1px dashed #4e73df;'>
                        $otp
                    </div>
                    <p style='margin-top: 20px;'>If you did not request this, please ignore this email.</p>
                    <hr style='border: 0; border-top: 1px solid #eee;'>
                    <small style='color: #777;'>Bactriz CRM Security Team</small>
                </div>
            ";

            $mail->send();
            echo "OTP has been sent to your email.";
            
        } catch (Exception $e) {
            echo "Failed to send email. Error: {$mail->ErrorInfo}";
        }    
    } else {
        echo "Email not found.";
    }
    // Removed the extra echo here
}

// --- STEP 2: VERIFY OTP ---
if (isset($_POST['action']) && $_POST['action'] == 'verify_otp') {
    $email = $_POST['email'];
    $otp = $_POST['otp'];
    $now = date("Y-m-d H:i:s");

    $query = Querydb("SELECT * FROM user_log WHERE email = '$email' AND otp_code = '$otp'");
    
    if (mysqli_num_rows($query) > 0) {
        $user = mysqli_fetch_assoc($query);
        if ($now <= $user['otp_expiry']) {
            echo "Success";
        } else {
            echo "OTP has expired.";
        }
    } else {
        echo "Invalid OTP code.";
    }
}

// --- STEP 3: UPDATE PASSWORD ---
if (isset($_POST['action']) && $_POST['action'] == 'update_password') {
    $email = $_POST['email'];
    $password = $_POST['password'];
    
    // Hash the password for security
    $hashed_password = password_hash($password, PASSWORD_BCRYPT);

    // Update password and CLEAR OTP so it can't be reused
    $sql = "UPDATE user_log SET 
            `password` = '$hashed_password', 
            otp_code = NULL, 
            otp_expiry = NULL 
            WHERE email = '$email'";

    if (Querydb($sql)) {
        echo "Password updated successfully!";
    } else {
        echo "Failed to update password.";
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'change_password_logged_in') {
    $user_id = $_SESSION['user_id'];
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    $current_pass = $_POST['currPass'];
    $new_pass = $_POST['confirm_password'];
    $log_id = $_SESSION['log_id'];

    // 1. Fetch current hashed password from the user_log table
    // We use $log_id to ensure we are updating the correct session/entry
    $query = mysqli_query($conn, "SELECT password FROM user_log WHERE user_id = '$user_id' AND id = '$log_id' LIMIT 1");
    
    if ($user = mysqli_fetch_assoc($query)) {
        
        // 2. Verify the "Current Password"
        if (password_verify($current_pass, $user['password'])) {
            
            // 3. Hash the NEW password
            $hashed_new_password = password_hash($new_pass, PASSWORD_BCRYPT);

            // 4. Update the database
            $update = mysqli_query($conn, "UPDATE user_log SET `password` = '$hashed_new_password' WHERE user_id = '$user_id' AND id = '$log_id'");

            if ($update) {                
                echo "successfully";
            } else {
                echo "Error: Database update failed.";
            }
        } else {
            echo "The current password you entered is incorrect.";
        }
    } else {
        echo "User session record not found.";
    }
    exit;
}
// User Sign Out
if (isset($_POST['action']) && $_POST['action'] == 'authorize_sign_out') {
    session_unset();
    session_destroy();
    echo 'logout_success';
} 
// Create a New Driver Record
if(isset($_POST['action']) && $_POST['action'] == 'create_Driver') {
    $DriverLicence = $_POST['DriverLicence'];
    $firstName = $_POST['firstname'];
    $lastName = $_POST['lastname'];
    $middleName = $_POST['middlename'];
    $phone = $_POST['phone_no'];
    $g1_name = $_POST['g1_name'];
    $g1_phone = $_POST['g1_phone'];
    $g2_name = $_POST['g2_name'];
    $g2_phone = $_POST['g2_phone'];
    $newFileName = 'default_pic.png';
    $business_id = $_SESSION['business_id'];
    
    if (isset($_FILES['profilePic']) && $_FILES['profilePic']['error'] == 0) {
        $profilePic = $_FILES["profilePic"];
        $file_tmp = $_FILES['profilePic']['tmp_name'];
        $file_name = basename($_FILES['profilePic']['name']);       
        $extension = pathinfo($file_name, PATHINFO_EXTENSION); 
        $fullName = strtolower($firstName.$lastName);
        $cleanName = preg_replace('/[^a-z0-9\s-]/', '', $fullName);
        $newFileName = $cleanName. '-' . date('Y-m-d') . '.' . $extension;
         $upload_dir = '../assets/images/avatar/'; 
        $destination = $upload_dir . $newFileName;

        if (move_uploaded_file($file_tmp, $destination)) {
            $profilePicPath = $destination; 
        } else {
            echo "Error uploading file.";
            exit;
        }
    } else {
        $profilePicPath = null; // No file uploaded
    }

    $docFile = '';
    if (!empty($_FILES['driverDoc']['name'])) {
        $docFile = time() . '_doc_' . $_FILES['driverDoc']['name'];
        move_uploaded_file($_FILES['driverDoc']['tmp_name'], "../assets/documents/$docFile");
    }
    $query = Querydb("INSERT INTO `drivers`(`profile_img`, `first_name`, `last_name`, `middle_name`,  `license_number`, `phone_number`, `license_doc`, `g1_name`, `g1_phone`, `g2_name`, `g2_phone`, `is_active`, `business_id`, `create_at`) VALUES ('$newFileName','$firstName','$lastName','$middleName','$DriverLicence','$phone', '$docFile', '$g1_name', '$g1_phone', '$g2_name', '$g2_phone', 1, '$business_id', '$time')");
    if($query) {
        echo 1;
    } else {
        echo 'Error Inserting record';
    }  
}
// Edit a Driver Record
if(isset($_POST['action']) && $_POST['action'] == 'edit_driver') {
    $driver_id = $_POST['driver_id'];
    $firstName = $_POST['firstname'];
    $lastName  = $_POST['lastname'];
    $middleName= $_POST['middlename'];
    $phone     = $_POST['phone'];
    $licenseNo = $_POST['DriverLicence'];
    $business_id = $_SESSION['business_id'];
    
    // Guarantors
    $g1_name   = $_POST['g1_name'];
    $g1_phone  = $_POST['g1_phone'];
    $g2_name   = $_POST['g2_name'];
    $g2_phone  = $_POST['g2_phone'];

    // 1. Handle Profile Image (Preview check)
    $newProfilePic = $_POST['current_profile_img']; // Hidden field from your form
    if (isset($_FILES['profilePic']) && $_FILES['profilePic']['error'] == 0) {
        $ext = pathinfo($_FILES['profilePic']['name'], PATHINFO_EXTENSION);
        $newProfilePic = strtolower($firstName.$lastName) . '_' . time() . '.' . $ext;
        move_uploaded_file($_FILES['profilePic']['tmp_name'], '../assets/images/avatar/' . $newProfilePic);
    }

    // 2. Handle License Document Upload
    $newLicenseDoc = $_POST['current_license_doc']; // Hidden field from your form
    if (isset($_FILES['driverDoc']) && $_FILES['driverDoc']['error'] == 0) {
        $ext = pathinfo($_FILES['driverDoc']['name'], PATHINFO_EXTENSION);
        $newLicenseDoc = 'doc_' . strtolower($lastName) . '_' . time() . '.' . $ext;
        move_uploaded_file($_FILES['driverDoc']['tmp_name'], '../assets/documents/' . $newLicenseDoc);
    }

    // 3. Unified Update Query
    // Note: Assuming your table is 'users' based on our previous multi-role setup
    $sql = "UPDATE `drivers` SET `profile_img` = '$newProfilePic', `license_doc` = '$newLicenseDoc',`first_name` = '$firstName', `last_name` = '$lastName', `middle_name` = '$middleName', `phone_number` = '$phone', `license_number` = '$licenseNo', `g1_name` = '$g1_name',`g1_phone` = '$g1_phone',`g2_name` = '$g2_name',`g2_phone` = '$g2_phone'
            WHERE driver_id = '$driver_id' AND business_id = '$business_id'";

    if($conn->query($sql)) {
        echo 1;
    } else {
        echo "Update Failed: " . $conn->error;
    }    
}
// Create a New Truck Record
if(isset($_POST['action']) && $_POST['action'] == 'Truck_record') {
    // Trim inputs to remove accidental spaces
    $license_plate = trim($_POST['license_plate']);
    $vin = trim($_POST['vin']);
    $make = $_POST['make'];
    $model = $_POST['model'];
    $year = $_POST['year'];
    $registration_start = $_POST['registration_start'];
    $registration_end = $_POST['registration_end'];
    $driver_id = $_POST['driver_id'];
    $business_id = $_SESSION['business_id'];
     
    // 1. Check if License Plate OR VIN already exists for this business
    $check_query = "SELECT truck_id FROM trucks 
                    WHERE (license_plate = '$license_plate' OR vin = '$vin') 
                    AND business_id = '$business_id' LIMIT 1";
    
    $check_result = Querydb($check_query);

    if (mysqli_num_rows($check_result) == 0) {
        // 2. No Duplicate Found - Proceed to Insert
        $sql = Querydb("INSERT into `trucks` 
            (`license_plate`, `make`, `model`, `year`, `vin`, `registration_start`, `registration_end`, `driver_id`, `business_id`, `create_at`) 
            VALUES 
            ('$license_plate', '$make', '$model', '$year', '$vin', '$registration_start', '$registration_end', '$driver_id', '$business_id', '$time')");
        
        if($sql) {
            Querydb("UPDATE drivers SET is_active = '1' WHERE driver_id = '$driver_id' AND business_id = '$business_id'");
            echo 1; // Success
        } else {
            echo "Truck Record Failed: Database Error";
        }    
    } else {
        // 3. Duplicate Found
        echo "Error: A truck with this License Plate or VIN already exists in your fleet.";
    }
} 
// Edit a Truck Record
if(isset($_POST['action']) && $_POST['action'] == 'edit_truck_record') {
    $truck_id = $_POST['truck_id'];
    $new_driver_id = $_POST['driver_id']; 
    $current_status = $_POST['current_status']; // e.g., 'Active', 'Maintenance', 'Inactive'
    $business_id = $_SESSION['business_id'];

    $get_old = Querydb("SELECT driver_id FROM trucks WHERE truck_id = '$truck_id' AND business_id = '$business_id' LIMIT 1");
    $old_data = mysqli_fetch_assoc($get_old);
    $old_driver_id = $old_data['driver_id'];

    $sql = Querydb("UPDATE `trucks` SET 
        `license_plate`= '{$_POST['license_plate']}', 
        `make`= '{$_POST['make']}', 
        `model`= '{$_POST['model']}', 
        `year`= '{$_POST['year']}', 
        `vin`= '{$_POST['vin']}', 
        `registration_start` = '{$_POST['registration_start']}', 
        `registration_end` = '{$_POST['registration_end']}', 
        `driver_id`= '$new_driver_id', 
        `current_status`= '$current_status' 
        WHERE truck_id = '$truck_id' AND business_id = '$business_id'");
    if($sql) {
        if ($current_status == 'Maintenance' || $current_status == 'Inactive') {
            Querydb("UPDATE drivers SET is_active  = '0' WHERE driver_id = '$new_driver_id' AND business_id = '$business_id'");
        } 
        else if ($old_driver_id != $new_driver_id) {
            Querydb("UPDATE drivers SET is_active  = '0' WHERE driver_id = '$old_driver_id' AND business_id = '$business_id'");
            Querydb("UPDATE drivers SET is_active  = '1' WHERE driver_id = '$new_driver_id' AND business_id = '$business_id'");
        }
        echo 1;
    } else {
        echo "Truck Record Update Failed";
    }    
}
// Create a New Customer Record
if(isset($_POST['action']) && $_POST['action'] == 'customer_record') {
    $name = $_POST['name'];
    $phone = $_POST['phone'];
    $email = $_POST['email'];
    $address = $_POST['address'];
    $sql = Querydb("INSERT into `customers` (`name`, `phone`, `email`,`address`,`create_at`) VALUES ('$name', '$phone', '$email', '$address', '$time')");
    if($sql) {
        echo 1;
    } else {
        echo "Customer Record Failed";
    }    
}
// Edit a Customer Record
if(isset($_POST['action']) && $_POST['action'] == 'edit_customer_record') {
    $customer_id = $_POST['customer_id'];
    $name = $_POST['name'];
    $phone = $_POST['phone'];
    $email = $_POST['email'];
    $address = $_POST['address'];
    $sql = Querydb("UPDATE `customers` SET  `name` = '$name', `phone` = '$phone', `email` = '$email',`address` = '$address' WHERE customer_id = $customer_id");
    if($sql) {
        echo 1;
    } else {
        echo "Customer Record Failed";
    }    
}
// Create a New Maintenance Record
if(isset($_POST['action']) && $_POST['action'] == 'maintence_record') {
    $truck_license = $_POST['truck_license'];
    $Maintence_type = $_POST['Maintence_type'];
    $cost = $_POST['cost'];
    $datecarryout = $_POST['datecarryout'];
    $vendor = $_POST['vendor_id'];
    $Description = $_POST['Description'];
    $user_id = $_SESSION['user_id'];
    $vendor_status = 0;
    $business_id = $_SESSION['business_id'];
    ($vendor == 0) ? '$vendor_status = 1' : '';

    $sql = Querydb("INSERT into `maintenance` (`truck_id`, `service_type`, `cost`,`date_performed`,`description`, `vendor_id`, `user_id`, `vendor_status`, `day`, `week`, `month`, `year`, `business_id`, `create_at`) VALUES ('$truck_license', '$Maintence_type', '$cost', '$datecarryout', '$Description', '$vendor', '$user_id', '$vendor_status', '".date('d')."', '".date('W')."', '".date('m')."', '".date('Y')."', '$business_id', '$time')");
    if($sql) {
        echo 1;
    } else {
        echo "Customer Record Failed";
    }    
}
// Edit a Maintenance Record
if(isset($_POST['action']) && $_POST['action'] == 'edit_maintence_record') {
    $user_id = $_SESSION['user_id'];
    $maintence_id = $_POST['maintence_id'];
    $truck_license = $_POST['truck_license'];
    $Maintence_type = $_POST['Maintence_type'];
    $cost = $_POST['cost'];
    $datecarryout = $_POST['datecarryout'];
    $vendor = $_POST['vendor_id'];
    $Description = $_POST['Description'];
    $vendor_status = 0;
    $business_id = $_SESSION['business_id'];
    ($vendor == 0) ? '$vendor_status = 1' : '';

    $sql = Querydb("SELECT * FROM maintenance WHERE maintenance_id = '$maintence_id' AND vendor_status = 1 AND vendor_id != ' '");
    if (mysqli_num_rows($sql) > 0) {
        echo "Sorry u can not Edit this record the vendor as Comfirm";
    } else {
        $sql = Querydb("UPDATE `maintenance` SET `truck_id`= '$truck_license', `service_type`= '$Maintence_type', `cost`= '$cost', `date_performed`= '$datecarryout', `description`= '$Description', `vendor_id` = '$vendor', `vendor_status` = '$vendor_status', `edit_user_id` = '$user_id' WHERE maintenance_id = '$maintence_id' AND business_id = '$business_id'");
        if($sql) {
            echo 1;
        } else {
            echo "Maintenance Record Update Failed";
        }      
    }
}
// Create a New ATC Record
if(isset($_POST['action']) && $_POST['action'] == 'atc_record') {
    // Trim accidental whitespace from inputs
    $atc_num = trim($_POST['atc_num']);
    $volume  = $_POST['volume'];
    $type    = $_POST['type'];
    $user_id = $_SESSION['user_id'];
    $business_id = $_SESSION['business_id'];
    
    // 1. Check for Duplicate within the same business
    // Using LOWER() ensures 'atc123' and 'ATC123' are treated as the same
    $check_query = "SELECT atc_id FROM atc_records 
                    WHERE LOWER(atc_number) = LOWER('$atc_num') 
                    AND business_id = '$business_id' LIMIT 1";
    
    $check_result = Querydb($check_query);

    if (mysqli_num_rows($check_result) == 0) {
        // 2. Proceed with Insert if no duplicate found
        $sql = Querydb("INSERT into `atc_records` 
            (`atc_number`, `type`, `volume`, `user_id`, `day`, `week`, `month`, `year`, `business_id`, `create_at`) 
            VALUES 
            ('$atc_num', '$type', '$volume', '$user_id', '".date('d')."', '".date('W')."', '".date('m')."', '".date('Y')."', '$business_id', '$time')");
        
        if($sql) {
            echo 1; // Success
        } else {
            echo "Error: ATC record could not be saved.";
        }    
    } else {
        // 3. Duplicate Found
        echo "Duplicate Entry: ATC Number '$atc_num' already exists in your records.";
    }
}
// Edit an ATC Record
if(isset($_POST['action']) && $_POST['action'] == 'edit_atc_record') {
    $atc_id = $_POST['atc_id'];
    $atc_num = $_POST['atc_num'];
    $type = $_POST['type'];
    $volume = $_POST['volume'];
    $business_id = $_SESSION['business_id'];

    $sql = Querydb("UPDATE `atc_records` SET `atc_number`= '$atc_num', `type` = '$type', `volume`= '$volume' WHERE atc_id = '$atc_id' AND business_id = '$business_id'");
    if($sql) {
        echo 1;
    } else {
        echo "ATC Record Update Failed";
    }    
}
// Create a New Trip Record
if(isset($_POST['action']) && $_POST['action'] == 'createTrip') {
    // 1. Sanitize Inputs
    $unit        = (float)$_POST['unit'];
    $atc_id      = mysqli_real_escape_string($conn, $_POST['atc_id']);
    $selling     = (float)$_POST['selling'];
    $cost        = (float)$_POST['cost'];
    $allowance   = (float)$_POST['allowance'];
    $customer_id = mysqli_real_escape_string($conn, $_POST['customer_id']);
    $location    = mysqli_real_escape_string($conn, $_POST['location']);
    $order_id    = isset($_POST['order_id']) ? mysqli_real_escape_string($conn, $_POST['order_id']) : null;
    $user_id     = $_SESSION['user_id'];
    $business_id = $_SESSION['business_id'];
    $user_id = $_SESSION['user_id'];

    // 2. Capacity Check Logic
    $atc_sql  = Querydb("SELECT volume FROM atc_records WHERE atc_id = '$atc_id' AND business_id = '$business_id'");
    $atc_data = mysqli_fetch_assoc($atc_sql);
    $type = $atc_data['type'];
    
    $trip_sql   = Querydb("SELECT SUM(unit) as total_used FROM trips WHERE atc_id = '$atc_id' AND status != 'Cancelled' AND business_id = '$business_id'");
    $trip_data  = mysqli_fetch_assoc($trip_sql);
    $remaining  = $atc_data['volume'] - ($trip_data['total_used'] ?? 0);
    $remainder = $remaining - $unit;

    if ($unit > $remaining) {
        echo "Insufficient volume. Remaining: " . $remaining;
        exit;
    }

    // 3. TRANSACTION START (Ensures both tables update or none do)
    mysqli_begin_transaction($conn);

    try {
        // B. Update or Insert Order Record
        Querydb("UPDATE atc_records SET atc_status = 'Dispatched' WHERE atc_id = '$atc_id' AND business_id = '$business_id'");
        if (!empty($order_id)) {
            // SCENARIO 1: Existing Order - Just update the status and attach ATC
            Querydb("UPDATE orders SET status = 'In Progress', varify_code = 'warning' WHERE order_id = '$order_id' AND business_id = '$business_id'");
        } else {
            // SCENARIO 2: Manual/Walk-in - Create new order record
            $order_id = generateSerialOrderId();
            Querydb("INSERT INTO orders (order_id, product_name, customer_id, quantity, location, order_date, status, business_id, varify_code) 
                     VALUES ('$order_id', '$type', '$customer_id', '$unit', '$location', '".date('Y-m-d')."', 'In Progress', '$business_id', 'warning')");
        }
        // A. Insert the Trip Record
        $insert_trip = "INSERT INTO trips (unit, atc_id, order_id, selling_price, cost_price, driver_allowence, customer_id, location, user_id, `day`, `week`, `month`, `year`, create_at, business_id, status) 
                        VALUES ('$unit', '$atc_id', '$order_id', '$selling', '$cost', '$allowance', '$customer_id', '$location', '$user_id', '".date('d')."', '".date('W')."', '".date('m')."', '".date('Y')."', '$time', '$business_id', 'In Progress')";
        Querydb($insert_trip);


        // C. Update ATC status if exhausted
        if (($remaining - $unit) <= 0) {
            Querydb("UPDATE atc_records SET atc_status = 'Dispatched' WHERE atc_id = '$atc_id' AND business_id = '$business_id'");
        }
        if ($remainder) {

        }

        mysqli_commit($conn);
        echo 1; // Success

    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo "Error processing logistics: " . $e->getMessage();
    }
}

// Edit a Trip Record
if(isset($_POST['action']) && $_POST['action'] == 'editTrip') {
    $trip_id = $_POST['trip_id'];
    $unit = floatval($_POST['unit']);
    $atc_id = $_POST['atc_id'];
    $selling = $_POST['selling'];
    $allowance = $_POST['allowance'];
    $cost = $_POST['cost'];
    $customer_id = $_POST['customer_id'];
    $location = $_POST['location'];
    // $status = $_POST['status'];
    $date = date('Y-m-d H:i:s');
    $business_id = $_SESSION['business_id'];
    $user_id = $_SESSION['user_id'];

    // 1. Fetch ATC details and associated Order ID from the trip
    $current_trip_sql = Querydb("SELECT t.*, u.wallet_balance, u.customer_id as user_db_id 
                                 FROM trips t 
                                 JOIN customers u ON t.customer_id = u.customer_id 
                                 WHERE t.trip_id = '$trip_id' LIMIT 1");
    $current_trip = mysqli_fetch_assoc($current_trip_sql);
    $associated_order_id = $current_trip['order_id'];

    $atc_sql = Querydb("SELECT * FROM atc_records WHERE atc_id = '$atc_id'");
    $atc_data = mysqli_fetch_assoc($atc_sql);

    if (mysqli_num_rows($atc_sql) > 0) {
        // 2. Capacity Validation (excluding current trip)
       // 1. Calculate how much has already been used by other (non-cancelled) trips
        $trip_sum_sql = Querydb("SELECT sum(unit) as total_unit FROM trips WHERE atc_id = '$atc_id' AND trip_id != '$trip_id' AND status != 'Cancelled' AND business_id = '$business_id'");
        $trip_sum_data = mysqli_fetch_assoc($trip_sum_sql);
        $total_used_by_others = floatval($trip_sum_data['total_unit'] ?? 0);

        // 2. Calculate remaining capacity
        $capacity_remaining = floatval($atc_data['volume']) - $total_used_by_others;

        // 3. VALIDATION FIRST: Check if the requested $unit fits
        if ($unit > $capacity_remaining) {
            echo "Insufficient volume. Only " . $capacity_remaining . " units available.";
            exit;
        }

        // 4. If capacity is okay, proceed with fetching data and inserting

        // 3. Start Transaction for Data Integrity
        mysqli_begin_transaction($conn);

        try {
            $remainder = $capacity_remaining - $unit;
            if ($remainder > 0) {
                $get_trip_sql = Querydb("SELECT * FROM trips WHERE trip_id = '$trip_id' AND business_id = '$business_id'");
                $get_trip = mysqli_fetch_assoc($get_trip_sql);
                
                // Use clear variable names to avoid overwriting IDs
                $current_truck_id = $get_trip['truck_id'];
                $current_driver_id = $get_trip['driver_id'];
                $type = $atc_data['type'];
                $curr_unit = $capacity_remaining - $unit;
                
                $order_id = generateSerialOrderId();
                
                // Create the Order
                Querydb("INSERT INTO orders (order_id, customer_id, product_name, business_id) VALUES ('$order_id', '$customer_id', '$type', '$business_id')");

                // Create the Trip using the $unit requested (or $capacity_remaining if that's your logic)
                $sql = "INSERT INTO trips (truck_id, driver_id, atc_id, business_id, user_id, order_id, unit, create_at) 
                        VALUES ('$current_truck_id', '$current_driver_id', '$atc_id', '$business_id', '$user_id', '$order_id', '$curr_unit', '$time')";
                
                $insertTrip = Querydb($sql);
            } 
                // Update the Trip table
                Querydb("UPDATE trips SET unit = '$unit', atc_id = '$atc_id', customer_id = '$customer_id', selling_price = '$selling', cost_price = '$cost', driver_allowence = '$allowance', location = '$location' WHERE trip_id = '$trip_id' AND business_id = '$business_id'");
                Querydb("UPDATE atc_records SET atc_status = 'Dispatched' WHERE atc_id = '$atc_id' AND business_id = '$business_id'");
                Querydb("UPDATE orders SET customer_id = '$customer_id', quantity = '$unit',  status = 'In Transit', location = '$location' WHERE order_id = '$associated_order_id' AND business_id = '$business_id'");

            mysqli_commit($conn);
            echo 1;

        } catch (Exception $e) {
            mysqli_rollback($conn);
            echo "Update failed: " . $e->getMessage();
        }
    }
}

// User Role Access Control management update
// if(isset($_POST['action']) && $_POST['action'] == 'update_user_acces') {
//     $user_id = $_POST['user_id'];
//     $pages = $_POST['pages'];
//     $pages_string = implode(',', $pages);

//     $check_sql = Querydb("select * from user_page_permissions where user_id = '$user_id'");
//     if (mysqli_num_rows($check_sql) > 0) {
//         $sql = Querydb("UPDATE user_page_permissions SET page = '$pages_string' WHERE user_id = $user_id");
//         if ($sql) {
//             echo 1;
//         } else {
//             echo 'Error updating user access';
//         }
//     } else {
//        $sql = Querydb("INSERT INTO user_page_permissions (user_id, page, allowed, created_at) VALUES ($user_id, '$pages', 1, $time)");
//         if ($sql) {
//             echo 1;
//         } else {
//             echo 'Error Inserting user access';
//         } 
//     } 
// }
if (isset($_POST['action']) && $_POST['action'] == 'update_user_access') {
    $user_id = mysqli_real_escape_string($conn, $_POST['user_id']);
    
    // 1. Handle Checkboxes (If none selected, set to empty array)
    $pages_array = isset($_POST['pages']) ? $_POST['pages'] : [];
    
    // 2. Sanitize each page name/URL
    $sanitized_pages = array_map(function($page) use ($conn) {
        return mysqli_real_escape_string($conn, trim($page));
    }, $pages_array);
    
    // 3. Convert array back to a comma-separated string
    $page_string = implode(',', $sanitized_pages);

    // 4. Update the Database
    // Check if a record already exists for this user
    $check_exists = Querydb("SELECT user_id FROM user_page_permissions WHERE user_id = '$user_id'");

    if (mysqli_num_rows($check_exists) > 0) {
        // Update existing record
        $sql = "UPDATE user_page_permissions SET page = '$page_string' WHERE user_id = '$user_id'";
    } else {
        // Create new record
        $sql = "INSERT INTO user_page_permissions (user_id, page) VALUES ('$user_id', '$page_string')";
    }

    if (Querydb($sql)) {
        echo 1;
    } else {
        echo "Error updating permissions: " . mysqli_error($conn);
    }
    exit;
}
if(isset($_POST['action']) && $_POST['action'] == 'admin_verify_topup') {
    $tid =  $_POST['topup_id'];
    $status = $_POST['status']; // 'Approve' or 'Decline'
    $business_id = $_SESSION['business_id'];

    // 1. Get the specific transaction record using the ID
    $topup = mysqli_fetch_assoc(Querydb("SELECT * FROM transactions WHERE transaction_id = '$tid' AND business_id = '$business_id'"));
    
    if (!$topup) { 
        echo "Transaction record not found"; 
        exit; 
    }
    
    // Safety check: Don't process if already completed
    if ($topup['transaction_status'] !== 'Pending') {
        echo "This transaction has already been processed.";
        exit;
    }

    $customer_id = $topup['customer_id'];
    $amount = $topup['amount'];

    if($status == 'Approve') {
        // 2. Fetch Customer's Current Balance
        $user_data = mysqli_fetch_assoc(Querydb("SELECT wallet_balance FROM customers WHERE customer_id = '$customer_id'"));
        $bal_before = $user_data['wallet_balance'];
        $bal_after = $bal_before + $amount;
        
        // 3. Update Customer Wallet
        // We use the direct connection variable here to avoid overwriting it
        $update_wallet = Querydb("UPDATE customers SET wallet_balance = '$bal_after' WHERE customer_id = '$customer_id'");
        
        // 4. Update the specific Transaction record
        $update_trans = Querydb("UPDATE transactions SET 
                                transaction_status = 'Successful', 
                                varify_code = 'Success',
                                bal_before = '$bal_before', 
                                bal_after = '$bal_after' 
                              WHERE transaction_id = '$tid'");
        
        if($update_wallet && $update_trans) {
            echo 1;
        } else {
            echo "Failed to update records.";
        }

    } else {
        // 5. Handle Decline (status: 'failed' or 'Declined')
        $decline = Querydb("UPDATE transactions SET transaction_status = 'failed' WHERE transaction_id = '$tid'");
        if($decline) {
            echo 1;
        } else {
            echo "Failed to update transaction status.";
        }
    }
    exit;
}

if(isset($_POST['action']) && $_POST['action'] == 'getTransactionDetails') {
    $tid = mysqli_real_escape_string($conn, $_POST['tid']);
    $query = Querydb("SELECT t.*, c.name as customer_name, c.phone 
                      FROM transactions t 
                      JOIN customers c ON t.customer_id = c.customer_id 
                      WHERE t.transaction_id = '$tid'");
    $data = mysqli_fetch_assoc($query);

    if($data) {
        $color = (strtolower($data['type']) == 'credit') ? 'text-success' : 'text-danger';
        ?>
        <div class="p-2">
            <div class="text-center mb-4">
                <small class="text-muted d-block">Total Amount</small>
                <h2 class="fw-bold <?php echo $color; ?>">₦<?php echo number_format(abs($data['amount']), 2); ?></h2>
                <span class="badge bg-light text-dark border"><?php echo $data['trans_id']; ?></span>
            </div>
            <hr>
            <div class="row g-3">
                <div class="col-6"><small class="text-muted d-block">Customer</small><strong><?php echo $data['customer_name']; ?></strong></div>
                <div class="col-6"><small class="text-muted d-block">Date</small><strong><?php echo date('d M Y, h:i A', strtotime($data['created_at'])); ?></strong></div>
                <div class="col-6"><small class="text-muted d-block">Type</small><strong class="text-uppercase"><?php echo $data['type']; ?></strong></div>
                <div class="col-6"><small class="text-muted d-block">Status</small><strong><?php echo $data['transaction_status']; ?></strong></div>
                <div class="col-6"><small class="text-muted d-block">Balance Before</small><strong>₦<?php echo number_format($data['bal_before'], 2); ?></strong></div>
                <div class="col-6"><small class="text-muted d-block">Balance After</small><strong>₦<?php echo number_format($data['bal_after'], 2); ?></strong></div>
                <div class="col-12"><small class="text-muted d-block">Description</small><strong><?php echo $data['description']; ?></strong></div>
            </div>
        </div>
        <?php
    } else {
        echo "Transaction not found.";
    }
    exit;
}

if(isset($_POST['action']) && $_POST['action'] == 'updateFullAssignment') {
    $trip_id = $_POST['trip_id'];
    $truck_id = $_POST['truck_id'];
    $driver_id = $_POST['driver_id'];
    $filling_station = $_POST['filling_station'];
    $initial_fuel = $_POST['initial_fuel'];
    $liters_filled = (float)$_POST['liters_filled'];
    $liters_used = (float)$_POST['used_liters'];
    $cost_per_liter = (float)$_POST['cost_per_liter'];
    $date_filled = $_POST['date_filled'];
    $business_id = $_SESSION['business_id'];

    // 1. SERVER-SIDE VALIDATION
    if($liters_filled <= 0 || empty($filling_station || empty($liters_used))) {
        echo "Error: Fuel details are incomplete. Operation aborted.";
        exit;
    }
    $liter_left = $initial_fuel + $liters_filled - $liters_used;
    // 2. UPDATE THE TRIP RECORD
    $trip_sql = "UPDATE trips SET 
                truck_id = '$truck_id', 
                driver_id = '$driver_id', 
                filling_station = '$filling_station', 
                initial_fuel = '$initial_fuel',
                liters_filled = '$liters_filled', 
                cost_per_liter = '$cost_per_liter',
                used_liters = '$liters_used', 
                date_filled = '$date_filled'
                WHERE trip_id = '$trip_id' AND business_id = '$business_id'";

    $updateTruck = "UPDATE trucks SET initial_fuel = '$liter_left' WHERE truck_id = '$truck_id'";

    if (Querydb($trip_sql) && Querydb($updateTruck)) {
        echo 1;
    } else {
        echo "error: Could not update record.";
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'toggle_user_status') {
    $log_id = $_POST['log_id'];
    $userid = $_POST['user_id'];
    $current_status = (int)$_POST['current_status'];
    
    // Switch the status: if 1 make it 0, if 0 make it 1
    $new_status = ($current_status == 1) ? 0 : 1;

    $sql = "UPDATE user_log SET is_active = '$new_status' WHERE id = '$log_id'";
    
    if ($conn->query($sql)) {
        $sql = Querydb("UPDATE users SET is_active = '$new_status' WHERE user_id = '$userid'");
        if($sql){
            echo 1;            
        }
    } else {
        echo "Failed to update user status.";
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'global_status_update') {
    $driver_id = $_POST['user_id'];
    $target_status = $_POST['target_status'];

    // 1. Validation: If trying to set status to 2, check for truck assignments
    if ($target_status == 0) {
        $check_truck = "SELECT license_plate FROM trucks WHERE driver_id = '$driver_id' LIMIT 1";
        $result = Querydb($check_truck);

        if ($result && mysqli_num_rows($result) > 0) {
            $truck = mysqli_fetch_assoc($result);
            echo "Error: This driver is still assigned to truck [" . $truck['license_plate'] . "]. Unassign them first.";
            exit;
        }
    }

    // 2. Proceed with Update if validation passes
    $sql = "UPDATE drivers SET is_active = '$target_status' WHERE driver_id = '$driver_id'";
    
    if ($conn->query($sql)) {
        echo 1;
    } else {
        echo 'Update failed';
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'toggle_customer_status') {
    $log_id = mysqli_real_escape_string($conn, $_POST['log_id']);
    $user_id = mysqli_real_escape_string($conn, $_POST['user_id']);
    $status = (int)$_POST['status'];

    // Update both tables to ensure the profile and the login match
    $sql1 = "UPDATE user_log SET is_active = '$status' WHERE id = '$log_id'";
    $sql2 = "UPDATE customers SET is_active = '$status' WHERE customer_id = '$user_id'";

    if ($conn->query($sql1) && $conn->query($sql2)) {
        echo 1;
    } else {
        echo "Database Error: " . $conn->error;
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'update_finance') {
    $trip_id = $_POST['trip_id'];
    $haulage = $_POST['haulage_amount'];
    $promo = $_POST['promo_amount'];
    $incentive = $_POST['incentive_amount'];
    $business_id = $_SESSION['business_id'];

    $sql = "UPDATE trips SET 
            haulage_amount = '$haulage', 
            promo_amount = '$promo', 
            incentive_amount = '$incentive' 
            WHERE trip_id = '$trip_id' AND business_id = '$business_id' ";

    if (Querydb($sql)) {
       echo 1;
    } else {
        echo "Error updating rates.";
    }
}

if(isset($_POST['action']) && $_POST['action'] == 'resend_invite') {
    $log_id = $_POST['log_id'];
    $email = $_POST['email'];
    
    // 1. Find the user and their role
    $check = Querydb("SELECT user_id, role_id FROM user_log WHERE id = '$log_id' AND is_active = 0 AND invite_token != ' '");
    
    if(mysqli_num_rows($check) > 0) {
        $row = mysqli_fetch_assoc($check);
        $profile_id = $row['user_id'];
        $role = $row['role_id'];

        // 2. Setup new token
        $token = bin2hex(random_bytes(32));
        $expiry = date('Y-m-d H:i:s', strtotime('+24 hours'));

        mysqli_begin_transaction($conn);
        try {
            // Update the profile table (Users or Customers)
            if($role == 5) {
                Querydb("UPDATE `customers` SET email = '$email' WHERE customer_id = '$profile_id'");
            } else {
                Querydb("UPDATE `users` SET email = '$email' WHERE user_id = '$profile_id'");
            }

            // Update the log with new email and fresh token
            Querydb("UPDATE `user_log` SET 
                        email = '$email', 
                        invite_token = '$token', 
                        token_expiry = '$expiry' 
                     WHERE id = '$log_id'");

            // 3. Resend the Mail (Use your existing $htmlBody logic here)
            // Tip: Get the First Name for the email greeting
            $name_query = ($role == 5) ? "SELECT name as first_name FROM customers WHERE customer_id = '$profile_id'" : "SELECT first_name FROM users WHERE user_id = '$profile_id'";
            $user_info = Querydb("$name_query")->fetch_assoc();
            $firstName = $user_info['first_name'];

            $setup_link = "192.168.0.177/bactriz_crm/activate.php?token=$token";
            $roleNames = [1 => 'Administrator', 2 => 'Manager', 3 => 'Staff', 4 => 'Vendor', 5 => 'Customer'];
            $roleDisplay = $roleNames[$role] ?? 'User';

            // The HTML Template
            $htmlBody = "
            <!DOCTYPE html>
            <html>
            <head>
                <style>
                    .email-container { font-family: 'Segoe UI', Arial, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; border: 1px solid #eee; border-radius: 8px; overflow: hidden; }
                    .header { background-color: #0d6efd; padding: 30px; text-align: center; color: #ffffff; }
                    .content { padding: 30px; background-color: #ffffff; }
                    .button-wrapper { text-align: center; margin: 30px 0; }
                    .btn { background-color: #0d6efd; color: #ffffff !important; padding: 14px 30px; text-decoration: none; border-radius: 50px; font-weight: bold; display: inline-block; font-size: 16px; box-shadow: 0 4px 6px rgba(13, 110, 253, 0.2); }
                    .footer { background-color: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #777; }
                    .badge { background: #e7f1ff; color: #0d6efd; padding: 4px 10px; border-radius: 4px; font-size: 13px; font-weight: 600; }
                </style>
            </head>
            <body>
                <div class='email-container'>
                    <div class='header'>
                        <h1 style='margin:0; font-size: 24px;'>Logistics Pro Portal</h1>
                    </div>
                    <div class='content'>
                        <p>Hello <strong>$firstName</strong>,</p>
                        <p>Welcome to the team! A new account has been prepared for you with the following role access:</p>
                        <p><span class='badge'>$roleDisplay</span></p>
                        <p>To access your dashboard and protect your account, please click the button below to set your unique <strong>Username</strong> and <strong>Password</strong>.</p>
                        
                        <div class='button-wrapper'>
                            <a href='$setup_link' class='btn'>Setup My Credentials</a>
                        </div>
                        
                        <p style='color: #dc3545; font-size: 13px;'><strong>Note:</strong> This invitation link is valid for 24 hours. If it expires, please contact your administrator for a new invite.</p>
                    </div>
                    <div class='footer'>
                        <p>&copy; " . date('Y') . " Your Logistics Company. All rights reserved.</p>
                        <p>This is an automated message, please do not reply directly to this email.</p>
                    </div>
                </div>
            </body>
            </html>";

            if(sendPHPMail($email, $firstName, "Action Required: Registration Link Updated", $htmlBody)) {
                mysqli_commit($conn);
                echo 1; 
            } else {
                throw new Exception("Mail server rejected the request.");
            }
        } catch (Exception $e) {
            mysqli_rollback($conn);
            echo "Failed: " . $e->getMessage();
        }
    } else {
        echo "This account is already active or does not exist.";
    }
}

// Query for trucks expiring soon
$today = date('Y-m-d');
$thirtyDaysFromNow = date('Y-m-d', strtotime('+30 days'));

// 2. Run the query BEFORE the modal check
// Added single quotes around $thirtyDaysFromNow to fix SQL syntax
$expiringTrucks = Querydb("SELECT truck_id, license_plate, registration_end FROM trucks WHERE registration_end BETWEEN '$today' AND '$thirtyDaysFromNow'");
$totalExpiring = mysqli_num_rows($expiringTrucks);

// 3. Handle the Session Trigger
$showModal = false;
if (isset($_SESSION['login_success_trigger'])) {
    $showModal = true;
    unset($_SESSION['login_success_trigger']); 
}

// 4. Fetch Admin Email
$admin_query = Querydb("SELECT email FROM users WHERE role_id = 1 ORDER BY user_id ASC LIMIT 1");
$admin_data = mysqli_fetch_assoc($admin_query);
$to_email = $admin_data['email'];

// 5. The Modal Output (Using a Clean PHP Block instead of echo)
if ($showModal && $totalExpiring > 0) {
    $mail = new PHPMailer(true);

    try {
        $mail->isSMTP();
        $mail->Host       = 'smtp.gmail.com';
        $mail->SMTPAuth   = true;
        $mail->Username   = 'bactrizcode@gmail.com';
        $mail->Password   = 'gvjfsljthxuqvbkz'; 
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
        $mail->Port       = 587;

        $mail->setFrom('system@bactriz.com', 'Bactriz CRM');
        $mail->addAddress($to_email);

            
            // --- Professional Email Content ---
        $mail->isHTML(true);
        $mail->Subject = 'URGENT: Fleet Registration Expiry Report';

        // Define brand colors
        $primary_color = '#0d6efd'; 
        $danger_color = '#dc3545';

        $body = "
        <div style='font-family: Arial, sans-serif; max-width: 600px; margin: auto; border: 1px solid #eee; border-radius: 10px; overflow: hidden;'>
            <div style='background: $primary_color; padding: 20px; text-align: center; color: white;'>
                <h2 style='margin: 0;'>Bactriz Logistics CRM</h2>
                <p style='margin: 5px 0 0 0; opacity: 0.8;'>Fleet Management Alert System</p>
            </div>
            
            <div style='padding: 30px; line-height: 1.6; color: #333;'>
                <h3 style='color: #444;'>Registration Expiry Alert</h3>
                <p>Hello Admin,</p>
                <p>Our system has identified <strong>$totalExpiring</strong> trucks with registrations expiring within the next 30 days. Please review the details below to avoid penalties or operational downtime.</p>
                
                <table style='width: 100%; border-collapse: collapse; margin-top: 20px;'>
                    <thead>
                        <tr style='background: #f8f9fa; border-bottom: 2px solid #eee;'>
                            <th style='padding: 12px; text-align: left; font-size: 13px; color: #666;'>TRUCK LICENSE</th>
                            <th style='padding: 12px; text-align: right; font-size: 13px; color: #666;'>EXPIRY DATE</th>
                        </tr>
                    </thead>
                    <tbody>";

        // Reset pointer and loop through trucks
        mysqli_data_seek($expiringTrucks, 0); 
        while($row = mysqli_fetch_assoc($expiringTrucks)) {
            $expiry_date = date('d M, Y', strtotime($row['registration_end']));
            $body .= "
                        <tr style='border-bottom: 1px solid #eee;'>
                            <td style='padding: 12px; font-weight: bold;'>{$row['license_plate']}</td>
                            <td style='padding: 12px; text-align: right; color: $danger_color; font-weight: bold;'>$expiry_date</td>
                        </tr>";
        }

        $body .= "
                    </tbody>
                </table>

                <div style='margin-top: 30px; text-align: center;'>
                    <a href='https://yourdomain.com/admin_dashboard.php' 
                       style='background: $primary_color; color: white; padding: 12px 25px; text-decoration: none; border-radius: 50px; font-weight: bold; display: inline-block;'>
                       Manage Renewals Now
                    </a>
                </div>
            </div>
            
            <div style='background: #f8f9fa; padding: 15px; text-align: center; font-size: 12px; color: #888;'>
                &copy; " . date('Y') . " Bactriz CRM • Automated Fleet Security System
            </div>
        </div>";

        $mail->Body = $body;
        $mail->AltBody = "You have $totalExpiring trucks expiring soon. Log in to the CRM to manage renewals.";
        $mail->send();
    } catch (Exception $e) {
        error_log("Mailer Error: {$mail->ErrorInfo}");
    }
} // End of $showModal check




if (isset($_POST['action']) && $_POST['action'] == 'vendor_status') {
    $m_id = mysqli_real_escape_string($conn, $_POST['id']);
    
    // Update query
    $sql = "UPDATE maintenance SET vendor_status = 1 WHERE maintenance_id = '$m_id'";
    
    if (Querydb($sql)) {
        echo "success";
    } else {
        echo "error";
    }
    exit;
}

if (isset($_POST['action']) && $_POST['action'] == 'topup') {
    $customer_id = $_POST['customer_id'];
    $amount = $_POST['amount'];
    $method = $_POST['payment_method'];
    $description = $_POST['description'] ?: "Wallet Top-up";
    $business_id = $_SESSION['business_id'];

    if ($amount <= 0) {
        echo 'Invalid amount';
        exit;
    }

    // Start Transaction to ensure data consistency
    Querydb("START TRANSACTION");

    try {
        $res = Querydb("SELECT wallet_balance FROM customers WHERE customer_id = '$customer_id' AND business_id = '$business_id' ");
        $customer = mysqli_fetch_assoc($res);
        
        if (!$customer) {
            echo "Customer not found.";
        } 

            $bal_before = $customer['wallet_balance'];
            $bal_after = $bal_before + $amount;
            $trans_id = generateSerialTransId();

            // 2. Update Customer Balance
            $updateBal = Querydb("UPDATE customers SET wallet_balance = $bal_after WHERE customer_id = '$customer_id' AND business_id = '$business_id'");

            $logTrans = Querydb("INSERT INTO transactions 
                (customer_id, business_id, amount, type, description, bal_before, bal_after, transaction_status, trans_id, created_at) 
                VALUES 
                ('$customer_id', '$business_id', '$amount', 'Credit', '$description', '$bal_before', '$bal_after', 'Successful', '$trans_id', NOW())");

            if ($updateBal && $logTrans) {
                Querydb("COMMIT");
                echo 'success';
            } else {
                echo "Database update failed";
            }

        
    } catch (Exception $e) {
        Querydb("ROLLBACK");
        echo $e->getMessage();
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'assign_truck') {
    // Sanitize inputs
    $truck_id  = $_POST['truck_id'];
    $driver_id = $_POST['driver_id'];
    $atc_id    = $_POST['atc_id'];
    $volume = $_POST['volume'];
    $type = $_POST['product_name'];
    $business_id = $_SESSION['business_id'];
    $user_id = $_SESSION['user_id'];
    $date = date('Y-m-d');
    try {

        $chk_atc = Querydb("SELECT * FROM trips WHERE atc_id = '$atc_id' AND business_id = '$business_id'");
        if (mysqli_num_rows($chk_atc) > 0) {
            $sql = Querydb("UPDATE trips SET truck_id = '$truck_id' AND driver_id = '$driver_id' WHERE atc_id = '$atc_id'");
            if ($sql) {
                echo 'success';
            } else {
                echo "Failed to update the records";
            }
        } 

        $order_id = generateSerialOrderId();
        Querydb("INSERT INTO orders (order_id, product_name, business_id, status, order_date) VALUES ('$order_id', '$type', '$business_id', 'In Progress', '$date')");

        $timestamp = time();
        $sql = "INSERT INTO trips (truck_id, driver_id, atc_id, business_id, user_id, unit, order_id, create_at) 
                VALUES ('$truck_id', '$driver_id', '$atc_id', '$business_id', '$user_id', '$volume', '$order_id', '$timestamp')";
        $insertTrip = Querydb($sql);

        // 2. Optional: Update Truck status to 'On Trip'
        $updateTruck = Querydb("UPDATE trucks SET current_status = 'On Trip' WHERE truck_id = '$truck_id' AND business_id = '$business_id'");

        // 3. Optional: Update ATC record to 'Dispatcheded'
        $updateATC = Querydb("UPDATE atc_records SET atc_status = 'Assigned' WHERE atc_id = '$atc_id' AND business_id = '$business_id'");

        if ($insertTrip) {
            Querydb("COMMIT");
            echo "success";
        } else {
            echo ("One or more database updates failed.");
        }

    } catch (Exception $e) {
        Querydb("ROLLBACK");
        echo "Error: " . $e->getMessage();
    }
    exit;
}

if(isset($_POST['action']) && $_POST['action'] == 'toggleStatus') {
    $trip_id     = $_POST['trip_id'];
    $unit        = floatval($_POST['unit']);
    $selling     = floatval($_POST['selling']);
    $customer_id = $_POST['customer_id'];
    $truck_id    = $_POST['truck_id'];
    $atc_id      = $_POST['atc_id'];
    $date        = date('Y-m-d H:i:s');
    $business_id = $_SESSION['business_id'];

    mysqli_begin_transaction($conn);

    try {
        // 1. Get Wallet Balance and Lock for Transaction
        // FIXED: Using INNER JOIN to ensure we only get the customer linked to this trip
        $current_trip_sql = Querydb("SELECT t.*, c.wallet_balance 
                                     FROM trips t 
                                     INNER JOIN customers c ON t.customer_id = c.customer_id 
                                     WHERE t.trip_id = '$trip_id' LIMIT 1");
        
        $cust = mysqli_fetch_assoc($current_trip_sql);
        if (!$cust) throw new Exception("Trip or Customer not found.");

        $associated_order_id = $cust['order_id'] ?? null;
        $bal_before = floatval($cust['wallet_balance']);
        $selling_cost = $selling * $unit;
        $bal_after = $bal_before - $selling_cost;

        // 2. Update Trip Status
        // FIXED: Added missing closing quote after $business_id
        Querydb("UPDATE trips SET status = 'Completed', actual_end_time = '$date' WHERE trip_id = '$trip_id' AND business_id = '$business_id'");

        // 3. Update Truck Status (Back to 'In Service' or 'Available')
        Querydb("UPDATE trucks SET current_status = 'In Service' WHERE truck_id = '$truck_id'");

        // 4. Update ATC Status (Mark it closed if this was the final trip)
        Querydb("UPDATE atc_records SET atc_status = 'Completed' WHERE atc_id = '$atc_id'");

        // 5. Update Wallet Balance
        Querydb("UPDATE customers SET wallet_balance = '$bal_after' WHERE customer_id = '$customer_id'");

        // 6. Create Financial Transaction Record
        $trans_id = generateSerialTransId();
        $desc = "Payment for Trip ID " . $trip_id . " (" . $unit . " units)";
        $type = "Debit";
        $neg_cost = -$selling_cost; // Ensuring the amount is stored as a negative for debits
        
        Querydb("INSERT INTO transactions (customer_id, business_id, description, amount, type, trans_id, transaction_status, bal_before, bal_after, created_at) 
                 VALUES ('$customer_id', '$business_id', '$desc', '$neg_cost', '$type', '$trans_id', 'Successful', '$bal_before', '$bal_after', '$date')");

        // 7. Update Associated Order
        if ($associated_order_id) {
            // FIXED: Changed $status to 'Completed' as defined in your logic
            $status_color = 'success'; 
            Querydb("UPDATE orders SET status = 'Delivered', varify_code = '$status_color' WHERE order_id = '$associated_order_id' AND business_id = '$business_id'");
        }

        mysqli_commit($conn);
        echo "1"; 

    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo "Update failed: " . $e->getMessage();
    }
    exit;
}

if(isset($_POST['action']) && $_POST['action'] == 'cancelTrip') {
    $trip_id = $_POST['trip_id'];
    $atc_id  = $_POST['atc_id'];
    $reason  = $_POST['reason'];
    $date    = date('Y-m-d H:i:s');
    $business_id = $_SESSION['business_id'];
    $user_id     = $_SESSION['user_id'];

    mysqli_begin_transaction($conn);

    try {
        // 1. Fetch original trip data to "clone" it if necessary
        $res_old = Querydb("SELECT * FROM trips WHERE trip_id = '$trip_id'");
        $old_data = mysqli_fetch_assoc($res_old);
        $type = $old_data['type']; // Fallback if type isn't set

        // 2. Mark current trip as Cancelled
        Querydb("UPDATE trips SET status = 'Cancelled', cancellation_reason = '$reason' WHERE trip_id = '$trip_id'");

        // 3. Check for other active trips on this ATC
        $res_check = Querydb("SELECT COUNT(*) as active_count FROM trips WHERE atc_id = '$atc_id' AND status NOT IN ('Cancelled', 'Canceled') AND trip_id != '$trip_id'");
        $row_check = mysqli_fetch_assoc($res_check);
        $active_count = $row_check['active_count'];

        if ($active_count == 0) {
            // CASE A: Only trip for this ATC - Re-open the ATC
            Querydb("UPDATE atc_records SET atc_status = 'Open' WHERE atc_id = '$atc_id'");
            
            // Cancel the associated order
            Querydb("UPDATE orders SET status = 'Cancelled' WHERE order_id = '{$old_data['order_id']}'");
            Querydb("UPDATE trucks SET current_status = 'In Service' WHERE truck_id = '$truck_id'");
        } else {
            // CASE B: Other trips exist - Create a new placeholder trip for the released volume
            $new_order_id = generateSerialOrderId();
            
            // Create a new Order record
            Querydb("INSERT INTO orders (order_id, product_name, business_id, status, order_date) 
                     VALUES ('$new_order_id', '$type', '$business_id', 'In Progress', '$date')");

            // Insert the fresh Trip record (cloning truck, driver, and units)
            // We use the variables from $old_data to ensure the 'fuel' and 'unit' fields are preserved
            $truck_id = $old_data['truck_id'];
            $driver_id = $old_data['driver_id'];
            $unit = $old_data['unit'];
            
            Querydb("INSERT INTO trips (truck_id, driver_id, atc_id, business_id, user_id, order_id, unit, status, created_at) 
                     VALUES ('$truck_id', '$driver_id', '$atc_id', '$business_id', '$user_id', '$new_order_id', '$unit', 'Pending', '$date')");
        }

        mysqli_commit($conn);
        echo "1";

    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo "Error: " . $e->getMessage();
    }
    exit;
}

if(isset($_GET['id'])) {
    $id = mysqli_real_escape_string($conn, $_GET['id']);
    $result = mysqli_query($conn, "SELECT * FROM businesses WHERE business_id = '$id'");
    $data = mysqli_fetch_assoc($result);
    
    header('Content-Type: application/json');
    echo json_encode($data);
}

if (isset($_POST['action']) && $_POST['action'] == 'resend_activation') {
    $biz_id = mysqli_real_escape_string($conn, $_POST['business_id']);
    $new_email = mysqli_real_escape_string($conn, $_POST['new_email']); // Capture from Swal

    // 1. Fetch current details to ensure business exists
    $query = mysqli_query($conn, "SELECT business_name, business_owner FROM businesses WHERE business_id = '$biz_id'");
    $biz = mysqli_fetch_assoc($query);

    if (!$biz) { die("Business record not found."); }
    $query = mysqli_query($conn, "SELECT business_name, business_status, business_owner FROM businesses WHERE business_id = '$biz_id'");
    $biz = mysqli_fetch_assoc($query);

    // BLOCKER: Prevent resending if already active
    if ($biz['business_status'] == 'active') {
        echo "This business is already active. No need to resend the activation link.";
        exit;
    }

    $owner_name = $biz['business_owner'];
    $biz_name = $biz['business_name'];
    $roleDisplay = 'Business Owner';
    $token = bin2hex(random_bytes(32));
    $expiry = date('Y-m-d H:i:s', strtotime('+24 hours'));
    $setup_link = "http://192.168.0.177/bactriz_crm/activate.php?token=$token";

    mysqli_begin_transaction($conn);

    try {
        // 2. Update all tables with the potentially NEW email
        // Update Business
        mysqli_query($conn, "UPDATE businesses SET business_email = '$new_email',  business_status = 'inactive' WHERE business_id = '$biz_id'");
        
        // Update Users (The login account)
        mysqli_query($conn, "UPDATE users SET email = '$new_email' WHERE business_id = '$biz_id'");
        
        // Update User Log (The invitation record)
        mysqli_query($conn, "UPDATE user_log SET 
                                email = '$new_email', 
                                invite_token = '$token', 
                                token_expiry = '$expiry', 
                                is_active = 0 
                             WHERE business_id = '$biz_id'");

        // 3. Reuse your $htmlBody template
        $htmlBody = "
        <!DOCTYPE html>
        <html>
        <head>
            <style>
                .email-container { font-family: 'Segoe UI', Arial, sans-serif; line-height: 1.6; color: #333; max-width: 600px; margin: 0 auto; border: 1px solid #eee; border-radius: 8px; overflow: hidden; }
                .header { background-color: #334155; padding: 30px; text-align: center; color: #ffffff; }
                .content { padding: 30px; background-color: #ffffff; }
                .button-wrapper { text-align: center; margin: 30px 0; }
                .btn { background-color: #334155; color: #ffffff !important; padding: 14px 30px; text-decoration: none; border-radius: 8px; font-weight: bold; display: inline-block; font-size: 16px; }
                .footer { background-color: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #777; }
                .badge { background: #e7f1ff; color: #334155; padding: 4px 10px; border-radius: 4px; font-size: 13px; font-weight: 600; }
            </style>
        </head>
        <body>
            <div class='email-container'>
                <div class='header'>
                    <h1 style='margin:0; font-size: 24px; color: #fff;'>Bactriz CRM</h1>
                </div>
                <div class='content'>
                    <p>Hello <strong>$owner_name</strong>,</p>
                    <p>Congratulations! Your business, <strong>$biz_name</strong>, has been successfully onboarded to Bactriz CRM.</p>
                    <p>Your access level: <span class='badge'>$roleDisplay</span></p>
                    <p>To start managing your fleet and customers, please click the button below to set up your password and activate your account.</p>
                    
                    <div class='button-wrapper'>
                        <a href='$setup_link' class='btn'>Activate My Account</a>
                    </div>
                    
                    <p style='color: #dc3545; font-size: 13px;'><strong>Note:</strong> This link is valid for 24 hours. For security, do not share this email with anyone.</p>
                </div>
                <div class='footer'>
                    <p>&copy; " . date('Y') . " Bactriz Logistics CRM. All rights reserved.</p>
                </div>
            </div>
        </body>
        </html>";

        if (sendPHPMail($new_email, $owner_name, "Updated Activation Link", $htmlBody)) {
            mysqli_commit($conn);
            echo "success";
        } else {
            throw new Exception("Mail failed to send.");
        }

    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo "Error: " . $e->getMessage();
    }
    exit;
}

if (isset($_POST['action']) && $_POST['action'] == 'ChengeStatus') {
    $id = mysqli_real_escape_string($conn, $_POST['id']);
    $status = mysqli_real_escape_string($conn, $_POST['status']);

    // Start Transaction
    mysqli_begin_transaction($conn);

    try {
        // 1. Update Business Table
        $sql1 = "UPDATE businesses SET business_status = '$status' WHERE business_id = '$id'";
        if (!mysqli_query($conn, $sql1)) throw new Exception("Business update failed");

        // 2. Update Users Table (Lock/Unlock login access)
        $log_active = ($status == 'Active') ? 1 : 0;
        $sql2 = "UPDATE users SET is_active = '$log_active' WHERE business_id = '$id'";
        if (!mysqli_query($conn, $sql2)) throw new Exception("User status update failed");

        // 3. Update User Log (Optional: Sync the is_active flag)
        $sql3 = "UPDATE user_log SET is_active = '$log_active' WHERE business_id = '$id'";
        if (!mysqli_query($conn, $sql3)) throw new Exception("Log update failed");

        mysqli_commit($conn);
        echo "success";

    } catch (Exception $e) {
        mysqli_rollback($conn);
        echo $e->getMessage();
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'update_business') {
    // 1. Capture and Sanitize Inputs
    $biz_id        = mysqli_real_escape_string($conn, $_POST['business_id']);
    $biz_name      = mysqli_real_escape_string($conn, $_POST['biz_name']);
    $biz_email     = mysqli_real_escape_string($conn, $_POST['biz_email']);
    $owner_name    = mysqli_real_escape_string($conn, $_POST['owner_name']);
    $owner_phone   = mysqli_real_escape_string($conn, $_POST['owner_phone']);
    $owner_address = mysqli_real_escape_string($conn, $_POST['owner_address']);

    // 2. Start Transaction
    mysqli_begin_transaction($conn);

    try {
        // Update the Businesses Table
        $sql_biz = "UPDATE businesses SET 
                    business_name = '$biz_name', 
                    business_email = '$biz_email', 
                    business_owner = '$owner_name', 
                    business_phone = '$owner_phone', 
                    business_address = '$owner_address' 
                    WHERE business_id = '$biz_id'";
        
        if (!mysqli_query($conn, $sql_biz)) {
            throw new Exception("Business update failed: " . mysqli_error($conn));
        }

        // Update the Users Table (Syncing the owner profile)
        $sql_user = "UPDATE users SET 
                     first_name = '$owner_name', 
                     email = '$biz_email', 
                     phone_no = '$owner_phone' 
                     WHERE business_id = '$biz_id'";
        
        if (!mysqli_query($conn, $sql_user)) {
            throw new Exception("User profile sync failed: " . mysqli_error($conn));
        }

        // If both queries worked, save changes permanently
        mysqli_commit($conn);
        echo "success";

    } catch (Exception $e) {
        // If anything goes wrong, undo all changes
        mysqli_rollback($conn);
        echo "Error: " . $e->getMessage();
    }
    exit;
}

if (isset($_POST['action']) && $_POST['action'] == 'withdrawal') {
    $customer_id = $_POST['customer_id'];
    $amount = floatval($_POST['amount']);
    $description = $_POST['note'] ?: "Wallet Withdrawal";
    $business_id = $_SESSION['business_id'];

    Querydb("START TRANSACTION");

    try {
        // SELECT ... FOR UPDATE prevents "Race Conditions" where two people withdraw at once
        $res = Querydb("SELECT wallet_balance FROM customers WHERE customer_id = '$customer_id' AND business_id = '$business_id' FOR UPDATE");
        $customer = mysqli_fetch_assoc($res);
        
        if (!$customer) {
            throw new Exception("Customer not found.");
        } 

        $bal_before = $customer['wallet_balance'];

        // Check if customer has enough money
        // if ($bal_before < $amount) {
        //     throw new Exception("Insufficient balance. Current balance is ₦" . number_format($bal_before, 2));
        // }

        $bal_after = $bal_before - $amount;
        $trans_id = generateSerialTransId();

        // 1. Update Customer Balance
        $updateBal = Querydb("UPDATE customers SET wallet_balance = $bal_after WHERE customer_id = '$customer_id' AND business_id = '$business_id'");

        // 2. Log Transaction (Notice 'Debit' type)
        $logTrans = Querydb("INSERT INTO transactions 
            (customer_id, business_id, amount, type, description, bal_before, bal_after, transaction_status, trans_id, created_at) 
            VALUES 
            ('$customer_id', '$business_id', '$amount', 'Debit', '$description', '$bal_before', '$bal_after', 'Successful', '$trans_id', NOW())");

        if ($updateBal && $logTrans) {
            Querydb("COMMIT");
            echo 'success';
        } else {
            throw new Exception("Database update failed");
        }
        
    } catch (Exception $e) {
        Querydb("ROLLBACK");
        echo $e->getMessage();
    }
    exit;
}












 ?>