-- Step 5 complete: extends (does not recreate) Step 5.1 account/ledger/payment/receipt architecture.
SET @db := DATABASE();
-- Existing dumps use INT for invoice/payment amount; convert without changing any values so new
-- partial payments and price snapshots retain paise.
SET @q:=IF((SELECT DATA_TYPE FROM information_schema.columns WHERE table_schema=@db AND table_name='invoice' AND column_name='amount') NOT IN ('decimal','numeric'),'ALTER TABLE invoice MODIFY amount DECIMAL(12,2) NOT NULL','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
SET @q:=IF((SELECT DATA_TYPE FROM information_schema.columns WHERE table_schema=@db AND table_name='payment' AND column_name='amount') NOT IN ('decimal','numeric'),'ALTER TABLE payment MODIFY amount DECIMAL(12,2) NOT NULL','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
CREATE TABLE IF NOT EXISTS `billing_invoice_item` (
 `id` BIGINT NOT NULL AUTO_INCREMENT, `invoice_id` INT NOT NULL, `patient_id` INT NOT NULL, `description` VARCHAR(255) NOT NULL, `service_code` VARCHAR(80) NOT NULL DEFAULT '', `quantity` DECIMAL(12,2) NOT NULL DEFAULT 1, `unit_rate` DECIMAL(12,2) NOT NULL DEFAULT 0, `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0, `tax_rate` DECIMAL(12,2) NOT NULL DEFAULT 0, `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0, `net_amount` DECIMAL(12,2) NOT NULL DEFAULT 0, `pricing_source` VARCHAR(80) NOT NULL DEFAULT '', `source_reference_type` VARCHAR(40) NOT NULL, `source_reference_id` INT NOT NULL, `source_module` VARCHAR(50) NOT NULL, `created_at` DATETIME NOT NULL, PRIMARY KEY(`id`), UNIQUE KEY `billing_source_once`(`source_reference_type`,`source_reference_id`), KEY `billing_invoice`(`invoice_id`), KEY `billing_patient`(`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `payment_allocation` (`id` BIGINT NOT NULL AUTO_INCREMENT,`payment_id` INT NOT NULL,`invoice_id` INT NOT NULL,`amount` DECIMAL(12,2) NOT NULL,`created_at` DATETIME NOT NULL,PRIMARY KEY(`id`),UNIQUE KEY `payment_invoice_once`(`payment_id`,`invoice_id`),KEY `allocation_invoice`(`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;
SET @q:=IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@db AND table_name='invoice' AND column_name='bill_number')=0,'ALTER TABLE invoice ADD COLUMN bill_number VARCHAR(60) NOT NULL DEFAULT '''' 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='invoice' AND column_name='gross_amount')=0,'ALTER TABLE invoice ADD COLUMN gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0','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='discount_amount')=0,'ALTER TABLE invoice ADD COLUMN discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0','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='tax_amount')=0,'ALTER TABLE invoice ADD COLUMN tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0','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='net_amount')=0,'ALTER TABLE invoice ADD COLUMN net_amount DECIMAL(12,2) NOT NULL DEFAULT 0','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='billing_status')=0,'ALTER TABLE invoice ADD COLUMN billing_status VARCHAR(20) NOT NULL DEFAULT ''POSTED''','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='source_module')=0,'ALTER TABLE invoice ADD COLUMN source_module VARCHAR(50) NOT NULL DEFAULT ''''','SELECT 1'); PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;