-- Database Updates for Lab Management System
-- This file is automatically applied by the system.
-- It uses IF NOT EXISTS for tables and ignores errors for columns if already present.

SET FOREIGN_KEY_CHECKS = 0;

-- 1. SETTINGS TABLE
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) UNIQUE NOT NULL,
    `setting_value` TEXT,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. TEST GROUPS
CREATE TABLE IF NOT EXISTS `test_groups` (
  `group_id` INT AUTO_INCREMENT PRIMARY KEY,
  `group_name` VARCHAR(100) NOT NULL,
  `dept_head_name` VARCHAR(100),
  `report_style` VARCHAR(50) DEFAULT 'Default',
  `sort_order` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2.1 TEST SUBGROUPS
CREATE TABLE IF NOT EXISTS `test_subgroups` (
  `subgroup_id` INT AUTO_INCREMENT PRIMARY KEY,
  `group_id` INT NOT NULL,
  `subgroup_name` VARCHAR(100) NOT NULL,
  `sort_order` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`group_id`) REFERENCES `test_groups`(`group_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. INSURANCE COMPANIES
CREATE TABLE IF NOT EXISTS `insurance_companies` (
  `insurance_id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(150) NOT NULL,
  `contact_person` VARCHAR(100),
  `phone` VARCHAR(20),
  `email` VARCHAR(120),
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. BILLING DETAILS
CREATE TABLE IF NOT EXISTS `billing_details` (
  `billing_id` INT AUTO_INCREMENT PRIMARY KEY,
  `booking_id` INT NOT NULL,
  `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `discount_type` ENUM('flat', 'percentage') DEFAULT 'flat',
  `discount_value` DECIMAL(10,2) DEFAULT 0.00,
  `total_discount` DECIMAL(10,2) DEFAULT 0.00,
  `tax_amount` DECIMAL(10,2) DEFAULT 0.00,
  `final_amount` DECIMAL(10,2) NOT NULL,
  `advance_amount` DECIMAL(10,2) DEFAULT 0.00,
  `due_amount` DECIMAL(10,2) DEFAULT 0.00,
  `payment_method` ENUM('cash', 'card', 'upi', 'insurance', 'other') DEFAULT 'cash',
  `remarks` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`booking_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. COLUMN UPDATES (Using MariaDB/MySQL compatible syntax)

-- Doctors: registration_no
ALTER TABLE `doctors` ADD COLUMN IF NOT EXISTS `registration_no` VARCHAR(100) AFTER `clinic_name`;

-- Test Master: group_id, subgroup_id, test_code
ALTER TABLE `test_master` ADD COLUMN IF NOT EXISTS `group_id` INT AFTER `test_id`;
ALTER TABLE `test_master` ADD COLUMN IF NOT EXISTS `subgroup_id` INT AFTER `group_id`;
ALTER TABLE `test_master` ADD COLUMN IF NOT EXISTS `test_code` VARCHAR(50) AFTER `short_name`;
ALTER TABLE `test_master` ADD FOREIGN KEY IF NOT EXISTS (`group_id`) REFERENCES `test_groups`(`group_id`) ON DELETE SET NULL;
ALTER TABLE `test_master` ADD FOREIGN KEY IF NOT EXISTS (`subgroup_id`) REFERENCES `test_subgroups`(`subgroup_id`) ON DELETE SET NULL;

-- Bookings: insurance_id
ALTER TABLE `bookings` ADD COLUMN IF NOT EXISTS `insurance_id` INT AFTER `agent_id`;
ALTER TABLE `bookings` ADD FOREIGN KEY IF NOT EXISTS (`insurance_id`) REFERENCES `insurance_companies`(`insurance_id`) ON DELETE SET NULL;

-- Booking Tests: row-level discount columns
ALTER TABLE `booking_tests` ADD COLUMN IF NOT EXISTS `row_price` DECIMAL(10,2) DEFAULT 0.00;
ALTER TABLE `booking_tests` ADD COLUMN IF NOT EXISTS `discount_type` ENUM('flat', 'percentage') DEFAULT 'flat';
ALTER TABLE `booking_tests` ADD COLUMN IF NOT EXISTS `discount_value` DECIMAL(10,2) DEFAULT 0.00;
ALTER TABLE `booking_tests` ADD COLUMN IF NOT EXISTS `net_amount` DECIMAL(10,2) DEFAULT 0.00;

-- 6. DUMMY DATA (Optional - INSERT IGNORE to prevent duplicates)
INSERT IGNORE INTO `test_groups` (`group_id`, `group_name`) VALUES 
(1, 'Hematology'), (2, 'Biochemistry'), (3, 'Clinical Pathology'), (4, 'Microbiology');

INSERT IGNORE INTO `insurance_companies` (`insurance_id`, `company_name`) VALUES 
(1, 'Star Health Insurance'), (2, 'HDFC Ergo'), (3, 'Niva Bupa');

INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES 
('lab_name', 'MedicoLab'),
('lab_address', '123 HEALTHCARE TOWER, MEDICAL ENCLAVE, CITY CENTER'),
('lab_email', 'info@medicolab.com'),
('lab_phone', '+91 98765 43210');

SET FOREIGN_KEY_CHECKS = 1;