-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               8.0.30 - MySQL Community Server - GPL
-- Server OS:                    Win64
-- HeidiSQL Version:             12.2.0.6576
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Dumping structure for table clinics.assistants
CREATE TABLE IF NOT EXISTS `assistants` (
  `id` int NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `position` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `status` enum('active','deleted','suspended') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `reason_for_status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.assistants: ~0 rows (approximately)

-- Dumping structure for table clinics.clinicassistants
CREATE TABLE IF NOT EXISTS `clinicassistants` (
  `id` int NOT NULL,
  `assistant_id` int DEFAULT NULL,
  `clinic_id` int DEFAULT NULL,
  `position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `position_type` enum('owner','doctor','reception','reservation','operation','laboratory','other') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other',
  PRIMARY KEY (`id`),
  KEY `assistantForClinic` (`assistant_id`),
  KEY `clinicForAssistant` (`clinic_id`),
  CONSTRAINT `assistantForClinic` FOREIGN KEY (`assistant_id`) REFERENCES `assistants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `clinicForAssistant` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.clinicassistants: ~0 rows (approximately)

-- Dumping structure for table clinics.clinicphotos
CREATE TABLE IF NOT EXISTS `clinicphotos` (
  `id` int NOT NULL,
  `file_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` enum('image','video') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `clinic_id` int DEFAULT NULL,
  `added_by_assistant_id` int DEFAULT NULL,
  `added_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clinicPhoto` (`clinic_id`),
  KEY `assistantPhoto` (`added_by_assistant_id`),
  CONSTRAINT `assistantPhoto` FOREIGN KEY (`added_by_assistant_id`) REFERENCES `assistants` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `clinicPhoto` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.clinicphotos: ~0 rows (approximately)

-- Dumping structure for table clinics.clinics
CREATE TABLE IF NOT EXISTS `clinics` (
  `id` int NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `logo` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `schedule` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `facebook` text COLLATE utf8mb4_unicode_ci,
  `domain` text COLLATE utf8mb4_unicode_ci,
  `doctor_id` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `clinicDoctor` (`doctor_id`),
  CONSTRAINT `clinicDoctor` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.clinics: ~0 rows (approximately)

-- Dumping structure for table clinics.clinicservices
CREATE TABLE IF NOT EXISTS `clinicservices` (
  `id` int NOT NULL,
  `service` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` int DEFAULT NULL,
  `speciality_id` int DEFAULT NULL,
  `clinic_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `specialityService` (`speciality_id`),
  KEY `clinicService` (`clinic_id`),
  CONSTRAINT `clinicService` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `specialityService` FOREIGN KEY (`speciality_id`) REFERENCES `specialities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.clinicservices: ~0 rows (approximately)

-- Dumping structure for table clinics.clinicsittings
CREATE TABLE IF NOT EXISTS `clinicsittings` (
  `entry_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entry_value` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `clinic_id` int NOT NULL,
  `assistant_id` int DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`entry_name`,`clinic_id`),
  KEY `clinic` (`clinic_id`),
  KEY `sittingsAssistant` (`assistant_id`),
  CONSTRAINT `clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `sittingsAssistant` FOREIGN KEY (`assistant_id`) REFERENCES `assistants` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.clinicsittings: ~0 rows (approximately)

-- Dumping structure for table clinics.clinictimeslots
CREATE TABLE IF NOT EXISTS `clinictimeslots` (
  `id` int NOT NULL,
  `date` int DEFAULT NULL,
  `starttime` int DEFAULT NULL,
  `endtime` int DEFAULT NULL,
  `status` enum('available','taken') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `clinic_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clinicslots` (`clinic_id`),
  CONSTRAINT `clinicslots` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.clinictimeslots: ~0 rows (approximately)

-- Dumping structure for table clinics.doctors
CREATE TABLE IF NOT EXISTS `doctors` (
  `id` int NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` enum('m','f') COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `commercial_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Example: Dr.Amr Medical Center, Dr. Amr group, Will be used as the name for package subscription',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.doctors: ~0 rows (approximately)

-- Dumping structure for table clinics.doctorswebsitedatatmp
CREATE TABLE IF NOT EXISTS `doctorswebsitedatatmp` (
  `basic_info` int DEFAULT NULL,
  `about_me` int DEFAULT NULL,
  `clinic_info` int DEFAULT NULL,
  `contact_details` int DEFAULT NULL,
  `pricing_services` int DEFAULT NULL,
  `education` int DEFAULT NULL,
  `experience` int DEFAULT NULL,
  `awards` int DEFAULT NULL,
  `scientific_articles` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.doctorswebsitedatatmp: ~0 rows (approximately)

-- Dumping structure for table clinics.messagestmp
CREATE TABLE IF NOT EXISTS `messagestmp` (
  `id` int DEFAULT NULL,
  `from` int DEFAULT NULL,
  `to` int DEFAULT NULL,
  `message` int DEFAULT NULL,
  `parent` int DEFAULT NULL,
  `attachments` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.messagestmp: ~0 rows (approximately)

-- Dumping structure for table clinics.packages
CREATE TABLE IF NOT EXISTS `packages` (
  `id` int NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` int NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `durationindays` int DEFAULT '7',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.packages: ~0 rows (approximately)

-- Dumping structure for table clinics.patients
CREATE TABLE IF NOT EXISTS `patients` (
  `id` int NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int NOT NULL,
  `gender` enum('m','f') COLLATE utf8mb4_unicode_ci NOT NULL,
  `governorate` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `smoker` enum('y','n','other') COLLATE utf8mb4_unicode_ci DEFAULT 'other',
  `alcoholic` enum('y','n','other') COLLATE utf8mb4_unicode_ci DEFAULT 'other',
  `chronic_diseases` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `blood_group` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `job` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `added_by` enum('clinic','himself','other') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'other',
  `assistant_id` int DEFAULT '0',
  `status` enum('active','deleted','suspended') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `reason_for_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `assistantWhoAdded` (`assistant_id`),
  CONSTRAINT `assistantWhoAdded` FOREIGN KEY (`assistant_id`) REFERENCES `assistants` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.patients: ~0 rows (approximately)

-- Dumping structure for table clinics.patientsmedicaldocuments
CREATE TABLE IF NOT EXISTS `patientsmedicaldocuments` (
  `id` int NOT NULL,
  `file_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_type` enum('analysis','xray','report','other') COLLATE utf8mb4_unicode_ci DEFAULT 'other',
  `date` datetime DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `patient_id` int DEFAULT NULL,
  `visit_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patientDocument` (`patient_id`),
  KEY `visitDocument` (`visit_id`),
  CONSTRAINT `patientDocument` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `visitDocument` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.patientsmedicaldocuments: ~0 rows (approximately)

-- Dumping structure for table clinics.renewals
CREATE TABLE IF NOT EXISTS `renewals` (
  `id` int NOT NULL,
  `doctor_id` int NOT NULL,
  `package_id` int NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `paidamount` int NOT NULL,
  `paid_at` datetime NOT NULL,
  `paid_by_assistant_id` int NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `paymentAssistant` (`paid_by_assistant_id`),
  KEY `package` (`package_id`),
  KEY `clinicPayment` (`doctor_id`) USING BTREE,
  CONSTRAINT `FK_clinicsrenewals_doctors` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `paymentAssistant` FOREIGN KEY (`paid_by_assistant_id`) REFERENCES `assistants` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.renewals: ~0 rows (approximately)

-- Dumping structure for table clinics.reviews
CREATE TABLE IF NOT EXISTS `reviews` (
  `id` int NOT NULL,
  `degree` enum('1','2','3','4','5') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '1',
  `comment` text COLLATE utf8mb4_unicode_ci,
  `visit_id` int DEFAULT NULL,
  `patient_id` int DEFAULT NULL,
  `clinic_id` int DEFAULT NULL,
  `doctor_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `visitreview` (`visit_id`),
  CONSTRAINT `visitreview` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.reviews: ~0 rows (approximately)

-- Dumping structure for table clinics.specialities
CREATE TABLE IF NOT EXISTS `specialities` (
  `id` int NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `icon` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `parent_speciality_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parentSpeciality` (`parent_speciality_id`),
  CONSTRAINT `parentSpeciality` FOREIGN KEY (`parent_speciality_id`) REFERENCES `specialities` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.specialities: ~0 rows (approximately)

-- Dumping structure for table clinics.visits
CREATE TABLE IF NOT EXISTS `visits` (
  `id` int NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `diagnosis` text COLLATE utf8mb4_unicode_ci,
  `prescription` text COLLATE utf8mb4_unicode_ci,
  `to_be_followed_on` datetime DEFAULT NULL,
  `status` enum('confirmed','completed','postponed','cancelledBypatient','cencelledByClinic','other') COLLATE utf8mb4_unicode_ci DEFAULT 'other',
  `patient_id` int NOT NULL,
  `clinic_id` int NOT NULL,
  `parent_visit_id` int DEFAULT '0',
  `assistant_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patientVisit` (`patient_id`),
  KEY `assistantVisit` (`assistant_id`),
  KEY `parentVisit` (`parent_visit_id`),
  KEY `clinicVisit` (`clinic_id`),
  CONSTRAINT `assistantVisit` FOREIGN KEY (`assistant_id`) REFERENCES `assistants` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `clinicVisit` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `parentVisit` FOREIGN KEY (`parent_visit_id`) REFERENCES `visits` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `patientVisit` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table clinics.visits: ~0 rows (approximately)

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
