-- Migration: Failed login tracking, admin activity log, user avatar
-- PHP User Registration - https://rskworld.in | Molla Samser | 2026
-- Run once on existing DB.

-- Failed login attempts (for security / admin view)
CREATE TABLE IF NOT EXISTS failed_login_attempts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NULL,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_ip (ip_address),
    INDEX idx_attempted (attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin activity log (role changes, suspend/unsuspend)
CREATE TABLE IF NOT EXISTS admin_activity_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,
    target_user_id INT UNSIGNED NULL,
    details VARCHAR(500) NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin (admin_user_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User avatar (optional profile picture path). If you get "duplicate column", it already exists.
ALTER TABLE users ADD COLUMN avatar_path VARCHAR(255) NULL;
