<?php

namespace classes;

/**
 * Ensures additive schema for department daily report customer complaints.
 */
class department_daily_report_complaints_schema_bootstrap
{
    private static bool $initialized = false;
    private const TABLE = 'department_daily_report_complaints';
    private const WASH_DATE_INDEX = 'idx_department_daily_report_complaints_department_wash_date';

    public static function ensureTables(): void
    {
        if (self::$initialized) {
            return;
        }

        global $db;

        self::ensureSupportingTables();

        $db->query(
            "CREATE TABLE IF NOT EXISTS department_daily_report_complaints (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                department_id INT NOT NULL,
                customer_number INT NULL,
                wash_date DATE NULL,
                category VARCHAR(64) NULL,
                description TEXT NOT NULL,
                created_by INT NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_department_daily_report_complaints_department_created (department_id, created_at),
                INDEX " . self::WASH_DATE_INDEX . " (department_id, wash_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );

        if (!self::columnExists(self::TABLE, 'wash_date')) {
            $db->query(
                "ALTER TABLE " . self::TABLE . "
                    ADD COLUMN wash_date DATE NULL
                    AFTER customer_number"
            );
        }

        if (!self::columnExists(self::TABLE, 'category')) {
            $db->query(
                "ALTER TABLE " . self::TABLE . "
                    ADD COLUMN category VARCHAR(64) NULL
                    AFTER wash_date"
            );
        }

        if (!self::indexExists(self::TABLE, self::WASH_DATE_INDEX)) {
            $db->query(
                "ALTER TABLE " . self::TABLE . "
                    ADD INDEX " . self::WASH_DATE_INDEX . " (department_id, wash_date)"
            );
        }

        self::$initialized = true;
    }

    private static function ensureSupportingTables(): void
    {
        global $db;

        $db->query(
            "CREATE TABLE IF NOT EXISTS departments (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                description TEXT NULL,
                economic_department_id INT NOT NULL DEFAULT 0,
                slack_webhook TEXT NULL,
                dimension INT NOT NULL DEFAULT 0,
                branding INT NOT NULL DEFAULT 0,
                visible TINYINT(1) NOT NULL DEFAULT 1,
                archived TINYINT(1) NOT NULL DEFAULT 0,
                longitude DECIMAL(10,7) NOT NULL DEFAULT 0,
                latitude DECIMAL(10,7) NOT NULL DEFAULT 0,
                order_priority INT NOT NULL DEFAULT 0,
                created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_departments_archived (archived)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );

        $db->query(
            "CREATE TABLE IF NOT EXISTS department_variables (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                department_id INT NOT NULL,
                variable VARCHAR(191) NOT NULL,
                value TEXT NULL,
                created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_department_variables_department_id (department_id),
                KEY idx_department_variables_variable (variable)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );

        $db->query(
            "CREATE TABLE IF NOT EXISTS users (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                customer_number INT NOT NULL,
                display_name VARCHAR(255) NULL,
                email VARCHAR(255) NULL,
                phone_country_code INT NULL,
                phone BIGINT NULL,
                password VARCHAR(255) NULL,
                group_id INT NOT NULL DEFAULT 0,
                xlvask_customer_id VARCHAR(255) NULL,
                sms_notifications_enabled TINYINT(1) NOT NULL DEFAULT 0,
                email_notifications_enabled TINYINT(1) NOT NULL DEFAULT 0,
                wash_certificate_email VARCHAR(255) NULL,
                two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0,
                two_factor_secret VARCHAR(255) NULL,
                created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                deleted_at DATETIME NULL,
                KEY idx_users_customer_number (customer_number),
                KEY idx_users_group_id (group_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
    }

    private static function indexExists(string $table, string $index): bool
    {
        global $db;

        $table_sql = $db->escape_string($table);
        $index_sql = $db->escape_string($index);
        $result = $db->query(
            "SHOW INDEX FROM `$table_sql` WHERE Key_name = '$index_sql'"
        );

        return $result !== false && $result->num_rows > 0;
    }

    private static function columnExists(string $table, string $column): bool
    {
        global $db;

        $table_sql = $db->escape_string($table);
        $column_sql = $db->escape_string($column);
        $result = $db->query(
            "SHOW COLUMNS FROM `$table_sql` LIKE '$column_sql'"
        );

        return $result !== false && $result->num_rows > 0;
    }
}
