<?php
/**
 * SMU Monitoring Page
 * Description: Displays SMU monitoring data with hour meter-based SMU Hours calculation and DataTables support.
 */

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

// Enqueue DataTables CSS & JS
function smu_monitoring_enqueue_assets($hook) {
    if ($hook !== 'vision_link_page_smu_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', 'smu_monitoring_enqueue_assets');

// Fetch WooCommerce Serial Numbers
function smu_page_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 SMU Data with Hour Meter Calculation
function get_smu_data($serial_numbers, $latest_from_date = '', $latest_to_date = '') {
    if (empty($serial_numbers)) {
        return [];
    }

    // Connect to external database
     // 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 placeholders
    $placeholders = implode(',', array_fill(0, count($serial_numbers), '%s'));
    $params = $serial_numbers;

    // Convert dates to SQL format
    $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)) : '';

    // SQL query to fetch data
    $query = "SELECT * FROM wp_equipment_utilization WHERE serial_number IN ($placeholders)";

    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]);
    }

    $prepared_query = $custom_db->prepare($query, $params);
    $results = $custom_db->get_results($prepared_query, ARRAY_A);

    // SMU Data processing
    $smu_data = [];
    $hour_meter_values = [];

    foreach ($results as $row) {
        $serial = $row['serial_number'];
        $date = $row['latest_report'];
        $hour_meter = isset($row['hour_meter']) ? (float) $row['hour_meter'] : 0;

        if (!isset($hour_meter_values[$serial])) {
            $hour_meter_values[$serial] = [];
        }

        // Store hour meter readings
        $hour_meter_values[$serial][$date] = $hour_meter;

        $smu_data[$serial] = [
            'serial_number' => $serial,
            'from_date' => $date,
            'to_date' => $date,

            // Initialize hour meter values
            'start_hour_meter' => $hour_meter ?? 0,
            'end_hour_meter' => $hour_meter ?? 0,
            'smu_hours' => 0,

            // Extra columns
            'product_family' => $row['product_family'] ?? '',
            'make' => $row['make'] ?? '',
            'model' => $row['model'] ?? '',
            'runtime_hours' => $row['runtime_hours'] ?? '',
            'distance_traveled' => $row['distance_traveled'] ?? '',
            'target_runtime_hours' => $row['target_runtime_hours'] ?? '',
            'device_status' => $row['device_status'] ?? '',
            'asset_status' => $row['asset_status'] ?? ''
        ];
    }

    // Calculate SMU Hours based on Hour Meter values
    foreach ($hour_meter_values as $serial => $dates) {
        ksort($dates);

        if (count($dates) >= 2) {
            $first_date = key($dates);
            $last_date = array_key_last($dates);

            $start_hour_meter = $dates[$first_date];
            $end_hour_meter = $dates[$last_date];

            $smu_hours = $end_hour_meter - $start_hour_meter;

            $smu_data[$serial]['from_date'] = $first_date;
            $smu_data[$serial]['to_date'] = $last_date;
            $smu_data[$serial]['start_hour_meter'] = $start_hour_meter;
            $smu_data[$serial]['end_hour_meter'] = $end_hour_meter;
            $smu_data[$serial]['smu_hours'] = $smu_hours;
        }
    }

    return array_values($smu_data);
}

// Display the SMU Monitoring Page
function smu_monitoring_display_page() {
    $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']) : '';

    // Fetch WooCommerce serial numbers
    $woocommerce_serials = smu_page_get_woocommerce_serial_numbers();

    // Fetch SMU data
    $smu_data = get_smu_data($woocommerce_serials, $latest_from_date, $latest_to_date);
    ?>
    <style>
        .table-responsive .table-data tr:nth-child(even) {
            background-color: #ffbd2b30 !important;
        }
        #smu-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>SMU Monitoring</h1>
        
        <form method="get" action="">
            <input type="hidden" name="page" value="vision_link_page_smu_monitoring">
            <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 style="padding: 5px 15px;background-color: #000; color: #FFBD2B; border-radius:5px; border: 1px solid #FFBD2B;" type="submit">Filter</button>
        </form>

        <table id="smu-monitoring-table" class="table table-striped table-bordered table-data" style="width:100%">
            <thead style="background-color:#FFBD2B;">
                <tr>
                    <th>Sr No</th>
                    <th>Equipment Name</th>
                    <th>Make</th>
                    <th>Model</th>
                    <th>Serial Number</th>
                    <th>From Date</th>
                    <th>To Date</th>
                    <th>Start Hour Meter</th>
                    <th>End Hour Meter</th>
                    <th>SMU Hours</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $sr_no = 1;
                foreach ($smu_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>" . esc_html($row['from_date']) . "</td>";
                    echo "<td>" . esc_html($row['to_date']) . "</td>";
                    echo "<td>" . esc_html($row['start_hour_meter']) . "</td>";
                    echo "<td>" . esc_html($row['end_hour_meter']) . "</td>";
                    echo "<td>" . esc_html($row['smu_hours']) . "</td>";
                    echo "</tr>";
                }
                ?>
            </tbody>
        </table>
    </div>

    <script>
        jQuery(document).ready(function($) {
            $('#smu-monitoring-table').DataTable({
                "order": [[5, "desc"]],  // Sort by "From Date"
                "pageLength": 10,
                "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 after sorting
                }
            });
        });

    </script>
<?php
}