<?php
require __DIR__ . '/bootstrap.php';

use App\Core\Auth;
use App\Core\Csrf;
use App\Core\Database;
use App\Core\Response;
use App\Core\Security;
use App\Services\NotificationService;
use App\Services\NativePushService;
use App\Services\UltraMsgService;
use App\Services\WhatsAppQueueService;

$user = Auth::requireUser();
if (!Auth::hasPermission('notifications.manage', $user)) {
    Response::error('You do not have permission to send member notifications.', 403);
}

$pdo = Database::connection();
$input = request_input();
if ($_POST) {
    $input = array_merge($input, $_POST);
}
$method = $_SERVER['REQUEST_METHOD'];
$action = $input['action'] ?? 'overview';

function pyrk_manual_notif_clean_html(string $html): string
{
    $html = trim($html);
    if ($html === '') return '';

    $allowed = '<p><br><strong><b><em><i><u><ul><ol><li><a><h3><h4><blockquote>';
    $html = strip_tags($html, $allowed);
    $html = preg_replace('/\son\w+\s*=\s*(".*?"|\'.*?\'|[^\s>]+)/i', '', $html);
    $html = preg_replace('/\sstyle\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="#">';
        $attrs = preg_replace('/\s(target|rel)\s*=\s*(".*?"|\'.*?\'|[^\s>]+)/i', '', $attrs);
        return '<a' . $attrs . ' target="_blank" rel="noopener noreferrer">';
    }, $html);

    return $html;
}

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

function pyrk_manual_notif_table_exists(PDO $pdo, string $table): bool
{
    static $cache = [];
    if (array_key_exists($table, $cache)) return $cache[$table];

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

function pyrk_manual_notif_column_exists(PDO $pdo, string $table, string $column): bool
{
    static $cache = [];
    $key = $table . '.' . $column;
    if (array_key_exists($key, $cache)) return $cache[$key];

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

function pyrk_manual_notif_ids(mixed $value): array
{
    if (is_string($value)) {
        $decoded = json_decode($value, true);
        if (is_array($decoded)) {
            $value = $decoded;
        } else {
            $value = preg_split('/\s*,\s*/', $value);
        }
    }
    if (!is_array($value)) $value = [];
    return array_values(array_unique(array_filter(array_map('intval', $value))));
}

function pyrk_manual_notif_members(PDO $pdo): array
{
    $stmt = $pdo->query('SELECT u.id, u.full_name, u.email, u.mobile, u.status
        FROM users u
        JOIN roles r ON r.id = u.role_id
        WHERE r.name = "member" AND u.status = "active"
        ORDER BY u.full_name ASC');
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function pyrk_manual_notif_groups(PDO $pdo): array
{
    try {
        $stmt = $pdo->query('SELECT g.id, g.group_name, g.description, g.status,
            (SELECT COUNT(*) FROM member_group_members mgm JOIN users u ON u.id = mgm.user_id JOIN roles r ON r.id = u.role_id WHERE mgm.group_id = g.id AND r.name = "member" AND u.status = "active") AS member_count
            FROM member_groups g
            ORDER BY g.status = "active" DESC, g.group_name ASC');
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable) {
        return [];
    }
}

function pyrk_manual_notif_visible_walk_sql(PDO $pdo, string $alias = 'w'): string
{
    $a = preg_replace('/[^a-zA-Z0-9_]/', '', $alias) ?: 'w';
    $parts = ['LOWER(TRIM(COALESCE(' . $a . '.status, ""))) NOT IN ("deleted","archived","removed","remove","trash","trashed","cancelled","canceled","inactive","hidden")'];

    if (pyrk_manual_notif_column_exists($pdo, 'walks', 'deleted_at')) $parts[] = $a . '.deleted_at IS NULL';
    if (pyrk_manual_notif_table_exists($pdo, 'audit_logs')) {
        $parts[] = 'NOT EXISTS (
            SELECT 1 FROM audit_logs al
            WHERE al.entity_type = "walks"
              AND al.entity_id = ' . $a . '.id
              AND al.action IN ("walk_deleted","walk_removed","walk_archived")
        )';
    }

    return '(' . implode(' AND ', $parts) . ')';
}

function pyrk_manual_notif_walks(PDO $pdo): array
{
    try {
        $sql = 'SELECT w.id, w.title, w.walk_date, w.start_time, w.status,
            (SELECT COUNT(*) FROM walk_registrations wr WHERE wr.walk_id = w.id AND wr.registration_status IN ("registered","approved")) AS registered_count
            FROM walks w
            WHERE w.walk_date >= CURDATE() AND ' . pyrk_manual_notif_visible_walk_sql($pdo, 'w') . '
            ORDER BY w.walk_date ASC, w.start_time ASC
            LIMIT 200';
        return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable) {
        return [];
    }
}

function pyrk_manual_notif_recipients(PDO $pdo, string $scope, array $memberIds, array $groupIds, int $walkId = 0): array
{
    $params = [];

    if ($scope === 'selected_members') {
        if (!$memberIds) return [];
        $placeholders = implode(',', array_fill(0, count($memberIds), '?'));
        $sql = "SELECT DISTINCT u.id, u.full_name, u.email, u.mobile
            FROM users u
            JOIN roles r ON r.id = u.role_id
            WHERE u.id IN ($placeholders) AND r.name = 'member' AND u.status = 'active'
            ORDER BY u.full_name ASC";
        $params = $memberIds;
    } elseif ($scope === 'selected_groups') {
        if (!$groupIds) return [];
        $placeholders = implode(',', array_fill(0, count($groupIds), '?'));
        $sql = "SELECT DISTINCT u.id, u.full_name, u.email, u.mobile
            FROM member_group_members mgm
            JOIN member_groups g ON g.id = mgm.group_id
            JOIN users u ON u.id = mgm.user_id
            JOIN roles r ON r.id = u.role_id
            WHERE mgm.group_id IN ($placeholders) AND g.status = 'active' AND r.name = 'member' AND u.status = 'active'
            ORDER BY u.full_name ASC";
        $params = $groupIds;
    } elseif ($scope === 'walk_registrants') {
        if ($walkId <= 0) return [];
        $sql = "SELECT DISTINCT u.id, u.full_name, u.email, u.mobile
            FROM walk_registrations wr
            JOIN users u ON u.id = wr.user_id
            JOIN roles r ON r.id = u.role_id
            JOIN walks w ON w.id = wr.walk_id
            WHERE wr.walk_id = ?
              AND wr.registration_status IN ('registered','approved')
              AND r.name = 'member'
              AND u.status = 'active'
              AND " . pyrk_manual_notif_visible_walk_sql($pdo, 'w') . "
            ORDER BY u.full_name ASC";
        $params = [$walkId];
    } else {
        $sql = "SELECT DISTINCT u.id, u.full_name, u.email, u.mobile
            FROM users u
            JOIN roles r ON r.id = u.role_id
            WHERE r.name = 'member' AND u.status = 'active'
            ORDER BY u.full_name ASC";
    }

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $out = [];
    $ultraMsg = new UltraMsgService();
    foreach ($rows as $row) {
        $rawMobile = trim((string)($row['mobile'] ?? ''));
        $normalizedMobile = $ultraMsg->normalizeMobile($rawMobile);
        $out[(int)$row['id']] = [
            'id' => (int)$row['id'],
            'full_name' => (string)($row['full_name'] ?: 'Member'),
            'email' => (string)($row['email'] ?? ''),
            'mobile' => $normalizedMobile ?? $rawMobile,
            'has_email' => filter_var((string)($row['email'] ?? ''), FILTER_VALIDATE_EMAIL) ? 1 : 0,
            'has_whatsapp' => $normalizedMobile !== null ? 1 : 0,
        ];
    }

    return array_values($out);
}

function pyrk_manual_notif_batches(PDO $pdo): array
{
    return pyrk_manual_notif_history($pdo, ['limit' => 60])['rows'];
}

function pyrk_manual_notif_history(PDO $pdo, array $filters = []): array
{
    if (!pyrk_manual_notif_table_exists($pdo, 'manual_notification_batches')) return ['rows' => [], 'total' => 0];

    $hasWalk = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'walk_id');
    $hasWhatsapp = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'delivery_whatsapp');
    $hasWhatsappSent = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'whatsapp_sent_count');
    $hasWhatsappFailed = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'whatsapp_failed_count');
    $hasWhatsappQueued = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'whatsapp_queued_count');
    $hasNative = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'delivery_native');
    $hasNativeSent = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'native_sent_count');
    $hasNativeFailed = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'native_failed_count');

    $select = 'SELECT b.*, u.full_name AS created_by_name'
        . ($hasWalk ? ', w.title AS walk_title, w.walk_date AS walk_date' : ', NULL AS walk_title, NULL AS walk_date')
        . ($hasWhatsapp ? '' : ', 0 AS delivery_whatsapp')
        . ($hasWhatsappSent ? '' : ', 0 AS whatsapp_sent_count')
        . ($hasWhatsappFailed ? '' : ', 0 AS whatsapp_failed_count')
        . ($hasWhatsappQueued ? '' : ', 0 AS whatsapp_queued_count')
        . ($hasNative ? '' : ', 0 AS delivery_native')
        . ($hasNativeSent ? '' : ', 0 AS native_sent_count')
        . ($hasNativeFailed ? '' : ', 0 AS native_failed_count')
        . ', (SELECT COUNT(*) FROM manual_notification_batch_recipients r WHERE r.batch_id = b.id) AS recipient_log_count'
        . (pyrk_manual_notif_table_exists($pdo, 'manual_notification_attachments')
            ? ', (SELECT COUNT(*) FROM manual_notification_attachments a WHERE a.batch_id = b.id) AS attachment_count'
            : ', 0 AS attachment_count');

    $from = ' FROM manual_notification_batches b LEFT JOIN users u ON u.id = b.created_by'
        . ($hasWalk ? ' LEFT JOIN walks w ON w.id = b.walk_id' : '');

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

    $q = trim((string)($filters['q'] ?? ''));
    if ($q !== '') {
        $where[] = '(b.subject LIKE ? OR b.message LIKE ? OR u.full_name LIKE ?' . ($hasWalk ? ' OR w.title LIKE ?' : '') . ')';
        $like = '%' . $q . '%';
        $params[] = $like;
        $params[] = $like;
        $params[] = $like;
        if ($hasWalk) $params[] = $like;
    }

    $channel = strtolower(trim((string)($filters['channel'] ?? '')));
    if ($channel === 'web') {
        $where[] = 'b.delivery_web = 1';
    } elseif ($channel === 'email') {
        $where[] = 'b.delivery_email = 1';
    } elseif ($channel === 'whatsapp' && $hasWhatsapp) {
        $where[] = 'b.delivery_whatsapp = 1';
    } elseif ($channel === 'native' && $hasNative) {
        $where[] = 'b.delivery_native = 1';
    }

    $audience = trim((string)($filters['audience'] ?? ''));
    if (in_array($audience, ['all_members','selected_groups','selected_members','walk_registrants'], true)) {
        $where[] = 'b.target_scope = ?';
        $params[] = $audience;
    }

    $walkId = (int)($filters['walk_id'] ?? 0);
    if ($walkId > 0 && $hasWalk) {
        $where[] = 'b.walk_id = ?';
        $params[] = $walkId;
    }

    $fromDate = trim((string)($filters['date_from'] ?? ''));
    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $fromDate)) {
        $where[] = 'b.created_at >= ?';
        $params[] = $fromDate . ' 00:00:00';
    }

    $toDate = trim((string)($filters['date_to'] ?? ''));
    if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $toDate)) {
        $where[] = 'b.created_at <= ?';
        $params[] = $toDate . ' 23:59:59';
    }

    $limit = (int)($filters['limit'] ?? 200);
    if ($limit < 1 || $limit > 500) $limit = 200;

    $sql = $select . $from . ' WHERE ' . implode(' AND ', $where) . ' ORDER BY b.created_at DESC, b.id DESC LIMIT ' . $limit;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);

    return ['rows' => $stmt->fetchAll(PDO::FETCH_ASSOC), 'total' => $stmt->rowCount()];
}

function pyrk_manual_notif_batch_detail(PDO $pdo, int $batchId): array
{
    if ($batchId <= 0) Response::error('Notification batch is required.');

    $history = pyrk_manual_notif_history($pdo, ['limit' => 1]);
    $hasWalk = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'walk_id');
    $hasWhatsapp = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'delivery_whatsapp');
    $hasWhatsappSent = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'whatsapp_sent_count');
    $hasWhatsappFailed = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'whatsapp_failed_count');
    $hasWhatsappQueued = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'whatsapp_queued_count');
    $hasNative = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'delivery_native');
    $hasNativeSent = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'native_sent_count');
    $hasNativeFailed = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', 'native_failed_count');

    $select = 'SELECT b.*, u.full_name AS created_by_name'
        . ($hasWalk ? ', w.title AS walk_title, w.walk_date AS walk_date' : ', NULL AS walk_title, NULL AS walk_date')
        . ($hasWhatsapp ? '' : ', 0 AS delivery_whatsapp')
        . ($hasWhatsappSent ? '' : ', 0 AS whatsapp_sent_count')
        . ($hasWhatsappFailed ? '' : ', 0 AS whatsapp_failed_count')
        . ($hasWhatsappQueued ? '' : ', 0 AS whatsapp_queued_count')
        . ($hasNative ? '' : ', 0 AS delivery_native')
        . ($hasNativeSent ? '' : ', 0 AS native_sent_count')
        . ($hasNativeFailed ? '' : ', 0 AS native_failed_count');
    $from = ' FROM manual_notification_batches b LEFT JOIN users u ON u.id = b.created_by'
        . ($hasWalk ? ' LEFT JOIN walks w ON w.id = b.walk_id' : '');
    $stmt = $pdo->prepare($select . $from . ' WHERE b.id = ? LIMIT 1');
    $stmt->execute([$batchId]);
    $batch = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$batch) Response::error('Notification batch not found.', 404);

    $hasMobile = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batch_recipients', 'mobile');
    $hasWaStatus = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batch_recipients', 'whatsapp_status');
    $hasNativeStatus = pyrk_manual_notif_column_exists($pdo, 'manual_notification_batch_recipients', 'native_status');
    $recSql = 'SELECT r.*, u.full_name, u.mobile AS user_mobile'
        . ($hasMobile ? '' : ', NULL AS mobile')
        . ($hasWaStatus ? '' : ', NULL AS whatsapp_status')
        . ($hasNativeStatus ? '' : ', NULL AS native_status')
        . ' FROM manual_notification_batch_recipients r
            LEFT JOIN users u ON u.id = r.user_id
            WHERE r.batch_id = ?
            ORDER BY r.id ASC
            LIMIT 500';
    $rec = $pdo->prepare($recSql);
    $rec->execute([$batchId]);

    $attachments = [];
    if (pyrk_manual_notif_table_exists($pdo, 'manual_notification_attachments')) {
        $att = $pdo->prepare('SELECT id, original_name, file_path, mime_type, size_bytes, created_at FROM manual_notification_attachments WHERE batch_id = ? ORDER BY id ASC');
        $att->execute([$batchId]);
        $attachments = $att->fetchAll(PDO::FETCH_ASSOC);
    }

    return [
        'batch' => $batch,
        'recipients' => $rec->fetchAll(PDO::FETCH_ASSOC),
        'attachments' => $attachments,
    ];
}


function pyrk_manual_notif_require_tables(PDO $pdo): void
{
    if (!pyrk_manual_notif_table_exists($pdo, 'notifications') || !pyrk_manual_notif_table_exists($pdo, 'manual_notification_batches')) {
        Response::error('Manual notification tables are not installed yet. Please run database/migrations/032_manual_push_notifications.sql');
    }
}

function pyrk_manual_public_base_url(): string
{
    $https = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off') || (($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https');
    $scheme = $https ? 'https' : 'http';
    $host = $_SERVER['HTTP_HOST'] ?? 'localhost';
    $scriptDir = str_replace('\\', '/', dirname($_SERVER['SCRIPT_NAME'] ?? '/api/manual_notifications.php'));
    $root = rtrim(dirname($scriptDir), '/');
    if ($root === '/' || $root === '.') $root = '';
    return $scheme . '://' . $host . $root . '/public/';
}

function pyrk_manual_push_allowed_tokens(): array
{
    return ['{first_name}','{last_name}','{full_name}','{walk_title}','{walk_date}','{walk_time}','{route_name}','{distance_km}','{walk_link}'];
}

function pyrk_manual_push_tokens(string $value): array
{
    preg_match_all('/\{[a-zA-Z0-9_]+\}|\$[a-zA-Z0-9_]+\$/', $value, $matches);
    return array_values(array_unique($matches[0] ?? []));
}

function pyrk_manual_push_unknown_tokens(string ...$values): array
{
    $found = [];
    foreach ($values as $value) $found = array_merge($found, pyrk_manual_push_tokens($value));
    return array_values(array_diff(array_unique($found), pyrk_manual_push_allowed_tokens()));
}

function pyrk_manual_push_walk_tokens_used(string ...$values): array
{
    $walkTokens = ['{walk_title}','{walk_date}','{walk_time}','{route_name}','{distance_km}','{walk_link}'];
    $found = [];
    foreach ($values as $value) $found = array_merge($found, pyrk_manual_push_tokens($value));
    return array_values(array_intersect(array_unique($found), $walkTokens));
}

function pyrk_manual_push_walk(PDO $pdo, int $walkId): ?array
{
    if ($walkId <= 0) return null;
    $stmt = $pdo->prepare('SELECT w.id, w.title, w.walk_date, w.start_time, r.route_name, r.total_km FROM walks w JOIN routes r ON r.id = w.route_id WHERE w.id = ? LIMIT 1');
    $stmt->execute([$walkId]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}

function pyrk_manual_push_name_parts(string $fullName): array
{
    $parts = preg_split('/\s+/u', trim($fullName), -1, PREG_SPLIT_NO_EMPTY) ?: [];
    $first = (string)($parts[0] ?? 'Member');
    $last = count($parts) > 1 ? implode(' ', array_slice($parts, 1)) : '';
    return ['first_name' => $first, 'last_name' => $last, 'full_name' => trim($fullName) ?: $first];
}

function pyrk_manual_push_date(string $value): string
{
    if (trim($value) === '') return '';
    try { return (new DateTimeImmutable($value))->format('d/m/Y'); }
    catch (Throwable) { return trim($value); }
}

function pyrk_manual_push_variables(array $recipient, ?array $walk, int $walkId, bool $htmlSafe = false): array
{
    $names = pyrk_manual_push_name_parts((string)($recipient['full_name'] ?? 'Member'));
    $distance = $walk ? rtrim(rtrim(number_format((float)($walk['total_km'] ?? 0), 2, '.', ''), '0'), '.') : '';
    $values = [
        '{first_name}' => $names['first_name'],
        '{last_name}' => $names['last_name'],
        '{full_name}' => $names['full_name'],
        '{walk_title}' => trim((string)($walk['title'] ?? '')),
        '{walk_date}' => pyrk_manual_push_date((string)($walk['walk_date'] ?? '')),
        '{walk_time}' => substr((string)($walk['start_time'] ?? ''), 0, 5),
        '{route_name}' => trim((string)($walk['route_name'] ?? '')),
        '{distance_km}' => $distance,
        '{walk_link}' => $walkId > 0 ? pyrk_manual_public_base_url() . 'walk.php?id=' . $walkId : '',
    ];
    if ($htmlSafe) {
        foreach ($values as $token => $value) $values[$token] = htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
    return $values;
}

function pyrk_manual_push_validate_templates(string $subject, string $message, string $messageHtml, ?array $walk): void
{
    $unknown = pyrk_manual_push_unknown_tokens($subject, $message, $messageHtml);
    if ($unknown) Response::error('Unknown variable(s): ' . implode(', ', $unknown) . '. Use only the approved Push preset variables.');
    $walkTokens = pyrk_manual_push_walk_tokens_used($subject, $message, $messageHtml);
    if ($walkTokens && !$walk) Response::error('A valid walk must be selected to resolve: ' . implode(', ', $walkTokens) . '.');
}

function pyrk_manual_push_render(string $template, array $recipient, ?array $walk, int $walkId, bool $htmlSafe = false): string
{
    $rendered = strtr($template, pyrk_manual_push_variables($recipient, $walk, $walkId, $htmlSafe));
    $left = pyrk_manual_push_tokens($rendered);
    if ($left) Response::error('Unresolved variable(s): ' . implode(', ', $left) . '. The notification was not sent.');
    return trim($rendered);
}

function pyrk_manual_push_active_preset(PDO $pdo, int $presetId): ?array
{
    if ($presetId <= 0 || !pyrk_manual_notif_table_exists($pdo, 'push_notification_presets')) return null;
    $stmt = $pdo->prepare('SELECT id, subject_template, message_template FROM push_notification_presets WHERE id = ? AND is_active = 1 LIMIT 1');
    $stmt->execute([$presetId]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
}

function pyrk_manual_uploaded_files_array(string $key): array
{
    if (empty($_FILES[$key])) return [];
    $file = $_FILES[$key];
    $files = [];

    if (is_array($file['name'])) {
        $count = count($file['name']);
        for ($i = 0; $i < $count; $i++) {
            $files[] = [
                'name' => $file['name'][$i] ?? '',
                'type' => $file['type'][$i] ?? '',
                'tmp_name' => $file['tmp_name'][$i] ?? '',
                'error' => $file['error'][$i] ?? UPLOAD_ERR_NO_FILE,
                'size' => $file['size'][$i] ?? 0,
            ];
        }
    } else {
        $files[] = $file;
    }

    return $files;
}

function pyrk_manual_store_attachments(PDO $pdo, int $batchId): array
{
    $files = pyrk_manual_uploaded_files_array('attachments');
    $files = array_values(array_filter($files, static fn($f) => (int)($f['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_NO_FILE));
    if (!$files) return [];

    if (!pyrk_manual_notif_table_exists($pdo, 'manual_notification_attachments')) {
        Response::error('Attachment table is not installed. Run database/migrations/037_push_notification_reminders_attachments.sql');
    }

    $allowedExt = ['pdf','png','jpg','jpeg','webp','gif','doc','docx','xls','xlsx','ppt','pptx','txt','csv'];
    $maxBytes = 8 * 1024 * 1024;
    $uploadRoot = realpath(__DIR__ . '/../public') ?: (__DIR__ . '/../public');
    $dir = $uploadRoot . '/uploads/notifications/batch_' . $batchId;
    if (!is_dir($dir) && !mkdir($dir, 0775, true)) {
        Response::error('Could not create notification upload folder.');
    }

    $stmt = $pdo->prepare('INSERT INTO manual_notification_attachments (batch_id, original_name, file_path, mime_type, size_bytes, created_at) VALUES (?, ?, ?, ?, ?, NOW())');
    $stored = [];

    foreach (array_slice($files, 0, 5) as $file) {
        if ((int)$file['error'] !== UPLOAD_ERR_OK) {
            Response::error('One attachment could not be uploaded.');
        }
        if ((int)$file['size'] > $maxBytes) {
            Response::error('Attachment is too large. Maximum is 8MB per file.');
        }

        $original = basename((string)$file['name']);
        $ext = strtolower(pathinfo($original, PATHINFO_EXTENSION));
        if (!in_array($ext, $allowedExt, true)) {
            Response::error('Attachment type not allowed: ' . $original);
        }

        $safeBase = preg_replace('/[^A-Za-z0-9_.-]+/', '_', pathinfo($original, PATHINFO_FILENAME));
        $name = $safeBase . '_' . bin2hex(random_bytes(5)) . '.' . $ext;
        $target = $dir . '/' . $name;

        if (!move_uploaded_file((string)$file['tmp_name'], $target)) {
            Response::error('Could not store attachment: ' . $original);
        }

        $relative = 'uploads/notifications/batch_' . $batchId . '/' . $name;
        $stmt->execute([$batchId, $original, $relative, (string)($file['type'] ?? ''), (int)$file['size']]);
        $stored[] = [
            'id' => (int)$pdo->lastInsertId(),
            'original_name' => $original,
            'file_path' => $relative,
            'url' => pyrk_manual_public_base_url() . $relative,
            'size_bytes' => (int)$file['size'],
        ];
    }

    return $stored;
}

function pyrk_manual_attachment_html(array $attachments): string
{
    if (!$attachments) return '';
    $html = '<hr><p><strong>Attachments:</strong></p><ul>';
    foreach ($attachments as $file) {
        $html .= '<li><a href="' . htmlspecialchars((string)$file['url'], ENT_QUOTES, 'UTF-8') . '">' . htmlspecialchars((string)$file['original_name'], ENT_QUOTES, 'UTF-8') . '</a></li>';
    }
    return $html . '</ul>';
}

function pyrk_manual_attachment_text(array $attachments): string
{
    if (!$attachments) return '';
    $lines = ["", "Attachments:"];
    foreach ($attachments as $file) {
        $lines[] = '- ' . $file['original_name'] . ': ' . $file['url'];
    }
    return implode("\n", $lines);
}

function pyrk_manual_update_optional_batch_fields(PDO $pdo, int $batchId, int $walkId, int $deliveryWhatsapp, int $whatsappSent, int $whatsappFailed, int $deliveryNative, int $nativeSent, int $nativeFailed): void
{
    $sets = [];
    $params = [];

    foreach ([
        'walk_id' => $walkId > 0 ? $walkId : null,
        'delivery_whatsapp' => $deliveryWhatsapp,
        'whatsapp_sent_count' => $whatsappSent,
        'whatsapp_failed_count' => $whatsappFailed,
        'delivery_native' => $deliveryNative,
        'native_sent_count' => $nativeSent,
        'native_failed_count' => $nativeFailed,
    ] as $col => $value) {
        if (pyrk_manual_notif_column_exists($pdo, 'manual_notification_batches', $col)) {
            $sets[] = $col . ' = ?';
            $params[] = $value;
        }
    }

    if (!$sets) return;
    $params[] = $batchId;
    $stmt = $pdo->prepare('UPDATE manual_notification_batches SET ' . implode(', ', $sets) . ' WHERE id = ?');
    $stmt->execute($params);
}


function pyrk_manual_whatsapp_summary(array $recipients): array
{
    $unique = [];
    $duplicates = [];
    $invalid = [];

    foreach ($recipients as $recipient) {
        $mobile = trim((string)($recipient['mobile'] ?? ''));
        if (empty($recipient['has_whatsapp']) || $mobile === '') {
            $invalid[(int)$recipient['id']] = true;
            continue;
        }
        if (isset($unique[$mobile])) {
            $duplicates[(int)$recipient['id']] = (int)$unique[$mobile]['id'];
            continue;
        }
        $unique[$mobile] = $recipient;
    }

    return [
        'unique_recipients' => array_values($unique),
        'unique_count' => count($unique),
        'duplicate_member_ids' => $duplicates,
        'duplicate_count' => count($duplicates),
        'invalid_member_ids' => array_keys($invalid),
        'invalid_count' => count($invalid),
    ];
}

function pyrk_manual_whatsapp_preview_hash(array $data): string
{
    $memberIds = pyrk_manual_notif_ids($data['member_ids'] ?? []);
    $groupIds = pyrk_manual_notif_ids($data['group_ids'] ?? []);
    sort($memberIds);
    sort($groupIds);

    $payload = [
        'subject' => trim((string)($data['subject'] ?? '')),
        'message' => trim((string)($data['message'] ?? '')),
        'target_scope' => (string)($data['target_scope'] ?? 'all_members'),
        'walk_id' => (int)($data['walk_id'] ?? 0),
        'member_ids' => $memberIds,
        'group_ids' => $groupIds,
        'delivery_whatsapp' => !empty($data['delivery_whatsapp']) ? 1 : 0,
    ];

    return hash('sha256', json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
}

function pyrk_manual_whatsapp_create_preview_token(array $input, int $recipientCount): string
{
    $token = bin2hex(random_bytes(24));
    $_SESSION['pyrk_whatsapp_preview_tokens'] ??= [];
    $_SESSION['pyrk_whatsapp_preview_tokens'][$token] = [
        'hash' => pyrk_manual_whatsapp_preview_hash($input),
        'recipient_count' => $recipientCount,
        'expires_at' => time() + 600,
    ];

    foreach ($_SESSION['pyrk_whatsapp_preview_tokens'] as $key => $row) {
        if (($row['expires_at'] ?? 0) < time()) unset($_SESSION['pyrk_whatsapp_preview_tokens'][$key]);
    }

    return $token;
}

function pyrk_manual_whatsapp_validate_preview_token(array $input, string $token, int $recipientCount): bool
{
    $stored = $_SESSION['pyrk_whatsapp_preview_tokens'][$token] ?? null;
    if (!is_array($stored)) return false;
    unset($_SESSION['pyrk_whatsapp_preview_tokens'][$token]);

    return (int)($stored['expires_at'] ?? 0) >= time()
        && (int)($stored['recipient_count'] ?? -1) === $recipientCount
        && hash_equals((string)($stored['hash'] ?? ''), pyrk_manual_whatsapp_preview_hash($input));
}

if ($method === 'GET' && $action === 'overview') {
    $members = pyrk_manual_notif_members($pdo);
    $nativePush = new NativePushService();
    $ultraMsg = new UltraMsgService();
    $whatsappQueue = new WhatsAppQueueService($pdo, $ultraMsg);
    Response::ok([
        'members' => $members,
        'groups' => pyrk_manual_notif_groups($pdo),
        'walks' => pyrk_manual_notif_walks($pdo),
        'batches' => pyrk_manual_notif_batches($pdo),
        'native_push' => [
            'configuration' => $nativePush->configurationStatus(),
            'registered_member_count' => $nativePush->activeUserCount(array_map(static fn($member) => (int)$member['id'], $members)),
        ],
        'whatsapp' => [
            'configuration' => $ultraMsg->configurationStatus(false),
            'queue' => $whatsappQueue->overview(),
        ],
    ]);
}

if ($method === 'GET' && $action === 'whatsapp_status') {
    $ultraMsg = new UltraMsgService();
    $whatsappQueue = new WhatsAppQueueService($pdo, $ultraMsg);
    Response::ok([
        'configuration' => $ultraMsg->configurationStatus(true),
        'queue' => $whatsappQueue->overview(),
    ]);
}

if ($method === 'POST' && $action === 'cancel_whatsapp_batch') {
    Csrf::validateFromRequest();
    $batchId = (int)($input['batch_id'] ?? 0);
    if ($batchId <= 0) Response::error('Notification batch is required.');
    $cancelled = (new WhatsAppQueueService($pdo, new UltraMsgService()))->cancelBatch($batchId);
    try {
        Security::audit((int)$user['id'], 'whatsapp_queue_cancelled', 'manual_notification_batches', $batchId, ['cancelled' => $cancelled]);
    } catch (Throwable) {
    }
    Response::ok(['cancelled' => $cancelled], $cancelled > 0 ? 'Queued WhatsApp messages cancelled.' : 'No queued WhatsApp messages were available to cancel.');
}

if ($method === 'GET' && $action === 'history') {
    Response::ok(pyrk_manual_notif_history($pdo, $input));
}

if ($method === 'GET' && $action === 'detail') {
    Response::ok(pyrk_manual_notif_batch_detail($pdo, (int)($input['id'] ?? 0)));
}


if ($method === 'POST' && $action === 'preview') {
    Csrf::validateFromRequest();

    $scope = (string)($input['target_scope'] ?? 'all_members');
    if (!in_array($scope, ['all_members','selected_groups','selected_members','walk_registrants'], true)) $scope = 'all_members';

    $walkId = (int)($input['walk_id'] ?? 0);
    if ($scope === 'walk_registrants' && $walkId <= 0) Response::error('Choose a walk for walk reminder recipients.');

    $recipients = pyrk_manual_notif_recipients(
        $pdo,
        $scope,
        pyrk_manual_notif_ids($input['member_ids'] ?? []),
        pyrk_manual_notif_ids($input['group_ids'] ?? []),
        $walkId
    );

    $subject = trim((string)($input['subject'] ?? ''));
    $messageHtml = pyrk_manual_notif_clean_html((string)($input['message_html'] ?? ''));
    $message = trim((string)($input['message'] ?? ''));
    if ($messageHtml === '' && $message !== '') {
        $messageHtml = nl2br(htmlspecialchars($message, ENT_QUOTES, 'UTF-8'));
    } elseif ($messageHtml !== '') {
        $message = pyrk_manual_notif_html_to_text($messageHtml);
    }

    $presetId = (int)($input['push_preset_id'] ?? 0);
    $preset = pyrk_manual_push_active_preset($pdo, $presetId);
    if ($presetId > 0 && !$preset) Response::error('The selected Push preset is missing or inactive. Reload presets and choose an active one.');
    if ($subject === '' && $preset) $subject = trim((string)$preset['subject_template']);
    if ($message === '' && $preset) {
        $message = trim((string)$preset['message_template']);
        $messageHtml = nl2br(htmlspecialchars($message, ENT_QUOTES, 'UTF-8'));
    }
    if ($subject === '') Response::error('Subject is required.');
    if ($message === '') Response::error('Message is required.');
    $walk = pyrk_manual_push_walk($pdo, $walkId);
    pyrk_manual_push_validate_templates($subject, $message, $messageHtml, $walk);
    $previewRecipient = $recipients[0] ?? ['full_name' => 'Alex Walker'];
    $previewSubject = pyrk_manual_push_render($subject, $previewRecipient, $walk, $walkId);
    $previewMessage = pyrk_manual_push_render($message, $previewRecipient, $walk, $walkId);
    $previewHtml = pyrk_manual_push_render($messageHtml, $previewRecipient, $walk, $walkId, true);

    $nativePush = new NativePushService();
    $recipientUserIds = array_map(static fn($r) => (int)$r['id'], $recipients);
    $deliveryWhatsapp = !empty($input['delivery_whatsapp']);
    $whatsappSummary = pyrk_manual_whatsapp_summary($recipients);
    $ultraMsg = new UltraMsgService();
    $whatsappQueue = new WhatsAppQueueService($pdo, $ultraMsg);
    $whatsappConfiguration = $ultraMsg->configurationStatus(false);
    $whatsappQueueOverview = $whatsappQueue->overview();
    $whatsappLimits = $whatsappConfiguration['limits'] ?? $ultraMsg->limits();
    $whatsappCanQueue = !$deliveryWhatsapp || (
        !empty($whatsappConfiguration['configured'])
        && !empty($whatsappQueueOverview['schema_ready'])
        && (int)$whatsappSummary['unique_count'] > 0
        && (int)$whatsappSummary['unique_count'] <= (int)$whatsappLimits['max_per_batch']
        && (int)$whatsappSummary['unique_count'] <= (int)$whatsappQueueOverview['remaining_today']
    );
    $previewToken = $deliveryWhatsapp
        ? pyrk_manual_whatsapp_create_preview_token(array_merge($input, ['message' => $message ?: pyrk_manual_notif_html_to_text($messageHtml)]), (int)$whatsappSummary['unique_count'])
        : null;

    Response::ok([
        'recipient_count' => count($recipients),
        'email_count' => count(array_filter($recipients, static fn($r) => !empty($r['has_email']))),
        'whatsapp_count' => (int)$whatsappSummary['unique_count'],
        'native_count' => $nativePush->activeUserCount($recipientUserIds),
        'native_configuration' => $nativePush->configurationStatus(),
        'whatsapp' => [
            'configuration' => $whatsappConfiguration,
            'queue' => $whatsappQueueOverview,
            'unique_count' => (int)$whatsappSummary['unique_count'],
            'duplicate_count' => (int)$whatsappSummary['duplicate_count'],
            'invalid_count' => (int)$whatsappSummary['invalid_count'],
            'can_queue' => $whatsappCanQueue,
            'preview_token' => $previewToken,
        ],
        'sample_recipients' => array_slice($recipients, 0, 20),
        'preview' => [
            'subject' => $previewSubject,
            'message_html' => $previewHtml,
            'message_text' => $previewMessage,
            'target_scope' => $scope,
            'walk_id' => $walkId,
        ],
    ]);
}

if ($method === 'POST' && $action === 'send') {
    Csrf::validateFromRequest();
    pyrk_manual_notif_require_tables($pdo);

    $subject = trim((string)($input['subject'] ?? ''));
    $messageHtml = pyrk_manual_notif_clean_html((string)($input['message_html'] ?? ''));
    $message = trim((string)($input['message'] ?? ''));

    if ($messageHtml !== '') {
        $message = pyrk_manual_notif_html_to_text($messageHtml);
    }

    $deliveryEmail = !empty($input['delivery_email']) ? 1 : 0;
    $deliveryWeb = !empty($input['delivery_web']) ? 1 : 0;
    $deliveryWhatsapp = !empty($input['delivery_whatsapp']) ? 1 : 0;
    $deliveryNative = !empty($input['delivery_native']) ? 1 : 0;

    if (!$deliveryEmail && !$deliveryWeb && !$deliveryWhatsapp && !$deliveryNative) {
        Response::error('Choose at least one delivery channel: Web notification, Phone push, Email or WhatsApp.');
    }

    $scope = (string)($input['target_scope'] ?? 'all_members');
    if (!in_array($scope, ['all_members','selected_groups','selected_members','walk_registrants'], true)) $scope = 'all_members';

    $walkId = (int)($input['walk_id'] ?? 0);
    $memberIds = pyrk_manual_notif_ids($input['member_ids'] ?? []);
    $groupIds = pyrk_manual_notif_ids($input['group_ids'] ?? []);

    if ($scope === 'selected_members' && !$memberIds) Response::error('Select at least one member.');
    if ($scope === 'selected_groups' && !$groupIds) Response::error('Select at least one team/group.');
    if ($scope === 'walk_registrants' && $walkId <= 0) Response::error('Choose a walk for walk reminder recipients.');

    $recipients = pyrk_manual_notif_recipients($pdo, $scope, $memberIds, $groupIds, $walkId);
    if (!$recipients) Response::error('No active member recipients were found.');

    $presetId = (int)($input['push_preset_id'] ?? 0);
    $preset = pyrk_manual_push_active_preset($pdo, $presetId);
    if ($presetId > 0 && !$preset) Response::error('The selected Push preset is missing or inactive. Reload presets and choose an active one.');
    if ($subject === '' && $preset) $subject = trim((string)$preset['subject_template']);
    if ($message === '' && $preset) {
        $message = trim((string)$preset['message_template']);
        $messageHtml = nl2br(htmlspecialchars($message, ENT_QUOTES, 'UTF-8'));
    }
    if ($subject === '') Response::error('Subject is required.');
    if ($message === '') Response::error('Message is required.');
    $walk = pyrk_manual_push_walk($pdo, $walkId);
    pyrk_manual_push_validate_templates($subject, $message, $messageHtml, $walk);

    $ultraMsg = new UltraMsgService();
    $whatsappQueue = new WhatsAppQueueService($pdo, $ultraMsg);
    $whatsappSummary = pyrk_manual_whatsapp_summary($recipients);
    $whatsappConfiguration = $ultraMsg->configurationStatus(false);
    $whatsappQueueOverview = $whatsappQueue->overview();

    if ($deliveryWhatsapp) {
        if (empty($input['whatsapp_consent_confirmed'])) {
            Response::error('Confirm that the selected members consented to receive this WhatsApp message.');
        }
        if (empty($whatsappConfiguration['configured'])) {
            Response::error('UltraMsg is not configured: ' . ($whatsappConfiguration['message'] ?? 'Check config/app.php.'));
        }
        if (empty($whatsappQueueOverview['schema_ready'])) {
            Response::error('WhatsApp queue is not installed. Import database/migrations/041_ultramsg_whatsapp_queue.sql.');
        }
        if ((int)$whatsappSummary['unique_count'] <= 0) {
            Response::error('No valid unique WhatsApp mobile numbers were found.');
        }
        $maxPerBatch = (int)($whatsappConfiguration['limits']['max_per_batch'] ?? 50);
        if ((int)$whatsappSummary['unique_count'] > $maxPerBatch) {
            Response::error('WhatsApp safety limit: this batch has ' . (int)$whatsappSummary['unique_count'] . ' unique numbers. The configured maximum is ' . $maxPerBatch . '. Use a smaller selected audience.');
        }
        if ((int)$whatsappSummary['unique_count'] > (int)$whatsappQueueOverview['remaining_today']) {
            Response::error('WhatsApp daily safety limit: only ' . (int)$whatsappQueueOverview['remaining_today'] . ' message slot(s) remain today.');
        }
        $previewToken = trim((string)($input['whatsapp_preview_token'] ?? ''));
        $previewInput = array_merge($input, ['message' => $message]);
        if ($previewToken === '' || !pyrk_manual_whatsapp_validate_preview_token($previewInput, $previewToken, (int)$whatsappSummary['unique_count'])) {
            Response::error('Preview this exact WhatsApp audience and message again before queueing it. The safety preview expires after 10 minutes.');
        }
        $liveStatus = $ultraMsg->configurationStatus(true);
        if (empty($liveStatus['authenticated'])) {
            Response::error('UltraMsg is not authenticated: ' . ($liveStatus['message'] ?? 'Reconnect the WhatsApp QR session.'));
        }
    }

    $nativePush = new NativePushService();
    $nativeConfiguration = $nativePush->configurationStatus();
    if ($deliveryNative && !$nativeConfiguration['configured'] && !$deliveryEmail && !$deliveryWeb && !$deliveryWhatsapp) {
        Response::error('Phone push is not configured yet: ' . $nativeConfiguration['message']);
    }

    $pdo->beginTransaction();
    try {
        $batchStmt = $pdo->prepare('INSERT INTO manual_notification_batches
            (subject, message, target_scope, delivery_email, delivery_web, total_recipients, email_sent_count, email_failed_count, web_sent_count, created_by, created_at)
            VALUES (?, ?, ?, ?, ?, ?, 0, 0, 0, ?, NOW())');
        $batchStmt->execute([$subject, $messageHtml ?: nl2br(htmlspecialchars($message, ENT_QUOTES, 'UTF-8')), $scope, $deliveryEmail, $deliveryWeb, count($recipients), (int)$user['id']]);
        $batchId = (int)$pdo->lastInsertId();

        if ($scope === 'selected_groups' && $groupIds) {
            $linkGroup = $pdo->prepare('INSERT IGNORE INTO manual_notification_batch_groups (batch_id, group_id) VALUES (?, ?)');
            foreach ($groupIds as $gid) $linkGroup->execute([$batchId, $gid]);
        }
        if ($scope === 'selected_members' && $memberIds) {
            $linkMember = $pdo->prepare('INSERT IGNORE INTO manual_notification_batch_members (batch_id, user_id) VALUES (?, ?)');
            foreach ($memberIds as $mid) $linkMember->execute([$batchId, $mid]);
        }

        $attachments = pyrk_manual_store_attachments($pdo, $batchId);

        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) $pdo->rollBack();
        Response::error('Could not create notification batch: ' . $e->getMessage());
    }

    $notifier = new NotificationService();
    $webSent = 0;
    $emailSent = 0;
    $emailFailed = 0;
    $whatsappSent = 0;
    $whatsappQueued = 0;
    $whatsappFailed = 0;
    $whatsappSkipped = 0;
    $nativeSent = 0;
    $nativeFailed = 0;

    $baseHtmlTemplate = $messageHtml ?: '<p>' . nl2br(htmlspecialchars($message, ENT_QUOTES, 'UTF-8')) . '</p>';
    $attachmentHtml = pyrk_manual_attachment_html($attachments);
    $attachmentText = pyrk_manual_attachment_text($attachments);
    $nativeTargetUrl = $walkId > 0
        ? pyrk_manual_public_base_url() . 'walk.php?id=' . $walkId
        : pyrk_manual_public_base_url() . 'notifications.php';

    $recipientLog = $pdo->prepare('INSERT INTO manual_notification_batch_recipients
        (batch_id, user_id, email, email_status, web_status, error_message, created_at)
        VALUES (?, ?, ?, ?, ?, ?, NOW())');

    $hasNotificationBatchId = pyrk_manual_notif_column_exists($pdo, 'notifications', 'batch_id');
    $webSql = $hasNotificationBatchId
        ? 'INSERT INTO notifications (batch_id, user_id, walk_id, channel, subject, message, status, scheduled_at, sent_at, error_message, read_at, created_at) VALUES (?, ?, ?, "in_app", ?, ?, "sent", NOW(), NOW(), NULL, NULL, NOW())'
        : 'INSERT INTO notifications (user_id, walk_id, channel, subject, message, status, scheduled_at, sent_at, error_message, read_at, created_at) VALUES (?, ?, "in_app", ?, ?, "sent", NOW(), NOW(), NULL, NULL, NOW())';
    $webStmt = $pdo->prepare($webSql);
    $whatsappUniqueUserIds = array_fill_keys(array_map(static fn($r) => (int)$r['id'], $whatsappSummary['unique_recipients']), true);
    $whatsappDuplicateMemberIds = $whatsappSummary['duplicate_member_ids'];
    $whatsappInvalidMemberIds = array_fill_keys($whatsappSummary['invalid_member_ids'], true);

    foreach ($recipients as $recipient) {
        $renderedSubject = pyrk_manual_push_render($subject, $recipient, $walk, $walkId);
        $renderedMessage = pyrk_manual_push_render($message, $recipient, $walk, $walkId);
        $renderedHtml = pyrk_manual_push_render($baseHtmlTemplate, $recipient, $walk, $walkId, true);
        $webMessage = $renderedHtml . $attachmentHtml;
        $plainMessage = trim($renderedMessage . $attachmentText);

        $emailStatus = $deliveryEmail ? 'skipped' : null;
        $webStatus = $deliveryWeb ? 'skipped' : null;
        $whatsappStatus = $deliveryWhatsapp ? 'pending_queue' : null;
        $nativeStatus = $deliveryNative ? 'skipped' : null;
        $error = null;

        if ($deliveryWeb) {
            try {
                if ($hasNotificationBatchId) {
                    $webStmt->execute([$batchId, (int)$recipient['id'], $walkId ?: null, $renderedSubject, $webMessage]);
                } else {
                    $webStmt->execute([(int)$recipient['id'], $walkId ?: null, $renderedSubject, $webMessage]);
                }
                $webStatus = 'sent';
                $webSent++;
            } catch (Throwable $e) {
                $webStatus = 'failed';
                $error = trim(($error ? $error . ' | ' : '') . 'Web: ' . $e->getMessage());
            }
        }

        if ($deliveryEmail) {
            $email = trim((string)$recipient['email']);
            if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $emailStatus = 'failed';
                $emailFailed++;
                $error = trim(($error ? $error . ' | ' : '') . 'Email missing or invalid.');
            } else {
                $result = $notifier->sendEmail((int)$recipient['id'], $walkId ?: null, $email, (string)$recipient['full_name'], $renderedSubject, $renderedHtml . $attachmentHtml, $plainMessage);
                $emailStatus = (string)($result['status'] ?? 'failed');
                if (in_array($emailStatus, ['sent','logged'], true)) {
                    $emailSent++;
                } else {
                    $emailFailed++;
                    if (!empty($result['error'])) {
                        $error = trim(($error ? $error . ' | ' : '') . 'Email: ' . $result['error']);
                    }
                }
            }
        }

        if ($deliveryWhatsapp) {
            $recipientId = (int)$recipient['id'];
            if (isset($whatsappInvalidMemberIds[$recipientId])) {
                $whatsappStatus = 'failed';
                $whatsappFailed++;
                $error = trim(($error ? $error . ' | ' : '') . 'WhatsApp mobile missing or invalid.');
            } elseif (isset($whatsappDuplicateMemberIds[$recipientId])) {
                $whatsappStatus = 'duplicate_skipped';
                $whatsappSkipped++;
            } elseif (!isset($whatsappUniqueUserIds[$recipientId])) {
                $whatsappStatus = 'skipped';
                $whatsappSkipped++;
            }
        }

        if ($deliveryNative) {
            if (!$nativeConfiguration['configured']) {
                $nativeStatus = 'not_configured';
                $nativeFailed++;
                $error = trim(($error ? $error . ' | ' : '') . 'Phone push: ' . $nativeConfiguration['message']);
            } else {
                $result = $nativePush->sendToUser((int)$recipient['id'], $renderedSubject, $plainMessage, [
                    'url' => $nativeTargetUrl,
                    'walk_id' => $walkId > 0 ? (string)$walkId : '',
                    'batch_id' => (string)$batchId,
                    'type' => $walkId > 0 ? 'walk_notification' : 'member_notification',
                ]);
                $nativeStatus = (string)($result['status'] ?? 'failed');
                if ($nativeStatus === 'sent') {
                    $nativeSent++;
                } elseif ($nativeStatus === 'no_device') {
                    // A member without a registered phone is logged clearly but is not a provider failure.
                    $nativeFailed++;
                } else {
                    $nativeFailed++;
                    if (!empty($result['error'])) {
                        $error = trim(($error ? $error . ' | ' : '') . 'Phone push: ' . $result['error']);
                    }
                }
            }
        }

        $recipientLog->execute([
            $batchId,
            (int)$recipient['id'],
            trim((string)$recipient['email']),
            $emailStatus,
            $webStatus,
            $error,
        ]);

        $recipientLogId = (int)$pdo->lastInsertId();
        $optionalSets = [];
        $optionalParams = [];
        if (pyrk_manual_notif_column_exists($pdo, 'manual_notification_batch_recipients', 'mobile')) {
            $optionalSets[] = 'mobile = ?';
            $optionalParams[] = trim((string)$recipient['mobile']);
        }
        if (pyrk_manual_notif_column_exists($pdo, 'manual_notification_batch_recipients', 'whatsapp_status')) {
            $optionalSets[] = 'whatsapp_status = ?';
            $optionalParams[] = $whatsappStatus;
        }
        if (pyrk_manual_notif_column_exists($pdo, 'manual_notification_batch_recipients', 'native_status')) {
            $optionalSets[] = 'native_status = ?';
            $optionalParams[] = $nativeStatus;
        }
        if ($optionalSets) {
            $optionalParams[] = $recipientLogId;
            $pdo->prepare('UPDATE manual_notification_batch_recipients SET ' . implode(', ', $optionalSets) . ' WHERE id = ?')->execute($optionalParams);
        }

        if ($deliveryWhatsapp && $whatsappStatus === 'pending_queue') {
            try {
                $queueResult = $whatsappQueue->enqueue([
                    'batch_id' => $batchId,
                    'recipient_log_id' => $recipientLogId,
                    'user_id' => (int)$recipient['id'],
                    'walk_id' => $walkId ?: null,
                    'mobile' => (string)$recipient['mobile'],
                    'message' => $plainMessage,
                    'created_by' => (int)$user['id'],
                ]);
                $queueStatus = (string)($queueResult['status'] ?? 'failed');
                $queueId = (int)($queueResult['queue_id'] ?? 0);

                if ($queueStatus === 'queued') {
                    $whatsappStatus = 'queued';
                    $whatsappQueued++;
                } elseif ($queueStatus === 'duplicate') {
                    $whatsappStatus = 'duplicate_skipped';
                    $whatsappSkipped++;
                } else {
                    $whatsappStatus = 'failed';
                    $whatsappFailed++;
                    $queueError = (string)($queueResult['error'] ?? 'Could not queue WhatsApp message.');
                    $error = trim(($error ? $error . ' | ' : '') . 'WhatsApp: ' . $queueError);
                }

                $queueSets = ['whatsapp_status = ?'];
                $queueParams = [$whatsappStatus];
                if ($queueId > 0 && pyrk_manual_notif_column_exists($pdo, 'manual_notification_batch_recipients', 'whatsapp_queue_id')) {
                    $queueSets[] = 'whatsapp_queue_id = ?';
                    $queueParams[] = $queueId;
                }
                if ($error !== null) {
                    $queueSets[] = 'error_message = ?';
                    $queueParams[] = $error;
                }
                $queueParams[] = $recipientLogId;
                $pdo->prepare('UPDATE manual_notification_batch_recipients SET ' . implode(', ', $queueSets) . ' WHERE id = ?')->execute($queueParams);
            } catch (Throwable $e) {
                $whatsappStatus = 'failed';
                $whatsappFailed++;
                $error = trim(($error ? $error . ' | ' : '') . 'WhatsApp queue: ' . $e->getMessage());
                $pdo->prepare('UPDATE manual_notification_batch_recipients SET whatsapp_status = "failed", error_message = ? WHERE id = ?')->execute([$error, $recipientLogId]);
            }
        }
    }

    $update = $pdo->prepare('UPDATE manual_notification_batches
        SET email_sent_count = ?, email_failed_count = ?, web_sent_count = ?
        WHERE id = ?');
    $update->execute([$emailSent, $emailFailed, $webSent, $batchId]);

    pyrk_manual_update_optional_batch_fields($pdo, $batchId, $walkId, $deliveryWhatsapp, $whatsappSent, $whatsappFailed, $deliveryNative, $nativeSent, $nativeFailed);
    if ($deliveryWhatsapp) {
        $whatsappQueue->recalculateBatch($batchId);
    }

    try {
        Security::audit((int)$user['id'], 'manual_notification_sent', 'manual_notification_batches', $batchId, [
            'subject' => $subject,
            'target_scope' => $scope,
            'walk_id' => $walkId ?: null,
            'push_preset_id' => $presetId ?: null,
            'delivery_email' => $deliveryEmail,
            'delivery_web' => $deliveryWeb,
            'delivery_whatsapp' => $deliveryWhatsapp,
            'delivery_native' => $deliveryNative,
            'attachments' => count($attachments),
            'total_recipients' => count($recipients),
            'email_sent' => $emailSent,
            'email_failed' => $emailFailed,
            'web_sent' => $webSent,
            'whatsapp_sent' => $whatsappSent,
            'whatsapp_queued' => $whatsappQueued,
            'whatsapp_skipped' => $whatsappSkipped,
            'whatsapp_failed' => $whatsappFailed,
            'native_sent' => $nativeSent,
            'native_failed' => $nativeFailed,
        ]);
    } catch (Throwable) {
        // Audit should never break sending.
    }

    Response::ok([
        'batch_id' => $batchId,
        'total_recipients' => count($recipients),
        'email_sent' => $emailSent,
        'email_failed' => $emailFailed,
        'web_sent' => $webSent,
        'whatsapp_sent' => $whatsappSent,
        'whatsapp_queued' => $whatsappQueued,
        'whatsapp_skipped' => $whatsappSkipped,
        'whatsapp_failed' => $whatsappFailed,
        'native_sent' => $nativeSent,
        'native_failed' => $nativeFailed,
        'native_configuration' => $nativeConfiguration,
        'attachments' => count($attachments),
    ], $deliveryWhatsapp ? 'Notification saved. WhatsApp messages were queued safely.' : 'Notification sent.');
}

Response::error('Unsupported manual notification action.', 404);
