<?php

if ( ! defined( 'ABSPATH' ) ) exit;

function dvs_courier_finance_bulk() {

    // START FORM DATA
    if (isset($_POST['dvs_finance_spreadsheet_data'])) {

        global $wpdb;

        $error_tracking = '';
        $courier = '';
        $invoice = '';

        $total_orders = 0;
        $success_orders = 0;
        $failed_orders = 0;

        $courier = $_POST['dvs_finance_courier'];
        $invoice = $_POST['dvs_finance_invoice'];
        $spreadsheetData = stripslashes($_POST['dvs_finance_spreadsheet_data']);

        // print_r($courier);
        // print_r($invoice);
        // print_r($spreadsheetData);

        $data = json_decode($spreadsheetData, true);

        foreach ($data as $row) {

            $courier    = sanitize_text_field($courier);          
            $tracking   = sanitize_text_field($row['Tracking']);
            $status     = sanitize_text_field($row['Status']);
            $cod        = sanitize_text_field($row['COD']);
            $fee        = sanitize_text_field($row['Fee']);         
            $weight     = sanitize_text_field($row['Weight']);
            $order      = sanitize_text_field($row['Order']);
            $invoice    = sanitize_text_field($invoice);

            $status = preg_replace("/[^ A-Za-z]+/", "", $status);
            $cod    = preg_replace("/[^0-9.]+/", "", $cod);
            $fee    = preg_replace("/[^0-9.]+/", "", $fee);

            $courier    = trim($courier);            
            $tracking   = trim($tracking);
            $status     = trim($status);            
            $cod        = trim($cod);
            $fee        = trim($fee);
            $weight     = trim($weight);
            $order      = trim($order);            
            $invoice    = trim($invoice);

            $check_tracking_status  = strtolower($status);

            if($check_tracking_status == 'rider delivered' || $check_tracking_status == "delivered payment in progress") {
                $payment = 'Partial';
            }
            else if ( preg_match("~\bdeliver\b~",$check_tracking_status) || preg_match("~\bdelivered\b~",$check_tracking_status) ) {
                $payment = 'Paid';
            }
            else if ( preg_match("~\breturn\b~",$check_tracking_status) || preg_match("~\breturned\b~",$check_tracking_status) || preg_match("~\bcancel\b~",$check_tracking_status) ) {
                $payment = 'Un-Paid';
            }          
            else {
                $payment = 'Partial';
            }

            $get_tracking_row = $wpdb->get_row("SELECT * FROM dvs_ucp_logs WHERE courier_tracking = '$tracking'");

            if (empty($get_tracking_row)) {
                $error_tracking .= $tracking .',';
                $failed_orders++;
            }
            else {
                $success_orders++;
                $old_courier_finance = $get_tracking_row->courier_finance;
                $old_courier_finance = json_decode($old_courier_finance, true) ;

                $finance_array = $old_courier_finance;

                $finance_array[] = [
                    "order"     => $order,
                    "courier"   => $courier,
                    "tracking"  => $tracking,
                    "status"    => $status,
                    "payment"   => $payment,
                    "cod"       => (int)$cod,
                    "fee"       => (int)$fee,
                    "weight"    => $weight,
                    "invoice"   => $invoice
                ];

                $new_courier_finance = json_encode($finance_array,JSON_UNESCAPED_UNICODE);

                $dbData = array();
                $dbData['courier_finance'] = $new_courier_finance;
                $wpdb->update('dvs_ucp_logs', $dbData, array('courier_tracking' => $tracking));
            }

            $total_orders++;
        }

        // SAVE FINANCE LOGS
        $old_invoice_logs   = get_option('dvs_invoice_logs');
        $old_invoice_logs   = json_decode($old_invoice_logs, true) ;
        $invoice_array      = $old_invoice_logs;
        $file               = "";

        $invoice_array[] = [
            "date" => wp_date( 'd F, Y g:i a' ),
            "courier" => $courier,
            "invoice" => $invoice,
            "file" => $file,
        ];
        $new_invoice_logs = json_encode($invoice_array,JSON_UNESCAPED_UNICODE);
        update_option('dvs_invoice_logs', $new_invoice_logs);


        // DISPLAY RESULTS
        if($error_tracking) {
            $error_tracking = substr($error_tracking, 0, -1);
            ?>
            <div class="error notice">
                <p>Error: Tracking not found for <?= $error_tracking ?></p>
            </div>        
            <?php
        }
        else {
            ?>
            <div class="updated notice">
                <p>Success: Finance report for <?php echo $courier ?> with Invoice <?php echo $invoice ?> has been submitted.</p>
            </div>
            <?php
        }

        ?>
        <div class="updated notice">
            <p>Total Orders: <?= $total_orders ?></p>
            <p>Success Orders: <?= $success_orders ?></p>
            <p>Failed Orders: <?= $failed_orders ?></p>
        </div>
        <?php

    }
    // END FORM DATA


    if(isset($_GET['success'])): ?>
        <div class="updated notice">
            <p>Finance report for <?php echo $_GET['courier'] ?> with Invoice <?php echo $_GET['invoice'] ?> has been submitted.</p>
        </div>
    <?php endif;?>

    <?php if( isset($_GET['error_tracking']) && !empty($_GET['error_tracking']) ): ?>
        <div class="error notice">
            <p>Tracking not found: <?php echo $_GET['error_tracking']?></p>
        </div>
    <?php endif;?>


    <div class="wrap">

        <h3 style="margin-bottom: 10px;">Courier Finance</h3>
        <small>Reconcile finance records of any courier company in Pakistan including TCS, Leoaprds Courier, Trax, M&P Courier, Call Courier, PostEx, BlueEx, Insta World,  Oshi Courier, Ship NOC, RGS Logistics, Couriers Next , Daewoo, Riders and AHL Courier.</small>

        <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
        <script src="https://bossanova.uk/jspreadsheet/v4/jexcel.js"></script>
        <link rel="stylesheet" href="https://bossanova.uk/jspreadsheet/v4/jexcel.css" type="text/css" />
        <script src="https://jsuites.net/v4/jsuites.js"></script>
        <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

        <center>
            <form id="dvs-finance-form" action="" method="post" style="margin: 20px 0px;">
                <input type="hidden" name="dvs_finance_spreadsheet_data" id="spreadsheet-data">
                <select id="dvs_finance_courier" name="dvs_finance_courier" required="true" style="min-width: 200px;">
                    <option value="">Select Courier</option>
                    <option value="TCS">TCS</option>
                    <option value="Leopards Courier">Leopards Courier</option>
                    <option value="Trax">Trax</option>
                    <option value="MNP Courier">MNP Courier</option>
                    <option value="Call Courier">Call Courier</option>
                    <option value="BlueEx">BlueEx</option>
                    <option value="PostEx">PostEx</option>
                    <option value="Insta World">Insta World</option>
                    <option value="Daewoo Courier">Daewoo Courier</option>                    
                    <option value="Rider">Rider</option>
                    <option value="Oshi Courier">Oshi Courier</option>
                    <option value="RGS Logistics">RGS Logistics</option>
                    <option value="Couriers Next">Couriers Next</option>
                    <option value="Falcomail">Falcomail</option>
                    <option value="Ship NOC">Ship NOC</option>
                    <option value="AHL Courier">AHL Courier</option>
                    <option value="Rocket Courier">Rocket Courier</option>
                    <option value="Covered Logistics">Covered Logistics</option>
                    <option value="Active Courier">Active Courier</option>
                </select>
                <input class="regular-text" type="text" id="dvs_finance_invoice" name="dvs_finance_invoice" required="true" placeholder="Invoice #" style="max-width: 250px;" />
                <input type="submit" value="Submit" class="button button-primary" style="margin-right: 30px;">
                <a href="<?= admin_url() ?>admin.php?page=dvs_courier_finance_bulk" class="woocommerce-Button button" style="float-right">Refresh</a>
            </form>
            <div id="my-spreadsheet"></div>
        </center>

        <br>
        <h3>Important Note</h3>
        <p>These are the following rules that must be apply before bulk uploading:</p>
        <ul>
            <li><strong>1.</strong> Max allowed 500 orders</li>
            <li><strong>2.</strong> Always upload oldest invoices first</li>
            <li><strong>3.</strong> You can view or export Finance details in courier logs</li>
            <li><strong>4.</strong> Please watch the video guide to avoid errors</li>
        <ul>  

        <br>
        <a href="https://www.loom.com/share/d2044c6a6bb445e6a8958a9773b7a795" target="blank" class="woocommerce-Button button">Video: How to Upload Courier Finance in Bulk</a>
        <br>
        <br>
        <a href="https://wa.me/9203234152099" target="blank" class="woocommerce-Button button">WhatsApp Support</a>

    </div>

    <script>
    // Maximum number of rows allowed
    var maxRows = 500;

    // Header names
    var headers = [
        'Tracking',
        'Status',
        'COD',
        'Fee',
        'Weight',
        'Order'
    ];

    // Initial data with empty rows
    var data = [];
    for (var i = 0; i < maxRows; i++) {
        data.push(['', '', '', '', '', '']);
    }

    var spreadsheet = jspreadsheet(document.getElementById('my-spreadsheet'), {
        data: data,
        columns:[
            { title:'Tracking', width:200 },
            { title:'Status', width:150 },
            { title:'COD', width:100 },
            { title:'Fee', width:100 },
            { title:'Weight', width:100 },
            { title:'Order', width:100 }
        ],
        onchange:function(instance, cell, x, y, value) {
            validateRows();
        }
    });

    // Form submit event
    document.getElementById('dvs-finance-form').addEventListener('submit', function(event) {
        // Prevent default form submission
        event.preventDefault();

        // Validate rows before submission
        if (!validateRows()) {
            alert('Please make sure each row with data has values in all columns before submitting.');
            return;
        }

        // Get data from the spreadsheet
        var spreadsheetData = spreadsheet.getData();

        // Convert data to JSON with headers
        var jsonData = [];
        for (var i = 0; i < spreadsheetData.length; i++) {
            var row = spreadsheetData[i];
            var hasData = false;
            for (var j = 0; j < row.length; j++) {
                if (row[j] !== '') {
                    hasData = true;
                    break;
                }
            }
            if (hasData) {
                var rowData = {};
                for (var k = 0; k < headers.length; k++) {
                    rowData[headers[k]] = row[k];
                }
                jsonData.push(rowData);
            }
        }

        // If no valid rows with data, do not set JSON data to the hidden input
        if (jsonData.length === 0) {
            alert('Please fill in at least one row before submitting.');
            return;
        }

        // Set JSON data to the hidden input
        document.getElementById('spreadsheet-data').value = JSON.stringify(jsonData);
        
        // Submit the form
        this.submit();
    });

    // Function to validate rows
    function validateRows() {
        var spreadsheetData = spreadsheet.getData();
        for (var i = 0; i < spreadsheetData.length; i++) {
            var row = spreadsheetData[i];
            var hasData = false;
            for (var j = 0; j < row.length; j++) {
                if (row[j] !== '') {
                    hasData = true;
                    break;
                }
            }
            if (hasData) {
                // If any column has data, check if all columns in the row have data
                for (var k = 0; k < row.length; k++) {
                    if (row[k] === '') {
                        return false; // If any column in the row is empty, return false
                    }
                }
            }
        }
        return true; // All rows are valid
    }
    </script>

<?php 
}