-- ---------------------------------------------------------------------------
-- delivery_request_views
-- ---------------------------------------------------------------------------
-- Backs the "considering" drivers feature (see
-- helpers/delivery_request_offers_shared.php and api/delivery-request/id/view.php).
-- One row per (request, driver): a driver who has genuinely OPENED a request's
-- details, as opposed to merely being pushed a notification about it.
--
-- The UNIQUE KEY on (deliveryRequestId, driverId) is REQUIRED — upsert_driver_view()
-- relies on it for its INSERT ... ON DUPLICATE KEY UPDATE (a driver re-opening the
-- same request just refreshes their own viewedAt clock).
--
-- Run once against the live database (phpMyAdmin → SQL tab, or the mysql CLI).
-- Safe to re-run: CREATE TABLE IF NOT EXISTS is a no-op when it already exists.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `delivery_request_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deliveryRequestId` int(11) NOT NULL,
  `driverId` int(11) NOT NULL,
  `viewedAt` datetime NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_request_driver_view` (`deliveryRequestId`,`driverId`),
  KEY `idx_request_viewedAt` (`deliveryRequestId`,`viewedAt`),
  CONSTRAINT `delivery_request_views_driver_fk` FOREIGN KEY (`driverId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `delivery_request_views_request_fk` FOREIGN KEY (`deliveryRequestId`) REFERENCES `delivery_requests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;