<?php

use classes\db;
use classes\department_outside_hours_statistics_service;

function department_outside_hours_integration_db(): db
{
    if (!integration_enabled()) {
        test()->markTestSkipped('Set RUN_INTEGRATION_TESTS=1 to run DB integration tests.');
    }

    $host = getenv('CONFIG_DB_HOST') ?: null;
    $user = getenv('CONFIG_DB_USER') ?: null;
    $password = getenv('CONFIG_DB_PASSWORD') ?: '';
    $database = getenv('CONFIG_DB_DATABASE') ?: null;
    if (!$host || !$user || !$database) {
        test()->markTestSkipped('Missing DB env vars: CONFIG_DB_HOST/CONFIG_DB_USER/CONFIG_DB_DATABASE.');
    }

    app_require('classes/db.php');
    app_require('classes/department_outside_hours_statistics_service.php');

    $GLOBALS['response'] = new class {
        public function error(string $message): void
        {
            throw new RuntimeException($message);
        }

        public function internal_server_error(string $message): void
        {
            throw new RuntimeException($message);
        }
    };

    $db = new db([
        'host' => $host,
        'user' => $user,
        'password' => $password,
        'database' => $database,
        'port' => (int)(getenv('CONFIG_DB_PORT') ?: 3306),
    ]);
    $db->connect();
    $GLOBALS['db'] = $db;

    department_outside_hours_prepare_tables($db);

    return $db;
}

function department_outside_hours_prepare_tables(db $db): void
{
    $db->query(
        'CREATE TABLE IF NOT EXISTS departments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT NULL
        )'
    );

    $db->query(
        'CREATE TABLE IF NOT EXISTS department_time_bookings_opening_hours (
            id INT AUTO_INCREMENT PRIMARY KEY,
            department INT NOT NULL,
            monday_start TIME NULL,
            monday_end TIME NULL,
            tuesday_start TIME NULL,
            tuesday_end TIME NULL
        )'
    );

    $db->query(
        'CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            is_wash TINYINT(1) NOT NULL DEFAULT 0
        )'
    );

    $db->query(
        'CREATE TABLE IF NOT EXISTS orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            customer_id INT NOT NULL,
            cashier_id INT NOT NULL,
            reference VARCHAR(255) NULL,
            notes TEXT NULL,
            department_id INT NOT NULL,
            reg_1 VARCHAR(64) NULL,
            reg_2 VARCHAR(64) NULL,
            reg_3 VARCHAR(64) NULL,
            created_at DATETIME NOT NULL,
            include_in_invoice TINYINT(1) NULL,
            wash_id VARCHAR(255) NULL,
            deleted_at DATETIME NULL
        )'
    );

    $db->query(
        'CREATE TABLE IF NOT EXISTS order_items (
            id INT AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL,
            product_id INT NOT NULL,
            reference VARCHAR(255) NULL,
            notes TEXT NULL,
            cashier_id INT NOT NULL,
            price DECIMAL(10,2) NOT NULL DEFAULT 0,
            quantity INT NOT NULL DEFAULT 1,
            deleted_at DATETIME NULL
        )'
    );

    $db->query(
        'CREATE TABLE IF NOT EXISTS selfserve_wash_sessions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            lane_id INT NOT NULL,
            department_id INT NOT NULL,
            reg VARCHAR(64) NOT NULL,
            status VARCHAR(64) NOT NULL,
            allowed TINYINT(1) NOT NULL DEFAULT 0,
            wash_started_at DATETIME NULL,
            machine_start_triggered_at DATETIME NULL,
            order_id INT NULL,
            completed_at DATETIME NULL,
            created_at DATETIME NULL,
            deleted_at DATETIME NULL
        )'
    );

    $db->query(
        'CREATE TABLE IF NOT EXISTS xlvask_usage_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            WashId VARCHAR(255) NOT NULL,
            CustomerId VARCHAR(255) NULL,
            Customer VARCHAR(255) NULL,
            VatNumber VARCHAR(255) NULL,
            Location VARCHAR(255) NULL,
            Hall VARCHAR(255) NULL,
            HallId VARCHAR(255) NULL,
            StartTime VARCHAR(64) NULL,
            FinishTime VARCHAR(64) NULL,
            RegistrationNumber VARCHAR(255) NULL,
            VehicleType VARCHAR(255) NULL,
            IdentificationType VARCHAR(255) NULL,
            IdentificationId VARCHAR(255) NULL,
            Info TEXT NULL,
            Updated VARCHAR(64) NULL,
            Prepaid VARCHAR(64) NULL,
            FinishStatus VARCHAR(16) NULL,
            CustomerGuid VARCHAR(255) NULL,
            VehicleId VARCHAR(255) NULL,
            WashItems TEXT NULL
        )'
    );
}

it('integrates orders, xlvask, and self-serve into one outside-hours summary with missing-hours diagnostics', function (): void {
    $db = department_outside_hours_integration_db();
    $service = new department_outside_hours_statistics_service();
    $suffix = (string)random_int(10000, 99999);
    $departmentIds = [];
    $orderIds = [];
    $sessionIds = [];
    $washIds = [];
    $xlvaskCustomerGuids = [];
    $productId = null;

    $createWashOrder = function (int $departmentId, int $productId, string $reference, string $createdAt, ?string $washId = null) use ($db, &$orderIds): int {
        $escapedReference = $db->escape_string($reference);
        $escapedCreatedAt = $db->escape_string($createdAt);
        $escapedWashId = $washId === null ? 'NULL' : "'" . $db->escape_string($washId) . "'";

        $db->query(
            "INSERT INTO orders (customer_id, cashier_id, reference, notes, department_id, reg_1, reg_2, reg_3, created_at, wash_id)
             VALUES (1, 1, '$escapedReference', 'Integration test', $departmentId, 'ZZ$departmentId', '', '', '$escapedCreatedAt', $escapedWashId)"
        );
        $orderId = (int)$db->insert_id();
        $orderIds[] = $orderId;

        $db->query(
            "INSERT INTO order_items (order_id, product_id, reference, notes, cashier_id, price, quantity)
             VALUES ($orderId, $productId, '$escapedReference', 'Integration test', 1, 100, 1)"
        );

        return $orderId;
    };

    $insertSelfserveSession = function (int $departmentId, string $reg, string $startedAt, ?int $orderId = null) use ($db, &$sessionIds): int {
        $escapedReg = $db->escape_string($reg);
        $escapedStartedAt = $db->escape_string($startedAt);
        $orderIdSql = $orderId === null ? 'NULL' : (string)$orderId;

        $db->query(
            "INSERT INTO selfserve_wash_sessions (lane_id, department_id, reg, status, allowed, wash_started_at, machine_start_triggered_at, order_id, completed_at, created_at)
             VALUES (1, $departmentId, '$escapedReg', 'COMPLETED', 1, '$escapedStartedAt', '$escapedStartedAt', $orderIdSql, '$escapedStartedAt', '$escapedStartedAt')"
        );

        $sessionId = (int)$db->insert_id();
        $sessionIds[] = $sessionId;
        return $sessionId;
    };

    $insertXlvaskLog = function (int $departmentId, string $departmentName, string $washId, string $startTime) use ($db, &$washIds, &$xlvaskCustomerGuids): void {
        $escapedDepartmentName = $db->escape_string($departmentName);
        $escapedWashId = $db->escape_string($washId);
        $escapedStartTime = $db->escape_string($startTime);
        $escapedFinishTime = $db->escape_string(substr($startTime, 0, 19) . '.000');
        $hall = $db->escape_string($departmentName . '_1');
        $washIds[] = $washId;
        $customerGuidSql = 'NULL';

        if ($db->num_rows($db->query("SHOW TABLES LIKE 'xlvask_customers'")) > 0) {
            $customerGuid = 'guid-' . $washId;
            $escapedCustomerGuid = $db->escape_string($customerGuid);
            $escapedVendorId = $db->escape_string('vendor-' . $washId);
            $escapedCustomerName = $db->escape_string('Integration Customer ' . $washId);

            $db->query(
                "INSERT IGNORE INTO xlvask_customers (`customerId`, `vendorId`, `name`)
                 VALUES ('$escapedCustomerGuid', '$escapedVendorId', '$escapedCustomerName')"
            );

            $xlvaskCustomerGuids[] = $customerGuid;
            $customerGuidSql = "'" . $escapedCustomerGuid . "'";
        }

        $db->query(
            "INSERT INTO xlvask_usage_logs
                (`WashId`, `CustomerId`, `Customer`, `VatNumber`, `Location`, `Hall`, `HallId`, `StartTime`, `FinishTime`,
                 `RegistrationNumber`, `VehicleType`, `IdentificationType`, `IdentificationId`, `Info`, `Updated`, `Prepaid`,
                  `FinishStatus`, `CustomerGuid`, `VehicleId`, `WashItems`)
             VALUES
                ('$escapedWashId', '123456', 'Integration Customer', '12345678', '$escapedDepartmentName', '$hall', 'hall-$departmentId',
                 '$escapedStartTime', '$escapedFinishTime', 'ZZ$departmentId', 'Truck', 'LPR', 'ZZ$departmentId', 'ZZ$departmentId',
                 NULL, '', '1', $customerGuidSql, 'vehicle-$departmentId', '[]')"
        );
    };

    try {
        $departmentAName = 'DognDeptA' . $suffix;
        $departmentBName = 'DognDeptB' . $suffix;

        $db->query("INSERT INTO departments (name, description) VALUES ('" . $db->escape_string($departmentAName) . "', 'Integration A')");
        $departmentAId = (int)$db->insert_id();
        $departmentIds[] = $departmentAId;

        $db->query("INSERT INTO departments (name, description) VALUES ('" . $db->escape_string($departmentBName) . "', 'Integration B')");
        $departmentBId = (int)$db->insert_id();
        $departmentIds[] = $departmentBId;

        $db->query(
            "INSERT INTO department_time_bookings_opening_hours
                (department, monday_start, monday_end, tuesday_start, tuesday_end)
             VALUES
                ($departmentAId, '08:00:00', '17:00:00', '08:00:00', '17:00:00')"
        );
        $db->query(
            "INSERT INTO department_time_bookings_opening_hours
                (department, tuesday_start, tuesday_end)
             VALUES
                ($departmentBId, '08:00:00', '17:00:00')"
        );

        $productNameColumn = null;
        $productHasDescriptionColumn = false;
        if ($db->num_rows($db->query("SHOW COLUMNS FROM products LIKE 'title'")) > 0) {
            $productNameColumn = 'title';
        } elseif ($db->num_rows($db->query("SHOW COLUMNS FROM products LIKE 'name'")) > 0) {
            $productNameColumn = 'name';
        } else {
            test()->markTestSkipped('Products table is missing both title and name columns required by this integration test.');
        }
        $productHasDescriptionColumn = $db->num_rows($db->query("SHOW COLUMNS FROM products LIKE 'description'")) > 0;

        $productNameValue = $db->escape_string('Outside hours test wash ' . $suffix);
        if ($productHasDescriptionColumn) {
            $productDescriptionValue = $db->escape_string('Integration outside-hours wash');
            $db->query("INSERT INTO products ($productNameColumn, description, is_wash) VALUES ('$productNameValue', '$productDescriptionValue', 1)");
        } else {
            $db->query("INSERT INTO products ($productNameColumn, is_wash) VALUES ('$productNameValue', 1)");
        }
        $productId = (int)$db->insert_id();

        $linkedXlvaskWashId = 'wash-linked-' . $suffix;
        $unmatchedXlvaskWashId = 'wash-unmatched-' . $suffix;

        $createWashOrder($departmentAId, $productId, 'plain-order-' . $suffix, '2026-03-23 05:30:00');
        $linkedSelfserveOrderId = $createWashOrder($departmentAId, $productId, 'linked-selfserve-' . $suffix, '2026-03-23 06:00:00');
        $createWashOrder($departmentAId, $productId, 'linked-xlvask-' . $suffix, '2026-03-23 06:10:00', $linkedXlvaskWashId);
        $createWashOrder($departmentBId, $productId, 'missing-hours-' . $suffix, '2026-03-23 05:00:00');
        $createWashOrder($departmentAId, $productId, 'inside-hours-' . $suffix, '2026-03-23 10:00:00');

        $insertSelfserveSession($departmentAId, 'SELF' . $suffix, '2026-03-23 06:05:00', $linkedSelfserveOrderId);
        $insertSelfserveSession($departmentAId, 'FREE' . $suffix, '2026-03-23 22:15:00');

        $insertXlvaskLog($departmentAId, $departmentAName, $linkedXlvaskWashId, '2026-03-23T06:15:00.000');
        $insertXlvaskLog($departmentAId, $departmentAName, $unmatchedXlvaskWashId, '2026-03-23T23:05:00.000');

        $summary = $service->getSummary('2026-03-23', [$departmentAId, $departmentBId], '2026-03-23');

        expect($summary['total'])->toBe(5);
        expect($summary['by_source'])->toBe([
            'orders' => 1,
            'xlvask' => 2,
            'selfserve' => 2,
        ]);
        expect($summary['has_missing_opening_hours'])->toBeTrue();
        expect($summary['missing_department_ids'])->toBe([$departmentBId]);
    } finally {
        if ($sessionIds !== []) {
            $db->query('DELETE FROM selfserve_wash_sessions WHERE id IN (' . implode(',', array_map('intval', $sessionIds)) . ')');
        }
        if ($washIds !== []) {
            $escapedWashIds = implode(',', array_map(static fn(string $washId): string => "'" . $db->escape_string($washId) . "'", $washIds));
            $db->query("DELETE FROM xlvask_usage_logs WHERE WashId IN ($escapedWashIds)");
        }
        if ($xlvaskCustomerGuids !== [] && $db->num_rows($db->query("SHOW TABLES LIKE 'xlvask_customers'")) > 0) {
            $escapedCustomerGuids = implode(',', array_map(static fn(string $customerGuid): string => "'" . $db->escape_string($customerGuid) . "'", $xlvaskCustomerGuids));
            $db->query("DELETE FROM xlvask_customers WHERE customerId IN ($escapedCustomerGuids)");
        }
        if ($orderIds !== []) {
            $orderIdsSql = implode(',', array_map('intval', $orderIds));
            $db->query("DELETE FROM order_items WHERE order_id IN ($orderIdsSql)");
            $db->query("DELETE FROM orders WHERE id IN ($orderIdsSql)");
        }
        if ($productId !== null) {
            $db->query('DELETE FROM products WHERE id = ' . (int)$productId);
        }
        if ($departmentIds !== []) {
            $departmentIdsSql = implode(',', array_map('intval', $departmentIds));
            $db->query("DELETE FROM department_time_bookings_opening_hours WHERE department IN ($departmentIdsSql)");
            $db->query("DELETE FROM departments WHERE id IN ($departmentIdsSql)");
        }
        $db->close();
    }
});
