<?php
/**
 * Financial Monitoring Page
 * Updated with proper UI-side date filtering and fetching the oldest work order.
 */

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

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

    if (!isset($connections[$db_name])) {
        // local
        // $connections[$db_name] = new wpdb('root', '', $db_name, 'localhost');
        // production
        $connections[$db_name] = new wpdb('vision', 'Passrms!123', $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 fin_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 with DISTINCT to prevent duplicates
function get_visionlink_data($serial_numbers) {
    if (empty($serial_numbers)) {
        return [];
    }

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

    $placeholders = implode(',', array_fill(0, count($serial_numbers), '%s'));

    $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
    ";

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

    return $results;
}

// Get Latest WooCommerce Order
function 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 ASC
        LIMIT 1
    ", $product_id);

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

// ✅ Get Oldest Work Order
function get_oldest_work_order($order_id) {
    global $wpdb;

    $query = $wpdb->prepare("
        SELECT id AS work_order_id, created_date
        FROM wp_work_orders
        WHERE contract_id = %d
        ORDER BY created_date ASC
        LIMIT 1
    ", $order_id);

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

// Get Hour Meter by Serial and Date
function get_hour_meter_by_date($serial_number, $date) {
    $db = 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;
}

// Display Financial Monitoring Page
function financial_monitoring_display_page() {
    $contract_from_date = isset($_GET['contract_from_date']) ? sanitize_text_field($_GET['contract_from_date']) : '';
    $contract_to_date = isset($_GET['contract_to_date']) ? sanitize_text_field($_GET['contract_to_date']) : '';

    $woocommerce_serials = fin_get_woocommerce_serial_numbers();
    $data = 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;
        }
        #financial-monitoring-table tbody tr:nth-child(odd) {
            background-color: #fff !important;
        }
        th, td {
            white-space: nowrap;
        }
    </style>

    <div class="wrap table-responsive">
        <br>
        <h1>Financial Monitoring</h1><br>

        <form method="get" action="">
        <input type="hidden" name="page" value="vision_link_page_financial_monitoring">
            <label>Contract From Date:</label>
            <input type="date" name="contract_from_date" value="<?php echo esc_attr($contract_from_date); ?>">
            <label>Contract To Date:</label>
            <input type="date" name="contract_to_date" value="<?php echo esc_attr($contract_to_date); ?>">
            <button style="padding: 5px 15px;background-color: #000; color: #FFBD2B; border-radius:5px; border: 1px solid #FFBD2B;" type="submit">Filter</button>
        </form>

        <br><br>

        <table id="financial-monitoring-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 Date</th>
                    <th>Work Order ID</th>
                    <th>Work Order Date</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;
                    }                    

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

                    $contract_id = $order->order_id ?? '-';
                    $contract_date_display = $order ? date('d-m-Y', strtotime($order->order_date)) : '-';

                    if ((!empty($contract_from_date) && $contract_date_display < date('d-m-Y', strtotime($contract_from_date))) || 
                        (!empty($contract_to_date) && $contract_date_display > date('d-m-Y', strtotime($contract_to_date)))) {
                        continue;
                    }

                    $work_order_id = $work_order->work_order_id ?? '-';
                    $work_order_date = $work_order ? date('d-m-Y', strtotime($work_order->created_date)) : '-';

                    $hour_meter_order = get_hour_meter_by_date($serial, $contract_date_display);
                    $hour_meter_work = get_hour_meter_by_date($serial, $work_order_date);

                    $leakage_smu = ($hour_meter_work && $hour_meter_order) ? round(max(0, $hour_meter_work - $hour_meter_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_date_display}</td>
                            <td>{$work_order_id}</td>
                            <td>{$work_order_date}</td>
                            <td>{$leakage}</td>
                            <td>{$leakage_smu}</td>
                          </tr>";
                    $sr_no++;
                }
                ?>
            </tbody>
        </table>
    </div>
    <script>$(document).ready(function() { $('#financial-monitoring-table').DataTable(); });</script>
<?php
}