<?php
namespace App\Services;

use App\Core\Database;
use PDO;
use RuntimeException;
use Throwable;

final class NotificationService
{
    private array $eventLabels = [
        'member_registered' => 'New member registration',
        'member_activation' => 'Member account activation email',
        'member_password_reset' => 'Member password reset email',
        'member_invited' => 'Member invited to walk',
        'reward_claimed' => 'Prize claim submitted',
        'reward_claim_status' => 'Prize claim status updated',
        'route_created' => 'Route created',
        'walk_created' => 'Walk created',
        'walk_registered' => 'Walk registration submitted',
        'vote_cast' => 'Photo vote cast',
        'photo_winner' => 'Photo contest winner',
        'prize_saved' => 'Prize created or updated',
        'points_adjusted' => 'Loyalty points adjusted',
        'photo_uploaded' => 'Photo uploaded for approval',
        'test_email' => 'Test email',
        'backup_created' => 'Database backup created',
        'backup_failed' => 'Database backup failed',
    ];

    public function eventCatalog(): array
    {
        $rows = [];
        foreach ($this->eventLabels as $key => $label) {
            $settings = $this->eventSettings($key);
            $rows[] = [
                'event_key' => $key,
                'label' => $label,
                'enabled' => (int)$settings['enabled'],
                'notify_admin' => (int)$settings['notify_admin'],
                'notify_user' => (int)$settings['notify_user'],
            ];
        }
        return $rows;
    }

    public function systemEnabled(): bool
    {
        return (bool)((int)$this->setting('email_notifications_enabled', '1'));
    }

    public function setting(string $key, string $default = ''): string
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT setting_value FROM app_settings WHERE setting_key = ? LIMIT 1');
            $stmt->execute([$key]);
            $value = $stmt->fetchColumn();
            return $value === false || $value === null ? $default : (string)$value;
        } catch (Throwable) {
            return $default;
        }
    }

    public function saveSetting(string $key, string $value): void
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare('INSERT INTO app_settings (setting_key, setting_value, created_at, updated_at) VALUES (?, ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_at = NOW()');
        $stmt->execute([$key, $value]);
    }

    public function eventSettings(string $eventKey): array
    {
        $defaults = [
            'enabled' => 1,
            'notify_admin' => 1,
            'notify_user' => in_array($eventKey, ['member_activation','member_password_reset','member_invited','photo_winner','reward_claim_status'], true) ? 1 : 0,
        ];

        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT enabled, notify_admin, notify_user FROM notification_event_settings WHERE event_key = ? LIMIT 1');
            $stmt->execute([$eventKey]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($row) {
                return [
                    'enabled' => (int)$row['enabled'],
                    'notify_admin' => (int)$row['notify_admin'],
                    'notify_user' => (int)$row['notify_user'],
                ];
            }
        } catch (Throwable) {
            // Migration may not be installed yet.
        }

        return $defaults;
    }

    public function saveEventSettings(string $eventKey, bool $enabled, bool $notifyAdmin, bool $notifyUser): void
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare('INSERT INTO notification_event_settings (event_key, label, enabled, notify_admin, notify_user, created_at, updated_at) VALUES (?, ?, ?, ?, ?, NOW(), NOW()) ON DUPLICATE KEY UPDATE label = VALUES(label), enabled = VALUES(enabled), notify_admin = VALUES(notify_admin), notify_user = VALUES(notify_user), updated_at = NOW()');
        $stmt->execute([$eventKey, $this->eventLabels[$eventKey] ?? $eventKey, $enabled ? 1 : 0, $notifyAdmin ? 1 : 0, $notifyUser ? 1 : 0]);
    }

    public function eventEnabled(string $eventKey, string $target = 'admin'): bool
    {
        if (!$this->systemEnabled()) {
            return false;
        }
        $settings = $this->eventSettings($eventKey);
        if (empty($settings['enabled'])) {
            return false;
        }
        if ($target === 'user') {
            return !empty($settings['notify_user']);
        }
        return !empty($settings['notify_admin']);
    }

    public function adminRecipients(): array
    {
        $custom = trim($this->setting('email_admin_recipients', ''));
        $recipients = [];

        if ($custom !== '') {
            foreach (preg_split('/[,;\n]+/', $custom) as $email) {
                $email = trim($email);
                if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $recipients[$email] = ['email' => $email, 'name' => 'Pyrk Admin'];
                }
            }
        }

        if (!$recipients) {
            try {
                $pdo = Database::connection();
                $stmt = $pdo->query('SELECT u.email, u.full_name FROM users u JOIN roles r ON r.id = u.role_id WHERE u.status = "active" AND r.name IN ("super_admin","event_manager") AND u.email IS NOT NULL AND u.email <> "" ORDER BY r.name = "super_admin" DESC, u.full_name ASC');
                foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                    if (filter_var((string)$row['email'], FILTER_VALIDATE_EMAIL)) {
                        $recipients[(string)$row['email']] = ['email' => (string)$row['email'], 'name' => (string)($row['full_name'] ?: 'Pyrk Admin')];
                    }
                }
            } catch (Throwable) {
                // ignore
            }
        }

        return array_values($recipients);
    }

    public function sendToAdmins(string $eventKey, string $subject, string $html, ?int $walkId = null, array $context = []): void
    {
        if (!$this->eventEnabled($eventKey, 'admin')) {
            return;
        }
        foreach ($this->adminRecipients() as $recipient) {
            $this->safeSendEmail(0, $walkId, $recipient['email'], $recipient['name'], $subject, $this->wrapTemplate($subject, $html, $context), strip_tags($html), $eventKey);
        }
    }

    public function sendToUser(string $eventKey, int $userId, string $subject, string $html, ?int $walkId = null, array $context = []): void
    {
        if (!$this->eventEnabled($eventKey, 'user')) {
            return;
        }

        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT id, full_name, email FROM users WHERE id = ? AND status = "active" LIMIT 1');
            $stmt->execute([$userId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$row || !filter_var((string)$row['email'], FILTER_VALIDATE_EMAIL)) {
                return;
            }
            $this->safeSendEmail((int)$row['id'], $walkId, (string)$row['email'], (string)$row['full_name'], $subject, $this->wrapTemplate($subject, $html, $context), strip_tags($html), $eventKey);
        } catch (Throwable $e) {
            $this->logEmail($eventKey, null, null, $subject, 'failed', $e->getMessage());
        }
    }

    public function sendEmail(int $userId, ?int $walkId, string $toEmail, string $toName, string $subject, string $html, string $text = ''): array
    {
        return $this->safeSendEmail($userId, $walkId, $toEmail, $toName, $subject, $this->wrapTemplate($subject, $html), $text ?: strip_tags($html), 'manual');
    }

    public function testEmail(string $toEmail): array
    {
        if (!filter_var($toEmail, FILTER_VALIDATE_EMAIL)) {
            return ['status' => 'failed', 'error' => 'Invalid email address.'];
        }
        return $this->safeSendEmail(0, null, $toEmail, 'Pyrk Admin', 'Pyrk email test', $this->wrapTemplate('Pyrk email test', '<p>Your email notification setup is working.</p><p>This can be SMTP or log mode depending on your config.</p>'), 'Your email notification setup is working.', 'test_email');
    }



    private function publicBaseUrl(): string
    {
        $configured = trim($this->setting('public_base_url', ''));
        if ($configured !== '') {
            $configured = rtrim($configured, '/') . '/';

            // The hosted project keeps public pages under /steps/public/.
            // If an older setting was saved as /steps/, normalize it so email links do not point to /steps/walk.php.
            $path = (string)(parse_url($configured, PHP_URL_PATH) ?: '');
            if (preg_match('#/steps/$#i', $path)) {
                return rtrim($configured, '/') . '/public/';
            }

            return $configured;
        }

        $https = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off')
            || (($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https');
        $scheme = $https ? 'https' : 'http';
        $host = $_SERVER['HTTP_HOST'] ?? ($_SERVER['SERVER_NAME'] ?? '');
        if ($host === '') return '';

        $script = str_replace('\\', '/', (string)($_SERVER['SCRIPT_NAME'] ?? ''));

        // API scripts run from /steps/api/... while public pages are /steps/public/...
        if (preg_match('#/api/[^/]*$#', $script)) {
            $base = preg_replace('#/api/[^/]*$#', '/public/', $script);
        } elseif (preg_match('#/admin/[^/]*$#', $script)) {
            $base = preg_replace('#/admin/[^/]*$#', '/', $script);
        } else {
            $base = rtrim(dirname($script), '/') . '/';
        }

        return rtrim($scheme . '://' . $host . $base, '/') . '/';
    }

    private function publicUrl(string $path): string
    {
        $base = $this->publicBaseUrl();
        if ($base === '') return $path;
        return $base . ltrim($path, '/');
    }

    private function adminUrl(string $path): string
    {
        return $this->publicUrl('admin/' . ltrim($path, '/'));
    }

    private function googleNavigationUrl(?float $lat, ?float $lng, string $label = ''): string
    {
        if ($lat === null || $lng === null || !is_finite($lat) || !is_finite($lng)) return '';
        $destination = $lat . ',' . $lng;
        return 'https://www.google.com/maps/dir/?api=1&travelmode=walking&destination=' . rawurlencode($destination);
    }

    private function routeGoogleNavigationUrl(int $routeId): string
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT latitude, longitude FROM route_points WHERE route_id = ? AND latitude IS NOT NULL AND longitude IS NOT NULL ORDER BY sort_order ASC, id ASC');
            $stmt->execute([$routeId]);
            $points = $stmt->fetchAll(PDO::FETCH_ASSOC);
            if (!$points) return '';

            $origin = $points[0];
            $destination = $points[count($points) - 1];
            $waypoints = array_slice($points, 1, -1);
            $url = 'https://www.google.com/maps/dir/?api=1&travelmode=walking'
                . '&origin=' . rawurlencode($origin['latitude'] . ',' . $origin['longitude'])
                . '&destination=' . rawurlencode($destination['latitude'] . ',' . $destination['longitude']);

            if ($waypoints) {
                $parts = array_map(static fn($p) => $p['latitude'] . ',' . $p['longitude'], array_slice($waypoints, 0, 9));
                $url .= '&waypoints=' . rawurlencode(implode('|', $parts));
            }

            return $url;
        } catch (Throwable) {
            return '';
        }
    }

    private function emailButton(string $url, string $label, string $background = '#1c7c54'): string
    {
        if (trim($url) === '') return '';
        return '<p style="margin:18px 0;"><a href="' . $this->e($url) . '" style="display:inline-block;background:' . $this->e($background) . ';color:#fff;text-decoration:none;padding:13px 20px;border-radius:12px;font-weight:bold;margin:4px 4px 4px 0;box-shadow:0 12px 24px rgba(28,124,84,.24);">' . $this->e($label) . '</a></p>';
    }


    public function walkEmailTemplateCatalog(): array
    {
        return [
            'member_invitation' => [
                'label' => 'Member walk invitation',
                'description' => 'Sent when you invite members to a specific walk.',
                'subject' => 'You are invited: {walk_title}',
                'body_html' => '<div style="margin:0 0 18px;padding:18px 18px 16px;border:1px solid #d1fae5;border-radius:18px;background:#ecfdf5;">'
                    . '<span style="display:inline-block;margin-bottom:10px;padding:5px 10px;border-radius:999px;background:#ffffff;color:#0f766e;font-size:12px;font-weight:700;letter-spacing:.04em;text-transform:uppercase;">Pyrk walk invitation</span>'
                    . '<h2 style="margin:0;color:#0f172a;font-size:24px;line-height:1.2;">You are invited to {walk_title}</h2>'
                    . '<p style="margin:10px 0 0;color:#334155;line-height:1.6;">Hi {member_name}, we are pleased to invite you to join this upcoming Pyrk walk.</p>'
                    . '</div>'
                    . '<div style="margin:16px 0;padding:16px;border:1px solid #e2e8f0;border-radius:16px;background:#ffffff;">'
                    . '<h3 style="margin:0 0 12px;color:#0f172a;font-size:18px;">Event details</h3>'
                    . '<strong>Date:</strong> {walk_date}<br>'
                    . '<strong>Time:</strong> {walk_time}<br>'
                    . '<strong>Route:</strong> {route_name}<br>'
                    . '<strong>Distance:</strong> {distance_km} KM'
                    . '</div>'
                    . '{walk_description_block}'
                    . '<div style="margin:16px 0;padding:14px 16px;border-left:4px solid #1c7c54;background:#f8fafc;border-radius:12px;color:#334155;line-height:1.6;">Please open the walk page to review the details, complete the required steps, and confirm your participation.</div>'
                    . '{walk_button}'
                    . '<p style="margin:18px 0 0;color:#334155;line-height:1.6;">See you there,<br><strong>{brand_name}</strong></p>',
            ],
            'walk_reminder' => [
                'label' => 'Registered member walk reminder',
                'description' => 'Sent by the reminder cron to members already registered for the walk.',
                'subject' => 'Walk reminder: {walk_title}',
                'body_html' => '<div style="margin:0 0 18px;padding:18px;border:1px solid #dbeafe;border-radius:18px;background:#eff6ff;">'
                    . '<span style="display:inline-block;margin-bottom:10px;padding:5px 10px;border-radius:999px;background:#ffffff;color:#1d4ed8;font-size:12px;font-weight:700;letter-spacing:.04em;text-transform:uppercase;">Walk reminder</span>'
                    . '<h2 style="margin:0;color:#0f172a;font-size:24px;line-height:1.2;">Your walk is coming up</h2>'
                    . '<p style="margin:10px 0 0;color:#334155;line-height:1.6;">Hi {member_name}, this is a friendly reminder for <strong>{walk_title}</strong>.</p>'
                    . '</div>'
                    . '<div style="margin:16px 0;padding:16px;border:1px solid #e2e8f0;border-radius:16px;background:#ffffff;">'
                    . '<h3 style="margin:0 0 12px;color:#0f172a;font-size:18px;">Walk details</h3>'
                    . '<strong>Date:</strong> {walk_date}<br>'
                    . '<strong>Time:</strong> {walk_time}<br>'
                    . '<strong>Route:</strong> {route_name}<br>'
                    . '<strong>Distance:</strong> {distance_km} KM'
                    . '</div>'
                    . '{walk_description_block}'
                    . '<div style="margin:16px 0;padding:14px 16px;border-left:4px solid #1c7c54;background:#f8fafc;border-radius:12px;color:#334155;line-height:1.6;">Please arrive 15 minutes early, wear comfortable shoes, and bring water.</div>'
                    . '{walk_button}',
            ],
        ];
    }

    public function walkEmailTemplateVariables(): array
    {
        return [
            '{member_name}' => 'Member full name',
            '{member_email}' => 'Member email address',
            '{walk_title}' => 'Walk title',
            '{walk_date}' => 'Walk date',
            '{walk_start_time}' => 'Start time',
            '{walk_end_time}' => 'Expected finish time',
            '{walk_time}' => 'Start and finish time',
            '{route_name}' => 'Route name',
            '{distance_km}' => 'Route distance in KM',
            '{walk_description}' => 'Walk description without formatting',
            '{walk_description_block}' => 'Formatted description block, hidden when empty',
            '{walk_link}' => 'Member walk page URL',
            '{walk_button}' => 'Professional green Open walk button',
            '{brand_name}' => 'Configured client brand name',
        ];
    }

    public function defaultWalkEmailTemplate(string $templateKey): array
    {
        $catalog = $this->walkEmailTemplateCatalog();
        return $catalog[$templateKey] ?? $catalog['member_invitation'];
    }

    public function renderWalkEmailContent(int $walkId, string $templateKey, array $member = [], array $walk = []): array
    {
        $template = $this->loadWalkEmailTemplate($walkId, $templateKey);
        if (!$template) {
            $template = $this->defaultWalkEmailTemplate($templateKey);
        }

        if (!$walk) {
            $walk = $this->loadWalkForEmail($walkId) ?: ['id' => $walkId];
        }

        $tokens = $this->walkEmailTokenMap($walkId, $member, $walk);
        $subject = trim($this->replaceTemplateTokens((string)($template['subject'] ?? ''), $tokens['subject']));
        $html = $this->replaceTemplateTokens((string)($template['body_html'] ?? ''), $tokens['html']);
        $html = $this->sanitizeTemplateHtml($html);

        if ($subject === '') {
            $subject = (string)$this->defaultWalkEmailTemplate($templateKey)['subject'];
            $subject = trim($this->replaceTemplateTokens($subject, $tokens['subject']));
        }
        if (trim(strip_tags($html)) === '' && strpos($html, '<a ') === false) {
            $html = $this->replaceTemplateTokens((string)$this->defaultWalkEmailTemplate($templateKey)['body_html'], $tokens['html']);
            $html = $this->sanitizeTemplateHtml($html);
        }

        return [
            'subject' => $subject,
            'html' => $html,
            'text' => $this->htmlToText($html),
            'template_key' => $templateKey,
        ];
    }

    public function renderWalkEmailContentFromRaw(int $walkId, string $templateKey, string $subject, string $bodyHtml, array $member = [], array $walk = []): array
    {
        if (!$walk) {
            $walk = $this->loadWalkForEmail($walkId) ?: ['id' => $walkId];
        }

        $defaults = $this->defaultWalkEmailTemplate($templateKey);
        $tokens = $this->walkEmailTokenMap($walkId, $member, $walk);

        $subject = trim($subject) !== '' ? $subject : (string)($defaults['subject'] ?? 'Pyrk walk');
        $bodyHtml = trim($bodyHtml) !== '' ? $bodyHtml : (string)($defaults['body_html'] ?? '');

        $renderedSubject = trim($this->replaceTemplateTokens($subject, $tokens['subject']));
        $renderedHtml = $this->replaceTemplateTokens($bodyHtml, $tokens['html']);
        $renderedHtml = $this->sanitizeTemplateHtml($renderedHtml);

        if ($renderedSubject === '') {
            $renderedSubject = trim($this->replaceTemplateTokens((string)($defaults['subject'] ?? 'Pyrk walk'), $tokens['subject']));
        }
        if (trim(strip_tags($renderedHtml)) === '' && strpos($renderedHtml, '<a ') === false) {
            $renderedHtml = $this->replaceTemplateTokens((string)($defaults['body_html'] ?? ''), $tokens['html']);
            $renderedHtml = $this->sanitizeTemplateHtml($renderedHtml);
        }

        return [
            'subject' => $renderedSubject,
            'html' => $renderedHtml,
            'text' => $this->htmlToText($renderedHtml),
            'template_key' => $templateKey,
        ];
    }

    private function loadWalkEmailTemplate(int $walkId, string $templateKey): ?array
    {
        try {
            if (!$this->tableExists('walk_email_templates')) return null;
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT template_key, subject, body_html, is_enabled FROM walk_email_templates WHERE walk_id = ? AND template_key = ? LIMIT 1');
            $stmt->execute([$walkId, $templateKey]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$row || !(int)($row['is_enabled'] ?? 1)) return null;
            return $row;
        } catch (Throwable) {
            return null;
        }
    }

    private function loadWalkForEmail(int $walkId): ?array
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare("SELECT w.*, r.route_name, r.total_km, COALESCE(NULLIF(w.description, ''), r.description, '') AS email_description FROM walks w LEFT JOIN routes r ON r.id = w.route_id WHERE w.id = ? LIMIT 1");
            $stmt->execute([$walkId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            return $row ?: null;
        } catch (Throwable) {
            return null;
        }
    }

    private function walkEmailTokenMap(int $walkId, array $member, array $walk): array
    {
        $name = (string)($member['full_name'] ?? $member['name'] ?? 'Member');
        $email = (string)($member['email'] ?? '');
        $title = (string)($walk['title'] ?? 'Pyrk walk');
        $walkDate = $this->formatDate((string)($walk['walk_date'] ?? ''));
        $start = substr((string)($walk['start_time'] ?? ''), 0, 5);
        $end = substr((string)($walk['expected_finish_time'] ?? ''), 0, 5);
        $time = trim($start . ($end !== '' ? '–' . $end : ''));
        $route = (string)($walk['route_name'] ?? '');
        $distance = number_format((float)($walk['total_km'] ?? $walk['distance_km'] ?? 0), 1);
        $description = trim((string)($walk['email_description'] ?? $walk['description'] ?? ''));
        $walkUrl = $this->publicUrl('walk.php?id=' . $walkId);
        $brand = $this->setting('header_client_brand_name', 'Pyrk');

        $plain = [
            '{member_name}' => $name,
            '{member_email}' => $email,
            '{walk_title}' => $title,
            '{walk_date}' => $walkDate,
            '{walk_start_time}' => $start,
            '{walk_end_time}' => $end,
            '{walk_time}' => $time,
            '{route_name}' => $route,
            '{distance_km}' => $distance,
            '{walk_description}' => $description,
            '{walk_description_block}' => $description,
            '{walk_link}' => $walkUrl,
            '{walk_button}' => $walkUrl,
            '{brand_name}' => $brand,
        ];

        $html = [];
        foreach ($plain as $key => $value) {
            $html[$key] = $this->e((string)$value);
        }
        $html['{walk_description_block}'] = $description !== ''
            ? '<div style="margin:16px 0;padding:14px 16px;background:#f8fafc;border:1px solid #e2e8f0;border-radius:14px;color:#334155;line-height:1.6;"><strong style="color:#0f172a;">Walk note</strong><br>' . nl2br($this->e($description)) . '</div>'
            : '';
        $html['{walk_button}'] = $this->emailButton($walkUrl, 'Open walk');

        return ['subject' => $plain, 'html' => $html, 'plain' => $plain];
    }

    private function replaceTemplateTokens(string $content, array $tokens): string
    {
        return strtr($content, $tokens);
    }

    private function sanitizeTemplateHtml(string $html): string
    {
        $html = trim($html);
        if ($html === '') return '';
        $allowed = '<p><br><strong><b><em><i><u><ul><ol><li><a><h2><h3><h4><blockquote><div><span><hr>';
        $html = strip_tags($html, $allowed);
        $html = preg_replace('/\son\w+\s*=\s*(".*?"|\'.*?\'|[^\s>]+)/i', '', $html);
        $html = preg_replace('/href\s*=\s*([\'\"])\s*javascript:.*?\1/i', 'href="#"', $html);
        $html = preg_replace_callback('/<a\b([^>]*)>/i', static function ($m) {
            $attrs = $m[1] ?? '';
            if (!preg_match('/\bhref\s*=/i', $attrs)) return '<a href="#">';
            if (!preg_match('/\btarget\s*=/i', $attrs)) $attrs .= ' target="_blank"';
            if (!preg_match('/\brel\s*=/i', $attrs)) $attrs .= ' rel="noopener noreferrer"';
            return '<a' . $attrs . '>';
        }, $html);
        return $html;
    }

    private function htmlToText(string $html): string
    {
        $text = preg_replace('/<\s*br\s*\/?\s*>/i', "\n", $html);
        $text = preg_replace('/<\/\s*(p|div|h2|h3|h4|li|blockquote)\s*>/i', "\n", (string)$text);
        $text = trim(html_entity_decode(strip_tags((string)$text), ENT_QUOTES, 'UTF-8'));
        $text = preg_replace("/[ \t]+\n/", "\n", $text);
        $text = preg_replace("/\n{3,}/", "\n\n", (string)$text);
        return $text ?: trim(strip_tags($html));
    }


    public function notifyMemberRegistered(int $userId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT u.id, u.full_name, u.email, u.mobile, u.created_at, mp.date_of_birth, mp.gender, mp.emergency_contact_mobile FROM users u LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE u.id = ? LIMIT 1');
            $stmt->execute([$userId]);
            $member = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$member) return;

            $subject = 'New member registration: ' . $member['full_name'];
            $memberAdminUrl = $this->adminUrl('members.php?open=' . (int)$userId);
            $activationRequired = in_array(strtolower($this->setting('member_activation_required', '0')), ['1','true','yes','on'], true);
            $html = '<p>A new member registered' . ($activationRequired ? ' and is waiting for email activation.' : '. The account is active immediately because activation links are disabled.') . '</p>'
                . '<p><strong>Name:</strong> ' . $this->e($member['full_name']) . '<br>'
                . '<strong>Email:</strong> ' . $this->e($member['email']) . '<br>'
                . '<strong>Mobile:</strong> ' . $this->e((string)$member['mobile']) . '<br>'
                . '<strong>Date of birth:</strong> ' . $this->e($this->formatDate((string)($member['date_of_birth'] ?? ''))) . '<br>'
                . '<strong>Gender:</strong> ' . $this->e((string)($member['gender'] ?? '')) . '</p>'
                . $this->emailButton($memberAdminUrl, 'Open member in admin')
                . ($activationRequired ? '<p>If the member does not activate within 2 hours, the account will stay inactive until a new activation link is sent.</p>' : '<p>No activation link was sent for this registration.</p>');
            $this->sendToAdmins('member_registered', $subject, $html, null, ['user_id' => $userId]);
        } catch (Throwable $e) {
            $this->logEmail('member_registered', null, null, 'Registration notification failed', 'failed', $e->getMessage());
        }
    }

    public function sendActivationEmail(int $userId, string $activationUrl, string $expiresAt): array
    {
        $subject = 'Activate your Pyrk account';

        try {
            if (!$this->systemEnabled()) {
                return ['status' => 'disabled', 'error' => 'Email notifications are disabled.'];
            }

            $pdo = Database::connection();
            // IMPORTANT: new members are inactive until activation.
            // Do NOT use sendToUser() here because sendToUser() intentionally loads only active users.
            $stmt = $pdo->prepare('SELECT id, full_name, email FROM users WHERE id = ? LIMIT 1');
            $stmt->execute([$userId]);
            $member = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$member || !filter_var((string)($member['email'] ?? ''), FILTER_VALIDATE_EMAIL)) {
                return ['status' => 'failed', 'error' => 'Member email address is missing or invalid.'];
            }

            $html = '<p>Hi ' . $this->e((string)$member['full_name']) . ',</p>'
                . '<p>Welcome to Pyrk Walking Club. Please activate your account within <strong>2 hours</strong>.</p>'
                . '<p><a href="' . $this->e($activationUrl) . '" style="display:inline-block;background:#1c7c54;color:#fff;text-decoration:none;padding:12px 18px;border-radius:12px;font-weight:bold;">Activate my account</a></p>'
                . '<p>If the button does not work, copy this link into your browser:</p>'
                . '<p style="word-break:break-all;color:#334155;">' . $this->e($activationUrl) . '</p>'
                . '<p><strong>Expiry:</strong> ' . $this->e($this->formatDateTime($expiresAt)) . '</p>';

            return $this->safeSendEmail(
                (int)$member['id'],
                null,
                (string)$member['email'],
                (string)$member['full_name'],
                $subject,
                $this->wrapTemplate($subject, $html, ['activation_expires_at' => $expiresAt]),
                strip_tags($html),
                'member_activation'
            );
        } catch (Throwable $e) {
            $this->logEmail('member_activation', null, null, $subject, 'failed', $e->getMessage());
            return ['status' => 'failed', 'error' => $e->getMessage()];
        }
    }


    public function sendPasswordResetEmail(int $userId, string $resetUrl, string $expiresAt): array
    {
        $subject = 'Reset your Pyrk password';

        try {
            // Password reset is a security/authentication email. It should not depend on
            // optional notification-event toggles; only the global email system can disable it.
            if (!$this->systemEnabled()) {
                return ['status' => 'disabled', 'error' => 'Email notifications are disabled.'];
            }

            $pdo = Database::connection();
            // Password reset can be sent to active or inactive members. Do not use sendToUser()
            // because sendToUser() intentionally loads only active users.
            $stmt = $pdo->prepare('SELECT u.id, u.full_name, u.email, u.status, r.name AS role_name
                                   FROM users u
                                   JOIN roles r ON r.id = u.role_id
                                   WHERE u.id = ?
                                   LIMIT 1');
            $stmt->execute([$userId]);
            $member = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$member || ($member['role_name'] ?? '') !== 'member') {
                return ['status' => 'failed', 'error' => 'Only member accounts can use this password reset email.'];
            }

            if (!filter_var((string)($member['email'] ?? ''), FILTER_VALIDATE_EMAIL)) {
                return ['status' => 'failed', 'error' => 'Member email address is missing or invalid.'];
            }

            if (($member['status'] ?? '') === 'blocked') {
                return ['status' => 'failed', 'error' => 'Member account is blocked.'];
            }

            $html = '<p>Hi ' . $this->e((string)$member['full_name']) . ',</p>'
                . '<p>We received a request to reset your Pyrk account password.</p>'
                . '<p>This secure link expires in <strong>45 minutes</strong>. If you did not request this, you can safely ignore this email.</p>'
                . $this->emailButton($resetUrl, 'Reset my password')
                . '<p>If the button does not work, copy this link into your browser:</p>'
                . '<p style="word-break:break-all;color:#334155;">' . $this->e($resetUrl) . '</p>'
                . '<p><strong>Expiry:</strong> ' . $this->e($this->formatDateTime($expiresAt)) . '</p>';

            return $this->safeSendEmail(
                (int)$member['id'],
                null,
                (string)$member['email'],
                (string)$member['full_name'],
                $subject,
                $this->wrapTemplate($subject, $html, ['password_reset_expires_at' => $expiresAt]),
                strip_tags($html),
                'member_password_reset'
            );
        } catch (Throwable $e) {
            $this->logEmail('member_password_reset', null, null, $subject, 'failed', $e->getMessage());
            return ['status' => 'failed', 'error' => $e->getMessage()];
        }
    }

    public function notifyWalkInvitations(int $walkId, int $invitedBy): array
    {
        $stats = ['status' => 'ok', 'sent' => 0, 'logged' => 0, 'failed' => 0, 'skipped' => 0, 'members' => []];

        if (!$this->eventEnabled('member_invited', 'user')) {
            $stats['status'] = 'disabled';
            return $stats;
        }

        try {
            $pdo = Database::connection();
            $walkStmt = $pdo->prepare('SELECT w.*, r.route_name, r.total_km FROM walks w JOIN routes r ON r.id = w.route_id WHERE w.id = ? LIMIT 1');
            $walkStmt->execute([$walkId]);
            $walk = $walkStmt->fetch(PDO::FETCH_ASSOC);
            if (!$walk) {
                $stats['status'] = 'missing_walk';
                return $stats;
            }

            $hasNotifyCols = $this->columnExists('walk_invitations', 'notified_at') && $this->columnExists('walk_invitations', 'notified_email_status');

            $sql = 'SELECT DISTINCT u.id, u.full_name, u.email
                    FROM users u
                    JOIN roles ro ON ro.id = u.role_id
                    LEFT JOIN walk_invitations wi_direct ON wi_direct.user_id = u.id AND wi_direct.walk_id = ? AND wi_direct.status = "invited"
                    LEFT JOIN member_group_members mgm ON mgm.user_id = u.id
                    LEFT JOIN walk_invitations wi_group ON wi_group.group_id = mgm.group_id AND wi_group.walk_id = ? AND wi_group.status = "invited"
                    WHERE ro.name = "member"
                      AND u.status = "active"
                      AND (wi_direct.id IS NOT NULL OR wi_group.id IS NOT NULL)';
            if ($hasNotifyCols) {
                $sql .= ' AND (wi_direct.notified_at IS NULL OR wi_group.notified_at IS NULL)';
            }
            $sql .= ' ORDER BY u.full_name ASC';
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$walkId, $walkId]);
            $members = $stmt->fetchAll(PDO::FETCH_ASSOC);

            if (!$members) {
                $stats['status'] = 'no_new_invitees';
                return $stats;
            }

            foreach ($members as $member) {
                if (!filter_var((string)($member['email'] ?? ''), FILTER_VALIDATE_EMAIL)) {
                    $stats['skipped']++;
                    continue;
                }

                $rendered = $this->renderWalkEmailContent((int)$walkId, 'member_invitation', $member, $walk);
                $subject = $rendered['subject'];
                $html = $rendered['html'];
                $text = $rendered['text'];

                $result = $this->safeSendEmail(
                    (int)$member['id'],
                    (int)$walkId,
                    (string)$member['email'],
                    (string)$member['full_name'],
                    $subject,
                    $this->wrapTemplate($subject, $html, ['walk_id' => $walkId, 'invited_by' => $invitedBy]),
                    $text,
                    'member_invited'
                );

                $status = (string)($result['status'] ?? 'failed');
                if ($status === 'sent') {
                    $stats['sent']++;
                } elseif ($status === 'logged') {
                    $stats['logged']++;
                } else {
                    $stats['failed']++;
                }
                $stats['members'][] = ['user_id' => (int)$member['id'], 'email' => (string)$member['email'], 'status' => $status];
            }

            if ($hasNotifyCols) {
                $ids = array_values(array_unique(array_map(static function($m) { return (int)$m['id']; }, $members)));
                if ($ids) {
                    $placeholders = implode(',', array_fill(0, count($ids), '?'));
                    $markSql = "UPDATE walk_invitations wi
                                SET wi.notified_at = NOW(),
                                    wi.notified_email_status = CASE WHEN wi.notified_email_status = 'failed' THEN 'failed' ELSE 'sent' END,
                                    wi.updated_at = NOW()
                                WHERE wi.walk_id = ?
                                  AND wi.status = 'invited'
                                  AND wi.notified_at IS NULL
                                  AND (
                                    wi.user_id IN ({$placeholders})
                                    OR wi.group_id IN (SELECT mgm.group_id FROM member_group_members mgm WHERE mgm.user_id IN ({$placeholders}))
                                  )";
                    $pdo->prepare($markSql)->execute(array_merge([$walkId], $ids, $ids));
                }
            }

            return $stats;
        } catch (Throwable $e) {
            $this->logEmail('member_invited', null, null, 'Invitation notification failed', 'failed', $e->getMessage());
            $stats['status'] = 'failed';
            $stats['failed']++;
            $stats['error'] = $e->getMessage();
            return $stats;
        }
    }

    public function notifyRewardClaim(int $claimId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT ur.*, rw.reward_name, u.full_name, u.email FROM user_rewards ur JOIN rewards rw ON rw.id = ur.reward_id JOIN users u ON u.id = ur.user_id WHERE ur.id = ? LIMIT 1');
            $stmt->execute([$claimId]);
            $claim = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$claim) return;
            $subject = 'New prize claim: ' . $claim['reward_name'];
            $html = '<p><strong>' . $this->e($claim['full_name']) . '</strong> claimed <strong>' . $this->e($claim['reward_name']) . '</strong>.</p>'
                . '<p><strong>Points spent:</strong> ' . (int)$claim['points_spent'] . '<br><strong>Status:</strong> ' . $this->e($claim['status']) . '</p>'
                . '<p>Open Admin &gt; Rewards &gt; Prizes & claims to approve or fulfil it.</p>';
            $this->sendToAdmins('reward_claimed', $subject, $html, null, ['claim_id' => $claimId]);
        } catch (Throwable $e) {
            $this->logEmail('reward_claimed', null, null, 'Reward claim notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyRewardClaimStatus(int $claimId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT ur.*, rw.reward_name, u.id AS user_id, u.full_name FROM user_rewards ur JOIN rewards rw ON rw.id = ur.reward_id JOIN users u ON u.id = ur.user_id WHERE ur.id = ? LIMIT 1');
            $stmt->execute([$claimId]);
            $claim = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$claim) return;
            $subject = 'Prize claim update: ' . $claim['reward_name'];
            $html = '<p>Hi ' . $this->e($claim['full_name']) . ',</p><p>Your claim for <strong>' . $this->e($claim['reward_name']) . '</strong> is now <strong>' . $this->e($claim['status']) . '</strong>.</p>';
            $this->sendToUser('reward_claim_status', (int)$claim['user_id'], $subject, $html, null);
        } catch (Throwable $e) {
            $this->logEmail('reward_claim_status', null, null, 'Reward claim status notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyRouteCreated(int $routeId, int $actorId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT r.*, u.full_name AS actor_name FROM routes r LEFT JOIN users u ON u.id = ? WHERE r.id = ? LIMIT 1');
            $stmt->execute([$actorId, $routeId]);
            $route = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$route) return;
            $subject = 'New route created: ' . $route['route_name'];
            $html = '<p><strong>' . $this->e($route['actor_name'] ?: 'A Pyrk admin') . '</strong> created a route.</p>'
                . '<p><strong>Route:</strong> ' . $this->e($route['route_name']) . '<br>'
                . '<strong>Distance:</strong> ' . number_format((float)$route['total_km'], 1) . ' KM<br>'
                . '<strong>Difficulty:</strong> ' . $this->e($route['difficulty']) . '</p>';
            $this->sendToAdmins('route_created', $subject, $html, null, ['route_id' => $routeId]);
        } catch (Throwable $e) {
            $this->logEmail('route_created', null, null, 'Route notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyWalkCreated(int $walkId, int $actorId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT w.*, r.route_name, u.full_name AS actor_name FROM walks w JOIN routes r ON r.id = w.route_id LEFT JOIN users u ON u.id = ? WHERE w.id = ? LIMIT 1');
            $stmt->execute([$actorId, $walkId]);
            $walk = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$walk) return;
            $subject = 'New walk scheduled: ' . $walk['title'];
            $walkAdminUrl = $this->adminUrl('walks.php');
            $walkClientUrl = $this->publicUrl('walk.php?id=' . (int)$walkId);
            $navUrl = $this->routeGoogleNavigationUrl((int)$walk['route_id']);
            $html = '<p><strong>' . $this->e($walk['actor_name'] ?: 'A Pyrk admin') . '</strong> scheduled a walk.</p>'
                . '<p><strong>Walk:</strong> ' . $this->e($walk['title']) . '<br>'
                . '<strong>Date:</strong> ' . $this->e($walk['walk_date']) . ' ' . $this->e(substr((string)$walk['start_time'], 0, 5)) . '<br>'
                . '<strong>Access:</strong> ' . $this->e(str_replace('_', ' ', (string)$walk['access_type'])) . '</p>'
                . $this->emailButton($walkAdminUrl, 'Open walk admin')
                . $this->emailButton($walkClientUrl, 'Open public walk')
                . $this->emailButton($navUrl, 'Open route in Google Maps', '#2563eb');
            $this->sendToAdmins('walk_created', $subject, $html, $walkId, ['walk_id' => $walkId]);
        } catch (Throwable $e) {
            $this->logEmail('walk_created', null, null, 'Walk notification failed', 'failed', $e->getMessage());
        }
    }


    public function notifyWalkRegistered(int $walkId, int $userId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT
                    w.id AS walk_id, w.title AS walk_title, w.walk_date, w.start_time, w.payment_model, w.payment_amount, w.payment_currency,
                    r.id AS route_id, r.route_name, r.total_km,
                    u.id AS user_id, u.full_name, u.email, u.mobile,
                    wr.id AS registration_id, wr.registration_status, wr.registered_at
                FROM walk_registrations wr
                JOIN walks w ON w.id = wr.walk_id
                JOIN routes r ON r.id = w.route_id
                JOIN users u ON u.id = wr.user_id
                WHERE wr.walk_id = ? AND wr.user_id = ?
                LIMIT 1');
            $stmt->execute([$walkId, $userId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$row) return;

            $subject = 'New walk registration: ' . $row['walk_title'];
            $adminUrl = $this->adminUrl('walks.php');
            $paymentsUrl = $this->adminUrl('payments.php?walk_id=' . (int)$walkId);
            $clientUrl = $this->publicUrl('walk.php?id=' . (int)$walkId);
            $navUrl = $this->routeGoogleNavigationUrl((int)$row['route_id']);

            $paymentLine = '';
            if (($row['payment_model'] ?? 'free') === 'paid') {
                $paymentLine = '<br><strong>Fee:</strong> ' . number_format((float)$row['payment_amount'], 2) . ' ' . $this->e((string)$row['payment_currency']);
            }

            $html = '<p><strong>' . $this->e($row['full_name']) . '</strong> registered for a walk.</p>'
                . '<p><strong>Walk:</strong> ' . $this->e($row['walk_title']) . '<br>'
                . '<strong>Date:</strong> ' . $this->e($this->formatDate((string)$row['walk_date'])) . ' ' . $this->e(substr((string)$row['start_time'], 0, 5)) . '<br>'
                . '<strong>Route:</strong> ' . $this->e($row['route_name']) . '<br>'
                . '<strong>Member email:</strong> ' . $this->e($row['email']) . '<br>'
                . '<strong>Mobile:</strong> ' . $this->e((string)$row['mobile'])
                . $paymentLine . '</p>'
                . $this->emailButton($adminUrl, 'Open walk admin')
                . (($row['payment_model'] ?? 'free') === 'paid' ? $this->emailButton($paymentsUrl, 'Open payment page', '#7c3aed') : '')
                . $this->emailButton($clientUrl, 'Open public walk')
                . $this->emailButton($navUrl, 'Open route in Google Maps', '#2563eb');

            $this->sendToAdmins('walk_registered', $subject, $html, $walkId, [
                'walk_id' => $walkId,
                'user_id' => $userId,
                'registration_id' => (int)($row['registration_id'] ?? 0),
            ]);
        } catch (Throwable $e) {
            $this->logEmail('walk_registered', null, null, 'Walk registration notification failed', 'failed', $e->getMessage());
        }
    }


    public function notifyVoteCast(int $photoId, int $voterId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT p.id, p.title, p.walk_id, p.user_id AS photo_owner_id, owner.full_name AS owner_name, voter.full_name AS voter_name, w.title AS walk_title FROM walk_photos p JOIN walks w ON w.id = p.walk_id JOIN users owner ON owner.id = p.user_id JOIN users voter ON voter.id = ? WHERE p.id = ? LIMIT 1');
            $stmt->execute([$voterId, $photoId]);
            $vote = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$vote) return;
            $subject = 'New photo vote: ' . $vote['walk_title'];
            $html = '<p><strong>' . $this->e($vote['voter_name']) . '</strong> voted for <strong>' . $this->e($vote['title']) . '</strong>.</p>'
                . '<p><strong>Walk:</strong> ' . $this->e($vote['walk_title']) . '<br><strong>Photo owner:</strong> ' . $this->e($vote['owner_name']) . '</p>';
            $this->sendToAdmins('vote_cast', $subject, $html, (int)$vote['walk_id'], ['photo_id' => $photoId]);
        } catch (Throwable $e) {
            $this->logEmail('vote_cast', null, null, 'Vote notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyPhotoWinner(int $walkId, int $winnerUserId, int $photoId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT p.title AS photo_title, p.votes_count, w.title AS walk_title, u.full_name AS winner_name FROM walk_photos p JOIN walks w ON w.id = p.walk_id JOIN users u ON u.id = p.user_id WHERE p.id = ? LIMIT 1');
            $stmt->execute([$photoId]);
            $winner = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$winner) return;
            $subject = 'Photo contest winner: ' . $winner['walk_title'];
            $html = '<p><strong>' . $this->e($winner['winner_name']) . '</strong> won the photo contest.</p>'
                . '<p><strong>Walk:</strong> ' . $this->e($winner['walk_title']) . '<br>'
                . '<strong>Photo:</strong> ' . $this->e($winner['photo_title']) . '<br>'
                . '<strong>Votes:</strong> ' . (int)$winner['votes_count'] . '</p>';
            $this->sendToAdmins('photo_winner', $subject, $html, $walkId, ['photo_id' => $photoId]);
            $this->sendToUser('photo_winner', $winnerUserId, 'Congratulations, you won the photo contest!', '<p>Congratulations ' . $this->e($winner['winner_name']) . '!</p><p>Your photo <strong>' . $this->e($winner['photo_title']) . '</strong> won the Pyrk photo contest for <strong>' . $this->e($winner['walk_title']) . '</strong>.</p>', $walkId);
        } catch (Throwable $e) {
            $this->logEmail('photo_winner', null, null, 'Winner notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyPrizeSaved(int $rewardId, int $actorId, bool $updated): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT rw.*, u.full_name AS actor_name FROM rewards rw LEFT JOIN users u ON u.id = ? WHERE rw.id = ? LIMIT 1');
            $stmt->execute([$actorId, $rewardId]);
            $reward = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$reward) return;
            $subject = ($updated ? 'Prize updated: ' : 'Prize created: ') . $reward['reward_name'];
            $html = '<p><strong>' . $this->e($reward['actor_name'] ?: 'A Pyrk admin') . '</strong> ' . ($updated ? 'updated' : 'created') . ' a prize.</p>'
                . '<p><strong>Prize:</strong> ' . $this->e($reward['reward_name']) . '<br>'
                . '<strong>Cost:</strong> ' . (int)$reward['points_cost'] . ' points<br>'
                . '<strong>Status:</strong> ' . $this->e($reward['status']) . '</p>';
            $this->sendToAdmins('prize_saved', $subject, $html, null, ['reward_id' => $rewardId]);
        } catch (Throwable $e) {
            $this->logEmail('prize_saved', null, null, 'Prize notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyPointsAdjusted(int $memberId, int $points, string $description, int $actorId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT member.full_name AS member_name, actor.full_name AS actor_name FROM users member LEFT JOIN users actor ON actor.id = ? WHERE member.id = ? LIMIT 1');
            $stmt->execute([$actorId, $memberId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$row) return;
            $subject = 'Loyalty points adjusted: ' . $row['member_name'];
            $html = '<p><strong>' . $this->e($row['actor_name'] ?: 'A Pyrk admin') . '</strong> adjusted loyalty points.</p>'
                . '<p><strong>Member:</strong> ' . $this->e($row['member_name']) . '<br>'
                . '<strong>Points:</strong> ' . ($points > 0 ? '+' : '') . (int)$points . '<br>'
                . '<strong>Reason:</strong> ' . $this->e($description) . '</p>';
            $this->sendToAdmins('points_adjusted', $subject, $html, null, ['member_id' => $memberId]);
        } catch (Throwable $e) {
            $this->logEmail('points_adjusted', null, null, 'Points notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyPhotoUploaded(int $photoId): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT p.*, u.full_name, w.title AS walk_title FROM walk_photos p JOIN users u ON u.id = p.user_id JOIN walks w ON w.id = p.walk_id WHERE p.id = ? LIMIT 1');
            $stmt->execute([$photoId]);
            $photo = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$photo) return;
            $subject = 'New photo uploaded: ' . $photo['walk_title'];
            $html = '<p><strong>' . $this->e($photo['full_name']) . '</strong> uploaded a photo for <strong>' . $this->e($photo['walk_title']) . '</strong>.</p>'
                . '<p><strong>Photo:</strong> ' . $this->e($photo['title']) . '<br><strong>Status:</strong> ' . $this->e($photo['status']) . '</p>'
                . '<p>Open Admin &gt; Operations &gt; Photo contest to approve or review it.</p>';
            $this->sendToAdmins('photo_uploaded', $subject, $html, (int)$photo['walk_id'], ['photo_id' => $photoId]);
        } catch (Throwable $e) {
            $this->logEmail('photo_uploaded', null, null, 'Photo upload notification failed', 'failed', $e->getMessage());
        }
    }

    public function notifyBackupResult(bool $success, string $message, ?string $filePath = null): void
    {
        $event = $success ? 'backup_created' : 'backup_failed';
        $subject = $success ? 'Pyrk database backup created' : 'Pyrk database backup failed';
        $html = '<p>' . $this->e($message) . '</p>';
        if ($filePath) {
            $html .= '<p><strong>File:</strong> ' . $this->e(basename($filePath)) . '</p>';
        }
        $this->sendToAdmins($event, $subject, $html, null, ['file' => $filePath]);
    }

    public function sendWhatsApp(int $userId, ?int $walkId, string $mobile, string $message): array
    {
        $wa = $GLOBALS['config']['whatsapp'] ?? [];
        if (!($wa['enabled'] ?? false)) {
            $this->logMessage('whatsapp', $mobile, $message);
            $this->insertNotification($userId, $walkId, 'whatsapp', null, $message, 'logged', null);
            return ['status' => 'logged', 'error' => null];
        }

        $driver = strtolower(trim((string)($wa['driver'] ?? $wa['provider'] ?? 'cloud_api')));
        try {
            if ($driver === 'ultramsg') {
                $result = (new UltraMsgService())->sendText($mobile, $message, 'pyrk-user-' . $userId . '-' . time());
                $this->insertNotification($userId, $walkId, 'whatsapp', null, $message, 'sent', null);
                return [
                    'status' => 'sent',
                    'error' => null,
                    'provider' => 'ultramsg',
                    'provider_message_id' => $result['provider_message_id'] ?? null,
                ];
            }

            $this->sendWhatsAppCloudApi($mobile, $message);
            $this->insertNotification($userId, $walkId, 'whatsapp', null, $message, 'sent', null);
            return ['status' => 'sent', 'error' => null, 'provider' => 'cloud_api'];
        } catch (Throwable $e) {
            $this->insertNotification($userId, $walkId, 'whatsapp', null, $message, 'failed', $e->getMessage());
            return ['status' => 'failed', 'error' => $e->getMessage(), 'provider' => $driver];
        }
    }

    public function createInApp(int $userId, ?int $walkId, string $subject, string $message): void
    {
        $this->insertNotification($userId, $walkId, 'in_app', $subject, $message, 'sent', null);
    }


    public function effectiveMailConfig(): array
    {
        $cfg = $GLOBALS['config']['mail'] ?? [];

        $driver = $this->setting('mail_driver', (string)($cfg['driver'] ?? 'log'));
        if (!in_array($driver, ['log', 'mail', 'smtp'], true)) {
            $driver = 'log';
        }

        $fromEmail = $this->setting('mail_from_email', (string)($cfg['from_email'] ?? 'club@pyrk.local'));
        $fromName = $this->setting('mail_from_name', (string)($cfg['from_name'] ?? 'Pyrk'));

        $smtpDefaults = $cfg['smtp'] ?? [];

        return [
            'driver' => $driver,
            'from_email' => $fromEmail ?: 'club@pyrk.local',
            'from_name' => $fromName ?: 'Pyrk',
            'smtp' => [
                'host' => $this->setting('smtp_host', (string)($smtpDefaults['host'] ?? '')),
                'port' => (int)$this->setting('smtp_port', (string)($smtpDefaults['port'] ?? '587')),
                'username' => $this->setting('smtp_username', (string)($smtpDefaults['username'] ?? '')),
                'password' => $this->setting('smtp_password', (string)($smtpDefaults['password'] ?? '')),
                'encryption' => $this->setting('smtp_encryption', (string)($smtpDefaults['encryption'] ?? 'tls')),
            ],
        ];
    }

    private function safeSendEmail(int $userId, ?int $walkId, string $toEmail, string $toName, string $subject, string $html, string $text = '', string $eventKey = 'manual'): array
    {
        $status = 'failed';
        $error = null;

        try {
            if (!$this->systemEnabled() && $eventKey !== 'test_email') {
                return ['status' => 'disabled', 'error' => null];
            }

            $mail = $this->effectiveMailConfig();
            $driver = $mail['driver'] ?? 'log';
            if ($driver === 'smtp') {
                $this->sendSmtp($toEmail, $toName, $subject, $html, $text);
                $status = 'sent';
            } elseif ($driver === 'mail') {
                $headers = "MIME-Version: 1.0\r\nContent-Type: text/html; charset=UTF-8\r\n";
                $from = ($mail['from_name'] ?? 'Pyrk') . ' <' . ($mail['from_email'] ?? 'club@pyrk.local') . '>';
                $headers .= "From: {$from}\r\n";
                if (!mail($toEmail, $subject, $html, $headers)) {
                    throw new RuntimeException('PHP mail() returned false.');
                }
                $status = 'sent';
            } else {
                $this->logMessage('email', $toEmail, $subject . "\n" . strip_tags($html));
                $status = 'logged';
            }

            $this->insertNotification($userId, $walkId, 'email', $subject, strip_tags($html), $status, null);
            $this->logEmail($eventKey, $toEmail, $toName, $subject, $status, null);
        } catch (Throwable $e) {
            $error = $e->getMessage();
            $this->insertNotification($userId, $walkId, 'email', $subject, strip_tags($html), 'failed', $error);
            $this->logEmail($eventKey, $toEmail, $toName, $subject, 'failed', $error);
        }

        return ['status' => $status, 'error' => $error];
    }

    private function sendSmtp(string $toEmail, string $toName, string $subject, string $html, string $text = ''): void
    {
        $cfg = $this->effectiveMailConfig();
        $smtp = $cfg['smtp'] ?? [];

        $host = trim((string)($smtp['host'] ?? ''));
        $username = trim((string)($smtp['username'] ?? ''));
        $password = (string)($smtp['password'] ?? '');
        $port = (int)($smtp['port'] ?? 587);
        $encryption = (string)($smtp['encryption'] ?? 'tls');

        if ($host === '') {
            throw new RuntimeException('SMTP host is missing. Configure it in Admin > Operations > Email & backup center.');
        }
        if ($username === '') {
            throw new RuntimeException('SMTP username is missing.');
        }
        if ($password === '') {
            throw new RuntimeException('SMTP password is missing.');
        }

        // If PHPMailer exists, use it. If not, use the built-in SMTP sender below.
        $autoload = __DIR__ . '/../../vendor/autoload.php';
        if (file_exists($autoload)) {
            require_once $autoload;
        }

        if (class_exists('PHPMailer\\PHPMailer\\PHPMailer')) {
            $mailer = new \PHPMailer\PHPMailer\PHPMailer(true);
            $mailer->isSMTP();
            $mailer->Host = $host;
            $mailer->SMTPAuth = true;
            $mailer->Username = $username;
            $mailer->Password = $password;

            if ($encryption === 'ssl') {
                $mailer->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_SMTPS;
            } elseif ($encryption === 'tls') {
                $mailer->SMTPSecure = \PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
            } else {
                $mailer->SMTPSecure = false;
                $mailer->SMTPAutoTLS = false;
            }

            $mailer->Port = $port ?: 587;
            $mailer->CharSet = 'UTF-8';
            $mailer->setFrom((string)$cfg['from_email'], (string)$cfg['from_name']);
            $mailer->addAddress($toEmail, $toName);
            $mailer->isHTML(true);
            $mailer->Subject = $subject;
            $mailer->Body = $html;
            $mailer->AltBody = $text ?: strip_tags($html);
            $mailer->send();
            return;
        }

        $this->sendNativeSmtp($cfg, $toEmail, $toName, $subject, $html, $text ?: strip_tags($html));
    }

    private function sendNativeSmtp(array $cfg, string $toEmail, string $toName, string $subject, string $html, string $text): void
    {
        $smtp = $cfg['smtp'] ?? [];
        $host = trim((string)($smtp['host'] ?? ''));
        $port = (int)($smtp['port'] ?? 587);
        $username = trim((string)($smtp['username'] ?? ''));
        $password = (string)($smtp['password'] ?? '');
        $encryption = (string)($smtp['encryption'] ?? 'tls');

        $remote = ($encryption === 'ssl' ? 'ssl://' : 'tcp://') . $host . ':' . ($port ?: ($encryption === 'ssl' ? 465 : 587));
        $context = stream_context_create([
            'ssl' => [
                'verify_peer' => true,
                'verify_peer_name' => true,
                'allow_self_signed' => false,
                'SNI_enabled' => true,
                'peer_name' => $host,
            ],
        ]);

        $socket = @stream_socket_client($remote, $errno, $errstr, 25, STREAM_CLIENT_CONNECT, $context);
        if (!$socket) {
            throw new RuntimeException('SMTP connection failed: ' . trim($errstr ?: ('error ' . $errno)));
        }

        stream_set_timeout($socket, 25);

        try {
            $this->smtpExpect($socket, [220], 'SMTP greeting failed');

            $serverName = $_SERVER['SERVER_NAME'] ?? 'localhost';
            $this->smtpCommand($socket, 'EHLO ' . $serverName, [250], 'SMTP EHLO failed');

            if ($encryption === 'tls') {
                $this->smtpCommand($socket, 'STARTTLS', [220], 'SMTP STARTTLS failed');

                $cryptoMethods = STREAM_CRYPTO_METHOD_TLS_CLIENT;
                if (defined('STREAM_CRYPTO_METHOD_TLSv1_2_CLIENT')) {
                    $cryptoMethods |= STREAM_CRYPTO_METHOD_TLSv1_2_CLIENT;
                }
                if (defined('STREAM_CRYPTO_METHOD_TLSv1_3_CLIENT')) {
                    $cryptoMethods |= STREAM_CRYPTO_METHOD_TLSv1_3_CLIENT;
                }

                if (!@stream_socket_enable_crypto($socket, true, $cryptoMethods)) {
                    throw new RuntimeException('SMTP TLS negotiation failed.');
                }

                $this->smtpCommand($socket, 'EHLO ' . $serverName, [250], 'SMTP EHLO after TLS failed');
            }

            $this->smtpCommand($socket, 'AUTH LOGIN', [334], 'SMTP AUTH LOGIN not accepted');
            $this->smtpCommand($socket, base64_encode($username), [334], 'SMTP username not accepted');
            $this->smtpCommand($socket, base64_encode($password), [235], 'SMTP password not accepted');

            $fromEmail = (string)($cfg['from_email'] ?? $username);
            $fromName = (string)($cfg['from_name'] ?? 'Pyrk Walking Club');

            $this->smtpCommand($socket, 'MAIL FROM:<' . $this->smtpAddress($fromEmail) . '>', [250], 'SMTP sender rejected');
            $this->smtpCommand($socket, 'RCPT TO:<' . $this->smtpAddress($toEmail) . '>', [250, 251], 'SMTP recipient rejected');
            $this->smtpCommand($socket, 'DATA', [354], 'SMTP DATA not accepted');

            $message = $this->buildSmtpMessage($fromEmail, $fromName, $toEmail, $toName, $subject, $html, $text);
            fwrite($socket, $this->smtpDotStuff($message) . "\r\n.\r\n");
            $this->smtpExpect($socket, [250], 'SMTP message send failed');

            $this->smtpCommand($socket, 'QUIT', [221, 250], 'SMTP QUIT failed');
        } finally {
            if (is_resource($socket)) {
                fclose($socket);
            }
        }
    }

    private function smtpAddress(string $email): string
    {
        $email = trim($email);
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            throw new RuntimeException('Invalid email address used for SMTP: ' . $email);
        }
        return $email;
    }

    private function smtpCommand($socket, string $command, array $expectedCodes, string $errorMessage): string
    {
        fwrite($socket, $command . "\r\n");
        return $this->smtpExpect($socket, $expectedCodes, $errorMessage);
    }

    private function smtpExpect($socket, array $expectedCodes, string $errorMessage): string
    {
        $response = '';
        while (($line = fgets($socket, 515)) !== false) {
            $response .= $line;
            if (preg_match('/^\d{3}\s/', $line)) {
                break;
            }
        }

        if ($response === '') {
            $meta = stream_get_meta_data($socket);
            $suffix = !empty($meta['timed_out']) ? ' Connection timed out.' : '';
            throw new RuntimeException($errorMessage . '.' . $suffix);
        }

        $code = (int)substr($response, 0, 3);
        if (!in_array($code, $expectedCodes, true)) {
            $clean = trim(preg_replace('/\s+/', ' ', $response));
            throw new RuntimeException($errorMessage . ': ' . $clean);
        }

        return $response;
    }

    private function buildSmtpMessage(string $fromEmail, string $fromName, string $toEmail, string $toName, string $subject, string $html, string $text): string
    {
        $boundary = 'pyrk_' . bin2hex(random_bytes(12));
        $headers = [
            'Date: ' . date(DATE_RFC2822),
            'From: ' . $this->mimeAddress($fromName, $fromEmail),
            'To: ' . $this->mimeAddress($toName, $toEmail),
            'Subject: ' . $this->mimeHeader($subject),
            'MIME-Version: 1.0',
            'Content-Type: multipart/alternative; boundary="' . $boundary . '"',
            'X-Mailer: Pyrk Native SMTP',
        ];

        return implode("\r\n", $headers)
            . "\r\n\r\n--" . $boundary
            . "\r\nContent-Type: text/plain; charset=UTF-8"
            . "\r\nContent-Transfer-Encoding: quoted-printable\r\n\r\n"
            . quoted_printable_encode($text)
            . "\r\n--" . $boundary
            . "\r\nContent-Type: text/html; charset=UTF-8"
            . "\r\nContent-Transfer-Encoding: quoted-printable\r\n\r\n"
            . quoted_printable_encode($html)
            . "\r\n--" . $boundary . "--\r\n";
    }

    private function mimeHeader(string $value): string
    {
        if (function_exists('mb_encode_mimeheader')) {
            return mb_encode_mimeheader($value, 'UTF-8', 'B', "\r\n");
        }
        return '=?UTF-8?B?' . base64_encode($value) . '?=';
    }

    private function mimeAddress(string $name, string $email): string
    {
        $email = $this->smtpAddress($email);
        $name = trim($name);
        if ($name === '') return '<' . $email . '>';
        return $this->mimeHeader($name) . ' <' . $email . '>';
    }

    private function smtpDotStuff(string $message): string
    {
        $message = str_replace(["\r\n", "\r"], "\n", $message);
        $message = preg_replace('/^\./m', '..', $message);
        return str_replace("\n", "\r\n", $message);
    }

    private function sendWhatsAppCloudApi(string $mobile, string $message): void
    {
        $wa = $GLOBALS['config']['whatsapp'] ?? [];
        foreach (['phone_number_id', 'access_token', 'api_version'] as $field) {
            if (empty($wa[$field])) {
                throw new RuntimeException('Missing WhatsApp config: ' . $field);
            }
        }
        $mobile = preg_replace('/[^0-9]/', '', $mobile);
        if (!$mobile) {
            throw new RuntimeException('Invalid WhatsApp mobile number. Use international format, e.g. 96170123456.');
        }
        $url = 'https://graph.facebook.com/' . rawurlencode($wa['api_version']) . '/' . rawurlencode($wa['phone_number_id']) . '/messages';
        $payload = [
            'messaging_product' => 'whatsapp',
            'to' => $mobile,
            'type' => 'text',
            'text' => ['preview_url' => false, 'body' => $message],
        ];
        $ch = curl_init($url);
        curl_setopt_array($ch, [
            CURLOPT_POST => true,
            CURLOPT_HTTPHEADER => [
                'Authorization: Bearer ' . $wa['access_token'],
                'Content-Type: application/json',
            ],
            CURLOPT_POSTFIELDS => json_encode($payload, JSON_UNESCAPED_UNICODE),
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT => 20,
        ]);
        $body = curl_exec($ch);
        $code = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $err = curl_error($ch);
        curl_close($ch);
        if ($body === false || $code < 200 || $code >= 300) {
            throw new RuntimeException('WhatsApp API failed: HTTP ' . $code . ' ' . ($body ?: $err));
        }
    }

    private function insertNotification(int $userId, ?int $walkId, string $channel, ?string $subject, string $message, string $status, ?string $error): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('INSERT INTO notifications (user_id, walk_id, channel, subject, message, status, scheduled_at, sent_at, error_message, created_at) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW(), ?, NOW())');
            $stmt->execute([$userId, $walkId, $channel, $subject, $message, $status, $error]);
        } catch (Throwable) {
            // Notification insert should never break the business workflow.
        }
    }

    private function logEmail(string $eventKey, ?string $toEmail, ?string $toName, string $subject, string $status, ?string $error): void
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('INSERT INTO notification_email_logs (event_key, to_email, to_name, subject, status, error_message, created_at) VALUES (?, ?, ?, ?, ?, ?, NOW())');
            $stmt->execute([$eventKey, $toEmail, $toName, $subject, $status, $error]);
        } catch (Throwable) {
            // migration may not be installed
        }
    }

    private function wrapTemplate(string $title, string $html, array $context = []): string
    {
        $brand = $this->setting('header_client_brand_name', 'Pyrk');
        $footer = $this->setting('email_footer_text', 'This is an automated notification from Pyrk Walking Club.');
        return '<!doctype html><html><body style="margin:0;background:#f4f7fb;font-family:Arial,sans-serif;color:#0f172a;">'
            . '<table width="100%" cellpadding="0" cellspacing="0" style="background:#f4f7fb;padding:24px;"><tr><td align="center">'
            . '<table width="100%" cellpadding="0" cellspacing="0" style="max-width:640px;background:#ffffff;border-radius:20px;overflow:hidden;border:1px solid #e5e7eb;">'
            . '<tr><td style="padding:22px 26px;background:linear-gradient(135deg,#0f172a,#1c7c54);color:#fff;"><strong style="font-size:18px;">' . $this->e($brand) . '</strong><div style="opacity:.82;margin-top:4px;">Walking Club</div></td></tr>'
            . '<tr><td style="padding:26px;"><h2 style="margin:0 0 16px;font-size:24px;">' . $this->e($title) . '</h2><div style="font-size:15px;line-height:1.6;color:#334155;">' . $html . '</div></td></tr>'
            . '<tr><td style="padding:16px 26px;background:#f8fafc;color:#64748b;font-size:12px;">' . $this->e($footer) . '</td></tr>'
            . '</table></td></tr></table></body></html>';
    }

    private function logMessage(string $channel, string $to, string $message): void
    {
        $dir = __DIR__ . '/../../storage/logs';
        if (!is_dir($dir)) mkdir($dir, 0775, true);
        file_put_contents($dir . '/notifications.log', '[' . date('c') . "] {$channel} to {$to}: {$message}\n", FILE_APPEND);
    }


    private function tableExists(string $table): bool
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?');
            $stmt->execute([$table]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Throwable) {
            return false;
        }
    }


    private function columnExists(string $table, string $column): bool
    {
        try {
            $pdo = Database::connection();
            $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?');
            $stmt->execute([$table, $column]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (Throwable) {
            return false;
        }
    }

    private function formatDate(string $value): string
    {
        if ($value === '') return '';
        $date = substr($value, 0, 10);
        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $date, $m)) {
            return $m[3] . '/' . $m[2] . '/' . $m[1];
        }
        return $value;
    }

    private function formatDateTime(string $value): string
    {
        if ($value === '') return '';
        $date = $this->formatDate(substr($value, 0, 10));
        $time = substr($value, 11, 5);
        return trim($date . ' ' . $time);
    }

    private function e(string $value): string
    {
        return htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
    }
}
