<?php
require 'vendor/autoload.php'; // Pastikan library PhpSpreadsheet terinstall

use PhpOffice\PhpSpreadsheet\IOFactory;

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

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

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

// Process form submission
if (isset($_POST['submit'])) {
    // Check if file was uploaded without errors
    if (isset($_FILES['excelfile']) && $_FILES['excelfile']['error'] == 0) {
        $filename = $_FILES['excelfile']['tmp_name'];
        
        // Check if file is Excel
        $file_ext = pathinfo($_FILES['excelfile']['name'], PATHINFO_EXTENSION);
        $allowed_ext = ['xls', 'xlsx'];
        
        if (!in_array(strtolower($file_ext), $allowed_ext)) {
            $error = "Error: Hanya file Excel (.xls, .xlsx) yang diperbolehkan.";
        } else {
            try {
                // Load Excel file
                $spreadsheet = IOFactory::load($filename);
                $worksheet = $spreadsheet->getActiveSheet();
                $rows = $worksheet->toArray();
                
                // Remove header row
                array_shift($rows);
                
                // Prepare SQL statement
                $sql = "INSERT INTO tiktok_order (
                    `Order ID`, `Order Status`, `Order Substatus`, `Cancelation/Return Type`, `Normal or Pre-order`, 
                    `SKU ID`, `Seller SKU`, `Product Name`, `Variation`, `Quantity`, `Sku Quantity of return`, 
                    `SKU Unit Original Price`, `SKU Subtotal Before Discount`, `SKU Platform Discount`, `SKU Seller Discount`, 
                    `SKU Subtotal After Discount`, `Shipping Fee After Discount`, `Original Shipping Fee`, `Shipping Fee Seller Discount`, 
                    `Shipping Fee Platform Discount`, `Payment platform discount`, `Buyer Service Fee`, `Handling Fee`, 
                    `Shipping Insurance`, `Item Insurance`, `Order Amount`, `Order Refund Amount`, `Created Time`, `Paid Time`, 
                    `RTS Time`, `Shipped Time`, `Delivered Time`, `Cancelled Time`, `Cancel By`, `Cancel Reason`, 
                    `Fulfillment Type`, `Warehouse Name`, `Tracking ID`, `Delivery Option`, `Shipping Provider Name`, 
                    `Buyer Message`, `Buyer Username`, `Recipient`, `Phone #`, `Zipcode`, `Country`, `Province`, 
                    `Regency and City`, `Districts`, `Villages`, `Detail Address`, `Additional address information`, 
                    `Payment Method`, `Weight(kg)`, `Product Category`, `Package ID`, `Purchase Channel`, `Seller Note`, 
                    `Checked Status`, `Checked Marked by`, `Tokopedia Invoice Number`
                ) VALUES (
                    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
                    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
                ) ON DUPLICATE KEY UPDATE 
                    `Order Status` = VALUES(`Order Status`),
                    `Order Substatus` = VALUES(`Order Substatus`),
                    `Delivered Time` = VALUES(`Delivered Time`),
                    `Tracking ID` = VALUES(`Tracking ID`)";
                
                $stmt = $conn->prepare($sql);
                if ($stmt === false) {
                    die("Error preparing statement: " . $conn->error);
                }
                
                // Bind parameters
                $stmt->bind_param(
                    'sssssssssiiddddddddddddddddssssssssssssssssssssssssssssssssssssss',
                    $order_id, $order_status, $order_substatus, $cancelation_return_type, $normal_or_preorder,
                    $sku_id, $seller_sku, $product_name, $variation, $quantity, $sku_quantity_of_return,
                    $sku_unit_original_price, $sku_subtotal_before_discount, $sku_platform_discount, $sku_seller_discount,
                    $sku_subtotal_after_discount, $shipping_fee_after_discount, $original_shipping_fee, $shipping_fee_seller_discount,
                    $shipping_fee_platform_discount, $payment_platform_discount, $buyer_service_fee, $handling_fee,
                    $shipping_insurance, $item_insurance, $order_amount, $order_refund_amount, $created_time, $paid_time,
                    $rts_time, $shipped_time, $delivered_time, $cancelled_time, $cancel_by, $cancel_reason,
                    $fulfillment_type, $warehouse_name, $tracking_id, $delivery_option, $shipping_provider_name,
                    $buyer_message, $buyer_username, $recipient, $phone, $zipcode, $country, $province,
                    $regency_and_city, $districts, $villages, $detail_address, $additional_address_information,
                    $payment_method, $weight, $product_category, $package_id, $purchase_channel, $seller_note,
                    $checked_status, $checked_marked_by, $tokopedia_invoice_number
                );
                
                $import_count = 0;
                $error_count = 0;
                
                // Process each row
                foreach ($rows as $row) {
                    // Skip empty rows
                    if (empty($row[0])) continue;
                    
                    // Assign values from Excel to variables
                    $order_id = trim($row[0]);
                    $order_status = trim($row[1]);
                    $order_substatus = trim($row[2]);
                    $cancelation_return_type = trim($row[3]);
                    $normal_or_preorder = trim($row[4]);
                    $sku_id = trim($row[5]);
                    $seller_sku = trim($row[6]);
                    $product_name = trim($row[7]);
                    $variation = trim($row[8]);
                    $quantity = (int)trim($row[9]);
                    $sku_quantity_of_return = (int)trim($row[10]);
                    $sku_unit_original_price = (float)trim($row[11]);
                    $sku_subtotal_before_discount = (float)trim($row[12]);
                    $sku_platform_discount = (float)trim($row[13]);
                    $sku_seller_discount = (float)trim($row[14]);
                    $sku_subtotal_after_discount = (float)trim($row[15]);
                    $shipping_fee_after_discount = (float)trim($row[16]);
                    $original_shipping_fee = (float)trim($row[17]);
                    $shipping_fee_seller_discount = (float)trim($row[18]);
                    $shipping_fee_platform_discount = (float)trim($row[19]);
                    $payment_platform_discount = (float)trim($row[20]);
                    $buyer_service_fee = (float)trim($row[21]);
                    $handling_fee = (float)trim($row[22]);
                    $shipping_insurance = (float)trim($row[23]);
                    $item_insurance = (float)trim($row[24]);
                    $order_amount = (float)trim($row[25]);
                    $order_refund_amount = (float)trim($row[26]);
                    
                    // Format date fields
                    $created_time = !empty(trim($row[27])) ? date('Y-m-d H:i:s', strtotime(trim($row[27]))) : null;
                    $paid_time = !empty(trim($row[28])) ? date('Y-m-d H:i:s', strtotime(trim($row[28]))) : null;
                    $rts_time = !empty(trim($row[29])) ? date('Y-m-d H:i:s', strtotime(trim($row[29]))) : null;
                    $shipped_time = !empty(trim($row[30])) ? date('Y-m-d H:i:s', strtotime(trim($row[30]))) : null;
                    $delivered_time = !empty(trim($row[31])) ? date('Y-m-d H:i:s', strtotime(trim($row[31]))) : null;
                    $cancelled_time = !empty(trim($row[32])) ? date('Y-m-d H:i:s', strtotime(trim($row[32]))) : null;
                    
                    $cancel_by = trim($row[33]);
                    $cancel_reason = trim($row[34]);
                    $fulfillment_type = trim($row[35]);
                    $warehouse_name = trim($row[36]);
                    $tracking_id = trim($row[37]);
                    $delivery_option = trim($row[38]);
                    $shipping_provider_name = trim($row[39]);
                    $buyer_message = trim($row[40]);
                    $buyer_username = trim($row[41]);
                    $recipient = trim($row[42]);
                    $phone = trim($row[43]);
                    $zipcode = trim($row[44]);
                    $country = trim($row[45]);
                    $province = trim($row[46]);
                    $regency_and_city = trim($row[47]);
                    $districts = trim($row[48]);
                    $villages = trim($row[49]);
                    $detail_address = trim($row[50]);
                    $additional_address_information = trim($row[51]);
                    $payment_method = trim($row[52]);
                    $weight = (float)trim($row[53]);
                    $product_category = trim($row[54]);
                    $package_id = trim($row[55]);
                    $purchase_channel = trim($row[56]);
                    $seller_note = trim($row[57]);
                    $checked_status = trim($row[58]);
                    $checked_marked_by = trim($row[59]);
                    $tokopedia_invoice_number = trim($row[60]);
                    
                    // Execute the prepared statement
                    if ($stmt->execute()) {
                        $import_count++;
                    } else {
                        $error_count++;
                        error_log("Error importing record: " . $stmt->error);
                    }
                }
                
                $stmt->close();
                
                $message = "Import completed. Success: $import_count, Errors: $error_count";
            } catch (Exception $e) {
                $error = "Error processing Excel file: " . $e->getMessage();
            }
        }
    } else {
        $error = "Error: Please select a file to upload.";
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Import TikTok Orders - Excel</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            line-height: 1.6;
            margin: 0;
            padding: 20px;
            background-color: #f5f5f5;
        }
        .container {
            max-width: 800px;
            margin: 0 auto;
            background: #fff;
            padding: 20px;
            border-radius: 5px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
        }
        h1 {
            text-align: center;
            color: #333;
        }
        .form-group {
            margin-bottom: 15px;
        }
        label {
            display: block;
            margin-bottom: 5px;
            font-weight: bold;
        }
        input[type="file"] {
            display: block;
            width: 100%;
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        button {
            background-color: #4CAF50;
            color: white;
            padding: 10px 15px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            font-size: 16px;
        }
        button:hover {
            background-color: #45a049;
        }
        .message {
            padding: 10px;
            margin: 15px 0;
            border-radius: 4px;
        }
        .success {
            background-color: #dff0d8;
            color: #3c763d;
            border: 1px solid #d6e9c6;
        }
        .error {
            background-color: #f2dede;
            color: #a94442;
            border: 1px solid #ebccd1;
        }
        .requirements {
            background-color: #f8f9fa;
            padding: 15px;
            border-radius: 4px;
            border: 1px solid #ddd;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Import TikTok Orders - Excel</h1>
        
        <?php if (!empty($error)): ?>
            <div class="message error">
                <?php echo htmlspecialchars($error); ?>
            </div>
        <?php endif; ?>
        
        <?php if (!empty($message)): ?>
            <div class="message success">
                <?php echo htmlspecialchars($message); ?>
            </div>
        <?php endif; ?>
        
        <form action="" method="post" enctype="multipart/form-data">
            <div class="form-group">
                <label for="excelfile">Select Excel File:</label>
                <input type="file" name="excelfile" id="excelfile" accept=".xlsx,.xls" required>
            </div>
            
            <div class="form-group">
                <button type="submit" name="submit">Import Excel</button>
            </div>
        </form>
        
        <div class="requirements">
            <h3>Format Excel yang Dibutuhkan:</h3>
            <ol>
                <li>File harus dalam format Excel (.xlsx atau .xls)</li>
                <li>Baris pertama harus berisi header dengan nama kolom yang sesuai</li>
                <li>Kolom harus berurutan sesuai dengan struktur database</li>
                <li>Format tanggal bisa dalam berbagai format yang dikenali Excel</li>
                <li>Data yang sudah ada akan diupdate berdasarkan Order ID</li>
            </ol>
            
            <h3>Instalasi Required:</h3>
            <p>Script ini membutuhkan library PhpSpreadsheet. Install dengan command berikut:</p>
            <pre>composer require phpoffice/phpspreadsheet</pre>
        </div>
    </div>
</body>
</html>

<?php
$conn->close();
?>