<?php
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin tables; all table names are internal constants never from user input
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * ACQUIS_Export_Engine
 *
 * Exports leads to CSV or XLSX with custom fields, lead score,
 * tags, segment, and download history.
 */
class ACQUIS_Export_Engine {

    /**
     * Stream a CSV file to the browser.
     *
     * @param  array $filters  Optional: date_from, date_to, form_id
     */
    public static function export_csv( $filters = array() ) {
        $leads  = ACQUIS_P3_DB::get_leads_for_export($filters);
        $fields = self::get_all_custom_field_keys();

        $filename = 'acquisitionsuite-export-' . gmdate('Y-m-d') . '.csv';

        // Headers
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Pragma: no-cache');
        header('Expires: 0');

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- php://output is a PHP stream wrapper, not a real file; WP_Filesystem cannot open it
        $fp = fopen('php://output', 'w');
        fprintf($fp, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM for Excel

        // Column headers
        $cols = array(
            'ID', 'Full Name', 'Email', 'File', 'Form', 'Lead Score',
            'Tags', 'Segment', 'UTM Source', 'UTM Medium', 'UTM Campaign',
            'Source URL', 'IP Address', 'Date Submitted',
        );
        foreach ($fields as $label) $cols[] = $label;
        fputcsv($fp, $cols);

        foreach ($leads as $lead) {
            $score   = ACQUIS_P3_DB::get_lead_score($lead->id);
            $sub     = self::get_submission_for_lead($lead->id);
            $values  = $sub ? self::get_flat_values($sub->id, $fields) : array_fill(0, count($fields), '');

            $row = array(
                $lead->id,
                $lead->full_name,
                $lead->email,
                $lead->file_name ?? '',
                $sub ? ($sub->form_id ?? '') : '',
                $score ? $score->total_score : 0,
                class_exists( 'ACQUIS_Automation_Engine' ) ? implode(', ', ACQUIS_Automation_Engine::get_lead_tags($lead->id)) : '',
                class_exists( 'ACQUIS_Automation_Engine' ) ? ACQUIS_Automation_Engine::get_lead_segment($lead->id) : '',
                $lead->utm_source   ?? '',
                $lead->utm_medium   ?? '',
                $lead->utm_campaign ?? '',
                $lead->source_url   ?? '',
                $lead->ip_address,
                $lead->created_at,
            );
            foreach ($values as $v) $row[] = $v;
            fputcsv($fp, $row);
        }
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- pairs with the php://output stream opened above
        fclose($fp);
        exit;
    }

    /**
     * Generate an XLSX and stream it.
     * Uses PHP's ZipArchive to build a minimal valid XLSX without dependencies.
     */
    public static function export_xlsx( $filters = array() ) {
        $leads  = ACQUIS_P3_DB::get_leads_for_export($filters);
        $fields = self::get_all_custom_field_keys();

        $cols = array(
            'ID','Full Name','Email','File','Form','Lead Score',
            'Tags','Segment','UTM Source','UTM Medium','UTM Campaign',
            'Source URL','IP Address','Date Submitted',
        );
        foreach ($fields as $label) $cols[] = $label;

        $rows = array($cols);
        foreach ($leads as $lead) {
            $score  = ACQUIS_P3_DB::get_lead_score($lead->id);
            $sub    = self::get_submission_for_lead($lead->id);
            $values = $sub ? self::get_flat_values($sub->id, $fields) : array_fill(0, count($fields), '');
            $row = array(
                $lead->id, $lead->full_name, $lead->email,
                $lead->file_name ?? '', $sub ? $sub->form_id : '',
                $score ? $score->total_score : 0,
                class_exists( 'ACQUIS_Automation_Engine' ) ? implode(', ', ACQUIS_Automation_Engine::get_lead_tags($lead->id)) : '',
                class_exists( 'ACQUIS_Automation_Engine' ) ? ACQUIS_Automation_Engine::get_lead_segment($lead->id) : '',
                $lead->utm_source ?? '', $lead->utm_medium ?? '', $lead->utm_campaign ?? '',
                $lead->source_url ?? '', $lead->ip_address, $lead->created_at,
            );
            foreach ($values as $v) $row[] = $v;
            $rows[] = $row;
        }

        $xlsx_data = self::build_xlsx($rows);
        $filename  = 'acquisitionsuite-export-' . gmdate('Y-m-d') . '.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Content-Length: ' . strlen($xlsx_data));
        header('Pragma: no-cache');
        header('Expires: 0');
        // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- Binary XLSX file download, not HTML output
        echo $xlsx_data;
        exit;
    }

    // ── Helpers ────────────────────────────────────────────────────────────
    private static function get_all_custom_field_keys() {
        global $wpdb;
        $keys = $wpdb->get_results(
            "SELECT DISTINCT sv.field_key, sv.field_label FROM " . ACQUIS_P3_DB::sub_values_table() . " sv
             ORDER BY sv.field_key ASC"
        );
        $map = array();
        foreach ($keys as $k) $map[$k->field_key] = $k->field_label ?: $k->field_key;
        return $map;
    }

    private static function get_submission_for_lead($lead_id) {
        global $wpdb;
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM %i WHERE lead_id=%d ORDER BY id DESC LIMIT 1",
            ACQUIS_P3_DB::submissions_table(), absint($lead_id)
        ));
    }

    private static function get_flat_values($submission_id, $field_map) {
        global $wpdb;
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT field_key, value FROM %i WHERE submission_id=%d",
            ACQUIS_P3_DB::sub_values_table(), absint($submission_id)
        ));
        $flat = array();
        foreach ($rows as $r) $flat[$r->field_key] = $r->value;
        $result = array();
        foreach ($field_map as $key => $label) {
            $result[] = $flat[$key] ?? '';
        }
        return $result;
    }

    /**
     * Build minimal XLSX binary using ZipArchive.
     * No external library needed pure PHP Open XML.
     */
    private static function build_xlsx( array $rows ) {
        // Build sheet XML
        $sheet_xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
            . '<sheetData>';

        $row_num = 1;
        foreach ($rows as $row) {
            $sheet_xml .= '<row r="' . $row_num . '">';
            $col_num = 0;
            foreach ($row as $cell) {
                $col_letter = self::col_letter($col_num++);
                $cell_ref   = $col_letter . $row_num;
                $val = htmlspecialchars((string)$cell, ENT_XML1, 'UTF-8');
                if (is_numeric($cell) && $cell !== '') {
                    $sheet_xml .= '<c r="' . $cell_ref . '" t="n"><v>' . $val . '</v></c>';
                } else {
                    $sheet_xml .= '<c r="' . $cell_ref . '" t="inlineStr"><is><t>' . $val . '</t></is></c>';
                }
            }
            $sheet_xml .= '</row>';
            $row_num++;
        }
        $sheet_xml .= '</sheetData></worksheet>';

        $wb_xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
            . 'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
            . '<sheets><sheet name="Leads" sheetId="1" r:id="rId1"/></sheets></workbook>';

        $wb_rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
            . '</Relationships>';

        $pkg_rels = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
            . '</Relationships>';

        $ct = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
            . '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
            . '<Default Extension="xml" ContentType="application/xml"/>'
            . '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
            . '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
            . '</Types>';

        // Build zip in memory
        $tmp = tempnam(sys_get_temp_dir(), 'acquis_xlsx_');
        $zip = new ZipArchive();
        $zip->open($tmp, ZipArchive::OVERWRITE);
        $zip->addFromString('[Content_Types].xml',              $ct);
        $zip->addFromString('_rels/.rels',                      $pkg_rels);
        $zip->addFromString('xl/workbook.xml',                  $wb_xml);
        $zip->addFromString('xl/_rels/workbook.xml.rels',       $wb_rels);
        $zip->addFromString('xl/worksheets/sheet1.xml',         $sheet_xml);
        $zip->close();
        $data = file_get_contents($tmp);
        wp_delete_file( $tmp );
        return $data;
    }

    private static function col_letter($n) {
        $letter = '';
        while ($n >= 0) {
            $letter  = chr(65 + ($n % 26)) . $letter;
            $n = intdiv($n, 26) - 1;
        }
        return $letter;
    }
}