-- DentalCare v0.5.9.8.7
-- Compact clinical encounter, structured disposition, examination states,
-- diagnosis catalogue, resource usage and source-traceable billing handoff.
-- Existing-table columns and indexes are added by System Setup so partial
-- upgrades remain repairable on MariaDB and MySQL installations.

CREATE TABLE IF NOT EXISTS `{{prefix}}diagnosis_catalog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(80) NULL,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(120) NULL,
  `default_status` ENUM('provisional','confirmed','chronic','under_observation','referred') NOT NULL DEFAULT 'provisional',
  `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_diagnosis_catalog_name` (`name`),
  KEY `idx_diagnosis_catalog_search` (`is_active`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `{{prefix}}diagnosis_catalog` (`code`,`name`,`category`,`default_status`,`created_at`) VALUES
('K02.9','Dental caries, unspecified','Caries','confirmed',NOW()),
('K04.0','Pulpitis','Endodontic','provisional',NOW()),
('K04.1','Necrosis of pulp','Endodontic','confirmed',NOW()),
('K04.4','Acute apical periodontitis of pulpal origin','Endodontic','provisional',NOW()),
('K04.5','Chronic apical periodontitis','Endodontic','chronic',NOW()),
('K05.1','Chronic gingivitis','Periodontal','chronic',NOW()),
('K05.3','Chronic periodontitis','Periodontal','chronic',NOW()),
('K08.8','Other specified disorders of teeth and supporting structures','General','provisional',NOW()),
('K12.1','Other forms of stomatitis','Soft tissue','provisional',NOW()),
('K10.2','Inflammatory conditions of jaws','Oral surgery','provisional',NOW()),
('S02.5','Fracture of tooth','Trauma','confirmed',NOW()),
(NULL,'Reversible pulpitis','Endodontic','provisional',NOW()),
(NULL,'Irreversible pulpitis','Endodontic','provisional',NOW()),
(NULL,'Periapical abscess','Endodontic','confirmed',NOW()),
(NULL,'Tooth sensitivity','General','under_observation',NOW()),
(NULL,'Impacted tooth','Oral surgery','confirmed',NOW()),
(NULL,'Temporomandibular joint disorder','TMJ','under_observation',NOW()),
(NULL,'Oral soft-tissue lesion','Soft tissue','provisional',NOW());

CREATE TABLE IF NOT EXISTS `{{prefix}}clinical_usage_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id` BIGINT UNSIGNED NOT NULL,
  `patient_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `inventory_item_id` BIGINT UNSIGNED NULL,
  `usage_type` ENUM('medication_administered','medicine_dispense','consumable','equipment','diagnostic_service') NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit` VARCHAR(40) NULL,
  `billing_mode` ENUM('included','bill_separately','track_only') NOT NULL DEFAULT 'track_only',
  `unit_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `line_total` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `fulfillment_status` ENUM('draft','requested','confirmed','cancelled') NOT NULL DEFAULT 'draft',
  `notes` VARCHAR(500) NULL,
  `recorded_by` BIGINT UNSIGNED NULL,
  `confirmed_by` BIGINT UNSIGNED NULL,
  `confirmed_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clinical_usage_encounter` (`encounter_id`,`fulfillment_status`),
  KEY `idx_clinical_usage_patient` (`patient_id`,`created_at`),
  KEY `idx_clinical_usage_inventory` (`inventory_item_id`,`fulfillment_status`),
  CONSTRAINT `{{prefix}}fk_usage_encounter` FOREIGN KEY (`encounter_id`) REFERENCES `{{prefix}}clinical_encounters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_usage_patient` FOREIGN KEY (`patient_id`) REFERENCES `{{prefix}}patients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `{{prefix}}fk_usage_inventory` FOREIGN KEY (`inventory_item_id`) REFERENCES `{{prefix}}inventory_items` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
