-- Migration Script: Add username field to account table
-- This script adds a new 'username' column for login authentication
-- while keeping 'uname' as the account number field

-- Step 1: Add username column to account table
ALTER TABLE `account` ADD COLUMN `username` VARCHAR(100) NULL AFTER `uname`;

-- Step 2: Create unique index on username for faster lookups and prevent duplicates
ALTER TABLE `account` ADD UNIQUE INDEX `idx_username` (`username`);

-- Step 3: (Optional) Copy existing uname values to username as initial data
-- You can modify this based on your requirements
-- UPDATE `account` SET `username` = `uname` WHERE `username` IS NULL;

-- Step 4: After populating username field, you can make it NOT NULL
-- ALTER TABLE `account` MODIFY COLUMN `username` VARCHAR(100) NOT NULL;

-- Notes:
-- 1. The 'uname' field will continue to store account numbers
-- 2. The new 'username' field will be used for login authentication
-- 3. You need to manually set usernames for existing accounts before enforcing NOT NULL
-- 4. Consider adding a script to generate unique usernames for existing accounts
