-- ============================================================================
-- FIX: "Unknown column 'created_by_type' in 'field list'" on lab_orders
-- ============================================================================
-- Run this ONCE on your LIVE database (phpMyAdmin -> SQL tab -> paste -> Go).
--
-- It is 100% SAFE to run even if some/all of these tables or columns
-- already exist:
--   - Tables are created with CREATE TABLE IF NOT EXISTS (no data loss).
--   - Columns are added only if missing, using a small helper procedure
--     (no duplicate-column errors, no data loss).
--
-- It does NOT delete or modify any existing row in any table.
-- ============================================================================

-- 1) Make sure the 5 lab tables exist (created only if missing)
CREATE TABLE IF NOT EXISTS `lab_tests` (
  `lab_test_id` int(11) NOT NULL AUTO_INCREMENT,
  `test_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `test_name` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `sample_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `turnaround_time` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `collection_instructions` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`lab_test_id`),
  UNIQUE KEY `test_code` (`test_code`),
  KEY `department_id` (`department_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_test_parameters` (
  `parameter_id` int(11) NOT NULL AUTO_INCREMENT,
  `lab_test_id` int(11) NOT NULL,
  `parameter_name` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `result_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `unit` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `reference_range` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `critical_low` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `critical_high` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  PRIMARY KEY (`parameter_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_orders` (
  `lab_order_id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` int(11) NOT NULL,
  `doctor_id` int(11) DEFAULT NULL,
  `clinical_diagnosis` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `reason_for_investigation` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `doctor_notes` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `priority` enum('Routine','Urgent','STAT') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Routine',
  `status` enum('Ordered','Processing','Completed','Cancelled') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Ordered',
  `created_by_type` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `created_by_id` int(11) NOT NULL,
  `cancel_reason` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`lab_order_id`),
  KEY `patient_id` (`patient_id`),
  KEY `doctor_id` (`doctor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_order_items` (
  `order_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `lab_order_id` int(11) NOT NULL,
  `lab_test_id` int(11) NOT NULL,
  `clinical_notes` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('Ordered','Sample Collected','Sample Received','Rejected','Completed','Cancelled') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Ordered',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`order_item_id`),
  KEY `lab_order_id` (`lab_order_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `lab_samples` (
  `sample_pk` int(11) NOT NULL AUTO_INCREMENT,
  `order_item_id` int(11) NOT NULL,
  `lab_order_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `sample_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `sample_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `collection_date` date NOT NULL,
  `collection_time` time NOT NULL,
  `collected_by` int(11) NOT NULL,
  `collection_location` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `collection_remarks` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('Collected','Received','Rejected') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Collected',
  `received_date` date DEFAULT NULL,
  `received_time` time DEFAULT NULL,
  `received_by` int(11) DEFAULT NULL,
  `sample_condition` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `rejection_reason` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `receiving_remarks` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`sample_pk`),
  KEY `order_item_id` (`order_item_id`),
  KEY `lab_order_id` (`lab_order_id`),
  KEY `patient_id` (`patient_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 2) Helper procedure: add a column only if it doesn't already exist yet.
--    (This is what makes the script safe to re-run / run on a table that
--    already partially exists on your live server.)
DROP PROCEDURE IF EXISTS hmsc_add_column_if_missing;

DELIMITER $$
CREATE PROCEDURE hmsc_add_column_if_missing(
  IN p_table VARCHAR(100),
  IN p_column VARCHAR(100),
  IN p_definition VARCHAR(255)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column
  ) THEN
    SET @hmsc_ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE hmsc_stmt FROM @hmsc_ddl;
    EXECUTE hmsc_stmt;
    DEALLOCATE PREPARE hmsc_stmt;
  END IF;
END$$
DELIMITER ;

-- 3) lab_orders — add any column that might be missing on the live table
CALL hmsc_add_column_if_missing('lab_orders', 'doctor_id', 'INT(11) DEFAULT NULL');
CALL hmsc_add_column_if_missing('lab_orders', 'clinical_diagnosis', 'LONGTEXT NOT NULL');
CALL hmsc_add_column_if_missing('lab_orders', 'reason_for_investigation', 'LONGTEXT NOT NULL');
CALL hmsc_add_column_if_missing('lab_orders', 'doctor_notes', 'LONGTEXT NOT NULL');
CALL hmsc_add_column_if_missing('lab_orders', 'priority', "ENUM('Routine','Urgent','STAT') NOT NULL DEFAULT 'Routine'");
CALL hmsc_add_column_if_missing('lab_orders', 'status', "ENUM('Ordered','Processing','Completed','Cancelled') NOT NULL DEFAULT 'Ordered'");
CALL hmsc_add_column_if_missing('lab_orders', 'created_by_type', 'VARCHAR(50) NOT NULL DEFAULT \'admin\'');
CALL hmsc_add_column_if_missing('lab_orders', 'created_by_id', 'INT(11) NOT NULL DEFAULT 0');
CALL hmsc_add_column_if_missing('lab_orders', 'cancel_reason', 'LONGTEXT NULL');
CALL hmsc_add_column_if_missing('lab_orders', 'created_at', 'DATETIME NULL');
CALL hmsc_add_column_if_missing('lab_orders', 'updated_at', 'DATETIME NULL');

-- 4) lab_order_items — add any column that might be missing
CALL hmsc_add_column_if_missing('lab_order_items', 'clinical_notes', 'LONGTEXT NOT NULL');
CALL hmsc_add_column_if_missing('lab_order_items', 'status', "ENUM('Ordered','Sample Collected','Sample Received','Rejected','Completed','Cancelled') NOT NULL DEFAULT 'Ordered'");
CALL hmsc_add_column_if_missing('lab_order_items', 'created_at', 'DATETIME NULL');
CALL hmsc_add_column_if_missing('lab_order_items', 'updated_at', 'DATETIME NULL');

-- 5) lab_samples — add any column that might be missing
CALL hmsc_add_column_if_missing('lab_samples', 'sample_code', 'VARCHAR(50) DEFAULT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'collection_location', 'VARCHAR(150) NOT NULL DEFAULT \'\'');
CALL hmsc_add_column_if_missing('lab_samples', 'collection_remarks', 'LONGTEXT NOT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'received_date', 'DATE DEFAULT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'received_time', 'TIME DEFAULT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'received_by', 'INT(11) DEFAULT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'sample_condition', 'VARCHAR(50) DEFAULT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'rejection_reason', 'LONGTEXT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'receiving_remarks', 'LONGTEXT NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'created_at', 'DATETIME NULL');
CALL hmsc_add_column_if_missing('lab_samples', 'updated_at', 'DATETIME NULL');

-- 6) Clean up the helper procedure (no longer needed once the ALTERs are done)
DROP PROCEDURE IF EXISTS hmsc_add_column_if_missing;

-- Done. Reload the "Assign Test" page on your live site — the error should
-- be gone and the lab order should now be created successfully.