-- Update Users Table - Add Missing Password Column
-- Run this script if you already have a database without password_hash column

USE trustbeam_bank;

-- Add password_hash column to existing users table
ALTER TABLE users ADD COLUMN password_hash VARCHAR(255) NOT NULL DEFAULT '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi' AFTER email;

-- Add last_login column to existing users table
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL AFTER status;

-- Update existing users with default password (password = 'password')
-- This sets a default password for all existing users
UPDATE users SET password_hash = '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi' WHERE password_hash = '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi';

-- Insert sample users if table is empty
INSERT IGNORE INTO users (firstname, lastname, username, email, password_hash, phone, address, nationality, account_number, account_type, balance, status) VALUES 
('John', 'Doe', 'johndoe', 'john.doe@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+1234567890', '123 Main St, City, State', 'American', 'ACC001234567', 'Savings', 5000.00, 'Active'),
('Jane', 'Smith', 'janesmith', 'jane.smith@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+1234567891', '456 Oak Ave, City, State', 'American', 'ACC001234568', 'Current', 7500.00, 'Active'),
('Mike', 'Johnson', 'mikejohnson', 'mike.johnson@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+1234567892', '789 Pine Rd, City, State', 'American', 'ACC001234569', 'Business', 15000.00, 'Active'),
('Sarah', 'Williams', 'sarahwilliams', 'sarah.williams@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+1234567893', '321 Elm St, City, State', 'American', 'ACC001234570', 'Premium', 25000.00, 'Active'),
('David', 'Brown', 'davidbrown', 'david.brown@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+1234567894', '654 Maple Dr, City, State', 'American', 'ACC001234571', 'Savings', 3000.00, 'Active');

-- Insert sample transactions if table is empty
INSERT IGNORE INTO transactions (user_id, transaction_type, amount, description, reference_number, status) VALUES 
(1, 'Deposit', 1000.00, 'Initial deposit', 'TXN001', 'Completed'),
(2, 'Deposit', 2000.00, 'Salary deposit', 'TXN002', 'Completed'),
(3, 'Deposit', 5000.00, 'Business payment', 'TXN003', 'Completed'),
(1, 'Withdrawal', 500.00, 'ATM withdrawal', 'TXN004', 'Completed'),
(2, 'Transfer', 1000.00, 'Transfer to savings', 'TXN005', 'Completed');

-- Insert sample deposits if table is empty
INSERT IGNORE INTO deposits (user_id, amount, deposit_method, reference_number, status, notes) VALUES 
(1, 1000.00, 'Bank Transfer', 'DEP001', 'Approved', 'Initial deposit'),
(2, 2000.00, 'Cash', 'DEP002', 'Approved', 'Cash deposit'),
(3, 5000.00, 'Check', 'DEP003', 'Approved', 'Business check'),
(4, 3000.00, 'Bank Transfer', 'DEP004', 'Pending', 'Salary deposit'),
(5, 1500.00, 'Mobile Money', 'DEP005', 'Pending', 'Mobile payment');

-- Show updated table structure
DESCRIBE users;

-- Show sample users with passwords
SELECT id, username, email, status FROM users LIMIT 5;
