CREATE TABLE IF NOT EXISTS `customer_reminders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `created_by_admin_id` int(10) unsigned NOT NULL,
  `assigned_to_admin_id` int(10) unsigned NOT NULL COMMENT 'کدام ادمین باید یادآوری را ببیند',
  `remind_at` datetime NOT NULL COMMENT 'زمان دقیق یادآوری',
  `message` varchar(500) NOT NULL,
  `status` enum('pending','done','dismissed') NOT NULL DEFAULT 'pending',
  `notified_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assigned_status_time` (`assigned_to_admin_id`, `status`, `remind_at`),
  KEY `idx_customer_id` (`customer_id`),
  CONSTRAINT `fk_customer_reminders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customer_reminders_creator` FOREIGN KEY (`created_by_admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_customer_reminders_assignee` FOREIGN KEY (`assigned_to_admin_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
