Add opening hours table to API test schema

Co-authored-by: Jeppe Bundgaard <jb@truckwash.dk>

services/nginx/app/classes/limited_backoffice_service.php

@@ -230,6 +230,16 @@ class limited_backoffice_service
         'limited_backoffice',
     ];
 
+    /**
+     * @var array<int, true>
+     */
+    private const PHONE_COUNTRY_CODES = [
+        45 => true,
+        46 => true,
+        47 => true,
+        358 => true,
+    ];
+
     /**
      * @var array<string, bool>
      */
@@ -534,7 +544,8 @@ class limited_backoffice_service
         $roleKey = $this->normalizeRoleKey($payload['role_key'] ?? null);
         $displayName = $this->normalizeRequiredString($payload['display_name'] ?? null, 'Display name is required.');
         $password = $this->normalizePassword($payload['password'] ?? null, true);
-        $email = $this->normalizeOptionalString($payload['email'] ?? null);
+        $email = $this->normalizeEmail($payload['email'] ?? null, true);
+        $phone = $this->normalizeOptionalPhonePair($payload);
 
         $mysqli = $this->mysqli();
         $mysqli->begin_transaction();
@@ -545,13 +556,23 @@ class limited_backoffice_service
             $passwordHash = password_hash($password, PASSWORD_DEFAULT);
 
             $statement = $mysqli->prepare(
-                'INSERT INTO `users` (`customer_number`, `display_name`, `email`, `password`, `group_id`)
-                 VALUES (?, ?, ?, ?, ?)'
+                'INSERT INTO `users`
+                    (`customer_number`, `display_name`, `email`, `password`, `group_id`, `phone_country_code`, `phone`)
+                 VALUES (?, ?, ?, ?, ?, ?, ?)'
             );
             if ($statement === false) {
                 throw new \RuntimeException('Unable to prepare employee insert.');
             }
-            $statement->bind_param('isssi', $customerNumber, $displayName, $email, $passwordHash, $groupId);
+            $statement->bind_param(
+                'isssiii',
+                $customerNumber,
+                $displayName,
+                $email,
+                $passwordHash,
+                $groupId,
+                $phone['phone_country_code'],
+                $phone['phone']
+            );
             $statement->execute();
             $employeeId = (int)$mysqli->insert_id;
             $statement->close();
@@ -629,11 +650,12 @@ class limited_backoffice_service
             ? $this->normalizeRequiredString($payload['display_name'], 'Display name is required.')
             : null;
         $email = array_key_exists('email', $payload)
-            ? $this->normalizeOptionalString($payload['email'])
+            ? $this->normalizeEmail($payload['email'], true)
             : null;
         $password = array_key_exists('password', $payload)
             ? $this->normalizePassword($payload['password'], false)
             : null;
+        $phone = $this->normalizeOptionalPhonePair($payload, false);
         $active = array_key_exists('active', $payload)
             ? (bool)$payload['active']
             : $this->isEmployeeRowActive($employee);
@@ -663,6 +685,10 @@ class limited_backoffice_service
             if ($password !== null) {
                 $userUpdates['password'] = password_hash($password, PASSWORD_DEFAULT);
             }
+            if ($phone !== null) {
+                $userUpdates['phone_country_code'] = $phone['phone_country_code'];
+                $userUpdates['phone'] = $phone['phone'];
+            }
             $usersHaveDeletedAt = $this->tableHasColumn('users', 'deleted_at');
             if ($active) {
                 $userUpdates['group_id'] = $managedGroupId;
@@ -1061,6 +1087,89 @@ class limited_backoffice_service
         return $value === '' ? null : $value;
     }
 
+    private function normalizeEmail(mixed $value, bool $required): ?string
+    {
+        $email = $this->normalizeOptionalString($value);
+        if ($email === null) {
+            if ($required) {
+                throw new limited_backoffice_exception('Email is required.', 400);
+            }
+            return null;
+        }
+
+        if (filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
+            throw new limited_backoffice_exception('Email must be a valid email address.', 400);
+        }
+
+        return $email;
+    }
+
+    /**
+     * @param array<string, mixed> $payload
+     * @return array{phone_country_code:int|null,phone:int|null}|null
+     */
+    private function normalizeOptionalPhonePair(array $payload, bool $defaultWhenMissing = true): ?array
+    {
+        $hasCountryCode = array_key_exists('phone_country_code', $payload);
+        $hasPhone = array_key_exists('phone', $payload);
+        if (!$hasCountryCode && !$hasPhone) {
+            return $defaultWhenMissing
+                ? ['phone_country_code' => null, 'phone' => null]
+                : null;
+        }
+
+        if (!$hasCountryCode || !$hasPhone) {
+            throw new limited_backoffice_exception('Phone country code and phone number must be provided together.', 400);
+        }
+
+        $countryCode = $this->normalizeOptionalDigits($payload['phone_country_code']);
+        $phone = $this->normalizeOptionalDigits($payload['phone']);
+        if ($countryCode === null && $phone === null) {
+            return ['phone_country_code' => null, 'phone' => null];
+        }
+
+        if ($countryCode === null || $phone === null) {
+            throw new limited_backoffice_exception('Phone country code and phone number must be provided together.', 400);
+        }
+
+        if (!isset(self::PHONE_COUNTRY_CODES[$countryCode])) {
+            throw new limited_backoffice_exception('Phone country code is not supported.', 400);
+        }
+
+        $phoneText = (string)$phone;
+        if (!preg_match('/^\d{4,15}$/', $phoneText)) {
+            throw new limited_backoffice_exception('Phone number must be 4-15 digits.', 400);
+        }
+
+        return [
+            'phone_country_code' => $countryCode,
+            'phone' => $phone,
+        ];
+    }
+
+    private function normalizeOptionalDigits(mixed $value): ?int
+    {
+        if ($value === null) {
+            return null;
+        }
+
+        if (is_int($value)) {
+            return $value > 0 ? $value : null;
+        }
+
+        if (is_string($value)) {
+            $value = trim($value);
+            if ($value === '') {
+                return null;
+            }
+            if (ctype_digit($value)) {
+                return (int)$value;
+            }
+        }
+
+        throw new limited_backoffice_exception('Phone values must contain digits only.', 400);
+    }
+
     private function normalizePassword(mixed $value, bool $required): ?string
     {
         if ($value === null || $value === '') {
@@ -1236,6 +1345,8 @@ class limited_backoffice_service
             'customer_number' => (int)$row['customer_number'],
             'display_name' => (string)($row['display_name'] ?? ''),
             'email' => $row['email'] === null ? null : (string)$row['email'],
+            'phone_country_code' => $row['phone_country_code'] === null ? null : (int)$row['phone_country_code'],
+            'phone' => $row['phone'] === null ? null : (int)$row['phone'],
             'active' => $active,
             'role' => $this->rolePayload((string)$row['role_key']),
             'departments' => $this->departmentSummaries($departmentIds),
@@ -1356,7 +1467,7 @@ class limited_backoffice_service
         $types = '';
         $values = [];
         foreach ($fields as $field => $value) {
-            if (!in_array($field, ['display_name', 'email', 'password', 'group_id', 'deleted_at'], true)) {
+            if (!in_array($field, ['display_name', 'email', 'password', 'group_id', 'deleted_at', 'phone_country_code', 'phone'], true)) {
                 continue;
             }
             if ($value === null) {

services/nginx/app/openapi.yaml

@@ -12523,6 +12523,40 @@ paths:
             application/json:
               schema: {}
 
+  /superuser/departments/{id}/overview:
+    get:
+      tags:
+        - Departments
+      summary: Get superuser department overview
+      description: Returns the selected department metadata and operational overview metrics for a superuser without requiring scoped department access.
+      operationId: getSuperuserDepartmentOverview
+      parameters:
+        - name: id
+          in: path
+          required: true
+          schema: {type: integer}
+        - name: date
+          in: query
+          required: true
+          schema: {type: string}
+        - name: date_to
+          in: query
+          required: false
+          schema: {type: string}
+      responses:
+        '200':
+          description: Superuser department overview loaded successfully
+          content:
+            application/json:
+              schema:
+                $ref: '#/components/schemas/SuperuserDepartmentOverviewResponse'
+        '400':
+          $ref: '#/components/responses/BadRequest'
+        '403':
+          $ref: '#/components/responses/Forbidden'
+        '404':
+          $ref: '#/components/responses/NotFound'
+
   /superuser/department/branding:
     put:
       tags:
@@ -21550,6 +21584,21 @@ components:
         data:
           $ref: '#/components/schemas/DepartmentDailyReportOverviewPayload'
 
+    SuperuserDepartmentOverviewPayload:
+      type: object
+      properties:
+        department:
+          $ref: '#/components/schemas/Department'
+        overview:
+          $ref: '#/components/schemas/DepartmentDailyReportOverviewPayload'
+
+    SuperuserDepartmentOverviewResponse:
+      type: object
+      properties:
+        success: { type: boolean, example: true }
+        data:
+          $ref: '#/components/schemas/SuperuserDepartmentOverviewPayload'
+
     DepartmentDailyReportTransactionCountPayload:
       type: object
       properties:

services/nginx/app/routes/departmentDailyReportsRoute.php

@@ -812,6 +812,53 @@ class departmentDailyReportsRoute
             ]
         );
 
+        $this->get('/superuser/departments/{id}/overview', function () {
+            global $response;
+            $this->requirePermission('superuser_fetch_department');
+
+            $user = (new authentication())->get_user();
+            if (!$user) {
+                (new logs_o())->add('departments', 'global', 1, 0, 'SUPERUSER_DEPARTMENT_OVERVIEW', 'No user found, or invalid session');
+                $response->error('Invalid session', 400);
+                return;
+            }
+
+            $department_id_param = (string)($this->fromRoute('id') ?? '');
+            if (!ctype_digit($department_id_param) || (int)$department_id_param <= 0) {
+                $response->error('Parameter id must be a positive integer', 400);
+                return;
+            }
+
+            self::requireParameters([
+                'date',
+            ]);
+
+            self::validateDateLocally();
+            $date_to = $this->getDate_to();
+            $department_id = (int)$department_id_param;
+            $department = (new departments_o())->select($department_id);
+
+            if (!$department->exists()) {
+                $response->error('Department not found', 404);
+                return;
+            }
+
+            (new logs_o())->add('departments', 'global', 1, $user->id, 'SUPERUSER_DEPARTMENT_OVERVIEW', 'Successfully loaded superuser department overview');
+
+            $response->success([
+                'department' => $department->asArray(['slack_webhook' => false]),
+                'overview' => $this->buildDailyReportOverview(
+                    [$department_id],
+                    (string)self::getParameter('date'),
+                    $date_to
+                ),
+            ]);
+        },
+            [
+                'superuser_fetch_department' => 'Get the superuser department overview'
+            ]
+        );
+
         $this->get('/departments/daily-reports/overview', function () {
             global $response;
             $this->requirePermission('list_department_daily_reports');

services/nginx/app/tests/Api/LimitedBackofficeApiTest.php

@@ -400,6 +400,8 @@ it('creates updates lists and deactivates scoped employees without exposing raw
     $created = api_client()->post('/limited-backoffice/employees', [
         'display_name' => 'Limited Cashier',
         'email' => 'limited-cashier@example.test',
+        'phone_country_code' => 45,
+        'phone' => 12345678,
         'password' => 'Secret123!',
         'role_key' => 'cashier',
         'department_ids' => [(int)$department['id']],
@@ -413,6 +415,9 @@ it('creates updates lists and deactivates scoped employees without exposing raw
     $employeeId = (int)($created->data()['id'] ?? 0);
     expect($employeeId)->toBeGreaterThan(0);
     limited_backoffice_cleanup_created_employee($employeeId);
+    expect($created->data()['email'] ?? null)->toBe('limited-cashier@example.test');
+    expect($created->data()['phone_country_code'] ?? null)->toBe(45);
+    expect($created->data()['phone'] ?? null)->toBe(12345678);
     expect($created->body)->not->toContain('department_access_');
     expect($created->body)->not->toContain('permissions');
 
@@ -431,6 +436,9 @@ it('creates updates lists and deactivates scoped employees without exposing raw
 
     $updated = api_client()->put('/limited-backoffice/employees/' . $employeeId, [
         'display_name' => 'Limited Lead',
+        'email' => 'limited-lead@example.test',
+        'phone_country_code' => 358,
+        'phone' => 87654321,
         'role_key' => 'operations_lead',
         'department_ids' => [(int)$department['id']],
     ], $session['headers']);
@@ -440,11 +448,25 @@ it('creates updates lists and deactivates scoped employees without exposing raw
         ->assertSuccess();
 
     expect($updated->data()['display_name'] ?? null)->toBe('Limited Lead');
+    expect($updated->data()['email'] ?? null)->toBe('limited-lead@example.test');
+    expect($updated->data()['phone_country_code'] ?? null)->toBe(358);
+    expect($updated->data()['phone'] ?? null)->toBe(87654321);
     expect($updated->data()['role']['key'] ?? null)->toBe('operations_lead');
 
     $list = api_client()->get('/limited-backoffice/employees', $session['headers']);
     $ids = array_column($list->data(), 'id');
     expect($ids)->toContain($employeeId);
+    $listedEmployee = null;
+    foreach ($list->data() as $employee) {
+        if ((int)($employee['id'] ?? 0) === $employeeId) {
+            $listedEmployee = $employee;
+            break;
+        }
+    }
+    expect($listedEmployee)->not->toBeNull();
+    expect($listedEmployee['email'] ?? null)->toBe('limited-lead@example.test');
+    expect($listedEmployee['phone_country_code'] ?? null)->toBe(358);
+    expect($listedEmployee['phone'] ?? null)->toBe(87654321);
     expect($list->body)->not->toContain('department_access_');
 
     $deactivated = api_client()->delete('/limited-backoffice/employees/' . $employeeId, null, $session['headers']);
@@ -466,6 +488,34 @@ it('creates updates lists and deactivates scoped employees without exposing raw
     });
 });
 
+it('accepts employees without optional phone details', function (): void {
+    api_test_covers('POST /limited-backoffice/employees', 'happy');
+
+    $department = api_fixtures()->createDepartment(['name' => 'Limited Employee No Phone']);
+    $session = limited_backoffice_manager_session([(int)$department['id']]);
+
+    $created = api_client()->post('/limited-backoffice/employees', [
+        'display_name' => 'Limited No Phone',
+        'email' => 'limited-no-phone@example.test',
+        'password' => 'Secret123!',
+        'role_key' => 'viewer',
+        'department_ids' => [(int)$department['id']],
+    ], $session['headers']);
+
+    $created
+        ->assertStatus(200)
+        ->assertEnvelope()
+        ->assertSuccess();
+
+    $employeeId = (int)($created->data()['id'] ?? 0);
+    expect($employeeId)->toBeGreaterThan(0);
+    limited_backoffice_cleanup_created_employee($employeeId);
+    expect(array_key_exists('phone_country_code', $created->data()))->toBeTrue();
+    expect(array_key_exists('phone', $created->data()))->toBeTrue();
+    expect($created->data()['phone_country_code'])->toBeNull();
+    expect($created->data()['phone'])->toBeNull();
+});
+
 it('rejects employee scopes roles raw permissions self edits superusers and shared groups', function (): void {
     api_test_covers('POST /limited-backoffice/employees', 'validation');
     api_test_covers('PUT /limited-backoffice/employees/{employeeId}', 'validation');
@@ -476,6 +526,7 @@ it('rejects employee scopes roles raw permissions self edits superusers and shar
 
     api_client()->post('/limited-backoffice/employees', [
         'display_name' => 'Outside Employee',
+        'email' => 'outside@example.test',
         'password' => 'Secret123!',
         'role_key' => 'cashier',
         'department_ids' => [(int)$otherDepartment['id']],
@@ -487,6 +538,7 @@ it('rejects employee scopes roles raw permissions self edits superusers and shar
 
     api_client()->post('/limited-backoffice/employees', [
         'display_name' => 'Raw Employee',
+        'email' => 'raw@example.test',
         'password' => 'Secret123!',
         'role_key' => 'cashier',
         'department_ids' => [(int)$department['id']],
@@ -499,6 +551,7 @@ it('rejects employee scopes roles raw permissions self edits superusers and shar
 
     api_client()->post('/limited-backoffice/employees', [
         'display_name' => 'Unknown Role Employee',
+        'email' => 'unknown-role@example.test',
         'password' => 'Secret123!',
         'role_key' => 'superuser',
         'department_ids' => [(int)$department['id']],
@@ -551,3 +604,88 @@ it('rejects employee scopes roles raw permissions self edits superusers and shar
         ->assertSuccess(false)
         ->assertMessage('Cannot manage shared groups.');
 });
+
+it('rejects invalid limited backoffice employee contact details', function (): void {
+    api_test_covers('POST /limited-backoffice/employees', 'validation');
+    api_test_covers('PUT /limited-backoffice/employees/{employeeId}', 'validation');
+
+    $department = api_fixtures()->createDepartment(['name' => 'Limited Employee Contact Validation']);
+    $session = limited_backoffice_manager_session([(int)$department['id']]);
+    $basePayload = [
+        'display_name' => 'Contact Employee',
+        'email' => 'contact@example.test',
+        'password' => 'Secret123!',
+        'role_key' => 'viewer',
+        'department_ids' => [(int)$department['id']],
+    ];
+
+    api_client()->post('/limited-backoffice/employees', array_diff_key($basePayload, ['email' => true]), $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Email is required.');
+
+    api_client()->post('/limited-backoffice/employees', [
+        ...$basePayload,
+        'email' => 'not-an-email',
+    ], $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Email must be a valid email address.');
+
+    api_client()->post('/limited-backoffice/employees', [
+        ...$basePayload,
+        'phone_country_code' => 45,
+    ], $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Phone country code and phone number must be provided together.');
+
+    api_client()->post('/limited-backoffice/employees', [
+        ...$basePayload,
+        'phone_country_code' => 1,
+        'phone' => 12345678,
+    ], $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Phone country code is not supported.');
+
+    api_client()->post('/limited-backoffice/employees', [
+        ...$basePayload,
+        'phone_country_code' => 45,
+        'phone' => '12ab',
+    ], $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Phone values must contain digits only.');
+
+    $created = api_client()->post('/limited-backoffice/employees', $basePayload, $session['headers'])
+        ->assertStatus(200)
+        ->assertEnvelope()
+        ->assertSuccess();
+
+    $employeeId = (int)($created->data()['id'] ?? 0);
+    expect($employeeId)->toBeGreaterThan(0);
+    limited_backoffice_cleanup_created_employee($employeeId);
+
+    api_client()->put('/limited-backoffice/employees/' . $employeeId, [
+        'email' => '',
+    ], $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Email is required.');
+
+    api_client()->put('/limited-backoffice/employees/' . $employeeId, [
+        'phone_country_code' => 45,
+        'phone' => '123',
+    ], $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Phone number must be 4-15 digits.');
+});

services/nginx/app/tests/Api/SuperuserDepartmentOverviewApiTest.php

@@ -0,0 +1,100 @@
+<?php
+
+declare(strict_types=1);
+
+usesApiSuite();
+
+it('loads a single department overview for superusers without department scoped access', function (): void {
+    api_test_covers('GET /superuser/departments/{id}/overview', 'happy');
+
+    $department = api_fixtures()->createDepartment([
+        'name' => 'Overview Department ' . uniqid('', false),
+        'description' => 'Department overview fixture',
+        'economic_department_id' => 42,
+        'visible' => 1,
+    ]);
+    $departmentRow = api_fixtures()->fetchRowById('departments', (int)$department['id']);
+    $session = api_fixtures()->createUserSession([
+        'superuser_fetch_department',
+    ]);
+
+    $response = api_client()->get(
+        '/superuser/departments/' . $department['id'] . '/overview?date=2026-07-06&date_to=2026-07-06',
+        $session['headers']
+    );
+
+    $response
+        ->assertStatus(200)
+        ->assertEnvelope()
+        ->assertSuccess();
+
+    $payload = $response->data();
+
+    expect($payload)->toBeArray();
+    expect($payload['department'])
+        ->toBeArray()
+        ->toHaveKey('id', (int)$department['id'])
+        ->toHaveKey('name', $departmentRow['name'])
+        ->toHaveKey('description', 'Department overview fixture')
+        ->toHaveKey('economic_department_id', 42);
+
+    expect($payload['overview'])
+        ->toBeArray()
+        ->toHaveKey('department_ids', [(int)$department['id']])
+        ->toHaveKey('date', '2026-07-06')
+        ->toHaveKey('date_to', '2026-07-06');
+
+    expect($payload['overview']['metrics'])
+        ->toBeArray()
+        ->toHaveKeys([
+            'bookings',
+            'complaints',
+            'night_washes',
+            'revenue',
+            'washes',
+            'products_sold',
+            'transactions',
+            'water_usage',
+            'overtime',
+        ]);
+    expect($payload['overview']['metrics']['revenue']['state'])->toBe('ready');
+    expect($payload['overview']['metrics']['revenue']['value'])->toBe(0);
+    expect($payload['overview']['products'])->toBeArray();
+});
+
+it('rejects superuser department overview requests without permission or valid input', function (): void {
+    api_test_covers('GET /superuser/departments/{id}/overview', 'auth');
+    api_test_covers('GET /superuser/departments/{id}/overview', 'failure');
+
+    $department = api_fixtures()->createDepartment();
+    $unauthorizedSession = api_fixtures()->createUserSession([]);
+
+    api_client()->get(
+        '/superuser/departments/' . $department['id'] . '/overview?date=2026-07-06',
+        $unauthorizedSession['headers']
+    )
+        ->assertStatus(403)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMissingPermissions(['superuser_fetch_department']);
+
+    $session = api_fixtures()->createUserSession(['superuser_fetch_department']);
+
+    api_client()->get('/superuser/departments/bad/overview?date=2026-07-06', $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Parameter id must be a positive integer');
+
+    api_client()->get('/superuser/departments/' . $department['id'] . '/overview', $session['headers'])
+        ->assertStatus(400)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Missing required parameters: date');
+
+    api_client()->get('/superuser/departments/99999999/overview?date=2026-07-06', $session['headers'])
+        ->assertStatus(404)
+        ->assertEnvelope()
+        ->assertSuccess(false)
+        ->assertMessage('Department not found');
+});

services/nginx/app/tests/Api/api_coverage_manifest.php

@@ -19,6 +19,7 @@ return [
         'GET /branding',
         'POST /branding',
         'PUT /branding',
+        'GET /superuser/departments/{id}/overview',
         'PUT /superuser/department/branding',
         'POST /bird/voice/calls/webhook/inbound',
     ],

services/nginx/app/tests/Support/Api/ApiSchemaBootstrap.php

@@ -111,6 +111,46 @@ CREATE TABLE IF NOT EXISTS `department_variables` (
     KEY `idx_department_variables_department_id` (`department_id`),
     KEY `idx_department_variables_variable` (`variable`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+SQL,
+            'department_daily_reports' => <<<'SQL'
+CREATE TABLE IF NOT EXISTS `department_daily_reports` (
+    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
+    `department_id` INT NOT NULL,
+    `water_usage` INT NOT NULL DEFAULT 0,
+    `water_usage_morning` INT NOT NULL DEFAULT 0,
+    `notes` TEXT NULL,
+    `filled_by` INT NOT NULL DEFAULT 0,
+    `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
+    `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+    PRIMARY KEY (`id`),
+    KEY `idx_department_daily_reports_department_id` (`department_id`),
+    KEY `idx_department_daily_reports_created_at` (`created_at`),
+    KEY `idx_department_daily_reports_department_created_at` (`department_id`, `created_at`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+SQL,
+            'department_time_bookings_opening_hours' => <<<'SQL'
+CREATE TABLE IF NOT EXISTS `department_time_bookings_opening_hours` (
+    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
+    `department` INT NOT NULL,
+    `monday_start` TIME NULL,
+    `monday_end` TIME NULL,
+    `tuesday_start` TIME NULL,
+    `tuesday_end` TIME NULL,
+    `wednesday_start` TIME NULL,
+    `wednesday_end` TIME NULL,
+    `thursday_start` TIME NULL,
+    `thursday_end` TIME NULL,
+    `friday_start` TIME NULL,
+    `friday_end` TIME NULL,
+    `saturday_start` TIME NULL,
+    `saturday_end` TIME NULL,
+    `sunday_start` TIME NULL,
+    `sunday_end` TIME NULL,
+    `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
+    `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+    PRIMARY KEY (`id`),
+    KEY `idx_department_time_bookings_opening_hours_department` (`department`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 SQL,
             'department_gates' => <<<'SQL'
 CREATE TABLE IF NOT EXISTS `department_gates` (

services/nginx/app/tests/Unit/DailyReports/DepartmentDailyReportsOverviewOpenApiSpecTest.php

@@ -31,8 +31,11 @@ it('documents the daily report overview endpoint and reusable schemas in openapi
     $content = department_daily_reports_openapi_content_or_skip();
 
     expect($content)->toContain('/departments/daily-reports/overview:');
+    expect($content)->toContain('/superuser/departments/{id}/overview:');
     expect($content)->toContain('operationId: getDailyReportOverview');
+    expect($content)->toContain('operationId: getSuperuserDepartmentOverview');
     expect($content)->toContain('DepartmentDailyReportOverviewResponse:');
+    expect($content)->toContain('SuperuserDepartmentOverviewResponse:');
     expect($content)->toContain('DepartmentDailyReportMetric:');
     expect($content)->toContain('DepartmentDailyReportProductTile:');
     expect($content)->toContain('- name: department_ids');

services/nginx/app/tests/Unit/DailyReports/DepartmentDailyReportsOverviewRouteTest.php

@@ -342,6 +342,8 @@ it('wires the overview route to batched repository methods and overview path', f
     $objectContent = (string)file_get_contents(app_path('objects/department_daily_reports_o.php'));
 
     expect($routeContent)->toContain('/departments/daily-reports/overview');
+    expect($routeContent)->toContain('/superuser/departments/{id}/overview');
+    expect($routeContent)->toContain('superuser_fetch_department');
     expect($routeContent)->toContain('/departments/daily-reports/complaints');
     expect($routeContent)->toContain('outsideHoursStatisticsService');
     expect($routeContent)->toContain('dailyReportComplaintsRepository');