Slot

CREATE TABLE IF NOT EXISTS activity_events (
  id TEXT PRIMARY KEY,
  booking_id TEXT,
  type TEXT NOT NULL,
  occurred_at TEXT NOT NULL DEFAULT (datetime('now')),
  metadata TEXT,
  idempotency_key TEXT
);

CREATE INDEX IF NOT EXISTS idx_activity_events_occurred_at
  ON activity_events(occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_activity_events_booking_id
  ON activity_events(booking_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_activity_events_idempotency_key
  ON activity_events(idempotency_key)
  WHERE idempotency_key IS NOT NULL;

-- Backfill the lifecycle points that can be reconstructed safely from existing
-- booking rows. Future changes are recorded as first-class activity events.
INSERT OR IGNORE INTO activity_events (id, booking_id, type, occurred_at, metadata, idempotency_key)
SELECT
  'backfill-confirmed-' || id,
  id,
  'booking_confirmed',
  created_at,
  '{"backfilled":true}',
  'booking:' || id || ':confirmed'
FROM bookings
WHERE status = 'confirmed'
   OR (status = 'cancelled' AND (payment_status <> 'cancelled' OR paid_at IS NOT NULL));

INSERT OR IGNORE INTO activity_events (id, booking_id, type, occurred_at, metadata, idempotency_key)
SELECT
  'backfill-paid-' || id,
  id,
  'payment_received',
  paid_at,
  '{"backfilled":true}',
  'booking:' || id || ':paid'
FROM bookings
WHERE paid_at IS NOT NULL;

INSERT OR IGNORE INTO activity_events (id, booking_id, type, occurred_at, metadata, idempotency_key)
SELECT
  'backfill-rescheduled-' || id,
  id,
  'booking_rescheduled',
  rescheduled_at,
  json_object('backfilled', 1, 'count', reschedule_count),
  'booking:' || id || ':reschedule-backfill'
FROM bookings
WHERE rescheduled_at IS NOT NULL AND reschedule_count > 0;

INSERT OR IGNORE INTO activity_events (id, booking_id, type, occurred_at, metadata, idempotency_key)
SELECT
  'backfill-suggested-' || id,
  id,
  'time_suggested',
  proposed_at,
  json_object('backfilled', 1, 'proposedStart', proposed_start_time, 'proposedEnd', proposed_end_time),
  'booking:' || id || ':suggestion-backfill'
FROM bookings
WHERE proposed_at IS NOT NULL;

INSERT OR IGNORE INTO activity_events (id, booking_id, type, occurred_at, metadata, idempotency_key)
SELECT
  'backfill-refunded-' || id,
  id,
  'refund_succeeded',
  refunded_at,
  json_object('backfilled', 1, 'amount', refunded_amount, 'currency', payment_currency),
  CASE WHEN stripe_refund_id IS NOT NULL THEN 'refund:' || stripe_refund_id || ':succeeded' ELSE 'booking:' || id || ':refund-backfill' END
FROM bookings
WHERE refunded_at IS NOT NULL;

INSERT OR IGNORE INTO activity_events (id, booking_id, type, occurred_at, metadata, idempotency_key)
SELECT
  'backfill-cancelled-' || id,
  id,
  CASE WHEN payment_status = 'cancelled' AND paid_at IS NULL THEN 'payment_hold_released' ELSE 'booking_cancelled' END,
  cancelled_at,
  '{"backfilled":true}',
  'booking:' || id || ':cancelled'
FROM bookings
WHERE cancelled_at IS NOT NULL;