<?php
/**
 * Indicative Billing Page
 * Uses latest work order and unique function names prefixed with "ind_".
 */

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

// Create a single reusable DB connection
function ind_get_db_connection($db_name = 'visionlink_db') {
    static $connections = [];

    if (!isset($connections[$db_name])) {
        $connections[$db_name] = new wpdb('root', '', $db_name, 'localhost');

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

    return $connections[$db_name];
}

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

    $query = "
        SELECT pm.meta_value AS serial_number, p.ID AS product_id, p.post_date
        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_results($query, ARRAY_A);

    $serials = [];
    foreach ($results as $row) {
        $serials[$row['serial_number']] = [
            'product_id' => $row['product_id'],
            'post_date' => $row['post_date']
        ];
    }

    return $serials;
}

// Get VisionLink Data
function ind_get_visionlink_data($serial_numbers, $latest_from_date = '', $latest_to_date = '') {
    if (empty($serial_numbers)) {
        return [];
    }

    $db = ind_get_db_connection('visionlink_db');
    if (!$db) {
        return [];
    }

    $placeholders = implode(',', array_fill(0, count($serial_numbers), '%s'));
    $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)) : '';

    $query = "
        SELECT DISTINCT serial_number, product_family, make, model, MIN(latest_report) AS first_report
        FROM wp_equipment_utilization
        WHERE serial_number IN ($placeholders)
        GROUP BY serial_number
    ";

    // 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";
        $serial_numbers = array_merge($serial_numbers, [$latest_from_sql, $latest_to_sql]);
    }

    $prepared_query = $db->prepare($query, ...array_keys($serial_numbers));
    $results = $db->get_results($prepared_query, ARRAY_A);

    return $results;
}

// Get Latest WooCommerce Order
function ind_get_latest_order_info($product_id) {
    global $wpdb;

    $query = $wpdb->prepare("
        SELECT p.ID AS order_id, p.post_date AS order_date
        FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON p.ID = oi.order_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
        WHERE oim.meta_key = '_product_id' 
        AND oim.meta_value = %d
        AND p.post_type = 'shop_order'
        ORDER BY p.post_date DESC  -- Latest Order
        LIMIT 1
    ", $product_id);

    return $wpdb->get_row($query);
}

// ✅ Get Latest Work Order (for indicative billing)
function ind_get_latest_work_order($order_id) {
    global $wpdb;

    $query = $wpdb->prepare("
        SELECT id AS work_order_id, created_date,billing_start_date,billing_end_date
        FROM wp_work_orders
        WHERE contract_id = %d 
        AND wo_type = %s
        ORDER BY created_date DESC 
        LIMIT 1
    ", $order_id, 'Monthly Work Order');

    return $wpdb->get_row($query);
}

// Get Odometer by Serial and Date
function ind_get_odometer_by_date($serial_number, $date) {
    $db = ind_get_db_connection('visionlink_db');
    if (!$db) {
        return null;
    }

    $formatted_date = date('Y-m-d', strtotime($date));

    $query = $db->prepare("
        SELECT odometer
        FROM wp_equipment_utilization
        WHERE serial_number = %s
        AND DATE(latest_report) = %s
        LIMIT 1
    ", $serial_number, $formatted_date);

    $result = $db->get_var($query);
    return $result ? floatval($result) : null;
}


// added-aseema
// Fetch Hour Meter from wp_equipment_utilization
function ind_get_hour_meter_by_date($serial_number, $date) {
    $db = ind_get_db_connection('visionlink_db');
    if (!$db) {
        return null;
    }

    $formatted_date = date('Y-m-d', strtotime($date));

    $query = $db->prepare("
        SELECT hour_meter
        FROM wp_equipment_utilization
        WHERE serial_number = %s
        AND DATE(latest_report) = %s
        LIMIT 1
    ", $serial_number, $formatted_date);

    $result = $db->get_var($query);
    return $result ? floatval($result) : null;
}
// end added -aseema
// Display Indicative Billing Page
function indicative_billing_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']) : '';

    $woocommerce_serials = ind_get_woocommerce_serial_numbers();
    $data = ind_get_visionlink_data($woocommerce_serials, );

    ?>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>

    <style>
        .table-responsive .table-data tr:nth-child(even) {
            background-color: #f0f0f0 !important;
        }
        #indicative-billing-table tbody tr:nth-child(odd) {
            background-color: #fff !important;
        }
        th, td {
            white-space: nowrap;
        }
    </style>

    <div class="wrap table-responsive">
        <br>
        <h1>Indicative Billing</h1><br>

        <form method="get" action="">
            <input type="hidden" name="page" value="indicative_billing">
           
            <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;background-color: #000; color: #FFBD2B; border-radius:5px; border: 1px solid #FFBD2B;" type="submit">Filter</button>
        </form>

        <br><br>

        <table id="indicative-billing-table" class="table table-striped table-bordered table-data display" 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>Contract ID</th>
                    <th>Contract Start Date</th>
                    <th>Contract End Date</th>
                    <th>Rental Amount</th>
                    <th>Per Day Amount</th>
                    <th>Work Order ID</th>
                    <th>WO Billing Start Date</th>
                    <th>WO Billing End Date</th>
                    <!-- <th>Hour Meter Start</th> -->
                    <!-- <th>Hour Meter End</th> -->
                    <th>Hour Meter Difference</th>
                    <!-- <th>Leakage</th>
                    <th>Leakage SMU Hours</th> -->
                    
                </tr>
            </thead>
            <tbody>
                <?php 
                $sr_no = 1;
                foreach ($data as $row) {
                    $serial = $row['serial_number'];
                    $product_info = $woocommerce_serials[$serial] ?? null;

                    if (!$product_info) {
                        continue;
                    }

                    $contract_date = $product_info['post_date'];

                    // Apply date filtering
                    if ((!empty($from_date) && $contract_date < $from_date) || 
                        (!empty($to_date) && $contract_date > $to_date)) {
                        continue;
                    }

                    $order = ind_get_latest_order_info($product_info['product_id']);
                    $work_order = $order ? ind_get_latest_work_order($order->order_id) : null;

                    $contract_id = $order->order_id ?? '-';
                    $contract_start_date = get_post_meta($order->order_id, 'start_date', true);
                    $contract_end_date = get_post_meta($order->order_id, 'end_date', true);

                    if (!empty($contract_start_date)) {
                        $contract_start_date = date('d-m-Y', strtotime($contract_start_date));
                    }
                    
                    if (!empty($contract_end_date)) {
                        $contract_end_date = date('d-m-Y', strtotime($contract_end_date));
                    }
                    // Calculate contract duration in days
                    $days_diff = 0;
                    if (!empty($contract_start_date) && !empty($contract_end_date)) {
                        $start_date_obj = DateTime::createFromFormat('d-m-Y', $contract_start_date);
                        $end_date_obj = DateTime::createFromFormat('d-m-Y', $contract_end_date);
                        $interval = $start_date_obj->diff($end_date_obj);
                        $days_diff = $interval->days + 1; // Including both start and end date
                    }
                    // Calculate per day amount
                    $per_day_amount = ($days_diff > 0) ? round($rental_amount / $days_diff, 2) : 0.00;

                    $rental_amount = get_post_meta($order->order_id, 'rental_amount', true);

                    $contract_date_display = $order ? date('d-m-Y', strtotime($order->order_date)) : '-';
                    $work_order_id = $work_order->work_order_id ?? '-';
                    $work_order_date = $work_order ? date('d-m-Y', strtotime($work_order->created_date)) : '-';
                    $work_order_billing_start_date = $work_order ? date('d-m-Y', strtotime($work_order->billing_start_date)) : '-';
                    $work_order_billing_end_date = $work_order ? date('d-m-Y', strtotime($work_order->billing_end_date)) : '-';

                    // Fetch hour_meter values for billing start & end dates
                    $hour_meter_start = ind_get_hour_meter_by_date($serial, $work_order->billing_start_date);
                    $hour_meter_end = ind_get_hour_meter_by_date($serial, $work_order->billing_end_date);
                    $hour_meter_diff = ($hour_meter_end !== null && $hour_meter_start !== null) ? round($hour_meter_end - $hour_meter_start, 2) : '-';

                    $odometer_order = ind_get_odometer_by_date($serial, $contract_date_display);
                    $odometer_work = ind_get_odometer_by_date($serial, $work_order_date);

                    $leakage_smu = ($odometer_work && $odometer_order) ? round(max(0, $odometer_work - $odometer_order), 2) : 0;
                    $leakage = $leakage_smu > 0 ? 'Yes' : 'No';

                    echo "<tr>
                    <td>{$sr_no}</td>
                    <td>{$row['product_family']}</td>
                    <td>{$row['make']}</td>
                    <td>{$row['model']}</td>
                    <td>{$serial}</td>
                    <td>{$contract_id}</td>
                    <td>{$contract_start_date}</td>
                    <td>{$contract_end_date}</td>
                    <td>{$rental_amount}</td>
                    <td>{$per_day_amount}</td>
                    <td>{$work_order_id}</td>
                    <td>{$work_order_billing_start_date}</td>
                    <td>{$work_order_billing_end_date}</td>
                    
                    <td>{$hour_meter_diff}</td>
                   
                    
                  </tr>";
                    $sr_no++;
                }
                ?>
            </tbody>
        </table>
    </div>
    <script>$(document).ready(function() { $('#indicative-billing-table').DataTable(); });</script>
<?php
}