<?php
/**
 * Resume export from exact server table list (user-provided).
 * - Skips tables already on server
 * - Re-fills wp_ai_acc_flows safely (no GENERATED cols in INSERT)
 * - Remaining tables + GIS ≤100
 *
 * php run-acc-export-resume-exact.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';
$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;

$skipFile = __DIR__ . '/storage/backups/db/tables-already-on-server.txt';
$skip = array_fill_keys( array_filter( array_map( 'trim', file( $skipFile ) ?: [] ) ), true );
// flows exists but data failed — re-export data only (no DROP structure)
unset( $skip['wp_ai_acc_flows'] );

global $wpdb;
$all = $wpdb->get_col( $wpdb->prepare(
	'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = %s ORDER BY TABLE_NAME',
	$db
) ) ?: [];

$gis = [];
foreach ( $all as $t ) {
	if ( str_starts_with( (string) $t, $wpdb->prefix . 'gis_' ) ) {
		$gis[ (string) $t ] = true;
	}
}
$genRows = $wpdb->get_results( $wpdb->prepare(
	"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
	 WHERE TABLE_SCHEMA = %s AND EXTRA LIKE %s GROUP BY TABLE_NAME",
	$db,
	'%GENERATED%'
), ARRAY_A ) ?: [];
$genMap = [];
foreach ( $genRows as $r ) {
	$genMap[ (string) $r['TABLE_NAME'] ] = true;
}

$todo = [];
foreach ( $all as $t ) {
	$t = (string) $t;
	if ( isset( $skip[ $t ] ) ) {
		continue;
	}
	$todo[] = $t;
}

echo 'Skip on server: ' . ( count( array_filter( array_map( 'trim', file( $skipFile ) ?: [] ) ) ) ) . PHP_EOL;
echo 'Export now: ' . count( $todo ) . PHP_EOL;

$outDir = __DIR__ . '/storage/backups/db';
$ts = date( 'Ymd-His' );
$name = "hosting-resume-exact-{$ts}.sql";
$abs = $outDir . DIRECTORY_SEPARATOR . $name;

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

function ex_run( array $cmd, string $out, string $label ): void {
	echo "→ {$label}\n";
	$desc = [ 1 => [ 'file', $out, 'a' ], 2 => [ 'pipe', 'w' ] ];
	$proc = proc_open( $cmd, $desc, $pipes, null, null, [ 'bypass_shell' => true ] );
	if ( ! is_resource( $proc ) ) {
		throw new RuntimeException( $label );
	}
	$err = stream_get_contents( $pipes[2] );
	fclose( $pipes[2] );
	if ( proc_close( $proc ) !== 0 ) {
		throw new RuntimeException( trim( (string) $err ) ?: $label );
	}
}

function ex_val( $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 ) . "'";
}

/** @return list<string> */
function ex_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 ) {
		if ( str_contains( strtoupper( (string) ( $r['EXTRA'] ?? '' ) ), 'GENERATED' ) ) {
			continue;
		}
		$out[] = (string) $r['COLUMN_NAME'];
	}
	return $out;
}

function ex_data_only( string $db, string $table, string $out, ?int $rowLimit, bool $truncateFirst ): void {
	global $wpdb;
	$cols = ex_cols( $db, $table );
	if ( ! $cols ) {
		return;
	}
	$colSql = '`' . implode( '`,`', $cols ) . '`';
	$lim = $rowLimit !== null ? ( ' LIMIT ' . (int) $rowLimit ) : '';
	// phpcs:ignore
	$rows = $wpdb->get_results( "SELECT {$colSql} FROM `{$table}`{$lim}", ARRAY_A ) ?: [];

	$fh = fopen( $out, 'ab' );
	fwrite( $fh, "\n-- Data for `{$table}`\n" );
	if ( $truncateFirst ) {
		fwrite( $fh, "DELETE FROM `{$table}`;\n" );
	}
	if ( ! $rows ) {
		fwrite( $fh, "-- empty\n" );
		fclose( $fh );
		echo "→ {$table} data 0\n";
		return;
	}
	$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[] = ex_val( $row[ $c ] ?? null );
		}
		$chunk[] = '(' . implode( ',', $vals ) . ')';
		if ( count( $chunk ) >= 150 ) {
			$flush();
		}
	}
	$flush();
	fclose( $fh );
	echo "→ {$table} data " . count( $rows ) . "\n";
}

function ex_full_safe( string $mysqldump, string $cnf, string $db, string $table, string $out, ?int $rowLimit ): void {
	ex_run( [
		$mysqldump, '--defaults-extra-file=' . $cnf, '--single-transaction',
		'--default-character-set=utf8mb4', '--hex-blob', '--skip-triggers', '--no-data',
		$db, $table,
	], $out, "{$table} structure" );
	ex_data_only( $db, $table, $out, $rowLimit, false );
}

file_put_contents(
	$abs,
	"-- ACC resume from exact server inventory\n"
	. '-- ' . date( 'c' ) . "\n"
	. "SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS=0;\nSET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n\n"
);

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

foreach ( $todo as $table ) {
	$isGis = isset( $gis[ $table ] );
	$hasGen = isset( $genMap[ $table ] );

	// Special: flows already exists on server — only refill data safely
	if ( $table === 'wp_ai_acc_flows' ) {
		ex_data_only( $db, $table, $abs, null, true );
		continue;
	}

	if ( $hasGen ) {
		ex_full_safe( $mysqldump, $cnf, $db, $table, $abs, $isGis ? $limit : null );
		continue;
	}
	if ( $isGis ) {
		ex_run( array_merge( $base, [ '--where=1 LIMIT ' . $limit, $db, $table ] ), $abs, "{$table} ≤{$limit}" );
		continue;
	}
	ex_run( array_merge( $base, [ $db, $table ] ), $abs, $table );
}

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

$desktop = ( getenv( 'USERPROFILE' ) ?: '' ) . '\\Desktop\\' . $name;
@copy( $abs, $desktop );

echo json_encode( [
	'success'      => true,
	'exported'     => count( $todo ),
	'file'         => $abs,
	'desktop_copy' => is_file( $desktop ) ? $desktop : null,
	'size_mb'      => round( filesize( $abs ) / 1048576, 2 ),
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT ) . PHP_EOL;
