<?php
declare(strict_types=1);

defined('BASE_PATH') || exit;

/**
 * Owner-facing controller for scheduling polls (Terminfindung).
 *
 * All routes here sit behind the global login gate, so every action additionally
 * re-checks ownership: a poll may only be managed by its creator or a global
 * admin. Public voting lives in {@see PublicPollController}.
 */
class MeetingPollController
{
    private const MAX_TITLE_LEN   = 200;
    private const MAX_TEXT_LEN    = 5000;
    private const MAX_LOC_LEN     = 300;
    private const MAX_OPTIONS     = 50;
    /** Hard ceiling on responses per poll. Bounds DB growth and owner-notification spam
     *  if the public link is abused; legitimate polls stay well under it. */
    public  const MAX_RESPONSES   = 500;
    /** Hard ceiling on stored invitations per poll, and on a single invite batch. */
    private const MAX_INVITES      = 200;
    private const MAX_INVITE_BATCH = 100;
    /** Minimum seconds between two sends of the same invitation. */
    private const RESEND_COOLDOWN  = 60;
    public  const VOTE_VALUES     = ['yes', 'maybe', 'no'];

    /** Collapse ASCII control characters to spaces — used on free-text that ends up
     *  in mail subjects/headers (defence in depth on top of the mailer's own encoding). */
    public static function singleLine(string $s): string
    {
        return trim((string)preg_replace('/[\x00-\x1F\x7F]+/', ' ', $s));
    }

    // ── List ────────────────────────────────────────────────────────────────

    public static function index(): void
    {
        Auth::requireLogin();
        $db  = Database::getInstance();
        $uid = (int)Auth::id();

        $polls = $db->fetchAll(
            'SELECT p.*,
                    (SELECT COUNT(*) FROM mp_options   o WHERE o.poll_id = p.id) AS option_count,
                    (SELECT COUNT(*) FROM mp_responses r WHERE r.poll_id = p.id) AS response_count
               FROM mp_polls p
              WHERE p.owner_id = ?
              ORDER BY p.created_at DESC',
            [$uid]
        );

        MeetingPollPlugin::render('list', [
            'pageTitle' => Lang::get('mp_nav'),
            'polls'     => $polls,
        ]);
    }

    // ── Create ──────────────────────────────────────────────────────────────

    public static function create(): void
    {
        Auth::requireLogin();
        $errors = [];
        $form   = self::emptyForm();

        if (Helper::isPost()) {
            Helper::verifyCsrf();
            $form = self::readForm();
            $errors = self::validateForm($form);
            if (empty($errors)) {
                $id = self::persistNew($form, (int)Auth::id());
                Helper::redirect('/polls/' . $id, ['success' => Lang::get('mp_created')]);
            }
        }

        MeetingPollPlugin::render('form', [
            'pageTitle' => Lang::get('mp_create_title'),
            'mode'      => 'create',
            'poll'      => null,
            'form'      => $form,
            'errors'    => $errors,
        ]);
    }

    // ── Edit ────────────────────────────────────────────────────────────────

    public static function edit(int $id): void
    {
        Auth::requireLogin();
        $poll = self::ownedPollOr404($id);

        $errors = [];
        if (Helper::isPost()) {
            Helper::verifyCsrf();
            $form   = self::readForm();
            $errors = self::validateForm($form);
            if (empty($errors)) {
                self::persistEdit($id, $form);
                Helper::redirect('/polls/' . $id, ['success' => Lang::get('mp_saved')]);
            }
        } else {
            $form = self::formFromPoll($poll);
        }

        MeetingPollPlugin::render('form', [
            'pageTitle' => Lang::get('mp_edit_title'),
            'mode'      => 'edit',
            'poll'      => $poll,
            'form'      => $form,
            'errors'    => $errors,
        ]);
    }

    // ── Manage / results ────────────────────────────────────────────────────

    public static function show(int $id): void
    {
        Auth::requireLogin();
        $poll      = self::ownedPollOr404($id);
        $options   = self::loadOptions($id);
        $responses = self::loadResponses($id);
        $stats     = self::tally($options, $responses);

        MeetingPollPlugin::render('manage', [
            'pageTitle' => $poll['title'],
            'poll'      => $poll,
            'options'   => $options,
            'responses' => $responses,
            'stats'     => $stats,
            'invites'   => self::loadInvites($id),
            'shareUrl'  => Helper::baseUrl() . url('/poll/' . $poll['token']),
        ]);
    }

    // ── Invite participants by e-mail ───────────────────────────────────────

    /** Type-ahead for the invite picker. Mirrors the app's cmd-k people search:
     *  active users matching the query, after the `available_users_filter` hook
     *  (vanilla returns everyone; Provider Edition isolates by group). */
    public static function userSearch(): void
    {
        Auth::requireLogin();
        if (!Helper::isPost()) Helper::json(['error' => 'Method not allowed'], 405);
        if (!Auth::verifyCsrfToken((string)Helper::post('csrf_token', ''))) Helper::json(['error' => 'CSRF mismatch'], 403);

        $q = trim((string)Helper::post('q', ''));
        if (mb_strlen($q) < 2) Helper::json(['users' => []]);

        $like = self::buildLike($q);
        $vis  = self::userVisibilityWhere();
        $rows = Database::getInstance()->fetchAll(
            "SELECT u.id, u.username, u.email FROM users u
              WHERE u.is_active = 1{$vis['where']}
                AND (u.username LIKE ? ESCAPE '\\' OR u.email LIKE ? ESCAPE '\\')
              ORDER BY u.username LIMIT 10",
            array_merge($vis['params'], [$like, $like])
        );
        $users = array_map(fn($u) => [
            'id'       => (int)$u['id'],
            'username' => (string)$u['username'],
            'email'    => (string)($u['email'] ?? ''),
        ], $rows);
        Helper::json(['users' => $users]);
    }

    public static function invite(int $id): void
    {
        Auth::requireLogin();
        $poll = self::ownedPollOr404($id);
        if (!Helper::isPost()) Helper::redirect('/polls/' . $id);
        Helper::verifyCsrf();

        $db = Database::getInstance();
        // Existing invitations for this poll: count, dedupe set, and the stable
        // per-invitee link token (so a re-invite keeps the same link).
        $seen = [];           // lowercased email => true (already handled / already invited)
        $tokenByEmail = [];   // lowercased email => existing invite_token
        $existing = 0;
        foreach ($db->fetchAll('SELECT email, invite_token FROM mp_invites WHERE poll_id = ?', [$id]) as $er) {
            $lk = strtolower((string)$er['email']);
            $seen[$lk] = true;
            $existing++;
            if (!empty($er['invite_token'])) $tokenByEmail[$lk] = (string)$er['invite_token'];
        }

        // 1) Resolve picked Plenvo users — re-apply the visibility filter so an
        //    owner can only invite users the people-search would have shown them.
        $recipients = [];
        $ids = array_values(array_unique(array_filter(array_map('intval', (array)Helper::post('invite_user_id', [])))));
        if (!empty($ids)) {
            $ids = array_slice($ids, 0, self::MAX_INVITE_BATCH);
            $vis = self::userVisibilityWhere();
            $ph  = implode(',', array_fill(0, count($ids), '?'));
            foreach ($db->fetchAll(
                "SELECT u.id, u.username, u.email FROM users u
                  WHERE u.is_active = 1{$vis['where']} AND u.id IN ($ph)",
                array_merge($vis['params'], $ids)
            ) as $u) {
                $email = trim((string)($u['email'] ?? ''));
                if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) continue;
                $recipients[] = ['user_id' => (int)$u['id'], 'email' => $email, 'name' => (string)$u['username']];
            }
        }

        // 2) Free-form external addresses.
        $raw = (string)Helper::post('invite_emails', '');
        foreach (array_slice(preg_split('/[\s,;]+/u', $raw, -1, PREG_SPLIT_NO_EMPTY) ?: [], 0, self::MAX_INVITE_BATCH) as $cand) {
            $email = trim($cand);
            if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) continue;
            $recipients[] = ['user_id' => null, 'email' => $email, 'name' => ''];
        }

        // 3) Dedupe, enforce the cap, upsert + send.
        $sent = 0; $capped = false;
        foreach ($recipients as $r) {
            $lk = strtolower($r['email']);
            $isNew = !isset($seen[$lk]);
            if ($isNew && $existing >= self::MAX_INVITES) { $capped = true; continue; }
            $seen[$lk] = true;
            if ($isNew) $existing++;
            $tok = $tokenByEmail[$lk] ?? bin2hex(random_bytes(16));
            $tokenByEmail[$lk] = $tok;
            $db->execute(
                'INSERT INTO mp_invites (poll_id, user_id, email, name, invite_token, last_sent_at)
                 VALUES (?,?,?,?,?,?)
                 ON CONFLICT(poll_id, email) DO UPDATE SET
                    user_id      = COALESCE(excluded.user_id, mp_invites.user_id),
                    name         = CASE WHEN excluded.name <> \'\' THEN excluded.name ELSE mp_invites.name END,
                    invite_token = COALESCE(mp_invites.invite_token, excluded.invite_token),
                    last_sent_at = excluded.last_sent_at',
                [$id, $r['user_id'], $r['email'], $r['name'], $tok, time()]
            );
            self::sendInviteEmail($poll, $r['email'], $r['name'], $tok);
            $sent++;
        }

        if ($sent === 0 && !$capped) {
            Helper::redirect('/polls/' . $id, ['error' => Lang::get('mp_err_no_recipients')]);
        }
        $flash = $capped
            ? ['error'   => Lang::get('mp_err_invite_cap', ['max' => (string)self::MAX_INVITES])]
            : ['success' => Lang::get('mp_invite_sent', ['count' => (string)$sent])];
        Helper::redirect('/polls/' . $id, $flash);
    }

    public static function resendInvite(int $id, int $inviteId): void
    {
        Auth::requireLogin();
        $poll = self::ownedPollOr404($id);
        if (!Helper::isPost()) Helper::redirect('/polls/' . $id);
        Helper::verifyCsrf();
        $db  = Database::getInstance();
        $inv = $db->fetch('SELECT * FROM mp_invites WHERE id = ? AND poll_id = ?', [$inviteId, $id]);
        if (!$inv) Helper::redirect('/polls/' . $id);
        if (!empty($inv['last_sent_at']) && time() - (int)$inv['last_sent_at'] < self::RESEND_COOLDOWN) {
            Helper::redirect('/polls/' . $id, ['error' => Lang::get('mp_invite_recently_sent')]);
        }
        $tok = (string)($inv['invite_token'] ?? '');
        if ($tok === '') {   // backfill for rows created before invite_token existed
            $tok = bin2hex(random_bytes(16));
            $db->execute('UPDATE mp_invites SET invite_token = ? WHERE id = ?', [$tok, $inviteId]);
        }
        self::sendInviteEmail($poll, (string)$inv['email'], (string)$inv['name'], $tok);
        $db->execute('UPDATE mp_invites SET last_sent_at = ? WHERE id = ?', [time(), $inviteId]);
        Helper::redirect('/polls/' . $id, ['success' => Lang::get('mp_invite_resent')]);
    }

    public static function deleteInvite(int $id, int $inviteId): void
    {
        Auth::requireLogin();
        self::ownedPollOr404($id);
        if (!Helper::isPost()) Helper::redirect('/polls/' . $id);
        Helper::verifyCsrf();
        Database::getInstance()->execute('DELETE FROM mp_invites WHERE id = ? AND poll_id = ?', [$inviteId, $id]);
        Helper::redirect('/polls/' . $id, ['success' => Lang::get('mp_invite_removed')]);
    }

    /** Invitations for a poll, each annotated with whether that person has responded yet. */
    public static function loadInvites(int $pollId): array
    {
        $db      = Database::getInstance();
        $invites = $db->fetchAll('SELECT * FROM mp_invites WHERE poll_id = ? ORDER BY name COLLATE NOCASE, email COLLATE NOCASE', [$pollId]);
        if (empty($invites)) return [];

        $rows        = $db->fetchAll('SELECT user_id, email FROM mp_responses WHERE poll_id = ?', [$pollId]);
        $respUserIds = [];
        $respEmails  = [];
        foreach ($rows as $r) {
            if (!empty($r['user_id'])) $respUserIds[(int)$r['user_id']] = true;
            $e = strtolower(trim((string)$r['email']));
            if ($e !== '') $respEmails[$e] = true;
        }
        if (!empty($respUserIds)) {
            $ph = implode(',', array_fill(0, count($respUserIds), '?'));
            foreach ($db->fetchAll("SELECT email FROM users WHERE id IN ($ph)", array_keys($respUserIds)) as $u) {
                $e = strtolower(trim((string)$u['email']));
                if ($e !== '') $respEmails[$e] = true;
            }
        }
        foreach ($invites as $i => $inv) {
            $resp = (!empty($inv['user_id']) && isset($respUserIds[(int)$inv['user_id']]))
                 || isset($respEmails[strtolower(trim((string)$inv['email']))]);
            $invites[$i]['responded'] = $resp;
        }
        return $invites;
    }

    // ── Finalize / reopen ───────────────────────────────────────────────────

    public static function finalize(int $id): void
    {
        Auth::requireLogin();
        $poll = self::ownedPollOr404($id);
        if (!Helper::isPost()) Helper::redirect('/polls/' . $id);
        Helper::verifyCsrf();

        $optionId = (int)Helper::post('option_id', 0);
        $option   = Database::getInstance()->fetch(
            'SELECT * FROM mp_options WHERE id = ? AND poll_id = ?', [$optionId, $id]
        );
        if (!$option) Helper::redirect('/polls/' . $id, ['error' => Lang::get('mp_err_option')]);

        Database::getInstance()->execute('UPDATE mp_polls SET final_option_id = ? WHERE id = ?', [$optionId, $id]);

        if (Helper::post('notify', '0') === '1') {
            self::notifyParticipantsFinalized($poll, $option);
        }
        Helper::redirect('/polls/' . $id, ['success' => Lang::get('mp_finalized')]);
    }

    public static function reopen(int $id): void
    {
        Auth::requireLogin();
        self::ownedPollOr404($id);
        if (!Helper::isPost()) Helper::redirect('/polls/' . $id);
        Helper::verifyCsrf();
        Database::getInstance()->execute('UPDATE mp_polls SET final_option_id = NULL WHERE id = ?', [$id]);
        Helper::redirect('/polls/' . $id, ['success' => Lang::get('mp_reopened')]);
    }

    // ── Delete poll / single response ───────────────────────────────────────

    public static function delete(int $id): void
    {
        Auth::requireLogin();
        self::ownedPollOr404($id);
        if (!Helper::isPost()) Helper::redirect('/polls/' . $id);
        Helper::verifyCsrf();
        Database::getInstance()->execute('DELETE FROM mp_polls WHERE id = ?', [$id]);
        Helper::redirect('/polls', ['success' => Lang::get('mp_deleted')]);
    }

    public static function deleteResponse(int $id, int $responseId): void
    {
        Auth::requireLogin();
        self::ownedPollOr404($id);
        if (!Helper::isPost()) Helper::redirect('/polls/' . $id);
        Helper::verifyCsrf();
        Database::getInstance()->execute('DELETE FROM mp_responses WHERE id = ? AND poll_id = ?', [$responseId, $id]);
        Helper::redirect('/polls/' . $id, ['success' => Lang::get('mp_response_deleted')]);
    }

    // ── Shared data loaders (used by the public controller too) ─────────────

    public static function loadPollByToken(string $token): array|false
    {
        return Database::getInstance()->fetch('SELECT * FROM mp_polls WHERE token = ?', [$token]);
    }

    public static function loadOptions(int $pollId): array
    {
        return Database::getInstance()->fetchAll(
            'SELECT * FROM mp_options WHERE poll_id = ? ORDER BY sort_order, starts_at, id', [$pollId]
        );
    }

    /** Responses with their per-option votes folded into a `votes[option_id] => value` map. */
    public static function loadResponses(int $pollId): array
    {
        $db   = Database::getInstance();
        $rows = $db->fetchAll(
            'SELECT * FROM mp_responses WHERE poll_id = ? ORDER BY created_at, id', [$pollId]
        );
        if (empty($rows)) return [];
        $indexById = [];
        foreach ($rows as $i => $r) { $rows[$i]['votes'] = []; $indexById[(int)$r['id']] = $i; }
        $ph    = implode(',', array_fill(0, count($indexById), '?'));
        $votes = $db->fetchAll(
            "SELECT response_id, option_id, value FROM mp_votes WHERE response_id IN ($ph)", array_keys($indexById)
        );
        foreach ($votes as $v) {
            $rid = (int)$v['response_id'];
            if (isset($indexById[$rid])) {
                $rows[$indexById[$rid]]['votes'][(int)$v['option_id']] = $v['value'];
            }
        }
        return $rows;
    }

    /** Per-option counts plus the id(s) with the most "yes" votes. */
    public static function tally(array $options, array $responses): array
    {
        $counts  = [];
        foreach ($options as $o) $counts[(int)$o['id']] = ['yes' => 0, 'maybe' => 0, 'no' => 0];
        foreach ($responses as $r) {
            foreach ($options as $o) {
                $oid = (int)$o['id'];
                $val = $r['votes'][$oid] ?? 'no';
                if (!isset($counts[$oid][$val])) $val = 'no';
                $counts[$oid][$val]++;
            }
        }
        $best    = 0;
        $bestIds = [];
        foreach ($counts as $oid => $c) {
            if ($c['yes'] > $best) { $best = $c['yes']; $bestIds = [$oid]; }
            elseif ($c['yes'] === $best && $best > 0) { $bestIds[] = $oid; }
        }
        return ['counts' => $counts, 'best_ids' => $bestIds, 'total' => count($responses)];
    }

    // ── Option formatting helpers (return plain text; escape in the view) ───

    public static function optionDate(array $o): string
    {
        $t = (int)$o['starts_at'];
        return Lang::get('mp_wd_' . (int)date('w', $t)) . ', ' . date('d.m.Y', $t);
    }

    public static function optionTime(array $o): ?string
    {
        if (!empty($o['all_day'])) return null;
        $s = date('H:i', (int)$o['starts_at']);
        if (!empty($o['ends_at']) && (int)$o['ends_at'] > (int)$o['starts_at']) {
            return $s . ' – ' . date('H:i', (int)$o['ends_at']);
        }
        return $s;
    }

    public static function optionLabel(array $o): string
    {
        $time = self::optionTime($o);
        return self::optionDate($o) . ($time !== null ? ' · ' . $time : ' · ' . Lang::get('mp_all_day'));
    }

    /** Formats a stored unix timestamp (deadline) in the active timezone. */
    public static function fmtTs(int|string|null $ts): string
    {
        $ts = (int)$ts;
        return $ts > 0 ? date('d.m.Y H:i', $ts) : '—';
    }

    public static function pollIsClosed(array $poll): bool
    {
        if (!empty($poll['final_option_id'])) return true;
        if (!empty($poll['deadline']) && (int)$poll['deadline'] < time()) return true;
        return false;
    }

    // ── Internals ───────────────────────────────────────────────────────────

    private static function ownedPollOr404(int $id): array
    {
        $poll = Database::getInstance()->fetch('SELECT * FROM mp_polls WHERE id = ?', [$id]);
        if (!$poll || ((int)$poll['owner_id'] !== (int)Auth::id() && !Auth::isAdmin())) {
            http_response_code(404);
            view('errors/404', [], 'base');
            exit;
        }
        return $poll;
    }

    private static function emptyForm(): array
    {
        return [
            'title'        => '',
            'description'  => '',
            'location'     => '',
            'allow_maybe'  => true,
            'hide_results' => false,
            'notify_owner' => true,
            'deadline'     => '',
            'options'      => [],
        ];
    }

    private static function formFromPoll(array $poll): array
    {
        $opts = [];
        foreach (self::loadOptions((int)$poll['id']) as $o) {
            $opts[] = [
                'date'  => date('Y-m-d', (int)$o['starts_at']),
                'start' => empty($o['all_day']) ? date('H:i', (int)$o['starts_at']) : '',
                'end'   => (empty($o['all_day']) && !empty($o['ends_at'])) ? date('H:i', (int)$o['ends_at']) : '',
            ];
        }
        if (empty($opts)) $opts[] = ['date' => '', 'start' => '', 'end' => ''];
        return [
            'title'        => $poll['title'],
            'description'  => $poll['description'],
            'location'     => $poll['location'],
            'allow_maybe'  => (int)$poll['allow_maybe'] === 1,
            'hide_results' => (int)$poll['hide_results'] === 1,
            'notify_owner' => (int)$poll['notify_owner'] === 1,
            'deadline'     => !empty($poll['deadline']) ? date('Y-m-d\TH:i', (int)$poll['deadline']) : '',
            'options'      => $opts,
        ];
    }

    private static function readForm(): array
    {
        $dates  = (array)Helper::post('opt_date', []);
        $starts = (array)Helper::post('opt_start', []);
        $ends   = (array)Helper::post('opt_end', []);
        $options = [];
        foreach ($dates as $i => $d) {
            $options[] = [
                'date'  => trim((string)$d),
                'start' => trim((string)($starts[$i] ?? '')),
                'end'   => trim((string)($ends[$i] ?? '')),
            ];
        }
        if (empty($options)) $options[] = ['date' => '', 'start' => '', 'end' => ''];

        return [
            'title'        => self::singleLine((string)Helper::post('title', '')),
            'description'  => trim((string)Helper::post('description', '')),
            'location'     => self::singleLine((string)Helper::post('location', '')),
            'allow_maybe'  => Helper::post('allow_maybe', '0') === '1',
            'hide_results' => Helper::post('hide_results', '0') === '1',
            'notify_owner' => Helper::post('notify_owner', '0') === '1',
            'deadline'     => trim((string)Helper::post('deadline', '')),
            'options'      => $options,
        ];
    }

    /** @return string[] list of error messages (empty == valid) */
    private static function validateForm(array $f): array
    {
        $errors = [];
        if ($f['title'] === '')                            $errors[] = Lang::get('mp_err_title_required');
        if (mb_strlen($f['title']) > self::MAX_TITLE_LEN)  $errors[] = Lang::get('mp_err_title_long');
        if (mb_strlen($f['description']) > self::MAX_TEXT_LEN) $errors[] = Lang::get('mp_err_desc_long');
        if (mb_strlen($f['location']) > self::MAX_LOC_LEN) $errors[] = Lang::get('mp_err_loc_long');

        $parsed = self::parseOptions($f['options']);
        if (empty($parsed))                                $errors[] = Lang::get('mp_err_no_options');
        if (count($parsed) > self::MAX_OPTIONS)            $errors[] = Lang::get('mp_err_too_many_options');

        if ($f['deadline'] !== '' && self::parseDeadline($f['deadline']) === null) {
            $errors[] = Lang::get('mp_err_deadline');
        }
        return $errors;
    }

    /** Turn raw {date,start,end} rows into normalised option records (sorted, deduped by start). */
    private static function parseOptions(array $rows): array
    {
        $out = [];
        $seen = [];
        $order = 0;
        foreach ($rows as $r) {
            $date = (string)($r['date'] ?? '');
            if ($date === '') continue;
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) continue;
            $start = (string)($r['start'] ?? '');
            $end   = (string)($r['end'] ?? '');
            $allDay = 0;
            if ($start === '' || !preg_match('/^\d{2}:\d{2}$/', $start)) { $allDay = 1; $start = '00:00'; }
            $startTs = strtotime($date . ' ' . $start);
            if ($startTs === false) continue;
            $endTs = null;
            if (!$allDay && $end !== '' && preg_match('/^\d{2}:\d{2}$/', $end)) {
                $e = strtotime($date . ' ' . $end);
                if ($e !== false && $e > $startTs) $endTs = $e;
            }
            $key = $startTs . '|' . ($endTs ?? '') . '|' . $allDay;
            if (isset($seen[$key])) continue;
            $seen[$key] = true;
            $out[] = ['starts_at' => $startTs, 'ends_at' => $endTs, 'all_day' => $allDay, 'sort_order' => $order++];
        }
        usort($out, fn($a, $b) => $a['starts_at'] <=> $b['starts_at']);
        foreach ($out as $i => &$o) $o['sort_order'] = $i;
        unset($o);
        return $out;
    }

    private static function parseDeadline(string $v): ?int
    {
        $v = str_replace('T', ' ', $v);
        $ts = strtotime($v);
        return $ts === false ? null : $ts;
    }

    private static function persistNew(array $f, int $ownerId): int
    {
        $db = Database::getInstance();
        $token = self::freshToken();
        $db->beginTransaction();
        try {
            $pollId = (int)$db->insert(
                'INSERT INTO mp_polls (owner_id, token, title, description, location, allow_maybe, hide_results, deadline, notify_owner)
                 VALUES (?,?,?,?,?,?,?,?,?)',
                [
                    $ownerId, $token, $f['title'], $f['description'], $f['location'],
                    $f['allow_maybe'] ? 1 : 0, $f['hide_results'] ? 1 : 0,
                    $f['deadline'] !== '' ? self::parseDeadline($f['deadline']) : null,
                    $f['notify_owner'] ? 1 : 0,
                ]
            );
            foreach (self::parseOptions($f['options']) as $o) {
                $db->execute(
                    'INSERT INTO mp_options (poll_id, starts_at, ends_at, all_day, sort_order) VALUES (?,?,?,?,?)',
                    [$pollId, $o['starts_at'], $o['ends_at'], $o['all_day'], $o['sort_order']]
                );
            }
            $db->commit();
        } catch (\Throwable $e) {
            $db->rollback();
            throw $e;
        }
        return $pollId;
    }

    private static function persistEdit(int $pollId, array $f): void
    {
        $db = Database::getInstance();
        $db->beginTransaction();
        try {
            $db->execute(
                'UPDATE mp_polls SET title=?, description=?, location=?, allow_maybe=?, hide_results=?, deadline=?, notify_owner=? WHERE id=?',
                [
                    $f['title'], $f['description'], $f['location'],
                    $f['allow_maybe'] ? 1 : 0, $f['hide_results'] ? 1 : 0,
                    $f['deadline'] !== '' ? self::parseDeadline($f['deadline']) : null,
                    $f['notify_owner'] ? 1 : 0, $pollId,
                ]
            );
            // Reconcile options: keep the ones still present (matched by start/end/all_day),
            // drop the rest (their votes cascade), insert brand-new ones.
            $existing = $db->fetchAll('SELECT * FROM mp_options WHERE poll_id = ?', [$pollId]);
            $existingByKey = [];
            foreach ($existing as $e) {
                $existingByKey[(int)$e['starts_at'] . '|' . ((int)($e['ends_at'] ?? 0)) . '|' . (int)$e['all_day']] = $e;
            }
            $keepIds = [];
            foreach (self::parseOptions($f['options']) as $o) {
                $key = $o['starts_at'] . '|' . ((int)($o['ends_at'] ?? 0)) . '|' . $o['all_day'];
                if (isset($existingByKey[$key])) {
                    $eid = (int)$existingByKey[$key]['id'];
                    $keepIds[] = $eid;
                    $db->execute('UPDATE mp_options SET sort_order=? WHERE id=?', [$o['sort_order'], $eid]);
                } else {
                    $newId = (int)$db->insert(
                        'INSERT INTO mp_options (poll_id, starts_at, ends_at, all_day, sort_order) VALUES (?,?,?,?,?)',
                        [$pollId, $o['starts_at'], $o['ends_at'], $o['all_day'], $o['sort_order']]
                    );
                    $keepIds[] = $newId;
                }
            }
            if (!empty($keepIds)) {
                $ph = implode(',', array_fill(0, count($keepIds), '?'));
                $db->execute("DELETE FROM mp_options WHERE poll_id = ? AND id NOT IN ($ph)", array_merge([$pollId], $keepIds));
            } else {
                $db->execute('DELETE FROM mp_options WHERE poll_id = ?', [$pollId]);
            }
            // If the slot that was finalized got removed, drop the finalization.
            $fin = $db->fetch('SELECT final_option_id FROM mp_polls WHERE id = ?', [$pollId]);
            if ($fin && !empty($fin['final_option_id']) && !in_array((int)$fin['final_option_id'], $keepIds, true)) {
                $db->execute('UPDATE mp_polls SET final_option_id = NULL WHERE id = ?', [$pollId]);
            }
            $db->commit();
        } catch (\Throwable $e) {
            $db->rollback();
            throw $e;
        }
    }

    private static function freshToken(): string
    {
        $db = Database::getInstance();
        do {
            $token = bin2hex(random_bytes(16));
        } while ($db->fetch('SELECT 1 FROM mp_polls WHERE token = ?', [$token]));
        return $token;
    }

    /** LIKE pattern with `%`, `_` and `\` escaped (paired with `ESCAPE '\\'`). */
    private static function buildLike(string $q): string
    {
        return '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q) . '%';
    }

    /**
     * The `available_users_filter` plugin hook expressed as a WHERE fragment +
     * params, validated with the same allowlist the core people-search uses.
     * Vanilla Plenvo returns an empty filter (every active user is reachable);
     * Provider Edition narrows it to the current user's group members.
     */
    private static function userVisibilityWhere(): array
    {
        $f = PluginManager::filter(
            'available_users_filter',
            ['extra_where' => '', 'extra_params' => []],
            ['is_admin' => Auth::isAdmin()]
        );
        $where  = (string)($f['extra_where'] ?? '');
        $params = array_values((array)($f['extra_params'] ?? []));
        if ($where !== '' && !ProjectController::isSafeWhereFragment($where)) {
            error_log('[meetingpoll] available_users_filter returned an unsafe SQL fragment, ignoring: ' . $where);
            $where = '';
            $params = [];
        }
        return ['where' => $where, 'params' => $params];
    }

    /** Queues the "you're invited to a scheduling poll" e-mail. The link carries
     *  the invitee's token so the poll page can pre-fill their e-mail address. */
    private static function sendInviteEmail(array $poll, string $email, string $name, string $inviteToken = ''): void
    {
        if (Helper::getSetting('notifications_enabled', '1') !== '1') return;
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) return;

        $shareUrl = Helper::baseUrl() . url('/poll/' . $poll['token']);
        if (preg_match('/^[a-f0-9]{32}$/', $inviteToken)) $shareUrl .= '?i=' . $inviteToken;
        $greetTo  = $name !== '' ? $name : $email;
        $loc      = trim((string)$poll['location']);
        $deadline = !empty($poll['deadline']) ? self::fmtTs($poll['deadline']) : '';

        $subject = Lang::get('mp_mail_invite_subject', ['title' => $poll['title']]);
        $body = '<p>' . Helper::e(Lang::get('mp_mail_greeting', ['name' => $greetTo])) . '</p>'
              . '<p>' . Helper::e(Lang::get('mp_mail_invite_body', ['title' => $poll['title']])) . '</p>'
              . ($loc !== '' ? '<p>' . Helper::e(Lang::get('mp_location')) . ': ' . Helper::e($loc) . '</p>' : '')
              . ($deadline !== '' ? '<p>' . Helper::e(Lang::get('mp_meta_deadline', ['date' => $deadline])) . '</p>' : '')
              . '<p><a href="' . Helper::e($shareUrl) . '">' . Helper::e(Lang::get('mp_mail_open_poll')) . '</a></p>';
        $text = Lang::get('mp_mail_greeting', ['name' => $greetTo]) . "\n\n"
              . Lang::get('mp_mail_invite_body', ['title' => $poll['title']]) . "\n"
              . ($loc !== '' ? Lang::get('mp_location') . ': ' . $loc . "\n" : '')
              . ($deadline !== '' ? Lang::get('mp_meta_deadline', ['date' => $deadline]) . "\n" : '')
              . "\n" . $shareUrl;
        Mailer::queue($email, $subject, $body, $text);
    }

    // ── Notifications ───────────────────────────────────────────────────────

    /** Owner gets a heads-up when somebody (un)changes their response. */
    public static function notifyOwnerNewResponse(array $poll, string $responderName): void
    {
        if ((int)$poll['notify_owner'] !== 1) return;
        if (Helper::getSetting('notifications_enabled', '1') !== '1') return;
        $owner = Database::getInstance()->fetch('SELECT email, username FROM users WHERE id = ? AND is_active = 1', [(int)$poll['owner_id']]);
        if (!$owner || empty($owner['email'])) return;

        $manageUrl = Helper::baseUrl() . url('/polls/' . (int)$poll['id']);
        $subject   = Lang::get('mp_mail_new_response_subject', ['title' => $poll['title']]);
        $body = '<p>' . Helper::e(Lang::get('mp_mail_greeting', ['name' => $owner['username']])) . '</p>'
              . '<p>' . Helper::e(Lang::get('mp_mail_new_response_body', ['name' => $responderName, 'title' => $poll['title']])) . '</p>'
              . '<p><a href="' . Helper::e($manageUrl) . '">' . Helper::e(Lang::get('mp_mail_view_results')) . '</a></p>';
        $text = Lang::get('mp_mail_greeting', ['name' => $owner['username']]) . "\n\n"
              . Lang::get('mp_mail_new_response_body', ['name' => $responderName, 'title' => $poll['title']]) . "\n\n"
              . $manageUrl;
        Mailer::queue($owner['email'], $subject, $body, $text);
    }

    /** Every participant with an e-mail address is told which slot won. */
    private static function notifyParticipantsFinalized(array $poll, array $option): void
    {
        if (Helper::getSetting('notifications_enabled', '1') !== '1') return;
        $db        = Database::getInstance();
        $shareUrl  = Helper::baseUrl() . url('/poll/' . $poll['token']);
        $whenLabel = self::optionLabel($option);

        $rows = $db->fetchAll(
            'SELECT r.name, r.email, u.email AS user_email
               FROM mp_responses r
               LEFT JOIN users u ON u.id = r.user_id AND u.is_active = 1
              WHERE r.poll_id = ?',
            [(int)$poll['id']]
        );
        $seen = [];
        foreach ($rows as $r) {
            $email = trim((string)($r['email'] ?: ($r['user_email'] ?? '')));
            if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) continue;
            $lk = strtolower($email);
            if (isset($seen[$lk])) continue;
            $seen[$lk] = true;

            $name    = $r['name'] !== '' ? $r['name'] : $email;
            $subject = Lang::get('mp_mail_finalized_subject', ['title' => $poll['title']]);
            $loc     = trim((string)$poll['location']);
            $body = '<p>' . Helper::e(Lang::get('mp_mail_greeting', ['name' => $name])) . '</p>'
                  . '<p>' . Helper::e(Lang::get('mp_mail_finalized_body', ['title' => $poll['title']])) . '</p>'
                  . '<p><strong>' . Helper::e($whenLabel) . '</strong></p>'
                  . ($loc !== '' ? '<p>' . Helper::e(Lang::get('mp_location')) . ': ' . Helper::e($loc) . '</p>' : '')
                  . '<p><a href="' . Helper::e($shareUrl) . '">' . Helper::e(Lang::get('mp_mail_open_poll')) . '</a></p>';
            $text = Lang::get('mp_mail_greeting', ['name' => $name]) . "\n\n"
                  . Lang::get('mp_mail_finalized_body', ['title' => $poll['title']]) . "\n"
                  . $whenLabel . "\n"
                  . ($loc !== '' ? Lang::get('mp_location') . ': ' . $loc . "\n" : '')
                  . "\n" . $shareUrl;
            Mailer::queue($email, $subject, $body, $text);
        }
    }
}
