-- Migration: Add firstname and lastname fields to users table
-- Run this if you have an existing database without these fields

USE escrow_system;

-- Add firstname and lastname columns if they don't exist
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS firstname VARCHAR(100) NOT NULL DEFAULT 'User' AFTER username,
ADD COLUMN IF NOT EXISTS lastname VARCHAR(100) NOT NULL DEFAULT 'Account' AFTER firstname;

-- Add indexes for the new fields
ALTER TABLE users 
ADD INDEX IF NOT EXISTS idx_firstname (firstname),
ADD INDEX IF NOT EXISTS idx_lastname (lastname);

-- Update existing users with placeholder names if they have NULL values
UPDATE users 
SET firstname = 'User', lastname = 'Account' 
WHERE firstname IS NULL OR lastname IS NULL;

-- Make the fields NOT NULL after setting default values
ALTER TABLE users 
MODIFY COLUMN firstname VARCHAR(100) NOT NULL,
MODIFY COLUMN lastname VARCHAR(100) NOT NULL;