-- Create notifications table for real-time notifications
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `recipient_type` varchar(50) NOT NULL COMMENT 'dispatcher, fleet, vendor, customer, etc.',
  `recipient_id` int(11) NOT NULL COMMENT 'ID of the recipient (dispatcher_id, fleet_id, etc.)',
  `title` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `type` varchar(30) NOT NULL DEFAULT 'info' COMMENT 'info, success, warning, error',
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_recipient` (`recipient_type`,`recipient_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Insert some sample notifications for testing
INSERT INTO `notifications` (`recipient_type`, `recipient_id`, `title`, `message`, `type`, `is_read`) VALUES
('dispatcher', 1, 'Welcome!', 'Welcome to Kasoowa Logistics. Your account is now active.', 'success', 0),
('dispatcher', 1, 'New Job Available', 'A new delivery request is available in your area.', 'info', 0),
('dispatcher', 1, 'Payment Received', '₦2,500 has been added to your wallet.', 'success', 1);
