<?php
namespace Domain\Banner;

/**
 * Repository odpowiedzialny za dostęp do danych banerów
 */
class BannerRepository
{
    private const MAX_PER_PAGE = 100;

    private $db;

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

    /**
     * Pobiera baner po ID wraz z tłumaczeniami
     *
     * @param int $bannerId ID banera
     * @return array|null Dane banera lub null
     */
    public function find(int $bannerId): ?array
    {
        $banner = $this->db->get('pp_banners', '*', ['id' => $bannerId]);

        if (!$banner) {
            return null;
        }

        $results = $this->db->select('pp_banners_langs', '*', [
            'id_banner' => $bannerId,
            'ORDER' => ['id' => 'ASC'],
        ]);
        if (is_array($results)) {
            foreach ($results as $row) {
                $banner['languages'][$row['id_lang']] = $row;
            }
        }

        return $banner;
    }

    /**
     * Usuwa baner
     *
     * @param int $bannerId ID banera
     * @return bool Czy usunięto
     */
    public function delete(int $bannerId): bool
    {
        $result = $this->db->delete('pp_banners', ['id' => $bannerId]);
        return $result !== false;
    }

    /**
     * Zapisuje baner (insert lub update)
     *
     * @param array $data Dane banera (obsługuje format z FormRequestHandler lub stary format)
     * @return int|false ID banera lub false
     */
    public function save(array $data)
    {
        $bannerId = $data['id'] ?? null;

        // Obsługa obu formatów: nowy (int) i stary ('on'/string)
        $status = $data['status'] ?? 0;
        if ($status === 'on') {
            $status = 1;
        }
        
        $homePage = $data['home_page'] ?? 0;
        if ($homePage === 'on') {
            $homePage = 1;
        }

        $bannerData = [
            'name'       => $data['name'],
            'status'     => (int)$status,
            'date_start' => !empty($data['date_start']) ? $data['date_start'] : null,
            'date_end'   => !empty($data['date_end']) ? $data['date_end'] : null,
            'home_page'  => (int)$homePage,
        ];

        if (!$bannerId) {
            $this->db->insert('pp_banners', $bannerData);
            $bannerId = $this->db->id();
            if (!$bannerId) {
                return false;
            }
        } else {
            $this->db->update('pp_banners', $bannerData, ['id' => (int)$bannerId]);
        }

        // Obsługa danych językowych - nowy format (translations) lub stary (src/url/html/text)
        if (isset($data['translations']) && is_array($data['translations'])) {
            // Nowy format z FormRequestHandler
            $this->saveTranslationsFromArray($bannerId, $data['translations']);
        } elseif (isset($data['src']) && is_array($data['src'])) {
            // Stary format (backward compatibility)
            $this->saveTranslations($bannerId, $data['src'], $data['url'], $data['html'], $data['text']);
        }

        \Shared\Helpers\Helpers::delete_dir('../temp/');

        return (int)$bannerId;
    }

    /**
     * Zwraca liste banerow do panelu admin z filtrowaniem, sortowaniem i paginacja.
     *
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listForAdmin(
        array $filters,
        string $sortColumn = 'name',
        string $sortDir = 'ASC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $sortColumn = trim($sortColumn);
        $sortDir = strtoupper(trim($sortDir));

        $allowedSortColumns = [
            'name' => 'b.name',
            'status' => 'b.status',
            'home_page' => 'b.home_page',
            'date_start' => 'b.date_start',
            'date_end' => 'b.date_end',
        ];

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'b.name';
        $sortDir = $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 (strlen($name) > 255) {
            $name = substr($name, 0, 255);
        }

        if ($name !== '') {
            $where[] = 'b.name LIKE :name';
            $params[':name'] = '%' . $name . '%';
        }

        if (($filters['status'] ?? '') !== '' && ($filters['status'] === '0' || $filters['status'] === '1')) {
            $where[] = 'b.status = :status';
            $params[':status'] = (int)$filters['status'];
        }

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

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

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

        $sql = "
            SELECT
                b.id,
                b.name,
                b.status,
                b.home_page,
                b.date_start,
                b.date_end
            FROM pp_banners AS b
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, b.id {$sortDir}
            LIMIT {$perPage} OFFSET {$offset}
        ";

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

        if (!empty($items)) {
            $bannerIds = array_map('intval', array_column($items, 'id'));
            $thumbByBannerId = $this->fetchThumbnailsByBannerIds($bannerIds);

            foreach ($items as &$item) {
                $item['thumbnail_src'] = $thumbByBannerId[(int)($item['id'] ?? 0)] ?? '';
            }
            unset($item);
        }

        return [
            'items' => $items,
            'total' => $total,
        ];
    }

    /**
     * Pobiera pierwsza dostepna sciezke obrazka (src) dla kazdego banera.
     *
     * @param array<int, int> $bannerIds
     * @return array<int, string> [id_banner => src]
     */
    private function fetchThumbnailsByBannerIds(array $bannerIds): array
    {
        $bannerIds = array_values(array_unique(array_filter($bannerIds, static function ($id): bool {
            return (int)$id > 0;
        })));

        if (empty($bannerIds)) {
            return [];
        }

        $in = [];
        $params = [];
        foreach ($bannerIds as $index => $bannerId) {
            $placeholder = ':id' . $index;
            $in[] = $placeholder;
            $params[$placeholder] = (int)$bannerId;
        }

        $sql = '
            SELECT id_banner, src
            FROM pp_banners_langs
            WHERE id_banner IN (' . implode(', ', $in) . ')
              AND src IS NOT NULL
              AND src <> \'\'
            ORDER BY id_lang ASC, id ASC
        ';

        $stmt = $this->db->query($sql, $params);
        $rows = $stmt ? $stmt->fetchAll() : [];
        if (!is_array($rows)) {
            return [];
        }

        $thumbByBannerId = [];
        foreach ($rows as $row) {
            $bannerId = (int)($row['id_banner'] ?? 0);
            if ($bannerId <= 0 || isset($thumbByBannerId[$bannerId])) {
                continue;
            }

            $src = trim((string)($row['src'] ?? ''));
            if ($src !== '') {
                $thumbByBannerId[$bannerId] = $src;
            }
        }

        return $thumbByBannerId;
    }

    /**
     * Zapisuje tłumaczenia banera (stary format - zachowano dla kompatybilności)
     */
    private function saveTranslations(int $bannerId, array $src, array $url, array $html, array $text): void
    {
        foreach ($src as $langId => $val) {
            $this->upsertTranslation($bannerId, $langId, [
                'src' => $src[$langId] ?? '',
                'url' => $url[$langId] ?? '',
                'html' => $html[$langId] ?? '',
                'text' => $text[$langId] ?? '',
            ]);
        }
    }

    /**
     * Zapisuje tłumaczenia banera z nowego formatu (z FormRequestHandler)
     * Format: [lang_id => [field => value]]
     */
    private function saveTranslationsFromArray(int $bannerId, array $translations): void
    {
        foreach ($translations as $langId => $fields) {
            $this->upsertTranslation($bannerId, $langId, [
                'src' => $fields['src'] ?? '',
                'url' => $fields['url'] ?? '',
                'html' => $fields['html'] ?? '',
                'text' => $fields['text'] ?? '',
            ]);
        }
    }

    /**
     * Upsert tlumaczenia banera.
     * Aktualizuje wszystkie rekordy dla pary id_banner + id_lang,
     * co usuwa problem z historycznymi duplikatami.
     */
    private function upsertTranslation(int $bannerId, $langId, array $fields): void
    {
        $where = ['AND' => ['id_banner' => $bannerId, 'id_lang' => $langId]];
        $translationData = [
            'id_banner' => $bannerId,
            'id_lang'   => $langId,
            'src'       => $fields['src'] ?? '',
            'url'       => $fields['url'] ?? '',
            'html'      => $fields['html'] ?? '',
            'text'      => $fields['text'] ?? '',
        ];

        $hasExisting = (int)$this->db->count('pp_banners_langs', $where) > 0;

        if ($hasExisting) {
            $this->db->update('pp_banners_langs', $translationData, $where);
            return;
        }

        $this->db->insert('pp_banners_langs', $translationData);
    }

    // ─── Frontend methods ───────────────────────────────────────────

    /**
     * Pobiera aktywne banery (home_page = 0) z filtrowaniem dat, z Redis cache.
     * Zwraca dane w formacie zgodnym z szablonami: $banner['languages'] = płaski wiersz.
     */
    public function banners(string $langId): ?array
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "BannerRepository::banners:{$langId}";

        $objectData = $cacheHandler->get($cacheKey);

        if ($objectData) {
            return unserialize($objectData);
        }

        $today = date('Y-m-d');
        $stmt = $this->db->query(
            "SELECT id, name FROM pp_banners "
            . "WHERE status = 1 "
            . "AND (date_start <= :today1 OR date_start IS NULL) "
            . "AND (date_end >= :today2 OR date_end IS NULL) "
            . "AND home_page = 0",
            [':today1' => $today, ':today2' => $today]
        );
        $results = $stmt ? $stmt->fetchAll() : [];

        $banners = null;
        if (is_array($results) && !empty($results)) {
            foreach ($results as $row) {
                $row['languages'] = $this->db->get('pp_banners_langs', '*', [
                    'AND' => ['id_banner' => (int)$row['id'], 'id_lang' => $langId]
                ]);
                $banners[] = $row;
            }
        }

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

        return $banners;
    }

    /**
     * Pobiera glowny baner (home_page = 1) z filtrowaniem dat, z Redis cache.
     * Zwraca dane w formacie zgodnym z szablonami: $banner['languages'] = plaski wiersz.
     */
    public function mainBanner(string $langId): ?array
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "BannerRepository::mainBanner:{$langId}";

        $objectData = $cacheHandler->get($cacheKey);

        if ($objectData) {
            return unserialize($objectData);
        }

        $today = date('Y-m-d');
        $stmt = $this->db->query(
            "SELECT * FROM pp_banners "
            . "WHERE status = 1 "
            . "AND (date_start <= :today1 OR date_start IS NULL) "
            . "AND (date_end >= :today2 OR date_end IS NULL) "
            . "AND home_page = 1 "
            . "ORDER BY date_end ASC "
            . "LIMIT 1",
            [':today1' => $today, ':today2' => $today]
        );
        $results = $stmt ? $stmt->fetchAll() : [];

        $banner = null;
        if (is_array($results) && !empty($results)) {
            $banner = $results[0];
            $banner['languages'] = $this->db->get('pp_banners_langs', '*', [
                'AND' => ['id_banner' => (int)$banner['id'], 'id_lang' => $langId]
            ]);
        }

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

        return $banner;
    }
}