Initial commit

ZnoteX / Commit #5

Commit Initial commit

Alex committed 01/10/2026 09:20 main Full upload

481 files +128,311 -0

All commits Browse code

A admin/modules/convert.php +2,811-0 View file

Large diff collapsed. 2,812 diff lines hidden.

		@@ -0,0 +1,2811 @@
1	+	<?php
2	+	/**
3	+	* Title: Convert SQL
4	+	* Icon: fa-exchange
5	+	* Group: Settings
6	+	* Order: 40
7	+	* Description: Upload a MyAAC or Gesior2012 SQL dump and download a ZnoteX conversion SQL.
8	+	* Tested with a clean MyAAC SQL (31 players test) + custom tables inserted
9	+	*/
10	+
11	+	if (!defined('ACP_ROOT')) {
12	+	http_response_code(403);
13	+	die('Direct access denied.');
14	+	}
15	+
16	+	function acp_convert_identifier(string $name): string {
17	+	if (!preg_match('/^[a-zA-Z0-9_]+$/', $name)) {
18	+	throw new InvalidArgumentException('Unsafe SQL identifier: ' . $name);
19	+	}
20	+	return $name;
21	+	}
22	+
23	+	function acp_convert_table_exists(string $table): bool {
24	+	$escaped = db()->connection()->real_escape_string($table);
25	+	return db()->rawFetchOne("SHOW TABLES LIKE '{$escaped}';") !== false;
26	+	}
27	+
28	+	function acp_convert_column_exists(string $table, string $column): bool {
29	+	$escaped = db()->connection()->real_escape_string($column);
30	+	return db()->rawFetchOne(
31	+	'SHOW COLUMNS FROM `' . acp_convert_identifier($table) . "` LIKE '{$escaped}';"
32	+	) !== false;
33	+	}
34	+
35	+	function acp_convert_count(string $table, string $where = '1=1'): int {
36	+	if (!acp_convert_table_exists($table)) {
37	+	return 0;
38	+	}
39	+	return acp_count('SELECT COUNT(*) AS `c` FROM `' . acp_convert_identifier($table) . "` WHERE {$where};");
40	+	}
41	+
42	+	function acp_convert_scalar(string $sql, string $key = 'v') {
43	+	$row = db()->fetchOne($sql);
44	+	return is_array($row) ? ($row[$key] ?? reset($row)) : null;
45	+	}
46	+
47	+	function acp_convert_insert(string $table, array $data): bool {
48	+	$fields = [];
49	+	$params = [];
50	+	foreach ($data as $key => $value) {
51	+	$fields[] = '`' . acp_convert_identifier((string)$key) . '`';
52	+	$params[] = $value;
53	+	}
54	+	$placeholders = implode(', ', array_fill(0, count($params), '?'));
55	+	return db()->execute(
56	+	'INSERT INTO `' . acp_convert_identifier($table) . '` (' . implode(', ', $fields) . ") VALUES ({$placeholders});",
57	+	$params
58	+	);
59	+	}
60	+
61	+	function acp_convert_ensure_tables(): void {
62	+	db()->execute("
63	+	CREATE TABLE IF NOT EXISTS `znote_pages` (
64	+	`id` int NOT NULL AUTO_INCREMENT,
65	+	`slug` varchar(64) NOT NULL,
66	+	`title` varchar(100) NOT NULL,
67	+	`body` mediumtext NOT NULL,
68	+	`created` int NOT NULL DEFAULT '0',
69	+	`updated` int NOT NULL DEFAULT '0',
70	+	`player_id` int NOT NULL DEFAULT '0',
71	+	`access` tinyint NOT NULL DEFAULT '0',
72	+	`active` tinyint NOT NULL DEFAULT '1',
73	+	PRIMARY KEY (`id`),
74	+	UNIQUE KEY `slug` (`slug`)
75	+	) ENGINE=InnoDB;
76	+	");
77	+
78	+	db()->execute("
79	+	CREATE TABLE IF NOT EXISTS `znote_convert_map` (
80	+	`id` int NOT NULL AUTO_INCREMENT,
81	+	`source` varchar(32) NOT NULL,
82	+	`source_table` varchar(64) NOT NULL,
83	+	`source_id` varchar(64) NOT NULL,
84	+	`target_table` varchar(64) NOT NULL,
85	+	`target_id` int NOT NULL,
86	+	`created` int NOT NULL,
87	+	PRIMARY KEY (`id`),
88	+	UNIQUE KEY `source_row` (`source`, `source_table`, `source_id`, `target_table`)
89	+	) ENGINE=InnoDB;
90	+	");
91	+
92	+	db()->execute("
93	+	CREATE TABLE IF NOT EXISTS `znote_legacy_tables` (
94	+	`id` int NOT NULL AUTO_INCREMENT,
95	+	`source` varchar(32) NOT NULL,
96	+	`table_name` varchar(64) NOT NULL,
97	+	`schema_sql` longtext NOT NULL,
98	+	`row_count` int NOT NULL DEFAULT '0',
99	+	`captured` int NOT NULL,
100	+	PRIMARY KEY (`id`),
101	+	UNIQUE KEY `source_table` (`source`, `table_name`)
102	+	) ENGINE=InnoDB;
103	+	");
104	+
105	+	db()->execute("
106	+	CREATE TABLE IF NOT EXISTS `znote_legacy_rows` (
107	+	`id` bigint NOT NULL AUTO_INCREMENT,
108	+	`source` varchar(32) NOT NULL,
109	+	`table_name` varchar(64) NOT NULL,
110	+	`source_pk` varchar(128) NOT NULL DEFAULT '',
111	+	`row_json` longtext NOT NULL,
112	+	`captured` int NOT NULL,
113	+	PRIMARY KEY (`id`),
114	+	KEY `source_table` (`source`, `table_name`),
115	+	KEY `source_pk` (`source`, `table_name`, `source_pk`)
116	+	) ENGINE=InnoDB;
117	+	");
118	+
119	+	if (!acp_convert_column_exists('znote_legacy_tables', 'schema_sql')) {
120	+	db()->execute("
121	+	ALTER TABLE `znote_legacy_tables`
122	+	ADD `schema_sql` longtext NULL AFTER `table_name`;
123	+	");
124	+	}
125	+	}
126	+
127	+	function acp_convert_slug(string $value, string $fallback): string {
128	+	$value = strtolower(trim($value));
129	+	$value = preg_replace('/[^a-z0-9_-]+/', '-', $value);
130	+	$value = trim((string)$value, '-_');
131	+	if ($value === '') {
132	+	$value = $fallback;
133	+	}
134	+	return substr($value, 0, 64);
135	+	}
136	+
137	+	function acp_convert_map_get(string $source, string $sourceTable, $sourceId, string $targetTable): int {
138	+	if (!acp_convert_table_exists('znote_convert_map')) {
139	+	return 0;
140	+	}
141	+	$row = db()->fetchOne("
142	+	SELECT `target_id`
143	+	FROM `znote_convert_map`
144	+	WHERE `source` = ?
145	+	AND `source_table` = ?
146	+	AND `source_id` = ?
147	+	AND `target_table` = ?
148	+	LIMIT 1;
149	+	", [$source, $sourceTable, (string)$sourceId, $targetTable]);
150	+	return is_array($row) ? (int)$row['target_id'] : 0;
151	+	}
152	+
153	+	function acp_convert_map_set(string $source, string $sourceTable, $sourceId, string $targetTable, int $targetId): void {
154	+	if ($targetId <= 0 \|\| !acp_convert_table_exists('znote_convert_map')) {
155	+	return;
156	+	}
157	+	db()->execute("
158	+	INSERT INTO `znote_convert_map`
159	+	(`source`, `source_table`, `source_id`, `target_table`, `target_id`, `created`)
160	+	VALUES (?, ?, ?, ?, ?, ?)
161	+	ON DUPLICATE KEY UPDATE `target_id` = VALUES(`target_id`);
162	+	", [$source, $sourceTable, (string)$sourceId, $targetTable, $targetId, time()]);
163	+	}
164	+
165	+	function acp_convert_config_set(string $key, string $value): bool {
166	+	if (!acp_convert_table_exists('znote_config')) {
167	+	return false;
168	+	}
169	+	return db()->execute("
170	+	INSERT INTO `znote_config` (`key`, `value`)
171	+	VALUES (?, ?)
172	+	ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);
173	+	", [substr($key, 0, 64), $value]);
174	+	}
175	+
176	+	function acp_convert_legacy_tables(string $source): array {
177	+	$prefix = $source === 'myaac' ? 'myaac_' : 'z_';
178	+	$rows = db()->fetchAll('SHOW TABLES;') ?: [];
179	+	$tables = [];
180	+
181	+	foreach ($rows as $row) {
182	+	$table = (string)reset($row);
183	+	if (strpos($table, $prefix) === 0) {
184	+	$tables[] = $table;
185	+	}
186	+	}
187	+
188	+	sort($tables);
189	+	return $tables;
190	+	}
191	+
192	+	function acp_convert_row_pk(string $table, array $row): string {
193	+	foreach (['id', 'account_id', 'player_id', 'name', 'key'] as $key) {
194	+	if (array_key_exists($key, $row)) {
195	+	return (string)$row[$key];
196	+	}
197	+	}
198	+	return substr(sha1($table . '\|' . json_encode($row)), 0, 40);
199	+	}
200	+
201	+	function acp_convert_archive_legacy(string $source): int {
202	+	acp_convert_ensure_tables();
203	+	$archived = 0;
204	+	$captured = time();
205	+
206	+	foreach (acp_convert_legacy_tables($source) as $table) {
207	+	$count = acp_convert_count($table);
208	+	db()->execute("
209	+	INSERT INTO `znote_legacy_tables` (`source`, `table_name`, `schema_sql`, `row_count`, `captured`)
210	+	VALUES (?, ?, '', ?, ?)
211	+	ON DUPLICATE KEY UPDATE `schema_sql` = VALUES(`schema_sql`), `row_count` = VALUES(`row_count`), `captured` = VALUES(`captured`);
212	+	", [$source, $table, $count, $captured]);
213	+	db()->execute("
214	+	DELETE FROM `znote_legacy_rows`
215	+	WHERE `source` = ?
216	+	AND `table_name` = ?;
217	+	", [$source, $table]);
218	+
219	+	$rows = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($table) . '`;') ?: [];
220	+	foreach ($rows as $row) {
221	+	$pk = acp_convert_row_pk($table, $row);
222	+	$json = json_encode($row, JSON_UNESCAPED_SLASHES \| JSON_UNESCAPED_UNICODE);
223	+	if ($json === false) {
224	+	$json = '{}';
225	+	}
226	+	if (acp_convert_insert('znote_legacy_rows', [
227	+	'source' => $source,
228	+	'table_name' => $table,
229	+	'source_pk' => substr($pk, 0, 128),
230	+	'row_json' => $json,
231	+	'captured' => $captured,
232	+	])) {
233	+	$archived++;
234	+	}
235	+	}
236	+	}
237	+
238	+	return $archived;
239	+	}
240	+
241	+	function acp_convert_sql_literal($value): string {
242	+	return $value === null ? 'NULL' : "'" . esc((string)$value) . "'";
243	+	}
244	+
245	+	function acp_convert_menu_category_labels(int $category): array {
246	+	return match ($category) {
247	+	1 => ['Home', 'News', 'Latest News'],
248	+	2 => ['Account'],
249	+	3 => ['Community'],
250	+	4 => ['Community', 'Forum'],
251	+	5 => ['Library'],
252	+	6 => ['Shop'],
253	+	default => [],
254	+	};
255	+	}
256	+
257	+	function acp_convert_dump_menu_parent_sql(int $category): string {
258	+	$labels = acp_convert_menu_category_labels($category);
259	+	if (!$labels) {
260	+	return 'NULL';
261	+	}
262	+
263	+	$literals = array_map('acp_convert_sql_literal', $labels);
264	+	return "(SELECT `p`.`id` FROM `znote_menu` AS `p`
265	+	WHERE `p`.`location` = 'main'
266	+	AND `p`.`parent_id` = 0
267	+	AND `p`.`label` IN (" . implode(', ', $literals) . ")
268	+	ORDER BY FIELD(`p`.`label`, " . implode(', ', $literals) . ")
269	+	LIMIT 1)";
270	+	}
271	+
272	+	function acp_convert_menu_parent_id(int $category): int {
273	+	$labels = acp_convert_menu_category_labels($category);
274	+	if (!$labels) {
275	+	return 0;
276	+	}
277	+
278	+	$literals = array_map('acp_convert_sql_literal', $labels);
279	+	return (int)acp_convert_scalar("SELECT `id` AS `v`
280	+	FROM `znote_menu`
281	+	WHERE `location` = 'main'
282	+	AND `parent_id` = 0
283	+	AND `label` IN (" . implode(', ', $literals) . ")
284	+	ORDER BY FIELD(`label`, " . implode(', ', $literals) . ")
285	+	LIMIT 1;");
286	+	}
287	+
288	+	function acp_convert_dump_skip_comment(string $sql, int $index, int $len) {
289	+	$ch = $sql[$index];
290	+	$next = ($index + 1 < $len) ? $sql[$index + 1] : '';
291	+
292	+	if (($ch === '-' && $next === '-') \|\| $ch === '#') {
293	+	while ($index < $len && $sql[$index] !== "\n") $index++;
294	+	return $index;
295	+	}
296	+
297	+	if ($ch === '/' && $next === '*') {
298	+	$index += 2;
299	+	while ($index + 1 < $len && !($sql[$index] === '*' && $sql[$index + 1] === '/')) $index++;
300	+	return $index + 1;
301	+	}
302	+
303	+	return false;
304	+	}
305	+
306	+	function acp_convert_dump_consume_quoted_char(string $sql, int &$index, int $len, string $quote, string &$buf): string {
307	+	$ch = $sql[$index];
308	+	$next = ($index + 1 < $len) ? $sql[$index + 1] : '';
309	+
310	+	if ($ch === '\\') {
311	+	if ($index + 1 < $len) {
312	+	$buf .= $sql[++$index];
313	+	}
314	+	return $quote;
315	+	}
316	+
317	+	if ($ch !== $quote) {
318	+	return $quote;
319	+	}
320	+
321	+	if ($quote === "'" && $next === "'") {
322	+	$buf .= $sql[++$index];
323	+	return $quote;
324	+	}
325	+
326	+	return '';
327	+	}
328	+
329	+	function acp_convert_dump_push_statement(array &$statements, string &$buf): void {
330	+	$statement = trim(substr($buf, 0, -1));
331	+	if ($statement !== '') {
332	+	$statements[] = $statement;
333	+	}
334	+	$buf = '';
335	+	}
336	+
337	+	function acp_convert_dump_split(string $sql): array {
338	+	$statements = [];
339	+	$buf = '';
340	+	$quote = '';
341	+	$len = strlen($sql);
342	+
343	+	for ($i = 0; $i < $len; $i++) {
344	+	if ($quote === '') {
345	+	$commentEnd = acp_convert_dump_skip_comment($sql, $i, $len);
346	+	if ($commentEnd !== false) {
347	+	$i = $commentEnd;
348	+	continue;
349	+	}
350	+	}
351	+
352	+	$ch = $sql[$i];
353	+	$buf .= $ch;
354	+
355	+	if ($quote !== '') {
356	+	$quote = acp_convert_dump_consume_quoted_char($sql, $i, $len, $quote, $buf);
357	+	continue;
358	+	}
359	+
360	+	if ($ch === "'" \|\| $ch === '"' \|\| $ch === '`') {
361	+	$quote = $ch;
362	+	continue;
363	+	}
364	+
365	+	if ($ch === ';') {
366	+	acp_convert_dump_push_statement($statements, $buf);
367	+	}
368	+	}
369	+
370	+	$tail = trim($buf);
371	+	if ($tail !== '') {
372	+	$statements[] = $tail;
373	+	}
374	+
375	+	return $statements;
376	+	}
377	+
378	+	function acp_convert_dump_columns(string $statement): array {
379	+	if (!preg_match('/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([a-zA-Z0-9_]+)`?\s$(.)$\s*(?:ENGINE\|DEFAULT\|CHARSET\|COLLATE\|$)/is', $statement, $m)) {
380	+	return [];
381	+	}
382	+
383	+	$columns = [];
384	+	foreach (preg_split('/\R/', $m[2]) ?: [] as $line) {
385	+	if (preg_match('/^\s*`([^`]+)`/', $line, $cm)) {
386	+	$columns[] = $cm[1];
387	+	}
388	+	}
389	+
390	+	return [$m[1], $columns];
391	+	}
392	+
393	+	function acp_convert_dump_column_defs(string $statement): array {
394	+	if (!preg_match('/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([a-zA-Z0-9_]+)`?\s$(.)$\s*(?:ENGINE\|DEFAULT\|CHARSET\|COLLATE\|$)/is', $statement, $m)) {
395	+	return [];
396	+	}
397	+
398	+	$defs = [];
399	+	foreach (preg_split('/\R/', $m[2]) ?: [] as $line) {
400	+	$line = trim($line);
401	+	if (!preg_match('/^`([^`]+)`\s+(.+?)(?:,)?$/', $line, $cm)) {
402	+	continue;
403	+	}
404	+	$defs[$cm[1]] = '`' . $cm[1] . '` ' . rtrim($cm[2], ',');
405	+	}
406	+
407	+	return [$m[1], $defs];
408	+	}
409	+
410	+	function acp_convert_dump_split_csv(string $text): array {
411	+	$items = [];
412	+	$buf = '';
413	+	$quote = '';
414	+	$depth = 0;
415	+	$len = strlen($text);
416	+
417	+	for ($i = 0; $i < $len; $i++) {
418	+	$ch = $text[$i];
419	+	$next = ($i + 1 < $len) ? $text[$i + 1] : '';
420	+
421	+	if ($quote !== '') {
422	+	$buf .= $ch;
423	+	if ($ch === '\\') {
424	+	if ($i + 1 < $len) {
425	+	$buf .= $text[++$i];
426	+	}
427	+	continue;
428	+	}
429	+	if ($ch === $quote) {
430	+	if ($quote === "'" && $next === "'") {
431	+	$buf .= $text[++$i];
432	+	continue;
433	+	}
434	+	$quote = '';
435	+	}
436	+	continue;
437	+	}
438	+
439	+	if ($ch === "'" \|\| $ch === '"' \|\| $ch === '`') {
440	+	$quote = $ch;
441	+	$buf .= $ch;
442	+	continue;
443	+	}
444	+	if ($ch === '(') $depth++;
445	+	if ($ch === ')') $depth--;
446	+
447	+	if ($ch === ',' && $depth === 0) {
448	+	$items[] = trim($buf);
449	+	$buf = '';
450	+	continue;
451	+	}
452	+
453	+	$buf .= $ch;
454	+	}
455	+
456	+	if (trim($buf) !== '') {
457	+	$items[] = trim($buf);
458	+	}
459	+
460	+	return $items;
461	+	}
462	+
463	+	function acp_convert_dump_tuples(string $values): array {
464	+	$tuples = [];
465	+	$buf = '';
466	+	$quote = '';
467	+	$depth = 0;
468	+	$len = strlen($values);
469	+
470	+	for ($i = 0; $i < $len; $i++) {
471	+	$ch = $values[$i];
472	+	$next = ($i + 1 < $len) ? $values[$i + 1] : '';
473	+
474	+	if ($quote !== '') {
475	+	$buf .= $ch;
476	+	if ($ch === '\\') {
477	+	if ($i + 1 < $len) {
478	+	$buf .= $values[++$i];
479	+	}
480	+	continue;
481	+	}
482	+	if ($ch === $quote) {
483	+	if ($quote === "'" && $next === "'") {
484	+	$buf .= $values[++$i];
485	+	continue;
486	+	}
487	+	$quote = '';
488	+	}
489	+	continue;
490	+	}
491	+
492	+	if ($ch === "'" \|\| $ch === '"') {
493	+	$quote = $ch;
494	+	$buf .= $ch;
495	+	continue;
496	+	}
497	+	if ($ch === '(') {
498	+	if ($depth > 0) $buf .= $ch;
499	+	$depth++;
500	+	continue;
501	+	}
502	+	if ($ch === ')') {
503	+	$depth--;
504	+	if ($depth === 0) {
505	+	$tuples[] = $buf;
506	+	$buf = '';
507	+	continue;
508	+	}
509	+	$buf .= $ch;
510	+	continue;
511	+	}
512	+	if ($depth > 0) {
513	+	$buf .= $ch;
514	+	}
515	+	}
516	+
517	+	return $tuples;
518	+	}
519	+
520	+	function acp_convert_dump_value(string $value) {
521	+	$value = trim($value);
522	+	if (strcasecmp($value, 'NULL') === 0) {
523	+	return null;
524	+	}
525	+	if (preg_match('/^(?:0x[0-9a-f]+\|x\'[0-9a-f]\'\|b\'[01]\')$/i', $value)) {
526	+	return ['sql' => $value, 'archive' => $value];
527	+	}
528	+	if (preg_match('/^-?\d+$/', $value)) {
529	+	return (int)$value;
530	+	}
531	+	if (
532	+	(strlen($value) >= 2) &&
533	+	(($value[0] === "'" && substr($value, -1) === "'") \|\| ($value[0] === '"' && substr($value, -1) === '"'))
534	+	) {
535	+	$value = substr($value, 1, -1);
536	+	$value = str_replace(["\\r", "\\n", "\\t", "\\0", "\\'", '\\"', "\\\\"], ["\r", "\n", "\t", "\0", "'", '"', "\\"], $value);
537	+	$value = str_replace("''", "'", $value);
538	+	return $value;
539	+	}
540	+	return $value;
541	+	}
542	+
543	+	function acp_convert_dump_model(string $sql): array {
544	+	$model = ['tables' => [], 'columns' => [], 'column_defs' => [], 'creates' => [], 'warnings' => []];
545	+
546	+	foreach (acp_convert_dump_split($sql) as $statement) {
547	+	$create = acp_convert_dump_columns($statement);
548	+	if ($create) {
549	+	$model['columns'][$create[0]] = $create[1];
550	+	$defs = acp_convert_dump_column_defs($statement);
551	+	if ($defs) {
552	+	$model['column_defs'][$defs[0]] = $defs[1];
553	+	}
554	+	$model['creates'][$create[0]] = $statement . ';';
555	+	if (!isset($model['tables'][$create[0]])) {
556	+	$model['tables'][$create[0]] = [];
557	+	}
558	+	continue;
559	+	}
560	+
561	+	if (!preg_match('/INSERT\s+INTO\s+`?([a-zA-Z0-9_]+)`?\s(?:$(.?)$)?\s+VALUES\s(.)$/is', $statement, $m)) {
562	+	continue;
563	+	}
564	+
565	+	$table = $m[1];
566	+	$insertColumns = (string)($m[2] ?? '');
567	+	$columns = [];
568	+	if (trim($insertColumns) !== '') {
569	+	foreach (acp_convert_dump_split_csv($insertColumns) as $col) {
570	+	$columns[] = trim($col, " `\t\n\r\0\x0B");
571	+	}
572	+	} else {
573	+	$columns = $model['columns'][$table] ?? [];
574	+	}
575	+
576	+	if (!$columns) {
577	+	$model['warnings'][] = 'Skipped INSERT for ' . $table . ' because no column list was available.';
578	+	continue;
579	+	}
580	+
581	+	foreach (acp_convert_dump_tuples((string)$m[3]) as $tuple) {
582	+	$values = acp_convert_dump_split_csv($tuple);
583	+	$row = [];
584	+	foreach ($columns as $i => $column) {
585	+	$row[$column] = array_key_exists($i, $values) ? acp_convert_dump_value($values[$i]) : null;
586	+	}
587	+	$model['tables'][$table][] = $row;
588	+	}
589	+	}
590	+
591	+	return $model;
592	+	}
593	+
594	+	function acp_convert_row_value(array $row, array $keys, $default = '') {
595	+	foreach ($keys as $key) {
596	+	if (array_key_exists($key, $row) && $row[$key] !== null) {
597	+	return $row[$key];
598	+	}
599	+	}
600	+	return $default;
601	+	}
602	+
603	+	function acp_convert_dump_source(array $model, string $requested): string {
604	+	if ($requested === 'myaac' \|\| $requested === 'gesior') {
605	+	return $requested;
606	+	}
607	+	foreach (array_keys($model['tables']) as $table) {
608	+	if (strpos($table, 'myaac_') === 0) return 'myaac';
609	+	if (strpos($table, 'z_') === 0) return 'gesior';
610	+	}
611	+	return 'legacy';
612	+	}
613	+
614	+	function acp_convert_dump_stats(array $model, string $source): array {
615	+	$stats = ['tables' => count($model['tables']), 'rows' => 0, 'known' => 0, 'custom' => 0];
616	+	$known = [
617	+	'accounts', 'players', 'myaac_news', 'myaac_changelog', 'myaac_forum_boards',
618	+	'myaac_forum', 'myaac_pages', 'myaac_gallery', 'myaac_menu', 'myaac_config',
619	+	'myaac_settings', 'z_news_big', 'z_news_tickers', 'z_forum_boards', 'z_forum',
620	+	'z_pages', 'z_config',
621	+	];
622	+
623	+	foreach ($model['tables'] as $table => $rows) {
624	+	$count = count($rows);
625	+	$stats['rows'] += $count;
626	+	if (in_array($table, $known, true)) {
627	+	$stats['known'] += $count;
628	+	} else {
629	+	$stats['custom'] += $count;
630	+	}
631	+	}
632	+
633	+	return $stats;
634	+	}
635	+
636	+	function acp_convert_dump_player_name(array $model, int $playerId): string {
637	+	if ($playerId <= 0 \|\| empty($model['tables']['players'])) {
638	+	return '';
639	+	}
640	+
641	+	foreach ($model['tables']['players'] as $player) {
642	+	if ((int)acp_convert_row_value($player, ['id'], 0) === $playerId) {
643	+	return substr((string)acp_convert_row_value($player, ['name'], ''), 0, 50);
644	+	}
645	+	}
646	+
647	+	return '';
648	+	}
649	+
650	+	function acp_convert_dump_sql_values(array $rows, array $columns): string {
651	+	$out = [];
652	+	foreach ($rows as $row) {
653	+	$values = [];
654	+	foreach ($columns as $column) {
655	+	$value = $row[$column] ?? null;
656	+	$values[] = acp_convert_dump_sql_value($value);
657	+	}
658	+	$out[] = '(' . implode(', ', $values) . ')';
659	+	}
660	+	return implode(",\n", $out);
661	+	}
662	+
663	+	function acp_convert_dump_sql_value($value): string {
664	+	if (is_array($value) && isset($value['sql'])) {
665	+	return (string)$value['sql'];
666	+	}
667	+	return is_int($value) ? (string)$value : acp_convert_sql_literal($value);
668	+	}
669	+
670	+	function acp_convert_dump_archive_row(array $row): array {
671	+	foreach ($row as $column => $value) {
672	+	if (is_array($value) && isset($value['sql'])) {
673	+	$row[$column] = (string)($value['archive'] ?? $value['sql']);
674	+	}
675	+	}
676	+	return $row;
677	+	}
678	+
679	+	function acp_convert_dump_integer_definition(string $definition, string $column, array $rows): array {
680	+	$pattern = '/^(`' . preg_quote($column, '/') . '`\s+)(tinyint\|smallint\|mediumint\|int\|bigint)(\s+unsigned)?(.*)$/i';
681	+	if (!preg_match($pattern, $definition, $match)) {
682	+	return ['definition' => $definition, 'type' => '', 'widened_from' => []];
683	+	}
684	+
685	+	$types = ['tinyint', 'smallint', 'mediumint', 'int', 'bigint'];
686	+	$signedMax = [127, 32767, 8388607, 2147483647, PHP_INT_MAX];
687	+	$signedMin = [-128, -32768, -8388608, -2147483648, PHP_INT_MIN];
688	+	$unsignedMax = [255, 65535, 16777215, 4294967295, PHP_INT_MAX];
689	+	$type = strtolower($match[2]);
690	+	$unsigned = trim((string)$match[3]) !== '';
691	+	$index = array_search($type, $types, true);
692	+	if ($index === false) {
693	+	return ['definition' => $definition, 'type' => '', 'widened_from' => []];
694	+	}
695	+
696	+	$minimum = 0;
697	+	$maximum = 0;
698	+	foreach ($rows as $row) {
699	+	$value = $row[$column] ?? null;
700	+	if (!is_int($value)) {
701	+	continue;
702	+	}
703	+	$minimum = min($minimum, $value);
704	+	$maximum = max($maximum, $value);
705	+	}
706	+
707	+	$required = $index;
708	+	while ($required < count($types) - 1) {
709	+	$fitsMinimum = $unsigned ? $minimum >= 0 : $minimum >= $signedMin[$required];
710	+	$fitsMaximum = $maximum <= ($unsigned ? $unsignedMax[$required] : $signedMax[$required]);
711	+	if ($fitsMinimum && $fitsMaximum) {
712	+	break;
713	+	}
714	+	$required++;
715	+	}
716	+
717	+	if ($required === $index) {
718	+	return [
719	+	'definition' => $definition,
720	+	'type' => $type,
721	+	'widened_from' => array_slice($types, 0, $index),
722	+	];
723	+	}
724	+
725	+	$replacement = $match[1] . $types[$required] . ($unsigned ? ' unsigned' : '') . $match[4];
726	+	return [
727	+	'definition' => $replacement,
728	+	'type' => $types[$required],
729	+	'widened_from' => array_slice($types, 0, $required),
730	+	];
731	+	}
732	+
733	+	function acp_convert_dump_sql_union(array $rows, array $columns): string {
734	+	$out = [];
735	+	foreach ($rows as $index => $row) {
736	+	$values = [];
737	+	foreach ($columns as $column) {
738	+	$value = $row[$column] ?? null;
739	+	$sqlValue = acp_convert_dump_sql_value($value);
740	+	$values[] = $index === 0 ? $sqlValue . ' AS `' . $column . '`' : $sqlValue;
741	+	}
742	+	$out[] = 'SELECT ' . implode(', ', $values);
743	+	}
744	+	return implode("\nUNION ALL\n", $out);
745	+	}
746	+
747	+	function acp_convert_dump_mapped_id_sql(string $source, string $sourceTable, int $sourceId, string $targetTable): string {
748	+	if ($sourceId <= 0) {
749	+	return '0';
750	+	}
751	+
752	+	return "COALESCE((SELECT `target_id` FROM `znote_convert_map`"
753	+	. " WHERE `source` = " . acp_convert_sql_literal($source)
754	+	. " AND `source_table` = " . acp_convert_sql_literal($sourceTable)
755	+	. " AND `source_id` = " . acp_convert_sql_literal((string)$sourceId)
756	+	. " AND `target_table` = " . acp_convert_sql_literal($targetTable)
757	+	. " LIMIT 1), {$sourceId})";
758	+	}
759	+
760	+	function acp_convert_dump_reference_value(string $source, string $table, string $column, $value) {
761	+	if (is_array($value) && isset($value['sql'])) {
762	+	return $value;
763	+	}
764	+	$id = (int)$value;
765	+	if ($id <= 0) {
766	+	return $value;
767	+	}
768	+
769	+	$accountColumns = ['account_id', 'author_aid', 'last_edit_aid', 'original_account_id', 'bidder_account_id'];
770	+	$playerColumns = ['player_id', 'author_guid'];
771	+	if ($table === 'players' && $column === 'account_id') {
772	+	return ['sql' => acp_convert_dump_mapped_id_sql($source, 'accounts', $id, 'accounts')];
773	+	}
774	+	if ($table !== 'accounts' && in_array($column, $accountColumns, true)) {
775	+	return ['sql' => acp_convert_dump_mapped_id_sql($source, 'accounts', $id, 'accounts')];
776	+	}
777	+	if ($table !== 'players' && in_array($column, $playerColumns, true)) {
778	+	return ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $id, 'players')];
779	+	}
780	+
781	+	return $value;
782	+	}
783	+
784	+	function acp_convert_dump_ordered_tables(array $model): array {
785	+	$orderedTables = array_keys($model['tables']);
786	+	usort($orderedTables, static function (string $left, string $right): int {
787	+	$priority = ['accounts' => 0, 'players' => 1];
788	+	return ($priority[$left] ?? 2) <=> ($priority[$right] ?? 2);
789	+	});
790	+
791	+	return $orderedTables;
792	+	}
793	+
794	+	function acp_convert_dump_restore_add_column_sql(string $table, string $column, string $definition, int $guard): string {
795	+	$checkVar = '@znote_restore_col_' . $guard;
796	+	$stmtName = 'znote_restore_col_stmt_' . $guard;
797	+
798	+	return "SET {$checkVar} := IF(
799	+	(
800	+	SELECT COUNT(*)
801	+	FROM INFORMATION_SCHEMA.COLUMNS
802	+	WHERE TABLE_SCHEMA = DATABASE()
803	+	AND TABLE_NAME = " . acp_convert_sql_literal($table) . "
804	+	AND COLUMN_NAME = " . acp_convert_sql_literal($column) . "
805	+	) = 0,
806	+	" . acp_convert_sql_literal('ALTER TABLE `' . $table . '` ADD ' . $definition) . ",
807	+	'SELECT 1'
808	+	);
809	+	PREPARE {$stmtName} FROM {$checkVar};
810	+	EXECUTE {$stmtName};
811	+	DEALLOCATE PREPARE {$stmtName};";
812	+	}
813	+
814	+	function acp_convert_dump_restore_widen_column_sql(string $table, string $column, string $definition, array $smallerTypes, int $guard): string {
815	+	$checkVar = '@znote_restore_widen_' . $guard;
816	+	$stmtName = 'znote_restore_widen_stmt_' . $guard;
817	+	$smallerTypesSql = implode(', ', array_map('acp_convert_sql_literal', $smallerTypes));
818	+
819	+	return "SET {$checkVar} := IF(
820	+	EXISTS(
821	+	SELECT 1
822	+	FROM INFORMATION_SCHEMA.COLUMNS
823	+	WHERE TABLE_SCHEMA = DATABASE()
824	+	AND TABLE_NAME = " . acp_convert_sql_literal($table) . "
825	+	AND COLUMN_NAME = " . acp_convert_sql_literal($column) . "
826	+	AND DATA_TYPE IN ({$smallerTypesSql})
827	+	),
828	+	" . acp_convert_sql_literal('ALTER TABLE `' . $table . '` MODIFY ' . $definition) . ",
829	+	'SELECT 1'
830	+	);
831	+	PREPARE {$stmtName} FROM {$checkVar};
832	+	EXECUTE {$stmtName};
833	+	DEALLOCATE PREPARE {$stmtName};";
834	+	}
835	+
836	+	function acp_convert_dump_restore_schema_sql(string $table, array $columns, array $defs, array $rows, int &$guard): array {
837	+	$out = [];
838	+	foreach ($columns as $column) {
839	+	if (!isset($defs[$column]) \|\| !preg_match('/^[a-zA-Z0-9_]+$/', (string)$column)) {
840	+	continue;
841	+	}
842	+
843	+	$definition = acp_convert_dump_integer_definition($defs[$column], $column, $rows);
844	+	$effectiveDef = (string)$definition['definition'];
845	+	$out[] = acp_convert_dump_restore_add_column_sql($table, $column, $effectiveDef, ++$guard);
846	+
847	+	if (!empty($definition['widened_from'])) {
848	+	$out[] = acp_convert_dump_restore_widen_column_sql($table, $column, $effectiveDef, $definition['widened_from'], ++$guard);
849	+	}
850	+	}
851	+
852	+	return $out;
853	+	}
854	+
855	+	function acp_convert_dump_restore_updates(string $table, array $columns): array {
856	+	$updates = [];
857	+	foreach ($columns as $column) {
858	+	if (($table === 'accounts' \|\| $table === 'players') && $column === 'id') {
859	+	continue;
860	+	}
861	+	$updates[] = '`' . $column . '` = VALUES(`' . $column . '`)';
862	+	}
863	+
864	+	return $updates;
865	+	}
866	+
867	+	function acp_convert_dump_restore_identity_row_sql(string $source, string $table, array $row, array $columns, array $quotedColumns, array $updates): array {
868	+	$sourceId = (int)acp_convert_row_value($row, ['id'], 0);
869	+	if ($sourceId <= 0) {
870	+	return [];
871	+	}
872	+
873	+	$mapVariable = $table === 'accounts' ? '@znote_import_account_id' : '@znote_import_player_id';
874	+	$sourceName = trim((string)acp_convert_row_value($row, ['name'], ''));
875	+	$sameNameSql = $sourceName !== ''
876	+	? "(SELECT `id` FROM `{$table}` WHERE `name` = " . acp_convert_sql_literal($sourceName) . " LIMIT 1),"
877	+	: '';
878	+
879	+	$out = [];
880	+	$out[] = "SET {$mapVariable} := COALESCE(
881	+	(SELECT `target_id` FROM `znote_convert_map`
882	+	WHERE `source` = " . acp_convert_sql_literal($source) . "
883	+	AND `source_table` = " . acp_convert_sql_literal($table) . "
884	+	AND `source_id` = " . acp_convert_sql_literal((string)$sourceId) . "
885	+	AND `target_table` = " . acp_convert_sql_literal($table) . " LIMIT 1),
886	+	{$sameNameSql}
887	+	IF(EXISTS(SELECT 1 FROM `{$table}` WHERE `id` = {$sourceId}),
888	+	(SELECT `next_id` FROM (SELECT COALESCE(MAX(`id`), 0) + 1 AS `next_id` FROM `{$table}`) AS `available_id`),
889	+	{$sourceId}
890	+	)
891	+	);";
892	+	$out[] = "INSERT INTO `znote_convert_map`
893	+	(`source`, `source_table`, `source_id`, `target_table`, `target_id`, `created`)
894	+	VALUES (" . acp_convert_sql_literal($source) . ", " . acp_convert_sql_literal($table) . ", "
895	+	. acp_convert_sql_literal((string)$sourceId) . ", " . acp_convert_sql_literal($table)
896	+	. ", {$mapVariable}, UNIX_TIMESTAMP())
897	+	ON DUPLICATE KEY UPDATE `target_id` = VALUES(`target_id`);";
898	+
899	+	$mappedRow = $row;
900	+	$mappedRow['id'] = ['sql' => $mapVariable];
901	+	foreach ($columns as $column) {
902	+	$mappedRow[$column] = acp_convert_dump_reference_value($source, $table, $column, $mappedRow[$column] ?? null);
903	+	}
904	+	$out[] = "INSERT INTO `{$table}` (" . implode(', ', $quotedColumns) . ") VALUES\n"
905	+	. acp_convert_dump_sql_values([$mappedRow], $columns)
906	+	. "\nON DUPLICATE KEY UPDATE " . implode(', ', $updates) . ';';
907	+
908	+	return $out;
909	+	}
910	+
911	+	function acp_convert_dump_restore_identity_table_sql(string $source, string $table, array $rows, array $columns, array $quotedColumns, array $updates): array {
912	+	$out = [];
913	+	foreach ($rows as $row) {
914	+	array_push($out, ...acp_convert_dump_restore_identity_row_sql($source, $table, $row, $columns, $quotedColumns, $updates));
915	+	}
916	+
917	+	return $out;
918	+	}
919	+
920	+	function acp_convert_dump_restore_regular_table_sql(string $source, string $table, array $rows, array $columns, array $quotedColumns, array $updates): array {
921	+	$mappedRows = [];
922	+	foreach ($rows as $row) {
923	+	foreach ($columns as $column) {
924	+	$row[$column] = acp_convert_dump_reference_value($source, $table, $column, $row[$column] ?? null);
925	+	}
926	+	$mappedRows[] = $row;
927	+	}
928	+
929	+	if (!$mappedRows) {
930	+	return [];
931	+	}
932	+
933	+	return [
934	+	"INSERT INTO `{$table}` (" . implode(', ', $quotedColumns) . ") VALUES\n"
935	+	. acp_convert_dump_sql_values($mappedRows, $columns)
936	+	. "\nON DUPLICATE KEY UPDATE " . implode(', ', $updates) . ';',
937	+	];
938	+	}
939	+
940	+	function acp_convert_dump_restore_table_sql(array $model, string $source, string $table, int &$guard): array {
941	+	if (!preg_match('/^[a-zA-Z0-9_]+$/', (string)$table)) {
942	+	return [];
943	+	}
944	+
945	+	$rows = $model['tables'][$table];
946	+	$columns = $model['columns'][$table] ?? [];
947	+	$defs = $model['column_defs'][$table] ?? [];
948	+	$create = trim((string)($model['creates'][$table] ?? ''));
949	+	if ($create === '' \|\| !$columns) {
950	+	return [];
951	+	}
952	+
953	+	$out = ["-- Restore source table: {$table}", $create];
954	+	array_push($out, ...acp_convert_dump_restore_schema_sql($table, $columns, $defs, $rows, $guard));
955	+	if (!$rows) {
956	+	return $out;
957	+	}
958	+
959	+	$quotedColumns = array_map(static fn($column) => '`' . $column . '`', $columns);
960	+	$updates = acp_convert_dump_restore_updates($table, $columns);
961	+	if ($table === 'accounts' \|\| $table === 'players') {
962	+	array_push($out, ...acp_convert_dump_restore_identity_table_sql($source, $table, $rows, $columns, $quotedColumns, $updates));
963	+	return $out;
964	+	}
965	+
966	+	array_push($out, ...acp_convert_dump_restore_regular_table_sql($source, $table, $rows, $columns, $quotedColumns, $updates));
967	+	return $out;
968	+	}
969	+
970	+	function acp_convert_dump_restore_sql(array $model, string $source): string {
971	+	$out = [];
972	+	$guard = 0;
973	+
974	+	foreach (acp_convert_dump_ordered_tables($model) as $table) {
975	+	$section = acp_convert_dump_restore_table_sql($model, $source, $table, $guard);
976	+	if ($section) {
977	+	array_push($out, ...$section);
978	+	}
979	+	}
980	+
981	+	return trim(implode("\n\n", $out));
982	+	}
983	+
984	+	function acp_convert_dump_header_sql(array $model, string $source): array {
985	+	$out = [
986	+	"-- ZnoteX uploaded SQL conversion",
987	+	"-- Source detected: {$source}",
988	+	"-- Generated " . date('Y-m-d H:i:s'),
989	+	"START TRANSACTION;",
990	+	"",
991	+	file_get_contents('SQL/migrations/2.0.0_pages_and_convert_map.sql') ?: '',
992	+	file_get_contents('SQL/migrations/2.0.0_gallery_image_url.sql') ?: '',
993	+	];
994	+
995	+	$restoreSql = acp_convert_dump_restore_sql($model, $source);
996	+	if ($restoreSql !== '') {
997	+	$out[] = "";
998	+	$out[] = "-- Restore original source tables, rows and custom columns";
999	+	$out[] = $restoreSql;
1000	+	}
1001	+
1002	+	return $out;
1003	+	}
1004	+
1005	+	function acp_convert_dump_account_compat_sql(array $model, string $source): array {
1006	+	if (!empty($model['tables']['accounts'])) {
1007	+	$rows = [];
1008	+	foreach ($model['tables']['accounts'] as $row) {
1009	+	$id = (int)acp_convert_row_value($row, ['id'], 0);
1010	+	if ($id <= 0) continue;
1011	+	$created = (int)acp_convert_row_value($row, ['creation', 'created'], time());
1012	+	$rows[] = [
1013	+	'account_id' => ['sql' => acp_convert_dump_mapped_id_sql($source, 'accounts', $id, 'accounts')],
1014	+	'ip' => 0,
1015	+	'created' => $created > 0 ? $created : time(),
1016	+	'flag' => '',
1017	+	];
1018	+	}
1019	+	if ($rows) {
1020	+	return [
1021	+	"-- Account compatibility rows",
1022	+	"INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)
1023	+	SELECT `v`.`account_id`, `v`.`ip`, `v`.`created`, `v`.`flag`
1024	+	FROM (
1025	+	" . acp_convert_dump_sql_union($rows, ['account_id', 'ip', 'created', 'flag']) . "
1026	+	) AS `v`
1027	+	WHERE NOT EXISTS (
1028	+	SELECT 1 FROM `znote_accounts` AS `z` WHERE `z`.`account_id` = `v`.`account_id`
1029	+	);",
1030	+	];
1031	+	}
1032	+	}
1033	+
1034	+	return [];
1035	+	}
1036	+
1037	+	function acp_convert_dump_player_compat_sql(array $model, string $source): array {
1038	+	if (!empty($model['tables']['players'])) {
1039	+	$rows = [];
1040	+	foreach ($model['tables']['players'] as $row) {
1041	+	$id = (int)acp_convert_row_value($row, ['id'], 0);
1042	+	if ($id <= 0) continue;
1043	+	$rows[] = [
1044	+	'player_id' => ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $id, 'players')],
1045	+	'created' => time(),
1046	+	'hide_char' => 0,
1047	+	'comment' => '',
1048	+	];
1049	+	}
1050	+	if ($rows) {
1051	+	return [
1052	+	"-- Player compatibility rows",
1053	+	"INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)
1054	+	SELECT `v`.`player_id`, `v`.`created`, `v`.`hide_char`, `v`.`comment`
1055	+	FROM (
1056	+	" . acp_convert_dump_sql_union($rows, ['player_id', 'created', 'hide_char', 'comment']) . "
1057	+	) AS `v`
1058	+	WHERE NOT EXISTS (
1059	+	SELECT 1 FROM `znote_players` AS `z` WHERE `z`.`player_id` = `v`.`player_id`
1060	+	);",
1061	+	];
1062	+	}
1063	+	}
1064	+
1065	+	return [];
1066	+	}
1067	+
1068	+	function acp_convert_dump_news_sql(array $model, string $source): array {
1069	+	$newsTable = $source === 'gesior' ? 'z_news_big' : 'myaac_news';
1070	+	if (!empty($model['tables'][$newsTable])) {
1071	+	$rows = [];
1072	+	foreach ($model['tables'][$newsTable] as $row) {
1073	+	$hide = (int)acp_convert_row_value($row, ['hide', 'hide_news'], 0);
1074	+	if ($hide !== 0) continue;
1075	+	$title = substr(trim((string)acp_convert_row_value($row, ['title', 'topic', 'name'], 'Imported news')), 0, 30);
1076	+	$body = (string)acp_convert_row_value($row, ['body', 'text'], '');
1077	+	$date = (int)acp_convert_row_value($row, ['date', 'time'], time());
1078	+	$pid = (int)acp_convert_row_value($row, ['player_id', 'author_id', 'pid'], 0);
1079	+	$rows[] = [
1080	+	'title' => $title,
1081	+	'text' => acp_convert_news_text($body),
1082	+	'date' => $date > 0 ? $date : time(),
1083	+	'pid' => ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $pid, 'players')],
1084	+	];
1085	+	}
1086	+	if ($rows) {
1087	+	return [
1088	+	"-- News",
1089	+	"INSERT INTO `znote_news` (`title`, `text`, `date`, `pid`)
1090	+	SELECT `v`.`title`, `v`.`text`, `v`.`date`, `v`.`pid`
1091	+	FROM (
1092	+	" . acp_convert_dump_sql_union($rows, ['title', 'text', 'date', 'pid']) . "
1093	+	) AS `v`
1094	+	WHERE NOT EXISTS (
1095	+	SELECT 1 FROM `znote_news` AS `z` WHERE `z`.`title` = `v`.`title` AND `z`.`date` = `v`.`date`
1096	+	);",
1097	+	];
1098	+	}
1099	+	}
1100	+
1101	+	return [];
1102	+	}
1103	+
1104	+	function acp_convert_dump_changelog_sql(array $model, string $source): array {
1105	+	$changeTable = $source === 'gesior' ? 'z_news_tickers' : 'myaac_changelog';
1106	+	if (!empty($model['tables'][$changeTable])) {
1107	+	$rows = [];
1108	+	foreach ($model['tables'][$changeTable] as $row) {
1109	+	$hide = (int)acp_convert_row_value($row, ['hide', 'hide_ticker'], 0);
1110	+	if ($hide !== 0) continue;
1111	+	$body = (string)acp_convert_row_value($row, ['body', 'text'], '');
1112	+	$text = substr(acp_convert_strip_html($body), 0, 254);
1113	+	if ($text === '') continue;
1114	+	$date = (int)acp_convert_row_value($row, ['date', 'time'], time());
1115	+	$rows[] = ['text' => $text, 'time' => $date > 0 ? $date : time(), 'report_id' => 0, 'status' => (int)acp_convert_row_value($row, ['type'], 0)];
1116	+	}
1117	+	if ($rows) {
1118	+	return [
1119	+	"-- Changelog / tickers",
1120	+	"INSERT INTO `znote_changelog` (`text`, `time`, `report_id`, `status`)
1121	+	SELECT `v`.`text`, `v`.`time`, `v`.`report_id`, `v`.`status`
1122	+	FROM (
1123	+	" . acp_convert_dump_sql_union($rows, ['text', 'time', 'report_id', 'status']) . "
1124	+	) AS `v`
1125	+	WHERE NOT EXISTS (
1126	+	SELECT 1 FROM `znote_changelog` AS `z` WHERE `z`.`text` = `v`.`text` AND `z`.`time` = `v`.`time`
1127	+	);",
1128	+	];
1129	+	}
1130	+	}
1131	+
1132	+	return [];
1133	+	}
1134	+
1135	+	function acp_convert_dump_pages_sql(array $model, string $source): array {
1136	+	if (!empty($model['tables']['myaac_pages'])) {
1137	+	$rows = [];
1138	+	foreach ($model['tables']['myaac_pages'] as $row) {
1139	+	if ((int)acp_convert_row_value($row, ['hide'], 0) !== 0) continue;
1140	+	$id = (int)acp_convert_row_value($row, ['id'], 0);
1141	+	$slug = acp_convert_slug((string)acp_convert_row_value($row, ['name', 'slug'], ''), 'imported-page-' . $id);
1142	+	$title = substr(trim((string)acp_convert_row_value($row, ['title', 'name'], $slug)), 0, 100);
1143	+	$date = (int)acp_convert_row_value($row, ['date'], time());
1144	+	$rows[] = [
1145	+	'slug' => $slug,
1146	+	'title' => $title,
1147	+	'body' => acp_convert_news_text((string)acp_convert_row_value($row, ['body', 'text'], '')),
1148	+	'created' => $date > 0 ? $date : time(),
1149	+	'updated' => $date > 0 ? $date : time(),
1150	+	'player_id' => ['sql' => acp_convert_dump_mapped_id_sql(
1151	+	$source,
1152	+	'players',
1153	+	(int)acp_convert_row_value($row, ['player_id'], 0),
1154	+	'players'
1155	+	)],
1156	+	'access' => (int)acp_convert_row_value($row, ['access'], 0),
1157	+	'active' => 1,
1158	+	];
1159	+	}
1160	+	if ($rows) {
1161	+	return [
1162	+	"-- Custom pages",
1163	+	"INSERT INTO `znote_pages` (`slug`, `title`, `body`, `created`, `updated`, `player_id`, `access`, `active`) VALUES\n"
1164	+	. acp_convert_dump_sql_values($rows, ['slug', 'title', 'body', 'created', 'updated', 'player_id', 'access', 'active'])
1165	+	. "\nON DUPLICATE KEY UPDATE `title` = VALUES(`title`), `body` = VALUES(`body`), `updated` = VALUES(`updated`);",
1166	+	];
1167	+	}
1168	+	}
1169	+
1170	+	return [];
1171	+	}
1172	+
1173	+	function acp_convert_dump_forum_boards_sql(array $model, string $source): array {
1174	+	$boardTable = $source === 'gesior' ? 'z_forum_boards' : 'myaac_forum_boards';
1175	+	$idOffset = $source === 'gesior' ? 2000000 : 1000000;
1176	+	if (!empty($model['tables'][$boardTable])) {
1177	+	$rows = [];
1178	+	foreach ($model['tables'][$boardTable] as $row) {
1179	+	$id = (int)acp_convert_row_value($row, ['id'], 0);
1180	+	$name = substr(trim((string)acp_convert_row_value($row, ['name', 'title'], 'Imported board')), 0, 50);
1181	+	if ($id <= 0 \|\| $name === '') continue;
1182	+	$rows[] = [
1183	+	'id' => $idOffset + $id,
1184	+	'name' => $name,
1185	+	'access' => max(1, (int)acp_convert_row_value($row, ['access'], 1)),
1186	+	'closed' => (int)acp_convert_row_value($row, ['closed'], 0),
1187	+	'hidden' => (int)acp_convert_row_value($row, ['hide', 'hidden'], 0),
1188	+	'guild_id' => (int)acp_convert_row_value($row, ['guild', 'guild_id'], 0),
1189	+	];
1190	+	}
1191	+	if ($rows) {
1192	+	return [
1193	+	"-- Forum boards",
1194	+	"INSERT INTO `znote_forum` (`id`, `name`, `access`, `closed`, `hidden`, `guild_id`) VALUES\n"
1195	+	. acp_convert_dump_sql_values($rows, ['id', 'name', 'access', 'closed', 'hidden', 'guild_id'])
1196	+	. "\nON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `access` = VALUES(`access`), `closed` = VALUES(`closed`), `hidden` = VALUES(`hidden`), `guild_id` = VALUES(`guild_id`);",
1197	+	];
1198	+	}
1199	+	}
1200	+
1201	+	return [];
1202	+	}
1203	+
1204	+	function acp_convert_dump_forum_sql(array $model, string $source): array {
1205	+	$sql = [];
1206	+	$idOffset = $source === 'gesior' ? 2000000 : 1000000;
1207	+	$forumTable = !empty($model['tables']['myaac_forum']) ? 'myaac_forum' : (!empty($model['tables']['z_forum']) ? 'z_forum' : '');
1208	+	if ($forumTable !== '') {
1209	+	$threads = [];
1210	+	$posts = [];
1211	+
1212	+	foreach ($model['tables'][$forumTable] as $row) {
1213	+	$id = (int)acp_convert_row_value($row, ['id'], 0);
1214	+	$firstPost = (int)acp_convert_row_value($row, ['first_post'], 0);
1215	+	$playerId = (int)acp_convert_row_value($row, ['author_guid', 'player_id'], 0);
1216	+	$mappedPlayerId = ['sql' => acp_convert_dump_mapped_id_sql($source, 'players', $playerId, 'players')];
1217	+	$created = (int)acp_convert_row_value($row, ['post_date', 'created', 'date'], time());
1218	+	$updated = (int)acp_convert_row_value($row, ['edit_date', 'updated'], $created);
1219	+	$text = sanitize((string)acp_convert_row_value($row, ['post_text', 'text'], ''));
1220	+
1221	+	if ($id > 0 && ($firstPost === 0 \|\| $firstPost === $id)) {
1222	+	$oldBoard = (int)acp_convert_row_value($row, ['section', 'forum_id'], 1);
1223	+	$title = substr(trim((string)acp_convert_row_value($row, ['post_topic', 'title'], 'Imported thread')), 0, 50);
1224	+	$threads[] = [
1225	+	'id' => $idOffset + $id,
1226	+	'forum_id' => $idOffset + max(1, $oldBoard),
1227	+	'player_id' => $mappedPlayerId,
1228	+	'player_name' => (string)acp_convert_row_value($row, ['author', 'player_name'], acp_convert_dump_player_name($model, $playerId)),
1229	+	'title' => $title !== '' ? $title : 'Imported thread',
1230	+	'text' => $text,
1231	+	'created' => $created > 0 ? $created : time(),
1232	+	'updated' => $updated > 0 ? $updated : ($created > 0 ? $created : time()),
1233	+	'sticky' => (int)acp_convert_row_value($row, ['sticked', 'sticky'], 0),
1234	+	'hidden' => (int)acp_convert_row_value($row, ['hidden', 'hide'], 0),
1235	+	'closed' => (int)acp_convert_row_value($row, ['closed'], 0),
1236	+	];
1237	+	} elseif ($id > 0 && $firstPost > 0) {
1238	+	$posts[] = [
1239	+	'thread_id' => $idOffset + $firstPost,
1240	+	'player_id' => $mappedPlayerId,
1241	+	'player_name' => (string)acp_convert_row_value($row, ['author', 'player_name'], acp_convert_dump_player_name($model, $playerId)),
1242	+	'text' => $text,
1243	+	'created' => $created > 0 ? $created : time(),
1244	+	'updated' => $updated > 0 ? $updated : ($created > 0 ? $created : time()),
1245	+	];
1246	+	}
1247	+	}
1248	+
1249	+	if ($threads) {
1250	+	$sql[] = "-- Forum threads";
1251	+	$sql[] = "INSERT INTO `znote_forum_threads` (`id`, `forum_id`, `player_id`, `player_name`, `title`, `text`, `created`, `updated`, `sticky`, `hidden`, `closed`) VALUES\n"
1252	+	. acp_convert_dump_sql_values($threads, ['id', 'forum_id', 'player_id', 'player_name', 'title', 'text', 'created', 'updated', 'sticky', 'hidden', 'closed'])
1253	+	. "\nON DUPLICATE KEY UPDATE `forum_id` = VALUES(`forum_id`), `title` = VALUES(`title`), `text` = VALUES(`text`), `updated` = VALUES(`updated`);";
1254	+	}
1255	+	if ($posts) {
1256	+	$sql[] = "-- Forum posts";
1257	+	$sql[] = "INSERT INTO `znote_forum_posts` (`thread_id`, `player_id`, `player_name`, `text`, `created`, `updated`)
1258	+	SELECT `v`.`thread_id`, `v`.`player_id`, `v`.`player_name`, `v`.`text`, `v`.`created`, `v`.`updated`
1259	+	FROM (
1260	+	" . acp_convert_dump_sql_union($posts, ['thread_id', 'player_id', 'player_name', 'text', 'created', 'updated']) . "
1261	+	) AS `v`
1262	+	WHERE NOT EXISTS (
1263	+	SELECT 1 FROM `znote_forum_posts` AS `z`
1264	+	WHERE `z`.`thread_id` = `v`.`thread_id`
1265	+	AND `z`.`player_id` = `v`.`player_id`
1266	+	AND `z`.`created` = `v`.`created`
1267	+	AND `z`.`text` = `v`.`text`
1268	+	);";
1269	+	}
1270	+	}
1271	+
1272	+	return $sql;
1273	+	}
1274	+
1275	+	function acp_convert_dump_gallery_sql(array $model, string $source): array {
1276	+	if (!empty($model['tables']['myaac_gallery'])) {
1277	+	$rows = [];
1278	+	foreach ($model['tables']['myaac_gallery'] as $row) {
1279	+	if ((int)acp_convert_row_value($row, ['hide'], 0) !== 0) continue;
1280	+	$image = trim((string)acp_convert_row_value($row, ['image', 'url'], ''));
1281	+	if ($image === '') continue;
1282	+	$comment = (string)acp_convert_row_value($row, ['comment', 'description', 'desc'], '');
1283	+	$rows[] = [
1284	+	'title' => substr($comment !== '' ? $comment : 'Imported image', 0, 30),
1285	+	'desc' => $comment,
1286	+	'date' => (int)acp_convert_row_value($row, ['date'], time()),
1287	+	'status' => 2,
1288	+	'image' => substr($image, 0, 255),
1289	+	'delhash' => '',
1290	+	'account_id' => ['sql' => acp_convert_dump_mapped_id_sql(
1291	+	$source,
1292	+	'accounts',
1293	+	(int)acp_convert_row_value($row, ['account_id'], 0),
1294	+	'accounts'
1295	+	)],
1296	+	];
1297	+	}
1298	+	if ($rows) {
1299	+	return [
1300	+	"-- Gallery",
1301	+	"INSERT INTO `znote_images` (`title`, `desc`, `date`, `status`, `image`, `delhash`, `account_id`)
1302	+	SELECT `v`.`title`, `v`.`desc`, `v`.`date`, `v`.`status`, `v`.`image`, `v`.`delhash`, `v`.`account_id`
1303	+	FROM (
1304	+	" . acp_convert_dump_sql_union($rows, ['title', 'desc', 'date', 'status', 'image', 'delhash', 'account_id']) . "
1305	+	) AS `v`
1306	+	WHERE NOT EXISTS (
1307	+	SELECT 1 FROM `znote_images` AS `z` WHERE `z`.`image` = `v`.`image`
1308	+	);",
1309	+	];
1310	+	}
1311	+	}
1312	+
1313	+	return [];
1314	+	}
1315	+
1316	+	function acp_convert_dump_menu_sql(array $model): array {
1317	+	if (!empty($model['tables']['myaac_menu'])) {
1318	+	$rows = [];
1319	+	foreach ($model['tables']['myaac_menu'] as $row) {
1320	+	if ((int)acp_convert_row_value($row, ['enabled'], 1) !== 1) continue;
1321	+	$label = substr(trim((string)acp_convert_row_value($row, ['name', 'label'], '')), 0, 64);
1322	+	$url = substr(trim((string)acp_convert_row_value($row, ['link', 'url'], '')), 0, 255);
1323	+	if ($label === '' \|\| $url === '') continue;
1324	+	$rows[] = [
1325	+	'location' => 'main',
1326	+	'parent_id' => ['sql' => acp_convert_dump_menu_parent_sql((int)acp_convert_row_value($row, ['category'], 0))],
1327	+	'label' => $label,
1328	+	'url' => $url,
1329	+	'icon' => '',
1330	+	'target' => (int)acp_convert_row_value($row, ['blank'], 0) === 1 ? '_blank' : '',
1331	+	'visibility' => 'all',
1332	+	'sort_order' => (int)acp_convert_row_value($row, ['ordering', 'sort_order'], 0),
1333	+	'active' => 1,
1334	+	];
1335	+	}
1336	+	if ($rows) {
1337	+	return [
1338	+	"-- Menu",
1339	+	"INSERT INTO `znote_menu` (`location`, `parent_id`, `label`, `url`, `icon`, `target`, `visibility`, `sort_order`, `active`)
1340	+	SELECT `v`.`location`, `v`.`parent_id`, `v`.`label`, `v`.`url`, `v`.`icon`, `v`.`target`, `v`.`visibility`, `v`.`sort_order`, `v`.`active`
1341	+	FROM (
1342	+	" . acp_convert_dump_sql_union($rows, ['location', 'parent_id', 'label', 'url', 'icon', 'target', 'visibility', 'sort_order', 'active']) . "
1343	+	) AS `v`
1344	+	WHERE `v`.`parent_id` IS NOT NULL
1345	+	AND NOT EXISTS (
1346	+	SELECT 1 FROM `znote_menu` AS `z`
1347	+	WHERE `z`.`location` = `v`.`location`
1348	+	AND `z`.`label` = `v`.`label`
1349	+	AND `z`.`url` = `v`.`url`
1350	+	);",
1351	+	];
1352	+	}
1353	+	}
1354	+
1355	+	return [];
1356	+	}
1357	+
1358	+	function acp_convert_dump_legacy_config_sql(array $model): array {
1359	+	$sql = [];
1360	+	foreach (['myaac_config' => 'legacy:myaac:', 'myaac_settings' => 'legacy:myaac_setting:', 'z_config' => 'legacy:gesior:'] as $configTable => $prefix) {
1361	+	if (empty($model['tables'][$configTable])) continue;
1362	+	$rows = [];
1363	+	foreach ($model['tables'][$configTable] as $row) {
1364	+	$key = (string)acp_convert_row_value($row, ['key', 'name', 'config'], '');
1365	+	if ($key === '') continue;
1366	+	$rows[] = ['key' => substr($prefix . $key, 0, 64), 'value' => (string)acp_convert_row_value($row, ['value'], '')];
1367	+	}
1368	+	if ($rows) {
1369	+	$sql[] = "-- Legacy config: {$configTable}";
1370	+	$sql[] = "INSERT INTO `znote_config` (`key`, `value`) VALUES\n"
1371	+	. acp_convert_dump_sql_values($rows, ['key', 'value'])
1372	+	. "\nON DUPLICATE KEY UPDATE `value` = VALUES(`value`);";
1373	+	}
1374	+	}
1375	+
1376	+	return $sql;
1377	+	}
1378	+
1379	+	function acp_convert_dump_archive_sql(array $model, string $source, int $captured): array {
1380	+	$sql = [];
1381	+	foreach ($model['tables'] as $table => $rows) {
1382	+	$sql[] = "-- Legacy archive: {$table}";
1383	+	$schemaSql = (string)($model['creates'][$table] ?? '');
1384	+	$sql[] = "INSERT INTO `znote_legacy_tables` (`source`, `table_name`, `schema_sql`, `row_count`, `captured`) VALUES ("
1385	+	. acp_convert_sql_literal($source) . ', ' . acp_convert_sql_literal($table) . ', ' . acp_convert_sql_literal($schemaSql) . ', ' . count($rows) . ", {$captured})
1386	+	ON DUPLICATE KEY UPDATE `schema_sql` = VALUES(`schema_sql`), `row_count` = VALUES(`row_count`), `captured` = VALUES(`captured`);";
1387	+	$sql[] = "DELETE FROM `znote_legacy_rows` WHERE `source` = " . acp_convert_sql_literal($source)
1388	+	. " AND `table_name` = " . acp_convert_sql_literal($table) . ';';
1389	+	foreach ($rows as $row) {
1390	+	$pk = substr(acp_convert_row_pk($table, $row), 0, 128);
1391	+	$json = json_encode(acp_convert_dump_archive_row($row), JSON_UNESCAPED_SLASHES \| JSON_UNESCAPED_UNICODE \| JSON_INVALID_UTF8_SUBSTITUTE);
1392	+	if ($json === false) $json = '{}';
1393	+	$sql[] = "INSERT INTO `znote_legacy_rows` (`source`, `table_name`, `source_pk`, `row_json`, `captured`) VALUES ("
1394	+	. acp_convert_sql_literal($source) . ', '
1395	+	. acp_convert_sql_literal($table) . ', '
1396	+	. acp_convert_sql_literal($pk) . ', '
1397	+	. acp_convert_sql_literal($json) . ', '
1398	+	. $captured . ');';
1399	+	}
1400	+	}
1401	+
1402	+	return $sql;
1403	+	}
1404	+
1405	+	function acp_convert_dump_script(array $model, string $requestedSource): string {
1406	+	$source = acp_convert_dump_source($model, $requestedSource);
1407	+	$captured = time();
1408	+	$sql = [];
1409	+
1410	+	foreach ([
1411	+	acp_convert_dump_header_sql($model, $source),
1412	+	acp_convert_dump_account_compat_sql($model, $source),
1413	+	acp_convert_dump_player_compat_sql($model, $source),
1414	+	acp_convert_dump_news_sql($model, $source),
1415	+	acp_convert_dump_changelog_sql($model, $source),
1416	+	acp_convert_dump_pages_sql($model, $source),
1417	+	acp_convert_dump_forum_boards_sql($model, $source),
1418	+	acp_convert_dump_forum_sql($model, $source),
1419	+	acp_convert_dump_gallery_sql($model, $source),
1420	+	acp_convert_dump_menu_sql($model),
1421	+	acp_convert_dump_legacy_config_sql($model),
1422	+	acp_convert_dump_archive_sql($model, $source, $captured),
1423	+	] as $section) {
1424	+	array_push($sql, ...$section);
1425	+	}
1426	+
1427	+	$sql[] = "COMMIT;";
1428	+	return trim(implode("\n\n", $sql)) . "\n";
1429	+	}
1430	+
1431	+	function acp_convert_legacy_archive_sql(string $source): string {
1432	+	$out = [];
1433	+	$captured = time();
1434	+
1435	+	foreach (acp_convert_legacy_tables($source) as $table) {
1436	+	$count = acp_convert_count($table);
1437	+	$out[] = "-- Preserve legacy table {$table}";
1438	+	$out[] = "INSERT INTO `znote_legacy_tables` (`source`, `table_name`, `schema_sql`, `row_count`, `captured`)
1439	+	VALUES (" . acp_convert_sql_literal($source) . ", " . acp_convert_sql_literal($table) . ", '', {$count}, {$captured})
1440	+	ON DUPLICATE KEY UPDATE `schema_sql` = VALUES(`schema_sql`), `row_count` = VALUES(`row_count`), `captured` = VALUES(`captured`);";
1441	+	$out[] = "DELETE FROM `znote_legacy_rows`
1442	+	WHERE `source` = " . acp_convert_sql_literal($source) . "
1443	+	AND `table_name` = " . acp_convert_sql_literal($table) . ";";
1444	+
1445	+	$rows = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($table) . '`;') ?: [];
1446	+	foreach ($rows as $row) {
1447	+	$pk = substr(acp_convert_row_pk($table, $row), 0, 128);
1448	+	$json = json_encode($row, JSON_UNESCAPED_SLASHES \| JSON_UNESCAPED_UNICODE);
1449	+	if ($json === false) {
1450	+	$json = '{}';
1451	+	}
1452	+	$out[] = "INSERT INTO `znote_legacy_rows` (`source`, `table_name`, `source_pk`, `row_json`, `captured`)
1453	+	VALUES (" . acp_convert_sql_literal($source) . ", " . acp_convert_sql_literal($table) . ", " . acp_convert_sql_literal($pk) . ", " . acp_convert_sql_literal($json) . ", {$captured});";
1454	+	}
1455	+	}
1456	+
1457	+	return implode("\n\n", $out);
1458	+	}
1459	+
1460	+	function acp_convert_player_name(int $playerId): string {
1461	+	if ($playerId <= 0) {
1462	+	return '';
1463	+	}
1464	+	$row = db()->fetchOne('SELECT `name` FROM `players` WHERE `id` = ? LIMIT 1;', [$playerId]);
1465	+	return is_array($row) ? (string)$row['name'] : '';
1466	+	}
1467	+
1468	+	function acp_convert_strip_html(string $text): string {
1469	+	$text = html_entity_decode($text, ENT_QUOTES, 'UTF-8');
1470	+	$text = preg_replace('~<\sbr\s/?\s*>~i', "\n", $text);
1471	+	$text = preg_replace('~</\s(h[1-6]\|div\|li\|tr\|td\|th)\s>~i', "\n", $text);
1472	+	$text = preg_replace('~</\sp\s>~i', "\n\n", $text);
1473	+	$text = trim(strip_tags((string)$text));
1474	+	return preg_replace("/[ \t]\n[ \t]/", "\n", $text);
1475	+	}
1476	+
1477	+	function acp_convert_news_text(string $text): string {
1478	+	// MyAAC and Gesior content is often HTML. ZnoteX stores BBCode/plain text.
1479	+	return acp_convert_strip_html($text);
1480	+	}
1481	+
1482	+	function acp_convert_myaac_detect(): array {
1483	+	return [
1484	+	'accounts' => acp_convert_table_exists('accounts'),
1485	+	'players' => acp_convert_table_exists('players'),
1486	+	'news' => acp_convert_table_exists('myaac_news'),
1487	+	'changelog' => acp_convert_table_exists('myaac_changelog'),
1488	+	'boards' => acp_convert_table_exists('myaac_forum_boards'),
1489	+	'forum' => acp_convert_table_exists('myaac_forum'),
1490	+	'pages' => acp_convert_table_exists('myaac_pages'),
1491	+	'gallery' => acp_convert_table_exists('myaac_gallery'),
1492	+	'menu' => acp_convert_table_exists('myaac_menu'),
1493	+	'config' => acp_convert_table_exists('myaac_config') \|\| acp_convert_table_exists('myaac_settings'),
1494	+	];
1495	+	}
1496	+
1497	+	function acp_convert_gesior_detect(): array {
1498	+	return [
1499	+	'accounts' => acp_convert_table_exists('accounts'),
1500	+	'players' => acp_convert_table_exists('players'),
1501	+	'news_big' => acp_convert_table_exists('z_news_big'),
1502	+	'tickers' => acp_convert_table_exists('z_news_tickers'),
1503	+	'boards' => acp_convert_table_exists('z_forum_boards'),
1504	+	'forum' => acp_convert_table_exists('z_forum'),
1505	+	'pages' => acp_convert_table_exists('z_pages'),
1506	+	'config' => acp_convert_table_exists('z_config'),
1507	+	];
1508	+	}
1509	+
1510	+	function acp_convert_compatibility(): array {
1511	+	$report = ['accounts' => 0, 'players' => 0];
1512	+
1513	+	if (acp_convert_table_exists('accounts') && acp_convert_table_exists('znote_accounts')) {
1514	+	$before = acp_convert_count('znote_accounts');
1515	+	db()->execute("
1516	+	INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)
1517	+	SELECT `a`.`id`, 0, UNIX_TIMESTAMP(CURDATE()), ''
1518	+	FROM `accounts` AS `a`
1519	+	LEFT JOIN `znote_accounts` AS `z` ON `z`.`account_id` = `a`.`id`
1520	+	WHERE `z`.`id` IS NULL;
1521	+	");
1522	+	$report['accounts'] = max(0, acp_convert_count('znote_accounts') - $before);
1523	+	}
1524	+
1525	+	if (acp_convert_table_exists('players') && acp_convert_table_exists('znote_players')) {
1526	+	$before = acp_convert_count('znote_players');
1527	+	db()->execute("
1528	+	INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)
1529	+	SELECT `p`.`id`, UNIX_TIMESTAMP(CURDATE()), 0, ''
1530	+	FROM `players` AS `p`
1531	+	LEFT JOIN `znote_players` AS `z` ON `z`.`player_id` = `p`.`id`
1532	+	WHERE `z`.`id` IS NULL;
1533	+	");
1534	+	$report['players'] = max(0, acp_convert_count('znote_players') - $before);
1535	+	}
1536	+
1537	+	return $report;
1538	+	}
1539	+
1540	+	function acp_convert_empty_report(): array {
1541	+	return [
1542	+	'accounts' => 0,
1543	+	'players' => 0,
1544	+	'news' => 0,
1545	+	'changelog' => 0,
1546	+	'boards' => 0,
1547	+	'threads' => 0,
1548	+	'posts' => 0,
1549	+	'pages' => 0,
1550	+	'gallery' => 0,
1551	+	'menu' => 0,
1552	+	'config' => 0,
1553	+	'archived' => 0,
1554	+	'warnings' => [],
1555	+	];
1556	+	}
1557	+
1558	+	function acp_convert_prepare_report(string $source, bool $dryRun, array $report): array {
1559	+	if (!$dryRun) {
1560	+	acp_convert_ensure_tables();
1561	+	$report = array_merge($report, acp_convert_compatibility());
1562	+	$report['archived'] = acp_convert_archive_legacy($source);
1563	+	} else {
1564	+	foreach (acp_convert_legacy_tables($source) as $table) {
1565	+	$report['archived'] += acp_convert_count($table);
1566	+	}
1567	+	}
1568	+
1569	+	return $report;
1570	+	}
1571	+
1572	+	function acp_convert_report_empty(array $report, array $keys): bool {
1573	+	foreach ($keys as $key) {
1574	+	if (!empty($report[$key])) {
1575	+	return false;
1576	+	}
1577	+	}
1578	+
1579	+	return true;
1580	+	}
1581	+
1582	+	function acp_convert_myaac_news(bool $dryRun, array &$report): void {
1583	+	if (acp_convert_table_exists('myaac_news') && acp_convert_table_exists('znote_news')) {
1584	+	if ($dryRun) {
1585	+	$report['news'] = acp_convert_count('myaac_news', '`hide` = 0 AND `type` IN (1, 3)');
1586	+	return;
1587	+	}
1588	+
1589	+	$rows = db()->fetchAll("SELECT * FROM `myaac_news` WHERE `hide` = 0 AND `type` IN (1, 3) ORDER BY `id` ASC;") ?: [];
1590	+	foreach ($rows as $row) {
1591	+	$title = trim((string)$row['title']);
1592	+	$date = (int)$row['date'];
1593	+	$pid = (int)$row['player_id'];
1594	+	$exists = db()->fetchOne("
1595	+	SELECT `id` FROM `znote_news`
1596	+	WHERE `title` = ?
1597	+	AND `date` = ?
1598	+	LIMIT 1;
1599	+	", [substr($title, 0, 30), $date]);
1600	+	if ($exists !== false) {
1601	+	continue;
1602	+	}
1603	+	if (acp_convert_insert('znote_news', [
1604	+	'title' => substr($title !== '' ? $title : 'Imported news', 0, 30),
1605	+	'text' => acp_convert_news_text((string)$row['body']),
1606	+	'date' => $date > 0 ? $date : time(),
1607	+	'pid' => $pid,
1608	+	])) {
1609	+	$report['news']++;
1610	+	}
1611	+	}
1612	+	}
1613	+	}
1614	+
1615	+	function acp_convert_myaac_changelog(bool $dryRun, array &$report): void {
1616	+	if (acp_convert_table_exists('myaac_changelog') && acp_convert_table_exists('znote_changelog')) {
1617	+	if ($dryRun) {
1618	+	$report['changelog'] = acp_convert_count('myaac_changelog', '`hide` = 0');
1619	+	return;
1620	+	}
1621	+
1622	+	$rows = db()->fetchAll("SELECT * FROM `myaac_changelog` WHERE `hide` = 0 ORDER BY `id` ASC;") ?: [];
1623	+	foreach ($rows as $row) {
1624	+	$text = substr(acp_convert_strip_html((string)$row['body']), 0, 254);
1625	+	$date = (int)$row['date'];
1626	+	$exists = db()->fetchOne("
1627	+	SELECT `id` FROM `znote_changelog`
1628	+	WHERE `text` = ?
1629	+	AND `time` = ?
1630	+	LIMIT 1;
1631	+	", [$text, $date]);
1632	+	if ($text === '' \|\| $exists !== false) {
1633	+	continue;
1634	+	}
1635	+	if (acp_convert_insert('znote_changelog', [
1636	+	'text' => $text,
1637	+	'time' => $date > 0 ? $date : time(),
1638	+	'report_id' => 0,
1639	+	'status' => (int)$row['type'],
1640	+	])) {
1641	+	$report['changelog']++;
1642	+	}
1643	+	}
1644	+	}
1645	+	}
1646	+
1647	+	function acp_convert_myaac_pages(bool $dryRun, array &$report): void {
1648	+	if (acp_convert_table_exists('myaac_pages') && acp_convert_table_exists('znote_pages')) {
1649	+	if ($dryRun) {
1650	+	$report['pages'] = acp_convert_count('myaac_pages', '`hide` = 0');
1651	+	return;
1652	+	}
1653	+
1654	+	$rows = db()->fetchAll("SELECT * FROM `myaac_pages` WHERE `hide` = 0 ORDER BY `id` ASC;") ?: [];
1655	+	foreach ($rows as $row) {
1656	+	$mapped = acp_convert_map_get('myaac', 'myaac_pages', $row['id'], 'znote_pages');
1657	+	if ($mapped > 0) {
1658	+	continue;
1659	+	}
1660	+	$slug = acp_convert_slug((string)$row['name'], 'myaac-page-' . (int)$row['id']);
1661	+	$title = substr(trim((string)$row['title']), 0, 100);
1662	+	$exists = db()->fetchOne('SELECT `id` FROM `znote_pages` WHERE `slug` = ? LIMIT 1;', [$slug]);
1663	+	if ($exists !== false) {
1664	+	acp_convert_map_set('myaac', 'myaac_pages', $row['id'], 'znote_pages', (int)$exists['id']);
1665	+	continue;
1666	+	}
1667	+	if (acp_convert_insert('znote_pages', [
1668	+	'slug' => $slug,
1669	+	'title' => $title !== '' ? $title : $slug,
1670	+	'body' => acp_convert_news_text((string)$row['body']),
1671	+	'created' => (int)$row['date'] > 0 ? (int)$row['date'] : time(),
1672	+	'updated' => (int)$row['date'] > 0 ? (int)$row['date'] : time(),
1673	+	'player_id' => (int)$row['player_id'],
1674	+	'access' => (int)$row['access'],
1675	+	'active' => 1,
1676	+	])) {
1677	+	$newId = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');
1678	+	acp_convert_map_set('myaac', 'myaac_pages', $row['id'], 'znote_pages', $newId);
1679	+	$report['pages']++;
1680	+	}
1681	+	}
1682	+	}
1683	+	}
1684	+
1685	+	function acp_convert_myaac_gallery(bool $dryRun, array &$report): void {
1686	+	if (acp_convert_table_exists('myaac_gallery') && acp_convert_table_exists('znote_images')) {
1687	+	if ($dryRun) {
1688	+	$report['gallery'] = acp_convert_count('myaac_gallery', '`hide` = 0');
1689	+	return;
1690	+	}
1691	+
1692	+	$rows = db()->fetchAll("SELECT * FROM `myaac_gallery` WHERE `hide` = 0 ORDER BY `id` ASC;") ?: [];
1693	+	foreach ($rows as $row) {
1694	+	$mapped = acp_convert_map_get('myaac', 'myaac_gallery', $row['id'], 'znote_images');
1695	+	if ($mapped > 0) {
1696	+	continue;
1697	+	}
1698	+	$image = trim((string)$row['image']);
1699	+	if ($image === '') {
1700	+	continue;
1701	+	}
1702	+	$title = substr(trim((string)($row['comment'] ?: 'Imported image')), 0, 30);
1703	+	$exists = db()->fetchOne('SELECT `id` FROM `znote_images` WHERE `image` = ? LIMIT 1;', [$image]);
1704	+	if ($exists !== false) {
1705	+	acp_convert_map_set('myaac', 'myaac_gallery', $row['id'], 'znote_images', (int)$exists['id']);
1706	+	continue;
1707	+	}
1708	+	if (acp_convert_insert('znote_images', [
1709	+	'title' => $title,
1710	+	'desc' => (string)$row['comment'],
1711	+	'date' => time(),
1712	+	'status' => 2,
1713	+	'image' => substr($image, 0, 255),
1714	+	'delhash' => '',
1715	+	'account_id' => 0,
1716	+	])) {
1717	+	$newId = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');
1718	+	acp_convert_map_set('myaac', 'myaac_gallery', $row['id'], 'znote_images', $newId);
1719	+	$report['gallery']++;
1720	+	}
1721	+	}
1722	+	}
1723	+	}
1724	+
1725	+	function acp_convert_myaac_menu(bool $dryRun, array &$report): void {
1726	+	if (acp_convert_table_exists('myaac_menu') && acp_convert_table_exists('znote_menu')) {
1727	+	if ($dryRun) {
1728	+	$report['menu'] = acp_convert_count('myaac_menu', '`enabled` = 1');
1729	+	return;
1730	+	}
1731	+
1732	+	$rows = db()->fetchAll("SELECT * FROM `myaac_menu` WHERE `enabled` = 1 ORDER BY `ordering` ASC, `id` ASC;") ?: [];
1733	+	foreach ($rows as $row) {
1734	+	$mapped = acp_convert_map_get('myaac', 'myaac_menu', $row['id'], 'znote_menu');
1735	+	if ($mapped > 0) {
1736	+	continue;
1737	+	}
1738	+	$label = substr(trim((string)$row['name']), 0, 64);
1739	+	$url = substr(trim((string)$row['link']), 0, 255);
1740	+	if ($label === '' \|\| $url === '') {
1741	+	continue;
1742	+	}
1743	+	$parentId = acp_convert_menu_parent_id((int)($row['category'] ?? 0));
1744	+	if ($parentId <= 0) {
1745	+	continue;
1746	+	}
1747	+	$exists = db()->fetchOne("
1748	+	SELECT `id` FROM `znote_menu`
1749	+	WHERE `location` = 'main'
1750	+	AND `label` = ?
1751	+	AND `url` = ?
1752	+	LIMIT 1;
1753	+	", [$label, $url]);
1754	+	if ($exists !== false) {
1755	+	acp_convert_map_set('myaac', 'myaac_menu', $row['id'], 'znote_menu', (int)$exists['id']);
1756	+	continue;
1757	+	}
1758	+	if (acp_convert_insert('znote_menu', [
1759	+	'location' => 'main',
1760	+	'parent_id' => $parentId,
1761	+	'label' => $label,
1762	+	'url' => $url,
1763	+	'icon' => '',
1764	+	'target' => !empty($row['blank']) ? '_blank' : '',
1765	+	'visibility' => 'all',
1766	+	'sort_order' => (int)$row['ordering'],
1767	+	'active' => 1,
1768	+	])) {
1769	+	$newId = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');
1770	+	acp_convert_map_set('myaac', 'myaac_menu', $row['id'], 'znote_menu', $newId);
1771	+	$report['menu']++;
1772	+	}
1773	+	}
1774	+	}
1775	+	}
1776	+
1777	+	function acp_convert_myaac_config(bool $dryRun, array &$report): void {
1778	+	if (acp_convert_table_exists('znote_config')) {
1779	+	if ($dryRun) {
1780	+	$report['config'] =
1781	+	acp_convert_count('myaac_config') +
1782	+	acp_convert_count('myaac_settings');
1783	+	return;
1784	+	}
1785	+
1786	+	if (acp_convert_table_exists('myaac_config')) {
1787	+	$rows = db()->fetchAll("SELECT * FROM `myaac_config` ORDER BY `id` ASC;") ?: [];
1788	+	foreach ($rows as $row) {
1789	+	if (acp_convert_config_set('legacy:myaac:' . (string)$row['name'], (string)$row['value'])) {
1790	+	$report['config']++;
1791	+	}
1792	+	}
1793	+	}
1794	+	if (acp_convert_table_exists('myaac_settings')) {
1795	+	$rows = db()->fetchAll("SELECT * FROM `myaac_settings` ORDER BY `id` ASC;") ?: [];
1796	+	foreach ($rows as $row) {
1797	+	if (acp_convert_config_set('legacy:myaac_setting:' . (string)$row['key'], (string)$row['value'])) {
1798	+	$report['config']++;
1799	+	}
1800	+	}
1801	+	}
1802	+	}
1803	+	}
1804	+
1805	+	function acp_convert_myaac_forum_boards(bool $dryRun, array &$report): array {
1806	+	$boardMap = [];
1807	+	if (acp_convert_table_exists('myaac_forum_boards') && acp_convert_table_exists('znote_forum')) {
1808	+	$rows = db()->fetchAll("SELECT * FROM `myaac_forum_boards` ORDER BY `id` ASC;") ?: [];
1809	+	if ($dryRun) {
1810	+	$report['boards'] = count($rows);
1811	+	} else {
1812	+	foreach ($rows as $row) {
1813	+	$name = substr(trim((string)$row['name']), 0, 50);
1814	+	$existing = db()->fetchOne("
1815	+	SELECT `id` FROM `znote_forum`
1816	+	WHERE `name` = ?
1817	+	LIMIT 1;
1818	+	", [$name]);
1819	+	if ($existing !== false) {
1820	+	$boardMap[(int)$row['id']] = (int)$existing['id'];
1821	+	continue;
1822	+	}
1823	+	if (acp_convert_insert('znote_forum', [
1824	+	'name' => $name !== '' ? $name : 'Imported board',
1825	+	'access' => max(1, (int)$row['access']),
1826	+	'closed' => (int)$row['closed'],
1827	+	'hidden' => (int)$row['hide'],
1828	+	'guild_id' => (int)$row['guild'],
1829	+	])) {
1830	+	$boardMap[(int)$row['id']] = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');
1831	+	$report['boards']++;
1832	+	}
1833	+	}
1834	+	}
1835	+	}
1836	+
1837	+	return $boardMap;
1838	+	}
1839	+
1840	+	function acp_convert_myaac_forum_threads(array $boardMap, array &$report): array {
1841	+	$threadMap = [];
1842	+	$threads = db()->fetchAll("SELECT * FROM `myaac_forum` WHERE `id` = `first_post` ORDER BY `id` ASC;") ?: [];
1843	+	foreach ($threads as $row) {
1844	+	$oldBoard = (int)$row['section'];
1845	+	$boardId = $boardMap[$oldBoard] ?? $oldBoard;
1846	+	$title = substr(trim((string)$row['post_topic']), 0, 50);
1847	+	$created = (int)$row['post_date'];
1848	+	$playerId = (int)$row['author_guid'];
1849	+	$existing = db()->fetchOne("
1850	+	SELECT `id` FROM `znote_forum_threads`
1851	+	WHERE `forum_id` = ?
1852	+	AND `player_id` = ?
1853	+	AND `created` = ?
1854	+	AND `title` = ?
1855	+	LIMIT 1;
1856	+	", [$boardId, $playerId, $created, $title]);
1857	+	if ($existing !== false) {
1858	+	$threadMap[(int)$row['id']] = (int)$existing['id'];
1859	+	continue;
1860	+	}
1861	+	if (acp_convert_insert('znote_forum_threads', [
1862	+	'forum_id' => $boardId,
1863	+	'player_id' => $playerId,
1864	+	'player_name' => acp_convert_player_name($playerId),
1865	+	'title' => $title !== '' ? $title : 'Imported thread',
1866	+	'text' => sanitize((string)$row['post_text']),
1867	+	'created' => $created > 0 ? $created : time(),
1868	+	'updated' => (int)$row['edit_date'] > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),
1869	+	'sticky' => (int)$row['sticked'],
1870	+	'hidden' => 0,
1871	+	'closed' => (int)$row['closed'],
1872	+	])) {
1873	+	$threadMap[(int)$row['id']] = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');
1874	+	$report['threads']++;
1875	+	}
1876	+	}
1877	+
1878	+	return $threadMap;
1879	+	}
1880	+
1881	+	function acp_convert_myaac_forum_posts(array $threadMap, array &$report): void {
1882	+	$posts = db()->fetchAll("SELECT * FROM `myaac_forum` WHERE `id` <> `first_post` ORDER BY `id` ASC;") ?: [];
1883	+	foreach ($posts as $row) {
1884	+	$oldThread = (int)$row['first_post'];
1885	+	$threadId = $threadMap[$oldThread] ?? 0;
1886	+	if ($threadId <= 0) {
1887	+	continue;
1888	+	}
1889	+	$created = (int)$row['post_date'];
1890	+	$playerId = (int)$row['author_guid'];
1891	+	$exists = db()->fetchOne("
1892	+	SELECT `id` FROM `znote_forum_posts`
1893	+	WHERE `thread_id` = ?
1894	+	AND `player_id` = ?
1895	+	AND `created` = ?
1896	+	LIMIT 1;
1897	+	", [$threadId, $playerId, $created]);
1898	+	if ($exists !== false) {
1899	+	continue;
1900	+	}
1901	+	if (acp_convert_insert('znote_forum_posts', [
1902	+	'thread_id' => $threadId,
1903	+	'player_id' => $playerId,
1904	+	'player_name' => acp_convert_player_name($playerId),
1905	+	'text' => sanitize((string)$row['post_text']),
1906	+	'created' => $created > 0 ? $created : time(),
1907	+	'updated' => (int)$row['edit_date'] > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),
1908	+	])) {
1909	+	$report['posts']++;
1910	+	}
1911	+	}
1912	+	}
1913	+
1914	+	function acp_convert_myaac_forum(bool $dryRun, array $boardMap, array &$report): void {
1915	+	if (acp_convert_table_exists('myaac_forum') && acp_convert_table_exists('znote_forum_threads') && acp_convert_table_exists('znote_forum_posts')) {
1916	+	if ($dryRun) {
1917	+	$report['threads'] = acp_count("SELECT COUNT(*) AS `c` FROM `myaac_forum` WHERE `id` = `first_post`;");
1918	+	$report['posts'] = acp_count("SELECT COUNT(*) AS `c` FROM `myaac_forum` WHERE `id` <> `first_post`;");
1919	+	return;
1920	+	}
1921	+
1922	+	$threadMap = acp_convert_myaac_forum_threads($boardMap, $report);
1923	+	acp_convert_myaac_forum_posts($threadMap, $report);
1924	+	}
1925	+	}
1926	+
1927	+	function acp_convert_myaac_run(bool $dryRun = true): array {
1928	+	$report = acp_convert_prepare_report('myaac', $dryRun, acp_convert_empty_report());
1929	+
1930	+	acp_convert_myaac_news($dryRun, $report);
1931	+	acp_convert_myaac_changelog($dryRun, $report);
1932	+	acp_convert_myaac_pages($dryRun, $report);
1933	+	acp_convert_myaac_gallery($dryRun, $report);
1934	+	acp_convert_myaac_menu($dryRun, $report);
1935	+	acp_convert_myaac_config($dryRun, $report);
1936	+	$boardMap = acp_convert_myaac_forum_boards($dryRun, $report);
1937	+	acp_convert_myaac_forum($dryRun, $boardMap, $report);
1938	+
1939	+	if ($dryRun && acp_convert_report_empty($report, ['news', 'changelog', 'boards', 'threads', 'posts', 'pages', 'gallery', 'menu', 'config'])) {
1940	+	$report['warnings'][] = 'No MyAAC content tables were found in this database.';
1941	+	}
1942	+
1943	+	return $report;
1944	+	}
1945	+
1946	+	function acp_convert_gesior_news(bool $dryRun, array &$report): void {
1947	+	if (acp_convert_table_exists('z_news_big') && acp_convert_table_exists('znote_news')) {
1948	+	$hideCol = acp_convert_column_exists('z_news_big', 'hide_news') ? 'hide_news' : 'hide';
1949	+	if ($dryRun) {
1950	+	$report['news'] += acp_convert_count('z_news_big', '`' . $hideCol . '` = 0');
1951	+	return;
1952	+	}
1953	+
1954	+	$rows = db()->fetchAll('SELECT * FROM `z_news_big` WHERE `' . acp_convert_identifier($hideCol) . '` = 0 ORDER BY `date` ASC;') ?: [];
1955	+	foreach ($rows as $row) {
1956	+	$title = substr(trim((string)($row['topic'] ?? 'Imported news')), 0, 30);
1957	+	$date = (int)($row['date'] ?? 0);
1958	+	$pid = (int)($row['author_id'] ?? 0);
1959	+	$exists = db()->fetchOne("
1960	+	SELECT `id` FROM `znote_news`
1961	+	WHERE `title` = ?
1962	+	AND `date` = ?
1963	+	LIMIT 1;
1964	+	", [$title, $date]);
1965	+	if ($exists !== false) {
1966	+	continue;
1967	+	}
1968	+	if (acp_convert_insert('znote_news', [
1969	+	'title' => $title,
1970	+	'text' => acp_convert_news_text((string)($row['text'] ?? '')),
1971	+	'date' => $date > 0 ? $date : time(),
1972	+	'pid' => $pid,
1973	+	])) {
1974	+	$report['news']++;
1975	+	}
1976	+	}
1977	+	}
1978	+	}
1979	+
1980	+	function acp_convert_gesior_changelog(bool $dryRun, array &$report): void {
1981	+	if (acp_convert_table_exists('z_news_tickers') && acp_convert_table_exists('znote_changelog')) {
1982	+	$hideWhere = acp_convert_column_exists('z_news_tickers', 'hide_ticker') ? '`hide_ticker` = 0' : '1=1';
1983	+	$textCol = acp_convert_column_exists('z_news_tickers', 'text') ? 'text' : 'body';
1984	+	if ($dryRun) {
1985	+	$report['changelog'] += acp_convert_count('z_news_tickers', $hideWhere);
1986	+	return;
1987	+	}
1988	+
1989	+	$rows = db()->fetchAll("SELECT * FROM `z_news_tickers` WHERE {$hideWhere} ORDER BY `date` ASC;") ?: [];
1990	+	foreach ($rows as $row) {
1991	+	$text = substr(acp_convert_strip_html((string)($row[$textCol] ?? '')), 0, 254);
1992	+	$date = (int)($row['date'] ?? 0);
1993	+	if ($text === '') {
1994	+	continue;
1995	+	}
1996	+	$exists = db()->fetchOne("
1997	+	SELECT `id` FROM `znote_changelog`
1998	+	WHERE `text` = ?
1999	+	AND `time` = ?
2000	+	LIMIT 1;
2001	+	", [$text, $date]);
2002	+	if ($exists !== false) {
2003	+	continue;
2004	+	}
2005	+	if (acp_convert_insert('znote_changelog', [
2006	+	'text' => $text,
2007	+	'time' => $date > 0 ? $date : time(),
2008	+	'report_id' => 0,
2009	+	'status' => 0,
2010	+	])) {
2011	+	$report['changelog']++;
2012	+	}
2013	+	}
2014	+	}
2015	+	}
2016	+
2017	+	function acp_convert_gesior_config(bool $dryRun, array &$report): void {
2018	+	if (acp_convert_table_exists('z_config') && acp_convert_table_exists('znote_config')) {
2019	+	if ($dryRun) {
2020	+	$report['config'] = acp_convert_count('z_config');
2021	+	return;
2022	+	}
2023	+
2024	+	$rows = db()->fetchAll("SELECT * FROM `z_config`;") ?: [];
2025	+	foreach ($rows as $row) {
2026	+	$name = (string)($row['key'] ?? ($row['name'] ?? ($row['config'] ?? '')));
2027	+	$value = (string)($row['value'] ?? '');
2028	+	if ($name !== '' && acp_convert_config_set('legacy:gesior:' . $name, $value)) {
2029	+	$report['config']++;
2030	+	}
2031	+	}
2032	+	}
2033	+	}
2034	+
2035	+	function acp_convert_gesior_forum_threads(string $sourceForum, array &$report): array {
2036	+	$threadMap = [];
2037	+	$threads = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($sourceForum) . '` WHERE `id` = `first_post` ORDER BY `id` ASC;') ?: [];
2038	+	foreach ($threads as $row) {
2039	+	$boardId = max(1, (int)($row['section'] ?? 0));
2040	+	$title = substr(trim((string)($row['post_topic'] ?? 'Imported thread')), 0, 50);
2041	+	$created = (int)($row['post_date'] ?? 0);
2042	+	$playerId = (int)($row['author_guid'] ?? 0);
2043	+	$existing = db()->fetchOne("
2044	+	SELECT `id` FROM `znote_forum_threads`
2045	+	WHERE `forum_id` = ?
2046	+	AND `player_id` = ?
2047	+	AND `created` = ?
2048	+	AND `title` = ?
2049	+	LIMIT 1;
2050	+	", [$boardId, $playerId, $created, $title]);
2051	+	if ($existing !== false) {
2052	+	$threadMap[(int)$row['id']] = (int)$existing['id'];
2053	+	continue;
2054	+	}
2055	+	if (acp_convert_insert('znote_forum_threads', [
2056	+	'forum_id' => $boardId,
2057	+	'player_id' => $playerId,
2058	+	'player_name' => acp_convert_player_name($playerId),
2059	+	'title' => $title,
2060	+	'text' => sanitize((string)($row['post_text'] ?? '')),
2061	+	'created' => $created > 0 ? $created : time(),
2062	+	'updated' => (int)($row['edit_date'] ?? 0) > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),
2063	+	'sticky' => (int)($row['sticked'] ?? 0),
2064	+	'hidden' => 0,
2065	+	'closed' => (int)($row['closed'] ?? 0),
2066	+	])) {
2067	+	$threadMap[(int)$row['id']] = (int)acp_convert_scalar('SELECT LAST_INSERT_ID() AS `v`;');
2068	+	$report['threads']++;
2069	+	}
2070	+	}
2071	+
2072	+	return $threadMap;
2073	+	}
2074	+
2075	+	function acp_convert_gesior_forum_posts(string $sourceForum, array $threadMap, array &$report): void {
2076	+	$posts = db()->fetchAll('SELECT * FROM `' . acp_convert_identifier($sourceForum) . '` WHERE `id` <> `first_post` ORDER BY `id` ASC;') ?: [];
2077	+	foreach ($posts as $row) {
2078	+	$threadId = $threadMap[(int)$row['first_post']] ?? 0;
2079	+	if ($threadId <= 0) {
2080	+	continue;
2081	+	}
2082	+	$created = (int)($row['post_date'] ?? 0);
2083	+	$playerId = (int)($row['author_guid'] ?? 0);
2084	+	$exists = db()->fetchOne("
2085	+	SELECT `id` FROM `znote_forum_posts`
2086	+	WHERE `thread_id` = ?
2087	+	AND `player_id` = ?
2088	+	AND `created` = ?
2089	+	LIMIT 1;
2090	+	", [$threadId, $playerId, $created]);
2091	+	if ($exists !== false) {
2092	+	continue;
2093	+	}
2094	+	if (acp_convert_insert('znote_forum_posts', [
2095	+	'thread_id' => $threadId,
2096	+	'player_id' => $playerId,
2097	+	'player_name' => acp_convert_player_name($playerId),
2098	+	'text' => sanitize((string)($row['post_text'] ?? '')),
2099	+	'created' => $created > 0 ? $created : time(),
2100	+	'updated' => (int)($row['edit_date'] ?? 0) > 0 ? (int)$row['edit_date'] : ($created > 0 ? $created : time()),
2101	+	])) {
2102	+	$report['posts']++;
2103	+	}
2104	+	}
2105	+	}
2106	+
2107	+	function acp_convert_gesior_forum(bool $dryRun, array &$report): void {
2108	+	$sourceForum = acp_convert_table_exists('z_forum') ? 'z_forum' : '';
2109	+	if ($sourceForum === '' \|\| !acp_convert_table_exists('znote_forum_threads') \|\| !acp_convert_table_exists('znote_forum_posts')) {
2110	+	return;
2111	+	}
2112	+
2113	+	if ($dryRun) {
2114	+	$report['threads'] = acp_count("SELECT COUNT(*) AS `c` FROM `{$sourceForum}` WHERE `id` = `first_post`;");
2115	+	$report['posts'] = acp_count("SELECT COUNT(*) AS `c` FROM `{$sourceForum}` WHERE `id` <> `first_post`;");
2116	+	return;
2117	+	}
2118	+
2119	+	$threadMap = acp_convert_gesior_forum_threads($sourceForum, $report);
2120	+	acp_convert_gesior_forum_posts($sourceForum, $threadMap, $report);
2121	+	}
2122	+
2123	+	function acp_convert_gesior_run(bool $dryRun = true): array {
2124	+	$report = acp_convert_prepare_report('gesior', $dryRun, acp_convert_empty_report());
2125	+
2126	+	acp_convert_gesior_news($dryRun, $report);
2127	+	acp_convert_gesior_changelog($dryRun, $report);
2128	+	acp_convert_gesior_config($dryRun, $report);
2129	+	acp_convert_gesior_forum($dryRun, $report);
2130	+
2131	+	if ($dryRun && acp_convert_report_empty($report, ['news', 'changelog', 'threads', 'posts', 'config'])) {
2132	+	$report['warnings'][] = 'No Gesior2012 content tables were found in this database.';
2133	+	}
2134	+
2135	+	return $report;
2136	+	}
2137	+
2138	+	function acp_convert_refresh_cache(): void {
2139	+	if (acp_convert_table_exists('znote_news')) {
2140	+	$cache = new Cache('engine/cache/news');
2141	+	$cache->setContent(fetchAllNews() ?: []);
2142	+	$cache->save();
2143	+	}
2144	+
2145	+	if (acp_convert_table_exists('znote_changelog')) {
2146	+	$cache = new Cache('engine/cache/changelog');
2147	+	$cache->useMemory(false);
2148	+	$cache->setContent(db()->fetchAll("
2149	+	SELECT `id`, `text`, `time`, `report_id`, `status`
2150	+	FROM `znote_changelog`
2151	+	ORDER BY `id` DESC;
2152	+	") ?: []);
2153	+	$cache->save();
2154	+	}
2155	+	}
2156	+
2157	+	function acp_convert_sql_preview(string $statement): string {
2158	+	$statement = preg_replace('/\s+/', ' ', trim($statement));
2159	+	if ($statement === null) {
2160	+	return '';
2161	+	}
2162	+	return strlen($statement) > 500 ? substr($statement, 0, 500) . '...' : $statement;
2163	+	}
2164	+
2165	+	function acp_convert_run_sql_script(string $sql): array {
2166	+	global $aacQueries, $accQueriesData;
2167	+	$connect = db()->connection();
2168	+
2169	+	$statements = acp_convert_dump_split($sql);
2170	+	$report = [
2171	+	'ok' => false,
2172	+	'executed' => 0,
2173	+	'total' => count($statements),
2174	+	'errors' => [],
2175	+	'rolled_back' => false,
2176	+	'started' => date('Y-m-d H:i:s'),
2177	+	'finished' => '',
2178	+	];
2179	+
2180	+	if (!$statements) {
2181	+	$report['errors'][] = [
2182	+	'statement' => 0,
2183	+	'message' => 'No SQL statements were found in the uploaded file.',
2184	+	'sql' => '',
2185	+	];
2186	+	$report['finished'] = date('Y-m-d H:i:s');
2187	+	return $report;
2188	+	}
2189	+
2190	+	$inTransaction = false;
2191	+	foreach ($statements as $index => $statement) {
2192	+	$statement = trim($statement);
2193	+	if ($statement === '') {
2194	+	continue;
2195	+	}
2196	+
2197	+	try {
2198	+	$aacQueries++;
2199	+	$accQueriesData[] = "[" . elapsedTime() . "] " . $statement;
2200	+	$result = mysqli_query($connect, $statement);
2201	+	if ($result instanceof mysqli_result) {
2202	+	mysqli_free_result($result);
2203	+	}
2204	+	$report['executed']++;
2205	+
2206	+	if (preg_match('/^START\s+TRANSACTION\b/i', $statement)) {
2207	+	$inTransaction = true;
2208	+	} elseif (preg_match('/^(COMMIT\|ROLLBACK)\b/i', $statement)) {
2209	+	$inTransaction = false;
2210	+	}
2211	+	} catch (mysqli_sql_exception $e) {
2212	+	$report['errors'][] = [
2213	+	'statement' => $index + 1,
2214	+	'message' => $e->getMessage(),
2215	+	'sql' => acp_convert_sql_preview($statement),
2216	+	];
2217	+
2218	+	if ($inTransaction) {
2219	+	try {
2220	+	mysqli_query($connect, 'ROLLBACK');
2221	+	$report['rolled_back'] = true;
2222	+	} catch (mysqli_sql_exception $rollbackError) {
2223	+	$report['errors'][] = [
2224	+	'statement' => 0,
2225	+	'message' => 'Rollback failed: ' . $rollbackError->getMessage(),
2226	+	'sql' => 'ROLLBACK',
2227	+	];
2228	+	}
2229	+	}
2230	+	break;
2231	+	}
2232	+	}
2233	+
2234	+	$report['ok'] = empty($report['errors']);
2235	+	$report['finished'] = date('Y-m-d H:i:s');
2236	+	return $report;
2237	+	}
2238	+
2239	+	function acp_convert_remap_report(string $sql): array {
2240	+	if (!preg_match('/^-- Source detected:\s*([a-z0-9_-]+)/mi', $sql, $match)) {
2241	+	return ['accounts' => 0, 'players' => 0];
2242	+	}
2243	+
2244	+	$source = (string)$match[1];
2245	+	$rows = db()->fetchAll("
2246	+	SELECT `source_table`, COUNT(*) AS `total`
2247	+	FROM `znote_convert_map`
2248	+	WHERE `source` = ?
2249	+	AND `target_table` IN ('accounts', 'players')
2250	+	AND CAST(`source_id` AS UNSIGNED) <> `target_id`
2251	+	GROUP BY `source_table`;
2252	+	", [$source]) ?: [];
2253	+	$report = ['accounts' => 0, 'players' => 0];
2254	+	foreach ($rows as $row) {
2255	+	$table = (string)($row['source_table'] ?? '');
2256	+	if (isset($report[$table])) {
2257	+	$report[$table] = (int)$row['total'];
2258	+	}
2259	+	}
2260	+	return $report;
2261	+	}
2262	+
2263	+	function acp_convert_current_account_snapshot(): array {
2264	+	global $session_user_id;
2265	+
2266	+	$accountId = (int)($session_user_id ?? 0);
2267	+	if ($accountId <= 0 \|\| !acp_convert_table_exists('accounts')) {
2268	+	return [];
2269	+	}
2270	+
2271	+	$row = db()->fetchOne('SELECT * FROM `accounts` WHERE `id` = ? LIMIT 1;', [$accountId]);
2272	+	return is_array($row) ? $row : [];
2273	+	}
2274	+
2275	+	function acp_convert_restore_account_snapshot(array $snapshot): bool {
2276	+	if (!$snapshot \|\| empty($snapshot['id']) \|\| !acp_convert_table_exists('accounts')) {
2277	+	return false;
2278	+	}
2279	+
2280	+	$columns = db()->fetchAll("SHOW COLUMNS FROM `accounts`;") ?: [];
2281	+	$available = [];
2282	+	foreach ($columns as $column) {
2283	+	if (!empty($column['Field'])) {
2284	+	$available[(string)$column['Field']] = true;
2285	+	}
2286	+	}
2287	+
2288	+	$sets = [];
2289	+	$params = [];
2290	+	foreach ($snapshot as $column => $value) {
2291	+	if ($column === 'id' \|\| empty($available[$column])) {
2292	+	continue;
2293	+	}
2294	+	$sets[] = '`' . acp_convert_identifier((string)$column) . '` = ?';
2295	+	$params[] = $value;
2296	+	}
2297	+
2298	+	if (!$sets) {
2299	+	return false;
2300	+	}
2301	+
2302	+	$params[] = (int)$snapshot['id'];
2303	+
2304	+	return db()->execute("
2305	+	UPDATE `accounts`
2306	+	SET " . implode(', ', $sets) . "
2307	+	WHERE `id` = ?
2308	+	LIMIT 1;
2309	+	", $params);
2310	+	}
2311	+
2312	+	function acp_convert_sql_script_header(string $source): array {
2313	+	$now = date('Y-m-d H:i:s');
2314	+	return [
2315	+	"-- ZnoteX {$source} conversion SQL",
2316	+	"-- Generated {$now}",
2317	+	"-- Import this into a database that already contains the legacy {$source} tables and the ZnoteX schema.",
2318	+	"START TRANSACTION;",
2319	+	"",
2320	+	file_get_contents('SQL/migrations/2.0.0_pages_and_convert_map.sql') ?: '',
2321	+	];
2322	+	}
2323	+
2324	+	function acp_convert_sql_script_compatibility(): array {
2325	+	return [
2326	+	"",
2327	+	"-- Accounts and players compatibility rows",
2328	+	"INSERT INTO `znote_accounts` (`account_id`, `ip`, `created`, `flag`)
2329	+	SELECT `a`.`id`, 0, UNIX_TIMESTAMP(CURDATE()), ''
2330	+	FROM `accounts` AS `a`
2331	+	LEFT JOIN `znote_accounts` AS `z` ON `z`.`account_id` = `a`.`id`
2332	+	WHERE `z`.`id` IS NULL;",
2333	+	"INSERT INTO `znote_players` (`player_id`, `created`, `hide_char`, `comment`)
2334	+	SELECT `p`.`id`, UNIX_TIMESTAMP(CURDATE()), 0, ''
2335	+	FROM `players` AS `p`
2336	+	LEFT JOIN `znote_players` AS `z` ON `z`.`player_id` = `p`.`id`
2337	+	WHERE `z`.`id` IS NULL;",
2338	+	];
2339	+	}
2340	+
2341	+	function acp_convert_sql_script_myaac_news(): array {
2342	+	if (!acp_convert_table_exists('myaac_news')) {
2343	+	return [];
2344	+	}
2345	+
2346	+	return [
2347	+	"",
2348	+	"-- MyAAC news",
2349	+	"INSERT INTO `znote_news` (`title`, `text`, `date`, `pid`)
2350	+	SELECT LEFT(`m`.`title`, 30), `m`.`body`, IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()), `m`.`player_id`
2351	+	FROM `myaac_news` AS `m`
2352	+	LEFT JOIN `znote_news` AS `z` ON `z`.`title` = LEFT(`m`.`title`, 30) AND `z`.`date` = `m`.`date`
2353	+	WHERE `m`.`hide` = 0 AND `m`.`type` IN (1, 3) AND `z`.`id` IS NULL;",
2354	+	];
2355	+	}
2356	+
2357	+	function acp_convert_sql_script_myaac_changelog(): array {
2358	+	if (!acp_convert_table_exists('myaac_changelog')) {
2359	+	return [];
2360	+	}
2361	+
2362	+	return [
2363	+	"",
2364	+	"-- MyAAC changelog",
2365	+	"INSERT INTO `znote_changelog` (`text`, `time`, `report_id`, `status`)
2366	+	SELECT LEFT(`m`.`body`, 254), IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()), 0, `m`.`type`
2367	+	FROM `myaac_changelog` AS `m`
2368	+	LEFT JOIN `znote_changelog` AS `z` ON `z`.`text` = LEFT(`m`.`body`, 254) AND `z`.`time` = `m`.`date`
2369	+	WHERE `m`.`hide` = 0 AND `m`.`body` <> '' AND `z`.`id` IS NULL;",
2370	+	];
2371	+	}
2372	+
2373	+	function acp_convert_sql_script_myaac_pages(): array {
2374	+	if (!acp_convert_table_exists('myaac_pages')) {
2375	+	return [];
2376	+	}
2377	+
2378	+	return [
2379	+	"",
2380	+	"-- MyAAC custom pages",
2381	+	"INSERT INTO `znote_pages` (`slug`, `title`, `body`, `created`, `updated`, `player_id`, `access`, `active`)
2382	+	SELECT
2383	+	LEFT(LOWER(REPLACE(REPLACE(`m`.`name`, ' ', '-'), '/', '-')), 64),
2384	+	LEFT(`m`.`title`, 100),
2385	+	`m`.`body`,
2386	+	IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()),
2387	+	IF(`m`.`date` > 0, `m`.`date`, UNIX_TIMESTAMP()),
2388	+	`m`.`player_id`,
2389	+	`m`.`access`,
2390	+	1
2391	+	FROM `myaac_pages` AS `m`
2392	+	LEFT JOIN `znote_pages` AS `z` ON `z`.`slug` = LEFT(LOWER(REPLACE(REPLACE(`m`.`name`, ' ', '-'), '/', '-')), 64)
2393	+	WHERE `m`.`hide` = 0 AND `z`.`id` IS NULL;",
2394	+	];
2395	+	}
2396	+
2397	+	function acp_convert_sql_script_myaac_gallery(): array {
2398	+	if (!acp_convert_table_exists('myaac_gallery')) {
2399	+	return [];
2400	+	}
2401	+
2402	+	return [
2403	+	"",
2404	+	"-- MyAAC gallery",
2405	+	"INSERT INTO `znote_images` (`title`, `desc`, `date`, `status`, `image`, `delhash`, `account_id`)
2406	+	SELECT LEFT(IF(`m`.`comment` <> '', `m`.`comment`, 'Imported image'), 30), `m`.`comment`, UNIX_TIMESTAMP(), 2, LEFT(`m`.`image`, 255), '', 0
2407	+	FROM `myaac_gallery` AS `m`
2408	+	LEFT JOIN `znote_images` AS `z` ON `z`.`image` = LEFT(`m`.`image`, 255)
2409	+	WHERE `m`.`hide` = 0 AND `m`.`image` <> '' AND `z`.`id` IS NULL;",
2410	+	];
2411	+	}
2412	+
2413	+	function acp_convert_sql_script_myaac_menu(): array {
2414	+	if (!acp_convert_table_exists('myaac_menu')) {
2415	+	return [];
2416	+	}
2417	+
2418	+	return [
2419	+	"",
2420	+	"-- MyAAC menu",
2421	+	"INSERT INTO `znote_menu` (`location`, `parent_id`, `label`, `url`, `icon`, `target`, `visibility`, `sort_order`, `active`)
2422	+	SELECT 'main', `p`.`id`, LEFT(`m`.`name`, 64), LEFT(`m`.`link`, 255), '', IF(`m`.`blank` = 1, '_blank', ''), 'all', `m`.`ordering`, 1
2423	+	FROM `myaac_menu` AS `m`
2424	+	INNER JOIN `znote_menu` AS `p`
2425	+	ON `p`.`location` = 'main'
2426	+	AND `p`.`parent_id` = 0
2427	+	AND `p`.`label` = CASE `m`.`category`
2428	+	WHEN 1 THEN 'Home'
2429	+	WHEN 2 THEN 'Account'
2430	+	WHEN 3 THEN 'Community'
2431	+	WHEN 4 THEN 'Community'
2432	+	WHEN 5 THEN 'Library'
2433	+	WHEN 6 THEN 'Shop'
2434	+	ELSE ''
2435	+	END
2436	+	LEFT JOIN `znote_menu` AS `z` ON `z`.`location` = 'main' AND `z`.`label` = LEFT(`m`.`name`, 64) AND `z`.`url` = LEFT(`m`.`link`, 255)
2437	+	WHERE `m`.`enabled` = 1 AND `m`.`name` <> '' AND `m`.`link` <> '' AND `z`.`id` IS NULL;",
2438	+	];
2439	+	}
2440	+
2441	+	function acp_convert_sql_script_myaac_config(): array {
2442	+	if (!acp_convert_table_exists('myaac_config')) {
2443	+	return [];
2444	+	}
2445	+
2446	+	return [
2447	+	"",
2448	+	"-- MyAAC config preserved as legacy keys",
2449	+	"INSERT INTO `znote_config` (`key`, `value`)
2450	+	SELECT LEFT(CONCAT('legacy:myaac:', `name`), 64), `value`
2451	+	FROM `myaac_config`
2452	+	ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);",
2453	+	];
2454	+	}
2455	+
2456	+	function acp_convert_sql_script_myaac_settings(): array {
2457	+	if (!acp_convert_table_exists('myaac_settings')) {
2458	+	return [];
2459	+	}
2460	+
2461	+	return [
2462	+	"INSERT INTO `znote_config` (`key`, `value`)
2463	+	SELECT LEFT(CONCAT('legacy:myaac_setting:', `key`), 64), `value`
2464	+	FROM `myaac_settings`
2465	+	ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);",
2466	+	];
2467	+	}
2468	+
2469	+	function acp_convert_sql_script_myaac_sections(): array {
2470	+	$sql = [];
2471	+	foreach ([
2472	+	acp_convert_sql_script_myaac_news(),
2473	+	acp_convert_sql_script_myaac_changelog(),
2474	+	acp_convert_sql_script_myaac_pages(),
2475	+	acp_convert_sql_script_myaac_gallery(),
2476	+	acp_convert_sql_script_myaac_menu(),
2477	+	acp_convert_sql_script_myaac_config(),
2478	+	acp_convert_sql_script_myaac_settings(),
2479	+	] as $section) {
2480	+	array_push($sql, ...$section);
2481	+	}
2482	+
2483	+	return $sql;
2484	+	}
2485	+
2486	+	function acp_convert_sql_script_gesior_news(): array {
2487	+	if (!acp_convert_table_exists('z_news_big')) {
2488	+	return [];
2489	+	}
2490	+
2491	+	$hideCol = acp_convert_column_exists('z_news_big', 'hide_news') ? 'hide_news' : 'hide';
2492	+	return [
2493	+	"",
2494	+	"-- Gesior big news",
2495	+	"INSERT INTO `znote_news` (`title`, `text`, `date`, `pid`)
2496	+	SELECT LEFT(`g`.`topic`, 30), `g`.`text`, IF(`g`.`date` > 0, `g`.`date`, UNIX_TIMESTAMP()), IFNULL(`g`.`author_id`, 0)
2497	+	FROM `z_news_big` AS `g`
2498	+	LEFT JOIN `znote_news` AS `z` ON `z`.`title` = LEFT(`g`.`topic`, 30) AND `z`.`date` = `g`.`date`
2499	+	WHERE `g`.`{$hideCol}` = 0 AND `z`.`id` IS NULL;",
2500	+	];
2501	+	}
2502	+
2503	+	function acp_convert_sql_script_gesior_tickers(): array {
2504	+	if (!acp_convert_table_exists('z_news_tickers')) {
2505	+	return [];
2506	+	}
2507	+
2508	+	$hideWhere = acp_convert_column_exists('z_news_tickers', 'hide_ticker') ? "`g`.`hide_ticker` = 0" : '1=1';
2509	+	$textCol = acp_convert_column_exists('z_news_tickers', 'text') ? 'text' : 'body';
2510	+	return [
2511	+	"",
2512	+	"-- Gesior tickers as Znote changelog entries",
2513	+	"INSERT INTO `znote_changelog` (`text`, `time`, `report_id`, `status`)
2514	+	SELECT LEFT(`g`.`{$textCol}`, 254), IF(`g`.`date` > 0, `g`.`date`, UNIX_TIMESTAMP()), 0, 0
2515	+	FROM `z_news_tickers` AS `g`
2516	+	LEFT JOIN `znote_changelog` AS `z` ON `z`.`text` = LEFT(`g`.`{$textCol}`, 254) AND `z`.`time` = `g`.`date`
2517	+	WHERE {$hideWhere} AND `g`.`{$textCol}` <> '' AND `z`.`id` IS NULL;",
2518	+	];
2519	+	}
2520	+
2521	+	function acp_convert_sql_script_gesior_sections(): array {
2522	+	$sql = [];
2523	+	foreach ([
2524	+	acp_convert_sql_script_gesior_news(),
2525	+	acp_convert_sql_script_gesior_tickers(),
2526	+	] as $section) {
2527	+	array_push($sql, ...$section);
2528	+	}
2529	+
2530	+	return $sql;
2531	+	}
2532	+
2533	+	function acp_convert_sql_script_archive_section(string $source): array {
2534	+	$archive = acp_convert_legacy_archive_sql($source);
2535	+	if ($archive !== '') {
2536	+	return [
2537	+	"",
2538	+	"-- Lossless legacy archive for custom tables and columns",
2539	+	$archive,
2540	+	];
2541	+	}
2542	+
2543	+	return [];
2544	+	}
2545	+
2546	+	function acp_convert_sql_script(string $source): string {
2547	+	$sql = [];
2548	+	foreach ([
2549	+	acp_convert_sql_script_header($source),
2550	+	acp_convert_sql_script_compatibility(),
2551	+	$source === 'myaac' ? acp_convert_sql_script_myaac_sections() : [],
2552	+	$source === 'gesior' ? acp_convert_sql_script_gesior_sections() : [],
2553	+	acp_convert_sql_script_archive_section($source),
2554	+	["", "COMMIT;", "-- Rebuild the ZnoteX news/changelog cache from the admin panel after importing."],
2555	+	] as $section) {
2556	+	array_push($sql, ...$section);
2557	+	}
2558	+
2559	+	return trim(implode("\n\n", $sql)) . "\n";
2560	+	}
2561	+
2562	+	if ($_SERVER['REQUEST_METHOD'] === 'POST') {
2563	+	$source = (string)($_POST['source'] ?? '');
2564	+	$action = (string)($_POST['action'] ?? 'upload_download');
2565	+
2566	+	if ($action === 'upload_download') {
2567	+	if (!in_array($source, ['auto', 'myaac', 'gesior'], true)) {
2568	+	acp_flash_error(t('acp.conv.err_choose_source'));
2569	+	acp_redirect('convert');
2570	+	}
2571	+
2572	+	if (!isset($_FILES['sql_file']) \|\| !is_uploaded_file($_FILES['sql_file']['tmp_name'])) {
2573	+	acp_flash_error(t('acp.conv.err_no_dump'));
2574	+	acp_redirect('convert');
2575	+	}
2576	+
2577	+	$size = (int)($_FILES['sql_file']['size'] ?? 0);
2578	+	if ($size <= 0 \|\| $size > 256 * 1024 * 1024) {
2579	+	acp_flash_error(t('acp.conv.err_dump_size'));
2580	+	acp_redirect('convert');
2581	+	}
2582	+
2583	+	$dump = (string)file_get_contents($_FILES['sql_file']['tmp_name']);
2584	+	$model = acp_convert_dump_model($dump);
2585	+	$converted = acp_convert_dump_script($model, $source);
2586	+	$detected = acp_convert_dump_source($model, $source);
2587	+
2588	+	header('Content-Type: application/sql; charset=UTF-8');
2589	+	header('Content-Disposition: attachment; filename="znote_' . $detected . '_converted.sql"');
2590	+	header('Content-Length: ' . strlen($converted));
2591	+	echo $converted;
2592	+	exit;
2593	+	}
2594	+
2595	+	if ($action === 'import_converted') {
2596	+	if (!isset($_FILES['converted_sql_file']) \|\| !is_uploaded_file($_FILES['converted_sql_file']['tmp_name'])) {
2597	+	acp_flash_error(t('acp.conv.err_no_converted_file'));
2598	+	acp_redirect('convert');
2599	+	}
2600	+
2601	+	$size = (int)($_FILES['converted_sql_file']['size'] ?? 0);
2602	+	if ($size <= 0 \|\| $size > 256 * 1024 * 1024) {
2603	+	acp_flash_error(t('acp.conv.err_converted_size'));
2604	+	acp_redirect('convert');
2605	+	}
2606	+
2607	+	$sql = (string)file_get_contents($_FILES['converted_sql_file']['tmp_name']);
2608	+	if (strpos($sql, '-- ZnoteX uploaded SQL conversion') === false \|\| strpos($sql, 'znote_legacy_tables') === false) {
2609	+	acp_flash_error(t('acp.conv.err_not_generated'));
2610	+	acp_redirect('convert');
2611	+	}
2612	+
2613	+	$currentAccount = acp_convert_current_account_snapshot();
2614	+	acp_convert_ensure_tables();
2615	+	$report = acp_convert_run_sql_script($sql);
2616	+	$report['protected_account'] = acp_convert_restore_account_snapshot($currentAccount);
2617	+	$report['remapped'] = acp_convert_remap_report($sql);
2618	+	$report['file'] = (string)($_FILES['converted_sql_file']['name'] ?? 'converted.sql');
2619	+	$report['size'] = $size;
2620	+	$_SESSION['acp_convert_import_report'] = $report;
2621	+
2622	+	if ($report['ok']) {
2623	+	acp_convert_refresh_cache();
2624	+	acp_log('convert.import', $report['file'], ['statements_executed' => (int)$report['executed']]);
2625	+	acp_flash_success(t('acp.conv.imported_success', ['n' => (int)$report['executed']]));
2626	+	} else {
2627	+	$error = $report['errors'][0] ?? ['statement' => 0, 'message' => t('acp.conv.unknown_sql_error')];
2628	+	acp_flash_error(t('acp.conv.import_stopped', ['n' => (int)$error['statement'], 'message' => h((string)$error['message'])]));
2629	+	}
2630	+	acp_redirect('convert');
2631	+	}
2632	+
2633	+	acp_flash_error(t('acp.conv.err_unknown_action'));
2634	+	acp_redirect('convert');
2635	+	}
2636	+
2637	+	$importReport = $_SESSION['acp_convert_import_report'] ?? null;
2638	+	unset($_SESSION['acp_convert_import_report']);
2639	+	?>
2640	+
2641	+	<style>
2642	+	.acp-convert-progress { display: none; margin: 0 0 16px; }
2643	+	.acp-convert-progress.is-active { display: block; }
2644	+	.acp-convert-bar { height: 8px; overflow: hidden; border-radius: 4px; background: var(--acp-panel-2); }
2645	+	.acp-convert-bar span { display: block; width: 35%; height: 100%; background: var(--acp-blue-600); animation: acpConvertMove 1.1s ease-in-out infinite; }
2646	+	.acp-convert-terminal { margin-top: 10px; padding: 10px 12px; border-radius: var(--acp-radius); background: #101820; color: #c9f7d1; font-family: Consolas, Monaco, monospace; font-size: 12.5px; line-height: 1.45; }
2647	+	.acp-convert-terminal div { white-space: pre-wrap; }
2648	+	.acp-convert-terminal strong { color: #fff; }
2649	+	.acp-convert-terminal--error { color: #ffd1d1; }
2650	+	@keyframes acpConvertMove {
2651	+	0% { transform: translateX(-110%); }
2652	+	100% { transform: translateX(310%); }
2653	+	}
2654	+	</style>
2655	+
2656	+	<div class="acp-convert-progress" id="convertProgress">
2657	+	<div class="acp-convert-bar"><span></span></div>
2658	+	<div class="acp-convert-terminal" id="convertTerminal">
2659	+	<div>$ waiting for action...</div>
2660	+	</div>
2661	+	</div>
2662	+
2663	+	<div class="acp-flash acp-flash--info">
2664	+	<i class="fa fa-info-circle"></i>
2665	+	<span><?= h(t('acp.conv.info_banner')) ?></span>
2666	+	</div>
2667	+
2668	+	<section class="acp-card">
2669	+	<header class="acp-card-head">
2670	+	<h2><?= h(t('acp.conv.convert_title')) ?></h2>
2671	+	<p><?= h(t('acp.conv.convert_sub')) ?></p>
2672	+	</header>
2673	+	<div class="acp-card-body">
2674	+	<form method="post" enctype="multipart/form-data" data-convert-form>
2675	+	<?= acp_csrf_field() ?>
2676	+	<input type="hidden" name="action" value="upload_download">
2677	+
2678	+	<div class="acp-field">
2679	+	<label class="acp-label" for="source"><?= h(t('acp.conv.source_label')) ?></label>
2680	+	<select class="acp-select" id="source" name="source">
2681	+	<option value="auto"><?= h(t('acp.conv.source_auto')) ?></option>
2682	+	<option value="myaac"><?= h(t('acp.conv.source_myaac')) ?></option>
2683	+	<option value="gesior"><?= h(t('acp.conv.source_gesior')) ?></option>
2684	+	</select>
2685	+	<p class="acp-hint"><?= t('acp.conv.source_hint', ['tag1' => '<code>myaac_news</code>', 'tag2' => '<code>z_news_big</code>']) ?></p>
2686	+	</div>
2687	+
2688	+	<div class="acp-field">
2689	+	<label class="acp-label" for="sql_file"><?= h(t('acp.conv.sql_dump_label')) ?></label>
2690	+	<input class="acp-input" id="sql_file" name="sql_file" type="file" accept=".sql,text/sql,text/plain" required>
2691	+	<p class="acp-hint"><?= h(t('acp.conv.sql_dump_hint')) ?></p>
2692	+	</div>
2693	+
2694	+	<div class="acp-actions">
2695	+	<button class="acp-btn acp-btn--blue" type="submit">
2696	+	<i class="fa fa-download"></i> <?= h(t('acp.conv.convert_btn')) ?>
2697	+	</button>
2698	+	</div>
2699	+	</form>
2700	+	</div>
2701	+	</section>
2702	+
2703	+	<section class="acp-card">
2704	+	<header class="acp-card-head">
2705	+	<h2><?= h(t('acp.conv.import_title')) ?></h2>
2706	+	<p><?= h(t('acp.conv.import_sub')) ?></p>
2707	+	</header>
2708	+	<div class="acp-card-body">
2709	+	<form method="post" enctype="multipart/form-data" data-convert-form>
2710	+	<?= acp_csrf_field() ?>
2711	+	<input type="hidden" name="action" value="import_converted">
2712	+
2713	+	<div class="acp-field">
2714	+	<label class="acp-label" for="converted_sql_file"><?= h(t('acp.conv.converted_sql_label')) ?></label>
2715	+	<input class="acp-input" id="converted_sql_file" name="converted_sql_file" type="file" accept=".sql,text/sql,text/plain" required>
2716	+	<p class="acp-hint"><?= h(t('acp.conv.converted_sql_hint')) ?></p>
2717	+	</div>
2718	+
2719	+	<div class="acp-actions">
2720	+	<button class="acp-btn acp-btn--green" type="submit">
2721	+	<i class="fa fa-upload"></i> <?= h(t('acp.conv.import_btn')) ?>
2722	+	</button>
2723	+	</div>
2724	+	</form>
2725	+
2726	+	<?php if (is_array($importReport)): ?>
2727	+	<div class="acp-convert-terminal<?= empty($importReport['errors']) ? '' : ' acp-convert-terminal--error' ?>">
2728	+	<div><strong>$ <?= h(t('acp.conv.report_title')) ?></strong></div>
2729	+	<div>$ <?= h(t('acp.conv.report_file')) ?>: <?= h((string)($importReport['file'] ?? 'converted.sql')) ?> (<?= number_format((int)($importReport['size'] ?? 0)) ?> bytes)</div>
2730	+	<div>$ <?= h(t('acp.conv.report_started')) ?>: <?= h((string)($importReport['started'] ?? '')) ?></div>
2731	+	<div>$ <?= h(t('acp.conv.report_finished')) ?>: <?= h((string)($importReport['finished'] ?? '')) ?></div>
2732	+	<div>$ <?= h(t('acp.conv.report_statements')) ?>: <?= (int)($importReport['executed'] ?? 0) ?> / <?= (int)($importReport['total'] ?? 0) ?></div>
2733	+	<div>$ <?= h(t('acp.conv.report_status')) ?>: <?= !empty($importReport['ok']) ? h(t('acp.conv.status_ok')) : h(t('acp.conv.status_error')) ?></div>
2734	+	<div>$ <?= h(t('acp.conv.report_protected')) ?>: <?= !empty($importReport['protected_account']) ? h(t('acp.conv.yes')) : h(t('acp.conv.no')) ?></div>
2735	+	<div>$ <?= h(t('acp.conv.report_remapped_accounts')) ?>: <?= (int)($importReport['remapped']['accounts'] ?? 0) ?></div>
2736	+	<div>$ <?= h(t('acp.conv.report_remapped_players')) ?>: <?= (int)($importReport['remapped']['players'] ?? 0) ?></div>
2737	+	<?php if (!empty($importReport['rolled_back'])): ?>
2738	+	<div>$ <?= h(t('acp.conv.report_rollback')) ?></div>
2739	+	<?php endif; ?>
2740	+	<?php foreach (($importReport['errors'] ?? []) as $error): ?>
2741	+	<div>$ <?= h(t('acp.conv.report_error_statement', ['n' => (int)($error['statement'] ?? 0), 'message' => (string)($error['message'] ?? t('acp.conv.unknown_sql_error'))])) ?></div>
2742	+	<?php if (!empty($error['sql'])): ?>
2743	+	<div>$ <?= h(t('acp.conv.report_sql')) ?>: <?= h((string)$error['sql']) ?></div>
2744	+	<?php endif; ?>
2745	+	<?php endforeach; ?>
2746	+	</div>
2747	+	<?php endif; ?>
2748	+	</div>
2749	+	</section>
2750	+
2751	+	<section class="acp-card">
2752	+	<header class="acp-card-head">
2753	+	<h2><?= h(t('acp.conv.what_gets_converted_title')) ?></h2>
2754	+	</header>
2755	+	<div class="acp-card-body">
2756	+	<p><?= h(t('acp.conv.info_p1')) ?></p>
2757	+	<p><?= t('acp.conv.info_p2', [
2758	+	'myaac_news' => '<code>myaac_news</code>',
2759	+	'myaac_changelog' => '<code>myaac_changelog</code>',
2760	+	'myaac_forum_boards' => '<code>myaac_forum_boards</code>',
2761	+	'myaac_forum' => '<code>myaac_forum</code>',
2762	+	'myaac_pages' => '<code>myaac_pages</code>',
2763	+	'myaac_gallery' => '<code>myaac_gallery</code>',
2764	+	'myaac_menu' => '<code>myaac_menu</code>',
2765	+	'z_news_big' => '<code>z_news_big</code>',
2766	+	'z_news_tickers' => '<code>z_news_tickers</code>',
2767	+	'z_forum' => '<code>z_forum</code>',
2768	+	'legacy' => '<code>legacy:*</code>',
2769	+	'znote_config' => '<code>znote_config</code>',
2770	+	'znote_legacy_tables' => '<code>znote_legacy_tables</code>',
2771	+	'znote_legacy_rows' => '<code>znote_legacy_rows</code>',
2772	+	]) ?></p>
2773	+	</div>
2774	+	</section>
2775	+
2776	+	<script>
2777	+	(function () {
2778	+	var progress = document.getElementById('convertProgress');
2779	+	var terminal = document.getElementById('convertTerminal');
2780	+	if (!progress \|\| !terminal) return;
2781	+
2782	+	function line(text) {
2783	+	var div = document.createElement('div');
2784	+	div.textContent = text;
2785	+	terminal.appendChild(div);
2786	+	}
2787	+
2788	+	document.addEventListener('submit', function (event) {
2789	+	var form = event.target;
2790	+	if (!form \|\| !form.hasAttribute('data-convert-form')) return;
2791	+
2792	+	var action = form.querySelector('input[name="action"]');
2793	+	var source = form.querySelector('select[name="source"]');
2794	+	var file = form.querySelector('input[type="file"]');
2795	+	var actionValue = action ? action.value : 'convert';
2796	+	progress.classList.add('is-active');
2797	+	terminal.innerHTML = '';
2798	+	line('$ file: ' + (file && file.files && file.files[0] ? file.files[0].name : 'uploaded sql'));
2799	+	line('$ source: ' + (source ? source.value : 'converted znote sql'));
2800	+	line('$ action: ' + actionValue);
2801	+	if (actionValue === 'import_converted') {
2802	+	line('$ executing converted SQL...');
2803	+	line('$ errors will be printed here after redirect');
2804	+	} else {
2805	+	line('$ checking tables...');
2806	+	line('$ preserving custom legacy rows...');
2807	+	line('$ browser will receive the converted SQL file when finished');
2808	+	}
2809	+	}, true);
2810	+	})();
2811	+	</script>