<?php
// Database configuration
define('DB_HOST', 'localhost');
define('DB_USER', 'wiselie_nanda');
define('DB_PASS', 'Clemira123');
define('DB_NAME', 'wiselie_inventory');

// Warehouse mapping
$warehouse_map = [
    1 => 'sidoarjo',
    2 => 'jakarta',
    3 => 'makassar',
    4 => 'Bali',
    5 => 'Semarang',
    6 => 'Bandung'
];

// Error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Initialize variables
$update_summary = [
    'updated' => 0,
    'already_updated' => 0,
    'needs_update' => 0,
    'update_errors' => [],
    'update_type' => ''
];
$connection_error = null;
$init_errors = [];

// Handle export request
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['export_errors'])) {
    if (!empty($_POST['error_messages'])) {
        $errors = json_decode($_POST['error_messages'], true);
        
        // Set headers for download
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="awb_update_errors_' . date('Y-m-d') . '.csv"');
        
        $output = fopen('php://output', 'w');
        fputcsv($output, ['Error Messages']);
        
        foreach ($errors as $error) {
            fputcsv($output, [$error]);
        }
        
        fclose($output);
        exit();
    }
}

try {
    // Create connection
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    // Check connection
    if ($conn->connect_error) {
        throw new Exception("Connection failed: " . $conn->connect_error);
    }

    // Set charset to utf8
    if (!$conn->set_charset("utf8mb4")) {
        $init_errors[] = "Error setting charset: " . $conn->error;
    }

    // Check if update by resi button was clicked
    if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['update_awb_resi'])) {
        $update_summary['update_type'] = 'resi';
        
        // Function to update AWB records from Resi data (matching resi.resi with awb.resi)
        function updateAwbFromResi($conn, $warehouse_map) {
            $summary = [
                'updated' => 0,
                'already_updated' => 0,
                'needs_update' => 0,
                'update_errors' => []
            ];
            
            try {
                // Get all resi records that have data to potentially update
                $resi_query = "SELECT resi, scan_in, scan_out, id_packer, id_warehouse FROM resi";
                $resi_result = $conn->query($resi_query);
                
                if ($resi_result === false) {
                    throw new Exception("Error fetching resi records: " . $conn->error);
                }
                
                if ($resi_result->num_rows > 0) {
                    while ($resi_row = $resi_result->fetch_assoc()) {
                        $resi = $conn->real_escape_string($resi_row['resi']);
                        $scan_in = $resi_row['scan_in'];
                        $scan_out = $resi_row['scan_out'];
                        $id_packer = $resi_row['id_packer'];
                        $id_warehouse = $resi_row['id_warehouse'];
                        
                        // Check if this resi exists in awb table (match with resi column)
                        $awb_check = "SELECT scan_in, scan_status, packer_no, loc FROM awb WHERE resi = '$resi' LIMIT 1";
                        $awb_result = $conn->query($awb_check);
                        
                        if ($awb_result === false) {
                            $summary['update_errors'][] = "Error checking AWB record for resi $resi: " . $conn->error;
                            $summary['needs_update']++;
                            continue;
                        }
                        
                        if ($awb_result->num_rows > 0) {
                            $awb_row = $awb_result->fetch_assoc();
                            
                            // Prepare update parts
                            $update_parts = [];
                            $needs_update = false;
                            
                            // Check each field that needs updating
                            if (is_null($awb_row['scan_in']) && !is_null($scan_in)) {
                                $update_parts[] = "scan_in = '" . $conn->real_escape_string($scan_in) . "'";
                                $needs_update = true;
                            }
                            
                            if (is_null($awb_row['scan_status']) && !is_null($scan_out)) {
                                $update_parts[] = "scan_status = '" . $conn->real_escape_string($scan_out) . "'";
                                $needs_update = true;
                            }
                            
                            if (($awb_row['packer_no'] == 0 || is_null($awb_row['packer_no'])) && !is_null($id_packer)) {
                                $update_parts[] = "packer_no = " . intval($id_packer);
                                $needs_update = true;
                            }
                            
                            // Check if location needs updating
                            if (!is_null($id_warehouse) && isset($warehouse_map[$id_warehouse])) {
                                $expected_loc = $warehouse_map[$id_warehouse];
                                if (empty($awb_row['loc']) || $awb_row['loc'] != $expected_loc) {
                                    $update_parts[] = "loc = '" . $conn->real_escape_string($expected_loc) . "'";
                                    $needs_update = true;
                                }
                            }
                            
                            if ($needs_update && !empty($update_parts)) {
                                // Perform the update
                                $update_query = "UPDATE awb SET " . implode(', ', $update_parts) . " WHERE resi = '$resi'";
                                if ($conn->query($update_query)) {
                                    $summary['updated']++;
                                } else {
                                    $summary['update_errors'][] = "Update failed for resi $resi: " . $conn->error;
                                    $summary['needs_update']++;
                                }
                            } else {
                                $summary['already_updated']++;
                            }
                        } else {
                            $summary['update_errors'][] = "No matching AWB record found for resi $resi";
                            $summary['needs_update']++;
                        }
                    }
                }
            } catch (Exception $e) {
                $summary['update_errors'][] = $e->getMessage();
                $summary['needs_update']++;
            }
            
            return $summary;
        }

        // Execute the update function
        $update_summary = array_merge($update_summary, updateAwbFromResi($conn, $warehouse_map));
    }

    // Check if update by invoice button was clicked
    if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['update_awb_invoice'])) {
        $update_summary['update_type'] = 'invoice';
        
        // Function to update AWB records from Resi data but matching resi.resi with awb.invoice
        function updateAwbFromInvoice($conn, $warehouse_map) {
            $summary = [
                'updated' => 0,
                'already_updated' => 0,
                'needs_update' => 0,
                'update_errors' => []
            ];
            
            try {
                // Get all resi records that have data to potentially update
                $resi_query = "SELECT resi, scan_in, scan_out, id_packer, id_warehouse FROM resi";
                $resi_result = $conn->query($resi_query);
                
                if ($resi_result === false) {
                    throw new Exception("Error fetching resi records: " . $conn->error);
                }
                
                if ($resi_result->num_rows > 0) {
                    while ($resi_row = $resi_result->fetch_assoc()) {
                        $resi = $conn->real_escape_string($resi_row['resi']);
                        $scan_in = $resi_row['scan_in'];
                        $scan_out = $resi_row['scan_out'];
                        $id_packer = $resi_row['id_packer'];
                        $id_warehouse = $resi_row['id_warehouse'];
                        
                        // Check if this resi exists in awb table (match with invoice column)
                        $awb_check = "SELECT scan_in, scan_status, packer_no, loc FROM awb WHERE invoice = '$resi' LIMIT 1";
                        $awb_result = $conn->query($awb_check);
                        
                        if ($awb_result === false) {
                            $summary['update_errors'][] = "Error checking AWB record for invoice $resi: " . $conn->error;
                            $summary['needs_update']++;
                            continue;
                        }
                        
                        if ($awb_result->num_rows > 0) {
                            $awb_row = $awb_result->fetch_assoc();
                            
                            // Prepare update parts
                            $update_parts = [];
                            $needs_update = false;
                            
                            // Check each field that needs updating
                            if (is_null($awb_row['scan_in']) && !is_null($scan_in)) {
                                $update_parts[] = "scan_in = '" . $conn->real_escape_string($scan_in) . "'";
                                $needs_update = true;
                            }
                            
                            if (is_null($awb_row['scan_status']) && !is_null($scan_out)) {
                                $update_parts[] = "scan_status = '" . $conn->real_escape_string($scan_out) . "'";
                                $needs_update = true;
                            }
                            
                            if (($awb_row['packer_no'] == 0 || is_null($awb_row['packer_no'])) && !is_null($id_packer)) {
                                $update_parts[] = "packer_no = " . intval($id_packer);
                                $needs_update = true;
                            }
                            
                            // Check if location needs updating
                            if (!is_null($id_warehouse) && isset($warehouse_map[$id_warehouse])) {
                                $expected_loc = $warehouse_map[$id_warehouse];
                                if (empty($awb_row['loc']) || $awb_row['loc'] != $expected_loc) {
                                    $update_parts[] = "loc = '" . $conn->real_escape_string($expected_loc) . "'";
                                    $needs_update = true;
                                }
                            }
                            
                            if ($needs_update && !empty($update_parts)) {
                                // Perform the update
                                $update_query = "UPDATE awb SET " . implode(', ', $update_parts) . " WHERE invoice = '$resi'";
                                if ($conn->query($update_query)) {
                                    $summary['updated']++;
                                } else {
                                    $summary['update_errors'][] = "Update failed for invoice $resi: " . $conn->error;
                                    $summary['needs_update']++;
                                }
                            } else {
                                $summary['already_updated']++;
                            }
                        } else {
                            $summary['update_errors'][] = "No matching AWB record found for invoice $resi";
                            $summary['needs_update']++;
                        }
                    }
                }
            } catch (Exception $e) {
                $summary['update_errors'][] = $e->getMessage();
                $summary['needs_update']++;
            }
            
            return $summary;
        }

        // Execute the update function
        $update_summary = array_merge($update_summary, updateAwbFromInvoice($conn, $warehouse_map));
    }

} catch (Exception $e) {
    $connection_error = $e->getMessage();
} finally {
    // Close connection if it was opened
    if (isset($conn) && $conn instanceof mysqli) {
        $conn->close();
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>AWB Update Report</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .summary { background: #f0f0f0; padding: 15px; border-radius: 5px; margin-bottom: 20px; }
        .success { color: green; }
        .warning { color: orange; }
        .error { color: red; }
        .update-btn { 
            background-color: #4CAF50;
            border: none;
            color: white;
            padding: 10px 20px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin: 10px 0;
            cursor: pointer;
            border-radius: 5px;
        }
        .update-btn:hover {
            background-color: #45a049;
        }
        .invoice-btn {
            background-color: #ff9800;
            border: none;
            color: white;
            padding: 10px 20px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin: 10px 0;
            cursor: pointer;
            border-radius: 5px;
        }
        .invoice-btn:hover {
            background-color: #e68a00;
        }
        .export-btn {
            background-color: #2196F3;
            border: none;
            color: white;
            padding: 10px 20px;
            text-align: center;
            text-decoration: none;
            display: inline-block;
            font-size: 16px;
            margin: 10px 0;
            cursor: pointer;
            border-radius: 5px;
        }
        .export-btn:hover {
            background-color: #0b7dda;
        }
        .error-box {
            background-color: #ffdddd;
            border-left: 6px solid #f44336;
            padding: 15px;
            margin-bottom: 20px;
        }
        .init-error {
            background-color: #fff3cd;
            border-left: 6px solid #ffc107;
            padding: 15px;
            margin-bottom: 20px;
        }
        .error-list {
            max-height: 200px;
            overflow-y: auto;
            border: 1px solid #ddd;
            padding: 10px;
            margin-top: 10px;
        }
        .button-group {
            display: flex;
            gap: 10px;
            flex-wrap: wrap;
        }
        .update-type {
            font-weight: bold;
            margin-bottom: 10px;
            color: #333;
        }
    </style>
</head>
<body>
    <h1>AWB Update Report</h1>
    
    <?php if ($connection_error): ?>
    <div class="error-box">
        <h2>Database Connection Error</h2>
        <p><?php echo htmlspecialchars($connection_error); ?></p>
        <p>Please check your database configuration and try again.</p>
    </div>
    <?php endif; ?>
    
    <?php if (!empty($init_errors)): ?>
    <div class="init-error">
        <h2>Initialization Warnings</h2>
        <?php foreach ($init_errors as $error): ?>
            <p><?php echo htmlspecialchars($error); ?></p>
        <?php endforeach; ?>
    </div>
    <?php endif; ?>
    
    <?php if (!$connection_error): ?>
    <form method="post">
        <div class="button-group">
            <button type="submit" name="update_awb_resi" class="update-btn">Update by Resi (match awb.resi)</button>
            <button type="submit" name="update_awb_invoice" class="invoice-btn">Update by Invoice (match awb.invoice)</button>
            <?php if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($update_summary['update_errors'])): ?>
                <button type="submit" name="export_errors" class="export-btn">Export Errors to CSV</button>
                <input type="hidden" name="error_messages" value="<?php echo htmlspecialchars(json_encode($update_summary['update_errors'])); ?>">
            <?php endif; ?>
        </div>
    </form>
    
    <?php if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($update_summary['update_type'] === 'resi' || $update_summary['update_type'] === 'invoice')): ?>
    <div class="summary">
        <h2>Update Summary</h2>
        <div class="update-type">
            <?php 
                if ($update_summary['update_type'] === 'resi') {
                    echo 'Update by Resi (matching awb.resi with resi.resi)';
                } else {
                    echo 'Update by Invoice (matching awb.invoice with resi.resi)';
                }
            ?>
        </div>
        <p><span class="success">Successfully updated records: <?php echo $update_summary['updated']; ?></span></p>
        <p>Already up-to-date records: <?php echo $update_summary['already_updated']; ?></p>
        <p><span class="warning">Records needing update: <?php echo $update_summary['needs_update']; ?></span></p>
        
        <?php if (!empty($update_summary['update_errors'])): ?>
        <div>
            <h3>Update Errors (<?php echo count($update_summary['update_errors']); ?>)</h3>
            <div class="error-list">
                <?php foreach ($update_summary['update_errors'] as $error): ?>
                    <p class="error"><?php echo htmlspecialchars($error); ?></p>
                <?php endforeach; ?>
            </div>
        </div>
        <?php endif; ?>
    </div>
    <?php endif; ?>
    <?php endif; ?>
</body>
</html>