<?php

/**
 * Export data preparation utility.
 *
 * @package Leadite
 */
namespace Leadite\Export;

defined( 'ABSPATH' ) || exit;
class Helper {
    /**
     * Neutralise CSV/spreadsheet formula injection (OWASP: CSV Injection).
     *
     * Any cell whose first character is one of = + - @ or a control character
     * (TAB, CR) recognised as a formula trigger by Excel / Google Sheets / LibreOffice
     * gets a leading single-quote prepended so the application treats the value as
     * plain text instead of executing it as a formula.
     *
     * This must be applied to every user-supplied value written to CSV or XLSX.
     *
     * @param mixed $value Raw cell value (will be cast to string).
     * @return string Sanitised string safe to embed in any spreadsheet cell.
     */
    public static function sanitize_cell( $value ) : string {
        if ( is_array( $value ) || is_object( $value ) ) {
            // Returned as a spreadsheet cell string (escaped by the writer), not echoed to a page.
            $value = wp_json_encode( $value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES );
        }
        $str = (string) $value;
        if ( $str === '' ) {
            return $str;
        }
        // Remove invalid UTF-8 bytes.
        if ( function_exists( 'wp_check_invalid_utf8' ) ) {
            $str = wp_check_invalid_utf8( $str, true );
        }
        // Remove UTF-8 BOM if it somehow entered a field.
        $str = preg_replace( '/^\\xEF\\xBB\\xBF/u', '', $str );
        // Normalize line endings for spreadsheet cells.
        $str = str_replace( array("\r\n", "\r"), "\n", $str );
        // Remove characters that are illegal in XML 1.0.
        // XLSX stores shared strings as XML, so these characters can break Excel.
        $str = preg_replace( '/[^\\x09\\x0A\\x0D\\x20-\\x{D7FF}\\x{E000}-\\x{FFFD}\\x{10000}-\\x{10FFFF}]/u', '', $str );
        if ( null === $str || $str === '' ) {
            return '';
        }
        // Prevent spreadsheet formula injection.
        $first = $str[0];
        if ( $first === '=' || $first === '+' || $first === '-' || $first === '@' || $first === "\t" || $first === "\r" ) {
            $str = "'" . $str;
        }
        return $str;
    }

    /**
     * Prepare dynamic EAV fields into flat headers and rows for grid exports (CSV/XLSX).
     *
     * @param array $leads Submissions rows.
     * @return array{0: array, 1: array} Headers and Rows.
     */
    public static function prepare_export_data( array $leads ) {
        // 1. Define standard submission columns
        $meta_headers = array(
            'id'         => __( 'ID', 'leadite' ),
            'created_at' => __( 'Date', 'leadite' ),
            'source'     => __( 'Source', 'leadite' ),
            'form_name'  => __( 'Form Name', 'leadite' ),
            'status'     => __( 'Status', 'leadite' ),
            'ip'         => __( 'IP Address', 'leadite' ),
            'lang'       => __( 'Language', 'leadite' ),
            'page_url'   => __( 'Page URL', 'leadite' ),
        );
        // 2. Define canonical fields in preferred order
        $canonical_keys = array(
            'full_name' => __( 'Full Name', 'leadite' ),
            'email'     => __( 'Email', 'leadite' ),
            'phone'     => __( 'Phone', 'leadite' ),
            'message'   => __( 'Message', 'leadite' ),
            'company'   => __( 'Company', 'leadite' ),
            'subject'   => __( 'Subject', 'leadite' ),
        );
        // 3. Collect all unique custom fields across all leads.
        //    Batch-fetch ALL meta in one query to avoid N+1.
        $meta_map = \Leadite\Storage\LeadsRepository::find_many( $leads );
        $custom_fields = array();
        $lead_ids = array_map( function ( $l ) {
            return (int) $l->id;
        }, $leads );
        $tags_map = array();
        $notes_map = array();
        foreach ( $leads as $lead ) {
            $fields = ( isset( $meta_map[(int) $lead->id] ) ? $meta_map[(int) $lead->id]['fields'] : array() );
            foreach ( $fields as $f ) {
                if ( 'system' === $f->field_type ) {
                    continue;
                    // Handle UTMs separately at the end
                }
                if ( !empty( $f->canonical ) && isset( $canonical_keys[$f->canonical] ) ) {
                    continue;
                    // Already in canonical list
                }
                if ( !isset( $custom_fields[$f->field_key] ) ) {
                    $custom_fields[$f->field_key] = $f->field_label;
                }
            }
        }
        // 4. Define UTM parameters
        $utm_keys = array(
            'utm_source'   => __( 'UTM Source', 'leadite' ),
            'utm_medium'   => __( 'UTM Medium', 'leadite' ),
            'utm_campaign' => __( 'UTM Campaign', 'leadite' ),
            'utm_term'     => __( 'UTM Term', 'leadite' ),
            'utm_content'  => __( 'UTM Content', 'leadite' ),
            'gclid'        => __( 'Gclid', 'leadite' ),
        );
        // Assemble headers
        $headers = array_merge(
            array_values( $meta_headers ),
            array_values( $canonical_keys ),
            array_values( $custom_fields ),
            array_values( $utm_keys )
        );
        // Assemble rows
        $rows = array();
        foreach ( $leads as $lead ) {
            $fields = ( isset( $meta_map[(int) $lead->id] ) ? $meta_map[(int) $lead->id]['fields'] : array() );
            $row = array();
            // Add meta columns
            foreach ( array_keys( $meta_headers ) as $mkey ) {
                if ( 'starred' === $mkey ) {
                    $starred_val = ( !empty( $lead->starred ) ? __( 'Yes', 'leadite' ) : __( 'No', 'leadite' ) );
                    $row[] = self::sanitize_cell( $starred_val );
                } elseif ( 'tags' === $mkey ) {
                    $tags = ( isset( $tags_map[(int) $lead->id] ) ? $tags_map[(int) $lead->id] : array() );
                    $tag_names = array_map( function ( $t ) {
                        return $t->name;
                    }, $tags );
                    $row[] = self::sanitize_cell( implode( ', ', $tag_names ) );
                } elseif ( 'notes' === $mkey ) {
                    $notes = ( isset( $notes_map[(int) $lead->id] ) ? $notes_map[(int) $lead->id] : array() );
                    $note_strings = array_map( function ( $n ) {
                        return '[' . $n->created_at . '] ' . $n->note;
                    }, $notes );
                    $row[] = self::sanitize_cell( implode( "\n", $note_strings ) );
                } elseif ( 'hot_score_cached' === $mkey ) {
                    $row[] = self::sanitize_cell( ( isset( $lead->hot_score_cached ) ? (int) $lead->hot_score_cached : 0 ) );
                } elseif ( 'sla_status' === $mkey ) {
                    $row[] = self::sanitize_cell( ( isset( $lead->sla_status ) ? (string) $lead->sla_status : '' ) );
                } else {
                    $row[] = self::sanitize_cell( ( isset( $lead->{$mkey} ) ? $lead->{$mkey} : '' ) );
                }
            }
            // Add canonical columns
            foreach ( array_keys( $canonical_keys ) as $ckey ) {
                $row[] = self::sanitize_cell( \Leadite\Support\FieldMapper::canonical_value( $fields, $ckey ) );
            }
            // Add custom fields columns
            foreach ( array_keys( $custom_fields ) as $cfkey ) {
                $val = '';
                foreach ( $fields as $f ) {
                    if ( $f->field_key === $cfkey ) {
                        $val = $f->field_value;
                        break;
                    }
                }
                $row[] = self::sanitize_cell( $val );
            }
            // Add UTM columns
            foreach ( array_keys( $utm_keys ) as $ukey ) {
                $val = '';
                foreach ( $fields as $f ) {
                    if ( 'system' === $f->field_type && $f->canonical === $ukey ) {
                        $val = $f->field_value;
                        break;
                    }
                }
                $row[] = self::sanitize_cell( $val );
            }
            $rows[] = $row;
        }
        return array($headers, $rows);
    }

    /**
     * Variant of prepare_export_data() that accepts a pre-fetched meta map.
     *
     * Used by CsvExporter::export_chunk() during streaming to avoid re-querying
     * the database when meta has already been batch-loaded via find_many().
     *
     * The returned [headers, rows] follow the same structure as prepare_export_data().
     *
     * @param array $leads    Submission row objects for this chunk.
     * @param array $meta_map [id => ['row' => object, 'fields' => object[]]] from find_many().
     * @return array{0: array, 1: array} Headers and Rows.
     */
    public static function prepare_export_data_from_map( array $leads, array $meta_map ) : array {
        $meta_headers = array(
            'id'         => __( 'ID', 'leadite' ),
            'created_at' => __( 'Date', 'leadite' ),
            'source'     => __( 'Source', 'leadite' ),
            'form_name'  => __( 'Form Name', 'leadite' ),
            'status'     => __( 'Status', 'leadite' ),
            'ip'         => __( 'IP Address', 'leadite' ),
            'lang'       => __( 'Language', 'leadite' ),
            'page_url'   => __( 'Page URL', 'leadite' ),
        );
        $canonical_keys = array(
            'full_name' => __( 'Full Name', 'leadite' ),
            'email'     => __( 'Email', 'leadite' ),
            'phone'     => __( 'Phone', 'leadite' ),
            'message'   => __( 'Message', 'leadite' ),
            'company'   => __( 'Company', 'leadite' ),
            'subject'   => __( 'Subject', 'leadite' ),
        );
        $utm_keys = array(
            'utm_source'   => __( 'UTM Source', 'leadite' ),
            'utm_medium'   => __( 'UTM Medium', 'leadite' ),
            'utm_campaign' => __( 'UTM Campaign', 'leadite' ),
            'utm_term'     => __( 'UTM Term', 'leadite' ),
            'utm_content'  => __( 'UTM Content', 'leadite' ),
            'gclid'        => __( 'Gclid', 'leadite' ),
        );
        // Collect custom fields visible in this chunk.
        $custom_fields = array();
        foreach ( $leads as $lead ) {
            $fields = ( isset( $meta_map[(int) $lead->id] ) ? $meta_map[(int) $lead->id]['fields'] : array() );
            foreach ( $fields as $f ) {
                if ( 'system' === $f->field_type ) {
                    continue;
                }
                if ( !empty( $f->canonical ) && isset( $canonical_keys[$f->canonical] ) ) {
                    continue;
                }
                if ( !isset( $custom_fields[$f->field_key] ) ) {
                    $custom_fields[$f->field_key] = $f->field_label;
                }
            }
        }
        $lead_ids = array_map( function ( $l ) {
            return (int) $l->id;
        }, $leads );
        $tags_map = array();
        $notes_map = array();
        $headers = array_merge(
            array_values( $meta_headers ),
            array_values( $canonical_keys ),
            array_values( $custom_fields ),
            array_values( $utm_keys )
        );
        $rows = array();
        foreach ( $leads as $lead ) {
            $fields = ( isset( $meta_map[(int) $lead->id] ) ? $meta_map[(int) $lead->id]['fields'] : array() );
            $row = array();
            foreach ( array_keys( $meta_headers ) as $mkey ) {
                if ( 'starred' === $mkey ) {
                    $starred_val = ( !empty( $lead->starred ) ? __( 'Yes', 'leadite' ) : __( 'No', 'leadite' ) );
                    $row[] = self::sanitize_cell( $starred_val );
                } elseif ( 'tags' === $mkey ) {
                    $tags = ( isset( $tags_map[(int) $lead->id] ) ? $tags_map[(int) $lead->id] : array() );
                    $tag_names = array_map( function ( $t ) {
                        return $t->name;
                    }, $tags );
                    $row[] = self::sanitize_cell( implode( ', ', $tag_names ) );
                } elseif ( 'notes' === $mkey ) {
                    $notes = ( isset( $notes_map[(int) $lead->id] ) ? $notes_map[(int) $lead->id] : array() );
                    $note_strings = array_map( function ( $n ) {
                        return '[' . $n->created_at . '] ' . $n->note;
                    }, $notes );
                    $row[] = self::sanitize_cell( implode( "\n", $note_strings ) );
                } elseif ( 'hot_score_cached' === $mkey ) {
                    $row[] = self::sanitize_cell( ( isset( $lead->hot_score_cached ) ? (int) $lead->hot_score_cached : 0 ) );
                } elseif ( 'sla_status' === $mkey ) {
                    $row[] = self::sanitize_cell( ( isset( $lead->sla_status ) ? (string) $lead->sla_status : '' ) );
                } else {
                    $row[] = self::sanitize_cell( ( isset( $lead->{$mkey} ) ? $lead->{$mkey} : '' ) );
                }
            }
            foreach ( array_keys( $canonical_keys ) as $ckey ) {
                $row[] = self::sanitize_cell( \Leadite\Support\FieldMapper::canonical_value( $fields, $ckey ) );
            }
            foreach ( array_keys( $custom_fields ) as $cfkey ) {
                $val = '';
                foreach ( $fields as $f ) {
                    if ( $f->field_key === $cfkey ) {
                        $val = $f->field_value;
                        break;
                    }
                }
                $row[] = self::sanitize_cell( $val );
            }
            foreach ( array_keys( $utm_keys ) as $ukey ) {
                $val = '';
                foreach ( $fields as $f ) {
                    if ( 'system' === $f->field_type && $f->canonical === $ukey ) {
                        $val = $f->field_value;
                        break;
                    }
                }
                $row[] = self::sanitize_cell( $val );
            }
            $rows[] = $row;
        }
        return array($headers, $rows);
    }

}
