-- DentalCare v0.5.9.8.5
-- Unlimited database-backed prescription medicine search/add and diagnostic review tasks.

CREATE TABLE IF NOT EXISTS `{{prefix}}medicine_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `catalog_key` CHAR(40) NOT NULL,
  `display_name` VARCHAR(190) NOT NULL,
  `generic_name` VARCHAR(190) NULL,
  `brand_name` VARCHAR(190) NULL,
  `form` VARCHAR(100) NULL,
  `strength` VARCHAR(80) NULL,
  `route` VARCHAR(80) NULL,
  `notes` TEXT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_medicine_catalog_key` (`catalog_key`),
  KEY `idx_medicine_catalog_search` (`is_active`,`display_name`),
  KEY `idx_medicine_catalog_generic` (`generic_name`,`is_active`),
  CONSTRAINT `{{prefix}}fk_medicine_catalog_user` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `{{prefix}}medicine_catalog`
(`catalog_key`,`display_name`,`generic_name`,`brand_name`,`form`,`strength`,`route`,`is_active`,`created_at`,`updated_at`) VALUES
('d3b64d00e561feb503a6f721445f7a85fcca62c4','Amoxicillin','Amoxicillin',NULL,'Capsule','500 mg','Oral',1,NOW(),NOW()),
('dd588d9b850df4c6d666f31f4ce769c579efb9ed','Amoxicillin + Clavulanic Acid','Amoxicillin / Clavulanate',NULL,'Tablet','625 mg','Oral',1,NOW(),NOW()),
('d88a900e5cec0e0891b103026a1062e9f7dcf7ac','Azithromycin','Azithromycin',NULL,'Tablet','500 mg','Oral',1,NOW(),NOW()),
('9fc6f83a939d17444c334cfb04e2dabc27769274','Clindamycin','Clindamycin',NULL,'Capsule','300 mg','Oral',1,NOW(),NOW()),
('af354bfa344a50ecd2d84662acd9cc797b967046','Metronidazole','Metronidazole',NULL,'Tablet','500 mg','Oral',1,NOW(),NOW()),
('b40d876d98cc0e9dd122b3e2d7346de89faf699e','Paracetamol','Paracetamol / Acetaminophen',NULL,'Tablet','500 mg','Oral',1,NOW(),NOW()),
('bbb01451e89c62bb54ad6af80e9150f62926f385','Ibuprofen','Ibuprofen',NULL,'Tablet','400 mg','Oral',1,NOW(),NOW()),
('a5965b923954a6f2f3947968446375e071c74444','Naproxen Sodium','Naproxen Sodium',NULL,'Tablet','550 mg','Oral',1,NOW(),NOW()),
('d937e96559a7d99a178e6bed2057ed30cdb75086','Mefenamic Acid','Mefenamic Acid',NULL,'Capsule','500 mg','Oral',1,NOW(),NOW()),
('5358a273c95603f346c4e082cdaf4c4fbf8a9c09','Celecoxib','Celecoxib',NULL,'Capsule','200 mg','Oral',1,NOW(),NOW()),
('d8f9af4a2b06feb64b0449f905892281a6cc4c7d','Chlorhexidine Gluconate','Chlorhexidine Gluconate',NULL,'Mouth rinse','0.12%','Oral rinse',1,NOW(),NOW()),
('227e28846b12515eb18ab5c6347db0299b38f09b','Benzydamine Hydrochloride','Benzydamine Hydrochloride',NULL,'Mouth rinse','0.15%','Oral rinse',1,NOW(),NOW());

CREATE TABLE IF NOT EXISTS `{{prefix}}diagnostic_review_tasks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `encounter_id` BIGINT UNSIGNED NULL,
  `appointment_id` BIGINT UNSIGNED NULL,
  `assigned_dentist_user_id` BIGINT UNSIGNED NOT NULL,
  `reason` TEXT NOT NULL,
  `priority` ENUM('routine','important','urgent') NOT NULL DEFAULT 'routine',
  `status` ENUM('pending','in_review','validated','closed','cancelled') NOT NULL DEFAULT 'pending',
  `source_role` VARCHAR(40) NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dentist_notes` TEXT NULL,
  `validated_by` BIGINT UNSIGNED NULL,
  `validated_at` DATETIME NULL,
  `closed_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_diag_review_dentist` (`assigned_dentist_user_id`,`status`,`priority`,`created_at`),
  KEY `idx_diag_review_patient` (`patient_id`,`status`,`created_at`),
  KEY `idx_diag_review_branch` (`branch_id`,`status`,`created_at`),
  CONSTRAINT `{{prefix}}fk_diag_review_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_diag_review_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_diag_review_appointment` FOREIGN KEY (`appointment_id`) REFERENCES `{{prefix}}appointments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_diag_review_dentist` FOREIGN KEY (`assigned_dentist_user_id`) REFERENCES `{{prefix}}users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `{{prefix}}fk_diag_review_creator` FOREIGN KEY (`created_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `{{prefix}}fk_diag_review_validator` FOREIGN KEY (`validated_by`) REFERENCES `{{prefix}}users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
