<?php

declare(strict_types=1);

require_once __DIR__ . '/../db.php';

$pdo = db();

function quoteIdentifier(string $identifier): string {
    if (!preg_match('/^[A-Za-z0-9_]+$/', $identifier)) {
        throw new InvalidArgumentException('Invalid identifier: ' . $identifier);
    }

    return '`' . $identifier . '`';
}

function tableExists(PDO $pdo, string $table): bool {
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?'
    );
    $stmt->execute([$table]);

    return (int) $stmt->fetchColumn() > 0;
}

function indexExists(PDO $pdo, string $table, string $index): bool {
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?'
    );
    $stmt->execute([$table, $index]);

    return (int) $stmt->fetchColumn() > 0;
}

function foreignKeyExists(PDO $pdo, string $constraint): bool {
    $stmt = $pdo->prepare(
        "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
         WHERE CONSTRAINT_SCHEMA = DATABASE()
           AND CONSTRAINT_NAME = ?
           AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
    );
    $stmt->execute([$constraint]);

    return (int) $stmt->fetchColumn() > 0;
}

function addIndexIfMissing(PDO $pdo, string $table, string $index, string $definition): void {
    if (indexExists($pdo, $table, $index)) {
        return;
    }

    $pdo->exec('ALTER TABLE ' . quoteIdentifier($table) . ' ADD ' . $definition);
}

function addForeignKeyIfMissing(PDO $pdo, string $constraint, string $definition): void {
    if (foreignKeyExists($pdo, $constraint)) {
        return;
    }

    $pdo->exec($definition);
}

function assetBlob(string $relativePath): ?string {
    $path = __DIR__ . '/../../raw-resources/' . ltrim($relativePath, '/');

    if (!is_file($path)) {
        return null;
    }

    return file_get_contents($path) ?: null;
}

function upsert(PDO $pdo, string $sql, array $params): void {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
}

try {
    $pdo->exec('SET FOREIGN_KEY_CHECKS = 0');

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `task_states` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `color` varchar(7) COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `task_priorities` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_0900_bin NOT NULL,
  `logo` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `task_types` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `logo` blob,
  `default_state` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `email` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `passwd` varchar(255) COLLATE utf8mb4_0900_bin NOT NULL,
  `picture` mediumblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `projects` (
  `id` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `owner` int NOT NULL,
  `created_date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `versions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
  `created_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `released_date` date DEFAULT NULL,
  `project` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
  `project` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  `created_date` date NOT NULL,
  `last_changed` date NOT NULL,
  `reporter` int NOT NULL,
  `assignee` int DEFAULT NULL,
  `fix_version` int DEFAULT NULL,
  `type` int NOT NULL,
  `priority` int NOT NULL,
  `status` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `assigned_task_states` (
  `id` int NOT NULL AUTO_INCREMENT,
  `state` int NOT NULL,
  `task_type` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `auth_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `token_hash` char(64) COLLATE utf8mb4_0900_bin NOT NULL,
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `response_to` int DEFAULT NULL,
  `task_id` varchar(26) COLLATE utf8mb4_0900_bin NOT NULL,
  `commenter` int NOT NULL,
  `comment` text COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `custom_task_fields` (
  `id` int NOT NULL AUTO_INCREMENT,
  `task_type` int NOT NULL,
  `name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `type` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `default_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `custom_field_values` (
  `id` int NOT NULL AUTO_INCREMENT,
  `field_id` int NOT NULL,
  `task_id` varchar(26) COLLATE utf8mb4_0900_bin NOT NULL,
  `value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `remember_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `selector` char(32) COLLATE utf8mb4_0900_bin NOT NULL,
  `token_hash` char(64) COLLATE utf8mb4_0900_bin NOT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `rights` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `setting_name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `setting_value` varchar(256) COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `task_state_transitions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `from_id` int NOT NULL,
  `to_id` int NOT NULL,
  `action_name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `user_access` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `project_id` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `user_rights` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `right_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $pdo->exec(<<<'SQL'
CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `setting_name` varchar(128) COLLATE utf8mb4_0900_bin NOT NULL,
  `setting_value` varchar(256) COLLATE utf8mb4_0900_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin
SQL);

    $indexes = [
        ['assigned_task_states', 'state', 'KEY `state` (`state`)'],
        ['assigned_task_states', 'task_type', 'KEY `task_type` (`task_type`)'],
        ['auth_tokens', 'unique_token_hash', 'UNIQUE KEY `unique_token_hash` (`token_hash`)'],
        ['auth_tokens', 'user_id', 'KEY `user_id` (`user_id`)'],
        ['comments', 'response_to', 'KEY `response_to` (`response_to`)'],
        ['comments', 'task_id', 'KEY `task_id` (`task_id`)'],
        ['comments', 'commenter', 'KEY `commenter` (`commenter`)'],
        ['custom_field_values', 'field_id', 'KEY `field_id` (`field_id`)'],
        ['custom_field_values', 'task_id', 'KEY `task_id` (`task_id`)'],
        ['custom_task_fields', 'task_type', 'KEY `task_type` (`task_type`)'],
        ['projects', 'owner', 'KEY `owner` (`owner`)'],
        ['remember_tokens', 'unique_selector', 'UNIQUE KEY `unique_selector` (`selector`)'],
        ['remember_tokens', 'user_id', 'KEY `user_id` (`user_id`)'],
        ['tasks', 'project', 'KEY `project` (`project`)'],
        ['tasks', 'reporter', 'KEY `reporter` (`reporter`,`assignee`,`fix_version`,`type`,`priority`)'],
        ['tasks', 'type', 'KEY `type` (`type`)'],
        ['tasks', 'priority', 'KEY `priority` (`priority`)'],
        ['tasks', 'assignee', 'KEY `assignee` (`assignee`)'],
        ['tasks', 'fix_version', 'KEY `fix_version` (`fix_version`)'],
        ['tasks', 'status', 'KEY `status` (`status`)'],
        ['task_state_transitions', 'from_id', 'KEY `from_id` (`from_id`)'],
        ['task_state_transitions', 'to_id', 'KEY `to_id` (`to_id`)'],
        ['task_types', 'default_state', 'KEY `default_state` (`default_state`)'],
        ['users', 'unique_email', 'UNIQUE KEY `unique_email` (`email`)'],
        ['user_access', 'user_id', 'KEY `user_id` (`user_id`)'],
        ['user_access', 'project_id', 'KEY `project_id` (`project_id`)'],
        ['user_rights', 'user_id', 'KEY `user_id` (`user_id`)'],
        ['user_rights', 'right_id', 'KEY `right_id` (`right_id`)'],
        ['user_settings', 'user_id', 'KEY `user_id` (`user_id`)'],
        ['versions', 'project', 'KEY `project` (`project`)'],
    ];

    foreach ($indexes as [$table, $index, $definition]) {
        addIndexIfMissing($pdo, $table, $index, $definition);
    }

    upsert(
        $pdo,
        "INSERT INTO `settings` (`id`, `setting_name`, `setting_value`)
         VALUES (1, 'version', '0.0.1')
         ON DUPLICATE KEY UPDATE `setting_name` = VALUES(`setting_name`), `setting_value` = VALUES(`setting_value`)",
        []
    );

    $states = [
        [1, 'Open', '#858e99'],
        [2, 'In Progress', '#3384e1'],
        [3, 'Resolved', '#1a7f21'],
        [4, 'Reopened', '#8693a2'],
        [5, 'Closed', '#3c810e'],
    ];

    foreach ($states as $state) {
        upsert(
            $pdo,
            "INSERT INTO `task_states` (`id`, `name`, `color`)
             VALUES (?, ?, ?)
             ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `color` = VALUES(`color`)",
            $state
        );
    }

    $priorities = [
        [1, 'Critical', 'images_task_priority/1_critical.svg'],
        [2, 'Heigh', 'images_task_priority/2_heigh.svg'],
        [3, 'Medium', 'images_task_priority/3_medium.svg'],
        [4, 'Low', 'images_task_priority/4_low.svg'],
        [5, 'Trivial', 'images_task_priority/5_trivial.svg'],
    ];

    foreach ($priorities as [$id, $name, $asset]) {
        upsert(
            $pdo,
            "INSERT INTO `task_priorities` (`id`, `name`, `logo`)
             VALUES (?, ?, ?)
             ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `logo` = VALUES(`logo`)",
            [$id, $name, assetBlob($asset)]
        );
    }

    $types = [
        [1, 'Unknown', 'images_task_types/unknown.svg', 1],
        [2, 'Bug', 'images_task_types/bug.svg', 1],
        [3, 'New Feature', 'images_task_types/new_feature.svg', 1],
        [4, 'Improvement', 'images_task_types/improvement.svg', 1],
        [5, 'Task', 'images_task_types/task.svg', 1],
    ];

    foreach ($types as [$id, $name, $asset, $defaultState]) {
        upsert(
            $pdo,
            "INSERT INTO `task_types` (`id`, `name`, `logo`, `default_state`)
             VALUES (?, ?, ?, ?)
             ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `logo` = VALUES(`logo`), `default_state` = VALUES(`default_state`)",
            [$id, $name, assetBlob($asset), $defaultState]
        );
    }

    $transitions = [
        [1, 1, 2, 'Start Work'],
        [2, 2, 3, 'Resolve Issue'],
        [3, 3, 4, 'Reopen Issue'],
        [4, 4, 2, 'Start Work'],
        [5, 1, 5, 'Close'],
        [6, 2, 5, 'Close'],
        [7, 2, 1, 'Stop Work'],
        [8, 5, 4, 'Reopen Issue'],
    ];

    foreach ($transitions as $transition) {
        upsert(
            $pdo,
            "INSERT INTO `task_state_transitions` (`id`, `from_id`, `to_id`, `action_name`)
             VALUES (?, ?, ?, ?)
             ON DUPLICATE KEY UPDATE `from_id` = VALUES(`from_id`), `to_id` = VALUES(`to_id`), `action_name` = VALUES(`action_name`)",
            $transition
        );
    }

    $assignedStates = [
        [1, 1, 5], [2, 2, 5], [3, 3, 5], [4, 5, 5], [5, 4, 5],
        [6, 1, 1],
        [7, 1, 2], [8, 2, 2], [9, 3, 2], [10, 4, 2], [11, 5, 2],
        [12, 1, 3], [13, 2, 3], [14, 3, 3], [15, 4, 3], [16, 5, 3],
        [17, 1, 4], [18, 2, 4], [19, 3, 4], [20, 4, 4], [21, 5, 4],
    ];

    foreach ($assignedStates as $assignedState) {
        upsert(
            $pdo,
            "INSERT INTO `assigned_task_states` (`id`, `state`, `task_type`)
             VALUES (?, ?, ?)
             ON DUPLICATE KEY UPDATE `state` = VALUES(`state`), `task_type` = VALUES(`task_type`)",
            $assignedState
        );
    }

    $rights = [
        [1, 'Admin'],
        [2, 'Create Tasks'],
        [3, 'Edit Tasks'],
        [4, 'Create Versions'],
        [5, 'Edit Versions'],
        [6, 'Create Projects'],
        [7, 'Edit Projects'],
    ];

    foreach ($rights as $right) {
        upsert(
            $pdo,
            "INSERT INTO `rights` (`id`, `name`)
             VALUES (?, ?)
             ON DUPLICATE KEY UPDATE `name` = VALUES(`name`)",
            $right
        );
    }

    $pdo->exec('SET FOREIGN_KEY_CHECKS = 1');

    $foreignKeys = [
        ['assigned_task_states_ibfk_1', 'ALTER TABLE `assigned_task_states` ADD CONSTRAINT `assigned_task_states_ibfk_1` FOREIGN KEY (`task_type`) REFERENCES `task_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['assigned_task_states_ibfk_2', 'ALTER TABLE `assigned_task_states` ADD CONSTRAINT `assigned_task_states_ibfk_2` FOREIGN KEY (`state`) REFERENCES `task_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['auth_tokens_ibfk_1', 'ALTER TABLE `auth_tokens` ADD CONSTRAINT `auth_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE'],
        ['comments_ibfk_1', 'ALTER TABLE `comments` ADD CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`commenter`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['comments_ibfk_2', 'ALTER TABLE `comments` ADD CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['comments_ibfk_3', 'ALTER TABLE `comments` ADD CONSTRAINT `comments_ibfk_3` FOREIGN KEY (`response_to`) REFERENCES `comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['custom_field_values_ibfk_1', 'ALTER TABLE `custom_field_values` ADD CONSTRAINT `custom_field_values_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `custom_task_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['custom_field_values_ibfk_2', 'ALTER TABLE `custom_field_values` ADD CONSTRAINT `custom_field_values_ibfk_2` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['custom_task_fields_ibfk_1', 'ALTER TABLE `custom_task_fields` ADD CONSTRAINT `custom_task_fields_ibfk_1` FOREIGN KEY (`task_type`) REFERENCES `task_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['projects_ibfk_1', 'ALTER TABLE `projects` ADD CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `users` (`id`) ON UPDATE CASCADE'],
        ['remember_tokens_ibfk_1', 'ALTER TABLE `remember_tokens` ADD CONSTRAINT `remember_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE'],
        ['tasks_ibfk_1', 'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`type`) REFERENCES `task_types` (`id`) ON UPDATE CASCADE'],
        ['tasks_ibfk_2', 'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`priority`) REFERENCES `task_priorities` (`id`) ON UPDATE CASCADE'],
        ['tasks_ibfk_3', 'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`reporter`) REFERENCES `users` (`id`) ON UPDATE CASCADE'],
        ['tasks_ibfk_4', 'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_ibfk_4` FOREIGN KEY (`assignee`) REFERENCES `users` (`id`) ON UPDATE CASCADE'],
        ['tasks_ibfk_5', 'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_ibfk_5` FOREIGN KEY (`project`) REFERENCES `projects` (`id`) ON UPDATE CASCADE'],
        ['tasks_ibfk_6', 'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_ibfk_6` FOREIGN KEY (`fix_version`) REFERENCES `versions` (`id`) ON UPDATE CASCADE'],
        ['tasks_ibfk_7', 'ALTER TABLE `tasks` ADD CONSTRAINT `tasks_ibfk_7` FOREIGN KEY (`status`) REFERENCES `task_states` (`id`) ON DELETE SET NULL ON UPDATE CASCADE'],
        ['task_state_transitions_ibfk_1', 'ALTER TABLE `task_state_transitions` ADD CONSTRAINT `task_state_transitions_ibfk_1` FOREIGN KEY (`to_id`) REFERENCES `task_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['task_state_transitions_ibfk_2', 'ALTER TABLE `task_state_transitions` ADD CONSTRAINT `task_state_transitions_ibfk_2` FOREIGN KEY (`from_id`) REFERENCES `task_states` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['task_types_ibfk_1', 'ALTER TABLE `task_types` ADD CONSTRAINT `task_types_ibfk_1` FOREIGN KEY (`default_state`) REFERENCES `task_states` (`id`) ON DELETE SET NULL ON UPDATE CASCADE'],
        ['user_access_ibfk_1', 'ALTER TABLE `user_access` ADD CONSTRAINT `user_access_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['user_access_ibfk_2', 'ALTER TABLE `user_access` ADD CONSTRAINT `user_access_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['user_rights_ibfk_1', 'ALTER TABLE `user_rights` ADD CONSTRAINT `user_rights_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['user_rights_ibfk_2', 'ALTER TABLE `user_rights` ADD CONSTRAINT `user_rights_ibfk_2` FOREIGN KEY (`right_id`) REFERENCES `rights` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['user_settings_ibfk_1', 'ALTER TABLE `user_settings` ADD CONSTRAINT `user_settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE'],
        ['versions_ibfk_1', 'ALTER TABLE `versions` ADD CONSTRAINT `versions_ibfk_1` FOREIGN KEY (`project`) REFERENCES `projects` (`id`) ON UPDATE CASCADE'],
    ];

    foreach ($foreignKeys as [$constraint, $definition]) {
        addForeignKeyIfMissing($pdo, $constraint, $definition);
    }

    if (realpath($_SERVER['SCRIPT_FILENAME'] ?? '') === __FILE__) {
        header('Location: install.php');
        exit;
    }
} catch (Throwable $exception) {
    try {
        $pdo->exec('SET FOREIGN_KEY_CHECKS = 1');
    } catch (Throwable) {
    }

    if (realpath($_SERVER['SCRIPT_FILENAME'] ?? '') !== __FILE__) {
        throw $exception;
    }

    http_response_code(500);
    echo '<!doctype html><meta charset="utf-8"><title>ProjectKiln install failed</title>';
    echo '<h1>Database install failed</h1>';
    echo '<pre>' . htmlspecialchars($exception->getMessage(), ENT_QUOTES, 'UTF-8') . '</pre>';
}