# Calendar Event Reminders

Calendar event reminders are sent by a cron-driven PHP CLI worker. The worker reads active events from `tbl_events`, expands recurring occurrences, evaluates each configured reminder offset, and then creates:

- in-app notifications in `tbl_notifications`
- email jobs in `tbl_email_queue`

The worker also writes dedupe records to `tbl_event_reminder_dispatch_log` so the same reminder is not queued twice for the same user, occurrence, offset, and channel.

## Purpose

This background process is used for calendar events that need reminder notifications before the event start time.

Examples:

- annual statutory report reminders
- quarterly update reminders
- personal user-created calendar reminders

## Entry Point

Worker file:

- `cli/calendar_event_reminder_worker.php`

Example manual run:

```bash
php /var/www/doi_gov_api/cli/calendar_event_reminder_worker.php
```

Example cron:

```cron
*/5 * * * * php /var/www/doi_gov_api/cli/calendar_event_reminder_worker.php
```

The worker uses UTC internally:

```php
date_default_timezone_set('UTC');
```

Reminder scheduling uses each occurrence's `start_utc`, derived from:

- `start_at_utc` / `end_at_utc` stored on `tbl_events` (sent by frontend in UTC)
- local display fields `start_date`, `start_time`, `end_date`, `end_time`

Frontend must send `start_at_utc` (and should send `end_at_utc`) on create/update so reminders fire at the correct real-world time for the user's timezone.

### Frontend payload (create / update)

Required on create (in addition to existing fields):

| Field | Format | Example |
|-------|--------|---------|
| `start_at_utc` | UTC instant | `2026-05-13 05:45:00` or `2026-05-13T05:45:00.000Z` |

Recommended:

| Field | Format | Example |
|-------|--------|---------|
| `end_at_utc` | UTC instant | `2026-05-13 07:03:33` |

Local display fields stay as today (`start_date`, `start_time`, etc.) — these are what the calendar UI shows.

JavaScript example (user picks local 11:15 AM IST on 2026-05-13):

```javascript
const startLocal = new Date('2026-05-13T11:15:00'); // browser local timezone
const payload = {
  title: 'TTTest event',
  start_date: '2026-05-13',
  end_date: '2026-05-13',
  start_time: '11:15:00',
  end_time: '12:03:33',
  start_at_utc: startLocal.toISOString().slice(0, 19).replace('T', ' '), // "2026-05-13 05:45:00"
  end_at_utc: new Date('2026-05-13T12:03:33').toISOString().slice(0, 19).replace('T', ' '),
  is_all_day: false,
  occurrence_type: 'once',
  reminders: [{ value: 5, unit: 'minutes' }],
  // ...other fields
};
```

Or keep ISO with `Z` / timezone offset — API accepts both.

On update: if you change `start_date` / `start_time` / `end_date` / `end_time`, you must also send updated `start_at_utc` / `end_at_utc`.

### Manual testing

1. Run migration for `start_at_utc` / `end_at_utc` columns on `tbl_events`.
2. Create or update an event via API/UI with `start_at_utc` = about 6 minutes from now in UTC.
3. Confirm DB: `SELECT event_id, start_date, start_time, start_at_utc FROM tbl_events WHERE event_id = ?`
4. Run worker: `php cli/calendar_event_reminder_worker.php`
5. Expect `Due reminder` when `now_utc` is within lookback window of `(start_at_utc - reminder offset)`.
6. Check `tbl_notifications` / `tbl_email_queue` and `tbl_event_reminder_dispatch_log`.

Quick SQL test (5-minute reminder, event in 6 minutes UTC):

```sql
UPDATE tbl_events
SET
  start_date = UTC_DATE(),
  end_date = UTC_DATE(),
  start_time = TIME(DATE_ADD(UTC_TIMESTAMP(), INTERVAL 6 MINUTE)),
  end_time = TIME(DATE_ADD(UTC_TIMESTAMP(), INTERVAL 36 MINUTE)),
  start_at_utc = DATE_ADD(UTC_TIMESTAMP(), INTERVAL 6 MINUTE),
  end_at_utc = DATE_ADD(UTC_TIMESTAMP(), INTERVAL 36 MINUTE),
  reminders = '[{"value":5,"unit":"minutes"}]'
WHERE event_id = 10;
```

Wait until ~1 minute before reminder time, run worker, verify dispatch.

## High-Level Workflow

1. Read reminder-related config from `tbl_platform_config`
2. Resolve channel policy for global and personal calendar reminders
3. Load active events from `tbl_events` that have non-empty `reminders`
4. Expand occurrences using `data_fetchers/class.Events.php`
5. For each occurrence:
   - calculate reminder trigger time from the reminder offset
   - compare it to the worker lookback window
6. Determine recipients:
   - personal event: event owner only
   - global event: all active users whose `role_id` matches configured role targets
7. If due:
   - create in-app notification
   - queue email
   - for global events only, route email based on platform config (`send_event_reminder_notifications_to_respective_emails`)
8. After successful channel action, write dedupe row to `tbl_event_reminder_dispatch_log`

## Required Event Data

The worker only processes events that meet all of these:

- `status = 'active'`
- `reminders` is not null and not empty
- `end_date` is not null

The worker currently queries:

```sql
SELECT event_id, user_id, title, reminders, start_date, end_date, is_all_day
FROM tbl_events
WHERE status = 'active'
  AND reminders IS NOT NULL
  AND reminders <> ''
  AND end_date IS NOT NULL
  AND end_date >= DATE_SUB(CURDATE(), INTERVAL 2 DAY)
```

## `tbl_events` Fields Used

The reminder flow relies on the separated date/time model:

- `user_id`
- `title`
- `start_date`
- `end_date`
- `start_time`
- `end_time`
- `is_all_day`
- `occurrence_type`
- `occurrence_config`
- `reminders`
- `status`

The events API and occurrence-expansion logic no longer depends on `start_datetime` and `end_datetime` for the calendar workflow. Separate date/time fields are the active contract.

## Reminder Configuration Format

`tbl_events.reminders` must be a JSON array.

Example:

```json
[
  { "value": 7, "unit": "days" },
  { "value": 2, "unit": "weeks" },
  { "value": 12, "unit": "hours" }
]
```

Allowed units:

- `days`
- `weeks`
- `hours`

Allowed value:

- positive integer only

Current worker conversion:

- `days` -> `value * 1440`
- `weeks` -> `value * 10080`
- `hours` -> `value * 60`

The API rejects invalid reminder items. The worker still skips invalid stored reminders and counts them in logs as `Invalid reminders`.

## Occurrence Types and `occurrence_config`

Supported occurrence types:

- `once`
- `custom`
- `monthly`
- `quarterly`
- `bi_yearly`
- `yearly`

Current behavior when `occurrence_config` is missing:

- `once`
  - config ignored
  - uses `start_date` to `end_date`
- `custom`
  - if `dates` array exists, uses it
  - otherwise falls back to one occurrence on `start_date`
- `monthly`
  - falls back to day-of-month from `start_date`
- `yearly`
  - falls back to month/day from `start_date`
- `quarterly`
  - requires quarter month/day values; otherwise no occurrences are generated
- `bi_yearly`
  - requires half-year month/day values; otherwise no occurrences are generated

Example yearly config:

```json
{ "month": 9, "day": 30 }
```

Example quarterly config:

```json
{
  "q1_month": 6,
  "q1_day": 1,
  "q2_month": 9,
  "q2_day": 1,
  "q3_month": 12,
  "q3_day": 1,
  "q4_month": 3,
  "q4_day": 1
}
```

## Global vs Personal Events

### Personal Events

- `user_id` contains the owner user id
- reminders are sent only to that user

### Global Events

- `user_id = NULL`
- reminders are **not** sent to all users blindly
- reminders are sent only to active users whose `role_id` is in the configured allowlist

Global recipient roles are currently read from:

- `tbl_platform_config.key = 'calendar_global_event_target_role_ids'`

Supported values:

- comma-separated string, e.g. `2,3`
- JSON array string, e.g. `[2,3]`

If the config resolves to an empty role list, global events will have no recipients.

### Personal reminders on global events (additive)

Per-user extra offsets are stored in `tbl_global_event_personal_reminders` (in addition to admin `tbl_events.reminders`).

| Action | Op | Payload |
|--------|-----|---------|
| Save | `updateCalenderEvent` | `personal_reminders_on_global: true`, `event_id`, `reminders` (`[]` clears) |
| Get list | `getAllEvents` | No token → global events only. Valid token → global + that user's personal events, with `my_personal_reminders` on each global occurrence by default. |

## Platform Config Keys

These values are read from `tbl_platform_config`:

| Key | Default | Purpose |
|-----|---------|---------|
| `calendar_reminder_lookback_minutes` | `10` | Worker checks reminders whose computed trigger time falls within `now - lookback` to `now` |
| `calendar_global_event_target_role_ids` | `[2,3]` | Role ids allowed to receive global event reminders |
| `send_event_reminder_notifications_to_respective_emails` | `false` | Global-event-only flag. `true` sends global reminders to each real recipient email; `false` redirects global reminders to configured test inboxes |
| `event_reminders_notifications_emails_tobe_sent` | `[]` | Global-event-only JSON array of override inbox emails used when the above flag is `false` |
| `notification_scope_calendar_global_event_reminder_email_enabled` | `0` | Enable email for global event reminders |
| `notification_scope_calendar_global_event_reminder_in_app_enabled` | `1` | Enable in-app for global event reminders |
| `notification_scope_calendar_personal_event_reminder_email_enabled` | `1` | Enable email for personal event reminders |
| `notification_scope_calendar_personal_event_reminder_in_app_enabled` | `1` | Enable in-app for personal event reminders |

Example manual update:

```sql
UPDATE tbl_platform_config
SET value = '[2,3,4]', updation_date = NOW()
WHERE `key` = 'calendar_global_event_target_role_ids';
```

Email routing examples:

```sql
UPDATE tbl_platform_config
SET value = 'true', updation_date = NOW()
WHERE `key` = 'send_event_reminder_notifications_to_respective_emails';

UPDATE tbl_platform_config
SET value = '["dev1@example.com","dev2@example.com"]', updation_date = NOW()
WHERE `key` = 'event_reminders_notifications_emails_tobe_sent';
```

## Required Tables

### 1. `tbl_events`

Source of event definitions and reminder config.

Important columns:

- `event_id`
- `user_id`
- `title`
- `start_date`
- `end_date`
- `start_time`
- `end_time`
- `is_all_day`
- `occurrence_type`
- `occurrence_config`
- `reminders`
- `status`

### 2. `tbl_event_reminder_dispatch_log`

Used for dedupe and operational tracking.

It prevents duplicate sends for the same:

- `event_id`
- `user_id`
- `occurrence_start`
- `reminder_offset_minutes`
- `channel`

Schema seeded in `incremental.sql`.

### 3. `tbl_notifications`

Stores in-app notifications created by the worker.

Worker inserts one row per recipient for in-app delivery.

Calendar reminders are stored with:

- `type = 'calendar_event_reminder'`
- `reference_id = event_id`
- `is_global = 'No'`

Even for global events, the worker creates per-user notification rows rather than one shared global notification row.

### 4. `tbl_email_queue`

Stores queued email jobs. Email queue worker is responsible for actual send.

Calendar event reminder emails are queued with:

- `email_type = 'calendar_event_reminder'`

### 5. `tbl_email_history`

Optional downstream tracking after queued emails are actually sent by the email queue worker.

## Notification and Email Flow

### In-App

The worker calls:

- `NotificationService::addNotification()`

That inserts into `tbl_notifications` and then pushes realtime websocket payloads using `NotificationWsPush`.

### Email

The worker calls:

- `mailToQueue()`

That inserts into `tbl_email_queue`. The actual email is later sent by:

- `cli/email_queue_worker.php`

So if reminders appear in `tbl_email_queue` but users do not receive email, check the email queue worker next.

### Config-Based Email Delivery

Global calendar reminder emails are controlled by `tbl_platform_config`.

Current behavior:

- For global events, if `send_event_reminder_notifications_to_respective_emails = true`, reminder emails are queued to the actual user email from `tbl_user_master.email`
- For global events, if `send_event_reminder_notifications_to_respective_emails = false`, reminder emails are redirected to the email list from `event_reminders_notifications_emails_tobe_sent`
- The worker still queues one email per real intended global recipient, so if one global reminder would normally go to 5 users, the selected inbox receives 5 separate emails
- Individual/personal event reminder emails are not affected by these two config keys and always route to the actual event owner email
- Redirected emails keep the same real subject and body content that the intended recipient would have received
- This means test emails show the real personalized content only; only the delivery destination changes

Implementation notes:

- `event_reminders_notifications_emails_tobe_sent` should preferably be stored as a JSON array string such as `["dev1@example.com","dev2@example.com"]`
- The worker also accepts a comma-separated string as a fallback for this config
- If `send_event_reminder_notifications_to_respective_emails = false` and the configured email list is empty, global email reminders are skipped for safety and the worker logs the skip reason
- In-app reminders are not affected by this email override behavior

## Dedupe Behavior

Dedupe is channel-specific and based on `tbl_event_reminder_dispatch_log`.

Current behavior:

1. Check if dispatch row already exists
2. If yes, skip
3. If no:
   - create in-app notification or queue email
   - then insert dispatch row

This means:

- existing dispatch log row = already processed for that channel
- no dispatch log row = channel still eligible to run

## Tracking and Debugging

### Best Tables To Check

#### Dispatch tracking

```sql
SELECT *
FROM tbl_event_reminder_dispatch_log
ORDER BY created_at DESC;
```

#### In-app notification rows

```sql
SELECT id, user_id, type, title, reference_id, creation_date
FROM tbl_notifications
WHERE type = 'calendar_event_reminder'
ORDER BY id DESC;
```

#### Queued emails

```sql
SELECT id, send_to, subject, status, email_type, created_at
FROM tbl_email_queue
WHERE email_type = 'calendar_event_reminder'
ORDER BY id DESC;
```

When `send_event_reminder_notifications_to_respective_emails = false` for global event reminders, confirm:

- `send_to` contains the configured override email list, not the real user email
- queued subject/body match the normal reminder template content

### Worker Console Output

Current worker logs:

- config values
- number of valid events with reminders
- each event reminder config
- occurrence count
- each due reminder
- each not-due reminder
- evaluation counters
- queued counts
- duplicate skip counts

Useful counters:

- `Due reminders`
- `Not due`
- `Invalid reminders`
- `Recipient targets`
- `In-app queued`
- `Emails queued`
- `Duplicates skipped`
- `No recipients skipped`

## Manual Testing

### Quick Trigger Test

The easiest manual test is to set an event so that its reminder becomes due immediately.

Example:

- reminder = `1` with unit `hours`
- event start = `UTC now + 1 hour`

Example SQL:

```sql
UPDATE tbl_events
SET
  start_date = UTC_DATE(),
  end_date = UTC_DATE(),
  start_time = TIME(DATE_ADD(UTC_TIMESTAMP(), INTERVAL 1 HOUR)),
  end_time = TIME(DATE_ADD(UTC_TIMESTAMP(), INTERVAL 2 HOUR)),
  occurrence_type = 'once',
  reminders = '[{"value":1,"unit":"hours"}]',
  status = 'active',
  updation_date = NOW()
WHERE event_id = 3;
```

Then run:

```bash
php /var/www/doi_gov_api/cli/calendar_event_reminder_worker.php
```

### Reset For Retest

If the same event/reminder/channel already ran, clear dispatch log before retesting:

```sql
DELETE FROM tbl_event_reminder_dispatch_log
WHERE event_id = 3;
```

Optional cleanup for in-app test rows:

```sql
DELETE FROM tbl_notifications
WHERE type = 'calendar_event_reminder'
  AND reference_id = 3;
```

Optional cleanup for queued email test rows:

```sql
DELETE FROM tbl_email_queue
WHERE email_type = 'calendar_event_reminder'
  AND subject LIKE '%Annual Performance Plan Report%';
```

## Common Troubleshooting

### 1. Worker says `Due reminders: 0`

Meaning:

- reminder exists
- occurrence exists
- but computed reminder trigger time is outside the worker lookback window

Check:

- event date/time in UTC
- `calendar_reminder_lookback_minutes`
- reminder offset

### 2. Worker says `Duplicates skipped`

Meaning:

- matching dispatch rows already exist in `tbl_event_reminder_dispatch_log`

Clear the relevant dispatch rows and rerun for test cases.

### 3. Dispatch log rows exist but no emails received

Check:

- rows in `tbl_email_queue`
- then `cli/email_queue_worker.php`
- SMTP config in `config/*/config_mail.php`
- `send_event_reminder_notifications_to_respective_emails`
- `event_reminders_notifications_emails_tobe_sent`

### 4. No recipients for global event

Check:

- `calendar_global_event_target_role_ids`
- active users with matching `role_id`

### 5. Event exists but no occurrences generated

Most common causes:

- missing `end_date`
- invalid `occurrence_type`
- missing `occurrence_config` for `quarterly` or `bi_yearly`

## Incremental SQL Entries

Relevant entries currently seeded in `incremental.sql`:

- `tbl_event_reminder_dispatch_log`
- `calendar_reminder_lookback_minutes`
- `calendar_global_event_target_role_ids`
- `send_event_reminder_notifications_to_respective_emails`
- `event_reminders_notifications_emails_tobe_sent`
- notification channel policy keys for calendar reminders

## Current Limitations

- Global event role targeting is one shared config value, not event-specific
- Email queue rows do not keep `reference_id` in the simplified queue schema
- Worker processes active events with reminders; it does not currently persist an event-level processing summary table

## Recommended Future Improvements

1. Replace shared global role config with event-specific target-role mapping
2. Add a dedicated reminder attempt/status log if finer operational tracking is needed
3. Add admin UI/API support for managing role targets per global event
4. Add richer email templates for calendar event reminders if business needs branded content per event type