-- Blood Bank Management System - Database Schema
-- MySQL / MariaDB


-- Create Database
CREATE DATABASE IF NOT EXISTS `blood_bank` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `blood_bank`;

-- --------------------------------------------------------
-- Table structure for table `donors`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `donors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `phone_secondary` varchar(20) DEFAULT NULL,
  `telegram_chat_id` varchar(50) DEFAULT NULL,
  `telegram_username` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `national_id` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` enum('male','female') DEFAULT NULL,
  `blood_type` enum('A+','A-','B+','B-','AB+','AB-','O+','O-') NOT NULL,
  `last_donation_date` date DEFAULT NULL,
  `has_chronic_diseases` tinyint(1) DEFAULT 0,
  `chronic_diseases_details` text DEFAULT NULL,
  `medications` text DEFAULT NULL,
  `allergies` text DEFAULT NULL,
  `weight` decimal(5,2) DEFAULT NULL COMMENT 'in kg',
  `address` text DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `status` enum('active','deferred','inactive') NOT NULL DEFAULT 'active',
  `emergency_consent` tinyint(1) NOT NULL DEFAULT 0,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `location_updated_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `notes_medical` text DEFAULT NULL COMMENT 'Medical notes',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `blood_type` (`blood_type`),
  KEY `status` (`status`),
  KEY `emergency_consent` (`emergency_consent`),
  KEY `location` (`latitude`,`longitude`),
  KEY `phone_secondary` (`phone_secondary`),
  KEY `telegram_chat_id` (`telegram_chat_id`),
  KEY `telegram_username` (`telegram_username`),
  KEY `email` (`email`),
  KEY `national_id` (`national_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `donations`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `donations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `donor_id` int(11) NOT NULL,
  `donation_date` date NOT NULL,
  `blood_group` enum('whole','plasma','platelets','red_cells') NOT NULL DEFAULT 'whole',
  `volume` decimal(5,2) DEFAULT NULL COMMENT 'in ml',
  `notes` text DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `donor_id` (`donor_id`),
  KEY `donation_date` (`donation_date`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `donations_ibfk_1` FOREIGN KEY (`donor_id`) REFERENCES `donors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `donations_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `blood_units`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `blood_units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_number` varchar(20) NOT NULL,
  `donation_id` int(11) DEFAULT NULL,
  `blood_type` enum('A+','A-','B+','B-','AB+','AB-','O+','O-') NOT NULL,
  `blood_group` enum('whole','plasma','platelets','red_cells') NOT NULL DEFAULT 'whole',
  `status` enum('available','reserved','used','discarded') NOT NULL DEFAULT 'available',
  `expiry_date` date NOT NULL,
  `created_at` datetime NOT NULL,
  `reserved_at` datetime DEFAULT NULL,
  `used_at` datetime DEFAULT NULL,
  `discarded_at` datetime DEFAULT NULL,
  `discard_reason` text DEFAULT NULL,
  `emergency_request_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unit_number` (`unit_number`),
  KEY `donation_id` (`donation_id`),
  KEY `blood_type` (`blood_type`),
  KEY `status` (`status`),
  KEY `expiry_date` (`expiry_date`),
  KEY `emergency_request_id` (`emergency_request_id`),
  CONSTRAINT `blood_units_ibfk_1` FOREIGN KEY (`donation_id`) REFERENCES `donations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `emergency_requests`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `emergency_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blood_type` enum('A+','A-','B+','B-','AB+','AB-','O+','O-') NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `blood_group` enum('whole','plasma','platelets','red_cells') NOT NULL DEFAULT 'whole',
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `search_radius` decimal(5,2) NOT NULL DEFAULT 3.00 COMMENT 'in km',
  `status` enum('open','active','closed') NOT NULL DEFAULT 'open',
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `activated_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `radius_expanded_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `blood_type` (`blood_type`),
  KEY `status` (`status`),
  KEY `created_by` (`created_by`),
  KEY `location` (`latitude`,`longitude`),
  CONSTRAINT `emergency_requests_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `emergency_responses`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `emergency_responses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL,
  `donor_id` int(11) NOT NULL,
  `status` enum('pending','accepted','rejected','completed') NOT NULL DEFAULT 'pending',
  `response_time` datetime NOT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`),
  KEY `donor_id` (`donor_id`),
  KEY `status` (`status`),
  UNIQUE KEY `unique_response` (`request_id`,`donor_id`),
  CONSTRAINT `emergency_responses_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `emergency_requests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `emergency_responses_ibfk_2` FOREIGN KEY (`donor_id`) REFERENCES `donors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `audit_logs`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `data` text DEFAULT NULL COMMENT 'JSON data',
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `action` (`action`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `notification_logs`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `notification_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('sms','whatsapp','email') NOT NULL,
  `recipient` varchar(100) NOT NULL,
  `message` text NOT NULL,
  `status` enum('sent','failed','pending') NOT NULL DEFAULT 'pending',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: Default password is 'admin123' - CHANGE IT IMMEDIATELY!

