-- PARAM Hospital accountant & billing extension (CodeIgniter 2 / MySQL).
-- Run after the existing Step 5 billing upgrade. This migration only adds fields/tables; it never resets or deletes data.
SET @db := DATABASE();
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 `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 `refund_requests` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `patient_id` INT NOT NULL, `invoice_id` INT DEFAULT NULL, `original_payment_id` INT NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL, `reason` TEXT NOT NULL, `refund_mode` VARCHAR(30) NOT NULL, `status` VARCHAR(25) NOT NULL DEFAULT 'PENDING_APPROVAL',
  `requested_by` INT NOT NULL, `approved_by` INT DEFAULT NULL, `processed_at` DATETIME DEFAULT NULL, `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`), KEY `refund_patient_status` (`patient_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `cash_shifts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `opening_cash` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `expected_closing` DECIMAL(12,2) NOT NULL DEFAULT 0, `actual_closing` DECIMAL(12,2) DEFAULT NULL, `difference_amount` DECIMAL(12,2) DEFAULT NULL,
  `remarks` TEXT, `status` VARCHAR(15) NOT NULL DEFAULT 'OPEN', `opened_at` DATETIME NOT NULL, `closed_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`), KEY `cash_shift_user` (`user_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET @q:=IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='invoice' AND column_name='finalized_at')=0,'ALTER TABLE invoice ADD COLUMN finalized_at DATETIME NULL','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='invoice' AND column_name='finalized_by')=0,'ALTER TABLE invoice ADD COLUMN finalized_by INT NULL','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
-- A normal index is safe for legacy databases where historic rows have a blank bill_number.
-- New numbers are generated from the auto-increment invoice id, which is unique by construction.
SET @q:=IF((SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db AND table_name='invoice' AND index_name='invoice_bill_number_idx')=0,'ALTER TABLE invoice ADD KEY invoice_bill_number_idx (bill_number)','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;