-- PARAM Hospital Management System
-- ACCOUNTANT & FINANCIAL MANAGEMENT UPGRADE - PART 1 OF 10
-- Patient Advance Payment.
--
-- 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 `patient_advances`, `print_history`),
--   - on a database that already ran DATABASE FILE/step5_1_patient_financial_ledger.sql
--     (which already defines `patient_financial_account`, `patient_financial_ledger`),
--   - 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/ledger 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 `print_history` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `reference_type` VARCHAR(30) NOT NULL, `reference_id` INT NOT NULL,
  `printed_by` INT NOT NULL DEFAULT 0, `printed_at` DATETIME NOT NULL, PRIMARY KEY (`id`), KEY `print_reference` (`reference_type`,`reference_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 1 specific columns on patient_advances. Each is guarded so re-running this file,
-- or running it after a partially-applied previous attempt, never raises a duplicate-column error.
SET @q:=IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='patient_advances' AND column_name='payment_mode')=0,
 'ALTER TABLE patient_advances ADD COLUMN payment_mode VARCHAR(30) NOT NULL DEFAULT '''' AFTER available_amount','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_advances' AND column_name='transaction_reference')=0,
 'ALTER TABLE patient_advances ADD COLUMN transaction_reference VARCHAR(100) NOT NULL DEFAULT '''' AFTER payment_mode','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_advances' AND column_name='bank_upi_ref')=0,
 'ALTER TABLE patient_advances ADD COLUMN bank_upi_ref VARCHAR(100) NOT NULL DEFAULT '''' AFTER transaction_reference','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_advances' AND column_name='remarks')=0,
 'ALTER TABLE patient_advances ADD COLUMN remarks VARCHAR(255) NOT NULL DEFAULT '''' AFTER bank_upi_ref','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_advances' AND column_name='adjusted_amount')=0,
 'ALTER TABLE patient_advances ADD COLUMN adjusted_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER remarks','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_advances' AND column_name='refunded_amount')=0,
 'ALTER TABLE patient_advances ADD COLUMN refunded_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER adjusted_amount','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;

-- Compatible request token, same pattern already used on `payment` (step5_1): NULL preserves
-- any pre-existing rows and makes a double-submitted advance fail safely on the unique key,
-- as a second line of defence behind the disabled-button/backend validation in the app layer.
SET @q:=IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='patient_advances' AND column_name='request_token')=0,
 'ALTER TABLE patient_advances ADD COLUMN request_token VARCHAR(64) DEFAULT NULL AFTER refunded_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='patient_advances' AND index_name='patient_advances_request_token_unique')=0,
 'ALTER TABLE patient_advances ADD UNIQUE KEY patient_advances_request_token_unique (request_token)','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;