<?php
require_once __DIR__ . '/app_paths.php';
require_once __DIR__ . '/app_runtime.php';
require_once __DIR__ . '/app_access.php';
// admin/db_bootstrap.php
// تحميل آمن لاتصال قاعدة البيانات من ملفات الجذر بدون إسقاط الصفحة مباشرة.

function ensure_db_from_install_config(): ?mysqli
{
    if (!function_exists('ua_install_database_config')) {
        return null;
    }

    $db = ua_install_database_config();
    $host = trim((string)($db['host'] ?? ''));
    $user = (string)($db['user'] ?? '');
    $name = trim((string)($db['name'] ?? ''));
    if ($host === '' || $user === '' || $name === '') {
        return null;
    }

    if (!class_exists('mysqli')) {
        return null;
    }

    $pass = (string)($db['pass'] ?? '');
    $port = (int)($db['port'] ?? 3306);
    if ($port <= 0) $port = 3306;
    $charset = trim((string)($db['charset'] ?? 'utf8mb4'));
    if ($charset === '') $charset = 'utf8mb4';

    try {
        $mysqli = mysqli_init();
        if (!$mysqli) return null;
        @mysqli_options($mysqli, MYSQLI_OPT_CONNECT_TIMEOUT, 5);
        if (!@$mysqli->real_connect($host, $user, $pass, $name, $port)) {
            error_log(sprintf(
                '[NetworkCustomers] Database connection failed host=%s port=%d database=%s user=%s error=%s',
                $host,
                $port,
                $name,
                $user,
                mysqli_connect_error()
            ));
            return null;
        }
        if (method_exists($mysqli, 'set_charset')) {
            @$mysqli->set_charset($charset);
        }
        $GLOBALS['conn'] = $mysqli;
        ua_core_schema_migrate($mysqli);
        return $mysqli;
    } catch (Throwable $e) {
        error_log(sprintf(
            '[NetworkCustomers] Database connection exception host=%s port=%d database=%s user=%s error=%s',
            $host,
            $port,
            $name,
            $user,
            $e->getMessage()
        ));
        return null;
    }
}

function ensure_db(bool $fatal = true): ?mysqli
{
    ua_enforce_install_access();

    if (isset($GLOBALS['conn']) && ($GLOBALS['conn'] instanceof mysqli)) {
        ua_core_schema_migrate($GLOBALS['conn']);
        return $GLOBALS['conn'];
    }

    $fromInstallConfig = ensure_db_from_install_config();
    if ($fromInstallConfig instanceof mysqli) {
        return $fromInstallConfig;
    }

    // Branch and agent installations are remote-data clients. Once their
    // link is approved they must never fall back to a local legacy database,
    // otherwise an unavailable main server could silently show stale or
    // unrelated local data.
    $remoteOnly = (function_exists('ua_is_branch_install') && ua_is_branch_install())
        || (function_exists('ua_is_agent_install') && ua_is_agent_install());
    if ($remoteOnly) {
        if ($fatal) {
            http_response_code(503);
            die('تعذر الاتصال بقاعدة بيانات النسخة الرئيسية. هذه النسخة لا تستخدم قاعدة بيانات محلية.');
        }
        return null;
    }

    $rootDir = dirname(__DIR__);
    $candidates = [
        $rootDir . '/db.php',
        $rootDir . '/config.php',
        $rootDir . '/includes/db.php',
        $rootDir . '/includes/config.php',
        $rootDir . '/inc/db.php',
        $rootDir . '/inc/config.php',
    ];

    $oldReport = null;
    if (function_exists('mysqli_report')) {
        $oldReport = true;
        @mysqli_report(MYSQLI_REPORT_OFF);
    }

    foreach ($candidates as $file) {
        if (!is_file($file)) continue;
        $conn = null;
        $mysqli = null;
        try {
            ob_start();
            include_once $file;
            ob_end_clean();
        } catch (Throwable $e) {
            while (ob_get_level()) { @ob_end_clean(); }
            continue;
        }

        if (isset($conn) && ($conn instanceof mysqli)) {
            $GLOBALS['conn'] = $conn;
        } elseif (isset($mysqli) && ($mysqli instanceof mysqli)) {
            $GLOBALS['conn'] = $mysqli;
        }

        if (isset($GLOBALS['conn']) && ($GLOBALS['conn'] instanceof mysqli)) {
            if (method_exists($GLOBALS['conn'], 'set_charset')) {
                @$GLOBALS['conn']->set_charset('utf8mb4');
            }
            ua_core_schema_migrate($GLOBALS['conn']);
            return $GLOBALS['conn'];
        }
    }

    if ($fatal) {
        http_response_code(500);
        die('تعذر إنشاء اتصال قاعدة البيانات. تحقق من خدمة MariaDB ومن إعدادات الربط.');
    }
    return null;
}

function ua_core_schema_migrate(mysqli $conn): void
{
    static $done = [];
    $connectionKey = spl_object_id($conn);
    if (!empty($done[$connectionKey]) && ua_core_schema_is_ready($conn)) return;
    $done[$connectionKey] = true;

    if (!ua_core_schema_migrate_needed($conn)) {
        return;
    }

    $oldReport = null;
    if (function_exists('mysqli_report')) {
        $oldReport = true;
        @mysqli_report(MYSQLI_REPORT_OFF);
    }

    try {
        @$conn->set_charset('utf8mb4');

        ua_core_ensure_subscribers($conn);
        ua_core_ensure_finance_schema($conn);
        ua_core_ensure_mikrotik_settings($conn);
        ua_core_ensure_airmax_devices($conn);
        ua_core_ensure_cashbox_daily($conn);
        ua_core_ensure_message_templates_charset($conn);
        if (ua_core_schema_is_ready($conn)) {
            ua_core_schema_migrate_mark($conn);
        }
    } catch (Throwable $e) {
        // Schema repair is best-effort. Individual pages still show their own errors.
    }
}

function ua_core_schema_marker_dir(): string
{
    $dir = __DIR__ . '/cache/runtime';
    if (!is_dir($dir)) {
        @mkdir($dir, 0777, true);
    }
    return $dir;
}

function ua_core_schema_marker_key(mysqli $conn): string
{
    $host = (string)($conn->host_info ?? '');
    $db = '';
    try {
        $r = @$conn->query('SELECT DATABASE() AS db_name');
        if ($r && ($row = $r->fetch_assoc())) {
            $db = (string)($row['db_name'] ?? '');
        }
    } catch (Throwable $e) {}
    $fingerprint = @filemtime(__FILE__) ?: time();
    return sha1($host . '|' . $db . '|' . $fingerprint);
}

function ua_core_schema_marker_file(mysqli $conn): string
{
    return ua_core_schema_marker_dir() . '/core_schema_' . ua_core_schema_marker_key($conn) . '.json';
}

function ua_core_schema_migrate_needed(mysqli $conn): bool
{
    // A marker is only a performance hint. The database itself is the source
    // of truth, especially on a newly installed or partially restored system.
    if (!ua_core_schema_is_ready($conn)) return true;
    if ((string)($_GET['ua_schema_migrate'] ?? '') === '1') return true;
    if ((string)($_ENV['UA_FORCE_SCHEMA_MIGRATE'] ?? '') === '1') return true;

    $file = ua_core_schema_marker_file($conn);
    if (!is_file($file)) return true;

    $raw = @file_get_contents($file);
    $data = json_decode((string)$raw, true);
    if (!is_array($data) || empty($data['ok'])) return true;

    $ts = (int)($data['ts'] ?? 0);
    return $ts <= 0 || (time() - $ts) > 86400;
}

function ua_core_schema_migrate_mark(mysqli $conn): void
{
    $file = ua_core_schema_marker_file($conn);
    $tmp = $file . '.tmp';
    $data = [
        'ok' => true,
        'ts' => time(),
        'source' => 'ua_core_schema_migrate',
    ];
    if (@file_put_contents($tmp, json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), LOCK_EX) !== false) {
        @rename($tmp, $file);
    }
}

function ua_core_table_exists(mysqli $conn, string $table): bool
{
    $t = $conn->real_escape_string($table);
    $r = @$conn->query("SHOW TABLES LIKE '{$t}'");
    return (bool)($r && $r->num_rows > 0);
}

function ua_core_required_tables(): array
{
    return [
        'subscribers',
        'invoices',
        'payments',
        'expenses',
        'cashbox_daily',
        'month_closings',
        'mikrotik_settings',
        'airmax_devices',
        'airmax_live_cache',
    ];
}

function ua_core_schema_missing_tables(mysqli $conn): array
{
    $missing = [];
    foreach (ua_core_required_tables() as $table) {
        if (!ua_core_table_exists($conn, $table)) $missing[] = $table;
    }
    return $missing;
}

function ua_core_schema_is_ready(mysqli $conn): bool
{
    return ua_core_schema_missing_tables($conn) === [];
}

function ua_core_col_exists(mysqli $conn, string $table, string $column): bool
{
    if (!ua_core_table_exists($conn, $table)) return false;
    $t = str_replace('`', '', $table);
    $c = $conn->real_escape_string($column);
    $r = @$conn->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    return (bool)($r && $r->num_rows > 0);
}

function ua_core_add_col(mysqli $conn, string $table, string $column, string $definition): void
{
    if (!ua_core_col_exists($conn, $table, $column)) {
        $t = str_replace('`', '', $table);
        @ $conn->query("ALTER TABLE `{$t}` ADD COLUMN `{$column}` {$definition}");
    }
}

function ua_core_ensure_subscribers(mysqli $conn): void
{
    @$conn->query("CREATE TABLE IF NOT EXISTS `subscribers` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `username` VARCHAR(150) NULL,
        `password_plain` VARCHAR(150) NULL,
        `full_name` VARCHAR(255) NULL,
        `first_name` VARCHAR(100) NULL,
        `father_name` VARCHAR(100) NULL,
        `last_name` VARCHAR(100) NULL,
        `mother_name` VARCHAR(100) NULL,
        `birth_date` DATE NULL,
        `document_type` VARCHAR(40) NOT NULL DEFAULT 'هوية شخصية',
        `nationality` VARCHAR(120) NOT NULL DEFAULT 'سوري',
        `document_number` VARCHAR(120) NULL,
        `phone` VARCHAR(50) NULL,
        `address_area` VARCHAR(255) NULL,
        `national_id` VARCHAR(80) NULL,
        `record_number` VARCHAR(80) NULL,
        `speed` VARCHAR(100) NULL,
        `package` VARCHAR(100) NULL,
        `package_name` VARCHAR(100) NULL,
        `speed_name` VARCHAR(100) NULL,
        `status` VARCHAR(50) NULL,
        `notes` TEXT NULL,
        `speed_state` VARCHAR(30) NOT NULL DEFAULT 'ORIGINAL',
        `base_speed` VARCHAR(100) NULL,
        `current_speed` VARCHAR(100) NULL,
        `is_deleted` TINYINT(1) NOT NULL DEFAULT 0,
        `deleted_at` DATETIME NULL,
        `is_online` TINYINT(1) NOT NULL DEFAULT 0,
        `last_seen_at` DATETIME NULL,
        `last_disconnect_at` DATETIME NULL,
        `ppp_username` VARCHAR(150) NULL,
        `ppp_password` VARCHAR(150) NULL,
        `ppp_profile` VARCHAR(150) NULL,
        `ppp_rate_limit` VARCHAR(80) NULL,
        `ppp_last_sync` DATETIME NULL,
        `ppp_is_disabled` TINYINT(1) NOT NULL DEFAULT 0,
        `ppp_disabled_at` DATETIME NULL,
        `ip_address` VARCHAR(45) NULL,
        `quota_gb` INT NULL,
        `used_gb` DECIMAL(12,2) NOT NULL DEFAULT 0,
        `sector_id` INT NULL,
        `vlan_id` INT NULL,
        `provision_status` VARCHAR(50) NULL,
        `mt_source_type` VARCHAR(20) NULL,
        `mt_profile` VARCHAR(100) NULL,
        `mt_rate_limit` VARCHAR(50) NULL,
        `mt_limit_bytes_in` BIGINT NULL DEFAULT 0,
        `mt_limit_bytes_out` BIGINT NULL DEFAULT 0,
        `mt_limit_bytes_total` BIGINT NULL DEFAULT 0,
        `mt_disabled` TINYINT(1) NOT NULL DEFAULT 0,
        `mt_comment` VARCHAR(255) NULL,
        `mt_last_sync` DATETIME NULL,
        `pppoe_profile` VARCHAR(150) NULL,
        `mikrotik_serial` VARCHAR(120) NULL,
        `id_card_front` VARCHAR(255) NULL,
        `id_card_back` VARCHAR(255) NULL,
        `id_front` VARCHAR(255) NULL,
        `id_back` VARCHAR(255) NULL,
        `id_pdf` VARCHAR(255) NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME NULL,
        INDEX (`username`),
        INDEX (`ppp_username`),
        INDEX (`phone`),
        INDEX (`is_deleted`),
        INDEX (`is_online`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $cols = [
        'username' => "VARCHAR(150) NULL",
        'password_plain' => "VARCHAR(150) NULL",
        'full_name' => "VARCHAR(255) NULL",
        'first_name' => "VARCHAR(100) NULL",
        'father_name' => "VARCHAR(100) NULL",
        'last_name' => "VARCHAR(100) NULL",
        'mother_name' => "VARCHAR(100) NULL",
        'birth_date' => "DATE NULL",
        'document_type' => "VARCHAR(40) NOT NULL DEFAULT 'هوية شخصية'",
        'nationality' => "VARCHAR(120) NOT NULL DEFAULT 'سوري'",
        'document_number' => "VARCHAR(120) NULL",
        'phone' => "VARCHAR(50) NULL",
        'address_area' => "VARCHAR(255) NULL",
        'national_id' => "VARCHAR(80) NULL",
        'record_number' => "VARCHAR(80) NULL",
        'speed' => "VARCHAR(100) NULL",
        'package' => "VARCHAR(100) NULL",
        'package_name' => "VARCHAR(100) NULL",
        'speed_name' => "VARCHAR(100) NULL",
        'status' => "VARCHAR(50) NULL",
        'notes' => "TEXT NULL",
        'speed_state' => "VARCHAR(30) NOT NULL DEFAULT 'ORIGINAL'",
        'base_speed' => "VARCHAR(100) NULL",
        'current_speed' => "VARCHAR(100) NULL",
        'is_deleted' => "TINYINT(1) NOT NULL DEFAULT 0",
        'deleted_at' => "DATETIME NULL",
        'is_online' => "TINYINT(1) NOT NULL DEFAULT 0",
        'last_seen_at' => "DATETIME NULL",
        'last_disconnect_at' => "DATETIME NULL",
        'ppp_username' => "VARCHAR(150) NULL",
        'ppp_password' => "VARCHAR(150) NULL",
        'ppp_profile' => "VARCHAR(150) NULL",
        'ppp_rate_limit' => "VARCHAR(80) NULL",
        'ppp_last_sync' => "DATETIME NULL",
        'ppp_is_disabled' => "TINYINT(1) NOT NULL DEFAULT 0",
        'ppp_disabled_at' => "DATETIME NULL",
        'ip_address' => "VARCHAR(45) NULL",
        'quota_gb' => "INT NULL",
        'used_gb' => "DECIMAL(12,2) NOT NULL DEFAULT 0",
        'sector_id' => "INT NULL",
        'vlan_id' => "INT NULL",
        'provision_status' => "VARCHAR(50) NULL",
        'mt_source_type' => "VARCHAR(20) NULL",
        'mt_profile' => "VARCHAR(100) NULL",
        'mt_rate_limit' => "VARCHAR(50) NULL",
        'mt_limit_bytes_in' => "BIGINT NULL DEFAULT 0",
        'mt_limit_bytes_out' => "BIGINT NULL DEFAULT 0",
        'mt_limit_bytes_total' => "BIGINT NULL DEFAULT 0",
        'mt_disabled' => "TINYINT(1) NOT NULL DEFAULT 0",
        'mt_comment' => "VARCHAR(255) NULL",
        'mt_last_sync' => "DATETIME NULL",
        'pppoe_profile' => "VARCHAR(150) NULL",
        'mikrotik_serial' => "VARCHAR(120) NULL",
        'id_card_front' => "VARCHAR(255) NULL",
        'id_card_back' => "VARCHAR(255) NULL",
        'id_front' => "VARCHAR(255) NULL",
        'id_back' => "VARCHAR(255) NULL",
        'id_pdf' => "VARCHAR(255) NULL",
        'created_at' => "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP",
        'updated_at' => "DATETIME NULL",
    ];
    foreach ($cols as $col => $def) ua_core_add_col($conn, 'subscribers', $col, $def);
}

function ua_core_ensure_finance_schema(mysqli $conn): void
{
    // These tables are application prerequisites, not page-local migrations.
    // Creating them here guarantees that a fresh installer cannot open the
    // dashboard before the billing and accounting schema exists.
    @$conn->query("CREATE TABLE IF NOT EXISTS `invoices` (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `subscriber_id` INT NULL,
        `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        `status` VARCHAR(20) NOT NULL DEFAULT 'غير مدفوع',
        `cycle_month` VARCHAR(7) NULL,
        `invoice_type` VARCHAR(30) NOT NULL DEFAULT 'BASE',
        `notes` TEXT NULL,
        `paid_by_type` VARCHAR(20) NULL,
        `paid_by_id` INT NULL,
        `paid_at` DATETIME NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `addon_size` VARCHAR(20) NULL,
        `paid_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `remaining_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `payment_status` VARCHAR(20) NOT NULL DEFAULT 'unpaid',
        `updated_at` DATETIME NULL,
        `cycle_label` VARCHAR(80) NULL,
        `cycle_start_date` DATE NULL,
        `cycle_end_date` DATE NULL,
        `due_date` DATE NULL,
        `credit_applied` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
        `generation_key` VARCHAR(191) NULL,
        `cycle_type` VARCHAR(20) NOT NULL DEFAULT 'AUTOMATIC',
        `exceptional_cycle_id` INT NULL,
        UNIQUE KEY `uq_invoice_generation_key` (`generation_key`),
        KEY `idx_inv_sub` (`subscriber_id`),
        KEY `idx_inv_status` (`status`),
        KEY `idx_inv_cycle` (`cycle_month`),
        KEY `idx_invoices_created_at` (`created_at`),
        KEY `idx_invoices_exceptional_cycle` (`exceptional_cycle_id`),
        KEY `idx_invoices_paid_actor` (`paid_by_type`,`paid_by_id`),
        CONSTRAINT `fk_invoices_subscriber`
          FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`id`)
          ON DELETE SET NULL ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @$conn->query("CREATE TABLE IF NOT EXISTS `billing_exceptional_cycles` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `label` VARCHAR(120) NOT NULL,
        `request_token` VARCHAR(64) NOT NULL,
        `status` VARCHAR(20) NOT NULL DEFAULT 'creating',
        `start_date` DATE NOT NULL,
        `due_date` DATE NOT NULL,
        `grace_days` INT NOT NULL DEFAULT 0,
        `limit_after_days` INT NOT NULL DEFAULT 0,
        `cut_after_days` INT NOT NULL DEFAULT 0,
        `reminder_offsets` VARCHAR(255) NULL,
        `reminders_enabled` TINYINT(1) NOT NULL DEFAULT 1,
        `limit_enabled` TINYINT(1) NOT NULL DEFAULT 0,
        `cut_enabled` TINYINT(1) NOT NULL DEFAULT 0,
        `subscriber_count` INT NOT NULL DEFAULT 0,
        `created_invoices` INT NOT NULL DEFAULT 0,
        `covered_by_credit` INT NOT NULL DEFAULT 0,
        `pricing_missing` INT NOT NULL DEFAULT 0,
        `skipped` INT NOT NULL DEFAULT 0,
        `created_by` INT NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `completed_at` DATETIME NULL,
        UNIQUE KEY `uq_exceptional_cycle_token` (`request_token`),
        KEY `idx_exceptional_cycle_created` (`created_at`),
        KEY `idx_exceptional_cycle_status` (`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @$conn->query("CREATE TABLE IF NOT EXISTS `payments` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `invoice_id` INT NULL,
        `subscriber_id` INT NULL,
        `amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
        `method` VARCHAR(50) NULL,
        `ref` VARCHAR(120) NULL,
        `reference` VARCHAR(120) NULL,
        `note` VARCHAR(255) NULL,
        `created_by` VARCHAR(100) NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `paid_at` DATETIME NULL,
        INDEX (`invoice_id`), INDEX (`subscriber_id`), INDEX (`created_at`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @$conn->query("CREATE TABLE IF NOT EXISTS `expenses` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `title` VARCHAR(120) NOT NULL,
        `category` VARCHAR(60) NULL,
        `amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
        `spent_at` DATE NULL,
        `expense_date` DATE NULL,
        `note` VARCHAR(255) NULL,
        `created_by` VARCHAR(100) NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX (`spent_at`), INDEX (`category`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @$conn->query("CREATE TABLE IF NOT EXISTS `month_closings` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `year` INT NOT NULL,
        `month` INT NOT NULL,
        `closed_by` VARCHAR(100) NULL,
        `closed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `archived_at` DATETIME NULL,
        `totals_json` LONGTEXT NULL,
        UNIQUE KEY (`year`,`month`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    // Repair older partial schemas as well as brand-new databases.
    foreach ([
        'paid_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
        'remaining_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0.00",
        'payment_status' => "VARCHAR(20) NOT NULL DEFAULT 'unpaid'",
        'updated_at' => "DATETIME NULL",
        'cycle_label' => "VARCHAR(80) NULL",
        'cycle_start_date' => "DATE NULL",
        'cycle_end_date' => "DATE NULL",
        'due_date' => "DATE NULL",
        'credit_applied' => "DECIMAL(12,2) NOT NULL DEFAULT 0.00",
        'generation_key' => "VARCHAR(191) NULL",
        'cycle_type' => "VARCHAR(20) NOT NULL DEFAULT 'AUTOMATIC'",
        'exceptional_cycle_id' => "INT NULL",
    ] as $col => $def) ua_core_add_col($conn, 'invoices', $col, $def);
}

function ua_core_ensure_mikrotik_settings(mysqli $conn): void
{
    @$conn->query("CREATE TABLE IF NOT EXISTS `mikrotik_settings` (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `host` VARCHAR(255) NOT NULL DEFAULT '',
        `username` VARCHAR(100) NOT NULL DEFAULT '',
        `password` VARCHAR(255) NOT NULL DEFAULT '',
        `api_port` INT NOT NULL DEFAULT 8728,
        `queue_parent` VARCHAR(120) NULL,
        `cut_speed` VARCHAR(60) NOT NULL DEFAULT '0/0',
        `limit_speed` VARCHAR(60) NOT NULL DEFAULT '512k/512k',
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    foreach ([
        'host' => "VARCHAR(255) NOT NULL DEFAULT ''",
        'username' => "VARCHAR(100) NOT NULL DEFAULT ''",
        'password' => "VARCHAR(255) NOT NULL DEFAULT ''",
        'api_port' => "INT NOT NULL DEFAULT 8728",
        'queue_parent' => "VARCHAR(120) NULL",
        'cut_speed' => "VARCHAR(60) NOT NULL DEFAULT '0/0'",
        'limit_speed' => "VARCHAR(60) NOT NULL DEFAULT '512k/512k'",
        'created_at' => "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP",
        'updated_at' => "DATETIME NULL",
    ] as $col => $def) ua_core_add_col($conn, 'mikrotik_settings', $col, $def);

    $has = @$conn->query("SELECT id FROM mikrotik_settings LIMIT 1");
    if ($has && $has->num_rows > 0) return;

    if (ua_core_table_exists($conn, 'routers')) {
        $r = @$conn->query("SELECT host,username,password,api_port FROM routers WHERE is_enabled=1 ORDER BY FIELD(role,'pppoe','uplink','monitor','other'), id ASC LIMIT 1");
        if ($r && ($row = $r->fetch_assoc())) {
            $host = $conn->real_escape_string((string)($row['host'] ?? ''));
            $user = $conn->real_escape_string((string)($row['username'] ?? ''));
            $pass = $conn->real_escape_string((string)($row['password'] ?? ''));
            $port = (int)($row['api_port'] ?? 8728);
            if ($port <= 0) $port = 8728;
            @$conn->query("INSERT INTO mikrotik_settings (id,host,username,password,api_port,cut_speed,limit_speed) VALUES (1,'{$host}','{$user}','{$pass}',{$port},'0/0','512k/512k')");
        }
    }
}

function ua_core_ensure_airmax_devices(mysqli $conn): void
{
    @$conn->query("CREATE TABLE IF NOT EXISTS `airmax_devices` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(120) NOT NULL,
        `host` VARCHAR(255) NOT NULL,
        `protocol` VARCHAR(10) NOT NULL DEFAULT 'https',
        `port` INT NOT NULL DEFAULT 443,
        `username` VARCHAR(100) NULL,
        `password` VARCHAR(255) NULL,
        `notes` TEXT NULL,
        `sort_order` INT NOT NULL DEFAULT 0,
        `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
        `device_type` VARCHAR(20) NOT NULL DEFAULT 'access',
        `preferred_vendor` VARCHAR(40) NULL DEFAULT 'auto',
        `snmp_community` VARCHAR(80) NULL DEFAULT 'public',
        `detected_protocol` VARCHAR(10) NULL,
        `detected_port` INT NULL,
        `detected_vendor` VARCHAR(50) NULL,
        `detected_os` VARCHAR(50) NULL,
        `detected_mode` VARCHAR(50) NULL,
        `detected_route` TEXT NULL,
        `login_path` VARCHAR(255) NULL,
        `status_path` VARCHAR(255) NULL,
        `bridge_path` VARCHAR(255) NULL,
        `sector_id` INT NULL,
        `parent_device_id` INT NULL,
        `client_count_method` VARCHAR(100) NULL,
        `probe_status` VARCHAR(50) NULL,
        `probe_progress` VARCHAR(10) NULL,
        `probe_message` TEXT NULL,
        `probe_last_error` TEXT NULL,
        `probe_meta_json` LONGTEXT NULL,
        `probe_raw_capture_json` LONGTEXT NULL,
        `probe_locked` TINYINT(1) NOT NULL DEFAULT 0,
        `probe_last_success_at` DATETIME NULL,
        `probe_last_error_at` DATETIME NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX (`host`),
        INDEX (`is_enabled`),
        INDEX (`device_type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $cols = [
        'name' => "VARCHAR(120) NOT NULL DEFAULT ''",
        'host' => "VARCHAR(255) NOT NULL DEFAULT ''",
        'protocol' => "VARCHAR(10) NOT NULL DEFAULT 'https'",
        'port' => "INT NOT NULL DEFAULT 443",
        'username' => "VARCHAR(100) NULL",
        'password' => "VARCHAR(255) NULL",
        'notes' => "TEXT NULL",
        'sort_order' => "INT NOT NULL DEFAULT 0",
        'is_enabled' => "TINYINT(1) NOT NULL DEFAULT 1",
        'device_type' => "VARCHAR(20) NOT NULL DEFAULT 'access'",
        'preferred_vendor' => "VARCHAR(40) NULL DEFAULT 'auto'",
        'snmp_community' => "VARCHAR(80) NULL DEFAULT 'public'",
        'detected_protocol' => "VARCHAR(10) NULL",
        'detected_port' => "INT NULL",
        'detected_vendor' => "VARCHAR(50) NULL",
        'detected_os' => "VARCHAR(50) NULL",
        'detected_mode' => "VARCHAR(50) NULL",
        'detected_route' => "TEXT NULL",
        'login_path' => "VARCHAR(255) NULL",
        'status_path' => "VARCHAR(255) NULL",
        'bridge_path' => "VARCHAR(255) NULL",
        'sector_id' => "INT NULL",
        'parent_device_id' => "INT NULL",
        'client_count_method' => "VARCHAR(100) NULL",
        'probe_status' => "VARCHAR(50) NULL",
        'probe_progress' => "VARCHAR(10) NULL",
        'probe_message' => "TEXT NULL",
        'probe_last_error' => "TEXT NULL",
        'probe_meta_json' => "LONGTEXT NULL",
        'probe_raw_capture_json' => "LONGTEXT NULL",
        'probe_locked' => "TINYINT(1) NOT NULL DEFAULT 0",
        'probe_last_success_at' => "DATETIME NULL",
        'probe_last_error_at' => "DATETIME NULL",
        'created_at' => "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP",
    ];
    foreach ($cols as $col => $def) ua_core_add_col($conn, 'airmax_devices', $col, $def);

    @$conn->query("CREATE TABLE IF NOT EXISTS `airmax_live_cache` (
        `device_id` INT NOT NULL PRIMARY KEY,
        `payload` LONGTEXT NULL,
        `updated_at` DATETIME NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

function ua_core_ensure_cashbox_daily(mysqli $conn): void
{
    @$conn->query("CREATE TABLE IF NOT EXISTS `cashbox_daily` (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `day` DATE NOT NULL,
        `opening_balance` DECIMAL(10,2) NOT NULL DEFAULT 0,
        `closing_balance` DECIMAL(10,2) NULL,
        `closed_by` VARCHAR(100) NULL,
        `closed_at` DATETIME NULL,
        `archived_at` DATETIME NULL,
        `totals_json` LONGTEXT NULL,
        `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY (`day`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    ua_core_add_col($conn, 'cashbox_daily', 'archived_at', "DATETIME NULL");
    ua_core_add_col($conn, 'cashbox_daily', 'totals_json', "LONGTEXT NULL");
    ua_core_add_col($conn, 'cashbox_daily', 'created_at', "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP");
}

function ua_core_ensure_message_templates_charset(mysqli $conn): void
{
    if (!ua_core_table_exists($conn, 'message_templates')) return;
    @ $conn->query("ALTER TABLE `message_templates` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    if (ua_core_col_exists($conn, 'message_templates', 'title')) {
        @ $conn->query("ALTER TABLE `message_templates` MODIFY `title` VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL");
    }
    if (ua_core_col_exists($conn, 'message_templates', 'body')) {
        @ $conn->query("ALTER TABLE `message_templates` MODIFY `body` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL");
    }
}

