<?php
/*
Plugin Name: Custom Dashboard BA
Description: Adds custom dashboard content.
Version: 1.1
Author: Itrosys
*/

// Add first dashboard menu item
function custom_dashboard_menu_item_one() {
    add_menu_page(
        'Dashboard', // Page title
        'Dashboard', // Menu title
        'manage_options',       // Capability required to access
        'custom-dashboard-one', // Menu slug
        'custom_dashboard_page_content_one', // Callback function for content
        'dashicons-admin-generic', // Icon
        5 // Position on the admin menu
    );
}
add_action( 'admin_menu', 'custom_dashboard_menu_item_one' );

// Callback function for first dashboard content
function custom_dashboard_page_content_one() {
    // Load HTML content from admin-tem.php
    include_once( plugin_dir_path( __FILE__ ) . 'admin-tem.php' );
}

// Add second dashboard menu item
function custom_dashboard_menu_item_two() {
    add_menu_page(
        'Custom Dashboard Two', // Page title
        'Custom Dashboard Two', // Menu title
        'manage_options',       // Capability required to access
        'custom-dashboard-two', // Menu slug
        'custom_dashboard_page_content_two', // Callback function for content
        'dashicons-admin-generic', // Icon
        6 // Position on the admin menu
    );
}
// add_action( 'admin_menu', 'custom_dashboard_menu_item_two' );

// Callback function for second dashboard content
function custom_dashboard_page_content_two() {
    // Load HTML content from admin-ecom.php
    include_once( plugin_dir_path( __FILE__ ) . 'admin-ecom.php' );
}


// Hook to admin_menu to add a submenu under 'Users'
add_action('admin_menu', 'add_active_users_submenu');

function add_active_users_submenu() {
    // Add submenu page under the "Users" menu
    add_users_page(
        'Active Users',         // Page title
        'Active Users',         // Menu title
        'manage_options',       // Capability (admin access)
        'active-users',         // Menu slug
        'display_active_users'  // Callback function to display content
    );
}

// Callback function to display content on the "Active Users" page
function display_active_users() {
    ?>
    <style>
        .dataTables_wrapper .dataTables_length select {
            padding: 0px 17px 1px 8px;
        }
        
    </style>
    <?php
    global $wpdb;
    $query = "
        SELECT u.ID as customer_id, u.display_name, u.user_email, 
               um_phone.meta_value AS phone_number, 
               um_sap.meta_value AS sap_customer_id, 
               -- Get Shipping Address, fallback to Billing Address if empty
               COALESCE(
                   NULLIF(
                       CONCAT_WS(', ',
                           NULLIF(shipping_first_name.meta_value, ''),
                           NULLIF(shipping_last_name.meta_value, ''),
                           NULLIF(shipping_address_1.meta_value, ''),
                           NULLIF(shipping_address_2.meta_value, ''),
                           NULLIF(shipping_city.meta_value, ''),
                           NULLIF(shipping_state.meta_value, ''),
                           NULLIF(shipping_postcode.meta_value, ''),
                           NULLIF(shipping_country.meta_value, '')
                       ), ', , , , , , , '  -- Empty address check
                   ), 
                   CONCAT_WS(', ',
                       NULLIF(billing_first_name.meta_value, ''),
                       NULLIF(billing_last_name.meta_value, ''),
                       NULLIF(billing_address_1.meta_value, ''),
                       NULLIF(billing_address_2.meta_value, ''),
                       NULLIF(billing_city.meta_value, ''),
                       NULLIF(billing_state.meta_value, ''),
                       NULLIF(billing_postcode.meta_value, ''),
                       NULLIF(billing_country.meta_value, '')
                   ), 'N/A'
               ) AS shipping_address,
               -- Get Billing Address
               COALESCE(
                   CONCAT_WS(', ',
                       NULLIF(billing_first_name.meta_value, ''),
                       NULLIF(billing_last_name.meta_value, ''),
                       NULLIF(billing_address_1.meta_value, ''),
                       NULLIF(billing_address_2.meta_value, ''),
                       NULLIF(billing_city.meta_value, ''),
                       NULLIF(billing_state.meta_value, ''),
                       NULLIF(billing_postcode.meta_value, ''),
                       NULLIF(billing_country.meta_value, '')
                   ), 'N/A'
               ) AS billing_address
        FROM {$wpdb->users} u
        INNER JOIN {$wpdb->usermeta} um ON u.ID = um.user_id
        LEFT JOIN {$wpdb->usermeta} um_phone ON u.ID = um_phone.user_id AND um_phone.meta_key = 'billing_phone'
        LEFT JOIN {$wpdb->usermeta} um_sap ON u.ID = um_sap.user_id AND um_sap.meta_key = 'sap_customer_id'
        INNER JOIN {$wpdb->postmeta} pm ON u.ID = pm.meta_value
        INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
         -- Shipping Address Fields from usermeta
        LEFT JOIN {$wpdb->usermeta} shipping_first_name ON u.ID = shipping_first_name.user_id AND shipping_first_name.meta_key = 'shipping_first_name'
        LEFT JOIN {$wpdb->usermeta} shipping_last_name ON u.ID = shipping_last_name.user_id AND shipping_last_name.meta_key = 'shipping_last_name'
        LEFT JOIN {$wpdb->usermeta} shipping_address_1 ON u.ID = shipping_address_1.user_id AND shipping_address_1.meta_key = 'shipping_address_1'
        LEFT JOIN {$wpdb->usermeta} shipping_address_2 ON u.ID = shipping_address_2.user_id AND shipping_address_2.meta_key = 'shipping_address_2'
        LEFT JOIN {$wpdb->usermeta} shipping_city ON u.ID = shipping_city.user_id AND shipping_city.meta_key = 'shipping_city'
        LEFT JOIN {$wpdb->usermeta} shipping_state ON u.ID = shipping_state.user_id AND shipping_state.meta_key = 'shipping_state'
        LEFT JOIN {$wpdb->usermeta} shipping_postcode ON u.ID = shipping_postcode.user_id AND shipping_postcode.meta_key = 'shipping_postcode'
        LEFT JOIN {$wpdb->usermeta} shipping_country ON u.ID = shipping_country.user_id AND shipping_country.meta_key = 'shipping_country'
        -- Billing Address Fields
        LEFT JOIN {$wpdb->usermeta} billing_first_name ON u.ID = billing_first_name.user_id AND billing_first_name.meta_key = 'billing_first_name'
        LEFT JOIN {$wpdb->usermeta} billing_last_name ON u.ID = billing_last_name.user_id AND billing_last_name.meta_key = 'billing_last_name'
        LEFT JOIN {$wpdb->usermeta} billing_address_1 ON u.ID = billing_address_1.user_id AND billing_address_1.meta_key = 'billing_address_1'
        LEFT JOIN {$wpdb->usermeta} billing_address_2 ON u.ID = billing_address_2.user_id AND billing_address_2.meta_key = 'billing_address_2'
        LEFT JOIN {$wpdb->usermeta} billing_city ON u.ID = billing_city.user_id AND billing_city.meta_key = 'billing_city'
        LEFT JOIN {$wpdb->usermeta} billing_state ON u.ID = billing_state.user_id AND billing_state.meta_key = 'billing_state'
        LEFT JOIN {$wpdb->usermeta} billing_postcode ON u.ID = billing_postcode.user_id AND billing_postcode.meta_key = 'billing_postcode'
        LEFT JOIN {$wpdb->usermeta} billing_country ON u.ID = billing_country.user_id AND billing_country.meta_key = 'billing_country'
        WHERE um.meta_key = '{$wpdb->prefix}capabilities'
        AND um.meta_value LIKE '%customer%'
        AND pm.meta_key = '_customer_user'
        AND p.post_type = 'shop_order'
        GROUP BY u.ID
        ORDER BY u.display_name ASC
    ";

    // Execute the query
    $customers_orders = $wpdb->get_results($query);
    ?>
    
    <!-- /end of top header -->
    <div class="wrap">
        <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; margin-top:20px;">
            <h1 style="margin: 0;">Active Customers</h1>
        </div>
        <button id="exportbuttonusers" style="padding: 5px 20px; background-color: #000; color: #FFBD2B; border-radius: 5px; margin-bottom: 20px;">Export</button>

        <?php if (!empty($customers_orders)) : ?>
            <table class="wp-list-table widefat fixed striped" id ="customerOrdersTable">
                <thead>
                    <tr>
                        <th scope="col" class="manage-column" style="width:50px;">S/N</th> <!-- Serial Number Column -->
                        <th scope="col" class="manage-column column-primary">Customer Name</th> <!-- Customer Name Column -->
                        <th scope="col" class="manage-column">Email</th> <!-- Email Column -->
                        <th scope="col" class="manage-column">Phone</th> <!-- Phone Column -->
                        <th scope="col" class="manage-column">SAP Customer ID</th>
                        <th scope="col" class="manage-column">Shipping Address</th> 
                        <th>Billing Address</th>
                        
                    </tr>
                </thead>
                <tbody>
                    <?php 
                    $serial_number = 1; // Initialize serial number
                    foreach ($customers_orders as $customer_order) : ?>
                        <tr>
                            <td><?php echo esc_html($serial_number); ?></td> <!-- Display Serial Number -->
                            <td><?php echo esc_html($customer_order->display_name); ?></td> <!-- Display Customer Name -->
                            <td><?php echo esc_html($customer_order->user_email); ?></td> <!-- Display Email -->
                            <td><?php echo esc_html($customer_order->phone_number); ?></td> <!-- Display Phone -->
                            <td><?php echo esc_html($customer_order->sap_customer_id); ?></td>
                            <td><?php echo esc_html($customer_order->shipping_address); ?></td>
                            <td><?php echo esc_html($customer_order->billing_address); ?></td>
                            
                            
                        </tr>
                        <?php $serial_number++; // Increment serial number ?>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php else : ?>
            <p>No active customers with orders found.</p>
        <?php endif; ?>
    </div>
    <style>
        #customerOrdersTable tr:nth-child(even) {
            background-color: #ffbd2b30 ;
        }
    </style>
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script>
        jQuery(document).ready(function($) {
            $('#customerOrdersTable').DataTable({
                "paging": true, // Enable DataTables pagination
                "searching": true,
                "ordering": true,
                "info": true
            });

            // for export active Users?
            function exportTableToCSV() {
                var csv = [];
                var table = document.querySelector("#customerOrdersTable");

                // Get visible table headers (column names)
                var headers = [];
                var headerCols = table.querySelectorAll("thead th");
                var visibleColumnsIndexes = []; // To track visible columns

                headerCols.forEach((header, index) => {
                    if (header.style.display !== "none") { // Check if the column is visible
                        headers.push(header.innerText.trim());
                        visibleColumnsIndexes.push(index); // Track visible column indexes
                    }
                });
                csv.push(headers.join(",")); // Push headers to CSV

                // Get table rows data
                var rows = table.querySelectorAll("tbody tr");
                rows.forEach((row) => {
                    var rowData = [];
                    var cols = row.querySelectorAll("td");
                    visibleColumnsIndexes.forEach((colIndex) => {
                        let cellText = cols[colIndex].innerText.trim();
                        
                        // Ensure addresses remain in one column
                        if (colIndex === headers.indexOf("Shipping Address") || colIndex === headers.indexOf("Billing Address")) {
                            cellText = '"' + cellText.replace(/,/g, ' ') + '"'; // Wrap in quotes and remove inner commas
                        }

                        rowData.push(cellText);
                    });
                    csv.push(rowData.join(",")); // Add the row data to CSV
                });

                // Create a timestamp for the filename
                var date = new Date();
                var timestamp = date.toISOString().slice(0, 10).split("-").reverse().join("-"); 
                var filename = `ActiveUsers__${timestamp}.csv`;

                // Trigger CSV download
                downloadCSV(csv.join("\n"), filename);
            }

            function downloadCSV(csvContent, filename) {
                var csvBlob = new Blob([csvContent], { type: "text/csv" });
                var csvURL = window.URL.createObjectURL(csvBlob);
                var downloadLink = document.createElement("a");

                downloadLink.href = csvURL;
                downloadLink.download = filename;
                downloadLink.style.display = "none";

                document.body.appendChild(downloadLink);
                downloadLink.click();
                document.body.removeChild(downloadLink);
            }

            // Attach event listener to the export button
            document.getElementById("exportbuttonusers").addEventListener("click", function() {
                exportTableToCSV();
            });

        });
    </script>
    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" />
    <?php
}