<?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, Daewoo FastEx, Insta World, Do Deliver, Barq Raftar, Rider TPL, RGS Logistics, Oshi Courier, Bouraq Express, Zaaf Courier, Ship NOC, Couriers Next, Rocket Courier, Falcomail 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="Daewoo Courier">Daewoo Courier</option>
                    <option value="Insta World">Insta World</option>
                    <option value="Rider">Rider</option>
                    <option value="Do Deliver">Do Deliver</option>
                    <option value="Bouraq Express">Bouraq Express</option>
                    <option value="Oshi Courier">Oshi Courier</option>
                    <option value="Zaaf Courier">Zaaf 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="Couriers Next">Couriers Next</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 
}