pomysloweprezenty.pl/autoload/Domain/Newsletter/NewsletterRepository.php

<?php
namespace Domain\Newsletter;

use Domain\Settings\SettingsRepository;
use Domain\Article\ArticleRepository;

class NewsletterRepository
{
    private const MAX_PER_PAGE = 100;

    private $db;
    private SettingsRepository $settingsRepository;
    private ?ArticleRepository $articleRepository;
    private ?NewsletterPreviewRenderer $previewRenderer;

    public function __construct(
        $db,
        ?SettingsRepository $settingsRepository = null,
        ?ArticleRepository $articleRepository = null,
        ?NewsletterPreviewRenderer $previewRenderer = null
    ) {
        $this->db = $db;
        $this->settingsRepository = $settingsRepository ?? new SettingsRepository($db);
        $this->articleRepository = $articleRepository;
        $this->previewRenderer = $previewRenderer;
    }

    private function getArticleRepository(): ArticleRepository
    {
        return $this->articleRepository ?? ($this->articleRepository = new ArticleRepository($this->db));
    }

    private function getPreviewRenderer(): NewsletterPreviewRenderer
    {
        return $this->previewRenderer ?? ($this->previewRenderer = new NewsletterPreviewRenderer());
    }

    public function getSettings(): array
    {
        return $this->settingsRepository->getSettings();
    }

    public function saveSettings(array $values): bool
    {
        $this->settingsRepository->updateSetting('newsletter_footer', (string)($values['newsletter_footer'] ?? ''));
        $this->settingsRepository->updateSetting('newsletter_header', (string)($values['newsletter_header'] ?? ''));
        \Shared\Helpers\Helpers::delete_dir('../temp/');

        return true;
    }

    public function queueSend(string $dates = '', int $templateId = 0): bool
    {
        $subscribers = $this->db->select('pp_newsletter', 'email', ['status' => 1]);
        if (!is_array($subscribers) || empty($subscribers)) {
            return true;
        }

        $cleanDates = trim($dates);
        $templateId = $templateId > 0 ? $templateId : 0;

        foreach ($subscribers as $subscriber) {
            $email = is_array($subscriber) ? (string)($subscriber['email'] ?? '') : (string)$subscriber;
            if ($email === '') {
                continue;
            }

            $this->db->insert('pp_newsletter_send', [
                'email' => $email,
                'dates' => $cleanDates,
                'id_template' => $templateId > 0 ? $templateId : null,
            ]);
        }

        return true;
    }

    public function templateByName(string $templateName): string
    {
        return (string)$this->db->get('pp_newsletter_templates', 'text', ['name' => $templateName]);
    }

    public function templateDetails(int $templateId): ?array
    {
        if ($templateId <= 0) {
            return null;
        }

        $row = $this->db->get('pp_newsletter_templates', '*', ['id' => $templateId]);
        if (!is_array($row)) {
            return null;
        }

        return $row;
    }

    public function isAdminTemplate(int $templateId): bool
    {
        $isAdmin = $this->db->get('pp_newsletter_templates', 'is_admin', ['id' => $templateId]);
        return (int)$isAdmin === 1;
    }

    public function deleteTemplate(int $templateId): bool
    {
        if ($templateId <= 0 || $this->isAdminTemplate($templateId)) {
            return false;
        }

        return (bool)$this->db->delete('pp_newsletter_templates', ['id' => $templateId]);
    }

    public function saveTemplate(int $templateId, string $name, string $text): ?int
    {
        $templateId = max(0, $templateId);
        $name = trim($name);

        if ($templateId <= 0) {
            if ($name === '') {
                return null;
            }

            $ok = $this->db->insert('pp_newsletter_templates', [
                'name' => $name,
                'text' => $text,
                'is_admin' => 0,
            ]);
            if (!$ok) {
                return null;
            }

            \Shared\Helpers\Helpers::delete_dir('../temp/');
            return (int)$this->db->id();
        }

        $current = $this->templateDetails($templateId);
        if (!is_array($current)) {
            return null;
        }

        if ((int)($current['is_admin'] ?? 0) === 1) {
            $name = (string)($current['name'] ?? '');
        }

        $this->db->update('pp_newsletter_templates', [
            'name' => $name,
            'text' => $text,
        ], [
            'id' => $templateId,
        ]);

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

    public function listTemplatesSimple(bool $adminTemplates = false): array
    {
        $rows = $this->db->select('pp_newsletter_templates', '*', [
            'is_admin' => $adminTemplates ? 1 : 0,
            'ORDER' => ['name' => 'ASC'],
        ]);

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

    public function deleteSubscriber(int $subscriberId): bool
    {
        if ($subscriberId <= 0) {
            return false;
        }

        return (bool)$this->db->delete('pp_newsletter', ['id' => $subscriberId]);
    }

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

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'pn.email';
        $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 = [];

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

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

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

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

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

        $sql = "
            SELECT
                pn.id,
                pn.email,
                pn.status
            FROM pp_newsletter AS pn
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, pn.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 listTemplatesForAdmin(
        bool $adminTemplates,
        array $filters,
        string $sortColumn = 'name',
        string $sortDir = 'ASC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $allowedSortColumns = [
            'id' => 'pnt.id',
            'name' => 'pnt.name',
        ];

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'pnt.name';
        $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 = ['pnt.is_admin = :is_admin'];
        $params = [':is_admin' => $adminTemplates ? 1 : 0];

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

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

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

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

        $sql = "
            SELECT
                pnt.id,
                pnt.name,
                pnt.is_admin
            FROM pp_newsletter_templates AS pnt
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, pnt.id ASC
            LIMIT {$perPage} OFFSET {$offset}
        ";

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

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

    // ── Frontend methods ──

    public function unsubscribe(string $hash): bool
    {
        $id = $this->db->get('pp_newsletter', 'id', ['hash' => $hash]);
        if (!$id) {
            return false;
        }

        $this->db->delete('pp_newsletter', ['id' => $id]);
        return true;
    }

    public function confirmSubscription(string $hash): bool
    {
        $id = $this->db->get('pp_newsletter', 'id', ['AND' => ['hash' => $hash, 'status' => 0]]);
        if (!$id) {
            return false;
        }

        $this->db->update('pp_newsletter', ['status' => 1], ['id' => $id]);
        return true;
    }

    public function getHashByEmail(string $email): ?string
    {
        $hash = $this->db->get('pp_newsletter', 'hash', ['email' => $email]);
        return $hash ? (string)$hash : null;
    }

    public function removeByEmail(string $email): bool
    {
        if (!$this->db->get('pp_newsletter', 'id', ['email' => $email])) {
            return false;
        }

        return (bool)$this->db->delete('pp_newsletter', ['email' => $email]);
    }

    public function signup(string $email, string $serverName, bool $ssl, array $settings): bool
    {
        if (!\Shared\Helpers\Helpers::email_check($email)) {
            return false;
        }

        if ($this->db->get('pp_newsletter', 'id', ['email' => $email])) {
            return false;
        }

        $hash = md5(time() . $email);

        $text = ($settings['newsletter_header'] ?? '');
        $text .= $this->templateByName('#potwierdzenie-zapisu-do-newslettera');
        $text .= ($settings['newsletter_footer'] ?? '');

        $base = $ssl ? 'https' : 'http';
        $link = '/newsletter/confirm/hash=' . $hash;
        $text = str_replace('[LINK]', $link, $text);
        $text = str_replace('[WYPISZ_SIE]', '', $text);

        $text = preg_replace(
            "-(<img[^>]+src\s*=\s*['\"])(((?!'|\"|https?://).)*)(['\"][^>]*>)-i",
            "$1" . $base . "://" . $serverName . "$2$4",
            $text
        );
        $text = preg_replace(
            "-(<a[^>]+href\s*=\s*['\"])(((?!'|\"|https?://).)*)(['\"][^>]*>)-i",
            "$1" . $base . "://" . $serverName . "$2$4",
            $text
        );

        $lang = \Shared\Helpers\Helpers::get_session('lang-' . \Shared\Helpers\Helpers::get_session('current-lang'));
        $subject = $lang['potwierdz-zapisanie-sie-do-newslettera'] ?? 'Newsletter';
        \Shared\Helpers\Helpers::send_email($email, $subject, $text);

        $this->db->insert('pp_newsletter', ['email' => $email, 'hash' => $hash, 'status' => 0]);

        return true;
    }

    public function sendQueued(int $limit, string $serverName, bool $ssl, string $unsubscribeLabel): bool
    {
        $settingsDetails = $this->settingsRepository->getSettings();

        $results = $this->db->query('SELECT * FROM pp_newsletter_send ORDER BY id ASC LIMIT ' . (int)$limit);
        $results = $results ? $results->fetchAll() : [];

        if (!is_array($results) || empty($results)) {
            return false;
        }

        $renderer = $this->getPreviewRenderer();
        $articleRepo = $this->getArticleRepository();

        foreach ($results as $row) {
            $dates = explode(' - ', $row['dates']);

            $articles = [];
            if (isset($dates[0], $dates[1])) {
                $articles = $articleRepo->articlesByDateAdd($dates[0], $dates[1]);
            }

            $text = $renderer->render(
                is_array($articles) ? $articles : [],
                $settingsDetails,
                $this->templateDetails((int)$row['id_template']),
                (string)$row['dates']
            );

            $base = $ssl ? 'https' : 'http';

            $text = preg_replace(
                "-(<img[^>]+src\s*=\s*['\"])(((?!'|\"|http://).)*)(['\"][^>]*>)-i",
                "$1" . $base . "://" . $serverName . "$2$4",
                $text
            );
            $text = preg_replace(
                "-(<a[^>]+href\s*=\s*['\"])(((?!'|\"|http://).)*)(['\"][^>]*>)-i",
                "$1" . $base . "://" . $serverName . "$2$4",
                $text
            );

            $hash = $this->getHashByEmail($row['email']);
            $link = $base . "://" . $serverName . '/newsletter/unsubscribe/hash=' . $hash;
            $text = str_replace('[WYPISZ_SIE]', '<a href="' . $link . '">' . $unsubscribeLabel . '</a>', $text);

            \Shared\Helpers\Helpers::send_email($row['email'], 'Newsletter ze strony: ' . $serverName, $text);

            $this->db->delete('pp_newsletter_send', ['id' => $row['id']]);
        }

        return true;
    }
}