-- Add after 019; unit 0 denotes a platform-wide job.
CREATE TABLE scheduled_job_state (
 scope_unit_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
 job_key VARCHAR(60) NOT NULL,
 last_started_at DATETIME NULL,
 last_completed_at DATETIME NULL,
 last_success_at DATETIME NULL,
 last_status ENUM('never','running','success','failed') NOT NULL DEFAULT 'never',
 last_summary VARCHAR(250) NULL,
 PRIMARY KEY(scope_unit_id,job_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE scheduled_job_runs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 scope_unit_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
 job_key VARCHAR(60) NOT NULL,
 started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 finished_at DATETIME NULL,
 status ENUM('running','success','failed') NOT NULL DEFAULT 'running',
 affected_count INT UNSIGNED NOT NULL DEFAULT 0,
 summary VARCHAR(250) NULL,
 KEY idx_job_history(scope_unit_id,job_key,started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE scheduled_payment_reminder_settings (
 unit_id BIGINT UNSIGNED PRIMARY KEY,
 enabled TINYINT(1) NOT NULL DEFAULT 0,
 authorised_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(authorised_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- A unique day key prevents two runs, including manual and scheduled sends,
-- from producing more than one payment/meeting reminder of each type per day.
ALTER TABLE reminders ADD COLUMN reminder_day DATE NULL;
UPDATE reminders SET reminder_day=DATE(scheduled_for) WHERE reminder_day IS NULL;
-- Keep historical duplicates by marking older rows as legacy before indexing.
UPDATE reminders r JOIN (SELECT * FROM (SELECT booking_id,reminder_type,reminder_day,MAX(id) AS newest FROM reminders GROUP BY booking_id,reminder_type,reminder_day HAVING COUNT(*)>1) AS duplicates) d ON d.booking_id=r.booking_id AND d.reminder_type=r.reminder_type AND d.reminder_day=r.reminder_day AND d.newest<>r.id SET r.reminder_day=NULL;
ALTER TABLE reminders ADD UNIQUE KEY uq_reminder_booking_type_day(booking_id,reminder_type,reminder_day);
-- Offer lifecycle: one outstanding message per booking offer, even after a retry.
ALTER TABLE outbound_mail_queue ADD COLUMN offer_booking_id BIGINT UNSIGNED NULL;
ALTER TABLE outbound_mail_queue ADD COLUMN offer_expires_at DATETIME NULL;
ALTER TABLE outbound_mail_queue ADD KEY idx_offer_mail(offer_booking_id,offer_expires_at,status);
UPDATE outbound_mail_queue q JOIN bookings b ON b.id=q.booking_id
SET q.offer_booking_id=b.id,q.offer_expires_at=b.offer_expires_at
WHERE q.message_kind='place_offered' AND q.status IN ('queued','sending') AND q.offer_booking_id IS NULL;

INSERT INTO scheduled_job_state(scope_unit_id,job_key)
VALUES (0,'guest_retention'),(0,'mail_delivery');
INSERT INTO scheduled_job_state(scope_unit_id,job_key)
SELECT u.id,j.job_key FROM units u
CROSS JOIN (SELECT 'event_closing' AS job_key UNION ALL SELECT 'event_archival' UNION ALL SELECT 'waiting_expiry' UNION ALL SELECT 'waiting_promotion' UNION ALL SELECT 'booking_reminders' UNION ALL SELECT 'payment_reminders') j;