-- =================
-- MySQL Database Schema for Login System (Simplified)
-- Database: mobileapp (on appdev.adnsmsgw.com)
-- Fixed version without foreign key issues
-- =================

-- Use your database
USE mobileapp;

-- =================
-- Login Attempts Table
-- Tracks login attempts for rate limiting and security monitoring
-- =================
CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ip_address` VARCHAR(45) NOT NULL,
    `username` VARCHAR(50) NOT NULL,
    `success` BOOLEAN NOT NULL DEFAULT FALSE,
    `attempt_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `user_agent` VARCHAR(500) NULL,
    
    PRIMARY KEY (`id`),
    INDEX `idx_ip_time` (`ip_address`, `attempt_time`),
    INDEX `idx_username_time` (`username`, `attempt_time`),
    INDEX `idx_attempt_time` (`attempt_time`),
    INDEX `idx_success` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DELETE FROM `login_attempts` WHERE username IN ('testuser', 'admin', 'john_doe', 'jane_smith');

-- =================
-- Verification Queries
-- =================

-- Show created users
SELECT 'Users created:' as info;

-- Show table structure
SELECT 'Users table structure:' as info;
DESCRIBE users;

SELECT 'Login attempts table structure:' as info;
DESCRIBE login_attempts;

-- =================
-- Test Password Verification (for debugging)
-- =================
SELECT 'Password verification test:' as info;

COMMIT;