-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: May 05, 2026 at 04:57 AM
-- Server version: 10.6.25-MariaDB-cll-lve
-- PHP Version: 8.4.20

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `nasrcity_antibiotic_management`
--

-- --------------------------------------------------------

--
-- Table structure for table `activity_log`
--

CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `action` varchar(100) NOT NULL,
  `table_name` varchar(50) DEFAULT NULL,
  `record_id` int(11) DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `activity_log`
--

INSERT INTO `activity_log` (`id`, `user_id`, `action`, `table_name`, `record_id`, `old_values`, `new_values`, `ip_address`, `user_agent`, `created_at`) VALUES
(1, 10, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-23 09:46:18'),
(2, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-23 09:50:50'),
(3, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-23 10:17:14'),
(4, 10, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-23 10:38:14'),
(5, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-23 10:38:32'),
(6, 1, 'update_profile_activity', 'users', 1, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-23 10:48:31'),
(7, 1, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-23 10:48:40'),
(8, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-23 10:48:52'),
(9, 10, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-23 10:50:42'),
(10, 1, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-23 10:51:01'),
(11, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-23 12:44:27'),
(12, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-23 13:28:50'),
(13, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-23 13:54:37'),
(14, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '196.154.201.174', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-23 14:13:14'),
(15, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-26 06:30:03'),
(16, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-26 06:46:51'),
(17, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-26 07:17:04'),
(18, 1, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-30 11:07:43'),
(19, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-30 11:07:51'),
(20, 10, 'add_antibiotic_activity', 'antibiotics', 9, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-30 11:29:09'),
(21, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-30 11:40:13'),
(22, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-30 12:24:37'),
(23, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-30 12:46:38'),
(24, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '196.158.129.170', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-04-30 14:37:40'),
(25, 10, 'add_patient_activity', 'patients', 13, NULL, NULL, '197.42.172.248', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-30 14:51:54'),
(26, 10, 'add_new_report', 'antibiotic_reports', 13, NULL, NULL, '197.42.172.248', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-30 14:54:15'),
(27, 10, 'add_new_report', 'antibiotic_reports', 14, NULL, NULL, '197.42.172.248', 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-04-30 14:55:44'),
(28, 1, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 08:31:21'),
(29, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 08:35:41'),
(30, 10, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 08:36:12'),
(31, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 08:38:09'),
(32, 10, 'تسجيل خروج', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 08:38:17'),
(33, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 08:42:31'),
(34, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 08:44:35'),
(35, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 09:07:34'),
(36, 10, 'change_antibiotic_status_activity', 'antibiotics', 9, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 10:40:24'),
(37, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 10:53:03'),
(38, 10, 'change_antibiotic_status_activity', 'antibiotics', 9, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 10:59:29'),
(39, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 11:14:58'),
(40, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 11:59:20'),
(41, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 12:48:42'),
(42, 10, 'إضافة مستخدم جديد', 'users', 11, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-02 12:52:00'),
(43, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 13:01:12'),
(44, 10, 'تحديث بيانات المستخدم', 'users', 11, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 13:02:46'),
(45, 10, 'تحديث بيانات المستخدم', 'users', 11, NULL, NULL, '196.156.87.66', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 13:09:07'),
(46, 11, 'تسجيل دخول', NULL, NULL, NULL, NULL, '196.156.87.66', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 13:10:30'),
(47, 11, 'تحديث بيانات المستخدم', 'users', 11, NULL, NULL, '196.156.87.66', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 13:11:32'),
(48, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '196.156.94.146', 'Mozilla/5.0 (Linux; U; Android 12; ar-us; CPH2113 Build/SKQ1.210216.001) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/115.0.5970.168 Mobile Safari/537.36 HeyTapBrowser/45.14.0.1', '2026-05-02 13:37:02'),
(49, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '196.156.87.66', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 13:51:15'),
(50, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '105.206.54.118', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 14:06:01'),
(51, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '102.40.162.124', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 15:17:33'),
(52, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '197.45.2.197', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-02 15:28:20'),
(53, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-03 06:05:18'),
(54, 1, 'تصدير المرضى Excel', 'patients', NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-03 08:11:36'),
(55, 1, 'تصدير المضادات الحيوية Excel', 'antibiotics', NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-03 08:12:40'),
(56, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-03 09:28:04'),
(57, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-03 09:32:41'),
(58, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '197.45.215.125', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-03 19:02:17'),
(59, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-04 06:04:56'),
(60, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-04 06:12:20'),
(61, 10, 'إلغاء تفعيل مضاد حيوي', 'antibiotics', 9, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-04 06:47:51'),
(62, 10, 'إلغاء تفعيل مضاد حيوي', 'antibiotics', 9, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-04 06:47:59'),
(63, 10, 'تصدير المضادات الحيوية Excel', 'antibiotics', NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-04 08:47:10'),
(64, 11, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:147.0) Gecko/20100101 Firefox/147.0', '2026-05-04 12:46:09'),
(65, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Safari/537.36', '2026-05-05 06:45:23'),
(66, 10, 'تسجيل دخول', NULL, NULL, NULL, NULL, '62.68.254.74', 'Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/147.0.0.0 Mobile Safari/537.36', '2026-05-05 07:20:32');

-- --------------------------------------------------------

--
-- Table structure for table `antibiotics`
--

CREATE TABLE `antibiotics` (
  `id` int(11) NOT NULL,
  `antibiotic_name` varchar(100) NOT NULL,
  `generic_name` varchar(100) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `dosage_form` varchar(50) DEFAULT NULL,
  `strength` varchar(50) DEFAULT NULL,
  `indication` text DEFAULT NULL,
  `contraindications` text DEFAULT NULL,
  `side_effects` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `antibiotics`
--

INSERT INTO `antibiotics` (`id`, `antibiotic_name`, `generic_name`, `category`, `dosage_form`, `strength`, `indication`, `contraindications`, `side_effects`, `is_active`, `created_at`) VALUES
(9, 'نتن', 'eer', 'Cephalosporin', 'Capsule', 'ii', 'kkkkkkkk', 'ppp', 'hhh', 0, '2026-04-30 11:29:09');

-- --------------------------------------------------------

--
-- Table structure for table `antibiotic_reports`
--

CREATE TABLE `antibiotic_reports` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `reporting_pharmacist_id` int(11) NOT NULL,
  `unit_id` int(11) NOT NULL,
  `antibiotic_id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `problem_authority` enum('physicians','nurses','pharmacist','other') NOT NULL,
  `problem_authority_name` varchar(100) DEFAULT NULL,
  `intervention_choice` text DEFAULT NULL,
  `degree_of_damage` text DEFAULT NULL,
  `result_of_intervention` enum('accept','reject','postpone') DEFAULT 'accept',
  `corrective_action` text DEFAULT NULL,
  `result_of_corrective_action` enum('damage_avoided','reported_to_avoid_future_damage','no_corrective_action','corrective_action_refused','physician_agree_no_action','other') DEFAULT NULL,
  `recommendations` text DEFAULT NULL,
  `status` enum('pending','completed','cancelled') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `problem_cause` text DEFAULT NULL,
  `problem_authority_details` text DEFAULT NULL,
  `antibiotic_classification` varchar(100) DEFAULT NULL,
  `intervention_choices` text DEFAULT NULL,
  `antibiotic_dose` varchar(100) DEFAULT NULL,
  `antibiotic_duration` varchar(100) DEFAULT NULL,
  `antibiotic_route` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `antibiotic_reports`
--

INSERT INTO `antibiotic_reports` (`id`, `patient_id`, `reporting_pharmacist_id`, `unit_id`, `antibiotic_id`, `report_date`, `problem_authority`, `problem_authority_name`, `intervention_choice`, `degree_of_damage`, `result_of_intervention`, `corrective_action`, `result_of_corrective_action`, `recommendations`, `status`, `created_at`, `updated_at`, `problem_cause`, `problem_authority_details`, `antibiotic_classification`, `intervention_choices`, `antibiotic_dose`, `antibiotic_duration`, `antibiotic_route`) VALUES
(13, 13, 10, 7, 9, '2026-04-30', 'physicians', 'نولتتا', '[\"drug_discontinuation\",\"changing_administration_route\"]', '[\"ability_cause_damage\",\"monitoring_required_avoid_damage\",\"temporary_damage\"]', 'accept', 'تبوابتب', 'reported_to_avoid_future_damage', 'لهنتالب نلزتةىب', 'pending', '2026-04-30 14:54:15', '2026-04-30 14:54:15', 'نوتاا', NULL, 'watch', NULL, NULL, NULL, NULL),
(14, 13, 10, 7, 9, '2026-04-30', 'physicians', 'نولتتا', '[\"drug_discontinuation\",\"changing_administration_route\"]', '[\"ability_cause_damage\",\"monitoring_required_avoid_damage\",\"temporary_damage\"]', 'accept', 'تبوابتب', 'reported_to_avoid_future_damage', 'لهنتالب نلزتةىب', 'pending', '2026-04-30 14:55:44', '2026-04-30 14:55:44', 'نوتاا', NULL, 'watch', NULL, NULL, NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `breakage_reports`
--

CREATE TABLE `breakage_reports` (
  `id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL,
  `reason` text DEFAULT NULL,
  `reported_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `clinical_pharmacy_forms`
--

CREATE TABLE `clinical_pharmacy_forms` (
  `id` int(11) NOT NULL,
  `form_date` date NOT NULL,
  `department` varchar(100) DEFAULT NULL,
  `patient_count` int(11) DEFAULT 0,
  `discharge_count` int(11) DEFAULT 0,
  `blue_text_data` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `custom_stats`
--

CREATE TABLE `custom_stats` (
  `id` int(11) NOT NULL,
  `stat_date` date NOT NULL,
  `stat_name` varchar(100) NOT NULL,
  `stat_value` varchar(50) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `daily_pharmacist_reports`
--

CREATE TABLE `daily_pharmacist_reports` (
  `id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `pharmacist_id` int(11) NOT NULL,
  `pharmacist_during_shift` varchar(100) NOT NULL COMMENT 'اسم الصيدلي أثناء الوردية',
  `total_patients` int(11) DEFAULT 0 COMMENT 'إجمالي عدد المرضى',
  `total_discharged` int(11) DEFAULT 0 COMMENT 'إجمالي عدد المرضى المخرجين',
  `patients_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'بيانات المرضى (الاسم، DAMA، التحسن، الأدوية المخرجة، الملاحظات)' CHECK (json_valid(`patients_data`)),
  `sedation_patients` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'المرضى تحت التخدير' CHECK (json_valid(`sedation_patients`)),
  `notes` text DEFAULT NULL COMMENT 'ملاحظات عامة',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `daily_reports_summary`
-- (See below for the actual view)
--
CREATE TABLE `daily_reports_summary` (
`id` int(11)
,`report_date` date
,`pharmacist_during_shift` varchar(100)
,`total_patients` int(11)
,`total_discharged` int(11)
,`created_by_name` varchar(100)
,`pharmacist_role` enum('admin','pharmacist','doctor','nurse')
,`created_at` timestamp
,`updated_at` timestamp
,`patients_with_data` int(10)
,`sedation_count` int(10)
);

-- --------------------------------------------------------

--
-- Table structure for table `daily_statistics`
--

CREATE TABLE `daily_statistics` (
  `id` int(11) NOT NULL,
  `stat_date` date NOT NULL,
  `total_reports` int(11) DEFAULT 0,
  `completed_reports` int(11) DEFAULT 0,
  `pending_reports` int(11) DEFAULT 0,
  `total_patients` int(11) DEFAULT 0,
  `active_antibiotics` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `expired_drugs_disposal`
--

CREATE TABLE `expired_drugs_disposal` (
  `id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `drug_name` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `batch_number` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `reported_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `export_logs`
--

CREATE TABLE `export_logs` (
  `id` int(11) NOT NULL,
  `export_type` varchar(100) NOT NULL,
  `exported_by` int(11) NOT NULL,
  `export_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `parameters` text DEFAULT NULL,
  `record_count` int(11) DEFAULT 0,
  `file_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `holidays`
--

CREATE TABLE `holidays` (
  `id` int(11) NOT NULL,
  `holiday_name` varchar(200) NOT NULL,
  `holiday_name_en` varchar(200) DEFAULT NULL,
  `holiday_date` date NOT NULL,
  `holiday_type` enum('national','religious','other') DEFAULT 'national',
  `is_recurring` tinyint(1) DEFAULT 0,
  `color` varchar(20) DEFAULT '#ff6b6b',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `holidays`
--

INSERT INTO `holidays` (`id`, `holiday_name`, `holiday_name_en`, `holiday_date`, `holiday_type`, `is_recurring`, `color`, `created_by`, `created_at`) VALUES
(3, 'عيد الاضحي', NULL, '2026-02-16', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(4, 'عيد الاضحي', NULL, '2026-02-17', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(5, 'عيد الاضحي', NULL, '2026-02-18', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(6, 'عيد الاضحي', NULL, '2026-02-19', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(7, 'عيد الاضحي', NULL, '2026-02-20', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(8, 'عيد الاضحي', NULL, '2026-02-21', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(9, 'عيد الاضحي', NULL, '2026-02-22', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(10, 'عيد الاضحي', NULL, '2026-02-23', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(11, 'عيد الاضحي', NULL, '2026-02-24', 'religious', 0, '#ff6b6b', 1, '2026-02-16 11:14:38'),
(12, 'رأس السنة الميلادية', 'New Year', '2026-01-01', 'national', 0, '#4CAF50', 1, '2026-02-16 11:39:37'),
(14, 'عيد تحرير سيناء', 'Sinai Liberation Day', '2026-04-25', 'national', 0, '#FF9800', 1, '2026-02-16 11:39:37'),
(15, 'عيد العمال', 'Labor Day', '2026-05-01', 'national', 0, '#F44336', 1, '2026-02-16 11:39:37'),
(16, 'ثورة 30 يونيو', 'June 30 Revolution', '2026-06-30', 'national', 0, '#9C27B0', 1, '2026-02-16 11:39:37'),
(17, 'ثورة 23 يوليو', 'July 23 Revolution', '2026-07-23', 'national', 0, '#E91E63', 1, '2026-02-16 11:39:37'),
(19, 'عيد الأضحى', 'Eid Al-Adha', '2026-06-16', 'religious', 1, '#009688', 1, '2026-02-16 11:39:37'),
(20, 'رأس السنة الهجرية', 'Islamic New Year', '2026-07-07', 'religious', 1, '#795548', 1, '2026-02-16 11:39:37'),
(21, 'المولد النبوي', 'Prophet\'s Birthday', '2026-09-15', 'religious', 1, '#607D8B', 1, '2026-02-16 11:39:37'),
(22, 'رأس السنة الميلادية', 'New Year', '2026-01-01', 'national', 0, '#4CAF50', 1, '2026-02-16 12:27:21'),
(24, 'عيد تحرير سيناء', 'Sinai Liberation Day', '2026-04-25', 'national', 0, '#FF9800', 1, '2026-02-16 12:27:21'),
(25, 'عيد العمال', 'Labor Day', '2026-05-01', 'national', 0, '#F44336', 1, '2026-02-16 12:27:21'),
(26, 'ثورة 30 يونيو', 'June 30 Revolution', '2026-06-30', 'national', 0, '#9C27B0', 1, '2026-02-16 12:27:21'),
(27, 'ثورة 23 يوليو', 'July 23 Revolution', '2026-07-23', 'national', 0, '#E91E63', 1, '2026-02-16 12:27:21'),
(29, 'عيد الأضحى', 'Eid Al-Adha', '2026-06-16', 'religious', 1, '#009688', 1, '2026-02-16 12:27:21'),
(30, 'رأس السنة الهجرية', 'Islamic New Year', '2026-07-07', 'religious', 1, '#795548', 1, '2026-02-16 12:27:21'),
(31, 'المولد النبوي', 'Prophet\'s Birthday', '2026-09-15', 'religious', 1, '#607D8B', 1, '2026-02-16 12:27:21'),
(32, 'عيد الفطر', NULL, '2026-02-17', 'religious', 0, '#ff6b6b', 1, '2026-02-16 13:17:34'),
(33, 'عيد الاضحي', NULL, '2026-07-15', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(34, 'عيد الاضحي', NULL, '2026-07-16', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(35, 'عيد الاضحي', NULL, '2026-07-17', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(36, 'عيد الاضحي', NULL, '2026-07-18', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(37, 'عيد الاضحي', NULL, '2026-07-19', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(38, 'عيد الاضحي', NULL, '2026-07-20', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(39, 'عيد الاضحي', NULL, '2026-07-21', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(40, 'عيد الاضحي', NULL, '2026-07-22', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(41, 'عيد الاضحي', NULL, '2026-07-23', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(42, 'عيد الاضحي', NULL, '2026-07-24', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(43, 'عيد الاضحي', NULL, '2026-07-25', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(44, 'عيد الاضحي', NULL, '2026-07-26', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(45, 'عيد الاضحي', NULL, '2026-07-27', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(46, 'عيد الاضحي', NULL, '2026-07-28', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(47, 'عيد الاضحي', NULL, '2026-07-29', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(48, 'عيد الاضحي', NULL, '2026-07-30', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(49, 'عيد الاضحي', NULL, '2026-07-31', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(50, 'عيد الاضحي', NULL, '2026-08-01', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(51, 'عيد الاضحي', NULL, '2026-08-02', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(52, 'عيد الاضحي', NULL, '2026-08-03', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(53, 'عيد الاضحي', NULL, '2026-08-04', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(54, 'عيد الاضحي', NULL, '2026-08-05', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(55, 'عيد الاضحي', NULL, '2026-08-06', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(56, 'عيد الاضحي', NULL, '2026-08-07', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(57, 'عيد الاضحي', NULL, '2026-08-08', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(58, 'عيد الاضحي', NULL, '2026-08-09', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(59, 'عيد الاضحي', NULL, '2026-08-10', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(60, 'عيد الاضحي', NULL, '2026-08-11', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(61, 'عيد الاضحي', NULL, '2026-08-12', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(62, 'عيد الاضحي', NULL, '2026-08-13', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(63, 'عيد الاضحي', NULL, '2026-08-14', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(64, 'عيد الاضحي', NULL, '2026-08-15', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(65, 'عيد الاضحي', NULL, '2026-08-16', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(66, 'عيد الاضحي', NULL, '2026-08-17', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(67, 'عيد الاضحي', NULL, '2026-08-18', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(68, 'عيد الاضحي', NULL, '2026-08-19', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(69, 'عيد الاضحي', NULL, '2026-08-20', 'religious', 0, '#2d1010', 1, '2026-02-16 13:18:16'),
(71, 'عيد الشرطة', NULL, '2026-03-17', 'national', 0, '#ff6b6b', 1, '2026-02-16 13:19:09'),
(72, 'عيد الشرطة', NULL, '2026-03-18', 'national', 0, '#ff6b6b', 1, '2026-02-16 13:19:09'),
(73, 'عيد الشرطة', NULL, '2026-03-19', 'national', 0, '#ff6b6b', 1, '2026-02-16 13:19:09'),
(74, 'عيد الشرطة', NULL, '2026-03-20', 'national', 0, '#ff6b6b', 1, '2026-02-16 13:19:09');

-- --------------------------------------------------------

--
-- Table structure for table `icu_daily_reports`
--

CREATE TABLE `icu_daily_reports` (
  `id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `pharmacist_shift` varchar(255) NOT NULL,
  `ph_no` varchar(20) DEFAULT NULL,
  `pharmacists_count` int(11) DEFAULT 0,
  `no_of_pt` int(11) DEFAULT 0,
  `total_patients` int(11) DEFAULT 0,
  `total_discharged` int(11) DEFAULT 0,
  `icu_cases` int(11) DEFAULT 0,
  `cardiac_care_cases` int(11) DEFAULT 0,
  `critical_cases` int(11) DEFAULT 0,
  `dialysis_cases` int(11) DEFAULT 0,
  `narcotic_cases` int(11) DEFAULT 0,
  `narcotic_disposal_reports` int(11) DEFAULT 0,
  `patients_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`patients_data`)),
  `patients_isolation` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`patients_isolation`)),
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `internal_reports`
--

CREATE TABLE `internal_reports` (
  `id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `shift_type` enum('morning','evening','night') NOT NULL,
  `time_from` time DEFAULT NULL,
  `time_to` time DEFAULT NULL,
  `total_cases` int(11) DEFAULT 0,
  `discharges` int(11) DEFAULT 0,
  `sixth_floor_cases` int(11) DEFAULT 0,
  `fourth_floor_cases` int(11) DEFAULT 0,
  `intermediate_care_cases` int(11) DEFAULT 0,
  `icu_cases` int(11) DEFAULT 0,
  `emergency_cases` int(11) DEFAULT 0,
  `surgery_cases` int(11) DEFAULT 0,
  `critical_cases` text DEFAULT NULL,
  `interventions` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `fifth_floor_cases` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `monthly_pharmacist_stats`
-- (See below for the actual view)
--
CREATE TABLE `monthly_pharmacist_stats` (
`report_year` int(5)
,`report_month` int(3)
,`total_reports` bigint(21)
,`avg_patients_per_day` decimal(14,4)
,`avg_discharged_per_day` decimal(14,4)
,`total_patients_month` decimal(32,0)
,`total_discharged_month` decimal(32,0)
,`active_pharmacists` bigint(21)
);

-- --------------------------------------------------------

--
-- Table structure for table `narcotic_disposal_reports`
--

CREATE TABLE `narcotic_disposal_reports` (
  `id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `drug_name` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL,
  `disposal_reason` enum('expired','damaged','other') DEFAULT 'expired',
  `notes` text DEFAULT NULL,
  `reported_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('info','success','warning','danger') DEFAULT 'info',
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `user_id`, `title`, `message`, `type`, `is_read`, `created_at`) VALUES
(17, 10, 'تنظيف السجلات', 'تم حذف 0 سجل من سجلات النشاط', 'success', 0, '2026-04-23 09:17:30');

-- --------------------------------------------------------

--
-- Table structure for table `notification_settings`
--

CREATE TABLE `notification_settings` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `email_notifications` tinyint(1) DEFAULT 1,
  `report_notifications` tinyint(1) DEFAULT 1,
  `system_notifications` tinyint(1) DEFAULT 1,
  `urgent_notifications` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `notification_settings`
--

INSERT INTO `notification_settings` (`id`, `user_id`, `email_notifications`, `report_notifications`, `system_notifications`, `urgent_notifications`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 1, '2026-01-10 10:50:59', '2026-01-10 10:50:59');

-- --------------------------------------------------------

--
-- Table structure for table `nursery_pediatric_reports`
--

CREATE TABLE `nursery_pediatric_reports` (
  `id` int(11) NOT NULL,
  `report_date` date NOT NULL,
  `pharmacist_name` varchar(255) NOT NULL,
  `shift_type` enum('صباحي','مسائي','ليلي') NOT NULL,
  `intermediate_care_count` int(11) DEFAULT 0,
  `intermediate_care_patients` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`intermediate_care_patients`)),
  `internal_care_count` int(11) DEFAULT 0,
  `internal_care_patients` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`internal_care_patients`)),
  `nursery_count` int(11) DEFAULT 0,
  `nursery_patients` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`nursery_patients`)),
  `pediatric_care_count` int(11) DEFAULT 0,
  `pediatric_care_patients` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`pediatric_care_patients`)),
  `intermediate_narcotic_cases` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`intermediate_narcotic_cases`)),
  `internal_narcotic_cases` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`internal_narcotic_cases`)),
  `nursery_narcotic_cases` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`nursery_narcotic_cases`)),
  `pediatric_narcotic_cases` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`pediatric_narcotic_cases`)),
  `disposal_reports_cases` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`disposal_reports_cases`)),
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `page_permissions`
--

CREATE TABLE `page_permissions` (
  `id` int(11) NOT NULL,
  `page_name` varchar(100) NOT NULL COMMENT '?????? ????????????',
  `permission_key` varchar(100) NOT NULL COMMENT '?????????? ????????????????',
  `permission_name_ar` varchar(200) NOT NULL COMMENT '?????? ???????????????? ??????????????',
  `permission_name_en` varchar(200) NOT NULL COMMENT '?????? ???????????????? ????????????????????',
  `permission_icon` varchar(10) DEFAULT NULL COMMENT '???????????? ????????????????',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `page_permissions`
--

INSERT INTO `page_permissions` (`id`, `page_name`, `permission_key`, `permission_name_ar`, `permission_name_en`, `permission_icon`, `created_at`) VALUES
(4, 'patients', 'view_patients', '?????? ????????????', 'View Patients', '???????', '2026-03-03 06:59:02'),
(5, 'patients', 'add_patient', '?????????? ????????', 'Add Patient', '???', '2026-03-03 06:59:02'),
(6, 'patients', 'edit_patient', '?????????? ????????', 'Edit Patient', '??????', '2026-03-03 06:59:02'),
(7, 'patients', 'delete_patient', '?????? ????????', 'Delete Patient', '???????', '2026-03-03 06:59:02'),
(8, 'patients', 'view_patient_details', '?????? ???????????? ????????????', 'View Patient Details', '????', '2026-03-03 06:59:02'),
(9, 'patients', 'export_patients_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(10, 'patients', 'export_patients_pdf', '?????????? PDF', 'Export PDF', '????', '2026-03-03 06:59:02'),
(11, 'patients', 'print_patients', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(12, 'antibiotics', 'view_antibiotics', '?????? ????????????????', 'View Antibiotics', '???????', '2026-03-03 06:59:02'),
(13, 'antibiotics', 'add_antibiotic', '?????????? ???????? ????????', 'Add Antibiotic', '???', '2026-03-03 06:59:02'),
(14, 'antibiotics', 'edit_antibiotic', '?????????? ???????? ????????', 'Edit Antibiotic', '??????', '2026-03-03 06:59:02'),
(15, 'antibiotics', 'delete_antibiotic', '?????? ???????? ????????', 'Delete Antibiotic', '???????', '2026-03-03 06:59:02'),
(16, 'antibiotics', 'toggle_antibiotic_status', '??????????/??????????', 'Activate/Deactivate', '????', '2026-03-03 06:59:02'),
(17, 'antibiotics', 'export_antibiotics_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(18, 'antibiotics', 'print_antibiotics', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(19, 'reports', 'view_reports', '?????? ????????????????', 'View Reports', '???????', '2026-03-03 06:59:02'),
(20, 'reports', 'add_report', '?????????? ??????????', 'Add Report', '???', '2026-03-03 06:59:02'),
(21, 'reports', 'edit_report', '?????????? ??????????', 'Edit Report', '??????', '2026-03-03 06:59:02'),
(22, 'reports', 'delete_report', '?????? ??????????', 'Delete Report', '???????', '2026-03-03 06:59:02'),
(23, 'reports', 'export_reports_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(24, 'reports', 'export_reports_pdf', '?????????? PDF', 'Export PDF', '????', '2026-03-03 06:59:02'),
(25, 'reports', 'print_reports', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(26, 'icu', 'view_icu', '?????? ?????????? ??????????????', 'View ICU Records', '???????', '2026-03-03 06:59:02'),
(27, 'icu', 'add_icu_record', '?????????? ??????', 'Add Record', '???', '2026-03-03 06:59:02'),
(28, 'icu', 'edit_icu_record', '?????????? ??????', 'Edit Record', '??????', '2026-03-03 06:59:02'),
(29, 'icu', 'delete_icu_record', '?????? ??????', 'Delete Record', '???????', '2026-03-03 06:59:02'),
(30, 'icu', 'view_icu_details', '?????? ????????????????', 'View Details', '????', '2026-03-03 06:59:02'),
(31, 'icu', 'export_icu_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(32, 'icu', 'print_icu', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(33, 'nursery', 'view_nursery', '?????? ?????????? ??????????????', 'View Nursery Records', '???????', '2026-03-03 06:59:02'),
(34, 'nursery', 'add_nursery_record', '?????????? ??????', 'Add Record', '???', '2026-03-03 06:59:02'),
(35, 'nursery', 'edit_nursery_record', '?????????? ??????', 'Edit Record', '??????', '2026-03-03 06:59:02'),
(36, 'nursery', 'delete_nursery_record', '?????? ??????', 'Delete Record', '???????', '2026-03-03 06:59:02'),
(37, 'nursery', 'export_nursery_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(38, 'nursery', 'print_nursery', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(39, 'operations', 'view_operations', '?????? ????????????????', 'View Operations', '???????', '2026-03-03 06:59:02'),
(40, 'operations', 'add_operation', '?????????? ??????????', 'Add Operation', '???', '2026-03-03 06:59:02'),
(41, 'operations', 'edit_operation', '?????????? ??????????', 'Edit Operation', '??????', '2026-03-03 06:59:02'),
(42, 'operations', 'delete_operation', '?????? ??????????', 'Delete Operation', '???????', '2026-03-03 06:59:02'),
(43, 'operations', 'export_operations_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(44, 'operations', 'print_operations', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(45, 'pharmacy', 'view_pharmacy', '?????? ????????????????', 'View Interventions', '???????', '2026-03-03 06:59:02'),
(46, 'pharmacy', 'add_intervention', '?????????? ????????', 'Add Intervention', '???', '2026-03-03 06:59:02'),
(47, 'pharmacy', 'edit_intervention', '?????????? ????????', 'Edit Intervention', '??????', '2026-03-03 06:59:02'),
(48, 'pharmacy', 'delete_intervention', '?????? ????????', 'Delete Intervention', '???????', '2026-03-03 06:59:02'),
(49, 'pharmacy', 'export_pharmacy_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(50, 'pharmacy', 'print_pharmacy', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(51, 'monitoring', 'view_monitoring', '?????? ????????????????', 'View Monitoring', '???????', '2026-03-03 06:59:02'),
(52, 'monitoring', 'add_monitoring', '?????????? ????????????', 'Add Monitoring', '???', '2026-03-03 06:59:02'),
(53, 'monitoring', 'edit_monitoring', '?????????? ????????????', 'Edit Monitoring', '??????', '2026-03-03 06:59:02'),
(54, 'monitoring', 'export_monitoring_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(55, 'monitoring', 'print_monitoring', '??????????', 'Print', '???????', '2026-03-03 06:59:02'),
(56, 'users', 'view_users', '?????? ????????????????????', 'View Users', '???????', '2026-03-03 06:59:02'),
(57, 'users', 'add_user', '?????????? ????????????', 'Add User', '???', '2026-03-03 06:59:02'),
(58, 'users', 'edit_user', '?????????? ????????????', 'Edit User', '??????', '2026-03-03 06:59:02'),
(59, 'users', 'delete_user', '?????? ????????????', 'Delete User', '???????', '2026-03-03 06:59:02'),
(60, 'users', 'manage_permissions', '?????????? ??????????????????', 'Manage Permissions', '????', '2026-03-03 06:59:02'),
(61, 'advanced_search', 'use_advanced_search', '?????????????? ?????????? ??????????????', 'Use Advanced Search', '????', '2026-03-03 06:59:02'),
(62, 'advanced_search', 'export_search_results', '?????????? ?????????? ??????????', 'Export Search Results', '????', '2026-03-03 06:59:02'),
(63, 'reports_center', 'view_reports_center', '?????? ???????? ????????????????', 'View Reports Center', '???????', '2026-03-03 06:59:02'),
(64, 'reports_center', 'generate_comprehensive_report', '?????????? ?????????? ????????', 'Generate Comprehensive Report', '????', '2026-03-03 06:59:02'),
(65, 'reports_center', 'export_comprehensive_excel', '?????????? ?????????? ???????? Excel', 'Export Comprehensive Excel', '????', '2026-03-03 06:59:02'),
(66, 'kpi', 'view_kpi', '?????? ???????????? ????????????', 'View KPI', '????', '2026-03-03 06:59:02'),
(67, 'kpi', 'export_kpi', '?????????? ???????????? ????????????', 'Export KPI', '????', '2026-03-03 06:59:02'),
(68, 'activity_log', 'view_activity_log', '?????? ?????? ????????????????', 'View Activity Log', '????', '2026-03-03 06:59:02'),
(69, 'activity_log', 'clear_activity_log', '?????? ??????????', 'Clear Log', '???????', '2026-03-03 06:59:02'),
(70, 'activity_log', 'export_activity_log', '?????????? ??????????', 'Export Log', '????', '2026-03-03 06:59:02'),
(71, 'settings', 'view_settings', '?????? ??????????????????', 'View Settings', '??????', '2026-03-03 06:59:02'),
(72, 'settings', 'edit_settings', '?????????? ??????????????????', 'Edit Settings', '??????', '2026-03-03 06:59:02'),
(73, 'settings', 'manage_backups', '?????????? ?????????? ????????????????????', 'Manage Backups', '????', '2026-03-03 06:59:02'),
(74, 'settings', 'create_backup', '?????????? ???????? ????????????????', 'Create Backup', '????', '2026-03-03 06:59:02'),
(75, 'pharmacist_schedule', 'view_schedule', '?????? ????????????', 'View Schedule', '????', '2026-03-03 06:59:02'),
(76, 'pharmacist_schedule', 'add_schedule', '?????????? ????????', 'Add Schedule', '???', '2026-03-03 06:59:02'),
(77, 'pharmacist_schedule', 'edit_schedule', '?????????? ????????', 'Edit Schedule', '??????', '2026-03-03 06:59:02'),
(78, 'pharmacist_schedule', 'delete_schedule', '?????? ????????', 'Delete Schedule', '???????', '2026-03-03 06:59:02'),
(79, 'pharmacist_schedule', 'export_schedule_excel', '?????????? Excel', 'Export Excel', '????', '2026-03-03 06:59:02'),
(96, 'dashboard', 'view_dashboard', 'عرض لوحة التحكم', 'View Dashboard', '????', '2026-03-03 10:28:49'),
(97, 'dashboard', 'view_statistics', 'عرض الإحصائيات', 'View Statistics', '????', '2026-03-03 10:28:49'),
(98, 'dashboard', 'export_dashboard', 'تصدير البيانات', 'Export Data', '????', '2026-03-03 10:28:49'),
(99, 'dashboard', 'export_excel', 'تصدير Excel', 'Export Excel', '????', '2026-03-03 10:28:49'),
(100, 'dashboard', 'export_pdf', 'تصدير PDF', 'Export PDF', '????', '2026-03-03 10:28:49'),
(101, 'dashboard', 'comprehensive_report', 'التقرير الشامل', 'Comprehensive Report', '????', '2026-03-03 10:28:49'),
(102, 'dashboard', 'print_dashboard', 'طباعة لوحة التحكم', 'Print Dashboard', '????️', '2026-03-03 10:28:49'),
(103, 'dashboard', 'quick_actions', 'الإجراءات السريعة', 'Quick Actions', '⚡', '2026-03-03 10:28:49');

-- --------------------------------------------------------

--
-- Table structure for table `patients`
--

CREATE TABLE `patients` (
  `id` int(11) NOT NULL,
  `patient_name` varchar(100) NOT NULL,
  `mrn` varchar(20) NOT NULL,
  `gender` enum('male','female') NOT NULL,
  `age` int(11) NOT NULL,
  `units` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`units`)),
  `reporting_pharmacist_id` varchar(50) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `patients`
--

INSERT INTO `patients` (`id`, `patient_name`, `mrn`, `gender`, `age`, `units`, `reporting_pharmacist_id`, `date_of_birth`, `phone`, `address`, `created_at`, `updated_at`, `created_by`) VALUES
(13, 'محمد', '22222', 'female', 0, '[\"5th floor\",\"4th ICU\",\"CCU\",\"KU\"]', 'منار', NULL, NULL, NULL, '2026-04-30 14:51:54', '2026-04-30 14:51:54', 0);

-- --------------------------------------------------------

--
-- Table structure for table `patient_medication_tracking`
--

CREATE TABLE `patient_medication_tracking` (
  `id` int(11) NOT NULL,
  `tracking_date` date NOT NULL,
  `unit_id` int(11) DEFAULT NULL,
  `patient_file` varchar(50) DEFAULT NULL,
  `patient_name` varchar(100) DEFAULT NULL,
  `medical_number` varchar(50) DEFAULT NULL,
  `drug_compatibility` tinyint(4) DEFAULT 0,
  `antibiotic_program` tinyint(4) DEFAULT 0,
  `high_risk_drugs` tinyint(4) DEFAULT 0,
  `strict_priority` tinyint(4) DEFAULT 0,
  `error_documentation` tinyint(4) DEFAULT 0,
  `medication_description` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `documentation_quality` tinyint(4) DEFAULT 0,
  `pharmacist_name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `pharmacists`
--

CREATE TABLE `pharmacists` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pharmacists`
--

INSERT INTO `pharmacists` (`id`, `name`, `code`, `is_active`, `created_at`) VALUES
(1, 'Doda Ramadan', '6767', 1, '2026-02-10 11:00:15'),
(2, 'Doda ', '8989', 1, '2026-02-10 11:05:18'),
(3, 'اتعاب الاطباء', '555', 1, '2026-02-10 11:05:59'),
(4, 'دنيا ', '9090', 1, '2026-02-14 09:23:50');

-- --------------------------------------------------------

--
-- Table structure for table `pharmacist_schedule`
--

CREATE TABLE `pharmacist_schedule` (
  `id` int(11) NOT NULL,
  `pharmacist_id` int(11) NOT NULL,
  `team` varchar(50) NOT NULL,
  `schedule_date` date NOT NULL,
  `shift_time` varchar(20) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pharmacist_schedule`
--

INSERT INTO `pharmacist_schedule` (`id`, `pharmacist_id`, `team`, `schedule_date`, `shift_time`, `notes`, `created_by`, `created_at`, `updated_at`) VALUES
(3, 1, 'OR Team', '2026-02-01', 'M', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(4, 1, 'OR Team', '2026-02-02', 'O', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(5, 1, 'OR Team', '2026-02-03', 'E', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(6, 1, 'OR Team', '2026-02-04', 'N', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(7, 1, 'OR Team', '2026-02-05', 'N', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(8, 1, 'OR Team', '2026-02-06', 'E', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(9, 1, 'OR Team', '2026-02-07', 'O', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(10, 1, 'OR Team', '2026-02-08', 'N', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(11, 1, 'OR Team', '2026-02-09', 'M', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(12, 1, 'OR Team', '2026-02-10', 'O', NULL, 1, '2026-02-10 11:04:22', '2026-02-10 11:04:22'),
(13, 2, 'Ward Team', '2026-02-09', 'E', 'خخخخ', 1, '2026-02-10 11:05:40', '2026-02-10 11:05:40'),
(14, 3, 'OR Team', '2026-02-09', 'E', 'هننه', 1, '2026-02-10 11:06:17', '2026-02-10 11:06:17'),
(15, 2, 'OR Team', '2026-02-01', 'N', NULL, 1, '2026-02-10 11:07:05', '2026-02-10 11:07:05'),
(16, 2, 'OR Team', '2026-02-02', 'M', NULL, 1, '2026-02-10 11:07:05', '2026-02-10 11:07:05'),
(17, 4, 'OR Team', '2026-01-21', 'C', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(18, 4, 'OR Team', '2026-01-22', 'L', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(19, 4, 'OR Team', '2026-01-23', 'N', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(20, 4, 'OR Team', '2026-01-24', 'F', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(21, 4, 'OR Team', '2026-01-25', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(22, 4, 'OR Team', '2026-01-26', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(23, 4, 'OR Team', '2026-01-27', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(24, 4, 'OR Team', '2026-01-28', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(25, 4, 'OR Team', '2026-01-29', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(26, 4, 'OR Team', '2026-01-30', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(27, 4, 'OR Team', '2026-01-31', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(28, 4, 'OR Team', '2026-02-01', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(29, 4, 'OR Team', '2026-02-02', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(30, 4, 'OR Team', '2026-02-03', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(31, 4, 'OR Team', '2026-02-04', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(32, 4, 'OR Team', '2026-02-05', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(33, 4, 'OR Team', '2026-02-06', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(34, 4, 'OR Team', '2026-02-07', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(35, 4, 'OR Team', '2026-02-08', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(36, 4, 'OR Team', '2026-02-09', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(37, 4, 'OR Team', '2026-02-10', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(38, 4, 'OR Team', '2026-02-11', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(39, 4, 'OR Team', '2026-02-12', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(40, 4, 'OR Team', '2026-02-13', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(41, 4, 'OR Team', '2026-02-14', 'A', NULL, 1, '2026-02-14 10:32:27', '2026-02-14 10:32:27'),
(42, 4, 'ICU Team', '2026-02-14', 'E', '', 1, '2026-02-14 10:46:56', '2026-02-14 10:46:56'),
(43, 2, 'NICU Team', '2026-01-21', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(44, 2, 'NICU Team', '2026-01-22', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(45, 2, 'NICU Team', '2026-01-23', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(46, 2, 'NICU Team', '2026-01-24', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(47, 2, 'NICU Team', '2026-01-25', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(48, 2, 'NICU Team', '2026-01-26', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(49, 2, 'NICU Team', '2026-01-27', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(50, 2, 'NICU Team', '2026-01-28', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(51, 2, 'NICU Team', '2026-01-29', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(52, 2, 'NICU Team', '2026-01-30', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(53, 2, 'NICU Team', '2026-01-31', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(54, 2, 'NICU Team', '2026-02-01', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(55, 2, 'NICU Team', '2026-02-02', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(56, 2, 'NICU Team', '2026-02-03', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(57, 2, 'NICU Team', '2026-02-04', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(58, 2, 'NICU Team', '2026-02-05', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(59, 2, 'NICU Team', '2026-02-06', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(60, 2, 'NICU Team', '2026-02-07', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(61, 2, 'NICU Team', '2026-02-08', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(62, 2, 'NICU Team', '2026-02-09', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(63, 2, 'NICU Team', '2026-02-10', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(64, 2, 'NICU Team', '2026-02-11', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(65, 2, 'NICU Team', '2026-02-12', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(66, 2, 'NICU Team', '2026-02-13', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(67, 2, 'NICU Team', '2026-02-14', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(68, 2, 'NICU Team', '2026-02-15', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(69, 2, 'NICU Team', '2026-02-16', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(70, 2, 'NICU Team', '2026-02-17', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(71, 2, 'NICU Team', '2026-02-18', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(72, 2, 'NICU Team', '2026-02-19', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26'),
(73, 2, 'NICU Team', '2026-02-20', 'L', NULL, 1, '2026-02-16 09:54:26', '2026-02-16 09:54:26');

-- --------------------------------------------------------

--
-- Table structure for table `pharmacy_handover_forms`
--

CREATE TABLE `pharmacy_handover_forms` (
  `id` int(11) NOT NULL,
  `form_date` date NOT NULL,
  `shift_type` varchar(50) DEFAULT NULL,
  `checkboxes` text DEFAULT NULL,
  `items` text DEFAULT NULL,
  `yes_no_selections` text DEFAULT NULL,
  `reasons` text DEFAULT NULL,
  `narcotic_patients` text DEFAULT NULL,
  `disposal_records` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pharmacy_handover_forms`
--

INSERT INTO `pharmacy_handover_forms` (`id`, `form_date`, `shift_type`, `checkboxes`, `items`, `yes_no_selections`, `reasons`, `narcotic_patients`, `disposal_records`, `notes`, `created_by`, `created_at`) VALUES
(1, '2026-02-05', 'morning', '{\"1\":1,\"2\":0,\"3\":0,\"4\":0,\"5\":0,\"6\":0,\"7\":0,\"8\":0,\"9\":0,\"10\":0,\"11\":0,\"12\":0,\"13\":0,\"14\":0,\"15\":0}', '{\"1\":\"\\u0645\\u0646\\u062a\\u0646\\u0649\\u0627\\u0646\\u062a\",\"2\":\"\",\"3\":\"\",\"4\":\"\",\"5\":\"\",\"6\":\"\",\"7\":\"\",\"8\":\"\",\"9\":\"\",\"10\":\"\",\"11\":\"\",\"12\":\"\",\"13\":\"\",\"14\":\"\",\"15\":\"\"}', '{\"1\":\"no\",\"2\":\"\",\"3\":\"\",\"4\":\"\",\"5\":\"\",\"6\":\"\",\"7\":\"\",\"8\":\"\",\"9\":\"\",\"10\":\"\",\"11\":\"\",\"12\":\"\",\"13\":\"\",\"14\":\"\",\"15\":\"\"}', '{\"1\":\"\\u062e\\u062e\\u0647\\u0647\\u0647\\u0647\\u0647\\u0647\\u0647\\u0647\",\"2\":\"\",\"3\":\"\",\"4\":\"\",\"5\":\"\",\"6\":\"\",\"7\":\"\",\"8\":\"\",\"9\":\"\",\"10\":\"\",\"11\":\"\",\"12\":\"\",\"13\":\"\",\"14\":\"\",\"15\":\"\"}', '{\"1\":{\"name\":\"\\u0639\\u0639\\u0639\\u0639\\u0639\\u0639\",\"floor\":\"5\"},\"2\":{\"name\":\"\",\"floor\":\"\"},\"3\":{\"name\":\"\",\"floor\":\"\"},\"4\":{\"name\":\"\",\"floor\":\"\"},\"5\":{\"name\":\"\",\"floor\":\"\"},\"6\":{\"name\":\"\",\"floor\":\"\"}}', '{\"narcotic_disposal\":{\"1\":\"\\u0639\\u0639\\u0639\\u0639\\u0639\\u063a\",\"2\":\"\",\"3\":\"\"},\"expired_disposal\":{\"1\":\"fg\",\"2\":\"\",\"3\":\"\"},\"breakage_records\":{\"1\":\"ffds\",\"2\":\"\",\"3\":\"\"}}', 'حححححححححح', 1, '2026-02-05 13:01:45'),
(2, '2026-02-05', 'morning', '{\"1\":1,\"2\":0,\"3\":0,\"4\":0,\"5\":0,\"6\":0,\"7\":0,\"8\":0,\"9\":0,\"10\":0,\"11\":0,\"12\":0,\"13\":0,\"14\":0,\"15\":0}', '{\"1\":\"\\u0645\\u0646\\u062a\\u0646\\u0649\\u0627\\u0646\\u062a\",\"2\":\"\",\"3\":\"\",\"4\":\"\",\"5\":\"\",\"6\":\"\",\"7\":\"\",\"8\":\"\",\"9\":\"\",\"10\":\"\",\"11\":\"\",\"12\":\"\",\"13\":\"\",\"14\":\"\",\"15\":\"\"}', '{\"1\":\"no\",\"2\":\"\",\"3\":\"\",\"4\":\"\",\"5\":\"\",\"6\":\"\",\"7\":\"\",\"8\":\"\",\"9\":\"\",\"10\":\"\",\"11\":\"\",\"12\":\"\",\"13\":\"\",\"14\":\"\",\"15\":\"\"}', '{\"1\":\"\\u062e\\u062e\\u0647\\u0647\\u0647\\u0647\\u0647\\u0647\\u0647\\u0647\",\"2\":\"\",\"3\":\"\",\"4\":\"\",\"5\":\"\",\"6\":\"\",\"7\":\"\",\"8\":\"\",\"9\":\"\",\"10\":\"\",\"11\":\"\",\"12\":\"\",\"13\":\"\",\"14\":\"\",\"15\":\"\"}', '{\"1\":{\"name\":\"\\u0639\\u0639\\u0639\\u0639\\u0639\\u0639\",\"floor\":\"5\"},\"2\":{\"name\":\"\",\"floor\":\"\"},\"3\":{\"name\":\"\",\"floor\":\"\"},\"4\":{\"name\":\"\",\"floor\":\"\"},\"5\":{\"name\":\"\",\"floor\":\"\"},\"6\":{\"name\":\"\",\"floor\":\"\"}}', '{\"narcotic_disposal\":{\"1\":\"\\u0639\\u0639\\u0639\\u0639\\u0639\\u063a\",\"2\":\"\",\"3\":\"\"},\"expired_disposal\":{\"1\":\"fg\",\"2\":\"\",\"3\":\"\"},\"breakage_records\":{\"1\":\"ffds\",\"2\":\"\",\"3\":\"\"}}', 'حححححححححح', 1, '2026-02-05 13:03:47');

-- --------------------------------------------------------

--
-- Table structure for table `problem_definitions`
--

CREATE TABLE `problem_definitions` (
  `id` int(11) NOT NULL,
  `report_id` int(11) NOT NULL,
  `problem_type` enum('medication_dispensing','medication_safety','medication_labelling','wrong_regimen','illegible_hand_writing','arabic_writing','transcription_violation','inappropriate_drug_choice','patient_own_medication','wrong_preparation','wrong_administration','drug_related_problem','illegible_abbreviations','other') NOT NULL,
  `description` text DEFAULT NULL,
  `problem_cause` text DEFAULT NULL,
  `solution` text DEFAULT NULL,
  `severity` enum('low','medium','high','critical') DEFAULT 'medium',
  `status` enum('open','resolved','pending') DEFAULT 'open',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `problem_definitions`
--

INSERT INTO `problem_definitions` (`id`, `report_id`, `problem_type`, `description`, `problem_cause`, `solution`, `severity`, `status`, `created_at`, `updated_at`) VALUES
(1, 13, 'medication_safety', NULL, NULL, NULL, 'medium', 'open', '2026-04-30 14:54:15', '2026-04-30 14:54:15'),
(2, 13, 'patient_own_medication', NULL, NULL, NULL, 'medium', 'open', '2026-04-30 14:54:15', '2026-04-30 14:54:15'),
(3, 13, 'wrong_preparation', NULL, NULL, NULL, 'medium', 'open', '2026-04-30 14:54:15', '2026-04-30 14:54:15'),
(4, 14, 'medication_safety', NULL, NULL, NULL, 'medium', 'open', '2026-04-30 14:55:44', '2026-04-30 14:55:44'),
(5, 14, 'patient_own_medication', NULL, NULL, NULL, 'medium', 'open', '2026-04-30 14:55:44', '2026-04-30 14:55:44'),
(6, 14, 'wrong_preparation', NULL, NULL, NULL, 'medium', 'open', '2026-04-30 14:55:44', '2026-04-30 14:55:44');

-- --------------------------------------------------------

--
-- Table structure for table `report_attachments`
--

CREATE TABLE `report_attachments` (
  `id` int(11) NOT NULL,
  `report_id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(50) DEFAULT NULL,
  `file_size` int(11) DEFAULT NULL,
  `uploaded_by` int(11) NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `report_attachments`
--

INSERT INTO `report_attachments` (`id`, `report_id`, `file_name`, `file_path`, `file_type`, `file_size`, `uploaded_by`, `uploaded_at`) VALUES
(3, 13, 'Screenshot_2026-04-30-15-48-27-31_40deb401b9ffe8e1df2f1cc5ba480b12.jpg', 'uploads/reports/69f36d1723c81.jpg', 'image/jpeg', 307523, 10, '2026-04-30 14:54:15'),
(4, 14, 'Screenshot_2026-04-30-15-48-27-31_40deb401b9ffe8e1df2f1cc5ba480b12.jpg', 'uploads/reports/69f36d70eb8ce.jpg', 'image/jpeg', 307523, 10, '2026-04-30 14:55:44');

-- --------------------------------------------------------

--
-- Table structure for table `simple_patient_monitoring`
--

CREATE TABLE `simple_patient_monitoring` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `report_date` date NOT NULL DEFAULT curdate(),
  `consultant_name` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `simple_patient_monitoring`
--

INSERT INTO `simple_patient_monitoring` (`id`, `patient_id`, `report_date`, `consultant_name`, `location`, `notes`, `created_by`, `created_at`, `updated_at`) VALUES
(3, 13, '2026-04-30', NULL, NULL, NULL, 10, '2026-04-30 14:51:54', '2026-04-30 14:51:54');

-- --------------------------------------------------------

--
-- Table structure for table `surgery_antibiotics`
--

CREATE TABLE `surgery_antibiotics` (
  `id` int(11) NOT NULL,
  `patient_name` varchar(255) NOT NULL,
  `file_number` varchar(100) NOT NULL,
  `surgery_type` varchar(255) NOT NULL,
  `surgeon_name` varchar(255) NOT NULL,
  `surgeon_number` varchar(100) DEFAULT NULL,
  `dosage` varchar(100) DEFAULT NULL,
  `time` time DEFAULT NULL,
  `additional_dosage` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `surgery_antibiotics_log`
--

CREATE TABLE `surgery_antibiotics_log` (
  `id` int(11) NOT NULL,
  `serial_number` int(11) NOT NULL,
  `patient_name` varchar(255) DEFAULT NULL,
  `medical_number` varchar(100) DEFAULT NULL,
  `surgery_type` varchar(255) DEFAULT NULL,
  `consultant_name` varchar(255) DEFAULT NULL,
  `antibiotic_id` int(11) DEFAULT NULL,
  `antibiotic_code` varchar(50) DEFAULT NULL,
  `quantity` varchar(50) DEFAULT NULL,
  `unit_distinction` varchar(100) DEFAULT NULL,
  `entry_time` datetime DEFAULT NULL,
  `entry_status` enum('تم','لم يتم') DEFAULT 'لم يتم',
  `is_matched` tinyint(1) DEFAULT 0,
  `report_date` date DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_backups`
--

CREATE TABLE `system_backups` (
  `id` int(11) NOT NULL,
  `backup_name` varchar(255) NOT NULL,
  `backup_path` varchar(500) NOT NULL,
  `backup_size` bigint(20) DEFAULT NULL,
  `backup_type` enum('manual','automatic') DEFAULT 'manual',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_type` enum('string','number','boolean','json') DEFAULT 'string',
  `description` text DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `system_settings`
--

INSERT INTO `system_settings` (`id`, `setting_key`, `setting_value`, `setting_type`, `description`, `updated_by`, `updated_at`) VALUES
(1, 'system_name', 'نظام إدارة المضادات الحيوية', 'string', 'اسم النظام', NULL, '2026-01-10 10:51:00'),
(2, 'max_file_size', '10485760', 'number', 'الحد الأقصى لحجم الملف بالبايت (10MB)', NULL, '2026-01-10 10:51:00'),
(3, 'auto_backup', 'true', 'boolean', 'تفعيل النسخ الاحتياطي التلقائي', NULL, '2026-01-10 10:51:00'),
(4, 'backup_frequency', '7', 'number', 'تكرار النسخ الاحتياطي بالأيام', NULL, '2026-01-10 10:51:00'),
(5, 'session_timeout', '3600', 'number', 'مهلة انتهاء الجلسة بالثواني', NULL, '2026-01-10 10:51:00'),
(6, 'maintenance_mode', 'false', 'boolean', 'وضع الصيانة', NULL, '2026-01-10 10:51:00');

-- --------------------------------------------------------

--
-- Table structure for table `units`
--

CREATE TABLE `units` (
  `id` int(11) NOT NULL,
  `unit_name` varchar(50) NOT NULL,
  `unit_code` varchar(10) NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `units`
--

INSERT INTO `units` (`id`, `unit_name`, `unit_code`, `description`, `is_active`, `created_at`) VALUES
(1, '6th Floor', '6TH', 'الطابق السادس', 1, '2026-01-09 00:10:08'),
(2, '5th floor', '5TH', 'الطابق الخامس', 1, '2026-01-09 00:10:08'),
(3, '4th floor', '4TH', 'الطابق الرابع', 1, '2026-01-09 00:10:08'),
(4, '4th ICU', '4ICU', 'وحدة العناية المركزة - الطابق الرابع', 1, '2026-01-09 00:10:08'),
(5, 'NICU', 'NICU', 'وحدة العناية المركزة للأطفال حديثي الولادة', 1, '2026-01-09 00:10:08'),
(6, 'OR', 'OR', 'غرفة العمليات', 1, '2026-01-09 00:10:08'),
(7, 'CCU', 'CCU', 'وحدة العناية بالقلب', 1, '2026-01-09 00:10:08'),
(8, 'Cath Lab', 'CATH', 'معمل القسطرة القلبية', 1, '2026-01-09 00:10:08'),
(9, 'CICU', 'CICU', 'وحدة العناية المركزة القلبية', 1, '2026-01-09 00:10:08'),
(10, 'KU', 'KU', 'وحدة أمراض الكلى', 1, '2026-01-09 00:10:08'),
(11, 'Day care', 'DAY', 'وحدة الرعاية النهارية', 1, '2026-01-09 00:10:08'),
(12, 'GIT Lab', 'GIT', 'معمل الجهاز الهضمي', 1, '2026-01-09 00:10:08'),
(13, 'CABG unit', 'CABG', 'وحدة جراحة القلب المفتوح', 1, '2026-01-09 00:10:08');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `role` enum('admin','pharmacist','doctor','nurse') DEFAULT 'pharmacist',
  `is_active` tinyint(1) DEFAULT 1,
  `can_view_dashboard` tinyint(1) DEFAULT 1,
  `can_view_records` tinyint(1) DEFAULT 1,
  `can_add_records` tinyint(1) DEFAULT 1,
  `can_edit_records` tinyint(1) DEFAULT 0,
  `can_delete_records` tinyint(1) DEFAULT 0,
  `can_export` tinyint(1) DEFAULT 0,
  `can_print` tinyint(1) DEFAULT 1,
  `can_manage_users` tinyint(1) DEFAULT 0,
  `can_manage_antibiotics` tinyint(1) DEFAULT 0,
  `can_manage_patients` tinyint(1) DEFAULT 1,
  `can_view_reports` tinyint(1) DEFAULT 1,
  `can_add_reports` tinyint(1) DEFAULT 1,
  `can_edit_reports` tinyint(1) DEFAULT 0,
  `can_delete_reports` tinyint(1) DEFAULT 0,
  `can_view_icu` tinyint(1) DEFAULT 1,
  `can_manage_icu` tinyint(1) DEFAULT 0,
  `can_view_pharmacy` tinyint(1) DEFAULT 1,
  `can_manage_pharmacy` tinyint(1) DEFAULT 0,
  `can_view_kpi` tinyint(1) DEFAULT 1,
  `can_access_advanced_search` tinyint(1) DEFAULT 1,
  `can_view_activity_log` tinyint(1) DEFAULT 0,
  `can_create_backup` tinyint(1) DEFAULT 0,
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `email`, `password`, `full_name`, `role`, `is_active`, `can_view_dashboard`, `can_view_records`, `can_add_records`, `can_edit_records`, `can_delete_records`, `can_export`, `can_print`, `can_manage_users`, `can_manage_antibiotics`, `can_manage_patients`, `can_view_reports`, `can_add_reports`, `can_edit_reports`, `can_delete_reports`, `can_view_icu`, `can_manage_icu`, `can_view_pharmacy`, `can_manage_pharmacy`, `can_view_kpi`, `can_access_advanced_search`, `can_view_activity_log`, `can_create_backup`, `last_login`, `created_at`, `updated_at`) VALUES
(1, 'admin', 'admin@hospital.com', '12345678', 'Eng.Donia', 'admin', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, NULL, '2026-01-09 00:10:08', '2026-04-23 10:48:31'),
(10, 'admin2', 'doniaramadan23@gmail.com', '1234567', 'دنيا', 'admin', 1, 1, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 1, 0, 0, NULL, '2026-04-23 08:07:35', '2026-04-23 08:07:35'),
(11, 'Dr. Ahmed Farouk', 'AhmedFarouk@gmail.com', '123456', 'Dr. Ahmed Farouk', 'admin', 1, 1, 1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 1, 0, 0, NULL, '2026-05-02 12:51:59', '2026-05-02 13:09:07');

-- --------------------------------------------------------

--
-- Table structure for table `user_page_permissions`
--

CREATE TABLE `user_page_permissions` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `page_name` varchar(100) NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  `granted_by` int(11) DEFAULT NULL COMMENT '???? ?????? ????????????????',
  `granted_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_page_permissions`
--

INSERT INTO `user_page_permissions` (`id`, `user_id`, `page_name`, `permission_key`, `granted_by`, `granted_at`) VALUES
(236, 1, 'dashboard', 'view_dashboard', 1, '2026-03-03 10:10:52'),
(237, 1, 'dashboard', 'view_statistics', 1, '2026-03-03 10:10:52'),
(238, 1, 'dashboard', 'export_dashboard', 1, '2026-03-03 10:10:52'),
(239, 1, 'dashboard', 'export_excel', 1, '2026-03-03 10:10:52'),
(240, 1, 'dashboard', 'export_pdf', 1, '2026-03-03 10:10:52'),
(241, 1, 'dashboard', 'comprehensive_report', 1, '2026-03-03 10:10:52'),
(242, 1, 'dashboard', 'print_dashboard', 1, '2026-03-03 10:10:52'),
(243, 1, 'dashboard', 'quick_actions', 1, '2026-03-03 10:10:52'),
(244, 1, 'patients', 'view_patients', 1, '2026-03-03 10:10:52'),
(245, 1, 'patients', 'add_patient', 1, '2026-03-03 10:10:52'),
(246, 1, 'patients', 'edit_patient', 1, '2026-03-03 10:10:52'),
(247, 1, 'patients', 'delete_patient', 1, '2026-03-03 10:10:52'),
(248, 1, 'patients', 'view_patient_details', 1, '2026-03-03 10:10:52'),
(249, 1, 'patients', 'export_patients_excel', 1, '2026-03-03 10:10:52'),
(250, 1, 'patients', 'export_patients_pdf', 1, '2026-03-03 10:10:52'),
(251, 1, 'patients', 'print_patients', 1, '2026-03-03 10:10:52'),
(252, 1, 'antibiotics', 'view_antibiotics', 1, '2026-03-03 10:10:52'),
(253, 1, 'antibiotics', 'add_antibiotic', 1, '2026-03-03 10:10:52'),
(254, 1, 'antibiotics', 'edit_antibiotic', 1, '2026-03-03 10:10:52'),
(255, 1, 'antibiotics', 'delete_antibiotic', 1, '2026-03-03 10:10:52'),
(256, 1, 'antibiotics', 'toggle_antibiotic_status', 1, '2026-03-03 10:10:52'),
(257, 1, 'antibiotics', 'export_antibiotics_excel', 1, '2026-03-03 10:10:52'),
(258, 1, 'antibiotics', 'print_antibiotics', 1, '2026-03-03 10:10:52'),
(259, 1, 'reports', 'view_reports', 1, '2026-03-03 10:10:52'),
(260, 1, 'reports', 'add_report', 1, '2026-03-03 10:10:52'),
(261, 1, 'reports', 'edit_report', 1, '2026-03-03 10:10:52'),
(262, 1, 'reports', 'delete_report', 1, '2026-03-03 10:10:52'),
(263, 1, 'reports', 'export_reports_excel', 1, '2026-03-03 10:10:52'),
(264, 1, 'reports', 'export_reports_pdf', 1, '2026-03-03 10:10:52'),
(265, 1, 'reports', 'print_reports', 1, '2026-03-03 10:10:52'),
(266, 1, 'icu', 'view_icu', 1, '2026-03-03 10:10:52'),
(267, 1, 'icu', 'add_icu_record', 1, '2026-03-03 10:10:52'),
(268, 1, 'icu', 'edit_icu_record', 1, '2026-03-03 10:10:52'),
(269, 1, 'icu', 'delete_icu_record', 1, '2026-03-03 10:10:52'),
(270, 1, 'icu', 'view_icu_details', 1, '2026-03-03 10:10:52'),
(271, 1, 'icu', 'export_icu_excel', 1, '2026-03-03 10:10:52'),
(272, 1, 'icu', 'print_icu', 1, '2026-03-03 10:10:52'),
(273, 1, 'nursery', 'view_nursery', 1, '2026-03-03 10:10:52'),
(274, 1, 'nursery', 'add_nursery_record', 1, '2026-03-03 10:10:52'),
(275, 1, 'nursery', 'edit_nursery_record', 1, '2026-03-03 10:10:52'),
(276, 1, 'nursery', 'delete_nursery_record', 1, '2026-03-03 10:10:52'),
(277, 1, 'nursery', 'export_nursery_excel', 1, '2026-03-03 10:10:52'),
(278, 1, 'nursery', 'print_nursery', 1, '2026-03-03 10:10:52'),
(279, 1, 'operations', 'view_operations', 1, '2026-03-03 10:10:52'),
(280, 1, 'operations', 'add_operation', 1, '2026-03-03 10:10:52'),
(281, 1, 'operations', 'edit_operation', 1, '2026-03-03 10:10:52'),
(282, 1, 'operations', 'delete_operation', 1, '2026-03-03 10:10:52'),
(283, 1, 'operations', 'export_operations_excel', 1, '2026-03-03 10:10:52'),
(284, 1, 'operations', 'print_operations', 1, '2026-03-03 10:10:52'),
(285, 1, 'pharmacy', 'view_pharmacy', 1, '2026-03-03 10:10:52'),
(286, 1, 'pharmacy', 'add_intervention', 1, '2026-03-03 10:10:52'),
(287, 1, 'pharmacy', 'edit_intervention', 1, '2026-03-03 10:10:52'),
(288, 1, 'pharmacy', 'delete_intervention', 1, '2026-03-03 10:10:52'),
(289, 1, 'pharmacy', 'export_pharmacy_excel', 1, '2026-03-03 10:10:52'),
(290, 1, 'pharmacy', 'print_pharmacy', 1, '2026-03-03 10:10:52'),
(291, 1, 'monitoring', 'view_monitoring', 1, '2026-03-03 10:10:52'),
(292, 1, 'monitoring', 'add_monitoring', 1, '2026-03-03 10:10:52'),
(293, 1, 'monitoring', 'edit_monitoring', 1, '2026-03-03 10:10:52'),
(294, 1, 'monitoring', 'export_monitoring_excel', 1, '2026-03-03 10:10:52'),
(295, 1, 'monitoring', 'print_monitoring', 1, '2026-03-03 10:10:52'),
(296, 1, 'users', 'view_users', 1, '2026-03-03 10:10:52'),
(297, 1, 'users', 'add_user', 1, '2026-03-03 10:10:52'),
(298, 1, 'users', 'edit_user', 1, '2026-03-03 10:10:52'),
(299, 1, 'users', 'delete_user', 1, '2026-03-03 10:10:52'),
(300, 1, 'users', 'manage_permissions', 1, '2026-03-03 10:10:52'),
(301, 1, 'advanced_search', 'use_advanced_search', 1, '2026-03-03 10:10:52'),
(302, 1, 'advanced_search', 'export_search_results', 1, '2026-03-03 10:10:52'),
(303, 1, 'reports_center', 'view_reports_center', 1, '2026-03-03 10:10:52'),
(304, 1, 'reports_center', 'generate_comprehensive_report', 1, '2026-03-03 10:10:52'),
(305, 1, 'reports_center', 'export_comprehensive_excel', 1, '2026-03-03 10:10:52'),
(306, 1, 'kpi', 'view_kpi', 1, '2026-03-03 10:10:52'),
(307, 1, 'kpi', 'export_kpi', 1, '2026-03-03 10:10:52'),
(308, 1, 'activity_log', 'view_activity_log', 1, '2026-03-03 10:10:52'),
(309, 1, 'activity_log', 'clear_activity_log', 1, '2026-03-03 10:10:52'),
(310, 1, 'activity_log', 'export_activity_log', 1, '2026-03-03 10:10:52'),
(311, 1, 'settings', 'view_settings', 1, '2026-03-03 10:10:52'),
(312, 1, 'settings', 'edit_settings', 1, '2026-03-03 10:10:52'),
(313, 1, 'settings', 'manage_backups', 1, '2026-03-03 10:10:52'),
(314, 1, 'settings', 'create_backup', 1, '2026-03-03 10:10:52'),
(315, 1, 'pharmacist_schedule', 'view_schedule', 1, '2026-03-03 10:10:52'),
(316, 1, 'pharmacist_schedule', 'add_schedule', 1, '2026-03-03 10:10:52'),
(317, 1, 'pharmacist_schedule', 'edit_schedule', 1, '2026-03-03 10:10:52'),
(318, 1, 'pharmacist_schedule', 'delete_schedule', 1, '2026-03-03 10:10:52'),
(319, 1, 'pharmacist_schedule', 'export_schedule_excel', 1, '2026-03-03 10:10:52'),
(446, 10, 'dashboard', 'view_dashboard', 1, '2026-04-23 08:07:35'),
(447, 10, 'dashboard', 'view_statistics', 1, '2026-04-23 08:07:35'),
(448, 10, 'dashboard', 'export_dashboard', 1, '2026-04-23 08:07:35'),
(449, 10, 'dashboard', 'export_excel', 1, '2026-04-23 08:07:35'),
(450, 10, 'dashboard', 'export_pdf', 1, '2026-04-23 08:07:35'),
(451, 10, 'dashboard', 'comprehensive_report', 1, '2026-04-23 08:07:35'),
(452, 10, 'dashboard', 'print_dashboard', 1, '2026-04-23 08:07:35'),
(453, 10, 'dashboard', 'quick_actions', 1, '2026-04-23 08:07:35'),
(454, 10, 'patients', 'view_patients', 1, '2026-04-23 08:07:35'),
(455, 10, 'patients', 'add_patient', 1, '2026-04-23 08:07:35'),
(456, 10, 'patients', 'edit_patient', 1, '2026-04-23 08:07:35'),
(457, 10, 'patients', 'delete_patient', 1, '2026-04-23 08:07:35'),
(458, 10, 'patients', 'view_patient_details', 1, '2026-04-23 08:07:35'),
(459, 10, 'patients', 'export_patients_excel', 1, '2026-04-23 08:07:35'),
(460, 10, 'patients', 'export_patients_pdf', 1, '2026-04-23 08:07:35'),
(461, 10, 'patients', 'print_patients', 1, '2026-04-23 08:07:35'),
(462, 10, 'antibiotics', 'view_antibiotics', 1, '2026-04-23 08:07:35'),
(463, 10, 'antibiotics', 'add_antibiotic', 1, '2026-04-23 08:07:35'),
(464, 10, 'antibiotics', 'edit_antibiotic', 1, '2026-04-23 08:07:35'),
(465, 10, 'antibiotics', 'delete_antibiotic', 1, '2026-04-23 08:07:35'),
(466, 10, 'antibiotics', 'toggle_antibiotic_status', 1, '2026-04-23 08:07:35'),
(467, 10, 'antibiotics', 'export_antibiotics_excel', 1, '2026-04-23 08:07:35'),
(468, 10, 'antibiotics', 'print_antibiotics', 1, '2026-04-23 08:07:35'),
(469, 10, 'reports', 'view_reports', 1, '2026-04-23 08:07:35'),
(470, 10, 'reports', 'add_report', 1, '2026-04-23 08:07:35'),
(471, 10, 'reports', 'edit_report', 1, '2026-04-23 08:07:35'),
(472, 10, 'reports', 'delete_report', 1, '2026-04-23 08:07:35'),
(473, 10, 'reports', 'export_reports_excel', 1, '2026-04-23 08:07:35'),
(474, 10, 'reports', 'export_reports_pdf', 1, '2026-04-23 08:07:35'),
(475, 10, 'reports', 'print_reports', 1, '2026-04-23 08:07:35'),
(476, 10, 'icu', 'view_icu', 1, '2026-04-23 08:07:35'),
(477, 10, 'icu', 'add_icu_record', 1, '2026-04-23 08:07:35'),
(478, 10, 'icu', 'edit_icu_record', 1, '2026-04-23 08:07:35'),
(479, 10, 'icu', 'delete_icu_record', 1, '2026-04-23 08:07:35'),
(480, 10, 'icu', 'view_icu_details', 1, '2026-04-23 08:07:35'),
(481, 10, 'icu', 'export_icu_excel', 1, '2026-04-23 08:07:35'),
(482, 10, 'icu', 'print_icu', 1, '2026-04-23 08:07:35'),
(483, 10, 'nursery', 'view_nursery', 1, '2026-04-23 08:07:35'),
(484, 10, 'nursery', 'add_nursery_record', 1, '2026-04-23 08:07:35'),
(485, 10, 'nursery', 'edit_nursery_record', 1, '2026-04-23 08:07:35'),
(486, 10, 'nursery', 'delete_nursery_record', 1, '2026-04-23 08:07:35'),
(487, 10, 'nursery', 'export_nursery_excel', 1, '2026-04-23 08:07:35'),
(488, 10, 'nursery', 'print_nursery', 1, '2026-04-23 08:07:35'),
(489, 10, 'operations', 'view_operations', 1, '2026-04-23 08:07:35'),
(490, 10, 'operations', 'add_operation', 1, '2026-04-23 08:07:35'),
(491, 10, 'operations', 'edit_operation', 1, '2026-04-23 08:07:35'),
(492, 10, 'operations', 'delete_operation', 1, '2026-04-23 08:07:35'),
(493, 10, 'operations', 'export_operations_excel', 1, '2026-04-23 08:07:35'),
(494, 10, 'operations', 'print_operations', 1, '2026-04-23 08:07:35'),
(495, 10, 'pharmacy', 'view_pharmacy', 1, '2026-04-23 08:07:35'),
(496, 10, 'pharmacy', 'add_intervention', 1, '2026-04-23 08:07:35'),
(497, 10, 'pharmacy', 'edit_intervention', 1, '2026-04-23 08:07:36'),
(498, 10, 'pharmacy', 'delete_intervention', 1, '2026-04-23 08:07:36'),
(499, 10, 'pharmacy', 'export_pharmacy_excel', 1, '2026-04-23 08:07:36'),
(500, 10, 'pharmacy', 'print_pharmacy', 1, '2026-04-23 08:07:36'),
(501, 10, 'monitoring', 'view_monitoring', 1, '2026-04-23 08:07:36'),
(502, 10, 'monitoring', 'add_monitoring', 1, '2026-04-23 08:07:36'),
(503, 10, 'monitoring', 'edit_monitoring', 1, '2026-04-23 08:07:36'),
(504, 10, 'monitoring', 'export_monitoring_excel', 1, '2026-04-23 08:07:36'),
(505, 10, 'monitoring', 'print_monitoring', 1, '2026-04-23 08:07:36'),
(506, 10, 'users', 'view_users', 1, '2026-04-23 08:07:36'),
(507, 10, 'users', 'add_user', 1, '2026-04-23 08:07:36'),
(508, 10, 'users', 'edit_user', 1, '2026-04-23 08:07:36'),
(509, 10, 'users', 'delete_user', 1, '2026-04-23 08:07:36'),
(510, 10, 'users', 'manage_permissions', 1, '2026-04-23 08:07:36'),
(511, 10, 'advanced_search', 'use_advanced_search', 1, '2026-04-23 08:07:36'),
(512, 10, 'advanced_search', 'export_search_results', 1, '2026-04-23 08:07:36'),
(513, 10, 'reports_center', 'view_reports_center', 1, '2026-04-23 08:07:36'),
(514, 10, 'reports_center', 'generate_comprehensive_report', 1, '2026-04-23 08:07:36'),
(515, 10, 'reports_center', 'export_comprehensive_excel', 1, '2026-04-23 08:07:36'),
(516, 10, 'kpi', 'view_kpi', 1, '2026-04-23 08:07:36'),
(517, 10, 'kpi', 'export_kpi', 1, '2026-04-23 08:07:36'),
(518, 10, 'activity_log', 'view_activity_log', 1, '2026-04-23 08:07:36'),
(519, 10, 'activity_log', 'clear_activity_log', 1, '2026-04-23 08:07:36'),
(520, 10, 'activity_log', 'export_activity_log', 1, '2026-04-23 08:07:36'),
(521, 10, 'settings', 'view_settings', 1, '2026-04-23 08:07:36'),
(522, 10, 'settings', 'edit_settings', 1, '2026-04-23 08:07:36'),
(523, 10, 'settings', 'manage_backups', 1, '2026-04-23 08:07:36'),
(524, 10, 'settings', 'create_backup', 1, '2026-04-23 08:07:36'),
(525, 10, 'pharmacist_schedule', 'view_schedule', 1, '2026-04-23 08:07:36'),
(526, 10, 'pharmacist_schedule', 'add_schedule', 1, '2026-04-23 08:07:36'),
(527, 10, 'pharmacist_schedule', 'edit_schedule', 1, '2026-04-23 08:07:36'),
(528, 10, 'pharmacist_schedule', 'delete_schedule', 1, '2026-04-23 08:07:36'),
(529, 10, 'pharmacist_schedule', 'export_schedule_excel', 1, '2026-04-23 08:07:36'),
(698, 11, 'dashboard', 'view_dashboard', 11, '2026-05-02 13:11:31'),
(699, 11, 'dashboard', 'view_statistics', 11, '2026-05-02 13:11:31'),
(700, 11, 'dashboard', 'export_dashboard', 11, '2026-05-02 13:11:31'),
(701, 11, 'dashboard', 'export_excel', 11, '2026-05-02 13:11:31'),
(702, 11, 'dashboard', 'export_pdf', 11, '2026-05-02 13:11:31'),
(703, 11, 'dashboard', 'comprehensive_report', 11, '2026-05-02 13:11:31'),
(704, 11, 'dashboard', 'print_dashboard', 11, '2026-05-02 13:11:31'),
(705, 11, 'dashboard', 'quick_actions', 11, '2026-05-02 13:11:31'),
(706, 11, 'patients', 'view_patients', 11, '2026-05-02 13:11:31'),
(707, 11, 'patients', 'add_patient', 11, '2026-05-02 13:11:31'),
(708, 11, 'patients', 'edit_patient', 11, '2026-05-02 13:11:32'),
(709, 11, 'patients', 'delete_patient', 11, '2026-05-02 13:11:32'),
(710, 11, 'patients', 'view_patient_details', 11, '2026-05-02 13:11:32'),
(711, 11, 'patients', 'export_patients_excel', 11, '2026-05-02 13:11:32'),
(712, 11, 'patients', 'export_patients_pdf', 11, '2026-05-02 13:11:32'),
(713, 11, 'patients', 'print_patients', 11, '2026-05-02 13:11:32'),
(714, 11, 'antibiotics', 'view_antibiotics', 11, '2026-05-02 13:11:32'),
(715, 11, 'antibiotics', 'add_antibiotic', 11, '2026-05-02 13:11:32'),
(716, 11, 'antibiotics', 'edit_antibiotic', 11, '2026-05-02 13:11:32'),
(717, 11, 'antibiotics', 'delete_antibiotic', 11, '2026-05-02 13:11:32'),
(718, 11, 'antibiotics', 'toggle_antibiotic_status', 11, '2026-05-02 13:11:32'),
(719, 11, 'antibiotics', 'export_antibiotics_excel', 11, '2026-05-02 13:11:32'),
(720, 11, 'antibiotics', 'print_antibiotics', 11, '2026-05-02 13:11:32'),
(721, 11, 'reports', 'view_reports', 11, '2026-05-02 13:11:32'),
(722, 11, 'reports', 'add_report', 11, '2026-05-02 13:11:32'),
(723, 11, 'reports', 'edit_report', 11, '2026-05-02 13:11:32'),
(724, 11, 'reports', 'delete_report', 11, '2026-05-02 13:11:32'),
(725, 11, 'reports', 'export_reports_excel', 11, '2026-05-02 13:11:32'),
(726, 11, 'reports', 'export_reports_pdf', 11, '2026-05-02 13:11:32'),
(727, 11, 'reports', 'print_reports', 11, '2026-05-02 13:11:32'),
(728, 11, 'icu', 'view_icu', 11, '2026-05-02 13:11:32'),
(729, 11, 'icu', 'add_icu_record', 11, '2026-05-02 13:11:32'),
(730, 11, 'icu', 'edit_icu_record', 11, '2026-05-02 13:11:32'),
(731, 11, 'icu', 'delete_icu_record', 11, '2026-05-02 13:11:32'),
(732, 11, 'icu', 'view_icu_details', 11, '2026-05-02 13:11:32'),
(733, 11, 'icu', 'export_icu_excel', 11, '2026-05-02 13:11:32'),
(734, 11, 'icu', 'print_icu', 11, '2026-05-02 13:11:32'),
(735, 11, 'nursery', 'view_nursery', 11, '2026-05-02 13:11:32'),
(736, 11, 'nursery', 'add_nursery_record', 11, '2026-05-02 13:11:32'),
(737, 11, 'nursery', 'edit_nursery_record', 11, '2026-05-02 13:11:32'),
(738, 11, 'nursery', 'delete_nursery_record', 11, '2026-05-02 13:11:32'),
(739, 11, 'nursery', 'export_nursery_excel', 11, '2026-05-02 13:11:32'),
(740, 11, 'nursery', 'print_nursery', 11, '2026-05-02 13:11:32'),
(741, 11, 'operations', 'view_operations', 11, '2026-05-02 13:11:32'),
(742, 11, 'operations', 'add_operation', 11, '2026-05-02 13:11:32'),
(743, 11, 'operations', 'edit_operation', 11, '2026-05-02 13:11:32'),
(744, 11, 'operations', 'delete_operation', 11, '2026-05-02 13:11:32'),
(745, 11, 'operations', 'export_operations_excel', 11, '2026-05-02 13:11:32'),
(746, 11, 'operations', 'print_operations', 11, '2026-05-02 13:11:32'),
(747, 11, 'pharmacy', 'view_pharmacy', 11, '2026-05-02 13:11:32'),
(748, 11, 'pharmacy', 'add_intervention', 11, '2026-05-02 13:11:32'),
(749, 11, 'pharmacy', 'edit_intervention', 11, '2026-05-02 13:11:32'),
(750, 11, 'pharmacy', 'delete_intervention', 11, '2026-05-02 13:11:32'),
(751, 11, 'pharmacy', 'export_pharmacy_excel', 11, '2026-05-02 13:11:32'),
(752, 11, 'pharmacy', 'print_pharmacy', 11, '2026-05-02 13:11:32'),
(753, 11, 'monitoring', 'view_monitoring', 11, '2026-05-02 13:11:32'),
(754, 11, 'monitoring', 'add_monitoring', 11, '2026-05-02 13:11:32'),
(755, 11, 'monitoring', 'edit_monitoring', 11, '2026-05-02 13:11:32'),
(756, 11, 'monitoring', 'export_monitoring_excel', 11, '2026-05-02 13:11:32'),
(757, 11, 'monitoring', 'print_monitoring', 11, '2026-05-02 13:11:32'),
(758, 11, 'users', 'view_users', 11, '2026-05-02 13:11:32'),
(759, 11, 'users', 'add_user', 11, '2026-05-02 13:11:32'),
(760, 11, 'users', 'edit_user', 11, '2026-05-02 13:11:32'),
(761, 11, 'users', 'delete_user', 11, '2026-05-02 13:11:32'),
(762, 11, 'users', 'manage_permissions', 11, '2026-05-02 13:11:32'),
(763, 11, 'advanced_search', 'use_advanced_search', 11, '2026-05-02 13:11:32'),
(764, 11, 'advanced_search', 'export_search_results', 11, '2026-05-02 13:11:32'),
(765, 11, 'reports_center', 'view_reports_center', 11, '2026-05-02 13:11:32'),
(766, 11, 'reports_center', 'generate_comprehensive_report', 11, '2026-05-02 13:11:32'),
(767, 11, 'reports_center', 'export_comprehensive_excel', 11, '2026-05-02 13:11:32'),
(768, 11, 'kpi', 'view_kpi', 11, '2026-05-02 13:11:32'),
(769, 11, 'kpi', 'export_kpi', 11, '2026-05-02 13:11:32'),
(770, 11, 'activity_log', 'view_activity_log', 11, '2026-05-02 13:11:32'),
(771, 11, 'activity_log', 'clear_activity_log', 11, '2026-05-02 13:11:32'),
(772, 11, 'activity_log', 'export_activity_log', 11, '2026-05-02 13:11:32'),
(773, 11, 'settings', 'view_settings', 11, '2026-05-02 13:11:32'),
(774, 11, 'settings', 'edit_settings', 11, '2026-05-02 13:11:32'),
(775, 11, 'settings', 'manage_backups', 11, '2026-05-02 13:11:32'),
(776, 11, 'settings', 'create_backup', 11, '2026-05-02 13:11:32'),
(777, 11, 'pharmacist_schedule', 'view_schedule', 11, '2026-05-02 13:11:32'),
(778, 11, 'pharmacist_schedule', 'add_schedule', 11, '2026-05-02 13:11:32'),
(779, 11, 'pharmacist_schedule', 'edit_schedule', 11, '2026-05-02 13:11:32'),
(780, 11, 'pharmacist_schedule', 'delete_schedule', 11, '2026-05-02 13:11:32'),
(781, 11, 'pharmacist_schedule', 'export_schedule_excel', 11, '2026-05-02 13:11:32');

-- --------------------------------------------------------

--
-- Stand-in structure for view `v_user_page_permissions`
-- (See below for the actual view)
--
CREATE TABLE `v_user_page_permissions` (
`user_id` int(11)
,`username` varchar(50)
,`full_name` varchar(100)
,`role` enum('admin','pharmacist','doctor','nurse')
,`page_name` varchar(100)
,`permission_key` varchar(100)
,`permission_name_ar` varchar(200)
,`permission_name_en` varchar(200)
,`permission_icon` varchar(10)
,`has_permission` int(1)
,`granted_at` timestamp
,`granted_by_username` varchar(50)
);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_log`
--
ALTER TABLE `activity_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_activity_log_user` (`user_id`),
  ADD KEY `idx_activity_log_date` (`created_at`),
  ADD KEY `idx_activity_log_user_date` (`user_id`,`created_at`);

--
-- Indexes for table `antibiotics`
--
ALTER TABLE `antibiotics`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `antibiotic_reports`
--
ALTER TABLE `antibiotic_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `reporting_pharmacist_id` (`reporting_pharmacist_id`),
  ADD KEY `unit_id` (`unit_id`),
  ADD KEY `antibiotic_id` (`antibiotic_id`),
  ADD KEY `idx_reports_date` (`report_date`),
  ADD KEY `idx_reports_status` (`status`),
  ADD KEY `idx_reports_patient` (`patient_id`),
  ADD KEY `idx_antibiotic_reports_status` (`status`);

--
-- Indexes for table `breakage_reports`
--
ALTER TABLE `breakage_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_breakage_date` (`report_date`),
  ADD KEY `idx_breakage_reported_by` (`reported_by`);

--
-- Indexes for table `clinical_pharmacy_forms`
--
ALTER TABLE `clinical_pharmacy_forms`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `custom_stats`
--
ALTER TABLE `custom_stats`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_stat` (`stat_date`,`stat_name`),
  ADD KEY `updated_by` (`updated_by`);

--
-- Indexes for table `daily_pharmacist_reports`
--
ALTER TABLE `daily_pharmacist_reports`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_daily_report` (`report_date`,`pharmacist_id`),
  ADD KEY `idx_report_date` (`report_date`),
  ADD KEY `idx_pharmacist_id` (`pharmacist_id`);

--
-- Indexes for table `daily_statistics`
--
ALTER TABLE `daily_statistics`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_date` (`stat_date`);

--
-- Indexes for table `expired_drugs_disposal`
--
ALTER TABLE `expired_drugs_disposal`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_expired_date` (`report_date`),
  ADD KEY `idx_expired_reported_by` (`reported_by`);

--
-- Indexes for table `export_logs`
--
ALTER TABLE `export_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `exported_by` (`exported_by`);

--
-- Indexes for table `holidays`
--
ALTER TABLE `holidays`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_holiday_date` (`holiday_date`);

--
-- Indexes for table `icu_daily_reports`
--
ALTER TABLE `icu_daily_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_icu_report_date` (`report_date`),
  ADD KEY `idx_icu_created_by` (`created_by`),
  ADD KEY `idx_icu_pharmacist` (`pharmacist_shift`),
  ADD KEY `idx_icu_date_pharmacist` (`report_date`,`pharmacist_shift`);

--
-- Indexes for table `internal_reports`
--
ALTER TABLE `internal_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `narcotic_disposal_reports`
--
ALTER TABLE `narcotic_disposal_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_narcotic_date` (`report_date`),
  ADD KEY `idx_narcotic_reported_by` (`reported_by`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_notifications` (`user_id`,`is_read`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `notification_settings`
--
ALTER TABLE `notification_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_settings` (`user_id`);

--
-- Indexes for table `nursery_pediatric_reports`
--
ALTER TABLE `nursery_pediatric_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_nursery_report_date` (`report_date`),
  ADD KEY `idx_nursery_created_by` (`created_by`),
  ADD KEY `idx_nursery_pharmacist` (`pharmacist_name`),
  ADD KEY `idx_nursery_shift` (`shift_type`);

--
-- Indexes for table `page_permissions`
--
ALTER TABLE `page_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_page_permission` (`page_name`,`permission_key`);

--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `mrn` (`mrn`),
  ADD KEY `idx_patients_mrn` (`mrn`);

--
-- Indexes for table `patient_medication_tracking`
--
ALTER TABLE `patient_medication_tracking`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_patient_medication_date_unit` (`tracking_date`,`unit_id`);

--
-- Indexes for table `pharmacists`
--
ALTER TABLE `pharmacists`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `pharmacist_schedule`
--
ALTER TABLE `pharmacist_schedule`
  ADD PRIMARY KEY (`id`),
  ADD KEY `pharmacist_id` (`pharmacist_id`),
  ADD KEY `idx_schedule_date` (`schedule_date`),
  ADD KEY `idx_team` (`team`);

--
-- Indexes for table `pharmacy_handover_forms`
--
ALTER TABLE `pharmacy_handover_forms`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `problem_definitions`
--
ALTER TABLE `problem_definitions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `report_id` (`report_id`);

--
-- Indexes for table `report_attachments`
--
ALTER TABLE `report_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `report_id` (`report_id`),
  ADD KEY `uploaded_by` (`uploaded_by`);

--
-- Indexes for table `simple_patient_monitoring`
--
ALTER TABLE `simple_patient_monitoring`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_patient_id` (`patient_id`),
  ADD KEY `idx_report_date` (`report_date`);

--
-- Indexes for table `surgery_antibiotics`
--
ALTER TABLE `surgery_antibiotics`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `surgery_antibiotics_log`
--
ALTER TABLE `surgery_antibiotics_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `antibiotic_id` (`antibiotic_id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `system_backups`
--
ALTER TABLE `system_backups`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`),
  ADD KEY `updated_by` (`updated_by`);

--
-- Indexes for table `units`
--
ALTER TABLE `units`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unit_code` (`unit_code`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `user_page_permissions`
--
ALTER TABLE `user_page_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_page_permission` (`user_id`,`page_name`,`permission_key`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `granted_by` (`granted_by`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_log`
--
ALTER TABLE `activity_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=67;

--
-- AUTO_INCREMENT for table `antibiotics`
--
ALTER TABLE `antibiotics`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `antibiotic_reports`
--
ALTER TABLE `antibiotic_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `breakage_reports`
--
ALTER TABLE `breakage_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `clinical_pharmacy_forms`
--
ALTER TABLE `clinical_pharmacy_forms`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `custom_stats`
--
ALTER TABLE `custom_stats`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=123;

--
-- AUTO_INCREMENT for table `daily_pharmacist_reports`
--
ALTER TABLE `daily_pharmacist_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `daily_statistics`
--
ALTER TABLE `daily_statistics`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expired_drugs_disposal`
--
ALTER TABLE `expired_drugs_disposal`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `export_logs`
--
ALTER TABLE `export_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `holidays`
--
ALTER TABLE `holidays`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=79;

--
-- AUTO_INCREMENT for table `icu_daily_reports`
--
ALTER TABLE `icu_daily_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `internal_reports`
--
ALTER TABLE `internal_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `narcotic_disposal_reports`
--
ALTER TABLE `narcotic_disposal_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

--
-- AUTO_INCREMENT for table `notification_settings`
--
ALTER TABLE `notification_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `nursery_pediatric_reports`
--
ALTER TABLE `nursery_pediatric_reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `page_permissions`
--
ALTER TABLE `page_permissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=104;

--
-- AUTO_INCREMENT for table `patients`
--
ALTER TABLE `patients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `patient_medication_tracking`
--
ALTER TABLE `patient_medication_tracking`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `pharmacists`
--
ALTER TABLE `pharmacists`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `pharmacist_schedule`
--
ALTER TABLE `pharmacist_schedule`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=74;

--
-- AUTO_INCREMENT for table `pharmacy_handover_forms`
--
ALTER TABLE `pharmacy_handover_forms`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `problem_definitions`
--
ALTER TABLE `problem_definitions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `report_attachments`
--
ALTER TABLE `report_attachments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `simple_patient_monitoring`
--
ALTER TABLE `simple_patient_monitoring`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `surgery_antibiotics`
--
ALTER TABLE `surgery_antibiotics`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `surgery_antibiotics_log`
--
ALTER TABLE `surgery_antibiotics_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `system_backups`
--
ALTER TABLE `system_backups`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `system_settings`
--
ALTER TABLE `system_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `units`
--
ALTER TABLE `units`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `user_page_permissions`
--
ALTER TABLE `user_page_permissions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=782;

-- --------------------------------------------------------

--
-- Structure for view `daily_reports_summary`
--
DROP TABLE IF EXISTS `daily_reports_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `daily_reports_summary`  AS SELECT `dpr`.`id` AS `id`, `dpr`.`report_date` AS `report_date`, `dpr`.`pharmacist_during_shift` AS `pharmacist_during_shift`, `dpr`.`total_patients` AS `total_patients`, `dpr`.`total_discharged` AS `total_discharged`, `u`.`full_name` AS `created_by_name`, `u`.`role` AS `pharmacist_role`, `dpr`.`created_at` AS `created_at`, `dpr`.`updated_at` AS `updated_at`, json_length(`dpr`.`patients_data`) AS `patients_with_data`, json_length(`dpr`.`sedation_patients`) AS `sedation_count` FROM (`daily_pharmacist_reports` `dpr` join `users` `u` on(`dpr`.`pharmacist_id` = `u`.`id`)) ORDER BY `dpr`.`report_date` DESC ;

-- --------------------------------------------------------

--
-- Structure for view `monthly_pharmacist_stats`
--
DROP TABLE IF EXISTS `monthly_pharmacist_stats`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `monthly_pharmacist_stats`  AS SELECT year(`daily_pharmacist_reports`.`report_date`) AS `report_year`, month(`daily_pharmacist_reports`.`report_date`) AS `report_month`, count(0) AS `total_reports`, avg(`daily_pharmacist_reports`.`total_patients`) AS `avg_patients_per_day`, avg(`daily_pharmacist_reports`.`total_discharged`) AS `avg_discharged_per_day`, sum(`daily_pharmacist_reports`.`total_patients`) AS `total_patients_month`, sum(`daily_pharmacist_reports`.`total_discharged`) AS `total_discharged_month`, count(distinct `daily_pharmacist_reports`.`pharmacist_id`) AS `active_pharmacists` FROM `daily_pharmacist_reports` GROUP BY year(`daily_pharmacist_reports`.`report_date`), month(`daily_pharmacist_reports`.`report_date`) ORDER BY year(`daily_pharmacist_reports`.`report_date`) DESC, month(`daily_pharmacist_reports`.`report_date`) DESC ;

-- --------------------------------------------------------

--
-- Structure for view `v_user_page_permissions`
--
DROP TABLE IF EXISTS `v_user_page_permissions`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `v_user_page_permissions`  AS SELECT `u`.`id` AS `user_id`, `u`.`username` AS `username`, `u`.`full_name` AS `full_name`, `u`.`role` AS `role`, `pp`.`page_name` AS `page_name`, `pp`.`permission_key` AS `permission_key`, `pp`.`permission_name_ar` AS `permission_name_ar`, `pp`.`permission_name_en` AS `permission_name_en`, `pp`.`permission_icon` AS `permission_icon`, CASE WHEN `upp`.`id` is not null THEN 1 WHEN `u`.`role` = 'admin' THEN 1 ELSE 0 END AS `has_permission`, `upp`.`granted_at` AS `granted_at`, `granter`.`username` AS `granted_by_username` FROM (((`users` `u` join `page_permissions` `pp`) left join `user_page_permissions` `upp` on(`u`.`id` = `upp`.`user_id` and `pp`.`page_name` = `upp`.`page_name` and `pp`.`permission_key` = `upp`.`permission_key`)) left join `users` `granter` on(`upp`.`granted_by` = `granter`.`id`)) ORDER BY `u`.`id` ASC, `pp`.`page_name` ASC, `pp`.`permission_key` ASC ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activity_log`
--
ALTER TABLE `activity_log`
  ADD CONSTRAINT `fk_activity_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `antibiotic_reports`
--
ALTER TABLE `antibiotic_reports`
  ADD CONSTRAINT `antibiotic_reports_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `antibiotic_reports_ibfk_2` FOREIGN KEY (`reporting_pharmacist_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `antibiotic_reports_ibfk_3` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`),
  ADD CONSTRAINT `antibiotic_reports_ibfk_4` FOREIGN KEY (`antibiotic_id`) REFERENCES `antibiotics` (`id`);

--
-- Constraints for table `breakage_reports`
--
ALTER TABLE `breakage_reports`
  ADD CONSTRAINT `breakage_reports_ibfk_1` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `clinical_pharmacy_forms`
--
ALTER TABLE `clinical_pharmacy_forms`
  ADD CONSTRAINT `clinical_pharmacy_forms_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `custom_stats`
--
ALTER TABLE `custom_stats`
  ADD CONSTRAINT `custom_stats_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `daily_pharmacist_reports`
--
ALTER TABLE `daily_pharmacist_reports`
  ADD CONSTRAINT `daily_pharmacist_reports_ibfk_1` FOREIGN KEY (`pharmacist_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `expired_drugs_disposal`
--
ALTER TABLE `expired_drugs_disposal`
  ADD CONSTRAINT `expired_drugs_disposal_ibfk_1` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `export_logs`
--
ALTER TABLE `export_logs`
  ADD CONSTRAINT `export_logs_ibfk_1` FOREIGN KEY (`exported_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `icu_daily_reports`
--
ALTER TABLE `icu_daily_reports`
  ADD CONSTRAINT `icu_daily_reports_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `internal_reports`
--
ALTER TABLE `internal_reports`
  ADD CONSTRAINT `internal_reports_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `narcotic_disposal_reports`
--
ALTER TABLE `narcotic_disposal_reports`
  ADD CONSTRAINT `narcotic_disposal_reports_ibfk_1` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notification_settings`
--
ALTER TABLE `notification_settings`
  ADD CONSTRAINT `notification_settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `nursery_pediatric_reports`
--
ALTER TABLE `nursery_pediatric_reports`
  ADD CONSTRAINT `nursery_pediatric_reports_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `patient_medication_tracking`
--
ALTER TABLE `patient_medication_tracking`
  ADD CONSTRAINT `patient_medication_tracking_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `pharmacist_schedule`
--
ALTER TABLE `pharmacist_schedule`
  ADD CONSTRAINT `pharmacist_schedule_ibfk_1` FOREIGN KEY (`pharmacist_id`) REFERENCES `pharmacists` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `pharmacy_handover_forms`
--
ALTER TABLE `pharmacy_handover_forms`
  ADD CONSTRAINT `pharmacy_handover_forms_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `problem_definitions`
--
ALTER TABLE `problem_definitions`
  ADD CONSTRAINT `problem_definitions_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `antibiotic_reports` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `report_attachments`
--
ALTER TABLE `report_attachments`
  ADD CONSTRAINT `report_attachments_ibfk_1` FOREIGN KEY (`report_id`) REFERENCES `antibiotic_reports` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `report_attachments_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `simple_patient_monitoring`
--
ALTER TABLE `simple_patient_monitoring`
  ADD CONSTRAINT `simple_patient_monitoring_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `simple_patient_monitoring_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `surgery_antibiotics`
--
ALTER TABLE `surgery_antibiotics`
  ADD CONSTRAINT `surgery_antibiotics_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `surgery_antibiotics_log`
--
ALTER TABLE `surgery_antibiotics_log`
  ADD CONSTRAINT `surgery_antibiotics_log_ibfk_1` FOREIGN KEY (`antibiotic_id`) REFERENCES `antibiotics` (`id`),
  ADD CONSTRAINT `surgery_antibiotics_log_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `system_backups`
--
ALTER TABLE `system_backups`
  ADD CONSTRAINT `system_backups_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD CONSTRAINT `system_settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `user_page_permissions`
--
ALTER TABLE `user_page_permissions`
  ADD CONSTRAINT `user_page_permissions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_page_permissions_ibfk_2` FOREIGN KEY (`granted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
