-- =====================================================================
-- STEP 4 PART 1 -- COMPLETE HOSPITAL CHARGE & PRICING MASTER
-- =====================================================================
-- Run this AFTER hmsci.sql + all prior upgrade files (hospital_master_setup_upgrade,
-- reception_billing_ward_master, step2_*, step3_*, tax_invoice_upgrade, etc).
--
-- WHAT THIS FILE DOES NOT TOUCH (already correct -- reused, not rebuilt):
--   ward_pricing        -- Ward Master + Ward Pricing (unchanged, only gains tax_id/effective_from)
--   lab_tests            -- Laboratory Test Master + Test Pricing (unchanged, only gains tax_id/emergency_price)
--   lab_test_parameters  -- untouched
--   medicine              -- Pharmacy Master (unchanged, only gains tax_id)
--   medicine_category     -- untouched
--   bed                    -- Bed Master (unchanged; bed-level charge continues to come from
--                             ward_pricing via bed.type = ward_pricing.ward_type, e.g. an
--                             'ICU' or 'HDU' row added to ward_pricing -- see note in section 6)
--
-- WHAT THIS FILE ADDS (genuinely missing, confirmed absent from hmsci.sql / all upgrade files):
--   1. tax_master                 -- Y. Tax
--   2. discount_master             -- X. Discount Rules
--   3. doctor_charges               -- B. Doctor Consultation Charges (doctor table currently
--                                     has NO fee column at all -- consultation fee is 100% hard-coded
--                                     nowhere in the schema today)
--   4. room_pricing                  -- E. Room Charges (distinct from Ward -- confirmed absent;
--                                     see navigation.php comment: "A distinct Room level does not
--                                     exist anywhere in the current schema")
--   5. procedure_master               -- K. Procedure Charges + L. OT/Surgery Charges (ONE shared
--                                     structure per the brief's explicit instruction not to build
--                                     separate duplicate Procedure and OT pricing systems)
--   6. service_charge_master           -- shared reusable structure for the simple name+price
--                                     categories that do not need their own domain master:
--                                     OPD Registration/Consultation, Nursing, Emergency,
--                                     Admission, ICU/HDU service items, Medical Equipment,
--                                     Oxygen/Medical Gas, Blood Bank services, Ambulance,
--                                     Home Care, Certificates/Documents. A `category` column
--                                     distinguishes them for filtering/reporting -- this is NOT
--                                     "everything in one table": Lab, Pharmacy, Ward, Room, Bed
--                                     and Procedure/OT all keep their own proper masters because
--                                     they carry extra domain-specific fields these do not.
--   7. package_master + package_items    -- V. Health Checkup / Package pricing, with duplicate-
--                                     billing prevention built into how items are consumed (see
--                                     README note at the end of this file)
--   8. payer_master + payer_price_rule    -- W. Corporate/Insurance/Panel pricing architecture
--                                     (rate table only -- no claim/workflow, as instructed)
--   9. price_history                       -- Z. generic audit trail for every price change made
--                                     through any of the tables above (and reused going forward
--                                     for ward_pricing / lab_tests / medicine too)
--
-- All new pricing tables carry: active/inactive, effective_from, tax_id (where a charge can be
-- taxed), and are written to price_history on every price change. Nothing here builds the
-- operational workflow (OPD check-in, admission, OT, ICU, blood bank, insurance claims etc) --
-- that is explicitly out of scope for this part.
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. TAX MASTER  (Y. Tax)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tax_master` (
  `tax_id` int(11) NOT NULL AUTO_INCREMENT,
  `tax_name` varchar(100) NOT NULL,
  `tax_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `effective_from` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`tax_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------
-- 2. DISCOUNT MASTER  (X. Discount Rules)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `discount_master` (
  `discount_id` int(11) NOT NULL AUTO_INCREMENT,
  `discount_name` varchar(150) NOT NULL,
  `discount_type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `discount_value` decimal(10,2) NOT NULL DEFAULT 0.00,
  `applicable_to` enum('all','service_charge','procedure','ward','room','lab_test','medicine','package','patient_category') NOT NULL DEFAULT 'all',
  `reference_id` int(11) DEFAULT NULL COMMENT 'id inside the applicable_to table, NULL = applies to all rows of that type',
  `max_percentage` decimal(5,2) DEFAULT NULL COMMENT 'hard ceiling this discount can never exceed',
  `requires_authorization` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'if 1, only an authorized role (see discount_permission) can apply it',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`discount_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Which staff roles/logins are allowed to apply an authorization-required discount.
-- Keeps discount authority permission-based instead of every biller having unlimited power.
CREATE TABLE IF NOT EXISTS `discount_permission` (
  `discount_permission_id` int(11) NOT NULL AUTO_INCREMENT,
  `role` enum('admin','accountant','receptionist') NOT NULL,
  `max_discount_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`discount_permission_id`),
  UNIQUE KEY `role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------
-- 3. DOCTOR CONSULTATION CHARGES  (B. Doctor Consultation Charges)
-- ---------------------------------------------------------------------
-- The `doctor` table has no fee column today, so every consultation fee is
-- effectively hard-coded to zero / decided ad-hoc at billing time. This table
-- makes it a real Master: dynamic per doctor, with department inherited for
-- reporting, and separate standard/follow-up/emergency/after-hours rates.
CREATE TABLE IF NOT EXISTS `doctor_charges` (
  `doctor_charge_id` int(11) NOT NULL AUTO_INCREMENT,
  `doctor_id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `standard_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `followup_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `emergency_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `after_hours_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_id` int(11) DEFAULT NULL,
  `effective_from` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`doctor_charge_id`),
  UNIQUE KEY `doctor_id` (`doctor_id`),
  KEY `department_id` (`department_id`),
  KEY `tax_id` (`tax_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- NOTE: UNIQUE on doctor_id keeps this "one active fee row per doctor, edited in place" --
-- consistent with how ward_pricing/lab_tests are edited today. price_history below is what
-- preserves the old amount once a price is changed, so old invoices are never affected.

-- ---------------------------------------------------------------------
-- 4. ROOM CHARGES  (E. Room Charges -- distinct from Ward)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `room_pricing` (
  `room_pricing_id` int(11) NOT NULL AUTO_INCREMENT,
  `room_category` varchar(100) NOT NULL COMMENT 'e.g. Deluxe Room, Suite, Single AC Room',
  `ac_type` enum('AC','Non-AC','NA') NOT NULL DEFAULT 'NA',
  `occupancy` enum('Single','Shared','Special','NA') NOT NULL DEFAULT 'NA',
  `daily_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_id` int(11) DEFAULT NULL,
  `description` varchar(255) NOT NULL DEFAULT '',
  `effective_from` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`room_pricing_id`),
  UNIQUE KEY `room_category` (`room_category`),
  KEY `tax_id` (`tax_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------
-- 5. PROCEDURE + OT/SURGERY MASTER  (K. Procedure Charges + L. OT/Surgery Charges)
-- ---------------------------------------------------------------------
-- One shared structure, as instructed -- `procedure_type` tells a Minor Procedure
-- apart from an OT/Surgery component instead of two near-identical tables.
CREATE TABLE IF NOT EXISTS `procedure_master` (
  `procedure_id` int(11) NOT NULL AUTO_INCREMENT,
  `procedure_code` varchar(50) NOT NULL,
  `procedure_name` varchar(200) NOT NULL,
  `procedure_type` enum(
    'minor_procedure','major_procedure','dressing','suturing','catheterization',
    'cannulation','other_procedure',
    'ot_usage','surgery_charge','surgeon_charge','assistant_surgeon_charge',
    'anesthesia_charge','ot_nursing','ot_consumables','other_ot'
  ) NOT NULL DEFAULT 'minor_procedure',
  `department_id` int(11) DEFAULT NULL,
  `standard_charge` decimal(10,2) NOT NULL DEFAULT 0.00,
  `emergency_charge` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_id` int(11) DEFAULT NULL,
  `effective_from` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`procedure_id`),
  UNIQUE KEY `procedure_code` (`procedure_code`),
  KEY `department_id` (`department_id`),
  KEY `tax_id` (`tax_id`),
  KEY `procedure_type` (`procedure_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------
-- 6. SERVICE CHARGE MASTER  (shared structure for the remaining simple categories)
-- ---------------------------------------------------------------------
-- Covers: A. OPD Charges, C. Nursing/Care, G. Admission, H. Emergency,
-- M. ICU/HDU (service items -- the bed itself stays on ward_pricing, see below),
-- N. Medical Equipment/Device, O. Oxygen/Medical Gas, P. Blood Bank/Transfusion
-- (services, not blood units), S. Ambulance, T. Home Care, U. Certificates/Documents.
-- These all share the exact same shape (name, category, price, active) and do not
-- carry any domain-specific fields the way Lab/Pharmacy/Ward/Room/Procedure do, so
-- one reusable table with a `category` filter is the correct call, not a violation
-- of "don't put everything in one table".
CREATE TABLE IF NOT EXISTS `service_charge_master` (
  `service_charge_id` int(11) NOT NULL AUTO_INCREMENT,
  `service_code` varchar(50) NOT NULL,
  `service_name` varchar(200) NOT NULL,
  `category` enum(
    'opd_registration','opd_consultation','opd_followup','opd_second_opinion','opd_other',
    'nursing','admission','emergency',
    'icu_service','hdu_service',
    'medical_equipment','oxygen_medical_gas',
    'blood_bank_service',
    'ambulance','home_care','certificate_document','other'
  ) NOT NULL DEFAULT 'other',
  `department_id` int(11) DEFAULT NULL,
  `standard_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `emergency_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `unit_label` varchar(50) NOT NULL DEFAULT '' COMMENT 'e.g. per KM, per hour, per visit -- display only',
  `tax_id` int(11) DEFAULT NULL,
  `effective_from` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`service_charge_id`),
  UNIQUE KEY `service_code` (`service_code`),
  KEY `category` (`category`),
  KEY `department_id` (`department_id`),
  KEY `tax_id` (`tax_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------
-- 7. HEALTH CHECKUP / PACKAGE MASTER  (V. Health Checkup / Package)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `package_master` (
  `package_id` int(11) NOT NULL AUTO_INCREMENT,
  `package_name` varchar(200) NOT NULL,
  `package_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `description` varchar(500) NOT NULL DEFAULT '',
  `tax_id` int(11) DEFAULT NULL,
  `effective_from` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`package_id`),
  KEY `tax_id` (`tax_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- What a package is made of. `item_type` + `item_id` point at whichever master the
-- included item actually lives in (lab_tests, procedure_master, service_charge_master...)
-- so nothing is duplicated -- the package just references the existing priced item.
-- Billing reads this table to know a test/service is "already covered by a package"
-- and must not also be billed individually (duplicate-billing prevention -- see README below).
CREATE TABLE IF NOT EXISTS `package_items` (
  `package_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `package_id` int(11) NOT NULL,
  `item_type` enum('lab_test','procedure','service_charge','room','ward') NOT NULL,
  `item_id` int(11) NOT NULL COMMENT 'lab_test_id / procedure_id / service_charge_id / room_pricing_id / ward_pricing_id depending on item_type',
  PRIMARY KEY (`package_item_id`),
  KEY `package_id` (`package_id`),
  KEY `item_lookup` (`item_type`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------
-- 8. CORPORATE / INSURANCE / PANEL PRICING  (W. -- architecture only, no claim workflow)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payer_master` (
  `payer_id` int(11) NOT NULL AUTO_INCREMENT,
  `payer_name` varchar(200) NOT NULL,
  `payer_type` enum('self_pay','insurance','corporate','tpa','hospital_panel','government_scheme','other') NOT NULL DEFAULT 'self_pay',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`payer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- One override rate per (payer, item). Absence of a row here simply means that
-- payer pays the item's normal standard price -- billing falls back to the
-- item's own master price when no payer_price_rule row matches.
CREATE TABLE IF NOT EXISTS `payer_price_rule` (
  `payer_price_rule_id` int(11) NOT NULL AUTO_INCREMENT,
  `payer_id` int(11) NOT NULL,
  `item_type` enum('lab_test','procedure','service_charge','room','ward','medicine') NOT NULL,
  `item_id` int(11) NOT NULL,
  `payer_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `effective_from` date NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`payer_price_rule_id`),
  UNIQUE KEY `payer_item` (`payer_id`,`item_type`,`item_id`),
  KEY `item_lookup` (`item_type`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------
-- 9. PRICE HISTORY  (Z. Price History + 6. AUDIT, generic across every table above)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `price_history` (
  `price_history_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_type` enum(
    'ward_pricing','room_pricing','doctor_charges','procedure_master',
    'service_charge_master','lab_tests','medicine','package_master','payer_price_rule'
  ) NOT NULL,
  `item_id` int(11) NOT NULL,
  `old_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `new_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `changed_by_role` varchar(50) NOT NULL DEFAULT '' COMMENT 'admin / accountant / etc',
  `changed_by_id` int(11) DEFAULT NULL,
  `change_reason` varchar(255) NOT NULL DEFAULT '',
  `changed_at` datetime NOT NULL,
  PRIMARY KEY (`price_history_id`),
  KEY `item_lookup` (`item_type`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- =====================================================================
-- 10. REUSE-ONLY ALTERATIONS to already-correct existing tables
--     (adds tax/effective-date support without touching anything that works)
-- =====================================================================

-- ward_pricing: add tax + effective date only. daily_price / ward_type / active untouched.
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ward_pricing' AND COLUMN_NAME = 'tax_id');
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `ward_pricing` ADD COLUMN `tax_id` int(11) DEFAULT NULL AFTER `active`', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ward_pricing' AND COLUMN_NAME = 'effective_from');
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `ward_pricing` ADD COLUMN `effective_from` date DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- lab_tests: add tax + emergency price only. Everything else (the ONLY place price
-- may be set, per admin::manage_test_pricing()) is unchanged.
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lab_tests' AND COLUMN_NAME = 'tax_id');
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `lab_tests` ADD COLUMN `tax_id` int(11) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'lab_tests' AND COLUMN_NAME = 'emergency_price');
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `lab_tests` ADD COLUMN `emergency_price` decimal(10,2) NOT NULL DEFAULT 0.00', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- medicine: add tax only. price/name/status untouched (Pharmacy step owns the rest).
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'medicine' AND COLUMN_NAME = 'tax_id');
SET @sql := IF(@col_exists = 0, 'ALTER TABLE `medicine` ADD COLUMN `tax_id` int(11) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- README -- things intentionally NOT done here
-- =====================================================================
-- * No separate "ICU Bed" / "HDU Bed" table: per the brief's own instruction not to
--   duplicate Ward/Room/Bed pricing, an ICU or HDU bed is just another `ward_type` row
--   inside the existing `ward_pricing` table (e.g. insert ward_type='ICU',
--   ward_type='HDU'). Only the ICU/HDU *service* items (monitoring, critical care,
--   ventilator use) are new, in `service_charge_master` with category='icu_service'/'hdu_service'.
-- * Blood Bank: only transfusion/processing *services* are added (service_charge_master,
--   category='blood_bank_service'). Actual blood unit inventory (blood_bank /
--   blood_donor tables) is untouched -- that is a Blood Bank step, not pricing.
-- * package_items duplicate-billing prevention is a billing-logic concern, not a DB
--   constraint: when Part 2 builds the billing screen, it must check package_items
--   before allowing an already-packaged test/service to also be added as a standalone
--   line item on the same invoice. The table is structured to make that check a single
--   indexed lookup (`item_lookup`), but no operational billing code has been written
--   here, matching the "no operational workflow in Part 1" instruction.
-- * No insurance claim workflow, no OT/ICU/Ambulance/Home Care *operational* screens,
--   no Blood Bank workflow -- only the configurable Master/pricing layer above, exactly
--   as scoped.