-- Migration: Add bill_number column for gapless sequencing
ALTER TABLE billing_records
ADD COLUMN bill_number INT DEFAULT NULL AFTER id;

-- Initialize bill_number with existing ID values
SET @count = 0;
UPDATE billing_records SET bill_number = (@count := @count + 1) ORDER BY id ASC;

-- Make it unique and not null after initialization
ALTER TABLE billing_records
MODIFY COLUMN bill_number INT NOT NULL,
ADD UNIQUE (bill_number);
