-- =====================================================
-- DATABASE FIX FOR USERNAME LOGIN
-- Run this script to fix the 500 error
-- =====================================================

USE alliedcreditunio_mkdb;

-- Step 1: Add username column if it doesn't exist
ALTER TABLE `account` 
ADD COLUMN IF NOT EXISTS `username` VARCHAR(100) DEFAULT NULL AFTER `uname`;

-- Step 2: Populate username from existing uname field (account number)
-- This sets username = uname for all existing accounts
UPDATE `account` 
SET `username` = `uname` 
WHERE `username` IS NULL OR `username` = '';

-- Step 3: For the specific test account, set username to 'Webster'
UPDATE `account` 
SET `username` = 'Webster', `uname` = '9180074482'
WHERE `id` = 517;

-- Step 4: Add index on username for better performance
CREATE INDEX IF NOT EXISTS `idx_username` ON `account`(`username`);

-- Step 5: Verify the changes
SELECT id, acc_no, uname, username, fname, lname, email 
FROM account 
LIMIT 10;

-- =====================================================
-- NOTES:
-- - uname = account number (e.g., '9180074482')
-- - username = login username (e.g., 'Webster')
-- - After running this, users login with username, not account number
-- =====================================================
