<?php
/**
 * Hosting SQL export:
 * - non-GIS: full
 * - GIS: max 100 rows each
 * - GENERATED columns excluded from INSERT (MySQL 8 import-safe)
 *
 * php run-acc-export-hosting.php
 */
declare(strict_types=1);

if ( PHP_SAPI !== 'cli' && PHP_SAPI !== 'phpdbg' ) {
	http_response_code( 403 );
	exit( "CLI only\n" );
}

define( 'ACC_BASE_PATH', __DIR__ );
require_once __DIR__ . '/standalone/bootstrap.php';

@set_time_limit( 0 );
@ini_set( 'memory_limit', '1024M' );

$mysqldump = 'C:\\xampp\\mysql\\bin\\mysqldump.exe';
if ( ! is_file( $mysqldump ) ) {
	fwrite( STDERR, "mysqldump not found\n" );
	exit( 1 );
}

$host  = defined( 'ACC_DB_HOST' ) ? ACC_DB_HOST : 'localhost';
$user  = defined( 'ACC_DB_USER' ) ? ACC_DB_USER : 'root';
$pass  = defined( 'ACC_DB_PASS' ) ? ACC_DB_PASS : '';
$db    = defined( 'ACC_DB_NAME' ) ? ACC_DB_NAME : 'sademlis_ostad51';
$limit = 100;

$outDir = __DIR__ . '/storage/backups/db';
if ( ! is_dir( $outDir ) ) {
	mkdir( $outDir, 0755, true );
}
$ts  = date( 'Ymd-His' );
$name = "hosting-export-gis{$limit}-{$ts}.sql";
$abs  = $outDir . DIRECTORY_SEPARATOR . $name;

$cnf = tempnam( sys_get_temp_dir(), 'accex' );
$passSafe = str_replace( [ '\\', '"' ], [ '\\\\', '\\"' ], $pass );
file_put_contents( $cnf, "[client]\nuser={$user}\npassword=\"{$passSafe}\"\nhost={$host}\n" );

global $wpdb;

/** @return list<string> */
function acc_export_gis_tables( string $db ): array {
	global $wpdb;
	$rows = $wpdb->get_col( $wpdb->prepare(
		"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
		 WHERE TABLE_SCHEMA = %s AND TABLE_NAME LIKE %s ORDER BY TABLE_NAME",
		$db,
		$wpdb->esc_like( $wpdb->prefix . 'gis_' ) . '%'
	) );
	return array_values( array_filter( array_map( 'strval', $rows ?: [] ) ) );
}

/** @return array<string, list<string>> table => generated cols */
function acc_export_generated_map( string $db ): array {
	global $wpdb;
	$rows = $wpdb->get_results( $wpdb->prepare(
		"SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
		 WHERE TABLE_SCHEMA = %s AND EXTRA LIKE %s
		 ORDER BY TABLE_NAME, ORDINAL_POSITION",
		$db,
		'%GENERATED%'
	), ARRAY_A ) ?: [];
	$map = [];
	foreach ( $rows as $r ) {
		$map[ (string) $r['TABLE_NAME'] ][] = (string) $r['COLUMN_NAME'];
	}
	return $map;
}

/** @return list<string> */
function acc_export_writable_cols( string $db, string $table ): array {
	global $wpdb;
	$rows = $wpdb->get_results( $wpdb->prepare(
		"SELECT COLUMN_NAME, EXTRA FROM INFORMATION_SCHEMA.COLUMNS
		 WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
		 ORDER BY ORDINAL_POSITION",
		$db,
		$table
	), ARRAY_A ) ?: [];
	$out = [];
	foreach ( $rows as $r ) {
		$extra = strtoupper( (string) ( $r['EXTRA'] ?? '' ) );
		if ( str_contains( $extra, 'GENERATED' ) ) {
			continue;
		}
		$out[] = (string) $r['COLUMN_NAME'];
	}
	return $out;
}

/**
 * @param list<string> $cmd
 */
function acc_export_run( array $cmd, string $outFile, string $label ): void {
	echo "→ {$label}\n";
	$descriptors = [
		1 => [ 'file', $outFile, 'a' ],
		2 => [ 'pipe', 'w' ],
	];
	$proc = proc_open( $cmd, $descriptors, $pipes, null, null, [ 'bypass_shell' => true ] );
	if ( ! is_resource( $proc ) ) {
		throw new RuntimeException( "proc_open failed: {$label}" );
	}
	$err = stream_get_contents( $pipes[2] );
	fclose( $pipes[2] );
	$code = proc_close( $proc );
	if ( $code !== 0 ) {
		throw new RuntimeException( trim( (string) $err ) !== '' ? trim( (string) $err ) : "dump failed: {$label}" );
	}
}

function acc_export_sql_value( $v ): string {
	if ( $v === null ) {
		return 'NULL';
	}
	if ( is_int( $v ) || is_float( $v ) ) {
		return (string) $v;
	}
	return "'" . str_replace( [ "\\", "'", "\0", "\n", "\r", "\x1a" ], [ '\\\\', "\\'", '\\0', '\\n', '\\r', '\\Z' ], (string) $v ) . "'";
}

/**
 * Structure via mysqldump + data INSERT without GENERATED columns.
 */
function acc_export_table_safe(
	string $mysqldump,
	string $cnf,
	string $db,
	string $table,
	string $outFile,
	?int $rowLimit = null
): void {
	$base = [
		$mysqldump,
		'--defaults-extra-file=' . $cnf,
		'--single-transaction',
		'--default-character-set=utf8mb4',
		'--hex-blob',
		'--skip-triggers',
		'--no-data',
		$db,
		$table,
	];
	acc_export_run( $base, $outFile, "{$table} structure" );

	$cols = acc_export_writable_cols( $db, $table );
	if ( ! $cols ) {
		return;
	}
	$colSql = '`' . implode( '`,`', $cols ) . '`';
	$limitSql = $rowLimit !== null ? ( ' LIMIT ' . (int) $rowLimit ) : '';

	global $wpdb;
	// phpcs:ignore
	$rows = $wpdb->get_results( "SELECT {$colSql} FROM `{$table}`{$limitSql}", ARRAY_A ) ?: [];
	if ( ! $rows ) {
		file_put_contents( $outFile, "\n-- no rows for `{$table}`\n", FILE_APPEND );
		return;
	}

	$fh = fopen( $outFile, 'ab' );
	if ( ! $fh ) {
		throw new RuntimeException( "cannot append {$table}" );
	}
	fwrite( $fh, "\n-- Data for `{$table}` (generated columns omitted)\n" );
	fwrite( $fh, "LOCK TABLES `{$table}` WRITE;\n" );
	fwrite( $fh, "/*!40000 ALTER TABLE `{$table}` DISABLE KEYS */;\n" );

	$chunk = [];
	$flush = static function () use ( &$chunk, $fh, $table, $colSql ): void {
		if ( ! $chunk ) {
			return;
		}
		fwrite( $fh, "INSERT INTO `{$table}` ({$colSql}) VALUES\n" . implode( ",\n", $chunk ) . ";\n" );
		$chunk = [];
	};

	foreach ( $rows as $row ) {
		$vals = [];
		foreach ( $cols as $c ) {
			$vals[] = acc_export_sql_value( $row[ $c ] ?? null );
		}
		$chunk[] = '(' . implode( ',', $vals ) . ')';
		if ( count( $chunk ) >= 200 ) {
			$flush();
		}
	}
	$flush();
	fwrite( $fh, "/*!40000 ALTER TABLE `{$table}` ENABLE KEYS */;\nUNLOCK TABLES;\n\n" );
	fclose( $fh );
	echo "→ {$table} data (" . count( $rows ) . " rows, safe cols)\n";
}

$gis = acc_export_gis_tables( $db );
$genMap = acc_export_generated_map( $db );
$special = array_values( array_unique( array_merge( array_keys( $genMap ), $gis ) ) );

echo 'GIS: ' . count( $gis ) . ' | generated-col tables: ' . count( $genMap ) . PHP_EOL;

file_put_contents(
	$abs,
	"-- ACC hosting export (MySQL 8 safe)\n"
	. "-- DB: {$db}\n"
	. '-- Generated: ' . date( 'c' ) . "\n"
	. "-- Non-GIS full | GIS ≤{$limit} rows | GENERATED cols omitted from INSERT\n"
	. "SET NAMES utf8mb4;\n"
	. "SET FOREIGN_KEY_CHECKS=0;\n"
	. "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n\n"
);

$base = [
	$mysqldump,
	'--defaults-extra-file=' . $cnf,
	'--single-transaction',
	'--routines',
	'--triggers',
	'--default-character-set=utf8mb4',
	'--hex-blob',
];

// Full dump excluding GIS + any table with generated columns (handled safely below)
$cmdFull = $base;
foreach ( $special as $table ) {
	$cmdFull[] = '--ignore-table=' . $db . '.' . $table;
}
$cmdFull[] = $db;
acc_export_run( $cmdFull, $abs, 'full dump (safe tables)' );

// Tables with generated columns that are NOT GIS: full data, safe insert
foreach ( array_keys( $genMap ) as $table ) {
	if ( in_array( $table, $gis, true ) ) {
		continue;
	}
	acc_export_table_safe( $mysqldump, $cnf, $db, $table, $abs, null );
}

// GIS tables (incl. those with generated cols)
foreach ( $gis as $table ) {
	if ( isset( $genMap[ $table ] ) ) {
		acc_export_table_safe( $mysqldump, $cnf, $db, $table, $abs, $limit );
	} else {
		$cmd = array_merge( $base, [
			'--skip-triggers',
			'--where=1 LIMIT ' . (int) $limit,
			$db,
			$table,
		] );
		acc_export_run( $cmd, $abs, "{$table} (≤{$limit})" );
	}
}

file_put_contents( $abs, "\nSET FOREIGN_KEY_CHECKS=1;\n", FILE_APPEND );
@unlink( $cnf );

$size = (int) filesize( $abs );
$desktop = getenv( 'USERPROFILE' ) ? ( getenv( 'USERPROFILE' ) . '\\Desktop\\' . $name ) : '';
$copied = $desktop ? @copy( $abs, $desktop ) : false;

echo json_encode( [
	'success'      => true,
	'file'         => $abs,
	'desktop_copy' => $copied ? $desktop : null,
	'size_mb'      => round( $size / 1048576, 2 ),
	'generated_tables_fixed' => array_keys( $genMap ),
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT ) . PHP_EOL;
