-- Add acc_no column to account table
ALTER TABLE `account` ADD COLUMN `acc_no` VARCHAR(50) NULL AFTER `uname`;

-- Copy existing uname values to acc_no for existing accounts
UPDATE `account` SET `acc_no` = `uname` WHERE `acc_no` IS NULL;

-- Make acc_no NOT NULL after copying data
ALTER TABLE `account` MODIFY COLUMN `acc_no` VARCHAR(50) NOT NULL;

-- Add index for faster lookups
ALTER TABLE `account` ADD INDEX `idx_acc_no` (`acc_no`);
