<?php
session_start();
include 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    header('Location: local_shipment.php');
    exit();
}

// Debug: Log the POST data
error_log("POST data: " . print_r($_POST, true));

// Get user ID for tracking
$user_id = $_SESSION['user_id'];

// Collect main shipment data
$file_number = $_POST['file_number'] ?? '';
$customer = isset($_POST['customer']) && !empty($_POST['customer']) ? intval($_POST['customer']) : null;
$consignee_name = $_POST['consignee_name'] ?? '';
$trans_type = $_POST['trans_type'] ?? null;
$transporter = isset($_POST['transporter']) && !empty($_POST['transporter']) ? $_POST['transporter'] : null;
$destination = $_POST['destination'] ?? '';
$currency = $_POST['currency'] ?? '$';
$obl_no = $_POST['obl_no'] ?? null;
$waybill = $_POST['way_bill'] ?? null;
$eta = !empty($_POST['eta']) ? date('Y-m-d', strtotime($_POST['eta'])) : null;
$pre_alert_received_date = !empty($_POST['pre_alert_received_date']) && is_string($_POST['pre_alert_received_date']) 
    ? date('Y-m-d', strtotime($_POST['pre_alert_received_date'])) 
    : null;

$discharge_date = !empty($_POST['discharge_date']) && is_string($_POST['discharge_date']) 
    ? date('Y-m-d', strtotime($_POST['discharge_date'])) 
    : null;

$load_date = !empty($_POST['load_date']) && is_string($_POST['load_date']) 
    ? date('Y-m-d', strtotime($_POST['load_date'])) 
    : null;

$offload_date = !empty($_POST['offload_date']) && is_string($_POST['offload_date']) 
    ? date('Y-m-d', strtotime($_POST['offload_date'])) 
    : null;

$port_date = !empty($_POST['port_charges_paid_date']) && is_string($_POST['port_charges_paid_date']) 
    ? date('Y-m-d', strtotime($_POST['port_charges_paid_date'])) 
    : null;

$wharfage_date = !empty($_POST['whafage_paid_date']) && is_string($_POST['whafage_paid_date']) 
    ? date('Y-m-d', strtotime($_POST['whafage_paid_date'])) 
    : null;

$delivery_date = !empty($_POST['delivery_order_apply_date']) && is_string($_POST['delivery_order_apply_date']) 
    ? date('Y-m-d', strtotime($_POST['delivery_order_apply_date'])) 
    : null;

$deli_order_date = !empty($_POST['delivery_order_received_date']) && is_string($_POST['delivery_order_received_date']) 
    ? date('Y-m-d', strtotime($_POST['delivery_order_received_date'])) 
    : null;

$c_type = $_POST['c_type'] ?? 'Container';
$charge = $_POST['charge'] ?? null;
$remarks = $_POST['remarks'] ?? null;

//  Additional fields based on cargo type
$bulk_item_count = isset($_POST['bulk_item_count']) ? intval($_POST['bulk_item_count']) : null;
$bulk_cbm = isset($_POST['bulk_cbm']) ? floatval($_POST['bulk_cbm']) : null;
$loose_item_count = isset($_POST['loose_item_count']) ? intval($_POST['loose_item_count']) : null;

// Load dates into main record (for Container type)
$load_date = null;
$offload_date = null;
$port_date = null;
$wharfage_date = null;
$delivery_date = null;
$deli_order_date = null;

if ($c_type === 'Container' && isset($_POST['load_date']) && is_array($_POST['load_date']) && !empty($_POST['load_date'][0])) {
    $load_date = date('Y-m-d', strtotime($_POST['load_date'][0]));
}
if ($c_type === 'Container' && isset($_POST['offload_date']) && is_array($_POST['offload_date']) && !empty($_POST['offload_date'][0])) {
    $offload_date = date('Y-m-d', strtotime($_POST['offload_date'][0]));
}
if ($c_type === 'Container' && isset($_POST['port_charges_paid_date']) && is_array($_POST['port_charges_paid_date']) && !empty($_POST['port_charges_paid_date'][0])) {
    $port_date = date('Y-m-d', strtotime($_POST['port_charges_paid_date'][0]));
}
if ($c_type === 'Container' && isset($_POST['whafage_paid_date']) && is_array($_POST['whafage_paid_date']) && !empty($_POST['whafage_paid_date'][0])) {
    $wharfage_date = date('Y-m-d', strtotime($_POST['whafage_paid_date'][0]));
}
if ($c_type === 'Container' && isset($_POST['delivery_order_apply_date']) && is_array($_POST['delivery_order_apply_date']) && !empty($_POST['delivery_order_apply_date'][0])) {
    $delivery_date = date('Y-m-d', strtotime($_POST['delivery_order_apply_date'][0]));
}
if ($c_type === 'Container' && isset($_POST['delivery_order_received_date']) && is_array($_POST['delivery_order_received_date']) && !empty($_POST['delivery_order_received_date'][0])) {
    $deli_order_date = date('Y-m-d', strtotime($_POST['delivery_order_received_date'][0]));
}

// This is a local shipment (local/transit = 1)
$local_transit = 1;

// Add after collecting main shipment data
if ($c_type === 'Bulk Cargo' || $c_type === 'Loose Cargo') {
    $total_trucks_required = isset($_POST['total_trucks_required']) ? intval($_POST['total_trucks_required']) : null;
    $trucks_to_assign = isset($_POST['trucks_to_assign']) ? intval($_POST['trucks_to_assign']) : null;
    $trucks_remaining = $total_trucks_required - $trucks_to_assign;

    // Insert initial truck records
    if ($c_type === 'Bulk Cargo') {
        $bulk_sql = "INSERT INTO bulk_cargo_details (
            file_number, 
            truck_reg,
            transporter,
            driver_name,
            tonnage,
            assignment_batch,
            created_by
        ) VALUES (?, 'Pending', NULL, NULL, 0, 1, ?)";
        
        $bulk_stmt = $conn->prepare($bulk_sql);
        
        for ($i = 0; $i < $trucks_to_assign; $i++) {
            $bulk_stmt->bind_param('si', $file_number, $user_id);
            if (!$bulk_stmt->execute()) {
                throw new Exception("Failed to create bulk cargo truck records");
            }
        }
    } else {
        $loose_sql = "INSERT INTO loose_cargo_details (
            file_number,
            truck_reg,
            transporter,
            driver_name,
            cbm,
            assignment_batch,
            created_by
        ) VALUES (?, 'Pending', NULL, NULL, 0, 1, ?)";
        
        $loose_stmt = $conn->prepare($loose_sql);
        
        for ($i = 0; $i < $trucks_to_assign; $i++) {
            $loose_stmt->bind_param('si', $file_number, $user_id);
            if (!$loose_stmt->execute()) {
                throw new Exception("Failed to create loose cargo truck records");
            }
        }
    }
}

try {
    // Check database connection
    if (!$conn || $conn->connect_error) {
        throw new Exception("Database connection failed: " . ($conn ? $conn->connect_error : 'Connection object is null'));
    }

    error_log("Database connection successful");
    
    // Start transaction
    if (!$conn->begin_transaction()) {
        throw new Exception("Failed to start transaction: " . $conn->error);
    }
    
    error_log("Transaction started successfully");
    
    // Prepare SQL based on cargo type
    if ($c_type === 'Bulk Cargo') {
        $sql = "INSERT INTO files (
                    file_number, customer, trans_type, transporter, currency, charge,
                    consignee_name, obl_no, way_bill, eta, port_charges_paid_date, whafage_paid_date, delivery_order_apply_date, delivery_order_received_date, destination, remarks, `local/transit`,
                    c_type, bulk_item_count, bulk_cbm, total_trucks_required, trucks_assigned, trucks_remaining, created_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare statement failed: " . $conn->error);
        }
        
        $stmt->bind_param(
            'sisssssssssssssiisidiii',
            $file_number, $customer, $trans_type, $transporter, $currency, $charge,
            $consignee_name, $obl_no, $waybill, $eta, $port_date, $wharfage_date, $delivery_date, $deli_order_date, $destination, $remarks, $local_transit,
            $c_type, $bulk_item_count, $bulk_cbm,
            $total_trucks_required, $trucks_to_assign, $trucks_remaining
        );
        
    } else if ($c_type === 'Loose Cargo') {
        $sql = "INSERT INTO files (
                    file_number, customer, trans_type, transporter, currency, charge,
                    consignee_name, obl_no, way_bill, eta, port_charges_paid_date, whafage_paid_date, delivery_order_apply_date, delivery_order_received_date, destination, remarks, `local/transit`,
                    c_type, loose_item_count, created_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare statement failed: " . $conn->error);
        }
        
        $stmt->bind_param(
            'sisssssssssssssisis',
            $file_number, $customer, $trans_type, $transporter, $currency, $charge,
            $consignee_name, $obl_no, $waybill, $eta, $port_date, $wharfage_date, $delivery_date, $deli_order_date, $destination, $remarks, $local_transit,
            $c_type, $loose_item_count
        );
        
    } else {
        // Default: Container type
        /*$sql = "INSERT INTO files (
                    file_number, customer, trans_type, transporter, currency, charge,
                    consignee_name, obl_no, way_bill, eta, port_charges_paid_date, whafage_paid_date, delivery_order_apply_date, delivery_order_received_date, destination, remarks, `local/transit`,
                    c_type, load_date, offload_date, created_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare statement failed: " . $conn->error);
        }
        
        $stmt->bind_param(
            'sissssssssssssssisss',
            $file_number, $customer, $trans_type, $transporter, $currency, $charge,
            $consignee_name, $obl_no, $waybill, $eta, $port_date, $wharfage_date, $delivery_date, $deli_order_date, $destination, $remarks, $local_transit,
            $c_type, $load_date, $offload_date
        ); */
        
        // For Container type
        $sql = "INSERT INTO files (
                    file_number, customer, trans_type, transporter, currency, charge,
                    consignee_name, obl_no, way_bill, eta, pre_alert_received_date, discharge_date,
                    port_charges_paid_date, whafage_paid_date, delivery_order_apply_date, 
                    delivery_order_received_date, destination, remarks, `local/transit`,
                    c_type, load_date, offload_date, created_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare statement failed: " . $conn->error);
        }
        
        $stmt->bind_param(
            'sisssssssssssssssissss',
            $file_number, $customer, $trans_type, $transporter, $currency, $charge,
            $consignee_name, $obl_no, $waybill, $eta, $pre_alert_received_date, $discharge_date,
            $port_date, $wharfage_date, $delivery_date, $deli_order_date, $destination, $remarks, 
            $local_transit, $c_type, $load_date, $offload_date
        );
        
    }
    
    error_log("Bound parameters to main statement");
    
    if (!$stmt->execute()) {
        throw new Exception("Error creating shipment: " . $stmt->error);
    }
    
    error_log("Main record inserted successfully");
    
    // Get the inserted file ID
    $file_id = $conn->insert_id;
    error_log("New file ID: " . $file_id);
    
    // Handle cargo type specific details
    if ($c_type === 'Container' && isset($_POST['container_code']) && is_array($_POST['container_code'])) {
        // Save container details
        $container_codes = $_POST['container_code'];
        $container_sizes = $_POST['container_size'] ?? [];
        $vnos = $_POST['vno'] ?? [];
        $way_bills = $_POST['way_bill'] ?? [];
        $driver_names = $_POST['driver_name'] ?? [];
        $driver_phones = $_POST['driver_phone_number'] ?? [];
        $driver_licenses = $_POST['driver_license'] ?? [];
        $container_remarks = $_POST['container_remarks'] ?? [];
       // $load_dates = $_POST['load_date'] ?? [];
        //$offload_dates = $_POST['offload_date'] ?? [];
        
        // Count of containers
        $container_count = count($container_codes);
        error_log("Processing $container_count containers");
        
        // Prepare statement for container details
        $container_sql = "INSERT INTO container_details (
                            file_number, container_code, container_size, way_bill, 
                            driver_name, driver_phone_number, driver_license,
                             remarks
                          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        $container_stmt = $conn->prepare($container_sql);
        if (!$container_stmt) {
            throw new Exception("Prepare container statement failed: " . $conn->error);
        }
        
        // Insert each container
        for ($i = 0; $i < $container_count; $i++) {
            // Skip if container code is empty
            if (empty($container_codes[$i])) {
                error_log("Skipping empty container at index $i");
                continue;
            }
            
            // Format dates or set to null if empty
            $load_date = !empty($load_dates[$i]) ? date('Y-m-d', strtotime($load_dates[$i])) : null;
            $offload_date = !empty($offload_dates[$i]) ? date('Y-m-d', strtotime($offload_dates[$i])) : null;
            
            $way_bill = $way_bills[$i] ?? '';
            $container_size = $container_sizes[$i] ?? '';
            $driver_name = $driver_names[$i] ?? '';
            $driver_phone = $driver_phones[$i] ?? '';
            $driver_license = $driver_licenses[$i] ?? '';
            $container_remark = $container_remarks[$i] ?? '';
            
            $container_stmt->bind_param(
                'ssssssss',
                $file_number,
                $container_codes[$i],
                $container_size,
                $way_bill,
                $driver_name,
                $driver_phone,
                $driver_license,
                //$load_date,
                //$offload_date,
                $container_remark
            );
            
            if (!$container_stmt->execute()) {
                throw new Exception("Error saving container details: " . $container_stmt->error);
            }
            
            error_log("Container $i saved successfully");
        }
        
        $container_stmt->close();
    } 
    else if ($c_type === 'Loose Cargo' && isset($_POST['chassis_number']) && is_array($_POST['chassis_number'])) {
        // Save loose cargo items
        $chassis_numbers = $_POST['chassis_number'];
        $car_models = $_POST['car_model'] ?? [];
        $item_cbms = $_POST['item_cbm'] ?? [];
        $item_transporters = $_POST['item_transporter'] ?? [];
        $item_drivers = $_POST['item_driver'] ?? [];
        $item_driver_phones = $_POST['item_driver_phone'] ?? [];
        $item_driver_licenses = $_POST['item_driver_license'] ?? [];
        $item_remarks = $_POST['item_remarks'] ?? [];
        
        // Count of items
        $item_count = count($chassis_numbers);
        error_log("Processing $item_count loose cargo items");
        
        // Prepare statement for loose cargo items
        $item_sql = "INSERT INTO loose_cargo_items (
                        file_number, chassis_number, car_model, cbm, 
                        transporter, driver_name, driver_phone, driver_license, remarks
                     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $item_stmt = $conn->prepare($item_sql);
        if (!$item_stmt) {
            throw new Exception("Prepare loose cargo item statement failed: " . $conn->error);
        }
        
        // Insert each loose cargo item
        for ($i = 0; $i < $item_count; $i++) {
            // Skip if chassis number is empty
            if (empty($chassis_numbers[$i])) {
                error_log("Skipping empty loose cargo item at index $i");
                continue;
            }
            
            $cbm = !empty($item_cbms[$i]) ? floatval($item_cbms[$i]) : null;
            $car_model = $car_models[$i] ?? '';
            $transporter = $item_transporters[$i] ?? '';
            $driver = $item_drivers[$i] ?? '';
            $driver_phone = $item_driver_phones[$i] ?? '';
            $driver_license = $item_driver_licenses[$i] ?? '';
            $remark = $item_remarks[$i] ?? '';
            
            $item_stmt->bind_param(
                'sssdsssss',
                $file_number,
                $chassis_numbers[$i],
                $car_model,
                $cbm,
                $transporter,
                $driver,
                $driver_phone,
                $driver_license,
                $remark
            );
            
            if (!$item_stmt->execute()) {
                throw new Exception("Error saving loose cargo item details: " . $item_stmt->error);
            }
            
            error_log("Loose cargo item $i saved successfully");
        }
        
        $item_stmt->close();
    }
    
    // Create notification
    try {
        $notif_message = "New local file with file number {$file_number} has been created.";
        $sql_notif = "INSERT INTO notifications (file_id, file_number, notif_message, notif_message_id, created_at) 
                      VALUES (?, ?, ?, 1, NOW())";
        
        $notif_stmt = $conn->prepare($sql_notif);
        if (!$notif_stmt) {
            error_log("Warning: Failed to prepare notification statement: " . $conn->error);
        } else {
            $notif_stmt->bind_param('iss', $file_id, $file_number, $notif_message);
            
            if (!$notif_stmt->execute()) {
                error_log("Warning: Failed to create notification for file: $file_number - " . $notif_stmt->error);
            } else {
                error_log("Notification created successfully");
            }
            
            $notif_stmt->close();
        }
    } catch (Exception $notif_e) {
        // Just log notification errors, don't fail the transaction
        error_log("Notification error: " . $notif_e->getMessage());
    }
    
    // Create charges record (empty placeholder)
    try {
        $charge_sql = "INSERT INTO charges (file_number, created_date) VALUES (?, NOW())";
        $charge_stmt = $conn->prepare($charge_sql);
        if ($charge_stmt) {
            $charge_stmt->bind_param('s', $file_number);
            if (!$charge_stmt->execute()) {
                error_log("Warning: Failed to create charges record: " . $charge_stmt->error);
            } else {
                error_log("Charges record created successfully");
            }
            $charge_stmt->close();
        }
    } catch (Exception $charge_e) {
        // Just log charges errors, don't fail the transaction
        error_log("Charges error: " . $charge_e->getMessage());
        }
    
    // Update local_file_counter in settings table
    try {
        // First check if the setting exists
        $check_sql = "SELECT setting_value FROM settings WHERE setting_key = 'local_file_counter'";
        $check_result = $conn->query($check_sql);
        
        if ($check_result && $check_result->num_rows > 0) {
            // Update existing counter
            $update_sql = "UPDATE settings SET setting_value = setting_value + 1 WHERE setting_key = 'local_file_counter'";
            if (!$conn->query($update_sql)) {
                error_log("Warning: Failed to update local_file_counter: " . $conn->error);
            } else {
                error_log("Updated local_file_counter successfully");
            }
        } else {
            // Insert new counter setting if it doesn't exist
            $insert_sql = "INSERT INTO settings (setting_key, setting_value) VALUES ('local_file_counter', '1')";
            if (!$conn->query($insert_sql)) {
                error_log("Warning: Failed to create local_file_counter: " . $conn->error);
            } else {
                error_log("Created local_file_counter successfully");
            }
        }
    } catch (Exception $counter_e) {
        // Just log counter errors, don't fail the transaction
        error_log("Counter update error: " . $counter_e->getMessage());
    }
    
    // Commit transaction
    if (!$conn->commit()) {
        throw new Exception("Failed to commit transaction: " . $conn->error);
    }
    
    error_log("Transaction committed successfully");
    
    // Success - redirect with success message
    //$_SESSION['success_message'] = "Local shipment file created successfully. File Number: $file_number";
    //error_log("Redirecting to file_list.php with success");
    //header('Location: file_list.php');
    //exit();
    
    // Commit transaction
    $conn->commit();
    
    // Store the file number in session for use on the next page
    $_SESSION['created_file_number'] = $file_number;
    $_SESSION['created_file_bl'] = $obl_no; // Pass the BL number to the next screen
    
    // Set the success message
    $_SESSION['success_message'] = "Shipment file created successfully. File Number: $file_number";
    
    // Redirect to the container deposit confirmation page
    header('Location: confirm_container_deposit.php');
    exit();
    
} catch (Exception $e) {
    // Rollback transaction on error
    error_log("Error encountered: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    try {
        if ($conn && $conn->ping()) {
            $conn->rollback();
            error_log("Transaction rolled back successfully");
        }
    } catch (Exception $rollback_e) {
        error_log("Rollback failed: " . $rollback_e->getMessage());
    }
    
    // Show error message
    $_SESSION['error_message'] = "Error: " . $e->getMessage();
    error_log("Redirecting to local_shipment.php with error");
    header('Location: local_shipment.php');
    exit();
}
?>