<?php
if (!defined('ABSPATH')) {
    exit;
}

function wc_collections_get_payments($args = array()) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'payments';
    
    $defaults = array(
        'limit' => 20,
        'offset' => 0,
        'sap_id' => '',
        'contract_id' => ''
    );
    
    $args = wp_parse_args($args, $defaults);
    
    $query = "SELECT * FROM $table_name WHERE 1=1";
    $count_query = "SELECT COUNT(*) FROM $table_name WHERE 1=1";
    $params = array();
    
    if (!empty($args['start_date'])) {
        $query .= " AND payment_date >= %s";
        $count_query .= " AND payment_date >= %s";
        $params[] = $args['start_date'];
    }
    if (!empty($args['end_date'])) {
        $query .= " AND payment_date <= %s";
        $count_query .= " AND payment_date <= %s";
        $params[] = $args['end_date'];
    }
    
    if (!empty($args['sap_id'])) {
        $query .= " AND user_id IN (SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = 'sap_customer_id' AND meta_value = %s)";
        $count_query .= " AND user_id IN (SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = 'sap_customer_id' AND meta_value = %s)";
        $params[] = $args['sap_id'];
    }
    
    if (!empty($args['contract_id'])) {
        $query .= " AND order_id IN (SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = 'contractid' AND meta_value = %s)";
        $count_query .= " AND order_id IN (SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = 'contractid' AND meta_value = %s)";
        $params[] = $args['contract_id'];
    }
    
    $query .= " LIMIT %d OFFSET %d";
    $params[] = $args['limit'];
    $params[] = $args['offset'];
    
    $results = $wpdb->get_results($wpdb->prepare($query, $params));
    $total = $wpdb->get_var($wpdb->prepare($count_query, array_slice($params, 0, -2)));
    
    return (object) array(
        'payments' => $results,
        'total' => $total,
        'max_pages' => ceil($total / $args['limit'])
    );
}

function wc_collections_admin_page() {
    if (!current_user_can('manage_woocommerce')) {
        wp_die(__('You do not have sufficient permissions to access this page.', 'wc-finance'));
    }

    $current_page = max(1, isset($_GET['paged']) ? absint($_GET['paged']) : 1);
    $per_page = 20;

    $query_args = array(
        'limit' => $per_page,
        'offset' => ($current_page - 1) * $per_page,
        'start_date' => !empty($_GET['start_date']) ? sanitize_text_field($_GET['start_date']) : '',
        'end_date' => !empty($_GET['end_date']) ? sanitize_text_field($_GET['end_date']) : '',
        'sap_id' => !empty($_GET['sap_id']) ? sanitize_text_field($_GET['sap_id']) : '',
        'contract_id' => !empty($_GET['contract_id']) ? sanitize_text_field($_GET['contract_id']) : ''
    );

    $data = wc_collections_get_payments($query_args);
    $payments = $data->payments;
    $max_pages = $data->max_pages;

    ?>
    <div class="wrap">
        <h1><?php _e('Collections', 'wc-finance'); ?></h1>

        <form method="get" class="wc-finance-filters">
            <input type="hidden" name="page" value="wc-collections">
            <div class="filter-row">
                <label><?php _e('Date Range:', 'wc-finance'); ?></label>
                <input type="text" class="datepicker" name="start_date" value="<?php echo esc_attr($_GET['start_date'] ?? ''); ?>" placeholder="Start Date">
                <input type="text" class="datepicker" name="end_date" value="<?php echo esc_attr($_GET['end_date'] ?? ''); ?>" placeholder="End Date">
                
                <label><?php _e('SAP ID:', 'wc-finance'); ?></label>
                <input type="text" name="sap_id" value="<?php echo esc_attr($_GET['sap_id'] ?? ''); ?>" placeholder="Customer SAP ID">
                
                <label><?php _e('Contract ID:', 'wc-finance'); ?></label>
                <input type="text" name="contract_id" value="<?php echo esc_attr($_GET['contract_id'] ?? ''); ?>" placeholder="SAP Contract ID">
                
                <button type="submit" class="button button-primary"><?php _e('Filter', 'wc-finance'); ?></button>
            </div>
        </form>

        <form method="post" class="export-form">
            <?php wp_nonce_field('wc_collections_export', 'export_nonce'); ?>
            <input type="hidden" name="export_csv" value="1">
            <button type="submit" class="button" style="background-color: #000;color:#FFBD2B; border-radius:5px;"><?php _e('Export to CSV', 'wc-finance'); ?></button>
        </form>

        <table class="wp-list-table widefat fixed striped" id="collection-finance" style="margin-top:10px;">
            <thead>
                <tr>
                    <th><?php _e('S.No', 'wc-finance'); ?></th>
                    <th><?php _e('Customer SAP ID', 'wc-finance'); ?></th>
                    <th><?php _e('Customer Name', 'wc-finance'); ?></th>
                    <th><?php _e('Phone Number', 'wc-finance'); ?></th>
                    <th><?php _e('RMS Contract ID', 'wc-finance'); ?></th>
                    <th><?php _e('SAP Contract ID', 'wc-finance'); ?></th>
                    <th><?php _e('Total Paid', 'wc-finance'); ?></th>
                    <th><?php _e('Balance', 'wc-finance'); ?></th>
                    <th><?php _e('Date', 'wc-finance'); ?></th>
                    <th><?php _e('Payment Mode', 'wc-finance'); ?></th>
                </tr>
            </thead>
            <tbody>
                <?php
                if (empty($payments)) {
                    echo '<tr><td colspan="10">' . __('No collection records found.', 'wc-finance') . '</td></tr>';
                } else {
                    $count = ($current_page - 1) * $per_page + 1;
                    foreach ($payments as $payment) {
                        $user_id = $payment->user_id;
                        $order = wc_get_order($payment->order_id);
                        $customer_name = $order ? trim($order->get_billing_first_name() . ' ' . $order->get_billing_last_name()) : 'N/A';
                        $phone = $order ? ($order->get_billing_phone() ?: get_user_meta($user_id, 'user_phone', true)) : get_user_meta($user_id, 'user_phone', true);
                        $contract_id = $order ? $order->get_meta('contractid') : '';
                        $balance = $order ? $order->get_meta('_balanceamount') : '0.00';
                        ?>
                        <tr>
                            <td><?php echo $count++; ?></td>
                            <td><?php echo esc_html(get_user_meta($user_id, 'sap_customer_id', true)); ?></td>
                            <td><?php echo esc_html($customer_name); ?></td>
                            <td><?php echo esc_html($phone); ?></td>
                            <td><?php echo esc_html($payment->order_id); ?></td>
                            <td><?php echo esc_html($contract_id); ?></td>
                            <td><?php echo wc_price($payment->paid_amount); ?></td>
                            <td><?php echo wc_price($balance); ?></td>
                            <td><?php echo esc_html(date_i18n(get_option('date_format'), strtotime($payment->payment_date))); ?></td>
                            <td><?php echo esc_html($payment->payment_mod); ?></td>
                        </tr>
                        <?php
                    }
                }
                ?>
            </tbody>
        </table>

        <?php
        echo paginate_links(array(
            'base' => add_query_arg('paged', '%#%'),
            'format' => '',
            'prev_text' => __('« Previous'),
            'next_text' => __('Next »'),
            'total' => $max_pages,
            'current' => $current_page,
            'add_args' => array(
                'start_date' => $_GET['start_date'] ?? '',
                'end_date' => $_GET['end_date'] ?? '',
                'sap_id' => $_GET['sap_id'] ?? '',
                'contract_id' => $_GET['contract_id'] ?? ''
            )
        ));
        ?>
    </div>

    <script>
    jQuery(document).ready(function($) {
        var dataTable = jQuery('#collection-finance').DataTable({
            "scrollX": true,
        });
        $('.datepicker').datepicker({
            dateFormat: 'yy-mm-dd',
            changeMonth: true,
            changeYear: true
        });
    });
    </script>
    <style>
        #collection-finance tbody tr:nth-child(even) {
        background-color: #ffbd2b30 ;
        }
    </style>
    <?php
}

function wc_collections_export_csv() {
    if (!isset($_POST['export_csv']) || !isset($_POST['export_nonce']) || 
        !wp_verify_nonce($_POST['export_nonce'], 'wc_collections_export') ||
        !current_user_can('manage_woocommerce')) {
        return;
    }

    ob_start();

    $query_args = array(
        'limit' => -1,
        'offset' => 0,
        'start_date' => !empty($_GET['start_date']) ? sanitize_text_field($_GET['start_date']) : '',
        'end_date' => !empty($_GET['end_date']) ? sanitize_text_field($_GET['end_date']) : '',
        'sap_id' => !empty($_GET['sap_id']) ? sanitize_text_field($_GET['sap_id']) : '',
        'contract_id' => !empty($_GET['contract_id']) ? sanitize_text_field($_GET['contract_id']) : ''
    );

    $data = wc_collections_get_payments($query_args);
    $payments = $data->payments;

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="collections_' . date('Y-m-d_H-i-s') . '.csv"');
    header('Cache-Control: no-cache, no-store, must-revalidate');
    header('Pragma: no-cache');
    header('Expires: 0');

    ob_end_clean();

    $output = fopen('php://output', 'w');
    
    fputcsv($output, array(
        'S.No',
        'Customer SAP ID',
        'Customer Name',
        'Phone Number',
        'RMS Contract ID',
        'SAP Contract ID',
        'Total Paid',
        'Balance',
        'Date',
        'Payment Mode'
    ));

    $count = 1;
    foreach ($payments as $payment) {
        $user_id = $payment->user_id;
        $order = wc_get_order($payment->order_id);
        $customer_name = $order ? trim($order->get_billing_first_name() . ' ' . $order->get_billing_last_name()) : 'N/A';
        $phone = $order ? ($order->get_billing_phone() ?: get_user_meta($user_id, 'user_phone', true)) : get_user_meta($user_id, 'user_phone', true);
        $contract_id = $order ? $order->get_meta('contractid') : '';
        $balance = $order ? $order->get_meta('_balanceamount') : '0.00';
        
        fputcsv($output, array(
            $count++,
            get_user_meta($user_id, 'sap_customer_id', true),
            $customer_name,
            $phone,
            $payment->order_id,
            $contract_id,
            wc_format_decimal($payment->paid_amount, 2),
            wc_format_decimal($balance, 2),
            date('Y-m-d', strtotime($payment->payment_date)),
            $payment->payment_mod
        ));
    }

    fclose($output);
    exit;
}