added bootstar, font awsome, and the project is in a useable state, bit needs some manual setup.

ProjectKiln/api/admin_options.php

@@ -0,0 +1,746 @@
+<?php
+
+declare(strict_types=1);
+
+require_once __DIR__ . '/../db.php';
+require_once __DIR__ . '/../auth.php';
+
+header('Content-Type: application/json; charset=utf-8');
+
+function jsonResponse(array $data, int $status = 200): void
+{
+    http_response_code($status);
+    header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
+    header('Pragma: no-cache');
+    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
+    exit;
+}
+
+function getInputData(): array
+{
+    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
+
+    if (str_contains($contentType, 'application/json')) {
+        $raw = file_get_contents('php://input');
+        $data = json_decode($raw, true);
+
+        return is_array($data) ? $data : [];
+    }
+
+    return $_POST;
+}
+
+function optionResponse(array $option): array
+{
+    return [
+        'id' => (int)$option['id'],
+        'name' => $option['name'],
+        'icon' => $option['logo'] !== null
+            ? 'data:image/svg+xml;base64,' . base64_encode($option['logo'])
+            : null
+    ];
+}
+
+function customFieldResponse(array $field): array
+{
+    if ($field['default_value'] === null) {
+        return [
+            'id' => (int)$field['id'],
+            'task_type' => (int)$field['task_type'],
+            'name' => $field['name'],
+            'type' => $field['type'],
+            'value' => null,
+            'raw_value' => ''
+        ];
+    }
+
+    $decodedValue = json_decode((string)$field['default_value'], true);
+    $decodeOk = json_last_error() === JSON_ERROR_NONE;
+    $rawValue = (string)$field['default_value'];
+
+    if ($decodeOk) {
+        if ($field['type'] === 'json') {
+            $rawValue = json_encode($decodedValue, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
+        } elseif ($field['type'] === 'boolean') {
+            $rawValue = $decodedValue ? 'true' : 'false';
+        } else {
+            $rawValue = (string)$decodedValue;
+        }
+    }
+
+    return [
+        'id' => (int)$field['id'],
+        'task_type' => (int)$field['task_type'],
+        'name' => $field['name'],
+        'type' => $field['type'],
+        'value' => $decodeOk ? $decodedValue : null,
+        'raw_value' => $rawValue
+    ];
+}
+
+function pictureDataUri(?string $picture): ?string
+{
+    if ($picture === null || $picture === '') {
+        return null;
+    }
+
+    $info = @getimagesizefromstring($picture);
+    $mime = is_array($info) && isset($info['mime'])
+        ? $info['mime']
+        : 'image/png';
+
+    return 'data:' . $mime . ';base64,' . base64_encode($picture);
+}
+
+function userResponse(array $user): array
+{
+    return [
+        'id' => (int)$user['id'],
+        'name' => $user['name'],
+        'email' => $user['email'],
+        'picture' => pictureDataUri($user['picture'] ?? null)
+    ];
+}
+
+function rightResponse(array $right): array
+{
+    return [
+        'id' => (int)$right['id'],
+        'name' => $right['name']
+    ];
+}
+
+function projectResponse(array $project): array
+{
+    return [
+        'id' => $project['id'],
+        'name' => $project['name'],
+        'owner' => (int)$project['owner']
+    ];
+}
+
+function optionTable(string $kind): string
+{
+    return match ($kind) {
+        'type' => 'task_types',
+        'priority' => 'task_priorities',
+        default => jsonResponse([
+            'success' => false,
+            'error' => 'Option kind must be type or priority.'
+        ], 400)
+    };
+}
+
+function optionLabel(string $kind): string
+{
+    return $kind === 'type' ? 'Task type' : 'Task priority';
+}
+
+function normalizeName(mixed $name, string $label, int $maxLength = 128): string
+{
+    $name = trim((string)$name);
+
+    if ($name === '') {
+        jsonResponse(['success' => false, 'error' => "{$label} name is required."], 400);
+    }
+
+    if (strlen($name) > $maxLength) {
+        jsonResponse(['success' => false, 'error' => "{$label} name is too long."], 400);
+    }
+
+    return $name;
+}
+
+function normalizeCustomFieldType(mixed $type): string
+{
+    $type = strtolower(trim((string)$type));
+    $allowedTypes = ['string', 'int', 'float', 'date', 'boolean', 'text', 'json'];
+
+    if (!in_array($type, $allowedTypes, true)) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Custom field type must be string, int, float, date, boolean, text, or json.'
+        ], 400);
+    }
+
+    return $type;
+}
+
+function normalizeCustomFieldValue(mixed $value, string $type): ?string
+{
+    $rawValue = trim((string)$value);
+
+    if ($rawValue === '') {
+        return null;
+    }
+
+    $normalized = match ($type) {
+        'int' => filter_var($rawValue, FILTER_VALIDATE_INT, FILTER_NULL_ON_FAILURE),
+        'float' => is_numeric($rawValue) ? (float)$rawValue : null,
+        'boolean' => match (strtolower($rawValue)) {
+            '1', 'true', 'yes', 'on' => true,
+            '0', 'false', 'no', 'off' => false,
+            default => null
+        },
+        'date' => preg_match('/^\d{4}-\d{2}-\d{2}$/', $rawValue) ? $rawValue : null,
+        'json' => json_decode($rawValue, true),
+        default => $rawValue
+    };
+
+    if ($type === 'json' && json_last_error() !== JSON_ERROR_NONE) {
+        jsonResponse(['success' => false, 'error' => 'Custom field JSON value is invalid.'], 400);
+    }
+
+    if ($normalized === null && $type !== 'json') {
+        jsonResponse(['success' => false, 'error' => "Custom field value does not match {$type}."], 400);
+    }
+
+    return json_encode($normalized, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
+}
+
+function ensureCustomTaskFieldsTable(): void
+{
+    db()->exec(
+        'CREATE TABLE IF NOT EXISTS custom_task_fields (
+            id INT NOT NULL AUTO_INCREMENT,
+            task_type INT NOT NULL,
+            name VARCHAR(128) NOT NULL,
+            type VARCHAR(128) NOT NULL,
+            default_value TEXT NULL,
+            PRIMARY KEY (id),
+            INDEX idx_custom_task_fields_task_type (task_type)
+        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
+    );
+
+    $columns = db()->query('SHOW COLUMNS FROM custom_task_fields')->fetchAll(PDO::FETCH_COLUMN);
+
+    if (!in_array('default_value', $columns, true)) {
+        db()->exec('ALTER TABLE custom_task_fields ADD COLUMN default_value TEXT NULL');
+    }
+
+    if (in_array('value', $columns, true)) {
+        db()->exec('UPDATE custom_task_fields SET default_value = `value` WHERE default_value IS NULL');
+    }
+}
+
+function ensureCustomFieldValuesTable(): void
+{
+    db()->exec(
+        'CREATE TABLE IF NOT EXISTS custom_field_values (
+            id INT NOT NULL AUTO_INCREMENT,
+            field_id INT NOT NULL,
+            task_id VARCHAR(26) NOT NULL,
+            `value` TEXT NULL,
+            PRIMARY KEY (id),
+            INDEX idx_custom_field_values_task (task_id),
+            INDEX idx_custom_field_values_field (field_id)
+        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
+    );
+}
+
+function uploadedLogo(): ?string
+{
+    if (!isset($_FILES['logo']) || !is_array($_FILES['logo'])) {
+        return null;
+    }
+
+    $file = $_FILES['logo'];
+
+    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
+        return null;
+    }
+
+    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
+        jsonResponse(['success' => false, 'error' => 'Logo upload failed.'], 400);
+    }
+
+    if (($file['size'] ?? 0) > 1024 * 1024) {
+        jsonResponse(['success' => false, 'error' => 'Logo must be 1 MB or smaller.'], 400);
+    }
+
+    $content = file_get_contents($file['tmp_name']);
+
+    if ($content === false || trim($content) === '') {
+        jsonResponse(['success' => false, 'error' => 'Logo file is empty.'], 400);
+    }
+
+    if (!preg_match('/<svg\b/i', $content)) {
+        jsonResponse(['success' => false, 'error' => 'Logo must be an SVG file.'], 400);
+    }
+
+    return $content;
+}
+
+function requireOption(string $table, int $id, string $label): void
+{
+    if ($id <= 0) {
+        jsonResponse(['success' => false, 'error' => "{$label} must be valid."], 400);
+    }
+
+    $stmt = db()->prepare("SELECT id FROM {$table} WHERE id = ? LIMIT 1");
+    $stmt->execute([$id]);
+
+    if (!$stmt->fetch()) {
+        jsonResponse(['success' => false, 'error' => "{$label} not found."], 404);
+    }
+}
+
+function preventDeleteIfUsed(string $kind, int $id): void
+{
+    if ($kind === 'type') {
+        $taskStmt = db()->prepare('SELECT COUNT(*) FROM tasks WHERE type = ?');
+        $taskStmt->execute([$id]);
+        $assignmentStmt = db()->prepare('SELECT COUNT(*) FROM assigned_task_states WHERE task_type = ?');
+        $assignmentStmt->execute([$id]);
+
+        if ((int)$taskStmt->fetchColumn() > 0 || (int)$assignmentStmt->fetchColumn() > 0) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Task type is still used by tasks or workflow assignments.'
+            ], 409);
+        }
+
+        return;
+    }
+
+    $stmt = db()->prepare('SELECT COUNT(*) FROM tasks WHERE priority = ?');
+    $stmt->execute([$id]);
+
+    if ((int)$stmt->fetchColumn() > 0) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Task priority is still used by tasks.'
+        ], 409);
+    }
+}
+
+function requireCustomField(int $fieldId): array
+{
+    if ($fieldId <= 0) {
+        jsonResponse(['success' => false, 'error' => 'Custom field must be valid.'], 400);
+    }
+
+    ensureCustomTaskFieldsTable();
+
+    $stmt = db()->prepare(
+        'SELECT id, task_type, name, type, default_value
+         FROM custom_task_fields
+         WHERE id = ?
+         LIMIT 1'
+    );
+    $stmt->execute([$fieldId]);
+    $field = $stmt->fetch();
+
+    if (!$field) {
+        jsonResponse(['success' => false, 'error' => 'Custom field not found.'], 404);
+    }
+
+    return $field;
+}
+
+function requireUserId(int $userId): void
+{
+    if ($userId <= 0) {
+        jsonResponse(['success' => false, 'error' => 'User must be valid.'], 400);
+    }
+
+    $stmt = db()->prepare('SELECT id FROM users WHERE id = ? LIMIT 1');
+    $stmt->execute([$userId]);
+
+    if (!$stmt->fetch()) {
+        jsonResponse(['success' => false, 'error' => 'User not found.'], 404);
+    }
+}
+
+function requireRightId(int $rightId): void
+{
+    if ($rightId <= 0) {
+        jsonResponse(['success' => false, 'error' => 'Right must be valid.'], 400);
+    }
+
+    $stmt = db()->prepare('SELECT id FROM rights WHERE id = ? LIMIT 1');
+    $stmt->execute([$rightId]);
+
+    if (!$stmt->fetch()) {
+        jsonResponse(['success' => false, 'error' => 'Right not found.'], 404);
+    }
+}
+
+function requireProjectId(string $projectId): void
+{
+    if ($projectId === '') {
+        jsonResponse(['success' => false, 'error' => 'Project must be valid.'], 400);
+    }
+
+    $stmt = db()->prepare('SELECT id FROM projects WHERE id = ? LIMIT 1');
+    $stmt->execute([$projectId]);
+
+    if (!$stmt->fetch()) {
+        jsonResponse(['success' => false, 'error' => 'Project not found.'], 404);
+    }
+}
+
+function rightName(int $rightId): ?string
+{
+    $stmt = db()->prepare('SELECT name FROM rights WHERE id = ? LIMIT 1');
+    $stmt->execute([$rightId]);
+    $name = $stmt->fetchColumn();
+
+    return $name !== false ? (string)$name : null;
+}
+
+function optionsData(): array
+{
+    $types = array_map('optionResponse', db()->query(
+        'SELECT id, name, logo
+         FROM task_types
+         ORDER BY id ASC'
+    )->fetchAll());
+
+    $priorities = array_map('optionResponse', db()->query(
+        'SELECT id, name, logo
+         FROM task_priorities
+         ORDER BY id ASC'
+    )->fetchAll());
+
+    try {
+        ensureCustomTaskFieldsTable();
+
+        $customFields = array_map('customFieldResponse', db()->query(
+            'SELECT id, task_type, name, type, default_value
+             FROM custom_task_fields
+             ORDER BY task_type ASC, id ASC'
+        )->fetchAll());
+    } catch (Throwable $e) {
+        $customFields = [];
+    }
+
+    $users = array_map('userResponse', db()->query(
+        'SELECT id, name, email, picture
+         FROM users
+         ORDER BY name ASC, id ASC'
+    )->fetchAll());
+
+    $rights = array_map('rightResponse', db()->query(
+        'SELECT id, name
+         FROM rights
+         ORDER BY id ASC'
+    )->fetchAll());
+
+    $userRights = db()->query(
+        'SELECT user_id, right_id
+         FROM user_rights
+         ORDER BY user_id ASC, right_id ASC'
+    )->fetchAll();
+
+    $projects = array_map('projectResponse', db()->query(
+        'SELECT id, name, owner
+         FROM projects
+         ORDER BY name ASC, id ASC'
+    )->fetchAll());
+
+    $userAccess = db()->query(
+        'SELECT id, user_id, project_id
+         FROM user_access
+         ORDER BY user_id ASC, project_id ASC'
+    )->fetchAll();
+
+    return [
+        'types' => $types,
+        'priorities' => $priorities,
+        'custom_fields' => $customFields,
+        'users' => $users,
+        'rights' => $rights,
+        'user_rights' => array_map(static fn (array $row): array => [
+            'user_id' => (int)$row['user_id'],
+            'right_id' => (int)$row['right_id']
+        ], $userRights),
+        'projects' => $projects,
+        'user_access' => array_map(static fn (array $row): array => [
+            'id' => (int)$row['id'],
+            'user_id' => (int)$row['user_id'],
+            'project_id' => $row['project_id']
+        ], $userAccess)
+    ];
+}
+
+$user = requireApiAuth();
+requireUserRight((int)$user['id'], 'Admin');
+
+$api = $_GET['api'] ?? '';
+
+switch ($api) {
+    case 'OptionsData': {
+        jsonResponse([
+            'success' => true,
+            'options' => optionsData()
+        ]);
+    }
+
+    case 'CreateOption': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'CreateOption requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $kind = (string)($data['kind'] ?? '');
+        $table = optionTable($kind);
+        $label = optionLabel($kind);
+        $name = normalizeName($data['name'] ?? '', $label, $kind === 'type' ? 20 : 128);
+        $logo = uploadedLogo();
+
+        $stmt = db()->prepare("INSERT INTO {$table} (name, logo) VALUES (?, ?)");
+        $stmt->execute([$name, $logo]);
+
+        jsonResponse([
+            'success' => true,
+            'option_id' => (int)db()->lastInsertId()
+        ], 201);
+    }
+
+    case 'UpdateOption': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'UpdateOption requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $kind = (string)($data['kind'] ?? '');
+        $table = optionTable($kind);
+        $label = optionLabel($kind);
+        $id = (int)($data['id'] ?? 0);
+        $name = normalizeName($data['name'] ?? '', $label, $kind === 'type' ? 20 : 128);
+        $logo = uploadedLogo();
+        $removeLogo = !empty($data['remove_logo']);
+
+        requireOption($table, $id, $label);
+
+        if ($logo !== null) {
+            $stmt = db()->prepare("UPDATE {$table} SET name = ?, logo = ? WHERE id = ?");
+            $stmt->execute([$name, $logo, $id]);
+        } elseif ($removeLogo) {
+            $stmt = db()->prepare("UPDATE {$table} SET name = ?, logo = NULL WHERE id = ?");
+            $stmt->execute([$name, $id]);
+        } else {
+            $stmt = db()->prepare("UPDATE {$table} SET name = ? WHERE id = ?");
+            $stmt->execute([$name, $id]);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'option_id' => $id
+        ]);
+    }
+
+    case 'DeleteOption': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'DeleteOption requires POST.'], 405);
+        }
+
+        $kind = (string)($_GET['kind'] ?? '');
+        $table = optionTable($kind);
+        $label = optionLabel($kind);
+        $id = (int)($_GET['id'] ?? 0);
+
+        requireOption($table, $id, $label);
+        preventDeleteIfUsed($kind, $id);
+
+        if ($kind === 'type') {
+            ensureCustomTaskFieldsTable();
+            ensureCustomFieldValuesTable();
+            db()->prepare(
+                'DELETE custom_field_values
+                 FROM custom_field_values
+                 INNER JOIN custom_task_fields ON custom_task_fields.id = custom_field_values.field_id
+                 WHERE custom_task_fields.task_type = ?'
+            )->execute([$id]);
+            db()->prepare('DELETE FROM custom_task_fields WHERE task_type = ?')->execute([$id]);
+        }
+
+        $stmt = db()->prepare("DELETE FROM {$table} WHERE id = ?");
+        $stmt->execute([$id]);
+
+        jsonResponse([
+            'success' => true,
+            'option_id' => $id
+        ]);
+    }
+
+    case 'CreateCustomField': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'CreateCustomField requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $taskType = (int)($data['task_type'] ?? 0);
+        $name = normalizeName($data['name'] ?? '', 'Custom field');
+        $type = normalizeCustomFieldType($data['type'] ?? '');
+        $value = normalizeCustomFieldValue($data['value'] ?? '', $type);
+
+        requireOption('task_types', $taskType, 'Task type');
+        ensureCustomTaskFieldsTable();
+
+        $stmt = db()->prepare(
+            'INSERT INTO custom_task_fields (task_type, name, type, default_value)
+             VALUES (?, ?, ?, ?)'
+        );
+        $stmt->execute([$taskType, $name, $type, $value]);
+
+        jsonResponse([
+            'success' => true,
+            'field_id' => (int)db()->lastInsertId()
+        ], 201);
+    }
+
+    case 'UpdateCustomField': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'UpdateCustomField requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $fieldId = (int)($data['id'] ?? 0);
+        $field = requireCustomField($fieldId);
+        $name = normalizeName($data['name'] ?? '', 'Custom field');
+        $type = normalizeCustomFieldType($data['type'] ?? '');
+        $value = normalizeCustomFieldValue($data['value'] ?? '', $type);
+        ensureCustomTaskFieldsTable();
+
+        $stmt = db()->prepare(
+            'UPDATE custom_task_fields
+             SET name = ?, type = ?, default_value = ?
+             WHERE id = ?'
+        );
+        $stmt->execute([$name, $type, $value, $field['id']]);
+
+        jsonResponse([
+            'success' => true,
+            'field_id' => (int)$field['id']
+        ]);
+    }
+
+    case 'DeleteCustomField': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'DeleteCustomField requires POST.'], 405);
+        }
+
+        $fieldId = (int)($_GET['field_id'] ?? 0);
+        $field = requireCustomField($fieldId);
+        ensureCustomTaskFieldsTable();
+
+        $stmt = db()->prepare('DELETE FROM custom_task_fields WHERE id = ?');
+        $stmt->execute([$field['id']]);
+
+        jsonResponse([
+            'success' => true,
+            'field_id' => (int)$field['id']
+        ]);
+    }
+
+    case 'SetUserRight': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'SetUserRight requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $userId = (int)($data['user_id'] ?? 0);
+        $rightId = (int)($data['right_id'] ?? 0);
+        $enabled = !empty($data['enabled']);
+
+        requireUserId($userId);
+        requireRightId($rightId);
+
+        if (!$enabled && $userId === (int)$user['id'] && rightName($rightId) === 'Admin') {
+            jsonResponse(['success' => false, 'error' => 'You cannot remove your own Admin right.'], 400);
+        }
+
+        if ($enabled) {
+            $stmt = db()->prepare(
+                'SELECT id
+                 FROM user_rights
+                 WHERE user_id = ?
+                   AND right_id = ?
+                 LIMIT 1'
+            );
+            $stmt->execute([$userId, $rightId]);
+
+            if (!$stmt->fetch()) {
+                db()->prepare(
+                    'INSERT INTO user_rights (user_id, right_id)
+                     VALUES (?, ?)'
+                )->execute([$userId, $rightId]);
+            }
+        } else {
+            db()->prepare(
+                'DELETE FROM user_rights
+                 WHERE user_id = ?
+                   AND right_id = ?'
+            )->execute([$userId, $rightId]);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'user_id' => $userId,
+            'right_id' => $rightId,
+            'enabled' => $enabled
+        ]);
+    }
+
+    case 'GrantProjectAccess': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'GrantProjectAccess requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $userId = (int)($data['user_id'] ?? 0);
+        $projectId = strtoupper(trim((string)($data['project_id'] ?? '')));
+
+        requireUserId($userId);
+        requireProjectId($projectId);
+
+        $stmt = db()->prepare(
+            'SELECT id
+             FROM user_access
+             WHERE user_id = ?
+               AND project_id = ?
+             LIMIT 1'
+        );
+        $stmt->execute([$userId, $projectId]);
+
+        if (!$stmt->fetch()) {
+            db()->prepare(
+                'INSERT INTO user_access (user_id, project_id)
+                 VALUES (?, ?)'
+            )->execute([$userId, $projectId]);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'user_id' => $userId,
+            'project_id' => $projectId
+        ], 201);
+    }
+
+    case 'RevokeProjectAccess': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'RevokeProjectAccess requires POST.'], 405);
+        }
+
+        $accessId = (int)($_GET['access_id'] ?? 0);
+
+        if ($accessId <= 0) {
+            jsonResponse(['success' => false, 'error' => 'Project access must be valid.'], 400);
+        }
+
+        db()->prepare('DELETE FROM user_access WHERE id = ?')->execute([$accessId]);
+
+        jsonResponse([
+            'success' => true,
+            'access_id' => $accessId
+        ]);
+    }
+
+    default:
+        jsonResponse([
+            'success' => false,
+            'error' => 'Unknown admin options API action.'
+        ], 400);
+}

ProjectKiln/api/auth.php

@@ -0,0 +1,160 @@
+<?php
+
+declare(strict_types=1);
+
+require_once __DIR__ . '/../db.php';
+require_once __DIR__ . '/../auth.php';
+
+header('Content-Type: application/json; charset=utf-8');
+
+function jsonResponse(array $data, int $status = 200): void
+{
+    http_response_code($status);
+    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
+    exit;
+}
+
+function getInputData(): array
+{
+    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
+
+    if (str_contains($contentType, 'application/json')) {
+        $raw = file_get_contents('php://input');
+        $data = json_decode($raw, true);
+
+        return is_array($data) ? $data : [];
+    }
+
+    return $_POST;
+}
+
+function getBearerTokenFromHeader(): ?string
+{
+    $header = $_SERVER['HTTP_AUTHORIZATION'] ?? '';
+
+    if (preg_match('/Bearer\s+(.+)/', $header, $matches)) {
+        return trim($matches[1]);
+    }
+
+    return null;
+}
+
+$api = $_GET['api'] ?? '';
+
+switch ($api) {
+
+    case 'Login': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Login requires POST.'
+            ], 405);
+        }
+
+        $data = getInputData();
+
+        $email = trim($data['email'] ?? '');
+        $password = (string)($data['password'] ?? '');
+
+        if ($email === '' || $password === '') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Email and password are required.'
+            ], 400);
+        }
+
+        $token = login($email, $password, false);
+
+        if ($token === null) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Invalid email or password.'
+            ], 401);
+        }
+
+        $user = currentUser();
+
+        jsonResponse([
+            'success' => true,
+            'token_type' => 'Bearer',
+            'access_token' => $token,
+            'expires_in' => 43200,
+            'user' => $user
+        ]);
+    }
+
+    case 'CreateToken': {
+        $user = currentUser();
+
+        if (!$user) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Not logged in.'
+            ], 401);
+        }
+
+        $token = createAuthToken((int)$user['id']);
+
+        jsonResponse([
+            'success' => true,
+            'token_type' => 'Bearer',
+            'access_token' => $token,
+            'expires_in' => 43200,
+            'user' => $user
+        ]);
+    }
+
+    case 'Me': {
+        $token = getBearerTokenFromHeader();
+
+        if ($token) {
+            $user = authenticateApiToken($token);
+        } else {
+            $user = currentUser();
+        }
+
+        if (!$user) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Not authenticated.'
+            ], 401);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'user' => $user
+        ]);
+    }
+
+    case 'Logout': {
+        $token = getBearerTokenFromHeader();
+
+        if ($token) {
+            $tokenHash = hash('sha256', $token);
+
+            db()->prepare(
+                'UPDATE auth_tokens
+                 SET revoked_at = NOW()
+                 WHERE token_hash = ?
+                   AND revoked_at IS NULL'
+            )->execute([$tokenHash]);
+
+            jsonResponse([
+                'success' => true
+            ]);
+        }
+
+        logout();
+
+        jsonResponse([
+            'success' => true
+        ]);
+    }
+
+    default: {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Unknown API action.'
+        ], 404);
+    }
+}

ProjectKiln/api/project.php

@@ -0,0 +1,289 @@
+<?php
+
+declare(strict_types=1);
+
+require_once __DIR__ . '/../db.php';
+require_once __DIR__ . '/../auth.php';
+
+header('Content-Type: application/json; charset=utf-8');
+
+function jsonResponse(array $data, int $status = 200): void
+{
+    http_response_code($status);
+    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
+    exit;
+}
+
+function getInputData(): array
+{
+    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
+
+    if (str_contains($contentType, 'application/json')) {
+        $raw = file_get_contents('php://input');
+        $data = json_decode($raw, true);
+
+        return is_array($data) ? $data : [];
+    }
+
+    return $_POST;
+}
+
+function requireUserExists(int $userId): void
+{
+    if ($userId <= 0) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Project owner must be valid.'
+        ], 400);
+    }
+
+    $stmt = db()->prepare('SELECT id FROM users WHERE id = ? LIMIT 1');
+    $stmt->execute([$userId]);
+
+    if (!$stmt->fetch()) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Project owner not found.'
+        ], 400);
+    }
+}
+
+$user = requireApiAuth();
+
+$api = $_GET['api'] ?? '';
+
+switch ($api) {
+
+    case 'ListProjects': {
+        if (userIsAdmin((int)$user['id'])) {
+            $stmt = db()->query(
+                'SELECT id
+                 FROM projects
+                 ORDER BY id ASC'
+            );
+        } else {
+            $stmt = db()->prepare(
+                'SELECT DISTINCT projects.id
+                 FROM projects
+                 LEFT JOIN user_access
+                    ON user_access.project_id = projects.id
+                   AND user_access.user_id = ?
+                 WHERE projects.owner = ?
+                    OR user_access.id IS NOT NULL
+                 ORDER BY projects.id ASC'
+            );
+            $stmt->execute([(int)$user['id'], (int)$user['id']]);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'projects' => $stmt->fetchAll(PDO::FETCH_COLUMN)
+        ]);
+    }
+
+    case 'ProjectInfo': {
+        $projectId = strtoupper(trim($_GET['project_id'] ?? ''));
+
+        if ($projectId === '') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Missing project_id.'
+            ], 400);
+        }
+
+        requireProjectAccessForUser((int)$user['id'], $projectId);
+
+        $stmt = db()->prepare(
+            'SELECT id, name, owner, created_date
+             FROM projects
+             WHERE id = ?
+             LIMIT 1'
+        );
+
+        $stmt->execute([$projectId]);
+        $project = $stmt->fetch();
+
+        if (!$project) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Project not found.'
+            ], 404);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'project' => $project
+        ]);
+    }
+
+    case 'Create': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Create requires POST.'
+            ], 405);
+        }
+
+        requireUserRight((int)$user['id'], 'Create Projects');
+
+        $data = getInputData();
+
+        $projectId = strtoupper(trim($data['id'] ?? ''));
+        $name = trim($data['name'] ?? '');
+        $owner = isset($data['owner']) && $data['owner'] !== ''
+            ? (int)$data['owner']
+            : (int)$user['id'];
+
+        if (!preg_match('/^[A-Z0-9]{2,12}$/', $projectId)) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Project id must be 2-12 characters and contain only A-Z and 0-9.'
+            ], 400);
+        }
+
+        if ($name === '') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Project name is required.'
+            ], 400);
+        }
+
+        if (strlen($name) > 128) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Project name is too long.'
+            ], 400);
+        }
+
+        requireUserExists($owner);
+
+        $stmt = db()->prepare(
+            'SELECT id FROM projects WHERE id = ? LIMIT 1'
+        );
+
+        $stmt->execute([$projectId]);
+
+        if ($stmt->fetch()) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Project id already exists.'
+            ], 409);
+        }
+
+        $stmt = db()->prepare(
+            'INSERT INTO projects (id, name, owner, created_date)
+             VALUES (?, ?, ?, CURDATE())'
+        );
+
+        $stmt->execute([
+            $projectId,
+            $name,
+            $owner
+        ]);
+
+        jsonResponse([
+            'success' => true,
+            'project_id' => $projectId
+        ], 201);
+    }
+
+    case 'Edit': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Edit requires POST.'
+            ], 405);
+        }
+
+        $projectId = strtoupper(trim($_GET['project_id'] ?? ''));
+
+        if ($projectId === '') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Missing project_id.'
+            ], 400);
+        }
+
+        requireProjectRight((int)$user['id'], $projectId, 'Edit Projects');
+
+        $data = getInputData();
+
+        $fields = [];
+        $values = [];
+
+        if (array_key_exists('name', $data)) {
+            $name = trim((string)$data['name']);
+
+            if ($name === '') {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Project name cannot be empty.'
+                ], 400);
+            }
+
+            if (strlen($name) > 128) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Project name is too long.'
+                ], 400);
+            }
+
+            $fields[] = 'name = ?';
+            $values[] = $name;
+        }
+
+        if (array_key_exists('owner', $data)) {
+            $owner = (int)$data['owner'];
+            requireUserExists($owner);
+
+            $fields[] = 'owner = ?';
+            $values[] = $owner;
+        }
+
+        if (empty($fields)) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'No editable fields provided.'
+            ], 400);
+        }
+
+        $values[] = $projectId;
+
+        $stmt = db()->prepare(
+            'UPDATE projects
+             SET ' . implode(', ', $fields) . '
+             WHERE id = ?'
+        );
+
+        $stmt->execute($values);
+
+        if ($stmt->rowCount() === 0) {
+            $existsStmt = db()->prepare('SELECT id FROM projects WHERE id = ? LIMIT 1');
+            $existsStmt->execute([$projectId]);
+
+            if ($existsStmt->fetch()) {
+                jsonResponse([
+                    'success' => true,
+                    'project_id' => $projectId
+                ]);
+            }
+
+            jsonResponse([
+                'success' => false,
+                'error' => 'Project not found or nothing changed.'
+            ], 404);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'project_id' => $projectId
+        ]);
+    }
+
+    default: {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Unknown API action.'
+        ], 404);
+    }
+}

ProjectKiln/api/user.php

@@ -0,0 +1,382 @@
+<?php
+
+declare(strict_types=1);
+
+require_once __DIR__ . '/../db.php';
+require_once __DIR__ . '/../auth.php';
+
+header('Content-Type: application/json; charset=utf-8');
+
+const VALID_THEMES = ['white', 'dark', 'purple', 'green', 'beige'];
+
+function jsonResponse(array $data, int $status = 200): void
+{
+    http_response_code($status);
+    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
+    exit;
+}
+
+function getInputData(): array
+{
+    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
+
+    if (str_contains($contentType, 'application/json')) {
+        $raw = file_get_contents('php://input');
+        $data = json_decode($raw, true);
+
+        return is_array($data) ? $data : [];
+    }
+
+    return $_POST;
+}
+
+function pictureDataUri(?string $picture): ?string
+{
+    if ($picture === null || $picture === '') {
+        return null;
+    }
+
+    $info = @getimagesizefromstring($picture);
+    $mime = is_array($info) && isset($info['mime']) ? $info['mime'] : 'image/png';
+
+    return 'data:' . $mime . ';base64,' . base64_encode($picture);
+}
+
+function uploadedPicture(): ?string
+{
+    if (empty($_FILES['picture']) || !is_array($_FILES['picture'])) {
+        return null;
+    }
+
+    $file = $_FILES['picture'];
+
+    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
+        return null;
+    }
+
+    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Could not upload profile picture.'
+        ], 400);
+    }
+
+    if (($file['size'] ?? 0) > 10 * 1024 * 1024) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Profile picture must be 10 MB or smaller.'
+        ], 400);
+    }
+
+    $tmpName = (string)($file['tmp_name'] ?? '');
+    $picture = $tmpName !== '' ? file_get_contents($tmpName) : false;
+
+    if ($picture === false) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Could not read profile picture.'
+        ], 400);
+    }
+
+    $info = @getimagesizefromstring($picture);
+    $allowedTypes = [
+        IMAGETYPE_PNG,
+        IMAGETYPE_JPEG,
+        IMAGETYPE_WEBP,
+        IMAGETYPE_GIF
+    ];
+
+    if (!is_array($info) || !in_array($info[2] ?? null, $allowedTypes, true)) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Profile picture must be a PNG, JPG, WEBP, or GIF image.'
+        ], 400);
+    }
+
+    return $picture;
+}
+
+function getUserSetting(int $userId, string $settingName): ?string
+{
+    $stmt = db()->prepare(
+        'SELECT setting_value
+         FROM user_settings
+         WHERE user_id = ?
+           AND setting_name = ?
+         LIMIT 1'
+    );
+
+    $stmt->execute([$userId, $settingName]);
+    $value = $stmt->fetchColumn();
+
+    return $value !== false ? (string)$value : null;
+}
+
+function setUserSetting(int $userId, string $settingName, string $settingValue): void
+{
+    $stmt = db()->prepare(
+        'SELECT id
+         FROM user_settings
+         WHERE user_id = ?
+           AND setting_name = ?
+         LIMIT 1'
+    );
+
+    $stmt->execute([$userId, $settingName]);
+    $settingId = $stmt->fetchColumn();
+
+    if ($settingId) {
+        $updateStmt = db()->prepare(
+            'UPDATE user_settings
+             SET setting_value = ?
+             WHERE id = ?'
+        );
+
+        $updateStmt->execute([$settingValue, $settingId]);
+        return;
+    }
+
+    $insertStmt = db()->prepare(
+        'INSERT INTO user_settings (user_id, setting_name, setting_value)
+         VALUES (?, ?, ?)'
+    );
+
+    $insertStmt->execute([$userId, $settingName, $settingValue]);
+}
+
+$user = requireApiAuth();
+
+$api = $_GET['api'] ?? '';
+
+switch ($api) {
+
+    case 'ListUsers': {
+        $stmt = db()->query(
+            'SELECT id, name, email, picture
+             FROM users
+             ORDER BY name ASC, email ASC'
+        );
+
+        $users = array_map(static function (array $targetUser): array {
+            $targetUser['picture'] = pictureDataUri($targetUser['picture']);
+
+            $targetUser['id'] = (int)$targetUser['id'];
+
+            return $targetUser;
+        }, $stmt->fetchAll());
+
+        jsonResponse([
+            'success' => true,
+            'users' => $users
+        ]);
+    }
+
+    case 'UserInfo': {
+        $userId = (int)($_GET['user_id'] ?? 0);
+
+        if ($userId <= 0) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Missing or invalid user_id.'
+            ], 400);
+        }
+
+        $stmt = db()->prepare(
+            'SELECT id, name, email, picture
+             FROM users
+             WHERE id = ?
+             LIMIT 1'
+        );
+
+        $stmt->execute([$userId]);
+        $targetUser = $stmt->fetch();
+
+        if (!$targetUser) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'User not found.'
+            ], 404);
+        }
+
+        $targetUser['picture'] = pictureDataUri($targetUser['picture']);
+        $targetUser['settings'] = [
+            'theme' => getUserSetting((int)$targetUser['id'], 'theme') ?? 'dark'
+        ];
+
+        jsonResponse([
+            'success' => true,
+            'user' => $targetUser
+        ]);
+    }
+
+    case 'Edit': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Edit requires POST.'
+            ], 405);
+        }
+
+        $userId = (int)($_GET['user_id'] ?? 0);
+
+        if ($userId <= 0) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Missing or invalid user_id.'
+            ], 400);
+        }
+
+        if ($userId !== (int)$user['id']) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'You can only edit your own user.'
+            ], 403);
+        }
+
+        $data = getInputData();
+
+        $fields = [];
+        $values = [];
+
+        if (array_key_exists('name', $data)) {
+            $name = trim((string)$data['name']);
+
+            if ($name === '') {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Name cannot be empty.'
+                ], 400);
+            }
+
+            if (strlen($name) > 128) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Name is too long.'
+                ], 400);
+            }
+
+            $fields[] = 'name = ?';
+            $values[] = $name;
+        }
+
+        if (array_key_exists('email', $data)) {
+            $email = strtolower(trim((string)$data['email']));
+
+            if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Invalid email.'
+                ], 400);
+            }
+
+            if (strlen($email) > 128) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Email is too long.'
+                ], 400);
+            }
+
+            $emailStmt = db()->prepare(
+                'SELECT id
+                 FROM users
+                 WHERE email = ?
+                   AND id <> ?
+                 LIMIT 1'
+            );
+
+            $emailStmt->execute([$email, $userId]);
+
+            if ($emailStmt->fetch()) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Email is already in use.'
+                ], 400);
+            }
+
+            $fields[] = 'email = ?';
+            $values[] = $email;
+        }
+
+        if (array_key_exists('password', $data)) {
+            $password = (string)$data['password'];
+
+            if (strlen($password) < 8) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Password must be at least 8 characters.'
+                ], 400);
+            }
+
+            $fields[] = 'passwd = ?';
+            $values[] = password_hash($password, PASSWORD_ARGON2ID);
+        }
+
+        $removePicture = ($data['remove_picture'] ?? '') === '1';
+        $picture = $removePicture ? null : uploadedPicture();
+
+        if ($picture !== null) {
+            $fields[] = 'picture = ?';
+            $values[] = $picture;
+        }
+
+        if ($removePicture) {
+            $fields[] = 'picture = NULL';
+        }
+
+        $theme = null;
+
+        if (array_key_exists('theme', $data)) {
+            $theme = strtolower(trim((string)$data['theme']));
+
+            if (!in_array($theme, VALID_THEMES, true)) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Theme must be valid.'
+                ], 400);
+            }
+        }
+
+        if (empty($fields) && $theme === null) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'No editable fields provided.'
+            ], 400);
+        }
+
+        try {
+            if (!empty($fields)) {
+                $values[] = $userId;
+
+                $stmt = db()->prepare(
+                    'UPDATE users
+                     SET ' . implode(', ', $fields) . '
+                     WHERE id = ?'
+                );
+
+                $stmt->execute($values);
+            }
+
+            if ($theme !== null) {
+                setUserSetting($userId, 'theme', $theme);
+            }
+
+            jsonResponse([
+                'success' => true,
+                'user_id' => $userId
+            ]);
+
+        } catch (PDOException $e) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Database error.'
+            ], 500);
+        }
+    }
+
+    default: {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Unknown API action.'
+        ], 404);
+    }
+}

ProjectKiln/api/version.php

@@ -0,0 +1,321 @@
+<?php
+
+declare(strict_types=1);
+
+require_once __DIR__ . '/../db.php';
+require_once __DIR__ . '/../auth.php';
+
+header('Content-Type: application/json; charset=utf-8');
+
+function jsonResponse(array $data, int $status = 200): void
+{
+    http_response_code($status);
+    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
+    exit;
+}
+
+function getInputData(): array
+{
+    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
+
+    if (str_contains($contentType, 'application/json')) {
+        $raw = file_get_contents('php://input');
+        $data = json_decode($raw, true);
+
+        return is_array($data) ? $data : [];
+    }
+
+    return $_POST;
+}
+
+function normalizeDateValue(mixed $value, string $fieldName): ?string
+{
+    $value = trim((string)$value);
+
+    if ($value === '') {
+        return null;
+    }
+
+    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)) {
+        jsonResponse([
+            'success' => false,
+            'error' => "Invalid {$fieldName}. Use YYYY-MM-DD."
+        ], 400);
+    }
+
+    return $value;
+}
+
+function requireProjectAccess(string $projectId, int $userId): void
+{
+    requireProjectAccessForUser($userId, $projectId);
+}
+
+$user = requireApiAuth();
+
+$api = $_GET['api'] ?? '';
+
+switch ($api) {
+
+    case 'ListVersions': {
+        $projectId = strtoupper(trim($_GET['project_id'] ?? ''));
+
+        if ($projectId === '') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Missing project_id.'
+            ], 400);
+        }
+
+        requireProjectAccess($projectId, (int)$user['id']);
+
+        $stmt = db()->prepare(
+            'SELECT id
+             FROM versions
+             WHERE project = ?
+             ORDER BY created_date DESC, id DESC'
+        );
+
+        $stmt->execute([$projectId]);
+
+        jsonResponse([
+            'success' => true,
+            'versions' => array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN))
+        ]);
+    }
+
+    case 'VersionInfo': {
+        $versionId = (int)($_GET['version_id'] ?? 0);
+
+        if ($versionId <= 0) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Missing or invalid version_id.'
+            ], 400);
+        }
+
+        $stmt = db()->prepare(
+            'SELECT
+                versions.id,
+                versions.name,
+                versions.description,
+                versions.created_date,
+                versions.due_date,
+                versions.released_date,
+                versions.project
+             FROM versions
+             INNER JOIN projects ON projects.id = versions.project
+             WHERE versions.id = ?
+             LIMIT 1'
+        );
+
+        $stmt->execute([$versionId]);
+
+        $version = $stmt->fetch();
+
+        if (!$version) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Version not found.'
+            ], 404);
+        }
+
+        requireProjectAccessForUser((int)$user['id'], (string)$version['project']);
+
+        jsonResponse([
+            'success' => true,
+            'version' => $version
+        ]);
+    }
+
+    case 'Create': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Create requires POST.'
+            ], 405);
+        }
+
+        $data = getInputData();
+
+        $projectId = strtoupper(trim($data['project_id'] ?? $data['project'] ?? ''));
+        $name = trim($data['name'] ?? '');
+        $description = array_key_exists('description', $data) ? trim((string)$data['description']) : null;
+        $dueDate = array_key_exists('due_date', $data) ? normalizeDateValue($data['due_date'], 'due_date') : null;
+        $releasedDate = array_key_exists('released_date', $data) ? normalizeDateValue($data['released_date'], 'released_date') : null;
+
+        if ($projectId === '') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Project id is required.'
+            ], 400);
+        }
+
+        requireProjectRight((int)$user['id'], $projectId, 'Create Versions');
+
+        if ($name === '') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Version name is required.'
+            ], 400);
+        }
+
+        if (strlen($name) > 128) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Version name is too long.'
+            ], 400);
+        }
+
+        $stmt = db()->prepare(
+            'INSERT INTO versions
+             (name, description, created_date, due_date, released_date, project)
+             VALUES (?, ?, CURDATE(), ?, ?, ?)'
+        );
+
+        $stmt->execute([
+            $name,
+            $description,
+            $dueDate,
+            $releasedDate,
+            $projectId
+        ]);
+
+        jsonResponse([
+            'success' => true,
+            'version_id' => (int)db()->lastInsertId()
+        ], 201);
+    }
+
+    case 'Edit': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Edit requires POST.'
+            ], 405);
+        }
+
+        $versionId = (int)($_GET['version_id'] ?? 0);
+
+        if ($versionId <= 0) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Missing or invalid version_id.'
+            ], 400);
+        }
+
+        $data = getInputData();
+        $projectStmt = db()->prepare(
+            'SELECT project
+             FROM versions
+             WHERE id = ?
+             LIMIT 1'
+        );
+        $projectStmt->execute([$versionId]);
+        $versionProject = $projectStmt->fetchColumn();
+
+        if (!$versionProject) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'Version not found.'
+            ], 404);
+        }
+
+        requireProjectRight((int)$user['id'], (string)$versionProject, 'Edit Versions');
+
+        $fields = [];
+        $values = [];
+
+        if (array_key_exists('name', $data)) {
+            $name = trim((string)$data['name']);
+
+            if ($name === '') {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Version name cannot be empty.'
+                ], 400);
+            }
+
+            if (strlen($name) > 128) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Version name is too long.'
+                ], 400);
+            }
+
+            $fields[] = 'versions.name = ?';
+            $values[] = $name;
+        }
+
+        if (array_key_exists('description', $data)) {
+            $description = trim((string)$data['description']);
+
+            $fields[] = 'versions.description = ?';
+            $values[] = $description !== '' ? $description : null;
+        }
+
+        if (array_key_exists('due_date', $data)) {
+            $fields[] = 'versions.due_date = ?';
+            $values[] = normalizeDateValue($data['due_date'], 'due_date');
+        }
+
+        if (array_key_exists('released_date', $data)) {
+            $fields[] = 'versions.released_date = ?';
+            $values[] = normalizeDateValue($data['released_date'], 'released_date');
+        }
+
+        if (empty($fields)) {
+            jsonResponse([
+                'success' => false,
+                'error' => 'No editable fields provided.'
+            ], 400);
+        }
+
+        $values[] = $versionId;
+
+        $stmt = db()->prepare(
+            'UPDATE versions
+             INNER JOIN projects ON projects.id = versions.project
+             SET ' . implode(', ', $fields) . '
+             WHERE versions.id = ?'
+        );
+
+        $stmt->execute($values);
+
+        if ($stmt->rowCount() === 0) {
+            $existsStmt = db()->prepare(
+                'SELECT versions.id
+                 FROM versions
+                 INNER JOIN projects ON projects.id = versions.project
+                 WHERE versions.id = ?
+                 LIMIT 1'
+            );
+
+            $existsStmt->execute([$versionId]);
+
+            if ($existsStmt->fetch()) {
+                jsonResponse([
+                    'success' => true,
+                    'version_id' => $versionId
+                ]);
+            }
+
+            jsonResponse([
+                'success' => false,
+                'error' => 'Version not found.'
+            ], 404);
+        }
+
+        jsonResponse([
+            'success' => true,
+            'version_id' => $versionId
+        ]);
+    }
+
+    default: {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Unknown API action.'
+        ], 404);
+    }
+}

ProjectKiln/api/workflow.php

@@ -0,0 +1,409 @@
+<?php
+
+declare(strict_types=1);
+
+require_once __DIR__ . '/../db.php';
+require_once __DIR__ . '/../auth.php';
+
+header('Content-Type: application/json; charset=utf-8');
+
+function jsonResponse(array $data, int $status = 200): void
+{
+    http_response_code($status);
+    header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
+    header('Pragma: no-cache');
+    echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
+    exit;
+}
+
+function getInputData(): array
+{
+    $contentType = $_SERVER['CONTENT_TYPE'] ?? '';
+
+    if (str_contains($contentType, 'application/json')) {
+        $raw = file_get_contents('php://input');
+        $data = json_decode($raw, true);
+
+        return is_array($data) ? $data : [];
+    }
+
+    return $_POST;
+}
+
+function normalizeColor(mixed $color): string
+{
+    $color = trim((string)$color);
+
+    if ($color === '') {
+        return '#4ea1ff';
+    }
+
+    if (!preg_match('/^#[0-9a-fA-F]{6}$/', $color)) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Color must be a hex color like #4ea1ff.'
+        ], 400);
+    }
+
+    return strtolower($color);
+}
+
+function requireState(int $stateId, string $label = 'State'): void
+{
+    if ($stateId <= 0) {
+        jsonResponse([
+            'success' => false,
+            'error' => "{$label} must be valid."
+        ], 400);
+    }
+
+    $stmt = db()->prepare('SELECT id FROM task_states WHERE id = ? LIMIT 1');
+    $stmt->execute([$stateId]);
+
+    if (!$stmt->fetch()) {
+        jsonResponse([
+            'success' => false,
+            'error' => "{$label} not found."
+        ], 400);
+    }
+}
+
+function requireTaskType(int $taskTypeId): void
+{
+    if ($taskTypeId <= 0) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Task type must be valid.'
+        ], 400);
+    }
+
+    $stmt = db()->prepare('SELECT id FROM task_types WHERE id = ? LIMIT 1');
+    $stmt->execute([$taskTypeId]);
+
+    if (!$stmt->fetch()) {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Task type not found.'
+        ], 400);
+    }
+}
+
+function optionResponse(array $option): array
+{
+    return [
+        'id' => (int)$option['id'],
+        'name' => $option['name'],
+        'default_state' => isset($option['default_state']) && $option['default_state'] !== null
+            ? (int)$option['default_state']
+            : null,
+        'icon' => $option['logo'] !== null
+            ? 'data:image/svg+xml;base64,' . base64_encode($option['logo'])
+            : null
+    ];
+}
+
+function workflowData(): array
+{
+    $states = array_map(static function (array $state): array {
+        return [
+            'id' => (int)$state['id'],
+            'name' => $state['name'],
+            'color' => $state['color']
+        ];
+    }, db()->query(
+        'SELECT id, name, color
+         FROM task_states
+         ORDER BY id ASC'
+    )->fetchAll());
+
+    $transitions = array_map(static function (array $transition): array {
+        return [
+            'id' => (int)$transition['id'],
+            'from_id' => (int)$transition['from_id'],
+            'to_id' => (int)$transition['to_id'],
+            'action_name' => $transition['action_name']
+        ];
+    }, db()->query(
+        'SELECT id, from_id, to_id, action_name
+         FROM task_state_transitions
+         ORDER BY id ASC'
+    )->fetchAll());
+
+    $taskTypes = array_map('optionResponse', db()->query(
+        'SELECT id, name, logo, default_state
+         FROM task_types
+         ORDER BY id ASC'
+    )->fetchAll());
+
+    $assignments = array_map(static function (array $assignment): array {
+        return [
+            'id' => (int)$assignment['id'],
+            'task_type' => (int)$assignment['task_type'],
+            'state' => (int)$assignment['state']
+        ];
+    }, db()->query(
+        'SELECT id, task_type, state
+         FROM assigned_task_states
+         ORDER BY task_type ASC, state ASC, id ASC'
+    )->fetchAll());
+
+    return [
+        'states' => $states,
+        'transitions' => $transitions,
+        'task_types' => $taskTypes,
+        'assignments' => $assignments
+    ];
+}
+
+$user = requireApiAuth();
+requireUserRight((int)$user['id'], 'Admin');
+
+$api = $_GET['api'] ?? '';
+
+switch ($api) {
+    case 'WorkflowData': {
+        jsonResponse([
+            'success' => true,
+            'workflow' => workflowData()
+        ]);
+    }
+
+    case 'CreateState': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'CreateState requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $name = trim((string)($data['name'] ?? ''));
+        $color = normalizeColor($data['color'] ?? '');
+
+        if ($name === '') {
+            jsonResponse(['success' => false, 'error' => 'State name is required.'], 400);
+        }
+
+        if (strlen($name) > 128) {
+            jsonResponse(['success' => false, 'error' => 'State name is too long.'], 400);
+        }
+
+        $stmt = db()->prepare('INSERT INTO task_states (name, color) VALUES (?, ?)');
+        $stmt->execute([$name, $color]);
+
+        jsonResponse([
+            'success' => true,
+            'state_id' => (int)db()->lastInsertId()
+        ], 201);
+    }
+
+    case 'DeleteState': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'DeleteState requires POST.'], 405);
+        }
+
+        $stateId = (int)($_GET['state_id'] ?? 0);
+        requireState($stateId);
+
+        try {
+            db()->beginTransaction();
+            db()->prepare('DELETE FROM assigned_task_states WHERE state = ?')->execute([$stateId]);
+            db()->prepare('DELETE FROM task_state_transitions WHERE from_id = ? OR to_id = ?')->execute([$stateId, $stateId]);
+            db()->prepare('UPDATE task_types SET default_state = NULL WHERE default_state = ?')->execute([$stateId]);
+            db()->prepare('DELETE FROM task_states WHERE id = ?')->execute([$stateId]);
+            db()->commit();
+
+        } catch (Throwable $e) {
+            db()->rollBack();
+            jsonResponse(['success' => false, 'error' => 'Could not delete state.'], 500);
+        }
+
+        jsonResponse(['success' => true, 'state_id' => $stateId]);
+    }
+
+    case 'CreateTransition': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'CreateTransition requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $fromId = (int)($data['from_id'] ?? 0);
+        $toId = (int)($data['to_id'] ?? 0);
+        $actionName = trim((string)($data['action_name'] ?? ''));
+
+        requireState($fromId, 'From state');
+        requireState($toId, 'To state');
+
+        if ($actionName === '') {
+            jsonResponse(['success' => false, 'error' => 'Action name is required.'], 400);
+        }
+
+        if (strlen($actionName) > 128) {
+            jsonResponse(['success' => false, 'error' => 'Action name is too long.'], 400);
+        }
+
+        $stmt = db()->prepare(
+            'INSERT INTO task_state_transitions (from_id, to_id, action_name)
+             VALUES (?, ?, ?)'
+        );
+        $stmt->execute([$fromId, $toId, $actionName]);
+
+        jsonResponse([
+            'success' => true,
+            'transition_id' => (int)db()->lastInsertId()
+        ], 201);
+    }
+
+    case 'DeleteTransition': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'DeleteTransition requires POST.'], 405);
+        }
+
+        $transitionId = (int)($_GET['transition_id'] ?? 0);
+
+        if ($transitionId <= 0) {
+            jsonResponse(['success' => false, 'error' => 'Transition must be valid.'], 400);
+        }
+
+        $stmt = db()->prepare('DELETE FROM task_state_transitions WHERE id = ?');
+        $stmt->execute([$transitionId]);
+
+        if ($stmt->rowCount() === 0) {
+            jsonResponse(['success' => false, 'error' => 'Transition not found.'], 404);
+        }
+
+        jsonResponse(['success' => true, 'transition_id' => $transitionId]);
+    }
+
+    case 'AssignState': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'AssignState requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $taskTypeId = (int)($data['task_type'] ?? 0);
+        $stateId = (int)($data['state'] ?? 0);
+
+        requireTaskType($taskTypeId);
+        requireState($stateId);
+
+        $existingStmt = db()->prepare(
+            'SELECT id
+             FROM assigned_task_states
+             WHERE task_type = ?
+               AND state = ?
+             LIMIT 1'
+        );
+        $existingStmt->execute([$taskTypeId, $stateId]);
+        $assignmentId = $existingStmt->fetchColumn();
+
+        if (!$assignmentId) {
+            $stmt = db()->prepare(
+                'INSERT INTO assigned_task_states (task_type, state)
+                 VALUES (?, ?)'
+            );
+            $stmt->execute([$taskTypeId, $stateId]);
+            $assignmentId = db()->lastInsertId();
+        }
+
+        jsonResponse([
+            'success' => true,
+            'assignment_id' => (int)$assignmentId
+        ], 201);
+    }
+
+    case 'SetDefaultState': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'SetDefaultState requires POST.'], 405);
+        }
+
+        $data = getInputData();
+        $taskTypeId = (int)($data['task_type'] ?? 0);
+        $stateId = isset($data['state']) && $data['state'] !== ''
+            ? (int)$data['state']
+            : null;
+
+        requireTaskType($taskTypeId);
+
+        if ($stateId !== null) {
+            requireState($stateId);
+
+            $assignedStmt = db()->prepare(
+                'SELECT id
+                 FROM assigned_task_states
+                 WHERE task_type = ?
+                   AND state = ?
+                 LIMIT 1'
+            );
+            $assignedStmt->execute([$taskTypeId, $stateId]);
+
+            if (!$assignedStmt->fetch()) {
+                jsonResponse([
+                    'success' => false,
+                    'error' => 'Default state must be assigned to this task type.'
+                ], 400);
+            }
+        }
+
+        $stmt = db()->prepare(
+            'UPDATE task_types
+             SET default_state = ?
+             WHERE id = ?'
+        );
+        $stmt->execute([$stateId, $taskTypeId]);
+
+        jsonResponse([
+            'success' => true,
+            'task_type' => $taskTypeId,
+            'default_state' => $stateId
+        ]);
+    }
+
+    case 'RemoveAssignedState': {
+        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
+            jsonResponse(['success' => false, 'error' => 'RemoveAssignedState requires POST.'], 405);
+        }
+
+        $assignmentId = (int)($_GET['assignment_id'] ?? 0);
+
+        if ($assignmentId <= 0) {
+            jsonResponse(['success' => false, 'error' => 'Assignment must be valid.'], 400);
+        }
+
+        $stmt = db()->prepare('DELETE FROM assigned_task_states WHERE id = ?');
+        $lookupStmt = db()->prepare(
+            'SELECT task_type, state
+             FROM assigned_task_states
+             WHERE id = ?
+             LIMIT 1'
+        );
+        $lookupStmt->execute([$assignmentId]);
+        $assignment = $lookupStmt->fetch();
+
+        if (!$assignment) {
+            jsonResponse(['success' => false, 'error' => 'Assignment not found.'], 404);
+        }
+
+        $stmt->execute([$assignmentId]);
+
+        if ($stmt->rowCount() === 0) {
+            jsonResponse(['success' => false, 'error' => 'Assignment not found.'], 404);
+        }
+
+        db()->prepare(
+            'UPDATE task_types
+             SET default_state = NULL
+             WHERE id = ?
+               AND default_state = ?'
+        )->execute([
+            (int)$assignment['task_type'],
+            (int)$assignment['state']
+        ]);
+
+        jsonResponse(['success' => true, 'assignment_id' => $assignmentId]);
+    }
+
+    default: {
+        jsonResponse([
+            'success' => false,
+            'error' => 'Unknown API action.'
+        ], 404);
+    }
+}