<?php
declare(strict_types=1);

if (!function_exists('vm_site_pages_h')) {
    function vm_site_pages_h(?string $value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('vm_site_pages_db')) {
    function vm_site_pages_db(): PDO
    {
        if (function_exists('db')) {
            $pdo = db();
            if ($pdo instanceof PDO) {
                return $pdo;
            }
        }

        if (isset($GLOBALS['pdo']) && $GLOBALS['pdo'] instanceof PDO) {
            return $GLOBALS['pdo'];
        }

        throw new RuntimeException('PDO connection is not available.');
    }
}

if (!function_exists('vm_site_pages_table_exists')) {
    function vm_site_pages_table_exists(PDO $pdo): bool
    {
        static $exists = null;
        if ($exists !== null) {
            return $exists;
        }

        try {
            if (function_exists('db_has_table')) {
                $exists = (bool)db_has_table($pdo, 'site_pages');
                return $exists;
            }
        } catch (Throwable $e) {
        }

        try {
            $st = $pdo->prepare('SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ? LIMIT 1');
            $st->execute(['site_pages']);
            $exists = (bool)$st->fetchColumn();
        } catch (Throwable $e) {
            $exists = false;
        }

        return $exists;
    }
}

if (!function_exists('vm_site_pages_current_lang')) {
    function vm_site_pages_current_lang(): string
    {
        $lang = function_exists('lang') ? (string)lang() : (string)($_SESSION['lang'] ?? 'uk');
        $lang = strtolower(trim($lang));
        if (!in_array($lang, ['uk', 'ru', 'en'], true)) {
            $lang = 'uk';
        }
        return $lang;
    }
}

if (!function_exists('vm_site_pages_slugify')) {
    function vm_site_pages_slugify(string $slug): string
    {
        $slug = strtolower(trim($slug));
        $slug = preg_replace('~[^a-z0-9\-]+~', '-', $slug) ?? '';
        $slug = preg_replace('~-{2,}~', '-', $slug) ?? '';
        $slug = trim($slug, '-');
        return $slug;
    }
}

if (!function_exists('vm_site_pages_pick_localized')) {
    function vm_site_pages_pick_localized(array $row, string $prefix, ?string $lang = null): string
    {
        $lang = $lang ?: vm_site_pages_current_lang();
        $try = [
            $prefix . '_' . $lang,
            $prefix . '_uk',
            $prefix . '_ru',
            $prefix . '_en',
        ];

        foreach ($try as $key) {
            $value = trim((string)($row[$key] ?? ''));
            if ($value !== '') {
                return $value;
            }
        }

        return '';
    }
}

if (!function_exists('vm_site_pages_row_title')) {
    function vm_site_pages_row_title(array $row, ?string $lang = null): string
    {
        return vm_site_pages_pick_localized($row, 'title', $lang);
    }
}

if (!function_exists('vm_site_pages_row_body')) {
    function vm_site_pages_row_body(array $row, ?string $lang = null): string
    {
        return vm_site_pages_pick_localized($row, 'body', $lang);
    }
}

if (!function_exists('vm_site_pages_find_by_slug')) {
    function vm_site_pages_find_by_slug(PDO $pdo, string $slug, bool $onlyActive = true): ?array
    {
        if (!vm_site_pages_table_exists($pdo)) {
            return null;
        }

        $slug = vm_site_pages_slugify($slug);
        if ($slug === '') {
            return null;
        }

        $sql = 'SELECT * FROM site_pages WHERE slug = ?';
        if ($onlyActive) {
            $sql .= ' AND is_active = 1';
        }
        $sql .= ' LIMIT 1';

        $st = $pdo->prepare($sql);
        $st->execute([$slug]);
        $row = $st->fetch(PDO::FETCH_ASSOC);

        return is_array($row) ? $row : null;
    }
}

if (!function_exists('vm_site_pages_find_by_id')) {
    function vm_site_pages_find_by_id(PDO $pdo, int $id): ?array
    {
        if (!vm_site_pages_table_exists($pdo) || $id <= 0) {
            return null;
        }

        $st = $pdo->prepare('SELECT * FROM site_pages WHERE id = ? LIMIT 1');
        $st->execute([$id]);
        $row = $st->fetch(PDO::FETCH_ASSOC);

        return is_array($row) ? $row : null;
    }
}

if (!function_exists('vm_site_pages_list_all')) {
    function vm_site_pages_list_all(PDO $pdo): array
    {
        if (!vm_site_pages_table_exists($pdo)) {
            return [];
        }

        $st = $pdo->query('SELECT * FROM site_pages ORDER BY sort_order ASC, id ASC');
        $rows = $st ? $st->fetchAll(PDO::FETCH_ASSOC) : [];

        return is_array($rows) ? $rows : [];
    }
}

if (!function_exists('vm_site_pages_footer_pages')) {
    function vm_site_pages_footer_pages(PDO $pdo): array
    {
        if (!vm_site_pages_table_exists($pdo)) {
            return [];
        }

        $st = $pdo->query('
            SELECT id, slug, title_uk, title_ru, title_en, sort_order
            FROM site_pages
            WHERE is_active = 1 AND show_in_footer = 1
            ORDER BY sort_order ASC, id ASC
        ');
        $rows = $st ? $st->fetchAll(PDO::FETCH_ASSOC) : [];

        return is_array($rows) ? $rows : [];
    }
}

if (!function_exists('vm_site_pages_save')) {
    function vm_site_pages_save(PDO $pdo, array $data, int $userId = 0, int $id = 0): int
    {
        if (!vm_site_pages_table_exists($pdo)) {
            throw new RuntimeException('Table site_pages does not exist.');
        }

        $slug = vm_site_pages_slugify((string)($data['slug'] ?? ''));
        if ($slug === '') {
            throw new InvalidArgumentException('Slug is required.');
        }

        $titleUk = trim((string)($data['title_uk'] ?? ''));
        $titleRu = trim((string)($data['title_ru'] ?? ''));
        $titleEn = trim((string)($data['title_en'] ?? ''));

        if ($titleUk === '' && $titleRu === '' && $titleEn === '') {
            throw new InvalidArgumentException('At least one title is required.');
        }

        $bodyUk = trim((string)($data['body_uk'] ?? ''));
        $bodyRu = trim((string)($data['body_ru'] ?? ''));
        $bodyEn = trim((string)($data['body_en'] ?? ''));

        $showInFooter = !empty($data['show_in_footer']) ? 1 : 0;
        $isActive = !empty($data['is_active']) ? 1 : 0;
        $sortOrder = max(0, (int)($data['sort_order'] ?? 100));
        $uid = $userId > 0 ? $userId : null;

        $checkSql = 'SELECT id FROM site_pages WHERE slug = ?';
        $checkArgs = [$slug];
        if ($id > 0) {
            $checkSql .= ' AND id <> ?';
            $checkArgs[] = $id;
        }
        $checkSql .= ' LIMIT 1';

        $check = $pdo->prepare($checkSql);
        $check->execute($checkArgs);
        if ($check->fetchColumn()) {
            throw new InvalidArgumentException('Slug must be unique.');
        }

        if ($id > 0) {
            $sql = '
                UPDATE site_pages
                SET
                    slug = ?,
                    title_uk = ?,
                    title_ru = ?,
                    title_en = ?,
                    body_uk = ?,
                    body_ru = ?,
                    body_en = ?,
                    show_in_footer = ?,
                    sort_order = ?,
                    is_active = ?,
                    updated_by_user_id = ?
                WHERE id = ?
                LIMIT 1
            ';
            $st = $pdo->prepare($sql);
            $st->execute([
                $slug,
                $titleUk,
                $titleRu,
                $titleEn,
                $bodyUk,
                $bodyRu,
                $bodyEn,
                $showInFooter,
                $sortOrder,
                $isActive,
                $uid,
                $id,
            ]);

            return $id;
        }

        $sql = '
            INSERT INTO site_pages
            (
                slug,
                title_uk,
                title_ru,
                title_en,
                body_uk,
                body_ru,
                body_en,
                show_in_footer,
                sort_order,
                is_active,
                created_by_user_id,
                updated_by_user_id
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ';
        $st = $pdo->prepare($sql);
        $st->execute([
            $slug,
            $titleUk,
            $titleRu,
            $titleEn,
            $bodyUk,
            $bodyRu,
            $bodyEn,
            $showInFooter,
            $sortOrder,
            $isActive,
            $uid,
            $uid,
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('vm_site_pages_delete')) {
    function vm_site_pages_delete(PDO $pdo, int $id): void
    {
        if (!vm_site_pages_table_exists($pdo) || $id <= 0) {
            return;
        }

        $st = $pdo->prepare('DELETE FROM site_pages WHERE id = ? LIMIT 1');
        $st->execute([$id]);
    }
}