<?php

namespace App\Model\Facade;

use App\Model\Database\KufrikConnectionFactory;
use Nette\Database\Connection;
use Nette\Http\FileUpload;
use Nette\Security\Passwords;
use Nette\Utils\FileSystem;
use Nette\Utils\Json;
use Nette\Utils\Validators;

class DatabazeFacade
{
    private const MASTER_DB = 'kufrik_master';
    private const SYSTEM_DBS = ['master', 'tempdb', 'model', 'msdb', self::MASTER_DB];

    /** Extended property na datové DB — jen takto označené se vypisují v Admin/Databáze. */
    private const APP_PROPERTY = 'kufrik_app';
    private const APP_PROPERTY_VALUE = '1';

    public function __construct(
        private Connection $masterDb,
        private string $sdfStorageDir,
        private Passwords $passwords,
        private KufrikConnectionFactory $kufrikConnectionFactory,
        private string $tenantServer,
        private string $tenantDatabase
    ) {
    }

    /**
     * Seznam datových databází Kufříku (označené extended property kufrik_app=1).
     *
     * @return \stdClass[]
     */
    public function getDatabases(): array
    {
        $rows = $this->masterDb->fetchAll(
            "SELECT name AS [DATABASE]
             FROM sys.databases
             WHERE state = 0
               AND name NOT IN (?, ?, ?, ?, ?)
             ORDER BY name",
            ...self::SYSTEM_DBS
        );

        $stats = [];
        foreach ($this->masterDb->fetchAll(
            "SELECT [DATABASE], [SERVER],
                    COUNT(*) AS USERS,
                    COUNT(DISTINCT FIRMA_ID) AS FIRMY
             FROM dbo.LOGIN
             WHERE [DATABASE] <> ?
             GROUP BY [DATABASE], [SERVER]",
            self::MASTER_DB
        ) as $row) {
            $stats[(string) $row->DATABASE] = $row;
        }

        $result = [];
        foreach ($rows as $row) {
            $name = (string) $row->DATABASE;
            if (!$this->isKufrikDatabase($name)) {
                continue;
            }
            $stat = $stats[$name] ?? null;
            $result[] = (object) [
                'DATABASE' => $name,
                'SERVER' => $stat->SERVER ?? $this->tenantServer,
                'USERS' => (int) ($stat->USERS ?? 0),
                'FIRMY' => (int) ($stat->FIRMY ?? 0),
            ];
        }

        return $result;
    }

    /**
     * @return array<string, string> database => database
     */
    public function getDatabaseSelectItems(): array
    {
        $items = [];
        foreach ($this->getDatabases() as $db) {
            $items[$db->DATABASE] = $db->DATABASE;
        }
        if ($items === [] && $this->tenantDatabase !== '') {
            $items[$this->tenantDatabase] = $this->tenantDatabase;
        }

        return $items;
    }

    /**
     * Firmy z vybrané datové DB.
     *
     * @return \stdClass[]
     */
    public function getFirmy(string $database, ?string $server = null): array
    {
        $server ??= $this->tenantServer;
        $this->assertTenantDatabase($database);

        $explorer = $this->kufrikConnectionFactory->createTenantExplorer($server, $database, 1);
        $userCounts = [];
        foreach ($this->masterDb->fetchAll(
            'SELECT FIRMA_ID, COUNT(*) AS C
             FROM dbo.LOGIN
             WHERE [DATABASE] = ? AND [SERVER] = ?
             GROUP BY FIRMA_ID',
            $database,
            $server
        ) as $row) {
            $userCounts[(int) $row->FIRMA_ID] = (int) $row->C;
        }

        $result = [];
        foreach ($explorer->table('FIRMA')->order('NAZEV ASC') as $firma) {
            $firmaId = (int) $firma->ID;
            $loginId = $this->findLoginIdForFirma($database, $firmaId, $server);
            $item = (object) [
                'ID' => $firmaId,
                'NAZEV' => (string) $firma->NAZEV,
                'IC' => $firma->IC,
                'DIC' => $firma->DIC,
                'EMAIL' => $firma->EMAIL,
                'TELEFON' => $firma->TELEFON,
                'MESTO' => $firma->MESTO,
                'DATABASE' => $database,
                'SERVER' => $server,
                'USERS' => $userCounts[$firmaId] ?? 0,
                'LOGIN_ID' => $loginId,
            ];
            if ($loginId) {
                $meta = $this->readSdfMeta($loginId);
                $item->SDF_SOUBOR = $meta['originalName'] ?? null;
                $item->SDF_NAHRANO = isset($meta['uploadedAt']) ? new \DateTimeImmutable($meta['uploadedAt']) : null;
                $item->SDF_IMPORTOVANO = isset($meta['importedAt']) ? new \DateTimeImmutable($meta['importedAt']) : null;
            } else {
                $item->SDF_SOUBOR = null;
                $item->SDF_NAHRANO = null;
                $item->SDF_IMPORTOVANO = null;
            }
            $result[] = $item;
        }

        return $result;
    }

    /**
     * @return array<int, string>
     */
    public function getFirmaSelectItems(string $database, ?string $server = null): array
    {
        $items = [];
        foreach ($this->getFirmy($database, $server) as $firma) {
            $items[(int) $firma->ID] = $firma->NAZEV . ' (#' . $firma->ID . ')';
        }

        return $items;
    }

    /**
     * Uživatelé z master tabulky LOGIN.
     *
     * @return \stdClass[]
     */
    public function getUzivatele(): array
    {
        $rows = $this->masterDb->fetchAll(
            'SELECT ID, EMAIL, POSLEDNI_PRIHLASENI, AKTIVNI, [DATABASE], [SERVER], FIRMA_ID, IS_ADMIN
             FROM dbo.LOGIN
             ORDER BY [DATABASE], FIRMA_ID, EMAIL'
        );

        $firmaNamesByDb = [];
        $result = [];
        foreach ($rows as $row) {
            $item = $this->hydrateLogin($row);
            $db = (string) $item->DATABASE;
            if (!isset($firmaNamesByDb[$db])) {
                $firmaNamesByDb[$db] = $this->loadFirmaNames($db, (string) $item->SERVER);
            }
            $item->FIRMA_NAZEV = $firmaNamesByDb[$db][(int) $item->FIRMA_ID] ?? null;
            $result[] = $item;
        }

        return $result;
    }

    public function getById(int $id): ?\stdClass
    {
        $row = $this->masterDb->fetch(
            'SELECT ID, EMAIL, POSLEDNI_PRIHLASENI, AKTIVNI, [DATABASE], [SERVER], FIRMA_ID, IS_ADMIN
             FROM dbo.LOGIN
             WHERE ID = ?',
            $id
        );
        if (!$row) {
            return null;
        }

        $item = $this->hydrateLogin($row);
        $names = $this->loadFirmaNames((string) $item->DATABASE, (string) $item->SERVER);
        $item->FIRMA_NAZEV = $names[(int) $item->FIRMA_ID] ?? null;

        return $item;
    }

    public function createFirma(string $firmaNazev, string $database, ?string $server = null): int
    {
        $server ??= $this->tenantServer;
        $firmaNazev = trim($firmaNazev);
        $this->assertTenantDatabase($database);

        if ($firmaNazev === '') {
            throw new \InvalidArgumentException('Zadejte název firmy.');
        }

        $explorer = $this->kufrikConnectionFactory->createTenantExplorer($server, $database, 1);
        $firmaRow = $explorer->table('FIRMA')->insert([
            'NAZEV' => mb_substr($firmaNazev, 0, 255),
            'PLATCE_DPH' => 0,
        ]);

        return (int) $firmaRow->ID;
    }

    /**
     * Založí uživatele (vždy role User, nikdy admin).
     */
    public function createUzivatel(
        string $email,
        string $password,
        string $database,
        int $firmaId,
        ?string $server = null
    ): int {
        $server ??= $this->tenantServer;
        $email = mb_strtolower(trim($email));
        $this->assertTenantDatabase($database);

        if (!Validators::isEmail($email)) {
            throw new \InvalidArgumentException('Zadejte platný e-mail uživatele.');
        }
        if (mb_strlen($password) < 8) {
            throw new \InvalidArgumentException('Heslo musí mít alespoň 8 znaků.');
        }
        if ($this->emailExists($email)) {
            throw new \InvalidArgumentException('Uživatel s tímto e-mailem už existuje.');
        }
        if (!$this->firmaExists($database, $firmaId, $server)) {
            throw new \InvalidArgumentException('Vybraná firma v databázi neexistuje.');
        }

        $this->masterDb->query(
            'INSERT INTO dbo.LOGIN (EMAIL, HESLO_HASH, AKTIVNI, [DATABASE], [SERVER], FIRMA_ID, IS_ADMIN)
             VALUES (?, ?, 1, ?, ?, ?, 0)',
            $email,
            $this->passwords->hash($password),
            $database,
            $server,
            $firmaId
        );

        $loginId = (int) $this->masterDb->fetchField('SELECT ID FROM dbo.LOGIN WHERE EMAIL = ?', $email);
        if (!$loginId) {
            throw new \RuntimeException('Uživatele se nepodařilo založit.');
        }

        return $loginId;
    }

    public function updateUserCredentials(int $loginId, string $email, ?string $password = null): void
    {
        $login = $this->getById($loginId);
        if (!$login) {
            throw new \InvalidArgumentException('Uživatel nebyl nalezen.');
        }

        $email = mb_strtolower(trim($email));
        if (!Validators::isEmail($email)) {
            throw new \InvalidArgumentException('Zadejte platný e-mail uživatele.');
        }
        if ($this->emailExists($email, $loginId)) {
            throw new \InvalidArgumentException('Uživatel s tímto e-mailem už existuje.');
        }

        if ($password !== null && $password !== '') {
            if (mb_strlen($password) < 8) {
                throw new \InvalidArgumentException('Heslo musí mít alespoň 8 znaků.');
            }
            $this->masterDb->query(
                'UPDATE dbo.LOGIN SET EMAIL = ?, HESLO_HASH = ? WHERE ID = ?',
                $email,
                $this->passwords->hash($password),
                $loginId
            );
            return;
        }

        $this->masterDb->query(
            'UPDATE dbo.LOGIN SET EMAIL = ? WHERE ID = ?',
            $email,
            $loginId
        );
    }

    /**
     * Změna přihlašovacího jména (e-mailu) přihlášeného uživatele.
     */
    public function changeOwnEmail(int $loginId, string $email): string
    {
        $this->updateUserCredentials($loginId, $email, null);

        return mb_strtolower(trim($email));
    }

    /**
     * Změna hesla. Původní heslo musí sedět s uloženým hashem.
     */
    public function changeOwnPassword(int $loginId, string $currentPassword, string $newPassword): void
    {
        $row = $this->masterDb->fetch(
            'SELECT HESLO_HASH FROM dbo.LOGIN WHERE ID = ? AND AKTIVNI = 1',
            $loginId
        );
        $hash = $row !== null ? (string) ($row->HESLO_HASH ?? '') : '';
        if ($hash === '' || !$this->passwords->verify($currentPassword, $hash)) {
            throw new \InvalidArgumentException('Původní heslo není správné.');
        }
        if (mb_strlen($newPassword) < 8) {
            throw new \InvalidArgumentException('Heslo musí mít alespoň 8 znaků.');
        }

        $this->masterDb->query(
            'UPDATE dbo.LOGIN SET HESLO_HASH = ? WHERE ID = ?',
            $this->passwords->hash($newPassword),
            $loginId
        );
    }

    public function findLoginIdForFirma(string $database, int $firmaId, ?string $server = null): ?int
    {
        $server ??= $this->tenantServer;
        $id = $this->masterDb->fetchField(
            'SELECT TOP 1 ID FROM dbo.LOGIN
             WHERE [DATABASE] = ? AND [SERVER] = ? AND FIRMA_ID = ?
             ORDER BY IS_ADMIN DESC, ID ASC',
            $database,
            $server,
            $firmaId
        );

        return $id ? (int) $id : null;
    }

    public function getSdfFilePath(int $loginId): ?string
    {
        // JSON export funguje i na Linuxu; .sdf vyžaduje Windows + SQL Compact.
        $export = $this->sdfExportPath($loginId);
        if (is_file($export)) {
            return $export;
        }

        $path = $this->sdfPath($loginId);
        return is_file($path) ? $path : null;
    }

    /**
     * @param array<string, mixed> $stats
     */
    public function markSdfImported(int $loginId, array $stats): void
    {
        $meta = $this->readSdfMeta($loginId);
        $meta['importedAt'] = (new \DateTimeImmutable())->format('c');
        $meta['importStats'] = $stats;
        FileSystem::createDir($this->sdfStorageDir);
        FileSystem::write($this->sdfMetaPath($loginId), Json::encode($meta, pretty: true));
    }

    public function saveSdf(int $loginId, FileUpload $file): void
    {
        $exists = $this->masterDb->fetchField('SELECT ID FROM dbo.LOGIN WHERE ID = ?', $loginId);
        if (!$exists) {
            throw new \InvalidArgumentException('Uživatel / login pro import neexistuje.');
        }

        if (!$file->isOk()) {
            throw new \RuntimeException('Soubor se nepodařilo nahrát. Zkontrolujte velikost (limit PHP/upload).');
        }

        $extension = strtolower(pathinfo($file->getUntrustedName(), PATHINFO_EXTENSION));
        if (!in_array($extension, ['sdf', 'json'], true)) {
            throw new \InvalidArgumentException('Soubor musí mít příponu .sdf nebo .json (export).');
        }

        FileSystem::createDir($this->sdfStorageDir);

        $target = $extension === 'json'
            ? $this->sdfExportPath($loginId)
            : $this->sdfPath($loginId);

        // Nový upload přepíše druhý formát, ať import nebere starý soubor.
        $other = $extension === 'json'
            ? $this->sdfPath($loginId)
            : $this->sdfExportPath($loginId);
        if (is_file($other)) {
            FileSystem::delete($other);
        }

        $file->move($target);
        $this->clearSdfImportMeta($loginId);

        $meta = [
            'originalName' => $this->safeOriginalName($file->getUntrustedName()),
            'uploadedAt' => (new \DateTimeImmutable())->format('c'),
            'size' => $file->getSize(),
            'loginId' => $loginId,
            'format' => $extension,
        ];
        FileSystem::write($this->sdfMetaPath($loginId), Json::encode($meta, pretty: true));
    }

    public function getDefaultTenantDatabase(): string
    {
        return $this->tenantDatabase;
    }

    public function getDefaultTenantServer(): string
    {
        return $this->tenantServer;
    }

    private function assertTenantDatabase(string $database): void
    {
        if ($database === '' || in_array($database, self::SYSTEM_DBS, true)) {
            throw new \InvalidArgumentException('Neplatná datová databáze.');
        }
        if (!$this->isKufrikDatabase($database)) {
            throw new \InvalidArgumentException(
                "Databáze „{$database}“ není označená jako Kufřík (chybí metadata kufrik_app)."
            );
        }
    }

    /**
     * Databáze patří do Kufříku, pokud má extended property kufrik_app = 1.
     */
    private function isKufrikDatabase(string $database): bool
    {
        if (!preg_match('/^[A-Za-z0-9_]+$/', $database)) {
            return false;
        }

        try {
            // sqlsrv + Nette fetchField nefunguje spolehlivě (column '0') → pojmenovaný sloupec
            $row = $this->masterDb->fetch(
                "SELECT TOP 1 CONVERT(nvarchar(100), value) AS prop_value
                 FROM [{$database}].sys.extended_properties
                 WHERE class = 0 AND major_id = 0 AND minor_id = 0 AND name = ?",
                self::APP_PROPERTY
            );

            return $row !== null && (string) $row->prop_value === self::APP_PROPERTY_VALUE;
        } catch (\Throwable) {
            return false;
        }
    }

    private function firmaExists(string $database, int $firmaId, string $server): bool
    {
        $explorer = $this->kufrikConnectionFactory->createTenantExplorer($server, $database, 1);
        return (bool) $explorer->table('FIRMA')->get($firmaId);
    }

    private function emailExists(string $email, ?int $exceptId = null): bool
    {
        if ($exceptId) {
            return (bool) $this->masterDb->fetchField(
                'SELECT ID FROM dbo.LOGIN WHERE EMAIL = ? AND ID <> ?',
                $email,
                $exceptId
            );
        }

        return (bool) $this->masterDb->fetchField(
            'SELECT ID FROM dbo.LOGIN WHERE EMAIL = ?',
            $email
        );
    }

    /**
     * @return array<int, string>
     */
    private function loadFirmaNames(string $database, string $server): array
    {
        try {
            $this->assertTenantDatabase($database);
            $explorer = $this->kufrikConnectionFactory->createTenantExplorer($server, $database, 1);
            $names = [];
            foreach ($explorer->table('FIRMA')->select('ID, NAZEV') as $firma) {
                $names[(int) $firma->ID] = (string) $firma->NAZEV;
            }
            return $names;
        } catch (\Throwable) {
            return [];
        }
    }

    /**
     * @return array{originalName?: string, uploadedAt?: string, size?: int, format?: string}|array{}
     */
    private function readSdfMeta(int $loginId): array
    {
        $source = $this->getSdfFilePath($loginId);
        if ($source === null) {
            return [];
        }

        $metaFile = $this->sdfMetaPath($loginId);
        $meta = [];
        if (is_file($metaFile)) {
            try {
                $decoded = Json::decode(FileSystem::read($metaFile), forceArrays: true);
                $meta = is_array($decoded) ? $decoded : [];
            } catch (\Throwable) {
                $meta = [];
            }
        }

        $meta['size'] = filesize($source) ?: ($meta['size'] ?? null);
        $meta['uploadedAt'] ??= date('c', filemtime($source) ?: time());
        $meta['originalName'] ??= basename($source);
        $meta['format'] ??= strtolower(pathinfo($source, PATHINFO_EXTENSION));

        return $meta;
    }

    private function hydrateLogin(object $row): \stdClass
    {
        $item = (object) (array) $row;
        $meta = $this->readSdfMeta((int) $item->ID);
        $item->SDF_SOUBOR = $meta['originalName'] ?? null;
        $item->SDF_NAHRANO = isset($meta['uploadedAt']) ? new \DateTimeImmutable($meta['uploadedAt']) : null;
        $item->SDF_VELIKOST = $meta['size'] ?? null;
        $item->SDF_VELIKOST_TEXT = isset($meta['size']) ? $this->formatBytes((int) $meta['size']) : null;
        $item->SDF_IMPORTOVANO = isset($meta['importedAt']) ? new \DateTimeImmutable($meta['importedAt']) : null;
        return $item;
    }

    private function clearSdfImportMeta(int $loginId): void
    {
        $metaFile = $this->sdfMetaPath($loginId);
        if (!is_file($metaFile)) {
            return;
        }
        try {
            $decoded = Json::decode(FileSystem::read($metaFile), forceArrays: true);
            $meta = is_array($decoded) ? $decoded : [];
        } catch (\Throwable) {
            return;
        }
        unset($meta['importedAt'], $meta['importStats']);
        FileSystem::write($metaFile, Json::encode($meta, pretty: true));
    }

    private function sdfPath(int $loginId): string
    {
        return $this->sdfStorageDir . DIRECTORY_SEPARATOR . $loginId . '.sdf';
    }

    private function sdfExportPath(int $loginId): string
    {
        return $this->sdfStorageDir . DIRECTORY_SEPARATOR . $loginId . '.export.json';
    }

    private function sdfMetaPath(int $loginId): string
    {
        return $this->sdfStorageDir . DIRECTORY_SEPARATOR . $loginId . '.json';
    }

    private function formatBytes(int $bytes): string
    {
        if ($bytes < 1024) {
            return $bytes . ' B';
        }
        if ($bytes < 1024 * 1024) {
            return number_format($bytes / 1024, 1, ',', ' ') . ' KB';
        }

        return number_format($bytes / (1024 * 1024), 1, ',', ' ') . ' MB';
    }

    private function safeOriginalName(string $name): string
    {
        $name = str_replace(["\0", "\r", "\n"], '', $name);
        $name = basename($name);

        return $name !== '' ? mb_substr($name, 0, 255) : 'databaze.sdf';
    }
}