<?php
/**
 * Full resume for HostNegar: remaining tables INCLUDING GIS.
 * Fixes MariaDB POINT DEFAULT → MySQL-compatible CREATE.
 *
 * php run-acc-export-resume-full-hostnegar.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';
$gisLimit = 100;

$skipFile = __DIR__ . '/storage/backups/db/tables-already-on-server-v2.txt';
$skip = array_fill_keys( array_filter( array_map( 'trim', file( $skipFile ) ?: [] ) ), true );

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

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

$genMap = [];
foreach ( $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 ) ?: [] as $r ) {
	$genMap[ (string) $r['TABLE_NAME'] ] = true;
}

echo 'Skip: ' . count( $skip ) . ' | Export: ' . count( $todo ) . PHP_EOL;

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

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

function full_run_capture( array $cmd ): string {
	$desc = [ 1 => [ 'pipe', 'w' ], 2 => [ 'pipe', 'w' ] ];
	$proc = proc_open( $cmd, $desc, $pipes, null, null, [ 'bypass_shell' => true ] );
	if ( ! is_resource( $proc ) ) {
		throw new RuntimeException( 'proc_open failed' );
	}
	$out = stream_get_contents( $pipes[1] );
	$err = stream_get_contents( $pipes[2] );
	fclose( $pipes[1] );
	fclose( $pipes[2] );
	$code = proc_close( $proc );
	if ( $code !== 0 ) {
		throw new RuntimeException( trim( (string) $err ) ?: 'dump failed' );
	}
	return (string) $out;
}

function full_fix_spatial_sql( string $sql ): string {
	// MariaDB DEFAULT expressions HostNegar MySQL rejects
	$sql = preg_replace(
		"/\s+DEFAULT\s+st_geometryfromtext\('POINT\(0 0\)'\)/i",
		'',
		$sql
	) ?? $sql;
	$sql = preg_replace(
		'/\s+DEFAULT\s+ST_GeomFromText\([^\)]+\)/i',
		'',
		$sql
	) ?? $sql;
	// Safer: allow NULL if NOT NULL without default remains problematic — make point nullable
	$sql = preg_replace(
		'/`location`\s+point\s+NOT NULL/i',
		'`location` point DEFAULT NULL',
		$sql
	) ?? $sql;
	$sql = preg_replace(
		'/`geom`\s+geometry\s+NOT NULL/i',
		'`geom` geometry DEFAULT NULL',
		$sql
	) ?? $sql;
	return $sql;
}

function full_val( $v ): string {
	if ( $v === null ) {
		return 'NULL';
	}
	if ( is_int( $v ) || is_float( $v ) ) {
		return (string) $v;
	}
	// Binary/geometry from PDO may arrive as string
	if ( is_string( $v ) && ( ! mb_check_encoding( $v, 'UTF-8' ) || preg_match( '/[\x00-\x08\x0B\x0C\x0E-\x1F]/', $v ) ) ) {
		return '0x' . bin2hex( $v );
	}
	return "'" . str_replace( [ "\\", "'", "\0", "\n", "\r", "\x1a" ], [ '\\\\', "\\'", '\\0', '\\n', '\\r', '\\Z' ], $v ) . "'";
}

/** @return list<string> */
function full_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 full_append( string $file, string $sql ): void {
	file_put_contents( $file, $sql, FILE_APPEND );
}

function full_dump_table(
	string $mysqldump,
	string $cnf,
	string $db,
	string $table,
	string $out,
	bool $isGis,
	int $gisLimit,
	bool $hasGen
): void {
	global $wpdb;
	echo "→ {$table}\n";

	$structCmd = [
		$mysqldump, '--defaults-extra-file=' . $cnf, '--single-transaction',
		'--default-character-set=utf8mb4', '--hex-blob', '--skip-triggers', '--no-data',
		$db, $table,
	];
	$struct = full_fix_spatial_sql( full_run_capture( $structCmd ) );
	full_append( $out, $struct . "\n" );

	if ( $hasGen ) {
		$cols = full_cols( $db, $table );
		if ( ! $cols ) {
			return;
		}
		$colSql = '`' . implode( '`,`', $cols ) . '`';
		$lim = $isGis ? ( ' LIMIT ' . (int) $gisLimit ) : '';
		// phpcs:ignore
		$rows = $wpdb->get_results( "SELECT {$colSql} FROM `{$table}`{$lim}", ARRAY_A ) ?: [];
		if ( ! $rows ) {
			full_append( $out, "-- no rows `{$table}`\n\n" );
			return;
		}
		$chunk = [];
		$buf = "LOCK TABLES `{$table}` WRITE;\n";
		foreach ( $rows as $row ) {
			$vals = [];
			foreach ( $cols as $c ) {
				$vals[] = full_val( $row[ $c ] ?? null );
			}
			$chunk[] = '(' . implode( ',', $vals ) . ')';
			if ( count( $chunk ) >= 80 ) {
				$buf .= "INSERT INTO `{$table}` ({$colSql}) VALUES\n" . implode( ",\n", $chunk ) . ";\n";
				$chunk = [];
			}
		}
		if ( $chunk ) {
			$buf .= "INSERT INTO `{$table}` ({$colSql}) VALUES\n" . implode( ",\n", $chunk ) . ";\n";
		}
		$buf .= "UNLOCK TABLES;\n\n";
		full_append( $out, $buf );
		return;
	}

	$dataCmd = [
		$mysqldump, '--defaults-extra-file=' . $cnf, '--single-transaction',
		'--default-character-set=utf8mb4', '--hex-blob', '--skip-triggers', '--no-create-info',
	];
	if ( $isGis ) {
		$dataCmd[] = '--where=1 LIMIT ' . (int) $gisLimit;
	}
	$dataCmd[] = $db;
	$dataCmd[] = $table;
	full_append( $out, full_run_capture( $dataCmd ) . "\n" );
}

file_put_contents(
	$abs,
	"-- ACC FULL resume for HostNegar (incl. GIS, spatial defaults fixed)\n"
	. '-- ' . date( 'c' ) . "\n"
	. "-- GIS rows limited to {$gisLimit} each\n"
	. "SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS=0;\nSET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n\n"
);

foreach ( $todo as $table ) {
	$isGis = str_starts_with( $table, $wpdb->prefix . 'gis_' );
	full_dump_table(
		$mysqldump,
		$cnf,
		$db,
		$table,
		$abs,
		$isGis,
		$gisLimit,
		isset( $genMap[ $table ] )
	);
}

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

// Sanity: no bad default left
$probe = file_get_contents( $abs );
if ( $probe !== false && stripos( $probe, 'st_geometryfromtext' ) !== false ) {
	fwrite( STDERR, "WARNING: st_geometryfromtext still present — scrubbing again\n" );
	$probe = full_fix_spatial_sql( $probe );
	file_put_contents( $abs, $probe );
}

$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 ),
	'includes_gis' => true,
	'gis_row_cap'  => $gisLimit,
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT ) . PHP_EOL;
