-- Pathology Management System - Final Setup (Consolidated Version)
-- Comprehensive schema including billing, insurance, groups, and settings.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET FOREIGN_KEY_CHECKS = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- 1. DROP EXISTING TABLES (Reverse Order of Dependencies)
DROP TABLE IF EXISTS `audit_logs`;
DROP TABLE IF EXISTS `test_results`;
DROP TABLE IF EXISTS `booking_tests`;
DROP TABLE IF EXISTS `billing_details`;
DROP TABLE IF EXISTS `bookings`;
DROP TABLE IF EXISTS `test_master`;
DROP TABLE IF EXISTS `test_groups`;
DROP TABLE IF EXISTS `patients`;
DROP TABLE IF EXISTS `collection_agents`;
DROP TABLE IF EXISTS `doctors`;
DROP TABLE IF EXISTS `insurance_companies`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `settings`;

-- 2. CREATE SCHEMA

-- Table: settings
CREATE TABLE `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;

-- Table: users
CREATE TABLE `users` (
  `user_id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin','lab','agent') NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: doctors
CREATE TABLE `doctors` (
  `doctor_id` INT AUTO_INCREMENT PRIMARY KEY,
  `doctor_name` VARCHAR(150) NOT NULL,
  `specialization` VARCHAR(150),
  `qualification` VARCHAR(200),
  `phone` VARCHAR(20),
  `email` VARCHAR(120),
  `clinic_name` VARCHAR(255),
  `registration_no` VARCHAR(100),
  `referral_commission_percentage` DECIMAL(5,2) DEFAULT 0.00,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: collection_agents
CREATE TABLE `collection_agents` (
  `agent_id` INT AUTO_INCREMENT PRIMARY KEY,
  `agent_name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(20),
  `email` VARCHAR(120),
  `commission_type` ENUM('percentage', 'fixed') DEFAULT 'percentage',
  `commission_value` DECIMAL(10,2) DEFAULT 0.00,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: insurance_companies
CREATE TABLE `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;

-- Table: test_groups
CREATE TABLE `test_groups` (
  `group_id` INT AUTO_INCREMENT PRIMARY KEY,
  `group_name` VARCHAR(100) NOT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: patients
CREATE TABLE `patients` (
  `patient_id` INT AUTO_INCREMENT PRIMARY KEY,
  `uhid` VARCHAR(30) UNIQUE NOT NULL,
  `patient_name` VARCHAR(150) NOT NULL,
  `gender` ENUM('male', 'female', 'other') NOT NULL,
  `dob` DATE,
  `age` INT,
  `phone` VARCHAR(20),
  `email` VARCHAR(120),
  `address` TEXT,
  `blood_group` VARCHAR(5),
  `referred_by_doctor_id` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`referred_by_doctor_id`) REFERENCES `doctors`(`doctor_id`) ON DELETE SET NULL,
  INDEX idx_uhid (uhid),
  INDEX idx_phone (phone),
  INDEX idx_name (patient_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: test_master
CREATE TABLE `test_master` (
  `test_id` INT AUTO_INCREMENT PRIMARY KEY,
  `group_id` INT,
  `test_name` VARCHAR(200) NOT NULL,
  `short_name` VARCHAR(50),
  `test_code` VARCHAR(50),
  `category` VARCHAR(100),
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `reference_ranges` TEXT,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  FOREIGN KEY (`group_id`) REFERENCES `test_groups`(`group_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: bookings
CREATE TABLE `bookings` (
  `booking_id` INT AUTO_INCREMENT PRIMARY KEY,
  `patient_id` INT NOT NULL,
  `doctor_id` INT,
  `agent_id` INT,
  `insurance_id` INT,
  `booking_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `total_amount` DECIMAL(10,2) DEFAULT 0.00,
  `paid_amount` DECIMAL(10,2) DEFAULT 0.00,
  `status` ENUM('Pending', 'In Lab', 'Completed', 'Cancelled') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`patient_id`) REFERENCES `patients`(`patient_id`) ON DELETE CASCADE,
  FOREIGN KEY (`doctor_id`) REFERENCES `doctors`(`doctor_id`) ON DELETE SET NULL,
  FOREIGN KEY (`agent_id`) REFERENCES `collection_agents`(`agent_id`) ON DELETE SET NULL,
  FOREIGN KEY (`insurance_id`) REFERENCES `insurance_companies`(`insurance_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: billing_details
CREATE TABLE `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;

-- Table: booking_tests
CREATE TABLE `booking_tests` (
  `booking_test_id` INT AUTO_INCREMENT PRIMARY KEY,
  `booking_id` INT NOT NULL,
  `test_id` INT NOT NULL,
  `row_price` DECIMAL(10,2) DEFAULT 0.00,
  `discount_type` ENUM('flat', 'percentage') DEFAULT 'flat',
  `discount_value` DECIMAL(10,2) DEFAULT 0.00,
  `net_amount` DECIMAL(10,2) DEFAULT 0.00,
  FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`booking_id`) ON DELETE CASCADE,
  FOREIGN KEY (`test_id`) REFERENCES `test_master`(`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: test_results
CREATE TABLE `test_results` (
  `result_id` INT AUTO_INCREMENT PRIMARY KEY,
  `booking_test_id` INT NOT NULL,
  `result_value` VARCHAR(100),
  `unit` VARCHAR(20),
  `is_abnormal` BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (`booking_test_id`) REFERENCES `booking_tests`(`booking_test_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: audit_logs
CREATE TABLE `audit_logs` (
  `log_id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `table_name` VARCHAR(100),
  `record_id` INT,
  `action_type` VARCHAR(20),
  `changed_field` VARCHAR(100),
  `old_value` TEXT,
  `new_value` TEXT,
  `changed_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. INSERT DUMMY DATA

-- Settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('lab_name', 'Aloka Polyclinic'),
('lab_address', 'Rasikganj, Bishnupur, Bankura'),
('lab_email', 'info@alokapolyclinic.com'),
('lab_phone', '9609828643, 9339946558'),
('lab_logo', 'lab_admin/Logo.jpg.jpeg');

-- Users (password: 'password123')
INSERT INTO `users` (`name`, `email`, `password`, `role`) VALUES
('Admin User', 'admin@hospital.com', '$2y$10$4gTlADWy9teYkMl9D1xx1eoddyNV8twKguzTdwMeeKkhWY6Di25.C', 'admin'),
('Lab Technician', 'lab@hospital.com', '$2y$10$4gTlADWy9teYkMl9D1xx1eoddyNV8twKguzTdwMeeKkhWY6Di25.C', 'lab'),
('Collection Agent', 'agent@hospital.com', '$2y$10$4gTlADWy9teYkMl9D1xx1eoddyNV8twKguzTdwMeeKkhWY6Di25.C', 'agent');

-- Doctors
INSERT INTO `doctors` (`doctor_name`, `specialization`, `qualification`, `phone`, `email`, `clinic_name`, `referral_commission_percentage`) VALUES
('Dr. Rajesh Khanna', 'Cardiology', 'MBBS, MD', '9876543210', 'rajesh@example.com', 'Khanna Heart Center', 15.00),
('Dr. Sneha Patil', 'Gynecology', 'MBBS, DGO', '9876543211', 'sneha@example.com', 'Lifeline Women Clinic', 10.00),
('Dr. Amit Verma', 'Orthopedics', 'MBBS, MS', '9876543212', 'amit@example.com', 'Orthofix Clinic', 12.50);

-- Collection Agents
INSERT INTO `collection_agents` (`agent_name`, `phone`, `email`, `commission_type`, `commission_value`) VALUES
('Rahul Sharma', '9000000001', 'rahul@example.com', 'percentage', 5.00),
('Anita Singh', '9000000002', 'anita@example.com', 'fixed', 50.00);

-- Insurance Companies
INSERT INTO `insurance_companies` (`company_name`, `contact_person`, `phone`) VALUES
('Star Health Insurance', 'Amit Sharma', '9876543220'),
('HDFC Ergo', 'Priya Verma', '9876543221'),
('Niva Bupa', 'Suresh Gupta', '9876543222');

-- Test Groups
INSERT INTO `test_groups` (`group_name`) VALUES
('Hematology'),
('Biochemistry'),
('Clinical Pathology'),
('Microbiology');

-- Test Master
INSERT INTO `test_master` (`group_id`, `test_name`, `short_name`, `category`, `price`, `reference_ranges`) VALUES
(1, 'Complete Blood Count', 'CBC', 'Hematology', 500.00, '{"Hb": "13-17", "WBC": "4k-11k"}'),
(2, 'Lipid Profile', 'LP', 'Biochemistry', 1200.00, '{"Chol": "<200", "HDL": ">40"}'),
(2, 'Liver Function Test', 'LFT', 'Biochemistry', 1500.00, '{"ALT": "7-55", "AST": "8-48"}');

-- Patients
INSERT INTO `patients` (`uhid`, `patient_name`, `gender`, `dob`, `age`, `phone`, `address`, `referred_by_doctor_id`) VALUES
('LAB-2026-00001', 'John Doe', 'male', '1980-05-15', 45, '9111111111', '123 Street, Mumbai', 1),
('LAB-2026-00002', 'Jane Smith', 'female', '1992-08-22', 33, '9111111112', '456 Avenue, Pune', 2);

-- Bookings & Billing
INSERT INTO `bookings` (`patient_id`, `doctor_id`, `agent_id`, `booking_date`, `total_amount`, `paid_amount`, `status`) VALUES
(1, 1, 1, NOW() - INTERVAL 2 HOUR, 1700.00, 1700.00, 'Completed'),
(2, 2, NULL, NOW() - INTERVAL 1 DAY, 2500.00, 1000.00, 'Pending');

INSERT INTO `billing_details` (`booking_id`, `subtotal`, `final_amount`, `advance_amount`, `due_amount`) VALUES
(1, 1700.00, 1700.00, 1700.00, 0.00),
(2, 2500.00, 2500.00, 1000.00, 1500.00);

-- Booking Tests
INSERT INTO `booking_tests` (`booking_id`, `test_id`, `row_price`, `net_amount`) VALUES
(1, 1, 500.00, 500.00), 
(1, 2, 1200.00, 1200.00),
(2, 3, 1500.00, 1500.00);

-- Test Results
INSERT INTO `test_results` (`booking_test_id`, `result_value`, `unit`, `is_abnormal`) VALUES
(1, '14.5', 'g/dL', 0), (2, '210', 'mg/dL', 1);

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;