# PRD — E-09: Calendar — Appointment Board & Reminder Execution

## Context

The Calendar module has a working week-grid appointment board (`/leads/appointment-board`) that shows counts (Conf/Res/Screened/Set) per user per day. This PRD upgrades that view to a full kanban board with draggable appointment cards, adds user/date filters to that board, and builds an automated email+SMS reminder system for calendar events.

---

## Existing Scaffolding — Do Not Rebuild

| Area | What Exists | Location |
|---|---|---|
| Appointment Board | `AppointmentBoardController@index` — week-grid table view | `app/Http/Controllers/Leads/AppointmentBoardController.php` |
| Board View | Table-based grid (user × date with counts) | `resources/views/admin/leads/appointment-board.blade.php` |
| Board Route | `GET /leads/appointment-board` name: `leads.appointment-board` | `routes/web.php` |
| Board Query | `Event::getAppointmentBoardListing($srch_params)` — groups by user/date with Conf/Res/Screened/Set counts | `app/Models/Calendar/Event.php:434` |
| Event Status Update | `EventController@updateStatus` — handles `status_type=confirm_status\|status` | `app/Http/Controllers/Calendar/EventController.php:561` |
| Confirm Status Update | `EventController@updateConfirmStatus` — toggles confirm_status to 2/3 | `EventController.php:534` |
| Event status/confirm routes | `POST /calendar/events/status` (`calendar.events.status`) and `POST /calendar/events/confirm-status` | `routes/web.php` |
| Email Service | `SendGridService::send(array $to, string $subject, string $htmlBody, array $options)` | `app/Services/Email/SendGridService.php` |
| SMS Service | `TwilioSmsService::send(string $to, string $body, int $companyId, ...)` — requires A2P campaign | `app/Services/Sms/TwilioSmsService.php` |
| Drag-drop library | jQuery UI Sortable — globally loaded via CDN | `resources/views/admin/components/scripts.blade.php` |
| Toast helpers | `showSuccessToast()`, `showErrorToast()` | `public/admin-form-plugins/form-validation.js` |
| Scheduler | Empty stub | `app/Console/Kernel.php` |
| User notification prefs | `UserNotification` model — `email_notification`, `mobile_app_notification` booleans | `app/Models/UserNotification.php` |

---

## Event Model — Status Field Mapping

```
event_type:      1=Task, 2=Appointment
status:          1=Incomplete, 2=Complete, 3=Cancelled
confirm_status:  1=Pending, 2=Confirmed, 3=Declined
seen_status:     1=Pending, 2=Seen, 3=Unseen
is_rescheduled:  0=No, 1=Yes
```

**Kanban column assignment rule (priority order, first match wins):**
1. `is_rescheduled = 1` → **Rescheduled**
2. `confirm_status = 2` → **Confirmed**
3. `seen_status = 2` → **Seen**
4. else → **Set** (initial state)

---

## E-09-S01: Appointment Board (Kanban) View

### S01-T01 — Build Appointment Board Kanban UI

**Replace** the current table-grid markup in `appointment-board.blade.php` with a 4-column kanban layout. Keep same route (`leads.appointment-board`) and controller.

**Column layout:**
```
| Set | Confirmed | Rescheduled | Seen |
```

Each column is a scrollable list of appointment **cards**. A card displays:
- Lead name (`leads.first_name + last_name` or `leads.dba`)
- Appointment date/time (`start_time` formatted)
- Assigned rep (`set_for_user_id` → user full name)
- Priority badge (color from `Event::$eventPriority`)
- `data-event-id` attribute for drag handling

**Controller additions — `AppointmentBoardController`:**

Add `getCards(Request $request): JsonResponse`:
- Accepts: `date_from`, `date_to`, `user_id`
- Scopes by `auth()->user()->company_id`
- Queries `calendar_events` WHERE `event_type=2`, `status=1`, within date range
- Joins `users` (set_for) and `leads`
- Applies column assignment rule above
- Returns: `{ set:[...], confirmed:[...], rescheduled:[...], seen:[...] }`

New route (inside authenticated admin group):
```
GET /leads/appointment-board/cards → AppointmentBoardController@getCards
name: leads.appointment-board.cards
```

**View — `appointment-board.blade.php`:**
- Replace `<table>` with `.kanban-board` div containing 4 `.kanban-col` columns
- Pass `window._appointmentBoardStatusUrl` and `window._appointmentBoardCardsUrl` in blade
- Include `@push('page_script')` → `assets/js/appointment-board.js`

**New file: `public/assets/js/appointment-board.js`** (IIFE)
```javascript
(function ($) {
    // jQuery UI Sortable connected across all 4 columns
    $('.kanban-cards').sortable({
        connectWith: '.kanban-cards',
        handle: '.kanban-card',
        placeholder: 'kanban-placeholder',
        tolerance: 'pointer',
        stop: function (event, ui) {
            var eventId = ui.item.data('event-id');
            var newCol  = ui.item.closest('.kanban-col').data('column');
            _updateStatus(eventId, newCol);
        }
    });

    function _updateStatus(eventId, column) {
        $.post(window._appointmentBoardStatusUrl, {
            _token: $('meta[name="csrf-token"]').attr('content'),
            event_id: eventId,
            column: column
        })
        .done(function (res) {
            if (res.success) { showSuccessToast('Status updated'); }
            else { showErrorToast(res.message || 'Update failed'); location.reload(); }
        })
        .fail(function () { showErrorToast('Request failed'); location.reload(); });
    }
})(jQuery);
```

**CSS additions** (inline `<style>` in view or append to `custom.css`):
```css
.kanban-board   { display:flex; gap:16px; overflow-x:auto; align-items:flex-start; }
.kanban-col     { flex:1; min-width:220px; background:#f8f9fa; border-radius:6px; padding:12px; }
.kanban-col h5  { font-size:.85rem; font-weight:600; text-transform:uppercase; margin-bottom:12px; }
.kanban-cards   { min-height:60px; }
.kanban-card    { background:#fff; border:1px solid #dee2e6; border-radius:4px; padding:10px 12px;
                  margin-bottom:8px; cursor:grab; font-size:.82rem; }
.kanban-placeholder { height:58px; background:#e9ecef; border:2px dashed #adb5bd;
                      border-radius:4px; margin-bottom:8px; }
```

---

### S01-T02 — Add User Filter and Date Range

The controller already defaults to current week and accepts `user_id`, `date_from`, `date_to`. The `$users` list is already passed to the view. Add:

1. **Inline filter bar** (above the kanban board) in the blade view:
   - Date range inputs (`date_from` / `date_to`) using Bootstrap datepicker (already in form plugins)
   - User select (`<select id="filter-user">`) populated from `$users`
   - "Apply" button (`#filter-apply`)

2. **AJAX filter** in `appointment-board.js`:
```javascript
$('#filter-apply').on('click', function () {
    $.get(window._appointmentBoardCardsUrl, {
        date_from : $('#filter-date-from').val(),
        date_to   : $('#filter-date-to').val(),
        user_id   : $('#filter-user').val()
    }, function (res) { _renderBoard(res); });
});

function _renderBoard(data) {
    $.each(['set','confirmed','rescheduled','seen'], function(i, col) {
        var $col = $('[data-column="' + col + '"] .kanban-cards').empty();
        $.each(data[col] || [], function(j, card) {
            $col.append(_buildCard(card));
        });
    });
    $('.kanban-cards').sortable('refresh');
}
```

---

### S01-T03 — Update Appointment Status on Card Drag

The existing `EventController::updateStatus()` does NOT cover `seen_status` updates or `is_rescheduled` resets. Add a new dedicated endpoint:

**New method — `AppointmentBoardController@updateKanbanStatus`:**
```php
public function updateKanbanStatus(Request $request): JsonResponse
{
    $request->validate(['event_id' => 'required|integer', 'column' => 'required|string']);
    try {
        $event = Event::where('id', $request->event_id)
                      ->where('company_id', auth()->user()->company_id)
                      ->firstOrFail();

        match ($request->column) {
            'confirmed'   => $event->fill(['confirm_status' => 2,
                                           'confirmed_by_user_id' => auth()->id()]),
            'rescheduled' => $event->fill(['is_rescheduled' => 1]),
            'seen'        => $event->fill(['seen_status' => 2]),
            'set'         => $event->fill(['confirm_status' => 1,
                                           'seen_status'    => 1,
                                           'is_rescheduled' => 0]),
            default       => throw new \InvalidArgumentException('Unknown column'),
        };
        $event->updated_by_user_id = auth()->id();
        $event->save();

        return response()->json(['success' => true]);
    } catch (\Exception $e) {
        ErrorLog::Log($e);
        return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
    }
}
```

New route:
```
POST /leads/appointment-board/update-status → AppointmentBoardController@updateKanbanStatus
name: leads.appointment-board.update-status
```

---

## E-09-S02: Appointment & Task Reminder Execution

### S02-T01 — Reminder Fields Migration + Console Command

**New migration:** `database/migrations/YYYY_MM_DD_add_reminder_fields_to_calendar_events_table.php`
```php
$table->datetime('reminder_at')->nullable()->after('end_time');
$table->tinyInteger('reminder_sent')->default(0)->after('reminder_at');
```

Add `reminder_at` and `reminder_sent` to `Event::$fillable`.

Add optional **"Remind me"** datetime field to the existing calendar event create/edit form blade view(s) under `resources/views/admin/calendar/`. Use the existing Bootstrap datepicker from form plugins.

**New console command:** `app/Console/Commands/ProcessEventReminders.php`
```php
protected $signature   = 'reminders:process';
protected $description = 'Send email/SMS reminders for due calendar events';

public function handle(): int
{
    $events = Event::where('reminder_sent', 0)
        ->whereNotNull('reminder_at')
        ->where('reminder_at', '<=', now())
        ->where('status', 1)          // Incomplete only
        ->with(['setFor', 'lead'])
        ->get();

    foreach ($events as $event) {
        try {
            $this->sendEmailReminder($event);
            $this->sendSmsReminder($event);
            $event->update(['reminder_sent' => 1]);
        } catch (\Exception $e) {
            ErrorLog::Log($e);
            // reminder_sent stays 0 — retries next minute
        }
    }
    return 0;
}
```

**Register in `app/Console/Kernel.php`:**
```php
protected function schedule(Schedule $schedule): void
{
    $schedule->command('reminders:process')->everyMinute();
}
```

---

### S02-T02 — Send Email Reminder via SendGrid

**In `ProcessEventReminders::sendEmailReminder(Event $event): void`:**
```php
$user = $event->setFor;
if (!$user || !$user->email) return;

// Respect user notification preferences
$pref = UserNotification::where('user_id', $user->id)->first();
if ($pref && !$pref->email_notification) return;

$type    = $event->event_type == 2 ? 'Appointment' : 'Task';
$subject = "Reminder: {$type} — {$event->title}";
$body    = view('emails.event-reminder', compact('event', 'user'))->render();

app(SendGridService::class)->send(
    ['email' => $user->email, 'name' => $user->full_name],
    $subject,
    $body,
    ['entity_type' => 'calendar_event', 'entity_id' => $event->id]
);
```

**New view: `resources/views/emails/event-reminder.blade.php`**
- Plain HTML with inline styles (email-safe)
- Show: event type, title, date/time, lead name, link to calendar
- No external CSS

---

### S02-T03 — Send SMS Reminder via Twilio

**In `ProcessEventReminders::sendSmsReminder(Event $event): void`:**
```php
$user = $event->setFor;
if (!$user) return;

$pref = UserNotification::where('user_id', $user->id)->first();
if ($pref && !$pref->mobile_app_notification) return;

$phone = '+' . ltrim((string)($user->phone_country_code ?? '1'), '+')
       . preg_replace('/\D/', '', $user->phone ?? '');

if (strlen(preg_replace('/\D/', '', $phone)) < 7) return;

$type = $event->event_type == 2 ? 'appointment' : 'task';
$body = 'Reminder: Your ' . $type . ' "' . $event->title . '" is at '
      . $event->start_time->format('g:i A') . '.';

// TwilioSmsService::send() throws if A2P campaign not approved — caught by caller
app(TwilioSmsService::class)->send(
    $phone, $body, $user->company_id,
    null, 'calendar_event', $event->id
);
```

> **A2P Note:** `TwilioSmsService::send()` throws if the company has no approved A2P campaign. The parent loop's catch block logs and skips `reminder_sent=1` — the event retries next minute. This is intentional: SMS failures should not suppress the email or prevent retry.

---

## Critical Files

| File | Change |
|---|---|
| `app/Http/Controllers/Leads/AppointmentBoardController.php` | Add `getCards()` and `updateKanbanStatus()` |
| `resources/views/admin/leads/appointment-board.blade.php` | Replace table-grid with kanban layout + inline filter bar |
| `public/assets/js/appointment-board.js` | **NEW** — IIFE, jQuery UI Sortable, AJAX filter + drag handler |
| `routes/web.php` | Add `getCards` and `updateKanbanStatus` routes (inside authenticated admin group under `leads` prefix) |
| `app/Models/Calendar/Event.php` | Add `reminder_at`, `reminder_sent` to `$fillable` |
| `app/Console/Commands/ProcessEventReminders.php` | **NEW** — Console command |
| `app/Console/Kernel.php` | Register `reminders:process` every minute |
| `resources/views/emails/event-reminder.blade.php` | **NEW** — Inline-styled email template |
| `database/migrations/..._add_reminder_fields_to_calendar_events.php` | **NEW** — `reminder_at`, `reminder_sent` columns |
| Calendar event create/edit blade view(s) | Add optional "Remind me" datetime input |

---

## Verification

### Kanban Board
```bash
php artisan serve
# Navigate to /leads/appointment-board
# Verify 4 columns render: Set / Confirmed / Rescheduled / Seen
# Drag card from Set → Confirmed; check DB: confirm_status=2
# Drag card to Seen; check DB: seen_status=2
# Drag card back to Set; check DB: confirm_status=1, seen_status=1, is_rescheduled=0
# Change user filter + date range → cards reload without full page refresh
```

### Reminder Scheduler
```bash
php artisan migrate
# Create calendar event with reminder_at = 1 minute from now
php artisan reminders:process
# Confirm email received via SendGrid
# Confirm SMS sent (or A2P error logged if Twilio not configured)
# Check DB: reminder_sent = 1
# Run again → no duplicate sends (reminder_sent=1 guard)
```