-- =====================================================
-- COPY AND PASTE THIS ENTIRE FILE INTO PHPMYADMIN
-- =====================================================
-- Database: alliedcreditunio_mkdb
-- Purpose: Fix 500 error by adding username column
-- Date: February 20, 2026
-- =====================================================

-- Select the correct database
USE alliedcreditunio_mkdb;

-- =====================================================
-- STEP 1: Add username column
-- =====================================================
-- This adds a new column called 'username' after the 'uname' column
-- VARCHAR(100) means it can store up to 100 characters
-- DEFAULT NULL means existing records will have NULL until we populate them

ALTER TABLE `account` 
ADD COLUMN `username` VARCHAR(100) DEFAULT NULL AFTER `uname`;

-- =====================================================
-- STEP 2: Populate username from uname (account number)
-- =====================================================
-- This copies the account number to username for all existing accounts
-- This is a safe default - you can change usernames later if needed

UPDATE `account` 
SET `username` = `uname` 
WHERE `username` IS NULL OR `username` = '';

-- =====================================================
-- STEP 3: Fix the test account (ID 517)
-- =====================================================
-- This sets the correct values for your test account
-- uname = account number (9180074482)
-- username = login username (Webster)

UPDATE `account` 
SET `username` = 'Webster', 
    `uname` = '9180074482'
WHERE `id` = 517;

-- =====================================================
-- STEP 4: Add index for better performance
-- =====================================================
-- This makes login queries faster by indexing the username column

CREATE INDEX `idx_username` ON `account`(`username`);

-- =====================================================
-- STEP 5: Verify the changes
-- =====================================================
-- Run this to see if everything worked correctly

SELECT 
    id,
    acc_no,
    uname AS 'Account Number',
    username AS 'Login Username',
    fname AS 'First Name',
    lname AS 'Last Name',
    email,
    status
FROM account 
ORDER BY id DESC
LIMIT 10;

-- =====================================================
-- EXPECTED RESULT:
-- =====================================================
-- You should see:
-- - id: 517
-- - Account Number: 9180074482
-- - Login Username: Webster
-- - First Name: Chibuogwu
-- - Last Name: Nnabuife
-- - Status: ACTIVE
-- =====================================================

-- =====================================================
-- OPTIONAL: Make username required (run after testing)
-- =====================================================
-- Uncomment these lines after you've tested and confirmed everything works
-- This prevents creating accounts without usernames

-- ALTER TABLE `account` 
-- MODIFY COLUMN `username` VARCHAR(100) NOT NULL;

-- =====================================================
-- OPTIONAL: Make username unique (run after testing)
-- =====================================================
-- Uncomment this after testing to prevent duplicate usernames
-- Make sure all usernames are unique first!

-- ALTER TABLE `account` 
-- ADD UNIQUE KEY `unique_username` (`username`);

-- =====================================================
-- VERIFICATION QUERIES
-- =====================================================

-- Check if username column exists
SHOW COLUMNS FROM `account` LIKE 'username';

-- Count accounts with usernames
SELECT 
    COUNT(*) as total_accounts,
    SUM(CASE WHEN username IS NOT NULL AND username != '' THEN 1 ELSE 0 END) as with_username,
    SUM(CASE WHEN username IS NULL OR username = '' THEN 1 ELSE 0 END) as without_username
FROM account;

-- Check for duplicate usernames
SELECT username, COUNT(*) as count 
FROM account 
WHERE username IS NOT NULL 
GROUP BY username 
HAVING count > 1;

-- =====================================================
-- TROUBLESHOOTING
-- =====================================================

-- If you get "Column already exists" error:
-- The column was already added. Skip to STEP 2.

-- If you get "Duplicate entry" error on unique key:
-- You have duplicate usernames. Run this to find them:
-- SELECT username, COUNT(*) FROM account GROUP BY username HAVING COUNT(*) > 1;

-- If you need to remove the username column (rollback):
-- ALTER TABLE `account` DROP COLUMN `username`;

-- =====================================================
-- DONE!
-- =====================================================
-- After running this script:
-- 1. Go to http://yoursite.com/verify_fix.php
-- 2. Check that all tests pass
-- 3. Try logging in with username: Webster, password: 12345678!
-- 4. If everything works, delete the test files
-- =====================================================
