<?php
// usage:
//   php __CRON/cron_vpnid_columns_rename_cli.php

@chdir(__DIR__ . '/..');

include_once(__DIR__ . '/../_common.php');

function vpnid_col_info($tableName, $columnName)
{
	$tableName = trim((string)$tableName);
	$columnName = trim((string)$columnName);
	if ($tableName === '' || $columnName === '') {
		return null;
	}

	$sql = "SHOW FULL COLUMNS FROM `" . sql_escape_string($tableName) . "` LIKE '" . sql_escape_string($columnName) . "'";
	$row = sql_fetch($sql, false);
	if (!is_array($row) || !isset($row['Field'])) {
		return null;
	}

	return $row;
}

function vpnid_build_col_def($col)
{
	$type = trim((string)($col['Type'] ?? ''));
	if ($type === '') {
		return '';
	}

	$null = strtoupper(trim((string)($col['Null'] ?? 'YES')));
	$default = array_key_exists('Default', (array)$col) ? $col['Default'] : null;
	$extra = trim((string)($col['Extra'] ?? ''));

	$parts = array();
	$parts[] = $type;
	$parts[] = ($null === 'NO') ? 'NOT NULL' : 'NULL';

	if ($default !== null) {
		if (strtoupper((string)$default) === 'CURRENT_TIMESTAMP') {
			$parts[] = 'DEFAULT CURRENT_TIMESTAMP';
		} else {
			$parts[] = "DEFAULT '" . sql_escape_string((string)$default) . "'";
		}
	} elseif ($null === 'YES') {
		$parts[] = 'DEFAULT NULL';
	}

	if ($extra !== '') {
		$parts[] = $extra;
	}

	return implode(' ', $parts);
}

$targets = array(
	'vpnid_list',
	'vpnid_list_free',
	'vpnid_list_log',
	'vpnid_list_log_free',
	'vpnid_free'
);

$renameMap = array(
	'member_id' => 'vpn_user_id',
	'member_pwd' => 'vpn_user_pwd',
	'member_id_expire' => 'vpn_id_expire',
	'member_memo' => 'vpn_memo'
);

$report = array(
	'ok' => true,
	'renamed' => array(),
	'skipped' => array(),
	'errors' => array(),
	'ts' => date('Y-m-d H:i:s')
);

$logPath = G5_PATH . '/data/cron_vpnid_columns_rename.log';

foreach ($targets as $tableName) {
	foreach ($renameMap as $oldCol => $newCol) {
		$oldInfo = vpnid_col_info($tableName, $oldCol);
		$newInfo = vpnid_col_info($tableName, $newCol);

		if ($oldInfo === null && $newInfo !== null) {
			$report['skipped'][] = array(
				'table' => $tableName,
				'old' => $oldCol,
				'new' => $newCol,
				'reason' => 'already_renamed'
			);
			continue;
		}

		if ($oldInfo === null && $newInfo === null) {
			$report['skipped'][] = array(
				'table' => $tableName,
				'old' => $oldCol,
				'new' => $newCol,
				'reason' => 'column_not_found'
			);
			continue;
		}

		if ($oldInfo !== null && $newInfo !== null) {
			$report['errors'][] = array(
				'table' => $tableName,
				'old' => $oldCol,
				'new' => $newCol,
				'error' => 'both_old_and_new_columns_exist'
			);
			$report['ok'] = false;
			continue;
		}

		$def = vpnid_build_col_def($oldInfo);
		if ($def === '') {
			$report['errors'][] = array(
				'table' => $tableName,
				'old' => $oldCol,
				'new' => $newCol,
				'error' => 'failed_to_build_column_definition'
			);
			$report['ok'] = false;
			continue;
		}

		$sql = "ALTER TABLE `" . sql_escape_string($tableName) . "` "
			 . "CHANGE `" . sql_escape_string($oldCol) . "` `" . sql_escape_string($newCol) . "` " . $def;

		$res = sql_query($sql, false);
		if ($res === false) {
			$report['errors'][] = array(
				'table' => $tableName,
				'old' => $oldCol,
				'new' => $newCol,
				'error' => 'alter_failed'
			);
			$report['ok'] = false;
			continue;
		}

		$report['renamed'][] = array(
			'table' => $tableName,
			'old' => $oldCol,
			'new' => $newCol
		);
	}
}

foreach ($targets as $tableName) {
	$itNameInfo = vpnid_col_info($tableName, 'it_name');
	if ($itNameInfo !== null) {
		$report['skipped'][] = array(
			'table' => $tableName,
			'old' => '',
			'new' => 'it_name',
			'reason' => 'it_name_already_exists'
		);
		continue;
	}

	$sql = "ALTER TABLE `" . sql_escape_string($tableName) . "` ADD COLUMN `it_name` varchar(255) NULL AFTER `server_name`";
	$res = sql_query($sql, false);
	if ($res === false) {
		$report['errors'][] = array(
			'table' => $tableName,
			'old' => '',
			'new' => 'it_name',
			'error' => 'add_it_name_failed'
		);
		$report['ok'] = false;
		continue;
	}

	$report['renamed'][] = array(
		'table' => $tableName,
		'old' => '',
		'new' => 'it_name'
	);
}

@file_put_contents($logPath, json_encode($report, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL, FILE_APPEND | LOCK_EX);
echo json_encode($report, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL;
exit($report['ok'] ? 0 : 1);

