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


// Hook into WooCommerce Order Creation
add_action('woocommerce_thankyou', 'cwp_insert_payment_record', 10, 1);
function cwp_insert_payment_record($order_id) {
    if (!$order_id) {
        return;
    }
    
    $order = wc_get_order($order_id);
    $user_id = $order->get_user_id();
    
    global $wpdb;
    $table_name = $wpdb->prefix . 'payments';
    $invoices_table = $wpdb->prefix . 'order_invoice_details';

    $invoice_number = $wpdb->get_var($wpdb->prepare(
        "SELECT invoice_number FROM $invoices_table WHERE contract_id = %d ORDER BY id DESC LIMIT 1",
        $order_id
    )) ?: 0;

    $paid_amount = get_post_meta($order_id, '_payable_amount', true) ?: 0.00;
    $payment_mod = get_post_meta($order_id, '_payment_method', true) ?: '';
    $doc_payment_date = get_post_meta($order_id, 'payment_date', true) ?: current_time('Y-m-d');
    $doc_reference_num = get_post_meta($order_id, 'doc_reference_num', true) ?: '';
    $order_post = get_post($order_id);
    $payment_date = $order_post ? $order_post->post_date : current_time('mysql');
    $invoice_date = current_time('Y-m-d'); // Assuming current date if not provided
    $invoice_total = $order->get_total() ?: '0.00';

    $wpdb->insert(
        $table_name,
        [
            'order_id' => $order_id,
            'user_id' => $user_id ?: 0,
            'doc_payment_date' => $doc_payment_date,
            'payment_date' => $payment_date,
            'paid_amount' => $paid_amount,
            'payment_mod' => $payment_mod,
            'doc_reference_num' => $doc_reference_num,
            'invoice_date' => $invoice_date,
            'invoice_number' => $invoice_number,
            'invoice_total' => $invoice_total
        ],
        [
            '%d', '%d', '%s', '%s', '%f', '%s', '%s', '%s', '%d', '%s'
        ]
    );
}

// Sync meta updates
add_action('updated_post_meta', 'cwp_sync_meta_to_payments_table', 10, 4);
add_action('added_post_meta', 'cwp_sync_meta_to_payments_table', 10, 4);
function cwp_sync_meta_to_payments_table($meta_id, $post_id, $meta_key, $_meta_value) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'payments';

    if ($meta_key === 'payment_date') {
        $wpdb->update(
            $table_name,
            ['doc_payment_date' => $_meta_value],
            ['order_id' => $post_id],
            ['%s'],
            ['%d']
        );
    }

    if ($meta_key === 'doc_reference_num') {
        $wpdb->update(
            $table_name,
            ['doc_reference_num' => $_meta_value],
            ['order_id' => $post_id],
            ['%s'],
            ['%d']
        );
    }
}

// Fetch payments with filters
function cwp_get_payments($args = array()) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'payments';
    
    $defaults = array(
        'limit' => 20,
        'offset' => 0,
        'start_date' => '',
        'end_date' => '',
        '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'])
    );
}

// Admin page
function cwp_payments_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 = cwp_get_payments($query_args);
    $payments = $data->payments;
    $max_pages = $data->max_pages;

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

        <form method="get" class="wc-finance-filters">
            <input type="hidden" name="page" value="wc-payments">
            <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('cwp_payments_export', 'export_nonce'); ?>
            <input type="hidden" name="export_csv" value="1">
            <button type="submit" class="button button-primary" 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="payments-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 payment 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 = $user_id && $order ? trim($order->get_billing_first_name() . ' ' . $order->get_billing_last_name()) : 'Guest';
                        $sap_customer_id = $user_id ? get_user_meta($user_id, 'sap_customer_id', true) : '';
                        $phone = $order ? ($order->get_billing_phone() ?: 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($sap_customer_id); ?></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('#payments-finance').DataTable({
            "scrollX": true,
        });
        $('.datepicker').datepicker({
            dateFormat: 'yy-mm-dd',
            changeMonth: true,
            changeYear: true
        });
    });
    </script>
    <style>
         #payments-finance tbody tr:nth-child(even) {
        background-color: #ffbd2b30 ;
        }
    </style>
    <?php
}

// CSV Export
function cwp_payments_export_csv() {
    if (!isset($_POST['export_csv']) || !isset($_POST['export_nonce']) || 
        !wp_verify_nonce($_POST['export_nonce'], 'cwp_payments_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 = cwp_get_payments($query_args);
    $payments = $data->payments;

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="payments_' . 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 = $user_id && $order ? trim($order->get_billing_first_name() . ' ' . $order->get_billing_last_name()) : 'Guest';
        $sap_customer_id = $user_id ? get_user_meta($user_id, 'sap_customer_id', true) : '';
        $phone = $order ? ($order->get_billing_phone() ?: 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++,
            $sap_customer_id,
            $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;
}