<?php 
include('function.php');
$user_id = $_SESSION['user_id'];


if (isset($_POST['action']) && $_POST['action'] == 'create_orders') {
    
    // Generate a unique Order ID (e.g., BT-12345)
    $order_id = generateSerialOrderId();
    
    // Sanitize inputs
    $product  = $_POST['item'];
    $quantity = $_POST['unit'];
    $location    = $_POST['location'];
    $date     = date('Y-m-d'); // Today's date
    $status   = "Pending";
    $business_id = $_SESSION['business_id'];
   	

    // 3. Prepare and Execute SQL
    $sql = 	Querydb("INSERT INTO orders (order_id, customer_id, product_name, quantity, location, order_date, business_id, status) 
            VALUES ('$order_id', '$user_id', '$product', '$quantity', '$location', '$date', '$business_id', '$status')");

    if ($sql) {
        echo 1;
    } else {
        echo "Error: " . $sql;
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'authorize_sign_out') {
    session_unset();
    session_destroy();
    echo 'logout_success';
} 

if(isset($_POST['action']) && $_POST['action'] == 'confirmDelivery') {
    $order_id = mysqli_real_escape_string($conn, $_POST['order_id']);
    $user_id = $_SESSION['user_id'];
    $date = date('Y-m-d H:i:s');

    // 1. Fetch Order and User Balance with strict error checking
    $sql = "SELECT o.*, u.wallet_balance, t.atc_id, t.selling_price as trip_selling, t.unit as trip_unit, t.truck_id
            FROM orders o 
            JOIN customers u ON u.customer_id = '$user_id' 
            JOIN trips t ON t.order_id = o.order_id
            WHERE o.order_id = '$order_id' AND o.status = 'In Transit' LIMIT 1";

    $res = Querydb($sql);
    
    if ($res && mysqli_num_rows($res) > 0) {
        $order = mysqli_fetch_assoc($res);
    } else {
        echo "Error: Order not found or not in transit.";
        exit;
    }

    $atc_id = $order['atc_id']; 
    $truck_id = $order['truck_id'];
    $price = (float)$order['trip_selling'] * (float)$order['trip_unit'];

    mysqli_begin_transaction($conn);
    mysqli_begin_transaction($conn);
    try {
        // 1. Get the current balance specifically for the audit log
        $bal_res = Querydb("SELECT wallet_balance FROM customers WHERE customer_id = '$user_id' FOR UPDATE");
        $bal_data = mysqli_fetch_assoc($bal_res);
        $balance_before = (float)$bal_data['wallet_balance'];
        
        $balance_after = $balance_before - $price;

        // A. Deduct from Wallet
        Querydb("UPDATE customers SET wallet_balance = $balance_after WHERE customer_id = '$user_id'");

        // B. Update Order & Trip Status
        Querydb("UPDATE orders SET status = 'Delivered', varify_code = 'success' WHERE order_id = '$order_id'");
        Querydb("UPDATE trips SET status = 'Completed', actual_end_time = '$date' WHERE order_id = '$order_id'");
        
        // C. Release the Truck
        Querydb("UPDATE trucks SET current_status = 'In Service' WHERE truck_id = '$truck_id'");
        Querydb("UPDATE atc_records SET atc_status = 'Completed' WHERE atc_id = '$atc_id'");

        // D. ATC Logic (Keep your existing logic here...)
        // ... (atc_status update code) ...

        // E. Create Transaction Record with Before/After Balances
        $desc = "Payment for Order $order_id (Confirmed by Customer)";
        $trans_id = generateSerialTransId();
        
        // Updated Insert: Added bal_before and bal_after columns
        Querydb("INSERT INTO transactions 
            (customer_id, description, amount, type, trans_id, transaction_status, bal_before, bal_after, created_at) 
            VALUES 
            ('$user_id', '$desc', '-$price', 'Debit', '$trans_id', 'Successful', '$balance_before', '$balance_after', '$date')");

        mysqli_commit($conn);
        echo 1; 

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

if(isset($_POST['action']) && $_POST['action'] == 'processTopup') {
    $user_id = $_SESSION['user_id'];
    $amount = mysqli_real_escape_string($conn, $_POST['amount']);
    $bank = mysqli_real_escape_string($conn, $_POST['bank']);
    $ref = mysqli_real_escape_string($conn, $_POST['reference']);
    $desc = mysqli_real_escape_string($conn, $_POST['description']);
    $business_id = $_SESSION['business_id'];
    $receipt_path = "";

    // Handle File Upload
    if (isset($_FILES['receipt']) && $_FILES['receipt']['error'] == 0) {
        $folder = "uploads/receipts/";
        if (!is_dir($folder)) mkdir($folder, 0777, true);
        
        $file_ext = pathinfo($_FILES['receipt']['name'], PATHINFO_EXTENSION);
        $file_name = "REC_" . time() . "_" . $user_id . "." . $file_ext;
        $receipt_path = $folder . $file_name;
        
        move_uploaded_file($_FILES['receipt']['tmp_name'], $receipt_path);
    }


    // Optional: Also log it in transactions as a "Pending" entry
    $trans_id = generateSerialTransId();
    $sql = Querydb("INSERT INTO transactions (customer_id, business_id, description, amount, type, trans_id, transaction_status, created_at) 
                  VALUES ('$user_id', '$business_id', 'Wallet Top-up Request ($bank) $desc', '$amount', 'Credit', '$trans_id', 'Pending', NOW())");
    if ($sql) {
        echo 1;
    } else {
        echo "Database Error: " . $conn->error;
    }
}

if (isset($_POST['action']) && $_POST['action'] == 'getStatement') {
    $user_id = $_SESSION['user_id'];
    $from = mysqli_real_escape_string($conn, $_POST['from']);
    $to = mysqli_real_escape_string($conn, $_POST['to']);

    // I am changing 'date' to 'created_at' here. 
    // If your column is named 'timestamp' or 'datetime', use that instead.
    $sql = "SELECT * FROM transactions 
            WHERE customer_id = '$user_id' 
            AND DATE(created_at) BETWEEN '$from' AND '$to' AND transaction_status = 'Successful'
            ORDER BY created_at DESC";
    
    $result = Querydb($sql);
    
    if (!$result) {
        // This will tell you if there is a different SQL error
        echo json_encode(["status" => "error", "message" => $conn->error]);
        exit;
    }

    $html = "";
    $inflow = 0;
    $outflow = 0;

    while ($row = $result->fetch_assoc()) {
        $amt = (float)$row['amount'];
        $is_debit = ($row['type'] == 'debit');
        
        $debit_display = $is_debit ? '₦' . number_format(abs($amt), 2) : '-';
        $credit_display = !$is_debit ? '₦' . number_format(abs($amt), 2) : '-';

        if (!$is_debit) $inflow += abs($amt);
        else $outflow += abs($amt);

        // Make sure to change $row['date'] to $row['created_at'] here too!
        $html .= "<tr>
            <td class='px-4 py-3 small'>".date('d M, Y', strtotime($row['created_at']))."</td>
            <td class='px-4 py-3 small'>".htmlspecialchars($row['description'])."</td>
            <td class='px-4 py-3 small text-end text-danger'>$debit_display</td>
            <td class='px-4 py-3 small text-end text-success'>$credit_display</td>
        </tr>";
    }

    echo json_encode([
        "status" => "success",
        "totals" => [
            "inflow" => number_format($inflow, 2),
            "outflow" => number_format($outflow, 2),
            "net" => number_format($inflow - $outflow, 2)
        ],
        "html" => $html
    ]);
    exit;
}

if (isset($_GET['from']) && isset($_GET['to'])) {
    $user_id = $_SESSION['user_id'];
    $from = mysqli_real_escape_string($conn, $_GET['from']);
    $to = mysqli_real_escape_string($conn, $_GET['to']);

    // Set headers for download
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=Statement_'.$from.'_to_'.$to.'.csv');

    // Create a file pointer connected to the output stream
    $output = fopen('php://output', 'w');

    // Output the column headings
    fputcsv($output, array('Date', 'Description', 'Type', 'Debit (₦)', 'Credit (₦)', 'Reference'));

    // Fetch transactions
    $query = "SELECT date, description, type, amount, reference FROM transactions WHERE user_id = '$user_id' AND DATE(date) BETWEEN '$from' AND '$to' ORDER BY date DESC";
    
    $result = Querydb($query);

    while ($row = $result->fetch_assoc()) {
        $date = date('d-M-Y', strtotime($row['date']));
        $desc = $row['description'];
        $type = $row['type'];
        $amt = abs((float)$row['amount']);
        
        // Split amount into Debit or Credit columns
        $debit = ($type == 'Debit') ? $amt : 0;
        $credit = ($type == 'Credit') ? $amt : 0;

        fputcsv($output, array($date, $desc, $type, $debit, $credit, $row['reference']));
    }
    
    fclose($output);
    exit;
}

if (isset($_POST['action']) && $_POST['action'] == 'update_user_profile') {
    // 1. Manually extract and sanitize the text fields
    $user_id    = $_SESSION['user_id'];
    $full_name  = $_POST['full_name'];
    $phone      = $_POST['phone'];
    $email      = $_POST['email'];
    $address    = $_POST['address'];
    $kin_name   = $_POST['kin_name'];
    $kin_rel    = $_POST['kin_relation'];
    $kin_phone  = $_POST['kin_phone'];
    $kin_email  = $_POST['kin_email'];
    $kin_addr   = $_POST['kin_address'];
    $pre_profile = $_POST['pre_profile_img'];

    $check_email = Querydb("SELECT id FROM user_log WHERE email = '$email' AND user_id != '$user_id' ");

    if (mysqli_num_rows($check_email) > 0) {
        // Email already exists in logs for a different user
        echo 'This email is already registered to another account.';
        exit;
    }

    $img_update_sql = $pre_profile;

    // 2. Inspect and Process the Profile Image
    if (isset($_FILES['profile_img']) && $_FILES['profile_img']['error'] == 0) {
        $allowed    = ['jpg', 'jpeg', 'png', 'webp'];
        $filename   = $_FILES['profile_img']['name'];
        $ext        = strtolower(pathinfo($filename, PATHINFO_EXTENSION));

        if (in_array($ext, $allowed)) {
            // Create a unique name to prevent caching issues
            $new_img_name = "profile_" . $user_id . "_" . time() . "." . $ext;
            $upload_path  = "../img/" . $new_img_name;

            if (move_uploaded_file($_FILES['profile_img']['tmp_name'], $upload_path)) {
                $img_update_sql = "profile_img = '$new_img_name'";
                
                // Update session so the header image changes immediately
                $_SESSION['profile_img'] = $new_img_name;
            }
        } else {
            echo "Invalid file type. Please upload an image.";
            exit;
        }
    }

    // 3. Update the Database
    $query = "UPDATE customers SET $img_update_sql, name = '$full_name', phone = '$phone', email = '$email', address = '$address', kin_full_name = '$kin_name', kin_relation = '$kin_rel', kin_phone_no = '$kin_phone', kin_email = '$kin_email', kin_address = '$kin_addr' WHERE customer_id = '$user_id'";

    if (Querydb($query)) {
        // Update session name if it changed
        $_SESSION['full_name'] = $full_name;
        echo "1";
    } else {
        echo "Database update failed.";
    }
    exit;
}

if (isset($_POST['action']) && $_POST['action'] == 'change_user_password') {
    $user_id = $_SESSION['user_id'];
    $log_id = $_SESSION['log_id'];
    $curr_pass = $_POST['curr_pass'];
    $new_pass = $_POST['new_pass'];

    // 1. Fetch current hashed password from DB
    $user = mysqli_fetch_assoc(Querydb("SELECT password FROM user_log WHERE id = '$log_id' AND user_id = '$user_id'"));

    // 2. Verify current password
    if (password_verify($curr_pass, $user['password'])) {
        // 3. Hash the new password and update
        $hashed_new_pass = password_hash($new_pass, PASSWORD_DEFAULT);
        
        $update = Querydb("UPDATE user_log SET password = '$hashed_new_pass' WHERE id = '$log_id' AND user_id = '$user_id'");
        
        if ($update) {
            echo "1";
        } else {
            echo "Failed to update password in database.";
        }
    } else {
        echo "The current password you entered is incorrect.";
    }
    exit;
}

if (isset($_POST['action']) && $_POST['action'] == 'get_tracking_info') {
    $order_id = $_POST['order_id'];
    
    // Join orders with the trip/logistics table to get driver and truck details
    $query = "SELECT o.*, t.driver_id, t.truck_id, o.status, t.unit, d.phone_number, CONCAT_WS(' ', d.first_name, d.last_name) AS driver_name, tr.license_plate 
              FROM orders o
              LEFT JOIN trips t ON o.order_id = t.order_id
              LEFT JOIN drivers d ON d.driver_id = t.driver_id
              LEFT JOIN trucks tr ON t.truck_id = tr.truck_id
              WHERE o.order_id = '$order_id'";
    
    $res = Querydb($query);
    $data = mysqli_fetch_assoc($res);

    if ($data) {
        ?>
        <div class="text-center mb-4">
            <div class="bg-light p-3 rounded-circle d-inline-block mb-2">
                <i class="fas fa-user-tie fa-2x text-primary"></i>
            </div>
            <h6 class="fw-bold mb-0"><?php echo $data['driver_name'] ?? 'Not Assigned Yet'; ?></h6>
            <small class="text-muted">Assigned Driver</small>
        </div>
        
        <ul class="list-group list-group-flush small">
            <li class="list-group-item d-flex justify-content-between align-items-center px-0">
                <span class="text-muted">Truck Number</span>
                <span class="fw-bold"><?php echo $data['license_plate'] ?? '---'; ?></span>
            </li>
            <li class="list-group-item d-flex justify-content-between align-items-center px-0">
                <span class="text-muted">Contact</span>
                <span class="fw-bold text-primary">
                    <a href="tel:<?php echo $data['phone_number']; ?>" class="text-decoration-none">
                        <i class="fas fa-phone-alt me-1"></i> <?php echo $data['phone_number'] ?? '---'; ?>
                    </a>
                </span>
            </li>
            <li class="list-group-item d-flex justify-content-between align-items-center px-0">
                <span class="text-muted small">Product</span>
                <span class="fw-bold small text-uppercase"><?php echo $data['product_name'] ?? 'General Cargo'; ?></span>
                </span>
            </li>
            <li class="list-group-item d-flex justify-content-between align-items-center px-0">
                <span class="text-muted">Quantity Order / Delivered</span>
                <span class="fw-bold text-primary"><?php echo $data['quantity'] ?? '---', " / ", $data['unit'] ?? '---'; ?></span>
            </li>
            <li class="list-group-item d-flex justify-content-between align-items-center px-0">
                <span class="text-muted">Current Status</span>
                <span class="badge bg-info rounded-pill"><?php echo $data['status']; ?></span>
            </li>
        </ul>
        <?php
    } else {
        ?>
        <div class="text-center py-4">
            <i class="fas fa-shipping-fast fa-3x text-light mb-3"></i>
            <h6 class="fw-bold">Assigning Your Driver...</h6>
            <p class="text-muted small">We are currently pairing your order with the best available vehicle.</p>
        </div>
        <?php
    }
    exit;
}


 ?>