<?php
/**
 * Export Endpoint for BoostBot REST API.
 *
 * @package BoostBot
 */

// Prevent direct access to the file.
if (!defined('ABSPATH')) {
    exit;
}

/**
 * BoostBot_Export_Endpoint class.
 *
 * Handles data export (CSV) for leads and conversations (Admin only).
 *
 * @since 1.0.0
 */
class BoostBot_Export_Endpoint extends BoostBot_Base_Endpoint
{
    /**
     * The base path for this endpoint.
     *
     * @since 1.0.0
     * @access protected
     * @var string $rest_base
     */
    protected $rest_base = 'export';

    /**
     * Register the routes for this endpoint.
     *
     * @since 1.0.0
     */
    public function register()
    {
        // GET /wp-json/boostbot/v1/export/leads - Export leads
        $this->register_route(
            '/leads',
            'GET',
            [$this, 'export_leads'],
            [$this, 'admin_access']
        );

        // GET /wp-json/boostbot/v1/export/conversations - Export conversations
        $this->register_route(
            '/conversations',
            'GET',
            [$this, 'export_conversations'],
            [$this, 'admin_access']
        );
    }

    /**
     * Export leads to CSV.
     *
     * @since 1.0.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response|WP_Error The response or error.
     */
    public function export_leads($request)
    {
        BoostBot_Logger::debug('Exporting leads');

        $format = sanitize_text_field($request->get_param('format')) ?: 'csv';

        if (!in_array($format, ['csv'], true)) {
            return $this->error(__('Invalid format. Only CSV is supported.', 'boostbot'), 400);
        }

        // Get filters
        $filters = [];
        if ($request->has_param('status')) {
            $filters['lead_status'] = sanitize_text_field($request->get_param('status'));
        }

        $language = sanitize_text_field($request->get_param('language'));
        if (!empty($language) && $language !== 'all') {
            $filters['language'] = $language;
        }

        $search = sanitize_text_field($request->get_param('search'));

        // Get leads
        $model = new BoostBot_Lead_Model();

        if ( ! empty( $search ) ) {
            global $wpdb;
            $table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_leads' );

            $where_parts = [];
            $params      = [];

            $like = '%' . $wpdb->esc_like( $search ) . '%';
            $where_parts[] = '(name LIKE %s OR email LIKE %s OR phone LIKE %s OR company LIKE %s)';
            $params = array_merge( $params, [ $like, $like, $like, $like ] );

            if ( ! empty( $filters['lead_status'] ) ) {
                $where_parts[] = 'lead_status = %s';
                $params[]      = $filters['lead_status'];
            }
            if ( ! empty( $filters['language'] ) ) {
                $where_parts[] = 'language = %s';
                $params[]      = $filters['language'];
            }

            $where_sql = implode( ' AND ', $where_parts );
            $params[]  = 10000;

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $leads = $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY created_at DESC LIMIT %d",
                    $params
                )
            );
        } else {
            $leads = $model->get_all([
                'where'    => $filters,
                'orderby'  => 'created_at',
                'order'    => 'DESC',
                'limit'    => 10000,
            ]);
        }

        if (empty($leads)) {
            return $this->error(__('No leads to export', 'boostbot'), 404);
        }

        // Generate CSV
        $result = $this->generate_leads_csv($leads);

        if (is_wp_error($result)) {
            return $result;
        }

        BoostBot_Logger::info('Leads exported', ['count' => count($leads)]);

        return $this->success($result);
    }

    /**
     * Export conversations to CSV.
     *
     * @since 1.0.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response|WP_Error The response or error.
     */
    public function export_conversations($request)
    {
        BoostBot_Logger::debug('Exporting conversations');

        $format = sanitize_text_field($request->get_param('format')) ?: 'csv';

        if (!in_array($format, ['csv'], true)) {
            return $this->error(__('Invalid format. Only CSV is supported.', 'boostbot'), 400);
        }

        // Get conversations
        $model = new BoostBot_Conversation_Model();
        $conversations = $model->get_all([
            'order_by' => 'started_at',
            'order'    => 'DESC',
            'limit'    => 10000,
        ]);

        if (empty($conversations)) {
            return $this->error(__('No conversations to export', 'boostbot'), 404);
        }

        // Generate CSV
        $result = $this->generate_conversations_csv($conversations);

        if (is_wp_error($result)) {
            return $result;
        }

        BoostBot_Logger::info('Conversations exported', ['count' => count($conversations)]);

        return $this->success($result);
    }

    /**
     * Generate CSV file for leads.
     *
     * @since 1.0.0
     * @param array $leads Array of lead objects.
     * @return array|WP_Error Result with download URL or error.
     */
    private function generate_leads_csv($leads)
    {
        $filename = 'boostbot-leads-' . gmdate('Y-m-d-His') . '.csv';
        $upload_dir = wp_upload_dir();
        $export_dir = $upload_dir['basedir'] . '/boostbot-exports';
        $filepath = $export_dir . '/' . $filename;

        $wp_filesystem = $this->get_wp_filesystem();
        if ( ! $wp_filesystem ) {
            return $this->error(__('Failed to initialize filesystem', 'boostbot'), 500);
        }

        if ( ! wp_mkdir_p($export_dir) ) {
            return $this->error(__('Failed to create export directory', 'boostbot'), 500);
        }

        $htaccess = $export_dir . '/.htaccess';
        if ( ! $wp_filesystem->exists( $htaccess ) ) {
            $wp_filesystem->put_contents( $htaccess, "Options -Indexes\n", FS_CHMOD_FILE );
        }

        // Build CSV in a temp stream, then write to disk via WP_Filesystem.
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- php://temp is a memory stream, not a filesystem operation
        $stream = fopen( 'php://temp', 'r+' );
        if ( ! $stream ) {
            return $this->error(__('Failed to create export stream', 'boostbot'), 500);
        }

        // UTF-8 BOM for Hebrew support
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- php://temp memory stream
        fwrite( $stream, "\xEF\xBB\xBF" );

        $headers = [
            'ID',
            __('Name', 'boostbot'),
            __('Email', 'boostbot'),
            __('Phone', 'boostbot'),
            __('Company', 'boostbot'),
            __('Status', 'boostbot'),
            __('Score', 'boostbot'),
            __('Source Page', 'boostbot'),
            __('Created', 'boostbot'),
            __('Updated', 'boostbot'),
        ];
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fputcsv -- php://temp memory stream
        fputcsv( $stream, $headers );

        foreach ($leads as $lead) {
            $row = [
                $lead->id,
                $this->csv_safe($lead->name ?? ''),
                $this->csv_safe($lead->email ?? ''),
                $this->csv_safe($lead->phone ?? ''),
                $this->csv_safe($lead->company ?? ''),
                $this->translate_status($lead->lead_status),
                $lead->lead_score,
                $this->csv_safe($lead->source_page ?? ''),
                $lead->created_at,
                $lead->updated_at,
            ];
            // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fputcsv -- php://temp memory stream
            fputcsv( $stream, $row );
        }

        rewind( $stream );
        $csv_content = stream_get_contents( $stream );
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- php://temp memory stream
        fclose( $stream );

        $wp_filesystem->put_contents( $filepath, $csv_content, FS_CHMOD_FILE );

        $download_url = $upload_dir['baseurl'] . '/boostbot-exports/' . $filename;

        wp_schedule_single_event(
            time() + HOUR_IN_SECONDS,
            'boostbot_delete_export_file',
            [$filepath]
        );

        return [
            'download_url' => $download_url,
            'filename'     => $filename,
            'count'        => count($leads),
            'expires'      => gmdate('Y-m-d H:i:s', time() + HOUR_IN_SECONDS),
        ];
    }

    /**
     * Neutralize spreadsheet formula injection in a CSV cell.
     *
     * Lead fields (name/email/phone/company/source_page) come from untrusted public
     * visitors. A value beginning with =, +, -, @, tab or CR is interpreted as a
     * formula by Excel/Google Sheets; prefixing a single quote forces it to be text.
     *
     * @param mixed $value Raw cell value.
     * @return string Sanitized value safe for CSV.
     */
    private function csv_safe( $value ) {
        $value = (string) $value;
        if ( '' !== $value && preg_match( '/^[=+\-@\t\r]/', $value ) ) {
            return "'" . $value;
        }
        return $value;
    }

    /**
     * Generate CSV file for conversations.
     *
     * @since 1.0.0
     * @param array $conversations Array of conversation objects.
     * @return array|WP_Error Result with download URL or error.
     */
    private function generate_conversations_csv($conversations)
    {
        $filename = 'boostbot-conversations-' . gmdate('Y-m-d-His') . '.csv';
        $upload_dir = wp_upload_dir();
        $export_dir = $upload_dir['basedir'] . '/boostbot-exports';
        $filepath = $export_dir . '/' . $filename;

        $wp_filesystem = $this->get_wp_filesystem();
        if ( ! $wp_filesystem ) {
            return $this->error(__('Failed to initialize filesystem', 'boostbot'), 500);
        }

        if ( ! wp_mkdir_p($export_dir) ) {
            return $this->error(__('Failed to create export directory', 'boostbot'), 500);
        }

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- php://temp is a memory stream, not a filesystem operation
        $stream = fopen( 'php://temp', 'r+' );
        if ( ! $stream ) {
            return $this->error(__('Failed to create export stream', 'boostbot'), 500);
        }

        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- php://temp memory stream
        fwrite( $stream, "\xEF\xBB\xBF" );

        $headers = [
            'ID',
            __('Conversation ID', 'boostbot'),
            __('Visitor ID', 'boostbot'),
            __('Total Messages', 'boostbot'),
            __('Bot Messages', 'boostbot'),
            __('User Messages', 'boostbot'),
            __('Conversion', 'boostbot'),
            __('Satisfaction Score', 'boostbot'),
            __('Duration (seconds)', 'boostbot'),
            __('Started At', 'boostbot'),
            __('Last Message', 'boostbot'),
        ];
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fputcsv -- php://temp memory stream
        fputcsv( $stream, $headers );

        foreach ($conversations as $conv) {
            $row = [
                $conv->id,
                $this->csv_safe($conv->conversation_id),
                $this->csv_safe($conv->visitor_id),
                $conv->total_messages,
                $conv->bot_messages,
                $conv->user_messages,
                $conv->conversion_achieved ? __('Yes', 'boostbot') : __('No', 'boostbot'),
                $conv->satisfaction_score ?? '',
                $conv->duration_seconds,
                $conv->started_at,
                $conv->last_message_at,
            ];
            // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fputcsv -- php://temp memory stream
            fputcsv( $stream, $row );
        }

        rewind( $stream );
        $csv_content = stream_get_contents( $stream );
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- php://temp memory stream
        fclose( $stream );

        $wp_filesystem->put_contents( $filepath, $csv_content, FS_CHMOD_FILE );

        $download_url = $upload_dir['baseurl'] . '/boostbot-exports/' . $filename;

        wp_schedule_single_event(
            time() + HOUR_IN_SECONDS,
            'boostbot_delete_export_file',
            [$filepath]
        );

        return [
            'download_url' => $download_url,
            'filename'     => $filename,
            'count'        => count($conversations),
            'expires'      => gmdate('Y-m-d H:i:s', time() + HOUR_IN_SECONDS),
        ];
    }

    /**
     * Translate lead status to Hebrew.
     *
     * @since 1.0.0
     * @param string $status Status in English.
     * @return string Status in Hebrew.
     */
    /**
     * Initialize and return the WP_Filesystem instance.
     *
     * @return WP_Filesystem_Base|false
     */
    private function get_wp_filesystem() {
        global $wp_filesystem;
        if ( empty( $wp_filesystem ) ) {
            require_once ABSPATH . 'wp-admin/includes/file.php';
            WP_Filesystem();
        }
        return $wp_filesystem ?: false;
    }

    private function translate_status($status)
    {
        $translations = [
            'new'       => __('New', 'boostbot'),
            'contacted' => __('Contacted', 'boostbot'),
            'qualified' => __('Qualified', 'boostbot'),
            'converted' => __('Converted', 'boostbot'),
            'lost'      => __('Lost', 'boostbot'),
        ];

        return $translations[$status] ?? $status;
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}










