-- In-app notifications when a permit is handed over to another user.
CREATE TABLE IF NOT EXISTS `ptw_handover_notifications` (
  `notification_id` INT AUTO_INCREMENT PRIMARY KEY,
  `recipient_user_id` INT NOT NULL,
  `permit_id` INT NOT NULL,
  `submission_id` INT NOT NULL,
  `handed_by_user_id` INT NULL,
  `permit_number` VARCHAR(255) NULL,
  `message` VARCHAR(500) NOT NULL,
  `read_at` DATETIME NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_ptw_handover_notify_recipient` (`recipient_user_id`, `read_at`, `created_at`),
  KEY `idx_ptw_handover_notify_permit` (`permit_id`),
  CONSTRAINT `fk_ptw_handover_notify_recipient`
    FOREIGN KEY (`recipient_user_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_ptw_handover_notify_permit`
    FOREIGN KEY (`permit_id`) REFERENCES `ptw_permits` (`permit_id`),
  CONSTRAINT `fk_ptw_handover_notify_submission`
    FOREIGN KEY (`submission_id`) REFERENCES `ptw_submissions` (`submission_id`),
  CONSTRAINT `fk_ptw_handover_notify_handed_by`
    FOREIGN KEY (`handed_by_user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB;
