<?php
declare(strict_types=1);

if (!function_exists('revenda_column_exists')) {
    function revenda_column_exists(PDO $db, string $table, string $column): bool
    {
        try {
            $st = $db->query('PRAGMA table_info(' . preg_replace('/[^A-Za-z0-9_]/', '', $table) . ')');
            if (!$st) return false;
            foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $col) {
                if (($col['name'] ?? '') === $column) return true;
            }
        } catch (Throwable $e) {}
        return false;
    }
}

if (!function_exists('revenda_ensure_schema')) {
    function revenda_ensure_schema(PDO $db): void
    {
        $db->exec("CREATE TABLE IF NOT EXISTS revendedores (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            password TEXT NOT NULL,
            email TEXT NOT NULL DEFAULT '',
            status TEXT DEFAULT 'ativo',
            nome TEXT DEFAULT '',
            mac_limit INTEGER DEFAULT 0,
            vencimento TEXT DEFAULT '',
            creditos INTEGER DEFAULT 0,
            whatsapp TEXT DEFAULT '',
            created_at INTEGER DEFAULT (strftime('%s','now')),
            updated_at INTEGER DEFAULT (strftime('%s','now'))
        )");

        foreach ([
            'email' => "TEXT NOT NULL DEFAULT ''",
            'status' => "TEXT DEFAULT 'ativo'",
            'nome' => "TEXT DEFAULT ''",
            'mac_limit' => "INTEGER DEFAULT 0",
            'vencimento' => "TEXT DEFAULT ''",
            'creditos' => "INTEGER DEFAULT 0",
            'whatsapp' => "TEXT DEFAULT ''",
            'logo_path' => "TEXT DEFAULT ''",
            'logo_updated_at' => "INTEGER DEFAULT 0",
            'created_at' => "INTEGER DEFAULT (strftime('%s','now'))",
            'updated_at' => "INTEGER DEFAULT (strftime('%s','now'))",
        ] as $col => $type) {
            if (!revenda_column_exists($db, 'revendedores', $col)) {
                $db->exec("ALTER TABLE revendedores ADD COLUMN {$col} {$type}");
            }
        }

        $db->exec("CREATE INDEX IF NOT EXISTS idx_revendedores_username ON revendedores(username COLLATE NOCASE)");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_revendedores_status ON revendedores(status)");

        $db->exec("CREATE TABLE IF NOT EXISTS revendedor_clientes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            revendedor_id INTEGER NOT NULL,
            mac TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
            UNIQUE(revendedor_id, mac)
        )");
        if (!revenda_column_exists($db, 'revendedor_clientes', 'created_at')) {
            $db->exec("ALTER TABLE revendedor_clientes ADD COLUMN created_at INTEGER NOT NULL DEFAULT 0");
        }
        $db->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_revendedor_clientes_unique ON revendedor_clientes(revendedor_id, mac)");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_revendedor_clientes_rev ON revendedor_clientes(revendedor_id)");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_revendedor_clientes_mac ON revendedor_clientes(mac)");

        try {
            $oldSql = (string)($db->query("SELECT sql FROM sqlite_master WHERE type='table' AND name='revendedor_dns'")->fetchColumn() ?: '');
            if ($oldSql !== '' && stripos($oldSql, 'dns_id INTEGER NOT NULL') !== false) {
                $legacy = 'revendedor_dns_legacy_' . time();
                $db->exec("ALTER TABLE revendedor_dns RENAME TO {$legacy}");
                $db->exec("CREATE TABLE revendedor_dns (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    revendedor_id INTEGER NOT NULL,
                    nome TEXT NOT NULL DEFAULT '',
                    url TEXT NOT NULL DEFAULT '',
                    created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
                    updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
                    UNIQUE(revendedor_id, nome)
                )");
                $rows = $db->query("SELECT * FROM {$legacy}")->fetchAll(PDO::FETCH_ASSOC);
                $insDns = $db->prepare("INSERT OR IGNORE INTO revendedor_dns (revendedor_id, nome, url, created_at, updated_at) VALUES (?, ?, ?, strftime('%s','now'), strftime('%s','now'))");
                foreach ($rows as $legacyRow) {
                    $legacyUrl = trim((string)($legacyRow['url'] ?? $legacyRow['dns_primario'] ?? $legacyRow['dns_secundario'] ?? ''));
                    if ($legacyUrl === '') continue;
                    $legacyName = trim((string)($legacyRow['nome'] ?? ''));
                    if ($legacyName === '') $legacyName = 'DNS ' . (string)($legacyRow['id'] ?? uniqid());
                    $insDns->execute([(int)($legacyRow['revendedor_id'] ?? 0), $legacyName, $legacyUrl]);
                }
            }
        } catch (Throwable $e) {}

        $db->exec("CREATE TABLE IF NOT EXISTS revendedor_dns (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            revendedor_id INTEGER NOT NULL,
            nome TEXT NOT NULL,
            url TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
            updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
            UNIQUE(revendedor_id, nome)
        )");
        foreach ([
            'nome' => "TEXT NOT NULL DEFAULT ''",
            'url' => "TEXT NOT NULL DEFAULT ''",
            'created_at' => "INTEGER NOT NULL DEFAULT 0",
            'updated_at' => "INTEGER NOT NULL DEFAULT 0",
        ] as $col => $type) {
            if (!revenda_column_exists($db, 'revendedor_dns', $col)) {
                $db->exec("ALTER TABLE revendedor_dns ADD COLUMN {$col} {$type}");
            }
        }
        $db->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_revendedor_dns_unique ON revendedor_dns(revendedor_id, nome)");
        $db->exec("CREATE INDEX IF NOT EXISTS idx_revendedor_dns_rev ON revendedor_dns(revendedor_id)");

        foreach ([
            'nome_cliente' => 'TEXT',
            'whatsapp' => 'TEXT',
            'usuario_id' => 'INTEGER DEFAULT 0',
            'login_mode' => "TEXT DEFAULT 'normal'",
            'activation_code' => "TEXT DEFAULT ''",
        ] as $col => $type) {
            if (!revenda_column_exists($db, 'usuarios', $col)) {
                $db->exec("ALTER TABLE usuarios ADD COLUMN {$col} {$type}");
            }
        }
        $db->exec("CREATE INDEX IF NOT EXISTS idx_usuarios_revendedor ON usuarios(usuario_id)");

        $db->exec("CREATE TABLE IF NOT EXISTS online_status (
            mac TEXT PRIMARY KEY,
            is_online INTEGER DEFAULT 0,
            usuario_id INTEGER DEFAULT 0,
            content_type TEXT DEFAULT '',
            content_name TEXT DEFAULT '',
            updated_at INTEGER DEFAULT 0,
            ip TEXT DEFAULT '',
            user_agent TEXT DEFAULT '',
            device_type TEXT DEFAULT ''
        )");
        if (!revenda_column_exists($db, 'online_status', 'usuario_id')) {
            $db->exec("ALTER TABLE online_status ADD COLUMN usuario_id INTEGER DEFAULT 0");
        }
        $db->exec("CREATE INDEX IF NOT EXISTS idx_online_status_owner ON online_status(usuario_id)");

        $db->exec("CREATE TABLE IF NOT EXISTS chatbot_integration_config (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            owner_type TEXT DEFAULT 'admin',
            owner_id INTEGER DEFAULT 1,
            base_url TEXT DEFAULT '',
            api_token TEXT DEFAULT '',
            token_preview TEXT DEFAULT '',
            last_sync_at INTEGER DEFAULT 0,
            last_error TEXT DEFAULT '',
            updated_at INTEGER DEFAULT 0
        )");
        foreach ([
            'owner_type' => "TEXT DEFAULT 'admin'",
            'owner_id' => "INTEGER DEFAULT 1",
            'base_url' => "TEXT DEFAULT ''",
            'api_token' => "TEXT DEFAULT ''",
            'token_preview' => "TEXT DEFAULT ''",
            'last_sync_at' => "INTEGER DEFAULT 0",
            'last_error' => "TEXT DEFAULT ''",
            'updated_at' => "INTEGER DEFAULT 0",
        ] as $col => $type) {
            if (!revenda_column_exists($db, 'chatbot_integration_config', $col)) {
                $db->exec("ALTER TABLE chatbot_integration_config ADD COLUMN {$col} {$type}");
            }
        }
        $db->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_chatbot_config_owner ON chatbot_integration_config(owner_type, owner_id)");

        try {
            $oldSql = (string)($db->query("SELECT sql FROM sqlite_master WHERE type='table' AND name='chatbot_sync_map'")->fetchColumn() ?: '');
            if ($oldSql !== '' && (stripos($oldSql, 'UNIQUE(panel_mac)') !== false || stripos($oldSql, 'panel_mac TEXT NOT NULL UNIQUE') !== false || stripos($oldSql, 'panel_mac TEXT UNIQUE') !== false)) {
                $legacy = 'chatbot_sync_map_legacy_' . time();
                $db->exec("ALTER TABLE chatbot_sync_map RENAME TO {$legacy}");
                $db->exec("CREATE TABLE chatbot_sync_map (id INTEGER PRIMARY KEY AUTOINCREMENT, owner_type TEXT DEFAULT 'admin', owner_id INTEGER DEFAULT 1, panel_mac TEXT DEFAULT '', chatbot_id INTEGER DEFAULT 0, phone TEXT DEFAULT '', updated_at INTEGER DEFAULT 0)");
                $rows = $db->query("SELECT * FROM {$legacy}")->fetchAll(PDO::FETCH_ASSOC) ?: [];
                $ins = $db->prepare("INSERT OR IGNORE INTO chatbot_sync_map (owner_type, owner_id, panel_mac, chatbot_id, phone, updated_at) VALUES ('admin', 1, ?, ?, ?, ?)");
                foreach ($rows as $r) $ins->execute([(string)($r['panel_mac'] ?? ''), (int)($r['chatbot_id'] ?? 0), (string)($r['phone'] ?? ''), (int)($r['updated_at'] ?? time())]);
            }
        } catch (Throwable $e) {}

        $db->exec("CREATE TABLE IF NOT EXISTS chatbot_sync_map (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            owner_type TEXT DEFAULT 'admin',
            owner_id INTEGER DEFAULT 1,
            panel_mac TEXT NOT NULL,
            bot_user_id TEXT DEFAULT '',
            bot_instance_id TEXT DEFAULT '',
            chatbot_id INTEGER DEFAULT 0,
            phone TEXT DEFAULT '',
            updated_at INTEGER DEFAULT 0
        )");
        foreach ([
            'owner_type' => "TEXT DEFAULT 'admin'",
            'owner_id' => "INTEGER DEFAULT 1",
            'panel_mac' => "TEXT DEFAULT ''",
            'bot_user_id' => "TEXT DEFAULT ''",
            'bot_instance_id' => "TEXT DEFAULT ''",
            'chatbot_id' => "INTEGER DEFAULT 0",
            'phone' => "TEXT DEFAULT ''",
            'updated_at' => "INTEGER DEFAULT 0",
        ] as $col => $type) {
            if (!revenda_column_exists($db, 'chatbot_sync_map', $col)) {
                $db->exec("ALTER TABLE chatbot_sync_map ADD COLUMN {$col} {$type}");
            }
        }
        $db->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_chatbot_sync_owner_mac_v2 ON chatbot_sync_map(owner_type, owner_id, panel_mac)");
    }
}

if (!function_exists('revenda_parse_expiry_ts')) {
    function revenda_parse_expiry_ts($value): int
    {
        $raw = trim((string)$value);
        if ($raw === '' || $raw === '0') return 0;
        if (ctype_digit($raw)) return (int)$raw;
        $ts = strtotime($raw);
        return $ts === false ? 0 : (int)$ts;
    }
}

if (!function_exists('revenda_is_expired')) {
    function revenda_is_expired($value): bool
    {
        $ts = revenda_parse_expiry_ts($value);
        return $ts > 0 && $ts < time();
    }
}

if (!function_exists('revenda_count_macs')) {
    function revenda_count_macs(PDO $db, int $revendedorId): int
    {
        $st = $db->prepare('SELECT COUNT(*) FROM usuarios WHERE usuario_id = ?');
        $st->execute([$revendedorId]);
        return (int)$st->fetchColumn();
    }
}

if (!function_exists('revenda_limit_info')) {
    function revenda_limit_info(PDO $db, int $revendedorId): array
    {
        $st = $db->prepare('SELECT id, username, nome, whatsapp, status, mac_limit, vencimento FROM revendedores WHERE id = ? LIMIT 1');
        $st->execute([$revendedorId]);
        $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];
        $limit = max(0, (int)($row['mac_limit'] ?? 0));
        $used = revenda_count_macs($db, $revendedorId);
        return [
            'row' => $row,
            'limit' => $limit,
            'used' => $used,
            'remaining' => $limit > 0 ? max(0, $limit - $used) : null,
            'unlimited' => $limit <= 0,
        ];
    }
}

if (!function_exists('revenda_can_add_mac')) {
    function revenda_can_add_mac(PDO $db, int $revendedorId, int $add = 1): bool
    {
        $info = revenda_limit_info($db, $revendedorId);
        if (!empty($info['unlimited'])) return true;
        return ((int)$info['used'] + max(1, $add)) <= (int)$info['limit'];
    }
}

if (!function_exists('revenda_password_ok')) {
    function revenda_password_ok(string $plain, string $hashOrPlain): bool
    {
        if ($hashOrPlain === '') return false;
        $info = password_get_info($hashOrPlain);
        if (!empty($info['algo']) && (int)$info['algo'] !== 0) return password_verify($plain, $hashOrPlain);
        if (strlen($hashOrPlain) === 32 && ctype_xdigit($hashOrPlain)) return hash_equals(strtolower($hashOrPlain), md5($plain));
        return hash_equals($hashOrPlain, $plain);
    }
}
?>
