<?php
if (!defined('ABSPATH'))
exit; // Exit if accessed directly
class DevsAccounting_Transactions{
    public $tr_table;
    
    public function __construct()
    {
        global $wpdb;
        $this->tr_table= $wpdb->prefix . 'dac_transactions';
        add_action('rest_api_init', [$this, 'transaction_api_endpoint']);
    }

    // Define your custom endpoint
    public function transaction_api_endpoint() {
        register_rest_route('devs-accounting/v1', '/add-transaction', array(
            'methods' => 'POST', // Change to POST method to handle data insertion
            'callback' => [$this, 'save_transaction_data'],
            'permission_callback' => [$this, 'verify_transactions_nonce'],
        ));

        register_rest_route('devs-accounting/v1', '/transactions', array(
            'methods' => 'get', // Change to POST method to handle data insertion
            'callback' => [$this, 'get_transaction'],
            'permission_callback' => function () {
                return true;
            },
        ));

        register_rest_route('devs-accounting/v1', '/transaction-type', [
            'methods'  => 'GET',
            'callback' => [$this, 'get_transaction_by_type'],
            'permission_callback' => function () {
                return true;
            },
        ]);

        register_rest_route('devs-accounting/v1', '/get-reports', array(
            'methods' => 'get', 
            'callback' => [$this, 'get_all_incomes'],
            'permission_callback' => function () {
                return true;
            },
        ));

        register_rest_route('devs-accounting/v1', '/get-transaction/(?P<id>\d+)', array(
            'methods' => 'get',
            'callback' => [$this, 'get_single_transaction'],
            'permission_callback' => function () {
                return true;
            },
        ));
        register_rest_route('devs-accounting/v1', '/get-transaction-invoice/(?P<id>\d+)', array(
            'methods' => 'get',
            'callback' => [$this, 'get_single_transaction_invoice'],
            'permission_callback' => function () {
                return true;
            },
        ));
        
        register_rest_route('devs-accounting/v1', '/delete-transaction/(?P<id>\d+)', array(
            'methods' => 'get', 
            'callback' => [$this, 'delete_single_transaction'],
            'permission_callback' => [$this, 'verify_transactions_nonce'],
        ));

        register_rest_route('devs-accounting/v1', '/import-transactions', [
            'methods' => 'POST',
            'callback' => [$this, 'import_transactions'],
            'permission_callback' => [$this, 'verify_transactions_nonce'],
        ]);

        register_rest_route('devs-accounting/v1', '/export-transactions', [
            'methods' => 'GET',
            'callback' => [$this, 'export_transactions'],
            'permission_callback' => [$this, 'verify_transactions_nonce'],
        ]);
        
        register_rest_route('devs-accounting/v1', '/sample-transactions', [
            'methods' => 'GET',
            'callback' => [$this, 'download_sample_transaction_file'],
            'permission_callback' => [$this, 'verify_transactions_nonce'],
        ]);
    }

    function verify_transactions_nonce($request) {
        $nonce = $request->get_header('X-WP-Nonce');
        if (!wp_verify_nonce($nonce, 'wp_rest')) {
            return new WP_Error('rest_forbidden', esc_html__('Invalid nonce.', 'devs-accounting'), array('status' => 403));
        }
        return true;
    }

    public function get_transaction() {
        global $wpdb;

        $table_name_transactions = $this->tr_table;
        $table_name_categories = $wpdb->prefix . 'dac_categories';
        $table_name_contacts = $wpdb->prefix . 'dac_contacts';

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT t.*, c.name AS cat_name, ct.name as contact_name
             FROM $table_name_transactions AS t 
             LEFT JOIN $table_name_categories AS c ON t.category_id = c.id 
             LEFT JOIN $table_name_contacts AS ct ON t.contact_id = ct.id
             WHERE t.deleted_at IS NULL
             ORDER BY t.paid_at DESC"
        ));
    
        foreach ($results as &$result) {
            if (!empty($result->data)) {
                $result->data = unserialize($result->data);
            }
        }
    
        // Return a REST API response
        return rest_ensure_response($results);
    }
    
    public function get_transaction_by_type($request) {
        global $wpdb;
    
        $type = isset($request['type']) ? sanitize_text_field($request['type']) : '';
    
        if (empty($type)) {
            return new WP_Error('missing_type', 'Transaction type is required.', ['status' => 400]);
        }
    
        $table_name_transactions = $this->tr_table;
        $table_name_categories = $wpdb->prefix . 'dac_categories';
        $table_name_contacts = $wpdb->prefix . 'dac_contacts';
    
        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT t.*, c.name AS cat_name, ct.name as contact_name
             FROM $table_name_transactions AS t 
             LEFT JOIN $table_name_categories AS c ON t.category_id = c.id 
             LEFT JOIN $table_name_contacts AS ct ON t.contact_id = ct.id
             WHERE t.deleted_at IS NULL AND t.type = %s
             ORDER BY t.paid_at DESC",
             $type
        ));
    
        foreach ($results as &$result) {
            if (!empty($result->data)) {
                $result->data = unserialize($result->data);
            }
        }
    
        return rest_ensure_response($results);
    }
    
    

    public function get_all_incomes() {
        global $wpdb;
        $transactions = $this->get_transaction();
        $transactions_data = $transactions->get_data(); 
        $settings = get_option('devsaccounting_settings');
        $settings = json_decode($settings, true);
    
        if ($settings && isset($settings['ordersync']) && $settings['ordersync'] == 1 && class_exists('DevsAccounting_Wc')) { 
            $orders = (new DevsAccounting_Wc)->get_wc_all_orders();
            $filtered_orders = array_filter($orders, function($order) {
                return in_array($order['status'], ['processing', 'completed']);
            });
    
            foreach ($filtered_orders as &$order) {
                $order['paid_at'] = $order['created_at'];
                $order['cat_name'] = 'WooCommerce Orders';
                $order['type'] = 'income';
            }
            $allIncomes = array_merge($transactions_data, $filtered_orders);
        } else {
            $allIncomes = $transactions_data;
        }
    
        return rest_ensure_response($allIncomes);
    }
     
    public function get_single_transaction($request) {
        global $wpdb;
    
        $table_name = $this->tr_table;
    
        $transaction_id = $request['id'];

        $transaction = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table_name WHERE ID = %d",
            $transaction_id
        ));
    
        if ($transaction) {
            if (!empty($transaction->data)) {
                $transaction->data = unserialize($transaction->data);
            }
    
            return rest_ensure_response($transaction);
        } else {
            return new WP_Error('transaction_not_found', esc_html__('Transaction not found', 'devs-accounting'), array('status' => 404));
        }
    }
    

    public function get_single_transaction_invoice($request) {
        global $wpdb;
    
        $table_name = $this->tr_table;
    
        // Retrieve the document ID from the request
        $transaction_id = $request['id'];
      
        // Fetch all matching transactions
        $transactions = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM $table_name WHERE document_id = %d AND deleted_at IS NULL",
            $transaction_id 
        ));
    
        // Check if any transactions were found
        if ($transactions) {
            return rest_ensure_response($transactions);
        } else {
            return new WP_Error('transactions_not_found', esc_html__('No transactions found!!', 'devs-accounting'), array('status' => 404));
        }
    }
    
    public function delete_single_transaction($request) {
        global $wpdb;

        $table_name = $this->tr_table;

        // Retrieve the task ID from the request
        $transaction_id = isset($request['id']) ? intval($request['id']) : 0;

        // Validate item ID
        if ($transaction_id <= 0) {
            return new WP_Error( 'invalid_item_id', esc_html__( 'Invalid item ID', 'devs-accounting' ), array( 'status' => 400 ) );
        }

        // Check if the item exists
        $existing_transaction = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM $table_name WHERE ID = %d",
                $transaction_id
            )
        );

        if (!$existing_transaction) {
            return new WP_Error( 'item_not_found', esc_html__( 'Transaction not found', 'devs-accounting' ), array( 'status' => 404 ) );
        }

        // Get current time
        $current_time = gmdate('Y-m-d H:i:s'); // 'mysql' format returns time in MySQL datetime format

        // Soft delete the item by updating its status
        $result = $wpdb->update(
            $table_name,
            array( 
                'deleted_at' => $current_time,
                ),
            array( 'id' => $transaction_id ),
        );
    
        // Check if update was successful
        if ($result === false) {
            return new WP_Error( 'soft_delete_failed', esc_html__( 'Failed to soft delete item', 'devs-accounting' ), array( 'status' => 500 ) );
        }

        // Return success response
        return rest_ensure_response( array( 'message' => esc_html__('Transaction soft deleted successfully', 'devs-accounting') ) );
    }

    public function save_transaction_data($request) {
        global $wpdb;
        $table_name = $this->tr_table;
        $errors = array();
    
        $paid_at = gmdate('Y-m-d H:i:s', strtotime($request['paid_at']));
        if (!$paid_at) {
            $errors['paid_at'] = esc_html__('Invalid date format for paid_at.' , 'devs-accounting');
        }
    
        if (empty($request['amount']) || $request['amount'] < 0) {
            $errors['amount'] = esc_html__('Amount must be a non-negative number.' , 'devs-accounting');
        } else {
            $amount = floatval($request['amount']);
        }
    
        if (empty($request['type'])) {
            $errors['type'] = esc_html__('Type is required.' , 'devs-accounting');
        } else {
            $type = sanitize_text_field($request['type']);
        }
    
        $description = sanitize_textarea_field($request['description']);
        $category_id = intval($request['category_id']);
        $account_id = intval($request['account_id']);
        $contact_id = intval($request['contact_id']);
        $document_id = intval($request['document_id']);
        $payment_method = sanitize_text_field($request['payment_method']);
        $transaction_id = isset($request['transaction_id']) && !empty($request['transaction_id']) ? sanitize_text_field($request['transaction_id']) : null;
        $data = isset($request['data']) ? serialize($request['data']) : null;
    
        if (!empty($errors)) {
            return new WP_REST_Response(array('errors' => $errors), 400); 
        }
  
        $insert_data = array(
            'paid_at' => $paid_at,
            'amount' => $amount,
            'type' => $type,
            'description' => $description,
            'category_id' => $category_id,
            'account_id' => $account_id,
            'contact_id' => $contact_id,
            'payment_method' => $payment_method,
            'document_id' => $document_id,
            'transaction_id' => $transaction_id,
            'data' => $data 
        );
    
        if (!empty($request['id'])) {
            $where = array('id' => intval($request['id']));
            $updated = $wpdb->update($table_name, $insert_data, $where);
            if ($updated !== false) {
                $response = array(
                    'message' => esc_html__('Transaction updated successfully!' , 'devs-accounting'),
                    'data' => $updated,
                );
            } else {
                $response = array(
                    'message' => esc_html__('Update transaction failed', 'devs-accounting'),
                );
            }
        } else {
            $inserted = $wpdb->insert($table_name, $insert_data);
            if (!empty($document_id)) {
                $invoice_obj = new DevsAccounting_Invoice();
                $invoice_obj->update_invoice_data(['id' => $document_id]);
            }
            if ($inserted) {
                $response = array(
                    'message' => esc_html__('Transaction created successfully!', 'devs-accounting'),
                    'data' => $wpdb->insert_id, 
                );
            } else {
                $response = array(
                    'message' => esc_html__('Create transaction failed', 'devs-accounting'),
                    "error" => $wpdb->last_error
                );
            }
        }
    
        return new WP_REST_Response($response, !empty($errors) ? 400 : 200);
    }
    

    public function get_invoice_total_paid($invoice_id)
    {
        global $wpdb;
        $transaction_table = $this->tr_table;
        $total_paid = $wpdb->get_var($wpdb->prepare("SELECT SUM(amount) FROM $transaction_table WHERE document_id = %d", $invoice_id));
        return $total_paid;
    }
    
    // public function import_transactions(WP_REST_Request $request)
    // {
    //     global $wpdb;
    //     $table_name       = $wpdb->prefix . 'dac_transactions';
    //     $contacts_table   = $wpdb->prefix . 'dac_contacts';
    //     $categories_table = $wpdb->prefix . 'dac_categories';
    //     $accounts_table   = $wpdb->prefix . 'dac_accounts';
    //     $invoices_table   = $wpdb->prefix . 'dac_invoices';

    //     if (empty($_FILES['file'])) {
    //         return new WP_REST_Response([
    //             'success' => false,
    //             'message' => 'No file uploaded.',
    //         ], 400);
    //     }

    //     $file = $_FILES['file'];

    //     if ($file['error'] !== UPLOAD_ERR_OK) {
    //         return new WP_REST_Response([
    //             'success' => false,
    //             'message' => 'File upload error.',
    //         ], 400);
    //     }

    //     $rows = [];
    //     $header = [];

    //     if (($handle = fopen($file['tmp_name'], 'r')) !== false) {
    //         do {
    //             $header = fgetcsv($handle, 10000, ",");
    //         } while ($header && count(array_filter($header)) === 0);

    //         if (isset($header[0])) {
    //             $header[0] = preg_replace('/^\xEF\xBB\xBF/', '', $header[0]);
    //         }

    //         if (!is_array($header) || count($header) < 2) {
    //             return new WP_REST_Response([
    //                 'success' => false,
    //                 'message' => 'Invalid CSV header.',
    //             ], 400);
    //         }

    //         while (($data = fgetcsv($handle, 10000, ",")) !== false) {
    //             if (!is_array($data) || count(array_filter($data)) === 0) {
    //                 continue;
    //             }
    //             if (count($data) === count($header)) {
    //                 $rows[] = array_combine($header, $data);
    //             }
    //         }

    //         fclose($handle);
    //     }

    //     if (empty($rows)) {
    //         return new WP_REST_Response([
    //             'success' => false,
    //             'message' => 'Empty or invalid CSV file.',
    //         ], 400);
    //     }

    //     if (($idIndex = array_search('id', $header)) !== false) {
    //         unset($header[$idIndex]);
    //     }

    //     $imported = 0;
    //     $skipped_rows = [];

    //     foreach ($rows as $data) {
    //         unset($data['id']);
    //         $contact_id = null;
    //         $account_id = null;
    //         $category_id = null;
    //         $document_id = null;

    //         if (!empty($data['contact_id'])) {
    //             $contact_id_input = sanitize_text_field($data['contact_id']);
    //             if (is_numeric($contact_id_input)) {
    //                 $contact_id = $wpdb->get_var($wpdb->prepare(
    //                     "SELECT id FROM $contacts_table WHERE id = %d", $contact_id_input
    //                 ));
    //                 if (!$contact_id) {
    //                     $skipped_rows[] = array_merge($data, ['_reason' => 'Contact ID not found']);
    //                     continue;
    //                 }
    //             } else {
    //                 $skipped_rows[] = array_merge($data, ['_reason' => 'Invalid contact_id format']);
    //                 continue;
    //             }
    //         }

    //         if (!empty($data['account_name'])) {
    //             $account_name = sanitize_text_field($data['account_name']);
    //             $account_id = $wpdb->get_var($wpdb->prepare(
    //                 "SELECT id FROM $accounts_table WHERE name = %s", $account_name
    //             ));
    //             if (!$account_id) {
    //                 $skipped_rows[] = array_merge($data, ['_reason' => 'Account name not found']);
    //                 continue;
    //             }
    //         } else {
    //             $skipped_rows[] = array_merge($data, ['_reason' => 'Missing account_name']);
    //             continue;
    //         }

    //         if (!empty($data['category_name'])) {
    //             $category_name = sanitize_text_field($data['category_name']);
    //             $category_id = $wpdb->get_var($wpdb->prepare(
    //                 "SELECT id FROM $categories_table WHERE name = %s", $category_name
    //             ));
    //             if (!$category_id) {
    //                 $skipped_rows[] = array_merge($data, ['_reason' => 'Category name not found']);
    //                 continue;
    //             }
    //         }

    //         if (!empty($data['document_id'])) {
    //             $invoice_no = sanitize_text_field($data['document_id']);
    //             $document_id = $wpdb->get_var($wpdb->prepare(
    //                 "SELECT id FROM $invoices_table WHERE BINARY TRIM(invoice_no) = %s", $invoice_no
    //             ));
    //             if (!$document_id) {
    //                 $skipped_rows[] = array_merge($data, ['_reason' => 'Invoice number not found']);
    //                 continue;
    //             }
    //         }

    //         $type   = !empty($data['type']) ? sanitize_text_field($data['type']) : null;
    //         $amount = isset($data['amount']) && $data['amount'] !== '' ? floatval($data['amount']) : null;

    //         if (!$type || $amount === null || !$account_id) {
    //             $skipped_rows[] = array_merge($data, ['_reason' => 'Missing required fields (type, amount, account_id)']);
    //             continue;
    //         }

    //         $transaction_data = [
    //             'transaction_id' => !empty($data['transaction_id']) ? sanitize_text_field($data['transaction_id']) : null,
    //             'type'           => $type,
    //             'paid_at'        => !empty($data['paid_at']) ? sanitize_text_field($data['paid_at']) : null,
    //             'amount'         => $amount,
    //             'currency_code'  => !empty($data['currency_code']) ? sanitize_text_field($data['currency_code']) : null,
    //             'currency_rate'  => isset($data['currency_rate']) && $data['currency_rate'] !== '' ? floatval($data['currency_rate']) : null,
    //             'account_id'     => $account_id,
    //             'document_id'    => $document_id,
    //             'contact_id'     => $contact_id,
    //             'category_id'    => $category_id,
    //             'description'    => !empty($data['description']) ? sanitize_text_field($data['description']) : null,
    //             'payment_method' => !empty($data['payment_method']) ? sanitize_text_field($data['payment_method']) : null,
    //         ];

    //         $inserted = $wpdb->insert($table_name, $transaction_data);

    //         if ($inserted) {
    //             $imported++;
    //         }
    //     }

    //     if ($imported > 0) {
    //         return new WP_REST_Response([
    //             'success' => true,
    //             'message' => "$imported transaction(s) imported successfully.",
    //             'skipped' => count($skipped_rows),
    //             'skipped_rows' => $skipped_rows, 
    //         ]);
    //     }else {
    //         return new WP_REST_Response([
    //             'success' => false,
    //             'message' => 'No transactions were imported.',
    //         ], 400);
    //     }
    // }

    public function import_transactions(WP_REST_Request $request)
    {
        global $wpdb;
        $table_name       = $wpdb->prefix . 'dac_transactions';
        $contacts_table   = $wpdb->prefix . 'dac_contacts';
        $categories_table = $wpdb->prefix . 'dac_categories';
        $accounts_table   = $wpdb->prefix . 'dac_accounts';
        $invoices_table   = $wpdb->prefix . 'dac_invoices';

        if (empty($_FILES['file'])) {
            return new WP_REST_Response([
                'success' => false,
                'message' => 'No file uploaded.',
            ], 400);
        }

        $file = $_FILES['file'];

        if ($file['error'] !== UPLOAD_ERR_OK) {
            return new WP_REST_Response([
                'success' => false,
                'message' => 'File upload error.',
            ], 400);
        }

        $rows = [];
        $header = [];

        if (($handle = fopen($file['tmp_name'], 'r')) !== false) {
            do {
                $header = fgetcsv($handle, 10000, ",");
            } while ($header && count(array_filter($header)) === 0);

            if (isset($header[0])) {
                $header[0] = preg_replace('/^\xEF\xBB\xBF/', '', $header[0]);
            }

            if (!is_array($header) || count($header) < 2) {
                return new WP_REST_Response([
                    'success' => false,
                    'message' => 'Invalid CSV header.',
                ], 400);
            }

            while (($data = fgetcsv($handle, 10000, ",")) !== false) {
                if (!is_array($data) || count(array_filter($data)) === 0) {
                    continue;
                }
                if (count($data) === count($header)) {
                    $rows[] = array_combine($header, $data);
                }
            }

            fclose($handle);
        }

        if (empty($rows)) {
            return new WP_REST_Response([
                'success' => false,
                'message' => 'Empty or invalid CSV file.',
            ], 400);
        }

        if (($idIndex = array_search('id', $header)) !== false) {
            unset($header[$idIndex]);
        }

        $imported = 0;
        $skipped_rows = [];

        foreach ($rows as $data) {
            unset($data['id']);
            $contact_id = null;
            $account_id = null;
            $category_id = null;
            $document_id = null;

            if (!empty($data['contact_id'])) {
                $contact_id_input = sanitize_text_field($data['contact_id']);
                if (is_numeric($contact_id_input)) {
                    $contact_id = $wpdb->get_var($wpdb->prepare(
                        "SELECT id FROM $contacts_table WHERE id = %d", $contact_id_input
                    ));
                    if (!$contact_id) {
                        $skipped_rows[] = array_merge($data, ['_reason' => 'Contact ID not found']);
                        continue;
                    }
                } else {
                    $skipped_rows[] = array_merge($data, ['_reason' => 'Invalid contact_id format']);
                    continue;
                }
            }

            if (!empty($data['account_name'])) {
                $account_name = sanitize_text_field($data['account_name']);
                $account_id = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM $accounts_table WHERE name = %s", $account_name
                ));
                if (!$account_id) {
                    $skipped_rows[] = array_merge($data, ['_reason' => 'Account name not found']);
                    continue;
                }
            } else {
                $skipped_rows[] = array_merge($data, ['_reason' => 'Missing account_name']);
                continue;
            }

            if (!empty($data['category_name'])) {
                $category_name = sanitize_text_field($data['category_name']);
                $category_id = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM $categories_table WHERE name = %s", $category_name
                ));
                if (!$category_id) {
                    $skipped_rows[] = array_merge($data, ['_reason' => 'Category name not found']);
                    continue;
                }
            }

            if (!empty($data['document_id'])) {
                $invoice_no = sanitize_text_field($data['document_id']);
                $document_id = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM $invoices_table WHERE BINARY TRIM(invoice_no) = %s", $invoice_no
                ));
                if (!$document_id) {
                    $skipped_rows[] = array_merge($data, ['_reason' => 'Invoice number not found']);
                    continue;
                }
            }

            $type   = !empty($data['type']) ? sanitize_text_field($data['type']) : null;
            $amount = isset($data['amount']) && $data['amount'] !== '' ? floatval($data['amount']) : null;

            if (!$type || $amount === null || !$account_id) {
                $skipped_rows[] = array_merge($data, ['_reason' => 'Missing required fields (type, amount, account_id)']);
                continue;
            }

            // Set `paid_at` value
            $paid_at = !empty($data['paid_at']) ? sanitize_text_field($data['paid_at']) : '0000/00/00';

            // Validate paid_at format if provided
            if ($paid_at !== '0000/00/00') {
                $date_regex = '/^\d{4}\/\d{2}\/\d{2} \d{2}:\d{2}:\d{2}$/'; // Matches yyyy/mm/dd hh:mm:ss format

                if (!preg_match($date_regex, $paid_at)) {
                    return new WP_REST_Response([
                        'success' => false,
                        'message' => 'Invalid paid_at format, expected yyyy/mm/dd hh:mm:ss.',
                        'invalid_row' => $data, // Optional, can include the row data for more detailed info
                    ], 400);
                }
            }

            $transaction_data = [
                'transaction_id' => !empty($data['transaction_id']) ? sanitize_text_field($data['transaction_id']) : null,
                'type'           => $type,
                'paid_at'        => $paid_at,
                'amount'         => $amount,
                'currency_code'  => !empty($data['currency_code']) ? sanitize_text_field($data['currency_code']) : null,
                'currency_rate'  => isset($data['currency_rate']) && $data['currency_rate'] !== '' ? floatval($data['currency_rate']) : null,
                'account_id'     => $account_id,
                'document_id'    => $document_id,
                'contact_id'     => $contact_id,
                'category_id'    => $category_id,
                'description'    => !empty($data['description']) ? sanitize_text_field($data['description']) : null,
                'payment_method' => !empty($data['payment_method']) ? sanitize_text_field($data['payment_method']) : null,
            ];

            $inserted = $wpdb->insert($table_name, $transaction_data);

            if ($inserted) {
                $imported++;
            }
        }

        if ($imported > 0) {
            return new WP_REST_Response([
                'success' => true,
                'message' => "$imported transaction(s) imported successfully.",
                'skipped' => count($skipped_rows),
                'skipped_rows' => $skipped_rows, 
            ]);
        } else {
            return new WP_REST_Response([
                'success' => false,
                'message' => 'No transactions were imported.',
            ], 400);
        }
    }


    
    public function export_transactions($request)
    {
        global $wpdb;
        $transactions_table = $wpdb->prefix . 'dac_transactions';
        $contacts_table     = $wpdb->prefix . 'dac_contacts';
        $categories_table   = $wpdb->prefix . 'dac_categories';
        $accounts_table     = $wpdb->prefix . 'dac_accounts';
        $invoices_table     = $wpdb->prefix . 'dac_invoices'; 
    
        $results = $wpdb->get_results(
            "SELECT t.type, t.paid_at, t.amount, t.currency_code, t.currency_rate, t.description, t.payment_method, t.transaction_id, t.contact_id AS contact_ref, 
                    t.category_id AS category_ref, t.account_id AS account_ref, t.document_id
             FROM $transactions_table t
             WHERE t.deleted_at IS NULL",
            ARRAY_A
        );
    
        if (empty($results)) {
            return new WP_Error('no_transactions', __('No transactions found for export.', 'devs-accounting'), ['status' => 404]);
        }
    
        $contact_ids = array_unique(array_column($results, 'contact_ref'));
        $contact_emails = [];
        if (!empty($contact_ids)) {
            $placeholders = implode(',', array_fill(0, count($contact_ids), '%d'));
            $query = $wpdb->prepare(
                "SELECT id, email FROM $contacts_table WHERE id IN ($placeholders)",
                ...$contact_ids
            );
            $contacts = $wpdb->get_results($query, ARRAY_A);
            foreach ($contacts as $contact) {
                $contact_emails[$contact['id']] = $contact['email'];
            }
        }
    
        $category_ids = array_unique(array_column($results, 'category_ref'));
        $category_names = [];
        if (!empty($category_ids)) {
            $placeholders = implode(',', array_fill(0, count($category_ids), '%d'));
            $query = $wpdb->prepare(
                "SELECT id, name FROM $categories_table WHERE id IN ($placeholders)",
                ...$category_ids
            );
            $categories = $wpdb->get_results($query, ARRAY_A);
            foreach ($categories as $cat) {
                $category_names[$cat['id']] = $cat['name'];
            }
        }
    
        $account_ids = array_unique(array_column($results, 'account_ref'));
        $account_names = [];
        if (!empty($account_ids)) {
            $placeholders = implode(',', array_fill(0, count($account_ids), '%d'));
            $query = $wpdb->prepare(
                "SELECT id, name FROM $accounts_table WHERE id IN ($placeholders)",
                ...$account_ids
            );
            $accounts = $wpdb->get_results($query, ARRAY_A);
            foreach ($accounts as $acc) {
                $account_names[$acc['id']] = $acc['name'];
            }
        }
    
        // Fetch invoice details based on document_id
        $document_ids = array_unique(array_column($results, 'document_id'));
        $invoice_ids = [];
        if (!empty($document_ids)) {
            $placeholders = implode(',', array_fill(0, count($document_ids), '%d'));
            $query = $wpdb->prepare(
                "SELECT id, invoice_no FROM $invoices_table WHERE id IN ($placeholders)",
                ...$document_ids
            );
            $invoices = $wpdb->get_results($query, ARRAY_A);
            foreach ($invoices as $invoice) {
                $invoice_ids[$invoice['id']] = $invoice['invoice_no'];
            }
        }
    
        foreach ($results as &$row) {
            $row['contact_email']   = $contact_emails[$row['contact_ref']] ?? '';
            $row['category_name']   = $category_names[$row['category_ref']] ?? '';
            $row['account_name']    = $account_names[$row['account_ref']] ?? '';
            $row['invoice_no']      = $invoice_ids[$row['document_id']] ?? ''; 
            unset($row['contact_ref'], $row['category_ref'], $row['account_ref'], $row['document_id']);
        }
    
        $header = array_keys($results[0]);
        $header = array_filter($header, fn($key) => !empty($key));
    
        $invoice_index = array_search('invoice_no', $header);
        if ($invoice_index !== false) {
            unset($header[$invoice_index]);
            $header[] = 'invoice_no'; 
        }

        $filename = 'transactions_export_' . date('Ymd_His') . '.csv';
        header('Content-Type: text/csv; charset=utf-8');
        header("Content-Disposition: attachment; filename={$filename}");
    
        $output = fopen('php://output', 'w');
        fwrite($output, chr(239) . chr(187) . chr(191));  
        fputcsv($output, $header);

        foreach ($results as $row) {
            $row_data = [];
            foreach ($header as $col) {
                $row_data[$col] = $row[$col] ?? '';
            }
            fputcsv($output, $row_data);
        }
    
        fclose($output);
        exit;
    }
    
    public function download_sample_transaction_file()
    {
        $header = [
            'transaction_id', 'type', 'paid_at', 'amount', 'currency_code', 'currency_rate',
            'account_name', 'invoice_no', 'contact_email', 'category_name', 'description', 'payment_method'
        ];
    
  
        $filename = 'sample_transactions_' . date('Ymd_His') . '.csv';
    
        header('Content-Type: text/csv; charset=utf-8');
        header("Content-Disposition: attachment; filename={$filename}");
    
        $output = fopen('php://output', 'w');
    

        fputcsv($output, $header);
    

        fclose($output);
        exit;
    }
    
}

$transactions = new DevsAccounting_Transactions(); 