<?php
/**
 * Export Implementations Trait
 *
 * Provides CSV and SQL export functionality for analytics data.
 *
 * @package Opti_Behavior
 * @copyright 2025 OptiUser
 * @version 1.0.3
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * Export Implementations Trait
 *
 * Provides methods for exporting analytics data in CSV and SQL formats.
 *
 * @since 1.0.0
 */
trait Opti_Behavior_Exports_Trait {

	/**
	 * Stream CSV export of analytics tables.
	 *
	 * Exports all analytics tables to a CSV file for download.
	 *
	 * @since 1.0.0
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function export_analytics_csv_impl() {
		if ( ! headers_sent() ) {
			header( 'Content-Type: application/octet-stream' );
			header( 'Content-Disposition: attachment; filename="opti-behavior-analytics-export.csv"' );
			header( 'X-Content-Type-Options: nosniff' );
			header( 'Cache-Control: no-store, no-cache, must-revalidate, max-age=0' );
			header( 'Pragma: no-cache' );
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$out = fopen( 'php://output', 'w' );
		global $wpdb;

		fputcsv( $out, array( 'TABLE', 'id', 'cols...' ) );

		$tables = array(
			'optibehavior_pages',
			'optibehavior_visitors',
			'optibehavior_sessions',
			'optibehavior_pageviews',
			'optibehavior_events',
			'optibehavior_recordings',
			'optibehavior_referrers',
			'optibehavior_outbound_clicks',
		);

		foreach ( $tables as $t ) {
			$safe_table = esc_sql( $wpdb->prefix . $t );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results( "SELECT * FROM {$safe_table}", ARRAY_A );

			foreach ( $rows as $r ) {
				fputcsv( $out, array_merge( array( $t ), array_values( $r ) ) );
			}
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		fclose( $out );
	}

	/**
	 * Stream SQL export of analytics tables.
	 *
	 * Exports all analytics tables to a SQL file for download.
	 *
	 * @since 1.0.0
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function export_analytics_sql_impl() {
		if ( ! headers_sent() ) {
			header( 'Content-Type: application/octet-stream' );
			header( 'Content-Disposition: attachment; filename="opti-behavior-analytics-export.sql"' );
			header( 'X-Content-Type-Options: nosniff' );
			header( 'Cache-Control: no-store, no-cache, must-revalidate, max-age=0' );
			header( 'Pragma: no-cache' );
		}

		global $wpdb;
		$prefix = $wpdb->prefix;

		$tables = array(
			'optibehavior_pages',
			'optibehavior_visitors',
			'optibehavior_sessions',
			'optibehavior_pageviews',
			'optibehavior_events',
			'optibehavior_recordings',
			'optibehavior_referrers',
			'optibehavior_outbound_clicks',
		);

		echo "BEGIN;\n";

		foreach ( $tables as $t ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results( "SELECT * FROM {$prefix}{$t}", ARRAY_A );

			foreach ( $rows as $r ) {
				$cols = array_map(
					function ( $c ) {
						return '`' . $c . '`';
					},
					array_keys( $r )
				);
				$vals = array_map(
					function ( $v ) use ( $wpdb ) {
						return is_null( $v ) ? 'NULL' : ( "'" . esc_sql( $v ) . "'" );
					},
					array_values( $r )
				);
				// phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
				echo "INSERT INTO `{$prefix}{$t}` (" . implode( ',', $cols ) . ') VALUES (' . implode( ',', $vals ) . ");\n";
			}
		}

		echo "COMMIT;\n";
	}
}

