-- PARAM Hospital Management System
-- ACCOUNTANT & FINANCIAL MANAGEMENT UPGRADE - PART 2 OF 10
-- Patient Advance Balance + Advance Transaction History + Advance Adjustment to Invoice.
--
-- SAFETY: This migration is strictly additive. It never drops a table, deletes a column,
-- deletes data, or resets the database. Every table uses CREATE TABLE IF NOT EXISTS and every
-- ALTER is guarded by an information_schema check, so this file is safe to run:
--   - on a fresh database that has never seen any prior upgrade file,
--   - on a database that already ran DATABASE FILE/accounting_billing_upgrade.sql
--     (which already defines `advance_adjustments`, `payment`.collection_type/request_token),
--   - on a database that already ran DATABASE FILE/part1_patient_advance_payment_upgrade.sql
--     (which already defines `patient_advances`),
--   - more than once, in any order, without duplicate-column/table/index errors.
SET @db := DATABASE();

-- Re-declared with IF NOT EXISTS so this file works standalone even if the earlier
-- accounting/advance upgrades were never applied. If they already exist, these are no-ops.
CREATE TABLE IF NOT EXISTS `patient_advances` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `patient_id` INT NOT NULL, `advance_type` VARCHAR(40) NOT NULL,
  `receipt_number` VARCHAR(60) NOT NULL, `amount` DECIMAL(12,2) NOT NULL, `available_amount` DECIMAL(12,2) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'ACTIVE', `created_by` INT NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `advance_receipt` (`receipt_number`), KEY `advance_patient` (`patient_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `advance_adjustments` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `advance_id` BIGINT NOT NULL, `invoice_id` INT NOT NULL, `amount` DECIMAL(12,2) NOT NULL,
  `created_by` INT NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `advance_invoice_once` (`advance_id`,`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `financial_audit_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `patient_id` INT NOT NULL, `action` VARCHAR(30) NOT NULL,
  `reference_type` VARCHAR(30) NOT NULL, `reference_id` INT NOT NULL, `role` VARCHAR(30) NOT NULL,
  `user_id` INT DEFAULT NULL, `details` TEXT NOT NULL, `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), KEY `audit_patient` (`patient_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `patient_financial_account` (
  `id` INT(11) NOT NULL AUTO_INCREMENT, `patient_id` INT(11) NOT NULL, `account_number` VARCHAR(50) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'active', `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `pfa_patient_unique` (`patient_id`), UNIQUE KEY `pfa_account_number_unique` (`account_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `patient_financial_ledger` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `patient_id` INT(11) NOT NULL, `account_id` INT(11) NOT NULL,
  `transaction_no` VARCHAR(60) NOT NULL, `transaction_type` VARCHAR(20) NOT NULL,
  `reference_type` VARCHAR(30) NOT NULL, `reference_id` INT(11) DEFAULT NULL,
  `description` VARCHAR(255) NOT NULL DEFAULT '',
  `debit` DECIMAL(12,2) NOT NULL DEFAULT 0.00, `credit` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `balance_after` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `payment_method` VARCHAR(30) NOT NULL DEFAULT '', `source_module` VARCHAR(50) NOT NULL DEFAULT '',
  `created_by` INT(11) DEFAULT NULL, `created_at` DATETIME NOT NULL, `status` VARCHAR(20) NOT NULL DEFAULT 'posted',
  PRIMARY KEY (`id`), UNIQUE KEY `pfl_reference_unique` (`reference_type`,`reference_id`),
  UNIQUE KEY `pfl_transaction_no_unique` (`transaction_no`),
  KEY `pfl_patient_date` (`patient_id`,`created_at`), KEY `pfl_account_date` (`account_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Part 2 specific columns on advance_adjustments. Each is guarded so re-running this file,
-- or running it after a partially-applied previous attempt, never raises a duplicate-column error.
-- patient_id: lets adjustment history be queried directly per patient without joining through
--   patient_advances (spec section N: "Patient ID if appropriate").
-- payment_id: links the adjustment back to the payment/payment_allocation row that actually moved
--   the invoice due amount, so the two stay traceable to each other.
SET @q:=IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='advance_adjustments' AND column_name='patient_id')=0,
 'ALTER TABLE advance_adjustments ADD COLUMN patient_id INT NOT NULL DEFAULT 0 AFTER invoice_id','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='advance_adjustments' AND column_name='payment_id')=0,
 'ALTER TABLE advance_adjustments ADD COLUMN payment_id INT DEFAULT NULL AFTER amount','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q:=IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='advance_adjustments' AND index_name='advance_adjustments_patient_idx')=0,
 'ALTER TABLE advance_adjustments ADD KEY advance_adjustments_patient_idx (patient_id,created_at)','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q:=IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='advance_adjustments' AND index_name='advance_adjustments_invoice_idx')=0,
 'ALTER TABLE advance_adjustments ADD KEY advance_adjustments_invoice_idx (invoice_id)','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Defensive re-declaration of columns Part 2 depends on (already added by step5_1_patient_financial_ledger.sql
-- in the normal deploy order). No-ops if already present; only relevant if this file is ever applied standalone.
SET @q:=IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='payment' AND column_name='collection_type')=0,
 'ALTER TABLE payment ADD COLUMN collection_type VARCHAR(20) NOT NULL DEFAULT ''PAYMENT'' AFTER payment_type','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='payment' AND column_name='request_token')=0,
 'ALTER TABLE payment ADD COLUMN request_token VARCHAR(64) DEFAULT NULL AFTER transaction_id','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q:=IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='payment' AND index_name='payment_request_token_unique')=0,
 'ALTER TABLE payment ADD UNIQUE KEY payment_request_token_unique (request_token)','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;