DROP TABLE IF EXISTS `clinic_roles`;
CREATE TABLE `clinic_roles` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
   `display_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
   `description` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `roles_name_unique` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `calendar`;
CREATE TABLE `calendar` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `Name` varchar(50) NOT NULL,
   `Color` varchar(50) DEFAULT NULL,
   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`ID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(50) NOT NULL,
   `password` varchar(50) NOT NULL,
   `role` varchar(50) NOT NULL,
   `clinic_role_id` int(10) unsigned DEFAULT NULL,
   `active` varchar(50) NOT NULL DEFAULT '1',
   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`ID`),
   UNIQUE KEY `username` (`username`),
   KEY `FK_account_clinic_roles` (`clinic_role_id`),
   CONSTRAINT `FK_account_clinic_roles` FOREIGN KEY (`clinic_role_id`) REFERENCES `clinic_roles` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `doctor`;
CREATE TABLE `doctor` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `SkillsDescription` varchar(50) DEFAULT NULL,
   `YearsOfExperience` int(11) DEFAULT NULL,
   `FirstName` varchar(50) NOT NULL,
   `LastName` varchar(50) DEFAULT NULL,
   `CardNum` varchar(50) DEFAULT NULL,
   `Birthdate` varchar(50) DEFAULT NULL,
   `Gender` varchar(50) DEFAULT NULL,
   `MaritalStatus` varchar(50) DEFAULT NULL,
   `Address` varchar(200) DEFAULT NULL,
   `HomeTel` varchar(50) DEFAULT NULL,
   `Mobile` varchar(50) NOT NULL,
   `AccountID` int(11) DEFAULT NULL,
   `InService` varchar(50) NOT NULL DEFAULT '1',
   `DoctorType` varchar(11) DEFAULT NULL,
   `CalendarID` int(11) DEFAULT NULL,
   `Participation` varchar(10) DEFAULT '0',
   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`ID`),
   KEY `FK_doctor_account` (`AccountID`),
   KEY `FK_doctor_calendar` (`CalendarID`),
   KEY `idx_doctor_CalendarID` (`CalendarID`),
   KEY `idx_doctor_AccountID` (`AccountID`),
   CONSTRAINT `FK_doctor_account` FOREIGN KEY (`AccountID`) REFERENCES `account` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
   CONSTRAINT `FK_doctor_calendar` FOREIGN KEY (`CalendarID`) REFERENCES `calendar` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `toothtreatments`;
CREATE TABLE `toothtreatments` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `Teeth` varchar(300) NOT NULL,
   `TreatmentDate` datetime NOT NULL,
   `PatientID` int(11) NOT NULL,
   `Amount` float NOT NULL DEFAULT '0',
   `DoctorID` int(11) NOT NULL,
   `Surface` varchar(50) DEFAULT NULL,
   `ProcedureID` int(11) NOT NULL,
   `Notes` varchar(2000) DEFAULT NULL,
   `InsuranceCheck` int(11) DEFAULT NULL,
   `InsuranceCode` varchar(50) DEFAULT NULL,
   `PlanID` int(11) NOT NULL DEFAULT '0',
   `ShadeID` int(11) NOT NULL DEFAULT '0',
   `VendorID` int(11) NOT NULL 
   DEFAULT '0',
   PRIMARY KEY (`ID`),
   KEY `Index 2` (`PatientID`),
   KEY `Index 3` (`PlanID`),
   KEY `Index 4` (`ProcedureID`),
   KEY `FK_toothtreatments_doctor` (`DoctorID`),
   KEY `idx_toothtreatments_PatientID` (`PatientID`),
   KEY `idx_toothtreatments_DoctorID` (`DoctorID`),
   KEY `idx_toothtreatments_ProcedureID` (`ProcedureID`),
   KEY `idx_toothtreatments_PlanID` (`PlanID`),
   CONSTRAINT `FK_toothtreatments_doctor` FOREIGN KEY (`DoctorID`) REFERENCES `doctor` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `treatmentplan`;
 CREATE TABLE `treatmentplan` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `Teeth` varchar(300) NOT NULL,
   `TreatmentDate` datetime DEFAULT NULL,
   `PatientID` int(11) NOT NULL,
   `Amount` float NOT NULL DEFAULT '0',
   `DoctorID` int(11) DEFAULT NULL,
   `Surface` varchar(50) DEFAULT NULL,
   `ProcedureID` int(11) NOT NULL,
   `Notes` varchar(2000) DEFAULT NULL,
   `Status` varchar(15) NOT NULL,
   `ShadeID` int(11) DEFAULT '0',
   `VendorID` int(11) DEFAULT NULL,
   `InsuranceCheck` int(11) DEFAULT NULL,
   `InsuranceCode` varchar(50) DEFAULT NULL,
   `created_at` timestamp NULL DEFAULT NULL,
   `updated_at` timestamp NULL DEFAULT NULL,
   `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`ID`),
   KEY `Index 2` (`PatientID`),
   KEY `Index 3` (`ProcedureID`),
   KEY `FK_treatmentplan_doctor` (`DoctorID`),
   KEY `idx_treatmentplan_PatientID` (`PatientID`),
   KEY `idx_treatmentplan_DoctorID` (`DoctorID`),
   KEY `idx_treatmentplan_ProcedureID` (`ProcedureID`),
   CONSTRAINT `FK_treatmentplan_doctor` FOREIGN KEY (`DoctorID`) REFERENCES `doctor` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 

 DROP TABLE IF EXISTS `payment`;
 CREATE TABLE `payment` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `PatientID` int(11) NOT NULL,
   `Paid` float NOT NULL DEFAULT '0',
   `Date` datetime NOT NULL,
   `DoctorID` int(11) NOT NULL,
   `PaymentType` varchar(10) DEFAULT NULL,
   PRIMARY KEY (`ID`),
   KEY `Index 2` (`PatientID`),
   KEY `Index 3` (`DoctorID`),
   KEY `idx_payment_PatientID` (`PatientID`),
   KEY `idx_payment_DoctorID` (`DoctorID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `visa`;
 CREATE TABLE `visa` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `PaymentID` int(11) NOT NULL,
   `VisaOwner` varchar(200) NOT NULL,
   `VisaNumber` varchar(200) NOT NULL,
   PRIMARY KEY (`ID`),
   UNIQUE KEY `PaymentID` (`PaymentID`),
   KEY `idx_visa_PaymentID` (`PaymentID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `cheque`;
 CREATE TABLE `cheque` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `Number` varchar(100) NOT NULL,
   `BankName` varchar(100) NOT NULL,
   `ValueDate` varchar(50) NOT NULL,
   `DraweeName` varchar(100) NOT NULL,
   `DraweeAddress` varchar(100) NOT NULL,
   `PaymentID` int(11) NOT NULL,
   PRIMARY KEY (`ID`),
   KEY `FK_cheque_payment` (`PaymentID`),
   KEY `idx_cheque_PaymentID` (`PaymentID`),
   CONSTRAINT `FK_cheque_payment` FOREIGN KEY (`PaymentID`) REFERENCES `payment` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP TABLE IF EXISTS `patient`;
 CREATE TABLE `patient` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `FirstName` varchar(50) NOT NULL,
   `LastName` varchar(50) NOT NULL,
   `CardNum` varchar(50) DEFAULT NULL,
   `BirthDate` varchar(50) DEFAULT NULL,
   `Gender` varchar(50) DEFAULT NULL,
   `MaritalStatus` varchar(10) DEFAULT NULL,
   `Address` varchar(100) DEFAULT NULL,
   `Occupation` varchar(50) DEFAULT NULL,
   `WorkingPlace` varchar(50) DEFAULT NULL,
   `HomeTel` varchar(50) DEFAULT NULL,
   `WorkTel` varchar(50) DEFAULT NULL,
   `Mobile` varchar(50) NOT NULL,
   `DoctorID` varchar(50) DEFAULT NULL,
   `DoctorName` varchar(500) DEFAULT NULL,
   `NextAppointment` timestamp NULL DEFAULT NULL,
   `DateInserted` date DEFAULT NULL,
   `live` varchar(50) NOT NULL DEFAULT '1',
   `TotalBalance` float NOT NULL DEFAULT '0',
   `ImageFileName` varchar(300) DEFAULT NULL,
   `Email` varchar(100) DEFAULT NULL,
   `Notes` varchar(2000) DEFAULT NULL,
   `IsChild` int(11) NOT NULL,
   `FatherName` varchar(100) DEFAULT NULL,
   `FatherMobile` varchar(100) DEFAULT NULL,
   `MotherName` varchar(100) DEFAULT NULL,
   `MotherMobile` varchar(100) DEFAULT NULL,
   `ChildWeight` varchar(10) DEFAULT NULL,
   `InsuranceProvider` int(11) DEFAULT '0',
   `InsuranceNumber` varchar(50) DEFAULT NULL,
   `insurance_plan_id` int(11) DEFAULT NULL,
   `responsable` int(11) DEFAULT '0',
   `type` int(2) NOT NULL DEFAULT '1',
   `paper_file_number` varchar(200) DEFAULT '-',
   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `deleted_at` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`ID`),
   KEY `Index 2` (`InsuranceProvider`),
   KEY `Index 3` (`responsable`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
  DROP TABLE IF EXISTS `discount`;
 CREATE TABLE `discount` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `PatientID` int(11) NOT NULL,
   `Amount` float NOT NULL,
   `Date` datetime NOT NULL,
   `Description` varchar(1000) DEFAULT NULL,
   PRIMARY KEY (`ID`),
   KEY `FK_discount_patient` (`PatientID`),
   KEY `idx_discount_PatientID` (`PatientID`),
   CONSTRAINT `FK_discount_patient` FOREIGN KEY (`PatientID`) REFERENCES `patient` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;