<?php

declare(strict_types=1);

namespace PpPhp\Core\Calendar;

use PpPhp\Core\DB;

/**
 * CalendarService — the runtime engine for the standalone Calendar / event app.
 *
 * This is NOT a view over a business table; the calendar owns its data
 * (CalendarSchema tables) and models real events, sharing, meetings, reminders
 * and recurrence. Every read is scoped by a visibility ACL — a user sees an
 * event when they own it, it is public, it is shared to them (directly, via a
 * group, or to everyone), they are an attendee, or they are an admin.
 *
 * Methods take an explicit $ctx = ['uid'=>int,'groups'=>int[],'isAdmin'=>bool]
 * so the logic is testable without a session; contextFromApp() builds it from
 * the generated app's auth at request time.
 */
final class CalendarService
{
    private const C = CalendarSchema::CALENDARS;
    private const E = CalendarSchema::EVENTS;
    private const S = CalendarSchema::SHARES;
    private const A = CalendarSchema::ATTENDEES;
    private const R = CalendarSchema::REMINDERS;

    /** Name of the generated app's in-app notifications table (Misc → Advanced). */
    private static string $notifTable = 'notifications';

    /** Configure runtime bindings baked into the generated app. */
    public static function configure(array $c): void
    {
        if (!empty($c['notifTable']) && preg_match('/^[A-Za-z0-9_]+$/', (string) $c['notifTable'])) {
            self::$notifTable = (string) $c['notifTable'];
        }
    }

    /* ───────────────────────── context ───────────────────────── */

    /** Build the ACL context from the generated app's auth layer. */
    public static function contextFromApp(): array
    {
        $uid = 0; $isAdmin = false; $groups = [];
        if (class_exists('\PpPhp\Core\Security')) {
            $uid = (int) (\PpPhp\Core\Security::CurrentUserData()['id'] ?? 0);
        }
        if (class_exists('\PpPhp\Core\Auth\Permissions')) {
            $isAdmin = \PpPhp\Core\Auth\Permissions::isAdmin();
        }
        if ($uid > 0) {
            try {
                foreach (DB::FetchAll('SELECT group_id FROM `user_groups` WHERE user_id = ?', [$uid]) ?: [] as $r) {
                    $groups[] = (int) $r['group_id'];
                }
            } catch (\Throwable $e) {
                // single-role project without a user_groups table
            }
        }
        return self::ctx(['uid' => $uid, 'groups' => $groups, 'isAdmin' => $isAdmin]);
    }

    /** Normalise a context array (ints only — safe to inline into SQL). */
    public static function ctx(array $c): array
    {
        return [
            'uid'     => max(0, (int) ($c['uid'] ?? 0)),
            'groups'  => array_values(array_unique(array_map('intval', (array) ($c['groups'] ?? [])))),
            'isAdmin' => (bool) ($c['isAdmin'] ?? false),
        ];
    }

    /* ───────────────────────── ACL SQL ───────────────────────── */

    /** CSV of the viewer's group ids for an IN() clause (never empty). */
    private static function groupCsv(array $ctx): string
    {
        $g = $ctx['groups'];
        return $g ? implode(',', $g) : '0';
    }

    /**
     * Boolean SQL (ints inlined — all validated) that is true when the viewer may
     * SEE event alias $e (joined to calendar alias $c).
     */
    private static function visibleSql(array $ctx, string $e = 'e', string $c = 'c'): string
    {
        if ($ctx['isAdmin']) return '1=1';
        $uid = (int) $ctx['uid'];
        $gcsv = self::groupCsv($ctx);
        $eff = "CASE WHEN $e.visibility='inherit' THEN $c.visibility ELSE $e.visibility END";
        return "(
            $e.owner_user_id = $uid
            OR ($eff) = 'public'
            OR EXISTS (SELECT 1 FROM `" . self::A . "` a WHERE a.event_id=$e.id AND a.user_id=$uid)
            OR EXISTS (SELECT 1 FROM `" . self::S . "` s
                WHERE (s.event_id=$e.id OR s.calendar_id=$e.calendar_id)
                  AND (s.principal_type='everyone'
                       OR (s.principal_type='user'  AND s.principal_id=$uid)
                       OR (s.principal_type='group' AND s.principal_id IN ($gcsv))))
        )";
    }

    /** Boolean SQL that is true when the viewer may EDIT event alias $e. */
    private static function editableSql(array $ctx, string $e = 'e'): string
    {
        if ($ctx['isAdmin']) return '1';
        $uid = (int) $ctx['uid'];
        $gcsv = self::groupCsv($ctx);
        return "($e.owner_user_id = $uid
            OR EXISTS (SELECT 1 FROM `" . self::S . "` s
                WHERE (s.event_id=$e.id OR s.calendar_id=$e.calendar_id) AND s.permission='edit'
                  AND (s.principal_type='everyone'
                       OR (s.principal_type='user'  AND s.principal_id=$uid)
                       OR (s.principal_type='group' AND s.principal_id IN ($gcsv)))))";
    }

    /* ───────────────────────── calendars ───────────────────────── */

    /** Calendars the viewer can see: own + public + explicitly shared. */
    public static function calendars(array $ctx): array
    {
        CalendarSchema::ensure();
        $uid = (int) $ctx['uid'];
        // Guarantee the signed-in user always has a personal calendar, so the
        // sidebar is never empty and new events have a home.
        if ($uid > 0) CalendarSchema::defaultCalendarFor($uid);
        $gcsv = self::groupCsv($ctx);
        $where = $ctx['isAdmin'] ? '1=1' : "(
            c.owner_user_id = $uid OR c.owner_user_id IS NULL OR c.visibility='public'
            OR EXISTS (SELECT 1 FROM `" . self::S . "` s WHERE s.calendar_id=c.id
                AND (s.principal_type='everyone'
                     OR (s.principal_type='user'  AND s.principal_id=$uid)
                     OR (s.principal_type='group' AND s.principal_id IN ($gcsv)))))";
        $rows = DB::FetchAll("SELECT id, name, color, owner_user_id, visibility, is_personal, is_default
            FROM `" . self::C . "` c WHERE $where ORDER BY is_default DESC, name ASC") ?: [];
        return array_map(static function ($r) use ($uid) {
            return [
                'id'       => (int) $r['id'],
                'name'     => (string) $r['name'],
                'color'    => (string) $r['color'],
                'mine'     => ((int) ($r['owner_user_id'] ?? 0)) === $uid,
                'visibility' => (string) $r['visibility'],
                'isDefault' => (int) $r['is_default'] === 1,
            ];
        }, $rows);
    }

    public static function createCalendar(array $ctx, array $d): array
    {
        CalendarSchema::ensure();
        if ($ctx['uid'] <= 0) return ['success' => false, 'error' => 'Not signed in.'];
        $now = date('Y-m-d H:i:s');
        DB::Insert(self::C, [
            'name'          => self::str($d['name'] ?? 'Calendar', 120) ?: 'Calendar',
            'color'         => self::color($d['color'] ?? '#2563eb'),
            'owner_user_id' => (int) $ctx['uid'],
            'visibility'    => self::vis($d['visibility'] ?? 'private'),
            'is_personal'   => 1,
            'is_default'    => 0,
            'created_at'    => $now, 'updated_at' => $now,
        ]);
        return ['success' => true, 'id' => (int) DB::FetchOne("SELECT LAST_INSERT_ID()")];
    }

    /* ───────────────────────── events (read) ───────────────────────── */

    /**
     * Visible event occurrences overlapping [$from,$to] (ISO dates). Recurring
     * events are expanded into concrete occurrences that share the master id.
     *
     * @return list<array<string,mixed>>
     */
    public static function events(array $ctx, string $from, string $to, ?array $calendarIds = null): array
    {
        CalendarSchema::ensure();
        $from = self::date($from) ?: date('Y-m-d');
        $to   = self::date($to) ?: $from;
        $lo = $from . ' 00:00:00';
        $hi = $to . ' 23:59:59';

        $calFilter = '';
        if (is_array($calendarIds) && $calendarIds !== []) {
            $ids = implode(',', array_map('intval', $calendarIds)) ?: '0';
            $calFilter = " AND e.calendar_id IN ($ids)";
        }

        $sql = "SELECT e.*, c.name AS _cal_name, c.color AS _cal_color,
                    (CASE WHEN e.visibility='inherit' THEN c.visibility ELSE e.visibility END) AS _eff_vis,
                    " . self::editableSql($ctx, 'e') . " AS _can_edit
                FROM `" . self::E . "` e
                JOIN `" . self::C . "` c ON c.id = e.calendar_id
                WHERE " . self::visibleSql($ctx, 'e', 'c') . $calFilter . "
                  AND (
                    ((e.rrule IS NULL OR e.rrule='') AND e.starts_at <= ? AND COALESCE(e.ends_at, e.starts_at) >= ?)
                    OR (e.rrule IS NOT NULL AND e.rrule<>'' AND e.starts_at <= ? AND (e.rrule_until IS NULL OR e.rrule_until >= ?))
                  )
                ORDER BY e.starts_at ASC";
        $rows = DB::FetchAll($sql, [$hi, $lo, $hi, $lo]) ?: [];

        $out = [];
        foreach ($rows as $r) {
            $rrule = (string) ($r['rrule'] ?? '');
            if ($rrule !== '') {
                foreach (CalendarRecurrence::expand($rrule, (string) $r['starts_at'], (string) ($r['ends_at'] ?: ''), $from, $to) as $occ) {
                    $out[] = self::mapEvent($r, $occ['start'], $occ['end'], true);
                }
            } else {
                $out[] = self::mapEvent($r, (string) $r['starts_at'], (string) ($r['ends_at'] ?: '') ?: null, false);
            }
        }

        // Attach attendee initials (for avatar stacks) in ONE batch query.
        $ids = array_values(array_unique(array_map(static fn ($e) => (int) $e['id'], $out)));
        if ($ids !== []) {
            $in = implode(',', $ids);
            $att = [];
            try {
                foreach (DB::FetchAll("SELECT a.event_id AS eid, COALESCE(u.username, '') AS name
                    FROM `" . self::A . "` a LEFT JOIN `users` u ON u.id = a.user_id
                    WHERE a.event_id IN ($in) ORDER BY a.is_organizer DESC, a.id ASC") ?: [] as $r2) {
                    $att[(int) $r2['eid']][] = ['name' => (string) $r2['name'], 'initials' => self::initials((string) $r2['name'])];
                }
            } catch (\Throwable $e) {
            }
            foreach ($out as &$o) { $o['attendees'] = $att[$o['id']] ?? []; }
            unset($o);
        }
        return $out;
    }

    /** Up-to-two-letter uppercase initials from a name/username. */
    private static function initials(string $name): string
    {
        $name = trim($name);
        if ($name === '') return '?';
        $parts = preg_split('/[\s._-]+/', $name) ?: [$name];
        $s = '';
        foreach ($parts as $p) { if ($p !== '') { $s .= mb_strtoupper(mb_substr($p, 0, 1)); if (mb_strlen($s) >= 2) break; } }
        return $s !== '' ? $s : mb_strtoupper(mb_substr($name, 0, 2));
    }

    private static function mapEvent(array $r, string $start, ?string $end, bool $recurring): array
    {
        $color = (string) ($r['color'] ?: $r['_cal_color'] ?: '#2563eb');
        return [
            'id'         => (int) $r['id'],
            'title'      => (string) $r['title'],
            'start'      => $start,
            'end'        => $end ?: null,
            'allDay'     => (int) $r['all_day'] === 1,
            'color'      => $color,
            'calendarId' => (int) $r['calendar_id'],
            'calendar'   => (string) $r['_cal_name'],
            'location'   => (string) ($r['location'] ?? ''),
            'icon'       => (string) ($r['icon'] ?? ''),
            'image'      => (string) ($r['image'] ?? ''),
            'description' => (string) ($r['description'] ?? ''),
            'editable'   => (int) $r['_can_edit'] === 1,
            'recurring'  => $recurring,
            'visibility' => (string) $r['_eff_vis'],
        ];
    }

    /** A single event with its shares / attendees / reminders, for the editor. */
    public static function getEvent(array $ctx, int $id): ?array
    {
        CalendarSchema::ensure();
        $row = DB::FetchAll("SELECT e.*, c.color AS _cal_color, c.name AS _cal_name,
                " . self::editableSql($ctx, 'e') . " AS _can_edit
            FROM `" . self::E . "` e JOIN `" . self::C . "` c ON c.id=e.calendar_id
            WHERE e.id = ? AND " . self::visibleSql($ctx, 'e', 'c') . " LIMIT 1", [$id])[0] ?? null;
        if (!$row) return null;
        $row['shares']    = DB::FetchAll("SELECT id, principal_type, principal_id, permission FROM `" . self::S . "` WHERE event_id=? OR calendar_id=?", [$id, (int) $row['calendar_id']]) ?: [];
        $row['attendees'] = DB::FetchAll("SELECT user_id, response, is_organizer FROM `" . self::A . "` WHERE event_id=?", [$id]) ?: [];
        $row['reminders'] = DB::FetchAll("SELECT minutes_before, method FROM `" . self::R . "` WHERE event_id=?", [$id]) ?: [];
        $row['can_edit']  = (int) $row['_can_edit'] === 1;
        return $row;
    }

    /* ───────────────────────── events (write) ───────────────────────── */

    public static function createEvent(array $ctx, array $d): array
    {
        CalendarSchema::ensure();
        if ($ctx['uid'] <= 0) return ['success' => false, 'error' => 'Not signed in.'];

        $calId = (int) ($d['calendarId'] ?? 0);
        if ($calId <= 0) $calId = CalendarSchema::defaultCalendarFor((int) $ctx['uid']);
        if (!self::canUseCalendar($ctx, $calId)) return ['success' => false, 'error' => 'You cannot add to that calendar.'];

        $start = self::stamp($d['start'] ?? '', !empty($d['allDay']));
        if ($start === null) return ['success' => false, 'error' => 'A valid start date/time is required.'];
        $end = self::stamp($d['end'] ?? '', !empty($d['allDay']));

        $now = date('Y-m-d H:i:s');
        $rrule = self::rrule($d['rrule'] ?? '');
        DB::Insert(self::E, [
            'calendar_id'   => $calId,
            'title'         => self::str($d['title'] ?? '', 255) ?: '(untitled)',
            'description'   => self::str($d['description'] ?? '', 20000),
            'location'      => self::str($d['location'] ?? '', 255),
            'starts_at'     => $start,
            'ends_at'       => $end,
            'all_day'       => !empty($d['allDay']) ? 1 : 0,
            'color'         => ($d['color'] ?? '') !== '' ? self::color($d['color']) : null,
            'icon'          => self::str($d['icon'] ?? '', 40) ?: null,
            'image'         => self::str($d['image'] ?? '', 500) ?: null,
            'owner_user_id' => (int) $ctx['uid'],
            'visibility'    => self::vis($d['visibility'] ?? 'inherit', true),
            'rrule'         => $rrule ?: null,
            'rrule_until'   => $rrule ? self::rruleUntil($rrule, $start) : null,
            'status'        => 'confirmed',
            'created_at'    => $now, 'updated_at' => $now,
        ]);
        $id = (int) DB::FetchOne("SELECT LAST_INSERT_ID()");
        self::applyShares($id, $calId, $d['shares'] ?? null);
        self::applyReminders($id, $d['reminders'] ?? null);
        self::applyAttendees($ctx, $id, $d['attendees'] ?? null);
        // Circulate the new event to its audience via the chosen channel(s).
        $delivery = (string) ($d['delivery'] ?? 'none');
        if (!in_array($delivery, ['notify', 'email', 'both', 'none'], true)) $delivery = 'none';
        $circ = $delivery !== 'none' ? self::circulate($ctx, $id, $delivery) : ['notified' => 0, 'emailed' => 0];
        return ['success' => true, 'id' => $id] + $circ;
    }

    public static function updateEvent(array $ctx, int $id, array $d): array
    {
        CalendarSchema::ensure();
        $ev = self::loadEditable($ctx, $id);
        if (!$ev) return ['success' => false, 'error' => 'Not found or not editable.'];

        $allDay = array_key_exists('allDay', $d) ? !empty($d['allDay']) : ((int) $ev['all_day'] === 1);
        $set = ['updated_at' => date('Y-m-d H:i:s')];
        if (array_key_exists('title', $d))       $set['title'] = self::str($d['title'], 255) ?: '(untitled)';
        if (array_key_exists('description', $d))  $set['description'] = self::str($d['description'], 20000);
        if (array_key_exists('location', $d))     $set['location'] = self::str($d['location'], 255);
        if (array_key_exists('allDay', $d))       $set['all_day'] = $allDay ? 1 : 0;
        if (array_key_exists('color', $d))        $set['color'] = $d['color'] !== '' ? self::color($d['color']) : null;
        if (array_key_exists('icon', $d))         $set['icon'] = self::str($d['icon'], 40) ?: null;
        if (array_key_exists('image', $d))        $set['image'] = self::str($d['image'], 500) ?: null;
        if (array_key_exists('visibility', $d))   $set['visibility'] = self::vis($d['visibility'], true);
        if (array_key_exists('calendarId', $d) && self::canUseCalendar($ctx, (int) $d['calendarId'])) $set['calendar_id'] = (int) $d['calendarId'];
        if (array_key_exists('start', $d)) { $s = self::stamp($d['start'], $allDay); if ($s !== null) $set['starts_at'] = $s; }
        if (array_key_exists('end', $d))   { $set['ends_at'] = self::stamp($d['end'], $allDay); }
        if (array_key_exists('rrule', $d)) {
            $rr = self::rrule($d['rrule']);
            $set['rrule'] = $rr ?: null;
            $set['rrule_until'] = $rr ? self::rruleUntil($rr, $set['starts_at'] ?? (string) $ev['starts_at']) : null;
        }
        // Snapshot who the event already reached, so if a delivery channel is
        // chosen we notify ONLY the people newly added in this edit (never
        // re-spam existing attendees/shares on an unrelated save).
        $beforeRec = self::eventRecipients($id);

        DB::Update(self::E, $set, ['id' => $id]);
        if (array_key_exists('shares', $d))    self::applyShares($id, (int) ($set['calendar_id'] ?? $ev['calendar_id']), $d['shares'], true);
        if (array_key_exists('reminders', $d)) self::applyReminders($id, $d['reminders'], true);
        if (array_key_exists('attendees', $d)) self::applyAttendees($ctx, $id, $d['attendees'], true);

        $delivery = (string) ($d['delivery'] ?? 'none');
        if (!in_array($delivery, ['notify', 'email', 'both', 'none'], true)) $delivery = 'none';
        $circ = ['notified' => 0, 'emailed' => 0];
        if ($delivery !== 'none') {
            $afterRec = self::eventRecipients($id);
            $newUsers = array_values(array_diff($afterRec['users'], $beforeRec['users']));
            $newEveryone = !empty($afterRec['everyone']) && empty($beforeRec['everyone']);
            if ($newUsers !== [] || $newEveryone) {
                $circ = self::circulate($ctx, $id, $delivery, ['users' => $newUsers, 'everyone' => $newEveryone]);
            }
        }
        return ['success' => true, 'id' => $id] + $circ;
    }

    /** Drag / resize convenience — moves start (and end, preserving duration). */
    public static function reschedule(array $ctx, int $id, string $start, ?string $end = null): array
    {
        $ev = self::loadEditable($ctx, $id);
        if (!$ev) return ['success' => false, 'error' => 'Not editable.'];
        $allDay = (int) $ev['all_day'] === 1;
        $ns = self::stamp($start, $allDay);
        if ($ns === null) return ['success' => false, 'error' => 'Invalid date.'];
        $ne = self::stamp((string) $end, $allDay);
        if ($ne === null && (string) ($ev['ends_at'] ?? '') !== '') {
            $dur = strtotime((string) $ev['ends_at']) - strtotime((string) $ev['starts_at']);
            if ($dur > 0) $ne = date($allDay ? 'Y-m-d' : 'Y-m-d H:i:s', strtotime($ns) + $dur);
        }
        DB::Update(self::E, ['starts_at' => $ns, 'ends_at' => $ne, 'updated_at' => date('Y-m-d H:i:s')], ['id' => $id]);
        return ['success' => true, 'start' => $ns, 'end' => $ne];
    }

    public static function deleteEvent(array $ctx, int $id): array
    {
        $ev = self::loadEditable($ctx, $id);
        if (!$ev) return ['success' => false, 'error' => 'Not found or not editable.'];
        DB::Delete(self::E, ['id' => $id]);
        DB::Delete(self::S, ['event_id' => $id]);
        DB::Delete(self::A, ['event_id' => $id]);
        DB::Delete(self::R, ['event_id' => $id]);
        return ['success' => true];
    }

    /* ───────────────────────── sharing / RSVP / reminders ───────────────────────── */

    public static function setRsvp(array $ctx, int $eventId, string $response): array
    {
        CalendarSchema::ensure();
        $resp = in_array($response, ['accepted', 'declined', 'tentative', 'pending'], true) ? $response : 'pending';
        $uid = (int) $ctx['uid'];
        if ($uid <= 0) return ['success' => false, 'error' => 'Not signed in.'];
        // Must be an invited attendee (or the event must be visible to them).
        $row = DB::FetchAll("SELECT id FROM `" . self::A . "` WHERE event_id=? AND user_id=? LIMIT 1", [$eventId, $uid])[0] ?? null;
        if ($row) DB::Update(self::A, ['response' => $resp], ['id' => (int) $row['id']]);
        else DB::Insert(self::A, ['event_id' => $eventId, 'user_id' => $uid, 'response' => $resp, 'is_organizer' => 0, 'created_at' => date('Y-m-d H:i:s')]);
        return ['success' => true, 'response' => $resp];
    }

    /* ───────────────────────── email reminders (cron) ───────────────────────── */

    /**
     * Send any due email reminders (events starting within their lead time that
     * haven't been notified yet) to the owner + attendees, and mark them sent.
     * Safe to call from cron on every project — a no-op when the calendar has
     * never been provisioned. Returns the number of reminders dispatched.
     */
    public static function dispatchReminders(): int
    {
        try {
            $has = DB::FetchOne("SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1", [self::R]);
        } catch (\Throwable $e) {
            return 0;
        }
        if (!$has) return 0;

        $rows = DB::FetchAll(
            "SELECT r.id AS rid, r.minutes_before, r.method, e.id AS eid, e.title, e.starts_at, e.location, e.owner_user_id
             FROM `" . self::R . "` r JOIN `" . self::E . "` e ON e.id = r.event_id
             WHERE r.method IN ('email','notify') AND r.sent_at IS NULL
               AND e.starts_at >= NOW()
               AND DATE_SUB(e.starts_at, INTERVAL r.minutes_before MINUTE) <= NOW()"
        ) ?: [];

        $sent = 0;
        $base = defined('APP_BASE') ? (string) constant('APP_BASE') : '';
        foreach ($rows as $r) {
            if ($r['method'] === 'notify') {
                // In-app: notify the owner + attendees.
                $rec = self::eventRecipients((int) $r['eid']);
                $ids = array_values(array_unique(array_merge($rec['users'], [(int) $r['owner_user_id']])));
                self::notifyUsers($ids, false, 'Reminder: ' . (string) $r['title'] . ' · ' . date('M j, g:ia', strtotime((string) $r['starts_at'])), $base . '/generated/calendar/index.php');
            } else {
                foreach (self::recipientEmails((int) $r['eid'], (int) $r['owner_user_id']) as $to) {
                    try {
                        \PpPhp\Core\Email::send([
                            'to'      => $to,
                            'subject' => 'Reminder: ' . (string) $r['title'],
                            'body'    => "Reminder for \"" . (string) $r['title'] . "\"\n"
                                . 'Starts: ' . (string) $r['starts_at']
                                . ((string) ($r['location'] ?? '') !== '' ? "\nLocation: " . (string) $r['location'] : ''),
                        ]);
                    } catch (\Throwable $e) {
                        // a single bad address must not stop the batch
                    }
                }
            }
            DB::Update(self::R, ['sent_at' => date('Y-m-d H:i:s')], ['id' => (int) $r['rid']]);
            $sent++;
        }
        return $sent;
    }

    /* ───────────────────────── in-app notifications + circulation ───────────────────────── */

    /** Ensure the generated app's notifications table exists (matches its schema). */
    private static function ensureNotifTable(): void
    {
        $t = self::$notifTable;
        try {
            DB::Exec("CREATE TABLE IF NOT EXISTS `$t` (
                `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                `user_id` INT UNSIGNED DEFAULT NULL,
                `message` VARCHAR(500) NOT NULL,
                `link` VARCHAR(500) DEFAULT NULL,
                `is_read` TINYINT(1) NOT NULL DEFAULT 0,
                `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_notif_user` (`user_id`, `is_read`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        } catch (\Throwable $e) {
        }
    }

    /** Write an in-app notification (one row per user; null user = everyone). */
    private static function notifyUsers(array $userIds, bool $everyone, string $message, string $link): int
    {
        self::ensureNotifTable();
        $t = self::$notifTable;
        $n = 0;
        $ids = $everyone ? [null] : array_values(array_unique(array_filter(array_map('intval', $userIds))));
        foreach ($ids as $uid) {
            try {
                DB::Insert($t, ['user_id' => $uid, 'message' => mb_substr($message, 0, 500), 'link' => mb_substr($link, 0, 500), 'is_read' => 0, 'created_at' => date('Y-m-d H:i:s')]);
                $n++;
            } catch (\Throwable $e) {
            }
        }
        return $n;
    }

    /** Everyone the event reaches: attendees + share targets (users/group members/everyone). */
    private static function eventRecipients(int $eventId): array
    {
        $users = [];
        $everyone = false;
        foreach (DB::FetchAll("SELECT user_id FROM `" . self::A . "` WHERE event_id=?", [$eventId]) ?: [] as $a) $users[] = (int) $a['user_id'];
        foreach (DB::FetchAll("SELECT principal_type, principal_id FROM `" . self::S . "` WHERE event_id=?", [$eventId]) ?: [] as $s) {
            if ($s['principal_type'] === 'everyone') { $everyone = true; }
            elseif ($s['principal_type'] === 'user') { $users[] = (int) $s['principal_id']; }
            elseif ($s['principal_type'] === 'group') {
                try {
                    foreach (DB::FetchAll("SELECT user_id FROM `user_groups` WHERE group_id=?", [(int) $s['principal_id']]) ?: [] as $m) $users[] = (int) $m['user_id'];
                } catch (\Throwable $e) {
                }
            }
        }
        return ['users' => array_values(array_unique(array_filter($users))), 'everyone' => $everyone];
    }

    /**
     * Circulate an event to everyone it reaches, via the chosen channel(s):
     * 'notify' (in-app), 'email', 'both', or 'none'. Returns counts.
     */
    public static function circulate(array $ctx, int $eventId, string $delivery, ?array $only = null): array
    {
        $delivery = in_array($delivery, ['notify', 'email', 'both', 'none'], true) ? $delivery : 'none';
        if ($delivery === 'none') return ['notified' => 0, 'emailed' => 0];

        $ev = DB::FetchAll("SELECT id, title, starts_at, location, owner_user_id FROM `" . self::E . "` WHERE id=? LIMIT 1", [$eventId])[0] ?? null;
        if (!$ev) return ['notified' => 0, 'emailed' => 0];
        // $only lets a caller target a specific set (e.g. edit → only new invitees);
        // otherwise circulate to everyone the event reaches.
        $rec = $only !== null
            ? ['users' => array_values(array_map('intval', (array) ($only['users'] ?? []))), 'everyone' => !empty($only['everyone'])]
            : self::eventRecipients($eventId);
        // Don't notify the organiser about their own event.
        $rec['users'] = array_values(array_diff($rec['users'], [(int) $ev['owner_user_id']]));

        $base = defined('APP_BASE') ? (string) constant('APP_BASE') : '';
        $link = $base . '/generated/calendar/index.php';
        $msg = 'Event: ' . (string) $ev['title'] . ' · ' . date('M j, g:ia', strtotime((string) $ev['starts_at']));

        $notified = 0; $emailed = 0;
        if ($delivery === 'notify' || $delivery === 'both') {
            $notified = self::notifyUsers($rec['users'], $rec['everyone'], $msg, $link);
        }
        if ($delivery === 'email' || $delivery === 'both') {
            $targets = $rec['everyone'] ? self::allUserEmails() : self::emailsForUserIds($rec['users']);
            foreach ($targets as $to) {
                try {
                    \PpPhp\Core\Email::send(['to' => $to, 'subject' => 'Invitation: ' . (string) $ev['title'], 'body' => $msg . ((string) ($ev['location'] ?? '') !== '' ? "\nLocation: " . (string) $ev['location'] : '') . "\n" . $link]);
                    $emailed++;
                } catch (\Throwable $e) {
                }
            }
        }
        return ['notified' => $notified, 'emailed' => $emailed];
    }

    private static function emailsForUserIds(array $ids): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $ids))));
        if ($ids === []) return [];
        $in = implode(',', $ids);
        $out = [];
        try {
            foreach (DB::FetchAll("SELECT email FROM `users` WHERE id IN ($in) AND email <> ''") ?: [] as $u) {
                $e = trim((string) $u['email']);
                if ($e !== '' && filter_var($e, FILTER_VALIDATE_EMAIL)) $out[] = $e;
            }
        } catch (\Throwable $e) {
        }
        return array_values(array_unique($out));
    }

    private static function allUserEmails(): array
    {
        $out = [];
        try {
            foreach (DB::FetchAll("SELECT email FROM `users` WHERE email <> '' LIMIT 2000") ?: [] as $u) {
                $e = trim((string) $u['email']);
                if ($e !== '' && filter_var($e, FILTER_VALIDATE_EMAIL)) $out[] = $e;
            }
        } catch (\Throwable $e) {
        }
        return array_values(array_unique($out));
    }

    /** Email addresses for an event's owner + attendees. */
    private static function recipientEmails(int $eventId, int $ownerId): array
    {
        $ids = [$ownerId];
        foreach (DB::FetchAll("SELECT user_id FROM `" . self::A . "` WHERE event_id = ?", [$eventId]) ?: [] as $a) {
            $ids[] = (int) $a['user_id'];
        }
        $ids = array_values(array_unique(array_filter($ids)));
        if ($ids === []) return [];
        $in = implode(',', $ids);
        $out = [];
        try {
            foreach (DB::FetchAll("SELECT email FROM `users` WHERE id IN ($in) AND email <> ''") ?: [] as $u) {
                $e = trim((string) $u['email']);
                if ($e !== '' && filter_var($e, FILTER_VALIDATE_EMAIL)) $out[] = $e;
            }
        } catch (\Throwable $e) {
        }
        return array_values(array_unique($out));
    }

    /* ───────────────────────── helpers ───────────────────────── */

    private static function loadEditable(array $ctx, int $id): ?array
    {
        $row = DB::FetchAll("SELECT e.* FROM `" . self::E . "` e JOIN `" . self::C . "` c ON c.id=e.calendar_id
            WHERE e.id=? AND " . self::editableSql($ctx, 'e') . " = 1 LIMIT 1", [$id])[0] ?? null;
        return $row ?: null;
    }

    private static function canUseCalendar(array $ctx, int $calId): bool
    {
        if ($calId <= 0) return false;
        if ($ctx['isAdmin']) return true;
        $uid = (int) $ctx['uid'];
        $gcsv = self::groupCsv($ctx);
        $row = DB::FetchAll("SELECT 1 FROM `" . self::C . "` c WHERE c.id=? AND (
            c.owner_user_id=$uid OR c.owner_user_id IS NULL OR c.visibility='public'
            OR EXISTS (SELECT 1 FROM `" . self::S . "` s WHERE s.calendar_id=c.id AND s.permission='edit'
                AND (s.principal_type='everyone' OR (s.principal_type='user' AND s.principal_id=$uid)
                     OR (s.principal_type='group' AND s.principal_id IN ($gcsv))))) LIMIT 1", [$calId]);
        return !empty($row);
    }

    private static function applyShares(int $eventId, int $calId, $shares, bool $replace = false): void
    {
        if ($shares === null) return;
        if ($replace) DB::Delete(self::S, ['event_id' => $eventId]);
        foreach ((array) $shares as $sh) {
            $type = in_array(($sh['type'] ?? ''), ['everyone', 'group', 'user'], true) ? $sh['type'] : null;
            if ($type === null) continue;
            $perm = ($sh['permission'] ?? 'view') === 'edit' ? 'edit' : 'view';
            $pid = $type === 'everyone' ? null : (int) ($sh['id'] ?? 0);
            if ($type !== 'everyone' && $pid <= 0) continue;
            DB::Insert(self::S, ['event_id' => $eventId, 'calendar_id' => null, 'principal_type' => $type, 'principal_id' => $pid, 'permission' => $perm, 'created_at' => date('Y-m-d H:i:s')]);
        }
    }

    private static function applyReminders(int $eventId, $reminders, bool $replace = false): void
    {
        if ($reminders === null) return;
        if ($replace) DB::Delete(self::R, ['event_id' => $eventId]);
        foreach ((array) $reminders as $rm) {
            $min = max(0, (int) ($rm['minutes'] ?? $rm['minutes_before'] ?? 0));
            $method = in_array(($rm['method'] ?? 'popup'), ['popup', 'email', 'notify'], true) ? (string) $rm['method'] : 'popup';
            DB::Insert(self::R, ['event_id' => $eventId, 'minutes_before' => $min, 'method' => $method]);
        }
    }

    private static function applyAttendees(array $ctx, int $eventId, $attendees, bool $replace = false): void
    {
        if ($attendees === null) return;
        if ($replace) DB::Delete(self::A, ['event_id' => $eventId]);
        // Organizer is always the event owner.
        DB::Insert(self::A, ['event_id' => $eventId, 'user_id' => (int) $ctx['uid'], 'response' => 'accepted', 'is_organizer' => 1, 'created_at' => date('Y-m-d H:i:s')]);
        foreach ((array) $attendees as $uidRaw) {
            $uid = (int) (is_array($uidRaw) ? ($uidRaw['id'] ?? 0) : $uidRaw);
            if ($uid <= 0 || $uid === (int) $ctx['uid']) continue;
            DB::Insert(self::A, ['event_id' => $eventId, 'user_id' => $uid, 'response' => 'pending', 'is_organizer' => 0, 'created_at' => date('Y-m-d H:i:s')]);
        }
    }

    /* ───────────────────────── sanitisers ───────────────────────── */

    private static function str($v, int $max): string { $v = trim((string) $v); return mb_substr($v, 0, $max); }
    private static function color($v): string { $v = trim((string) $v); return preg_match('/^(#[0-9A-Fa-f]{3,8}|[A-Za-z][A-Za-z-]{1,24})$/', $v) ? $v : '#2563eb'; }
    private static function vis($v, bool $allowInherit = false): string
    {
        $v = strtolower(trim((string) $v));
        $ok = ['private', 'public', 'groups', 'users'];
        if ($allowInherit) $ok[] = 'inherit';
        return in_array($v, $ok, true) ? $v : ($allowInherit ? 'inherit' : 'private');
    }
    private static function date(string $v): string { $t = strtotime(trim($v)); return $t ? date('Y-m-d', $t) : ''; }
    private static function stamp(string $v, bool $allDay): ?string { $v = trim($v); if ($v === '') return null; $t = strtotime($v); return $t === false ? null : date($allDay ? 'Y-m-d' : 'Y-m-d H:i:s', $t); }
    private static function rrule($v): string
    {
        $v = trim((string) $v);
        if ($v === '') return '';
        $p = CalendarRecurrence::parse($v);
        return isset($p['FREQ']) && in_array(strtoupper((string) $p['FREQ']), ['DAILY', 'WEEKLY', 'MONTHLY', 'YEARLY'], true) ? $v : '';
    }
    private static function rruleUntil(string $rrule, string $start): ?string
    {
        $p = CalendarRecurrence::parse($rrule);
        if (!empty($p['UNTIL'])) { $t = strtotime((string) $p['UNTIL']); return $t ? date('Y-m-d H:i:s', $t) : null; }
        if (!empty($p['COUNT'])) {
            // Cheap upper bound so windowed queries can prune — expand far out once.
            $occ = CalendarRecurrence::expand($rrule, $start, null, date('Y-m-d', strtotime($start)), date('Y-m-d', strtotime($start . ' +10 years')));
            $last = end($occ);
            return $last ? date('Y-m-d H:i:s', strtotime($last['start'])) : null;
        }
        return null; // open-ended
    }
}
