-- Balance System Database Schema
-- Add balance column to users table
ALTER TABLE users ADD COLUMN balance DECIMAL(10,2) DEFAULT 0.00 AFTER mpin;

-- Create transactions table for balance history
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    transaction_type ENUM('credit', 'debit') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    description VARCHAR(255) NOT NULL,
    reference_id VARCHAR(100) NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_created_at (created_at)
);

-- Create balance_logs table for audit trail
CREATE TABLE IF NOT EXISTS balance_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    old_balance DECIMAL(10,2) NOT NULL,
    new_balance DECIMAL(10,2) NOT NULL,
    amount_changed DECIMAL(10,2) NOT NULL,
    change_type ENUM('credit', 'debit') NOT NULL,
    description VARCHAR(255) NOT NULL,
    admin_id INT NULL,
    transaction_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Update existing users to have initial balance of 0
UPDATE users SET balance = 0.00 WHERE balance IS NULL;

-- Insert some sample transactions for testing (optional)
INSERT INTO transactions (user_id, transaction_type, amount, description, reference_id) 
SELECT id, 'credit', 1000.00, 'Welcome bonus', CONCAT('WB_', id) 
FROM users 
WHERE id = 1;

-- Update user balance based on sample transaction
UPDATE users SET balance = 1000.00 WHERE id = 1;