USE `blood_bank`;

-- Create inventory_alerts table
CREATE TABLE IF NOT EXISTS `inventory_alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_type` enum('low_stock','critical_stock','expiring_soon','expired') NOT NULL,
  `blood_type` enum('A+','A-','B+','B-','AB+','AB-','O+','O-') NOT NULL,
  `severity` enum('info','warning','critical','danger') NOT NULL DEFAULT 'warning',
  `message` text NOT NULL,
  `alert_data` text DEFAULT NULL COMMENT 'JSON data with additional alert information',
  `is_active` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=active, 0=dismissed/hidden',
  `is_read` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1=read by user, 0=unread',
  `dismissed_by` int(11) DEFAULT NULL COMMENT 'User who dismissed the alert',
  `dismissed_at` datetime DEFAULT NULL,
  `read_by` int(11) DEFAULT NULL COMMENT 'User who read the alert',
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `alert_type` (`alert_type`),
  KEY `blood_type` (`blood_type`),
  KEY `severity` (`severity`),
  KEY `is_active` (`is_active`),
  KEY `is_read` (`is_read`),
  KEY `dismissed_by` (`dismissed_by`),
  KEY `read_by` (`read_by`),
  CONSTRAINT `inventory_alerts_ibfk_1` FOREIGN KEY (`dismissed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `inventory_alerts_ibfk_2` FOREIGN KEY (`read_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

