<?php

declare(strict_types=1);

class Page
{
    public static function find(int $id): ?array
    {
        return Database::fetchOne('SELECT * FROM pages WHERE id = ?', [$id]);
    }

    public static function findBySlug(string $slug): ?array
    {
        return Database::fetchOne('SELECT * FROM pages WHERE slug = ? AND status = ?', [$slug, 'published']);
    }

    public static function all(int $limit = 1000, int $offset = 0): array
    {
        return Database::fetchAll('SELECT * FROM pages ORDER BY sort_order ASC, title ASC LIMIT ? OFFSET ?', [$limit, $offset]);
    }

    public static function count(): int
    {
        return Database::count('pages');
    }

    public static function footerPages(): array
    {
        $defaultLang = Translator::getDefaultCode();
        $currentLang = Translator::locale();
        $pages = Database::fetchAll(
            'SELECT id, title, slug FROM pages WHERE show_in_footer = 1 AND status = ? ORDER BY sort_order ASC',
            ['published']
        );

        if ($currentLang !== $defaultLang) {
            $ids = array_column($pages, 'id');
            if (!empty($ids)) {
                $placeholders = implode(',', array_fill(0, count($ids), '?'));
                $translations = Database::fetchAll(
                    "SELECT page_id, title, slug FROM page_translations WHERE lang_code = ? AND page_id IN ({$placeholders})",
                    array_merge([$currentLang], array_map('intval', $ids))
                );
                $transMap = [];
                foreach ($translations as $t) {
                    $transMap[(int) $t['page_id']] = $t;
                }
                foreach ($pages as &$page) {
                    $pid = (int) $page['id'];
                    if (isset($transMap[$pid])) {
                        $page['title'] = $transMap[$pid]['title'];
                        $page['slug'] = $transMap[$pid]['slug'];
                    }
                }
                unset($page);
            }
        }

        return $pages;
    }

    public static function allPublished(): array
    {
        return Database::fetchAll(
            'SELECT id, title, slug FROM pages WHERE status = ? ORDER BY sort_order ASC, title ASC',
            ['published']
        );
    }

    public static function findByIds(array $ids): array
    {
        if (empty($ids)) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $rows = Database::fetchAll(
            "SELECT id, title, slug FROM pages WHERE id IN ({$placeholders}) AND status = 'published'",
            array_map('intval', $ids)
        );

        $defaultLang = Translator::getDefaultCode();
        $currentLang = Translator::locale();

        if ($currentLang !== $defaultLang && !empty($rows)) {
            $rowIds = array_column($rows, 'id');
            $ph2 = implode(',', array_fill(0, count($rowIds), '?'));
            $translations = Database::fetchAll(
                "SELECT page_id, title, slug FROM page_translations WHERE lang_code = ? AND page_id IN ({$ph2})",
                array_merge([$currentLang], array_map('intval', $rowIds))
            );
            $transMap = [];
            foreach ($translations as $t) {
                $transMap[(int) $t['page_id']] = $t;
            }
            foreach ($rows as &$row) {
                $pid = (int) $row['id'];
                if (isset($transMap[$pid])) {
                    $row['title'] = $transMap[$pid]['title'];
                    $row['slug'] = $transMap[$pid]['slug'];
                }
            }
            unset($row);
        }

         
        $indexed = [];
        foreach ($rows as $row) {
            $indexed[(int) $row['id']] = $row;
        }
        $result = [];
        foreach ($ids as $id) {
            if (isset($indexed[(int) $id])) {
                $result[] = $indexed[(int) $id];
            }
        }
        return $result;
    }

    public static function create(array $data): int
    {
        return Database::insert('pages', $data);
    }

    public static function update(int $id, array $data): void
    {
        Database::update('pages', $data, 'id = ?', [$id]);
    }

    public static function delete(int $id): void
    {
        Database::delete('pages', 'id = ?', [$id]);
    }

     

    public static function translations(int $pageId): array
    {
        $rows = Database::fetchAll(
            'SELECT * FROM page_translations WHERE page_id = ? ORDER BY lang_code',
            [$pageId]
        );
        $result = [];
        foreach ($rows as $row) {
            $result[$row['lang_code']] = $row;
        }
        return $result;
    }

    public static function translation(int $pageId, string $langCode): ?array
    {
        return Database::fetchOne(
            'SELECT * FROM page_translations WHERE page_id = ? AND lang_code = ?',
            [$pageId, $langCode]
        );
    }

    public static function saveTranslation(int $pageId, string $langCode, array $data): void
    {
        $existing = self::translation($pageId, $langCode);
        if ($existing) {
            Database::update('page_translations', $data, 'page_id = ? AND lang_code = ?', [$pageId, $langCode]);
        } else {
            Database::insert('page_translations', array_merge($data, [
                'page_id' => $pageId,
                'lang_code' => $langCode,
            ]));
        }
    }

    public static function deleteTranslation(int $pageId, string $langCode): void
    {
        Database::delete('page_translations', 'page_id = ? AND lang_code = ?', [$pageId, $langCode]);
    }

    public static function findBySlugLocalized(string $slug, string $langCode): ?array
    {
        $defaultLang = Translator::getDefaultCode();

        if ($langCode === $defaultLang) {
            return Database::fetchOne('SELECT * FROM pages WHERE slug = ? AND status = ?', [$slug, 'published']);
        }

         
        $trans = Database::fetchOne(
            'SELECT pt.*, p.status, p.sort_order, p.show_in_footer, p.meta_title AS p_meta_title, p.meta_description AS p_meta_desc
             FROM page_translations pt
             JOIN pages p ON p.id = pt.page_id
             WHERE pt.slug = ? AND pt.lang_code = ? AND p.status = ?',
            [$slug, $langCode, 'published']
        );

        if ($trans) {
             
            $page = Database::fetchOne('SELECT * FROM pages WHERE id = ?', [(int) $trans['page_id']]);
            if ($page) {
                $page['title'] = $trans['title'];
                $page['slug'] = $trans['slug'];
                $page['body'] = (!empty($trans['body'])) ? $trans['body'] : $page['body'];
                $page['meta_title'] = (!empty($trans['meta_title'])) ? $trans['meta_title'] : $page['meta_title'];
                $page['meta_description'] = (!empty($trans['meta_description'])) ? $trans['meta_description'] : $page['meta_description'];
                return $page;
            }
        }

         
        return Database::fetchOne('SELECT * FROM pages WHERE slug = ? AND status = ?', [$slug, 'published']);
    }

    public static function translationStatus(int $pageId): array
    {
        $defaultLang = Translator::getDefaultCode();
        $available = Translator::available();
        $translations = self::translations($pageId);

        $result = [];
        foreach ($available as $lang) {
            if (empty($lang['is_enabled'])) {
                continue;
            }
            $code = $lang['code'];
            if ($code === $defaultLang) {
                $result[$code] = 'default';
            } elseif (isset($translations[$code])) {
                $result[$code] = 'translated';
            } else {
                $result[$code] = 'missing';
            }
        }

        return $result;
    }

    public static function allTranslationStatuses(): array
    {
        $defaultLang = Translator::getDefaultCode();
        $available = Translator::available();
        $enabledLangs = [];
        foreach ($available as $lang) {
            if (!empty($lang['is_enabled'])) {
                $enabledLangs[] = $lang['code'];
            }
        }

        $rows = Database::fetchAll('SELECT page_id, lang_code FROM page_translations');
        $transMap = [];
        foreach ($rows as $row) {
            $transMap[(int) $row['page_id']][$row['lang_code']] = true;
        }

        return ['defaultLang' => $defaultLang, 'enabledLangs' => $enabledLangs, 'transMap' => $transMap];
    }
}
