-- STEP 4 PART 2 -- Pricing -> Billing wiring.
-- Run this ONCE after step4_part1_pricing_master_architecture.sql has already been applied.
--
-- What this does, and why:
--
-- Step 4 Part 1 built the pricing master tables (tax_master, discount_master,
-- discount_permission, doctor_charges, room_pricing, procedure_master,
-- service_charge_master, package_master/package_items, payer_master/payer_price_rule,
-- price_history) with full admin CRUD, but nothing outside the admin panel ever read
-- from them -- receptionist Collect Payment still took a free-typed title + a
-- manually-typed amount/GST for every line, so the masters had no effect on any real
-- bill. This part wires Collect Payment to them:
--
--   1) patient_receipts gains two nullable columns so a billed line can record which
--      master item (if any) it was generated from. NULL/empty means "custom/free-text
--      item", exactly like every existing row today -- so no existing receipt row is
--      reinterpreted or changed.
--   2) patient_receipts gains a nullable payer_id + a flag recording whether a
--      payer-specific override price (payer_price_rule) was applied for that line,
--      purely for traceability on the receipt/report -- no claim workflow is added.
--   3) discount_permission is seeded with one default row per role ONLY if the table
--      is currently empty, so a fresh install gets sane defaults (admin: 100% i.e.
--      unrestricted, accountant: 30%, receptionist: 15%) instead of silently having no
--      rows at all. If any role rows already exist (e.g. an admin already configured
--      this from the Discount Master screen), this file changes nothing -- existing
--      configuration is never overwritten.
--
-- Nothing existing is removed, renamed, or overwritten, and no existing patient_receipts
-- row is touched. Compatibility-safe (only runs if not already applied), so it is safe
-- to re-run this file.

SET @db := DATABASE();

-- 1) Which master item (if any) this billed line came from.
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='patient_receipts' AND COLUMN_NAME='pricing_item_type') = 0,
  'ALTER TABLE `patient_receipts` ADD COLUMN `pricing_item_type` VARCHAR(30) NOT NULL DEFAULT '''' AFTER `service_name`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='patient_receipts' AND COLUMN_NAME='pricing_item_id') = 0,
  'ALTER TABLE `patient_receipts` ADD COLUMN `pricing_item_id` INT(11) DEFAULT NULL AFTER `pricing_item_type`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) Which payer (if any) this billed line was rated under, and whether a payer-specific
--    override price was actually applied (vs. falling back to the item's standard price).
SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='patient_receipts' AND COLUMN_NAME='payer_id') = 0,
  'ALTER TABLE `patient_receipts` ADD COLUMN `payer_id` INT(11) DEFAULT NULL AFTER `insurance_tpa`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

SET @q := IF(
  (SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA=@db AND TABLE_NAME='patient_receipts' AND COLUMN_NAME='payer_price_applied') = 0,
  'ALTER TABLE `patient_receipts` ADD COLUMN `payer_price_applied` TINYINT(1) NOT NULL DEFAULT 0 AFTER `payer_id`',
  'SELECT 1'
);
PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- 3) Seed default discount permission rows ONLY if the table is completely empty.
INSERT INTO `discount_permission` (`role`,`max_discount_percentage`,`active`)
SELECT * FROM (
  SELECT 'admin' AS role, 100.00 AS max_discount_percentage, 1 AS active
  UNION ALL SELECT 'accountant', 30.00, 1
  UNION ALL SELECT 'receptionist', 15.00, 1
) AS defaults
WHERE (SELECT COUNT(*) FROM `discount_permission`) = 0;