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

$manfiet_expense_tracker_autoload = plugin_dir_path(__FILE__) . '../vendor/autoload.php';
if (file_exists($manfiet_expense_tracker_autoload)) {
    require_once $manfiet_expense_tracker_autoload;
}
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use Dompdf\Dompdf;
use Dompdf\Options;

class Manfiet_Expense_Export {
    private $table;
    private $nonce_action = 'manfiet_export_expenses';

    private function user_can_manage_expenses() {
        return current_user_can('manage_expenses') || current_user_can('manage_options');
    }

    public function __construct() {
        global $wpdb;
        $this->table = $wpdb->prefix . 'manfiet_expenses';
    }

    private function verify_export_nonce() {
        $nonce = isset($_REQUEST['_wpnonce']) ? sanitize_text_field(wp_unslash($_REQUEST['_wpnonce'])) : '';

        if (!$nonce || !wp_verify_nonce($nonce, $this->nonce_action)) {
            wp_die(
                esc_html__('Security check failed. Please retry from the dashboard.', 'manfi-expense-tracker'),
                esc_html__('Invalid nonce', 'manfi-expense-tracker'),
                ['response' => 403]
            );
        }
    }

    private function sanitize_export_cell($value) {
        $value = (string) $value;

        // Prevent formula injection in CSV/XLSX exports.
        if (preg_match('/^[=+\-@\t\r]/', $value) === 1) {
            return "'" . $value;
        }

        return $value;
    }

    private function prepare_download_response($content_type, $filename) {
        while (ob_get_level() > 0) {
            ob_end_clean();
        }

        nocache_headers();
        header('Content-Type: ' . $content_type);
        header('Content-Disposition: attachment; filename="' . $filename . '"');
    }

    private function get_export_filename($extension) {
        $date = wp_date('Y-m-d');
        $extension = ltrim((string) $extension, '.');

        return 'expenses-' . $date . '.' . $extension;
    }

    private function get_missing_user_label() {
        $default_label = __('Unknown User', 'manfi-expense-tracker');
        $configured_label = get_option('manfiet_export_unknown_user_label', $default_label);
        $configured_label = sanitize_text_field((string) $configured_label);

        return $configured_label !== '' ? $configured_label : $default_label;
    }

    private function add_user_full_name_column(array $expenses) {
        if (empty($expenses)) {
            return $expenses;
        }

        $user_ids = [];
        foreach ($expenses as $expense) {
            if (isset($expense['user_id']) && is_numeric($expense['user_id'])) {
                $user_ids[] = (int) $expense['user_id'];
            }
        }

        $user_ids = array_values(array_unique(array_filter($user_ids)));
        if (empty($user_ids)) {
            return $expenses;
        }

        $users = get_users([
            'include' => $user_ids,
            'fields'  => 'all',
        ]);

        $user_full_names = [];
        foreach ($users as $user) {
            $full_name = trim($user->first_name . ' ' . $user->last_name);
            if ($full_name === '') {
                $full_name = $user->display_name;
            }

            $user_full_names[(int) $user->ID] = $full_name;
        }

        foreach ($expenses as &$expense) {
            $full_name = $this->get_missing_user_label();
            if (isset($expense['user_id']) && is_numeric($expense['user_id'])) {
                $user_id = (int) $expense['user_id'];
                if (isset($user_full_names[$user_id])) {
                    $full_name = $user_full_names[$user_id];
                }
            }

            $updated_expense = [];
            foreach ($expense as $key => $value) {
                $updated_expense[$key] = $value;
                if ($key === 'user_id') {
                    $updated_expense['full_name'] = $full_name;
                }
            }

            if (!isset($updated_expense['full_name'])) {
                $updated_expense['full_name'] = $full_name;
            }

            $expense = $updated_expense;
        }
        unset($expense);

        return $expenses;
    }

    private function get_human_readable_headers(array $columns) {
        return array_map(
            static function ($column) {
                return ucwords(str_replace('_', ' ', (string) $column));
            },
            $columns
        );
    }

    private function parse_export_filters() {
        // phpcs:disable WordPress.Security.NonceVerification.Recommended -- Nonce is verified in each export entrypoint before this helper is called.
        $start_date = isset($_REQUEST['start_date']) ? sanitize_text_field(wp_unslash($_REQUEST['start_date'])) : '';
        $end_date = isset($_REQUEST['end_date']) ? sanitize_text_field(wp_unslash($_REQUEST['end_date'])) : '';
        // phpcs:enable WordPress.Security.NonceVerification.Recommended

        $is_valid_date = static function ($value) {
            if ($value === '') {
                return true;
            }

            $date = DateTime::createFromFormat('Y-m-d', $value);
            return $date instanceof DateTime && $date->format('Y-m-d') === $value;
        };

        if (!$is_valid_date($start_date) || !$is_valid_date($end_date)) {
            wp_die(esc_html__('Invalid export date range.', 'manfi-expense-tracker'));
        }

        if (($start_date && !$end_date) || (!$start_date && $end_date)) {
            wp_die(esc_html__('Both start date and end date are required for custom report export.', 'manfi-expense-tracker'));
        }

        if ($start_date && $end_date && strtotime($start_date) > strtotime($end_date)) {
            wp_die(esc_html__('Start date cannot be later than end date.', 'manfi-expense-tracker'));
        }

        return [
            'start_date' => $start_date,
            'end_date' => $end_date,
            'has_custom_range' => ($start_date !== '' && $end_date !== ''),
        ];
    }

    private function get_filtered_expenses(array $filters) {
        global $wpdb;
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic export SQL is assembled from fixed clauses and prepared values.

        $query = "SELECT * FROM {$this->table} WHERE 1=1";
        $query_params = [];

        if (!$this->user_can_manage_expenses()) {
            $query .= ' AND user_id = %d';
            $query_params[] = get_current_user_id();
        }

        if (!empty($filters['has_custom_range'])) {
            $query .= ' AND date BETWEEN %s AND %s';
            $query_params[] = $filters['start_date'];
            $query_params[] = $filters['end_date'];
        }

        $query .= ' ORDER BY date DESC, id DESC';

        if (!empty($query_params)) {
            return $wpdb->get_results($wpdb->prepare($query, $query_params), ARRAY_A);
        }

        $result = $wpdb->get_results($query, ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return $result;
    }

    private function format_currency($value) {
        $symbol = class_exists('Manfiet_Expense_Tracker_Settings') ? Manfiet_Expense_Tracker_Settings::get_current_currency_symbol() : '$';
        return $symbol . number_format((float) $value, 2, '.', ',');
    }

    private function get_range_label(array $filters) {
        if (!empty($filters['has_custom_range'])) {
            return sprintf(
                '%s - %s',
                $filters['start_date'],
                $filters['end_date']
            );
        }

        return __('All time', 'manfi-expense-tracker');
    }

    private function get_period_key($date_value, $period) {
        $timestamp = strtotime((string) $date_value);
        if (!$timestamp) {
            return '';
        }

        if ($period === 'daily') {
            return gmdate('Y-m-d', $timestamp);
        }

        if ($period === 'weekly') {
            // ISO year + ISO week.
            return gmdate('o-\\WW', $timestamp);
        }

        if ($period === 'monthly') {
            return gmdate('Y-m', $timestamp);
        }

        return gmdate('Y', $timestamp);
    }

    private function summarize_by_period(array $expenses, $period) {
        $totals = [];

        foreach ($expenses as $expense) {
            $key = $this->get_period_key(isset($expense['date']) ? $expense['date'] : '', $period);
            if ($key === '') {
                continue;
            }

            if (!isset($totals[$key])) {
                $totals[$key] = 0;
            }

            $totals[$key] += (float) (isset($expense['amount']) ? $expense['amount'] : 0);
        }

        ksort($totals);
        $rows = [];
        foreach ($totals as $label => $amount) {
            $rows[] = [
                'label' => $label,
                'amount' => (float) $amount,
            ];
        }

        return $rows;
    }

    private function summarize_by_category(array $expenses, $limit = 8) {
        $totals = [];

        foreach ($expenses as $expense) {
            $category = isset($expense['category']) && $expense['category'] !== ''
                ? (string) $expense['category']
                : __('Uncategorized', 'manfi-expense-tracker');

            if (!isset($totals[$category])) {
                $totals[$category] = 0;
            }

            $totals[$category] += (float) (isset($expense['amount']) ? $expense['amount'] : 0);
        }

        arsort($totals);
        $totals = array_slice($totals, 0, $limit, true);

        $rows = [];
        foreach ($totals as $label => $amount) {
            $rows[] = [
                'label' => (string) $label,
                'amount' => (float) $amount,
            ];
        }

        return $rows;
    }

    private function build_report_data(array $expenses, array $filters) {
        $count = count($expenses);
        $total_spent = 0;

        foreach ($expenses as $expense) {
            $total_spent += (float) (isset($expense['amount']) ? $expense['amount'] : 0);
        }

        $average = $count > 0 ? ($total_spent / $count) : 0;

        return [
            'generated_at' => wp_date(get_option('date_format') . ' ' . get_option('time_format')),
            'range_label' => $this->get_range_label($filters),
            'summary' => [
                'count' => $count,
                'total_spent' => $total_spent,
                'average' => $average,
            ],
            'daily' => $this->summarize_by_period($expenses, 'daily'),
            'weekly' => $this->summarize_by_period($expenses, 'weekly'),
            'monthly' => $this->summarize_by_period($expenses, 'monthly'),
            'yearly' => $this->summarize_by_period($expenses, 'yearly'),
            'categories' => $this->summarize_by_category($expenses, 8),
            'has_custom_range' => !empty($filters['has_custom_range']),
            'custom_range' => !empty($filters['has_custom_range']) ? [
                'start_date' => $filters['start_date'],
                'end_date' => $filters['end_date'],
            ] : null,
        ];
    }

    private function get_table_html_for_period_rows(array $rows, $period_label) {
        $html = '<h3>' . esc_html($period_label) . '</h3>';

        if (empty($rows)) {
            $html .= '<p class="muted">' . esc_html__('No data available.', 'manfi-expense-tracker') . '</p>';
            return $html;
        }

        $html .= '<table><thead><tr><th>' . esc_html__('Period', 'manfi-expense-tracker') . '</th><th>' . esc_html__('Amount', 'manfi-expense-tracker') . '</th></tr></thead><tbody>';

        foreach ($rows as $row) {
            $html .= '<tr>';
            $html .= '<td>' . esc_html($row['label']) . '</td>';
            $html .= '<td>' . esc_html($this->format_currency($row['amount'])) . '</td>';
            $html .= '</tr>';
        }

        $html .= '</tbody></table>';

        return $html;
    }

    public function export_csv() {
        if (!$this->user_can_manage_expenses()) {
            wp_die(esc_html__('Unauthorized Access', 'manfi-expense-tracker'));
        }

        $this->verify_export_nonce();

        $filters = $this->parse_export_filters();
        $expenses = $this->get_filtered_expenses($filters);
        $expenses = $this->add_user_full_name_column($expenses);

        if (empty($expenses)) {
            wp_die(esc_html__('No expenses found to export.', 'manfi-expense-tracker'));
        }

        $this->prepare_download_response('text/csv; charset=utf-8', $this->get_export_filename('csv'));

        $output = fopen('php://output', 'w');
        $report_data = $this->build_report_data($expenses, $filters);

        fputcsv($output, [__('Expense Report', 'manfi-expense-tracker')]);
        // translators: %s: report generation timestamp.
        fputcsv($output, [sprintf(__('Generated on: %s', 'manfi-expense-tracker'), $report_data['generated_at'])]);
        // translators: %s: selected report range label.
        fputcsv($output, [sprintf(__('Report range: %s', 'manfi-expense-tracker'), $report_data['range_label'])]);
        fputcsv($output, []);

        fputcsv($output, [__('Summary', 'manfi-expense-tracker')]);
        fputcsv($output, [__('Total Expenses', 'manfi-expense-tracker'), $report_data['summary']['count']]);
        fputcsv($output, [__('Total Spent', 'manfi-expense-tracker'), $this->format_currency($report_data['summary']['total_spent'])]);
        fputcsv($output, [__('Average Expense', 'manfi-expense-tracker'), $this->format_currency($report_data['summary']['average'])]);
        fputcsv($output, []);

        $period_sections = [
            __('Daily Report', 'manfi-expense-tracker') => $report_data['daily'],
            __('Weekly Report', 'manfi-expense-tracker') => $report_data['weekly'],
            __('Monthly Report', 'manfi-expense-tracker') => $report_data['monthly'],
            __('Yearly Report', 'manfi-expense-tracker') => $report_data['yearly'],
        ];

        foreach ($period_sections as $section_title => $rows) {
            fputcsv($output, [$section_title]);
            fputcsv($output, [__('Period', 'manfi-expense-tracker'), __('Amount', 'manfi-expense-tracker')]);

            if (empty($rows)) {
                fputcsv($output, [__('No data available', 'manfi-expense-tracker'), '']);
            } else {
                foreach ($rows as $row) {
                    fputcsv($output, [$row['label'], $this->format_currency($row['amount'])]);
                }
            }

            fputcsv($output, []);
        }

        if (!empty($report_data['has_custom_range'])) {
            fputcsv($output, [__('Custom Selected Time Frame', 'manfi-expense-tracker')]);
            fputcsv($output, [__('Start Date', 'manfi-expense-tracker'), $report_data['custom_range']['start_date']]);
            fputcsv($output, [__('End Date', 'manfi-expense-tracker'), $report_data['custom_range']['end_date']]);
            fputcsv($output, [__('Total Spent', 'manfi-expense-tracker'), $this->format_currency($report_data['summary']['total_spent'])]);
            fputcsv($output, []);
        }

        fputcsv($output, [__('Top Categories', 'manfi-expense-tracker')]);
        fputcsv($output, [__('Category', 'manfi-expense-tracker'), __('Amount', 'manfi-expense-tracker')]);
        foreach ($report_data['categories'] as $row) {
            fputcsv($output, [$row['label'], $this->format_currency($row['amount'])]);
        }
        fputcsv($output, []);

        fputcsv($output, [__('Detailed Expenses Table', 'manfi-expense-tracker')]);
        $columns = array_keys($expenses[0]);
        fputcsv($output, $this->get_human_readable_headers($columns));

        foreach ($expenses as $expense) {
            fputcsv($output, array_map([$this, 'sanitize_export_cell'], $expense));
        }

        // phpcs:disable WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Closing the php://output stream after CSV generation.
        fclose($output);
        // phpcs:enable WordPress.WP.AlternativeFunctions.file_system_operations_fclose
        exit;
    }

    public function export_excel() {
        if (!$this->user_can_manage_expenses()) {
            wp_die(esc_html__('Unauthorized Access', 'manfi-expense-tracker'));
        }

        $this->verify_export_nonce();

        if (!class_exists('PhpOffice\\PhpSpreadsheet\\Spreadsheet') || !class_exists('PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx')) {
            wp_die(esc_html__('Excel export dependency is missing. Please run composer install.', 'manfi-expense-tracker'));
        }

        $filters = $this->parse_export_filters();
        $expenses = $this->get_filtered_expenses($filters);
        $expenses = $this->add_user_full_name_column($expenses);

        if (empty($expenses)) {
            wp_die(esc_html__('No expenses found to export.', 'manfi-expense-tracker'));
        }

        $spreadsheet = new Spreadsheet();
        $s = $spreadsheet->getActiveSheet();
        $s->setTitle('Report Summary');
        $report_data = $this->build_report_data($expenses, $filters);
        $symbol = class_exists('Manfiet_Expense_Tracker_Settings') ? Manfiet_Expense_Tracker_Settings::get_current_currency_symbol() : '$';
        $xl_format_code = '"' . $symbol . '"#,##0.00';

        // ── Style definitions ────────────────────────────────────────────────
        $xl_title   = [
            'font'      => ['bold' => true, 'size' => 18, 'color' => ['rgb' => 'FFFFFF']],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '1E3A8A']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'vertical' => Alignment::VERTICAL_CENTER],
        ];
        $xl_meta    = [
            'font'      => ['italic' => true, 'size' => 10, 'color' => ['rgb' => '6B7280']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT],
        ];
        $xl_section = [
            'font'      => ['bold' => true, 'size' => 11, 'color' => ['rgb' => 'FFFFFF']],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '2563EB']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'vertical' => Alignment::VERTICAL_CENTER],
        ];
        $xl_th      = [
            'font'      => ['bold' => true, 'size' => 10, 'color' => ['rgb' => '1E3A8A']],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'EFF6FF']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'vertical' => Alignment::VERTICAL_CENTER],
            'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'BFDBFE']]],
        ];
        $xl_td_odd  = [
            'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
            'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'E5E7EB']]],
        ];
        $xl_td_even = [
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'EFF6FF']],
            'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
            'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'E5E7EB']]],
        ];
        $xl_stat_lbl = [
            'font'      => ['size' => 10, 'color' => ['rgb' => '374151']],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F8FAFC']],
            'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'DBE4F2']]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'vertical' => Alignment::VERTICAL_CENTER],
        ];
        $xl_stat_val = [
            'font'      => ['bold' => true, 'size' => 11, 'color' => ['rgb' => '1D4ED8']],
            'fill'      => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F8FAFC']],
            'borders'   => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'DBE4F2']]],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_RIGHT, 'vertical' => Alignment::VERTICAL_CENTER],
        ];

        // Column widths
        $s->getColumnDimension('A')->setWidth(28);
        $s->getColumnDimension('B')->setWidth(20);
        $s->getColumnDimension('C')->setWidth(3);

        // ── Row 1: Title ──────────────────────────────────────────────────────
        $s->mergeCells('A1:B1');
        $s->setCellValue('A1', __('Expense Report', 'manfi-expense-tracker'));
        $s->getRowDimension(1)->setRowHeight(34);
        $s->getStyle('A1:B1')->applyFromArray($xl_title);

        // ── Rows 2–3: Meta ────────────────────────────────────────────────────
        // translators: %s: report generation timestamp.
        $s->setCellValue('A2', sprintf(__('Generated on: %s', 'manfi-expense-tracker'), $report_data['generated_at']));
        $s->getStyle('A2')->applyFromArray($xl_meta);
        // translators: %s: selected report range label.
        $s->setCellValue('A3', sprintf(__('Report range: %s', 'manfi-expense-tracker'), $report_data['range_label']));
        $s->getStyle('A3')->applyFromArray($xl_meta);

        // ── Row 5: Summary Statistics section header ──────────────────────────
        $s->mergeCells('A5:B5');
        $s->setCellValue('A5', __('Summary Statistics', 'manfi-expense-tracker'));
        $s->getRowDimension(5)->setRowHeight(24);
        $s->getStyle('A5:B5')->applyFromArray($xl_section);

        // ── Rows 6–8: Stats ───────────────────────────────────────────────────
        $xl_stats_rows = [
            6 => [__('Total Expenses', 'manfi-expense-tracker'), (string) $report_data['summary']['count']],
            7 => [__('Total Spent', 'manfi-expense-tracker'), $this->format_currency($report_data['summary']['total_spent'])],
            8 => [__('Average Expense', 'manfi-expense-tracker'), $this->format_currency($report_data['summary']['average'])],
        ];
        foreach ($xl_stats_rows as $xl_r => [$xl_lbl, $xl_val]) {
            $s->setCellValue("A{$xl_r}", $xl_lbl);
            $s->setCellValue("B{$xl_r}", $xl_val);
            $s->getRowDimension($xl_r)->setRowHeight(20);
            $s->getStyle("A{$xl_r}")->applyFromArray($xl_stat_lbl);
            $s->getStyle("B{$xl_r}")->applyFromArray($xl_stat_val);
        }

        // ── Row 10: Top Categories section header ─────────────────────────────
        $s->mergeCells('A10:B10');
        $s->setCellValue('A10', __('Top Categories', 'manfi-expense-tracker'));
        $s->getRowDimension(10)->setRowHeight(24);
        $s->getStyle('A10:B10')->applyFromArray($xl_section);

        // ── Row 11: Category table header ─────────────────────────────────────
        $s->setCellValue('A11', __('Category', 'manfi-expense-tracker'));
        $s->setCellValue('B11', __('Amount', 'manfi-expense-tracker'));
        $s->getStyle('A11:B11')->applyFromArray($xl_th);

        // ── Rows 12+: Category data ───────────────────────────────────────────
        $cat_start_row = 12;
        foreach ($report_data['categories'] as $xl_i => $xl_cat) {
            $xl_r = $cat_start_row + $xl_i;
            $s->setCellValue("A{$xl_r}", $xl_cat['label']);
            $s->setCellValue("B{$xl_r}", $xl_cat['amount']);
            $s->getStyle("A{$xl_r}:B{$xl_r}")->applyFromArray($xl_i % 2 === 0 ? $xl_td_odd : $xl_td_even);
        }
        $cat_count   = count($report_data['categories']);
        $cat_end_row = $cat_start_row + max($cat_count - 1, 0);
        if ($cat_count > 0) {
            $s->getStyle("B{$cat_start_row}:B{$cat_end_row}")->getNumberFormat()->setFormatCode($xl_format_code);
        }

        // ── Trend section ─────────────────────────────────────────────────────
        $trend_source       = !empty($report_data['monthly']) ? $report_data['monthly'] : $report_data['daily'];
        $trend_section_row  = $cat_end_row + 3;
        $s->mergeCells("A{$trend_section_row}:B{$trend_section_row}");
        $s->setCellValue("A{$trend_section_row}", __('Expense Trend (Monthly)', 'manfi-expense-tracker'));
        $s->getRowDimension($trend_section_row)->setRowHeight(24);
        $s->getStyle("A{$trend_section_row}:B{$trend_section_row}")->applyFromArray($xl_section);

        $trend_hdr_row  = $trend_section_row + 1;
        $s->setCellValue("A{$trend_hdr_row}", __('Period', 'manfi-expense-tracker'));
        $s->setCellValue("B{$trend_hdr_row}", __('Amount', 'manfi-expense-tracker'));
        $s->getStyle("A{$trend_hdr_row}:B{$trend_hdr_row}")->applyFromArray($xl_th);

        $trend_data_start = $trend_hdr_row + 1;
        foreach ($trend_source as $xl_i => $xl_trow) {
            $xl_r = $trend_data_start + $xl_i;
            $s->setCellValue("A{$xl_r}", $xl_trow['label']);
            $s->setCellValue("B{$xl_r}", $xl_trow['amount']);
            $s->getStyle("A{$xl_r}:B{$xl_r}")->applyFromArray($xl_i % 2 === 0 ? $xl_td_odd : $xl_td_even);
        }
        $trend_count   = count($trend_source);
        $trend_end_row = $trend_data_start + max($trend_count - 1, 0);
        if ($trend_count > 0) {
            $s->getStyle("B{$trend_data_start}:B{$trend_end_row}")->getNumberFormat()->setFormatCode($xl_format_code);
        }

        // ── Period Breakdown (Daily / Weekly / Monthly / Yearly) ──────────────
        $xl_periods = [
            [__('Daily Report', 'manfi-expense-tracker'), $report_data['daily']],
            [__('Weekly Report', 'manfi-expense-tracker'), $report_data['weekly']],
            [__('Monthly Report', 'manfi-expense-tracker'), $report_data['monthly']],
            [__('Yearly Report', 'manfi-expense-tracker'), $report_data['yearly']],
        ];
        $xl_cursor = $trend_end_row + 3;
        foreach ($xl_periods as [$xl_plabel, $xl_pdata]) {
            if (empty($xl_pdata)) {
                continue;
            }
            $s->mergeCells("A{$xl_cursor}:B{$xl_cursor}");
            $s->setCellValue("A{$xl_cursor}", $xl_plabel);
            $s->getRowDimension($xl_cursor)->setRowHeight(24);
            $s->getStyle("A{$xl_cursor}:B{$xl_cursor}")->applyFromArray($xl_section);
            $xl_cursor++;

            $s->setCellValue("A{$xl_cursor}", __('Period', 'manfi-expense-tracker'));
            $s->setCellValue("B{$xl_cursor}", __('Total Amount', 'manfi-expense-tracker'));
            $s->getStyle("A{$xl_cursor}:B{$xl_cursor}")->applyFromArray($xl_th);
            $xl_cursor++;

            foreach ($xl_pdata as $xl_pi => $xl_prow) {
                $s->setCellValue("A{$xl_cursor}", $xl_prow['label']);
                $s->setCellValue("B{$xl_cursor}", $xl_prow['amount']);
                $s->getStyle("A{$xl_cursor}:B{$xl_cursor}")->applyFromArray($xl_pi % 2 === 0 ? $xl_td_odd : $xl_td_even);
                $s->getStyle("B{$xl_cursor}")->getNumberFormat()->setFormatCode($xl_format_code);
                $xl_cursor++;
            }
            $xl_cursor += 2;
        }

        $detail_sheet = $spreadsheet->createSheet();
        $detail_sheet->setTitle('Expense Details');
        $sheet = $detail_sheet;
        $columns = array_keys($expenses[0]);
        $sheet->fromArray($this->get_human_readable_headers($columns), NULL, 'A1');
        $row = 2;
        foreach ($expenses as $expense) {
            $sheet->fromArray(array_map([$this, 'sanitize_export_cell'], array_values($expense)), NULL, "A$row");
            $row++;
        }

        $highest_column = $sheet->getHighestColumn();
        $highest_row = $sheet->getHighestRow();
        $header_range = "A1:{$highest_column}1";

        $sheet->getStyle($header_range)->applyFromArray([
            'font' => [
                'bold' => true,
                'color' => ['rgb' => '1E3A8A'],
                'size' => 11,
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => 'EFF6FF'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_LEFT,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => 'BFDBFE'],
                ],
            ],
        ]);

        if ($highest_row >= 2) {
            $data_range = "A2:{$highest_column}{$highest_row}";
            $sheet->getStyle($data_range)->applyFromArray([
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical' => Alignment::VERTICAL_TOP,
                ],
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['rgb' => 'D1D5DB'],
                    ],
                ],
            ]);

            for ($excel_row = 2; $excel_row <= $highest_row; $excel_row++) {
                if ($excel_row % 2 === 0) {
                    $zebra_range = "A{$excel_row}:{$highest_column}{$excel_row}";
                    $sheet->getStyle($zebra_range)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('F9FAFB');
                }
            }
        }

        $sheet->freezePane('A2');
        $sheet->setAutoFilter($header_range);

        for ($column_index = 1; $column_index <= count($columns); $column_index++) {
            $column_letter = Coordinate::stringFromColumnIndex($column_index);
            $sheet->getColumnDimension($column_letter)->setAutoSize(true);
        }

        $spreadsheet->setActiveSheetIndex(0);

        $writer = new Xlsx($spreadsheet);
        $tmp_file = tempnam(sys_get_temp_dir(), 'manfiet_xlsx_');
        $writer->save($tmp_file);
        $this->prepare_download_response('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', $this->get_export_filename('xlsx'));
        header('Content-Length: ' . filesize($tmp_file));
        // phpcs:disable WordPress.WP.AlternativeFunctions.file_system_operations_readfile -- Streaming the generated temporary export file to the browser.
        readfile($tmp_file);
        // phpcs:enable WordPress.WP.AlternativeFunctions.file_system_operations_readfile
        wp_delete_file($tmp_file);
        exit;
    }

    public function export_pdf() {
        if (!$this->user_can_manage_expenses()) {
            wp_die(esc_html__('Unauthorized Access', 'manfi-expense-tracker'));
        }

        $this->verify_export_nonce();

        if (!class_exists('Dompdf\\Dompdf') || !class_exists('Dompdf\\Options')) {
            wp_die(esc_html__('PDF export dependency is missing. Please run composer install.', 'manfi-expense-tracker'));
        }

        $filters = $this->parse_export_filters();
        $expenses = $this->get_filtered_expenses($filters);
        $expenses = $this->add_user_full_name_column($expenses);

        if (empty($expenses)) {
            wp_die(esc_html__('No expenses found to export.', 'manfi-expense-tracker'));
        }

        $columns = array_keys($expenses[0]);
        $report_data = $this->build_report_data($expenses, $filters);
        $max_category_amount = 0;

        foreach ($report_data['categories'] as $row) {
            if ($row['amount'] > $max_category_amount) {
                $max_category_amount = $row['amount'];
            }
        }

        $trend_rows = !empty($report_data['monthly']) ? $report_data['monthly'] : $report_data['daily'];
        $max_trend_amount = 0;
        foreach ($trend_rows as $row) {
            if ($row['amount'] > $max_trend_amount) {
                $max_trend_amount = $row['amount'];
            }
        }

        $css_file = MANFIET_EXPENSE_TRACKER_PLUGIN_DIR . 'assets/css/expense-pdf.css';
        $css_content = file_exists($css_file) ? file_get_contents($css_file) : '';

        $html = '<!doctype html><html><head><meta charset="utf-8">' . sprintf('<%1$s>%2$s</%1$s>', 'style', $css_content) . '</head><body>';
        $html .= '<div class="report-header">';
        $html .= '<h2 class="report-title">' . esc_html__('Expense Report', 'manfi-expense-tracker') . '</h2>';
        // translators: %s: report generation timestamp.
        $html .= '<p class="report-meta">' . sprintf(esc_html__('Generated on: %s', 'manfi-expense-tracker'), esc_html($report_data['generated_at'])) . '</p>';
        // translators: %s: selected report range label.
        $html .= '<p class="report-meta">' . sprintf(esc_html__('Report range: %s', 'manfi-expense-tracker'), esc_html($report_data['range_label'])) . '</p>';
        $html .= '</div>';

        $html .= '<table class="stats"><tr>';
        $html .= '<td><div class="stat-label">' . esc_html__('Total Expenses', 'manfi-expense-tracker') . '</div><div class="stat-value">' . esc_html((string) $report_data['summary']['count']) . '</div></td>';
        $html .= '<td><div class="stat-label">' . esc_html__('Total Spent', 'manfi-expense-tracker') . '</div><div class="stat-value">' . esc_html($this->format_currency($report_data['summary']['total_spent'])) . '</div></td>';
        $html .= '<td><div class="stat-label">' . esc_html__('Average Expense', 'manfi-expense-tracker') . '</div><div class="stat-value">' . esc_html($this->format_currency($report_data['summary']['average'])) . '</div></td>';
        $html .= '</tr></table>';

        $html .= '<div class="section-title">' . esc_html__('Daily / Weekly / Monthly / Yearly Reports', 'manfi-expense-tracker') . '</div>';
        $html .= $this->get_table_html_for_period_rows($report_data['daily'], __('Daily Report', 'manfi-expense-tracker'));
        $html .= $this->get_table_html_for_period_rows($report_data['weekly'], __('Weekly Report', 'manfi-expense-tracker'));
        $html .= $this->get_table_html_for_period_rows($report_data['monthly'], __('Monthly Report', 'manfi-expense-tracker'));
        $html .= $this->get_table_html_for_period_rows($report_data['yearly'], __('Yearly Report', 'manfi-expense-tracker'));

        if (!empty($report_data['has_custom_range'])) {
            $html .= '<div class="section-title">' . esc_html__('Custom Selected Time Frame', 'manfi-expense-tracker') . '</div>';
            $html .= '<p class="muted">' . esc_html(sprintf('%s to %s', $report_data['custom_range']['start_date'], $report_data['custom_range']['end_date'])) . '</p>';
        }

        $html .= '<div class="section-title">' . esc_html__('Category Chart', 'manfi-expense-tracker') . '</div>';
        if (empty($report_data['categories'])) {
            $html .= '<p class="muted">' . esc_html__('No data available.', 'manfi-expense-tracker') . '</p>';
        } else {
            foreach ($report_data['categories'] as $row) {
                $width = $max_category_amount > 0 ? (int) round(($row['amount'] / $max_category_amount) * 100) : 0;
                if ($width < 2 && $row['amount'] > 0) {
                    $width = 2;
                }

                $html .= '<div class="chart-row">';
                $html .= '<div class="chart-label">' . esc_html($row['label']) . ' - ' . esc_html($this->format_currency($row['amount'])) . '</div>';
                $html .= '<div class="chart-track"><div class="chart-fill" style="width:' . esc_attr((string) $width) . '%"></div></div>';
                $html .= '</div>';
            }
        }

        $html .= '<div class="section-title">' . esc_html__('Trend Chart', 'manfi-expense-tracker') . '</div>';
        if (empty($trend_rows)) {
            $html .= '<p class="muted">' . esc_html__('No data available.', 'manfi-expense-tracker') . '</p>';
        } else {
            foreach ($trend_rows as $row) {
                $width = $max_trend_amount > 0 ? (int) round(($row['amount'] / $max_trend_amount) * 100) : 0;
                if ($width < 2 && $row['amount'] > 0) {
                    $width = 2;
                }

                $html .= '<div class="chart-row">';
                $html .= '<div class="chart-label">' . esc_html($row['label']) . ' - ' . esc_html($this->format_currency($row['amount'])) . '</div>';
                $html .= '<div class="chart-track"><div class="chart-fill" style="width:' . esc_attr((string) $width) . '%"></div></div>';
                $html .= '</div>';
            }
        }

        $html .= '<div class="section-title">' . esc_html__('Detailed Expenses Table', 'manfi-expense-tracker') . '</div>';
        $html .= '<table><thead><tr>';

        foreach ($columns as $col) {
            $label = ucwords(str_replace('_', ' ', (string) $col));
            $html .= '<th>' . esc_html($label) . '</th>';
        }

        $html .= '</tr></thead><tbody>';

        foreach ($expenses as $expense) {
            $html .= '<tr>';
            foreach ($columns as $col) {
                $data = isset($expense[$col]) ? $expense[$col] : '';
                $html .= '<td>' . esc_html((string) $data) . '</td>';
            }
            $html .= '</tr>';
        }

        $html .= '</tbody></table></body></html>';

        $options = new Options();
        $options->set('isHtml5ParserEnabled', true);
        $dompdf = new Dompdf($options);
        $dompdf->loadHtml($html);
        $dompdf->setPaper('A4', 'landscape');
        $dompdf->render();

        while (ob_get_level() > 0) {
            ob_end_clean();
        }

        $dompdf->stream($this->get_export_filename('pdf'), ['Attachment' => 1]);
        exit;
    }
}

// Register Admin Actions for Export
add_action('admin_post_manfiet_export_csv', [new Manfiet_Expense_Export(), 'export_csv']);
add_action('admin_post_manfiet_export_excel', [new Manfiet_Expense_Export(), 'export_excel']);
add_action('admin_post_manfiet_export_pdf', [new Manfiet_Expense_Export(), 'export_pdf']);
