-- Update script for Pathology Lab Masters

SET FOREIGN_KEY_CHECKS = 0;

-- 1. Create test_groups table
CREATE TABLE IF NOT EXISTS `test_groups` (
  `group_id` INT AUTO_INCREMENT PRIMARY KEY,
  `group_name` VARCHAR(100) NOT NULL UNIQUE,
  `dept_head_name` VARCHAR(150),
  `report_style` VARCHAR(50), -- Color/Header selection
  `sort_order` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Update test_master table
-- First check if columns exist before adding them to avoid errors if script is re-run
-- (Assuming standard ALTER TABLE for simplicity here, but in production we'd use procedure guards)

ALTER TABLE `test_master` 
ADD COLUMN IF NOT EXISTS `group_id` INT,
ADD COLUMN IF NOT EXISTS `test_code` VARCHAR(50) UNIQUE AFTER `test_name`,
ADD COLUMN IF NOT EXISTS `sample_type` VARCHAR(100),
ADD COLUMN IF NOT EXISTS `container_type` VARCHAR(100),
ADD COLUMN IF NOT EXISTS `tat_hours` INT,
ADD COLUMN IF NOT EXISTS `patient_instruction` TEXT,
ADD COLUMN IF NOT EXISTS `base_price` DECIMAL(10,2) DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS `offer_price` DECIMAL(10,2) DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS `max_discount` DECIMAL(10,2) DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS `doctor_commission_type` ENUM('fixed', 'percentage') DEFAULT 'percentage',
ADD COLUMN IF NOT EXISTS `doctor_commission_value` DECIMAL(10,2) DEFAULT 0.00,
ADD COLUMN IF NOT EXISTS `agent_commission_type` ENUM('fixed', 'percentage') DEFAULT 'percentage',
ADD COLUMN IF NOT EXISTS `agent_commission_value` DECIMAL(10,2) DEFAULT 0.00;

-- Add foreign key for group_id
-- (Wrapped in TRY/CATCH equivalent or just ignore if already exists)
-- ALTER TABLE `test_master` ADD CONSTRAINT `fk_test_group` FOREIGN KEY (`group_id`) REFERENCES `test_groups`(`group_id`) ON DELETE SET NULL;

-- 3. Create test_components table
CREATE TABLE IF NOT EXISTS `test_components` (
  `component_id` INT AUTO_INCREMENT PRIMARY KEY,
  `test_id` INT NOT NULL,
  `component_name` VARCHAR(150) NOT NULL,
  `unit` VARCHAR(50),
  `result_type` ENUM('numeric', 'descriptive') DEFAULT 'numeric',
  `formula` TEXT, -- Basic arithmetic e.g., {A} + {B}
  `critical_low` DECIMAL(10,4),
  `critical_high` DECIMAL(10,4),
  `sort_order` INT DEFAULT 0,
  FOREIGN KEY (`test_id`) REFERENCES `test_master`(`test_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Create component_reference_ranges table
CREATE TABLE IF NOT EXISTS `component_reference_ranges` (
  `range_id` INT AUTO_INCREMENT PRIMARY KEY,
  `component_id` INT NOT NULL,
  `gender` ENUM('all', 'male', 'female', 'other') DEFAULT 'all',
  `age_min_days` INT DEFAULT 0, 
  `age_max_days` INT DEFAULT 36500, -- ~100 years
  `ref_min` DECIMAL(10,4),
  `ref_max` DECIMAL(10,4),
  `description` TEXT, -- For descriptive results or abnormal flags
  FOREIGN KEY (`component_id`) REFERENCES `test_components`(`component_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;