<?php
namespace Domain\Languages;

class LanguagesRepository
{
    private const MAX_PER_PAGE = 100;

    private $db;

    public function __construct($db)
    {
        $this->db = $db;
    }

    /**
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listForAdmin(
        array $filters,
        string $sortColumn = 'o',
        string $sortDir = 'ASC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $allowedSortColumns = [
            'id' => 'pl.id',
            'name' => 'pl.name',
            'status' => 'pl.status',
            'start' => 'pl.start',
            'o' => 'pl.o',
        ];

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'pl.o';
        $sortDir = strtoupper(trim($sortDir)) === 'DESC' ? 'DESC' : 'ASC';
        $page = max(1, $page);
        $perPage = min(self::MAX_PER_PAGE, max(1, $perPage));
        $offset = ($page - 1) * $perPage;

        $where = ['1 = 1'];
        $params = [];

        $name = trim((string)($filters['name'] ?? ''));
        if ($name !== '') {
            if (strlen($name) > 255) {
                $name = substr($name, 0, 255);
            }
            $where[] = 'pl.name LIKE :name';
            $params[':name'] = '%' . $name . '%';
        }

        $status = trim((string)($filters['status'] ?? ''));
        if ($status === '0' || $status === '1') {
            $where[] = 'pl.status = :status';
            $params[':status'] = (int)$status;
        }

        $start = trim((string)($filters['start'] ?? ''));
        if ($start === '0' || $start === '1') {
            $where[] = 'pl.start = :start';
            $params[':start'] = (int)$start;
        }

        $whereSql = implode(' AND ', $where);

        $sqlCount = "
            SELECT COUNT(0)
            FROM pp_langs AS pl
            WHERE {$whereSql}
        ";

        $stmtCount = $this->db->query($sqlCount, $params);
        $countRows = $stmtCount ? $stmtCount->fetchAll() : [];
        $total = isset($countRows[0][0]) ? (int)$countRows[0][0] : 0;

        $sql = "
            SELECT
                pl.id,
                pl.name,
                pl.status,
                pl.start,
                pl.o
            FROM pp_langs AS pl
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, pl.o ASC, pl.id ASC
            LIMIT {$perPage} OFFSET {$offset}
        ";

        $stmt = $this->db->query($sql, $params);
        $items = $stmt ? $stmt->fetchAll() : [];

        return [
            'items' => is_array($items) ? $items : [],
            'total' => $total,
        ];
    }

    /**
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listTranslationsForAdmin(
        array $filters,
        string $sortColumn = 'text',
        string $sortDir = 'ASC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $allowedSortColumns = [
            'id' => 'plt.id',
            'text' => 'plt.text',
        ];

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'plt.text';
        $sortDir = strtoupper(trim($sortDir)) === 'DESC' ? 'DESC' : 'ASC';
        $page = max(1, $page);
        $perPage = min(self::MAX_PER_PAGE, max(1, $perPage));
        $offset = ($page - 1) * $perPage;

        $where = ['1 = 1'];
        $params = [];

        $text = trim((string)($filters['text'] ?? ''));
        if ($text !== '') {
            if (strlen($text) > 255) {
                $text = substr($text, 0, 255);
            }
            $where[] = 'plt.text LIKE :text';
            $params[':text'] = '%' . $text . '%';
        }

        $whereSql = implode(' AND ', $where);

        $sqlCount = "
            SELECT COUNT(0)
            FROM pp_langs_translations AS plt
            WHERE {$whereSql}
        ";

        $stmtCount = $this->db->query($sqlCount, $params);
        $countRows = $stmtCount ? $stmtCount->fetchAll() : [];
        $total = isset($countRows[0][0]) ? (int)$countRows[0][0] : 0;

        $sql = "
            SELECT
                plt.id,
                plt.text
            FROM pp_langs_translations AS plt
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, plt.id ASC
            LIMIT {$perPage} OFFSET {$offset}
        ";

        $stmt = $this->db->query($sql, $params);
        $items = $stmt ? $stmt->fetchAll() : [];

        return [
            'items' => is_array($items) ? $items : [],
            'total' => $total,
        ];
    }

    public function languageDetails(string $languageId): ?array
    {
        $language = $this->db->get('pp_langs', '*', ['id' => $languageId]);
        return $language ?: null;
    }

    public function translationDetails(int $translationId): ?array
    {
        $translation = $this->db->get('pp_langs_translations', '*', ['id' => $translationId]);
        return $translation ?: null;
    }

    public function maxOrder(): int
    {
        $max = $this->db->max('pp_langs', 'o');
        return $max ? (int)$max : 0;
    }

    public function languagesList(bool $onlyActive = false): array
    {
        $where = [];
        if ($onlyActive) {
            $where['status'] = 1;
        }

        $rows = $this->db->select('pp_langs', '*', array_merge(['ORDER' => ['o' => 'ASC']], $where));
        return is_array($rows) ? $rows : [];
    }

    public function defaultLanguageId(): string
    {
        $languages = $this->languagesList();
        if (empty($languages)) {
            return 'pl';
        }

        foreach ($languages as $language) {
            if ((int)($language['start'] ?? 0) === 1 && !empty($language['id'])) {
                return (string)$language['id'];
            }
        }

        if (!empty($languages[0]['id'])) {
            return (string)$languages[0]['id'];
        }

        return 'pl';
    }

    public function deleteLanguage(string $languageId): bool
    {
        $languageId = $this->sanitizeLanguageId($languageId);
        if ($languageId === null) {
            return false;
        }

        if ((int)$this->db->count('pp_langs') <= 1) {
            return false;
        }

        if (!$this->db->count('pp_langs', ['id' => $languageId])) {
            return false;
        }

        $dropResult = $this->db->query('ALTER TABLE pp_langs_translations DROP COLUMN `' . $languageId . '`');
        if (!$dropResult) {
            return false;
        }

        $deleteResult = $this->db->delete('pp_langs', ['id' => $languageId]);
        if (!$deleteResult) {
            return false;
        }

        \Shared\Helpers\Helpers::htacces();
        \Shared\Helpers\Helpers::delete_dir('../temp/');
        return true;
    }

    public function saveLanguage(string $languageId, string $name, $status, $start, int $order): ?string
    {
        $languageId = $this->sanitizeLanguageId($languageId);
        if ($languageId === null) {
            return null;
        }

        $statusVal = $this->toSwitchValue($status);
        $startVal = $this->toSwitchValue($start);

        $exists = (bool)$this->db->count('pp_langs', ['id' => $languageId]);

        if ($startVal === 1) {
            $this->db->update('pp_langs', ['start' => 0], ['id[!]' => $languageId]);
        }

        if ($exists) {
            $this->db->update('pp_langs', [
                'status' => $statusVal,
                'start' => $startVal,
                'name' => $name,
                'o' => $order,
            ], [
                'id' => $languageId,
            ]);
        } else {
            $addResult = $this->db->query('ALTER TABLE pp_langs_translations ADD COLUMN `' . $languageId . '` TEXT NULL DEFAULT NULL');
            if (!$addResult) {
                return null;
            }

            $insertResult = $this->db->insert('pp_langs', [
                'id' => $languageId,
                'name' => $name,
                'status' => $statusVal,
                'start' => $startVal,
                'o' => $order,
            ]);
            if (!$insertResult) {
                return null;
            }
        }

        if (!(int)$this->db->count('pp_langs', ['start' => 1])) {
            $idTmp = (string)$this->db->get('pp_langs', 'id', ['ORDER' => ['o' => 'ASC']]);
            if ($idTmp !== '') {
                $this->db->update('pp_langs', ['start' => 1], ['id' => $idTmp]);
            }
        }

        \Shared\Helpers\Helpers::htacces();
        \Shared\Helpers\Helpers::delete_dir('../temp/');
        return $languageId;
    }

    public function deleteTranslation(int $translationId): bool
    {
        $result = $this->db->delete('pp_langs_translations', ['id' => $translationId]);
        return (bool)$result;
    }

    public function saveTranslation(int $translationId, string $text, array $translations): ?int
    {
        if ($translationId > 0) {
            $this->db->update('pp_langs_translations', ['text' => $text], ['id' => $translationId]);
        } else {
            $insertResult = $this->db->insert('pp_langs_translations', ['text' => $text]);
            if (!$insertResult) {
                return null;
            }
            $translationId = (int)$this->db->id();
        }

        if ($translationId <= 0) {
            return null;
        }

        foreach ($translations as $languageId => $value) {
            $safeLanguageId = $this->sanitizeLanguageId((string)$languageId);
            if ($safeLanguageId === null) {
                continue;
            }

            $this->db->update('pp_langs_translations', [
                $safeLanguageId => (string)$value,
            ], [
                'id' => $translationId,
            ]);
        }

        \Shared\Helpers\Helpers::htacces();
        \Shared\Helpers\Helpers::delete_dir('../temp/');
        return $translationId;
    }

    /**
     * Zwraca ID domyslnego jezyka (z flaga start=1) z cache Redis.
     */
    public function defaultLanguage(): string
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'Domain\Languages\LanguagesRepository::defaultLanguage';

        $objectData = $cacheHandler->get($cacheKey);
        if ($objectData) {
            return unserialize($objectData);
        }

        $stmt = $this->db->query(
            'SELECT id FROM pp_langs WHERE status = 1 ORDER BY start DESC, o ASC LIMIT 1'
        );
        $results = $stmt ? $stmt->fetchAll() : [];

        $defaultLanguage = $results[0][0] ?? 'pl';

        $cacheHandler->set($cacheKey, $defaultLanguage);

        return $defaultLanguage;
    }

    /**
     * Zwraca liste aktywnych jezykow z cache Redis.
     */
    public function activeLanguages(): array
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'Domain\Languages\LanguagesRepository::activeLanguages';

        $objectData = $cacheHandler->get($cacheKey);
        if ($objectData) {
            return unserialize($objectData);
        }

        $activeLanguages = $this->db->select(
            'pp_langs',
            ['id', 'name'],
            ['status' => 1, 'ORDER' => ['o' => 'ASC']]
        );

        if (!is_array($activeLanguages)) {
            $activeLanguages = [];
        }

        $cacheHandler->set($cacheKey, $activeLanguages);

        return $activeLanguages;
    }

    /**
     * Zwraca tlumaczenia dla danego jezyka z cache Redis.
     */
    public function translations(string $language = 'pl'): array
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "Domain\Languages\LanguagesRepository::translations:$language";

        $objectData = $cacheHandler->get($cacheKey);
        if ($objectData) {
            return unserialize($objectData);
        }

        $translations = ['0' => $language];

        $results = $this->db->select('pp_langs_translations', ['text', $language]);
        if (is_array($results)) {
            foreach ($results as $row) {
                $translations[$row['text']] = $row[$language];
            }
        }

        $cacheHandler->set($cacheKey, $translations);

        return $translations;
    }

    private function sanitizeLanguageId(string $languageId): ?string
    {
        $languageId = strtolower(trim($languageId));
        if (!preg_match('/^[a-z]{2}$/', $languageId)) {
            return null;
        }

        return $languageId;
    }

    private function toSwitchValue($value): int
    {
        return ($value === 'on' || $value === 1 || $value === '1' || $value === true) ? 1 : 0;
    }
}