<?php
/**
 * RMS Monitoring Page
 * Description: Displays RMS monitoring data dynamically by matching WooCommerce product serial numbers with data from an external database (visionlink_db) and applying a date range filter.
 */

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

// Enqueue DataTables CSS & JS
// function rms_monitoring_enqueue_assets($hook) {
//     if ($hook !== 'vision_link_page_rms_monitoring') {
//         return;
//     }
//     wp_enqueue_style('datatable-css', 'https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css');
//     wp_enqueue_script('jquery');
//     wp_enqueue_script('datatable-js', 'https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js', array('jquery'), null, true);
// }
// add_action('admin_enqueue_scripts', 'rms_monitoring_enqueue_assets');

// Fetch WooCommerce Serial Numbers
function get_woocommerce_serial_numbers() {
    global $wpdb;

    $query = "
        SELECT pm.meta_value AS serial_number
        FROM {$wpdb->postmeta} pm
        INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
        WHERE pm.meta_key = 'equipment_serial_number'
    ";

    $results = $wpdb->get_col($query);

    return array_unique($results);
}

// Fetch Data from External DB (visionlink_db) Matching WooCommerce Serial Numbers with Date Filter
function get_rms_data_from_external_db($serial_numbers, $from_date = '', $to_date = '',$latest_from_date = '', $latest_to_date = '') {
    if (empty($serial_numbers)) {
        return [];
    }

    // Connect to external database (visionlink_db)
    // Local
    // $custom_db = new wpdb('root', '', 'visionlink_db', 'localhost');  
    // Production
    $custom_db = new wpdb('vision', 'Passrms!123', 'visionlink_db', 'localhost');

    if ($custom_db->last_error) {
        echo 'Database connection error: ' . esc_html($custom_db->last_error);
        return [];
    }

    // Prepare SQL with placeholders
    $placeholders = implode(',', array_fill(0, count($serial_numbers), '%s'));

    // Convert dates to SQL format
    $from_date_sql = !empty($from_date) ? date('Y-m-d', strtotime($from_date)) : '';
    $to_date_sql = !empty($to_date) ? date('Y-m-d', strtotime($to_date)) : '';
    $latest_from_sql = !empty($latest_from_date) ? date('Y-m-d', strtotime($latest_from_date)) : '';
    $latest_to_sql = !empty($latest_to_date) ? date('Y-m-d', strtotime($latest_to_date)) : '';


    // Base query
    $query = "
        SELECT * 
        FROM visionlink_db.wp_equipment_utilization
        WHERE serial_number IN ($placeholders)
    ";

    // Add date filtering using latest_report column
    $params = $serial_numbers;
    

    if ($from_date_sql && $to_date_sql) {
        $query .= " AND STR_TO_DATE(latest_report, '%d-%m-%Y') BETWEEN %s AND %s";
        $params = array_merge($serial_numbers, [$from_date_sql, $to_date_sql]);
    }
    // Add filtering for latest_report column based on DATE only
    if ($latest_from_sql && $latest_to_sql) {
        $query .= " AND DATE(latest_report) BETWEEN %s AND %s";
        $params = array_merge($params, [$latest_from_sql, $latest_to_sql]);
    }

    // Prepare and execute the query safely
    $prepared_query = call_user_func_array([$custom_db, 'prepare'], array_merge([$query], $params));
    $results = $custom_db->get_results($prepared_query, ARRAY_A);

    return $results;
}

// Display RMS Monitoring Page
function rms_monitoring_display_page() {
    $from_date = isset($_GET['from_date']) ? sanitize_text_field($_GET['from_date']) : '';
    $to_date = isset($_GET['to_date']) ? sanitize_text_field($_GET['to_date']) : '';
    $latest_from_date = isset($_GET['latest_from_date']) ? sanitize_text_field($_GET['latest_from_date']) : '';
    $latest_to_date = isset($_GET['latest_to_date']) ? sanitize_text_field($_GET['latest_to_date']) : '';

    // Get WooCommerce serial numbers
    $woocommerce_serials = get_woocommerce_serial_numbers();

    // Fetch matching data from external DB (visionlink_db) with date filtering
    $data = get_rms_data_from_external_db($woocommerce_serials, $from_date, $to_date,$latest_from_date, $latest_to_date);
    ?>
    <style>
        .table-responsive .table-data tr:nth-child(even) {
            background-color: #f0f0f0 !important;
        }
        #rms-monitoring-table tbody tr:nth-child(odd) {
            background-color: #fff !important;
        }
        button#show-settings-link {
            display: none;
        }
    </style>

    <div class="wrap table-responsive">
        <br>
        <h1 style="margin-bottom: 15px;">Rental Machines</h1>

        <!-- Date Range Filter -->
        <form method="get" action="">
            <input type="hidden" name="page" value="vision_link_page_rms_monitoring">
            <!-- <label>From Date:</label>
            <input type="date" name="from_date" value="<?php echo esc_attr($from_date); ?>">
            <label>To Date:</label>
            <input type="date" name="to_date" value="<?php echo esc_attr($to_date); ?>"> -->
            <label>Latest Report From:</label>
            <input type="date" name="latest_from_date" value="<?php echo esc_attr($latest_from_date); ?>">
            <label>Latest Report To:</label>
            <input type="date" name="latest_to_date" value="<?php echo esc_attr($latest_to_date); ?>">
            <button type="submit" style="margin-bottom: 10px; padding: 5px 15px; background-color: #000; color: #FFBD2B; border-radius: 5px; border: 1px solid #FFBD2B;">Filter</button>
        </form>

        <br><br>
        
        <table id="rms-monitoring-table" class="table table-striped table-bordered table-data" style="width:100%">
            <thead style="background-color:#FFBD2B;" class="table-dark">
                <tr>
                    <th>Sr No</th>
                    <th>Equipment Name</th>
                    <th>Make</th>
                    <th>Model</th>
                    <th>Serial Number</th>
                    <th>Latest Report</th>
                    <th>Hour Meter</th>
                    <!-- <th>Runtime Hours</th>
                    <th>Distance Traveled</th>
                    <th>Odometer</th>
                    <th>Target Runtime Hours</th>
                    <th>Device Status</th>
                    <th>Asset Status</th> -->
                </tr>
            </thead>
            <tbody>
                <?php 
                if (!empty($data)) {
                    $sr_no = 1;
                    foreach ($data as $row) {
                        echo "<tr>";
                        echo "<td>" . $sr_no++ . "</td>";
                        echo "<td>" . esc_html($row['product_family']) . "</td>";
                        echo "<td>" . esc_html($row['make']) . "</td>";
                        echo "<td>" . esc_html($row['model']) . "</td>";
                        echo "<td>" . esc_html($row['serial_number']) . "</td>";
                        echo "<td>" . (!empty($row['latest_report']) ? date('d-m-Y', strtotime($row['latest_report'])) : '') . "</td>";
                        echo "<td>" . esc_html($row['hour_meter']) . "</td>";
                        // echo "<td>" . esc_html($row['runtime_hours']) . "</td>";
                        // echo "<td>" . esc_html($row['distance_traveled']) . "</td>";
                        // echo "<td>" . esc_html($row['odometer']) . "</td>";
                        // echo "<td>" . esc_html($row['target_runtime_hours']) . "</td>";
                        // echo "<td>" . esc_html($row['device_status']) . "</td>";
                        // echo "<td>" . esc_html($row['asset_status']) . "</td>";
                        echo "</tr>";
                    }
                } else {
                    echo "<tr><td colspan='13'>No matching equipment found.</td></tr>";
                }
                ?>
            </tbody>
        </table>

    </div>
    
    <script>
        jQuery(document).ready(function($) {
            $('#rms-monitoring-table').DataTable({
                "paging": true,
                "order": [[5, "desc"]],  // Sort by "From Date"
                "searching": true,
                "ordering": true,
                "info": true,
                "columnDefs": [
                    { "orderable": false, "targets": 0 } // Prevent sorting on Sr No
                ],
                "rowCallback": function(row, data, index) {
                    $('td:eq(0)', row).html(index + 1); // Correct Sr No dynamically
                }
            });
        });

    </script>
    <?php
}