<?php
/**
 * TitanCart Export Service — Module 13
 *
 * Generates CSV files for products, categories, orders, and customers.
 * Files are written to wp-content/uploads/titancart/exports/ and
 * cleaned up after 24 hours by a WP-Cron job.
 *
 * Usage:
 *   $svc = new ExportService();
 *   $result = $svc->generate('products', [], ['name','product_code','price']);
 *   // $result = [ 'url' => '...', 'filename' => '...', 'rows' => 142 ]
 *
 * @package TitanCart\Modules\ImportExport
 */

namespace TitanCart\Modules\ImportExport;

defined( 'ABSPATH' ) || exit;

class ExportService {

	/** Max rows fetched in a single query. */
	const CHUNK_SIZE = 500;

	// ──────────────────────────────────────────────────────────────────────────
	// Public API
	// ──────────────────────────────────────────────────────────────────────────

	/**
	 * Generate a CSV file for the given entity type and return its public URL.
	 *
	 * @param  string   $type     products|categories|orders|customers
	 * @param  array    $filters  e.g. ['status' => 'A', 'date_from' => '...']
	 * @param  string[] $columns  Column keys to include (empty = all)
	 * @return array{ url: string, filename: string, rows: int }
	 * @throws \RuntimeException
	 */
	public function generate( string $type, array $filters = [], array $columns = [] ): array {
		$this->assertValidType( $type );

		$all_columns = $this->getColumns( $type );
		if ( empty( $columns ) ) {
			$columns = array_keys( $all_columns );
		} else {
			$columns = array_intersect( $columns, array_keys( $all_columns ) );
		}

		if ( empty( $columns ) ) {
			throw new \RuntimeException( esc_html( "No valid columns specified for export type '{$type}'." ) );
		}

		$dir      = $this->exportsDir();
		$filename = $type . '_export_' . gmdate( 'Y-m-d_His' ) . '.csv';
		$filepath = $dir['path'] . '/' . $filename;

		$handle = @fopen( $filepath, 'w' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- streamed CSV export write via fputcsv; WP_Filesystem has no streaming or fputcsv equivalent.
		if ( ! $handle ) {
			throw new \RuntimeException( esc_html( "Could not create export file at: {$filepath}" ) );
		}

		// UTF-8 BOM for Excel compatibility.
		fwrite( $handle, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- BOM prefix on the streaming CSV handle.

		// Header row.
		$labels = array_map( fn( $k ) => $all_columns[ $k ] ?? $k, $columns );
		fputcsv( $handle, $labels );

		// Data rows in chunks.
		$row_count = 0;
		$offset    = 0;

		do {
			$rows  = $this->fetchRows( $type, $filters, $columns, self::CHUNK_SIZE, $offset );
			$count = count( $rows );

			foreach ( $rows as $row ) {
				fputcsv( $handle, $row );
				$row_count++;
			}

			$offset += $count;
		} while ( $count === self::CHUNK_SIZE );

		fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- closes the streaming CSV export handle.

		return [
			'url'      => $dir['url'] . '/' . $filename,
			'filename' => $filename,
			'rows'     => $row_count,
		];
	}

	/**
	 * Return all available column definitions for a given entity type.
	 *
	 * @param  string $type
	 * @return array<string, string>  [ column_key => label ]
	 */
	public function getColumns( string $type ): array {
		$maps = [
			'products' => [
				'id'                => 'ID',
				'product_code'      => 'Product Code',
				'name'              => 'Name',
				'price'             => 'Price',
				'list_price'        => 'List Price',
				'cost_price'        => 'Cost Price',
				'status'            => 'Status',
				'quantity'          => 'Stock Qty',
				'weight'            => 'Weight',
				'weight_unit'       => 'Weight Unit',
				'categories'        => 'Categories',
				'short_description' => 'Short Description',
				'description'       => 'Description',
				'free_shipping'     => 'Free Shipping',
				'shipping_freight'  => 'Shipping Freight',
				'min_qty'           => 'Min Qty',
				'max_qty'           => 'Max Qty',
				'qty_step'          => 'Qty Step',
				'tracking'          => 'Inventory Tracking',
				'product_type'      => 'Product Type',
				'slug'              => 'URL Slug',
				'position'          => 'Position',
				'images'            => 'Image URLs',
				'meta_title'        => 'Meta Title',
				'meta_description'  => 'Meta Description',
				'variation_group_code'     => 'Variation Group Code',
				'variation_set_as_default' => 'Variation Set as Default',
				'created_at'        => 'Date Created',
			],
			'categories' => [
				'id'               => 'ID',
				'name'             => 'Name',
				'parent'           => 'Parent Category',
				'status'           => 'Status',
				'description'      => 'Description',
				'meta_title'       => 'Meta Title',
				'meta_description' => 'Meta Description',
				'product_count'    => 'Product Count',
				'created_at'       => 'Date Created',
			],
			'orders' => [
				'id'              => 'Order ID',
				'status'          => 'Status',
				'email'           => 'Customer Email',
				'billing_name'    => 'Billing Name',
				'billing_address' => 'Billing Address',
				'shipping_name'   => 'Shipping Name',
				'shipping_address'=> 'Shipping Address',
				'subtotal'        => 'Subtotal',
				'discount'        => 'Discount',
				'shipping_cost'   => 'Shipping Cost',
				'tax_total'       => 'Tax',
				'total'           => 'Total',
				'currency'        => 'Currency',
				'items'           => 'Items',
				'payment_method'  => 'Payment Method',
				'notes'           => 'Customer Notes',
				'created_at'      => 'Date Created',
			],
			'customers' => [
				'id'         => 'Customer ID',
				'email'      => 'Email',
				'first_name' => 'First Name',
				'last_name'  => 'Last Name',
				'phone'      => 'Phone',
				'company'    => 'Company',
				'orders'     => 'Total Orders',
				'spent'      => 'Total Spent',
				'country'    => 'Country',
				'created_at' => 'Date Created',
			],
			'reviews' => [
				'id'           => 'Review ID',
				'product_code' => 'Product Code',
				'product_name' => 'Product Name',
				'name'         => 'Reviewer Name',
				'email'        => 'Reviewer Email',
				'rating'       => 'Rating',
				'title'        => 'Title',
				'content'      => 'Content',
				'status'       => 'Status',
				'ip_address'   => 'IP Address',
				'created_at'   => 'Date Created',
			],
			'waitlist' => [
				'id'           => 'Entry ID',
				'email'        => 'Email',
				'name'         => 'Name',
				'product_code' => 'Product Code',
				'product_name' => 'Product Name',
				'created_at'   => 'Date Created',
			],
		];

		$result = $maps[ $type ] ?? [];

		// For products: dynamically append all product attributes as exportable columns
		if ( $type === 'products' ) {
			$result = $this->appendDynamicFeatureColumns( $result );
		}

		return $result;
	}

	/**
	 * Query all active product features and append them as export columns.
	 */
	private function appendDynamicFeatureColumns( array $columns ): array {
		global $wpdb;
		$p    = $wpdb->prefix . TITANCART_TABLE_PREFIX;
		$lang = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$features = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT f.id, COALESCE(t.name, CONCAT('Attribute #', f.id)) AS name
				 FROM {$p}product_features f
				 LEFT JOIN {$p}product_feature_translations t ON t.feature_id = f.id AND t.language_code = %s
				 WHERE f.status = 'active' AND f.type != 'G'
				 ORDER BY f.position ASC, f.id ASC",
				$lang
			)
		);

		if ( $features ) {
			foreach ( $features as $f ) {
				$columns[ 'feature:' . $f->id ] = $f->name;
			}
		}

		return $columns;
	}

	/**
	 * Delete export files older than 24 hours.
	 * Called by WP-Cron on the titancart_hourly schedule.
	 */
	public function cleanupOldExports(): int {
		$dir      = $this->exportsDir();
		$cutoff   = time() - DAY_IN_SECONDS;
		$deleted  = 0;

		foreach ( glob( $dir['path'] . '/*.csv' ) ?: [] as $file ) {
			if ( filemtime( $file ) < $cutoff ) {
				wp_delete_file( $file );
				$deleted++;
			}
		}

		return $deleted;
	}

	// ──────────────────────────────────────────────────────────────────────────
	// Row fetchers per entity type
	// ──────────────────────────────────────────────────────────────────────────

	private function fetchRows( string $type, array $filters, array $columns, int $limit, int $offset ): array {
		return match ( $type ) {
			'products'   => $this->fetchProducts( $filters, $columns, $limit, $offset ),
			'categories' => $this->fetchCategories( $filters, $columns, $limit, $offset ),
			'orders'     => $this->fetchOrders( $filters, $columns, $limit, $offset ),
			'customers'  => $this->fetchCustomers( $filters, $columns, $limit, $offset ),
			'reviews'    => $this->fetchReviews( $filters, $columns, $limit, $offset ),
			'waitlist'   => $this->fetchWaitlist( $filters, $columns, $limit, $offset ),
			default      => [],
		};
	}

	private function fetchProducts( array $filters, array $columns, int $limit, int $offset ): array {
		global $wpdb;
		$p    = $wpdb->prefix . TITANCART_TABLE_PREFIX;
		$lang = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$where = [ 'p.deleted_at IS NULL' ];
		$args  = [];

		if ( ! empty( $filters['status'] ) ) {
			$where[] = 'p.status = %s';
			$args[]  = $filters['status'];
		}
		if ( ! empty( $filters['date_from'] ) ) {
			$where[] = 'p.created_at >= %s';
			$args[]  = $filters['date_from'] . ' 00:00:00';
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[] = 'p.created_at <= %s';
			$args[]  = $filters['date_to'] . ' 23:59:59';
		}
		if ( ! empty( $filters['category_ids'] ) && is_array( $filters['category_ids'] ) ) {
			$cat_placeholders = implode( ',', array_fill( 0, count( $filters['category_ids'] ), '%d' ) );
			$where[] = "p.id IN (SELECT pc2.product_id FROM {$p}product_categories pc2 WHERE pc2.category_id IN ({$cat_placeholders}))";
			$args    = array_merge( $args, array_map( 'intval', $filters['category_ids'] ) );
		}

		$where_sql = implode( ' AND ', $where );

		$sql = "SELECT p.id, p.sku, t.name, p.price, p.list_price, p.cost_price, p.status,
		               p.stock_quantity, p.weight, p.weight_unit, p.length, p.width, p.height, p.dimension_unit,
		               p.free_shipping, p.shipping_freight, p.min_quantity, p.max_quantity, p.quantity_step,
		               p.track_inventory, p.type AS product_type, p.slug, p.position,
		               t.short_desc, t.description,
		               t.meta_title, t.meta_desc, p.created_at
		          FROM {$p}products p
		     LEFT JOIN {$p}product_translations t
		                ON t.product_id = p.id AND t.language_code = %s
		         WHERE {$where_sql}
		         ORDER BY p.id ASC
		         LIMIT %d OFFSET %d";

		$prepared = $wpdb->prepare( $sql, array_merge( [ $lang ], $args, [ $limit, $offset ] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql interpolates tc_ table-prefix constants + a placeholder-built WHERE; all values bound here.
		$results  = $wpdb->get_results( $prepared ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $prepared is wpdb::prepare() output from the line above.

		$rows = [];
		foreach ( $results as $r ) {
			// Get category names.
			$cat_names = [];
			if ( in_array( 'categories', $columns, true ) ) {
				$cat_names = $wpdb->get_col(
					$wpdb->prepare(
						"SELECT ct.name FROM {$p}product_categories pc
						 JOIN {$p}category_translations ct ON ct.category_id = pc.category_id AND ct.language_code = %s
						 WHERE pc.product_id = %d",
						$lang, $r->id
					)
				);
			}

			// Get image URLs.
			$image_urls = '';
			if ( in_array( 'images', $columns, true ) ) {
				$att_ids = $wpdb->get_col(
					$wpdb->prepare(
						"SELECT wp_attachment_id FROM {$p}product_images WHERE product_id = %d ORDER BY position ASC",
						$r->id
					)
				);
				$urls = [];
				foreach ( $att_ids as $att_id ) {
					$url = wp_get_attachment_url( (int) $att_id );
					if ( $url ) $urls[] = $url;
				}
				$image_urls = implode( '|', $urls );
			}

			// Get variation group info.
			$group_code  = '';
			$is_default  = '';
			if ( in_array( 'variation_group_code', $columns, true ) || in_array( 'variation_set_as_default', $columns, true ) ) {
				$gp = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT gp.position, g.group_code
						   FROM {$p}product_group_products gp
						   JOIN {$p}product_groups g ON g.id = gp.group_id
						  WHERE gp.product_id = %d LIMIT 1",
						$r->id
					)
				);
				if ( $gp ) {
					$group_code = $gp->group_code;
					$is_default = ( (int) $gp->position === 0 ) ? 'Y' : '';
				}
			}

			$all = [
				'id'                => $r->id,
				'product_code'      => $r->sku,
				'name'              => $r->name,
				'price'             => number_format( (float) $r->price, 2, '.', '' ),
				'list_price'        => number_format( (float) $r->list_price, 2, '.', '' ),
				'cost_price'        => number_format( (float) $r->cost_price, 2, '.', '' ),
				'status'            => $r->status,
				'quantity'          => $r->stock_quantity,
				'weight'            => rtrim( rtrim( $r->weight, '0' ), '.' ) ?: '0',
				'weight_unit'       => $r->weight_unit,
				'categories'        => implode( '|', $cat_names ),
				'short_description' => $r->short_desc,
				'description'       => $r->description,
				'free_shipping'     => $r->free_shipping,
				'shipping_freight'  => number_format( (float) $r->shipping_freight, 2, '.', '' ),
				'min_qty'           => $r->min_quantity,
				'max_qty'           => $r->max_quantity,
				'qty_step'          => $r->quantity_step,
				'tracking'          => $r->track_inventory,
				'product_type'      => $r->product_type,
				'slug'              => $r->slug,
				'position'          => $r->position,
				'images'            => $image_urls,
				'meta_title'        => $r->meta_title,
				'meta_description'  => $r->meta_desc,
				'variation_group_code'     => $group_code,
				'variation_set_as_default' => $is_default,
				'created_at'        => $r->created_at,
			];

			$rows[] = array_map( fn( $k ) => $all[ $k ] ?? '', $columns );
		}

		return $rows;
	}

	private function fetchCategories( array $filters, array $columns, int $limit, int $offset ): array {
		global $wpdb;
		$p    = $wpdb->prefix . TITANCART_TABLE_PREFIX;
		$lang = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$where = [ '1=1' ];
		$args  = [ $lang, $lang ];

		if ( ! empty( $filters['status'] ) ) {
			$where[] = 'c.status = %s';
			$args[]  = $filters['status'];
		}

		if ( ! empty( $filters['category_ids'] ) && is_array( $filters['category_ids'] ) ) {
			$id_placeholders = implode( ',', array_fill( 0, count( $filters['category_ids'] ), '%d' ) );
			$where[] = "c.id IN ({$id_placeholders})";
			$args    = array_merge( $args, array_map( 'intval', $filters['category_ids'] ) );
		}

		$where_sql = implode( ' AND ', $where );

		$sql = "SELECT c.id, c.parent_id, c.status, c.created_at,
		               t.name, t.description, t.meta_title, t.meta_desc AS meta_description,
		               pt.name AS parent_name,
		               (SELECT COUNT(*) FROM {$p}product_categories pc
		                  JOIN {$p}products p ON p.id = pc.product_id
		                 WHERE pc.category_id = c.id AND p.deleted_at IS NULL
		               ) AS product_count
		          FROM {$p}categories c
		     LEFT JOIN {$p}category_translations t  ON t.category_id = c.id AND t.language_code = %s
		     LEFT JOIN {$p}category_translations pt ON pt.category_id = c.parent_id AND pt.language_code = %s
		         WHERE c.deleted_at IS NULL AND {$where_sql}
		         ORDER BY c.id ASC
		         LIMIT %d OFFSET %d";

		$prepared = $wpdb->prepare( $sql, array_merge( $args, [ $limit, $offset ] ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql interpolates tc_ table-prefix constants + a placeholder-built WHERE; all values bound here.
		$results  = $wpdb->get_results( $prepared ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $prepared is wpdb::prepare() output from the line above.

		$rows = [];
		foreach ( $results as $r ) {
			$all = [
				'id'               => $r->id,
				'name'             => $r->name,
				'parent'           => $r->parent_name ?: '',
				'status'           => $r->status,
				'description'      => $r->description,
				'meta_title'       => $r->meta_title,
				'meta_description' => $r->meta_description,
				'product_count'    => $r->product_count,
				'created_at'       => $r->created_at,
			];
			$rows[] = array_map( fn( $k ) => $all[ $k ] ?? '', $columns );
		}

		return $rows;
	}

	private function fetchOrders( array $filters, array $columns, int $limit, int $offset ): array {
		global $wpdb;
		$p = $wpdb->prefix . TITANCART_TABLE_PREFIX;

		$where = [ '1=1' ];
		$args  = [];

		// Status filter accepts the status handle (e.g. 'completed', 'pending'),
		// matching OrdersController + OrderRepository::resolve_status_id() convention.
		if ( ! empty( $filters['status'] ) ) {
			$where[] = 'os.handle = %s';
			$args[]  = $filters['status'];
		}
		if ( ! empty( $filters['date_from'] ) ) {
			$where[] = 'o.created_at >= %s';
			$args[]  = $filters['date_from'] . ' 00:00:00';
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[] = 'o.created_at <= %s';
			$args[]  = $filters['date_to'] . ' 23:59:59';
		}

		$where_sql = implode( ' AND ', $where );

		// JOIN order_statuses (status_id is the FK; handle is the canonical machine name,
		// translated name lives in order_status_translations).
		// LEFT JOIN payment_methods + payment_method_translations (payment_method_id is nullable;
		// translated method name preferred, gateway id as fallback).
		$sql = "SELECT o.*,
		               COALESCE(ost.name, os.handle) AS status_label,
		               COALESCE(NULLIF(pmt.name, ''), pm.gateway, '') AS payment_method_label
		          FROM {$p}orders o
		          JOIN {$p}order_statuses os
		            ON os.id = o.status_id
		     LEFT JOIN {$p}order_status_translations ost
		            ON ost.status_id = os.id AND ost.language_code = 'en'
		     LEFT JOIN {$p}payment_methods pm
		            ON pm.id = o.payment_method_id
		     LEFT JOIN {$p}payment_method_translations pmt
		            ON pmt.method_id = pm.id AND pmt.language_code = 'en'
		         WHERE {$where_sql}
		      ORDER BY o.id DESC
		         LIMIT %d OFFSET %d";

		$prepared = ! empty( $args )
			? $wpdb->prepare( $sql, array_merge( $args, [ $limit, $offset ] ) ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql interpolates tc_ table-prefix constants + a placeholder-built WHERE; all values bound here.
			: $wpdb->prepare( $sql, $limit, $offset ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no-filters branch: $sql's only placeholders are LIMIT/OFFSET, bound here.

		$results = $wpdb->get_results( $prepared ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $prepared is wpdb::prepare() output from above.

		$rows = [];
		foreach ( $results as $r ) {
			// Assemble flat address arrays from the denormalized billing_*/shipping_* columns
			// so formatAddressFlat() keeps working unchanged.
			$billing = [
				'first_name' => $r->billing_first_name ?? '',
				'last_name'  => $r->billing_last_name  ?? '',
				'address_1'  => $r->billing_address_1  ?? '',
				'address_2'  => $r->billing_address_2  ?? '',
				'city'       => $r->billing_city       ?? '',
				'state'      => $r->billing_state      ?? '',
				'postcode'   => $r->billing_postcode   ?? '',
				'country'    => $r->billing_country    ?? '',
			];
			$shipping = [
				'first_name' => $r->shipping_first_name ?? '',
				'last_name'  => $r->shipping_last_name  ?? '',
				'address_1'  => $r->shipping_address_1  ?? '',
				'address_2'  => $r->shipping_address_2  ?? '',
				'city'       => $r->shipping_city       ?? '',
				'state'      => $r->shipping_state      ?? '',
				'postcode'   => $r->shipping_postcode   ?? '',
				'country'    => $r->shipping_country    ?? '',
			];

			// Flatten item names.
			$item_names = [];
			if ( in_array( 'items', $columns, true ) ) {
				$items = $wpdb->get_results(
					$wpdb->prepare( "SELECT name, quantity FROM {$p}order_items WHERE order_id = %d", $r->id )
				);
				foreach ( $items as $item ) {
					$item_names[] = $item->name . ' × ' . $item->quantity;
				}
			}

			// KI-97 (S425): the gateway row may have been deleted since the order was
			// placed, in which case the join above resolves to ''. Fall back to the
			// payment_info snapshot captured at checkout so exporting historical
			// orders never loses the method name. Decoded here in PHP, AFTER the
			// fetch — deliberately not as a JSON_* expression inside the SELECT:
			// S424 did that in OrderManager's shared column helper and one failing
			// expression returned zero orders on both stores. A malformed or absent
			// snapshot here just leaves the '' that was already there.
			$payment_label = (string) ( $r->payment_method_label ?? '' );
			if ( $payment_label === '' && ! empty( $r->payment_info ) ) {
				$psnap = json_decode( (string) $r->payment_info, true );
				if ( is_array( $psnap ) && ! empty( $psnap['name'] ) ) {
					$payment_label = (string) $psnap['name'];
				}
			}

			$all = [
				'id'              => $r->id,
				'status'          => $r->status_label,
				'email'           => $r->billing_email ?? '',
				'billing_name'    => trim( $billing['first_name'] . ' ' . $billing['last_name'] ),
				'billing_address' => $this->formatAddressFlat( $billing ),
				'shipping_name'   => trim( $shipping['first_name'] . ' ' . $shipping['last_name'] ),
				'shipping_address'=> $this->formatAddressFlat( $shipping ),
				'subtotal'        => $r->subtotal,
				'discount'        => $r->discount ?? 0,
				'shipping_cost'   => $r->shipping_cost ?? 0,
				'tax_total'       => $r->tax_total ?? 0,
				'total'           => $r->total,
				'currency'        => $r->currency ?? '',
				'items'           => implode( '; ', $item_names ),
				'payment_method'  => $payment_label,
				'notes'           => $r->customer_note ?? '',
				'created_at'      => $r->created_at,
			];

			$rows[] = array_map( fn( $k ) => $all[ $k ] ?? '', $columns );
		}

		return $rows;
	}

	private function fetchCustomers( array $filters, array $columns, int $limit, int $offset ): array {
		global $wpdb;
		$p = $wpdb->prefix . TITANCART_TABLE_PREFIX;

		$where = [ '1=1' ];
		$args  = [];

		if ( ! empty( $filters['date_from'] ) ) {
			$where[] = 'c.created_at >= %s';
			$args[]  = $filters['date_from'] . ' 00:00:00';
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[] = 'c.created_at <= %s';
			$args[]  = $filters['date_to'] . ' 23:59:59';
		}

		$where_sql = implode( ' AND ', $where );

		// FK is tc_orders.customer_id → tc_customers.id (no user_id column on either side).
		// "Total Spent" semantic preserved as completed-orders-only via JOIN to order_statuses
		// on handle='completed'. Aliased to total_spent_calc to avoid collision with the
		// denormalized tc_customers.total_spent column brought in by SELECT c.*.
		$sql = "SELECT c.*,
		               (SELECT COUNT(*)
		                  FROM {$p}orders o
		                 WHERE o.customer_id = c.id) AS order_count,
		               (SELECT COALESCE(SUM(o.total), 0)
		                  FROM {$p}orders o
		                  JOIN {$p}order_statuses os ON os.id = o.status_id
		                 WHERE o.customer_id = c.id AND os.handle = 'completed') AS total_spent_calc
		          FROM {$p}customers c
		         WHERE {$where_sql}
		         ORDER BY c.id ASC
		         LIMIT %d OFFSET %d";

		$prepared = ! empty( $args )
			? $wpdb->prepare( $sql, array_merge( $args, [ $limit, $offset ] ) ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql interpolates tc_ table-prefix constants + a placeholder-built WHERE; all values bound here.
			: $wpdb->prepare( $sql, $limit, $offset ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no-filters branch: $sql's only placeholders are LIMIT/OFFSET, bound here.

		$results = $wpdb->get_results( $prepared ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $prepared is wpdb::prepare() output from above.

		$rows = [];
		foreach ( $results as $r ) {
			// Default-address lookup serves both country and company (parallel pattern).
			// Note: tc_customers has no company column — it lives only on tc_customer_addresses,
			// so the customer's "Company" export field reflects their default address.
			$country = '';
			$company = '';
			$need_country = in_array( 'country', $columns, true );
			$need_company = in_array( 'company', $columns, true );
			if ( $need_country || $need_company ) {
				$addr = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT country, company FROM {$p}customer_addresses WHERE customer_id = %d AND is_default = 1 LIMIT 1",
						$r->id
					)
				);
				if ( $addr ) {
					$country = $addr->country ?? '';
					$company = $addr->company ?? '';
				}
			}

			$all = [
				'id'         => $r->id,
				'email'      => $r->email,
				'first_name' => $r->first_name,
				'last_name'  => $r->last_name,
				'phone'      => $r->phone,
				'company'    => $company,
				'orders'     => $r->order_count,
				'spent'      => number_format( (float) $r->total_spent_calc, 2, '.', '' ),
				'country'    => $country,
				'created_at' => $r->created_at,
			];

			$rows[] = array_map( fn( $k ) => $all[ $k ] ?? '', $columns );
		}

		return $rows;
	}

	// ──────────────────────────────────────────────────────────────────────────
	// Helpers
	// ──────────────────────────────────────────────────────────────────────────

	private function formatAddressFlat( array $addr ): string {
		$parts = array_filter( [
			$addr['address_1'] ?? '',
			$addr['address_2'] ?? '',
			$addr['city']      ?? '',
			$addr['state']     ?? '',
			$addr['postcode']  ?? '',
			$addr['country']   ?? '',
		] );
		return implode( ', ', $parts );
	}

	/**
	 * Fetch review rows for CSV export.
	 *
	 * Canonical table is `tc_reviews` (Schema.php::reviews() L1800), NOT `tc_product_reviews`.
	 * Reviewer identity (name + email) lives on `tc_customers` via `r.customer_id` FK —
	 * the legacy `name`/`email` columns this method previously SELECTed never existed
	 * on the canonical schema; pre-S208 every reviews CSV export silently returned
	 * blank name/email columns because the wpdb query failed on the wrong table name.
	 *
	 * Pattern #176 (S205) sharpened at-risk-class match: reviews export is a
	 * peripheral module NOT exercised by routine QA, so the same column drift class
	 * as ImportService's writeOrder/writeOrderItem (S206/S207) accumulated unnoticed.
	 *
	 * @since S208 — table name fix (product_reviews → reviews), customer JOIN for
	 *               reviewer identity, content alias from canonical `body` column,
	 *               $lang switched to %s placeholder (security side-fix).
	 */
	private function fetchReviews( array $filters, array $columns, int $limit, int $offset ): array {
		global $wpdb;
		$p    = $wpdb->prefix . TITANCART_TABLE_PREFIX;
		$lang = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$where = [];
		$args  = [];

		// $lang is the FIRST placeholder in the SQL (product_translations JOIN);
		// $args is pre-seeded so prepare() bind order matches placeholder order.
		$args[] = $lang;

		if ( ! empty( $filters['status'] ) ) {
			$where[] = 'r.status = %s';
			$args[]  = $filters['status'];
		}
		if ( ! empty( $filters['date_from'] ) ) {
			$where[] = 'r.created_at >= %s';
			$args[]  = $filters['date_from'] . ' 00:00:00';
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[] = 'r.created_at <= %s';
			$args[]  = $filters['date_to'] . ' 23:59:59';
		}

		$where_sql = $where ? 'WHERE ' . implode( ' AND ', $where ) : '';

		// COALESCE(prod.sku, '') + COALESCE(pt.name, '') preserved from pre-S208 shape.
		// `TRIM(CONCAT(...))` composes reviewer name from customer first/last; NULL
		// customer_id (anonymous review) yields empty string via the COALESCE wrappers.
		// `r.body AS content` aliases the canonical column to the CSV key the
		// downstream array_map loop reads from getColumns()['reviews'].
		$sql = "SELECT r.id, r.product_id, r.rating, r.title, r.status,
		               r.ip_address, r.created_at,
		               r.body AS content,
		               TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))) AS name,
		               COALESCE(c.email, '') AS email,
		               COALESCE(prod.sku, '') AS product_code,
		               COALESCE(pt.name, '') AS product_name
		        FROM {$p}reviews r
		        LEFT JOIN {$p}customers c ON c.id = r.customer_id
		        LEFT JOIN {$p}products prod ON prod.id = r.product_id
		        LEFT JOIN {$p}product_translations pt ON pt.product_id = r.product_id AND pt.language_code = %s
		        {$where_sql}
		        ORDER BY r.created_at DESC LIMIT %d OFFSET %d";

		$args[] = $limit;
		$args[] = $offset;

		$raw = $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql interpolates tc_ table-prefix constants + a placeholder-built WHERE; all values bound via prepare() here.

		return array_map( function ( $r ) use ( $columns ) {
			$row = [];
			foreach ( $columns as $col ) {
				$row[ $col ] = $r[ $col ] ?? '';
			}
			return $row;
		}, $raw ?: [] );
	}

	/**
	 * Fetch waitlist rows for CSV export.
	 *
	 * Canonical table is `tc_product_waitlist` (Schema.php::product_waitlist() L879),
	 * NOT `tc_waitlist`. Pre-S208 every waitlist CSV export silently returned zero rows
	 * because the wpdb query failed on the wrong table name.
	 *
	 * Same Pattern #176 sharpened at-risk-class match as fetchReviews — waitlist
	 * export is peripheral and not exercised by routine QA.
	 *
	 * Columns happen to all match canonical (id, email, name, product_id, created_at)
	 * so this fix is table-rename only.
	 *
	 * @since S208 — table name fix (waitlist → product_waitlist), $lang switched to
	 *               %s placeholder (security side-fix).
	 */
	private function fetchWaitlist( array $filters, array $columns, int $limit, int $offset ): array {
		global $wpdb;
		$p    = $wpdb->prefix . TITANCART_TABLE_PREFIX;
		$lang = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$where = [];
		$args  = [];

		// $lang is the FIRST placeholder in the SQL (product_translations JOIN);
		// $args is pre-seeded so prepare() bind order matches placeholder order.
		$args[] = $lang;

		if ( ! empty( $filters['date_from'] ) ) {
			$where[] = 'w.created_at >= %s';
			$args[]  = $filters['date_from'] . ' 00:00:00';
		}
		if ( ! empty( $filters['date_to'] ) ) {
			$where[] = 'w.created_at <= %s';
			$args[]  = $filters['date_to'] . ' 23:59:59';
		}

		$where_sql = $where ? 'WHERE ' . implode( ' AND ', $where ) : '';
		$sql = "SELECT w.id, w.email, w.name, w.product_id, w.created_at,
		               COALESCE(prod.sku, '') AS product_code,
		               COALESCE(pt.name, '') AS product_name
		        FROM {$p}product_waitlist w
		        LEFT JOIN {$p}products prod ON prod.id = w.product_id
		        LEFT JOIN {$p}product_translations pt ON pt.product_id = w.product_id AND pt.language_code = %s
		        {$where_sql}
		        ORDER BY w.created_at DESC LIMIT %d OFFSET %d";

		$args[] = $limit;
		$args[] = $offset;

		$raw = $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql interpolates tc_ table-prefix constants + a placeholder-built WHERE; all values bound via prepare() here.

		return array_map( function ( $r ) use ( $columns ) {
			$row = [];
			foreach ( $columns as $col ) {
				$row[ $col ] = $r[ $col ] ?? '';
			}
			return $row;
		}, $raw ?: [] );
	}

	private function exportsDir(): array {
		$upload = wp_upload_dir();
		$path   = $upload['basedir'] . '/titancart/exports';
		$url    = $upload['baseurl'] . '/titancart/exports';

		if ( ! is_dir( $path ) ) {
			wp_mkdir_p( $path );
			// Block direct HTTP access to the exports directory.
			file_put_contents( $path . '/.htaccess', "Options -Indexes\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- tiny self-owned .htaccess in the exports dir; WP_Filesystem init is disproportionate in REST context.
		}

		return [ 'path' => $path, 'url' => $url ];
	}

	private function assertValidType( string $type ): void {
		if ( ! in_array( $type, [ 'products', 'categories', 'orders', 'customers', 'reviews', 'waitlist' ], true ) ) {
			throw new \InvalidArgumentException( esc_html( "Invalid export type: {$type}" ) );
		}
	}
}
