Diss/05_Database/PHP/recreate_computeddata_table.php

<?php

require('header_auth.php');
header_auth('Qnb7jfeGZM');

require('http_errors.php');

if (empty($_FILES['csv']) || empty($_POST['classes'])) {
	HttpResponse::bad_request()->set_and_exit();
}

$types = array();

foreach (explode("\n", $_POST['classes']) as $line) {
	$split = explode(' ', $line);
	$name = $split[0];
	$type = $split[1];
	if ($type === 'cell') {
		$type = 'text not null';
	} else if ($type === 'logical') {
		$type = 'boolean not null';
	}
	$types[$name] = $type;
}

$csv_fd = fopen($_FILES['csv']['tmp_name'], 'r');
$column_names = fgetcsv($csv_fd);

$sql_columns = '';
foreach ($column_names as $column_name) {
	if ($column_name === 'setup_id') {
		$sql_columns .= 'setup_id int not null,';
		continue;
	}
	$no_number = preg_replace('/_[0-9]+$/', '', $column_name);
	$sql_columns .= sprintf('%s %s,', $column_name, $types[$no_number]);
}

try {
	$pdo = new PDO('mysql:host=localhost;dbname=observer', 'ziegmann', 'Zieg7+89');

	$pdo->beginTransaction();

	if ($pdo->exec('drop table computeddata;') === false) {
		if ($pdo->errorCode() !== '42S02') { // Table does not exist
			error_log(print_r($pdo->errorInfo(), true));
			$pdo->rollBack();
			HttpResponse::internal_server_error()->set_and_exit();
		}
	}

	if ($pdo->exec(sprintf('create table computeddata ('
		. 'id bigint primary key not null auto_increment,'
		. '%s'
		. 'constraint `fk_computeddata_setup`'
		. 'foreign key (setup_id) references setup (setup_id)'
		. 'on delete cascade on update restrict'
		. ');', $sql_columns)) === false) {
		error_log(print_r($pdo->errorInfo(), true));
		$pdo->rollBack();
		HttpResponse::internal_server_error()->set_and_exit();
	}

	$pdo->commit();
} catch (Exception $e) {
	error_log($e->getMessage());
	if (isset($pdo) && $pdo->inTransaction()) {
		$pdo->rollBack();
	}
	HttpResponse::internal_server_error()->set_and_exit();
}