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

-- Use your database
USE mobileapp;

-- =================
-- Users Table
-- Stores user account information with secure password hashing
-- =================
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(100) NOT NULL,
    `status` ENUM('active', 'inactive', 'suspended', 'pending') NOT NULL DEFAULT 'active',
    `failed_login_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `last_login` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`id`),
    INDEX `idx_username` (`username`),
    INDEX `idx_email` (`email`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================
-- 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;

-- =================
-- Sample Test Users
-- Password for all test users is: password123
-- Hash generated with: password_hash('password123', PASSWORD_DEFAULT)
-- =================

-- Clear existing test data if needed
DELETE FROM `users` WHERE username IN ('testuser', 'admin', 'john_doe', 'jane_smith');
DELETE FROM `login_attempts` WHERE username IN ('testuser', 'admin', 'john_doe', 'jane_smith');

-- Insert test users
INSERT INTO `users` (`username`, `email`, `password_hash`, `full_name`, `status`) VALUES
('testuser', 'test@appdev.adnsmsgw.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test User', 'active'),
('admin', 'admin@appdev.adnsmsgw.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator', 'active'),
('john_doe', 'john@appdev.adnsmsgw.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'John Doe', 'active'),
('jane_smith', 'jane@appdev.adnsmsgw.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Jane Smith', 'active');

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

-- Show created users
SELECT 'Users created:' as info;
SELECT id, username, email, full_name, status, created_at FROM users ORDER BY id;

-- 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;
SELECT 
    username,
    CASE 
        WHEN password_hash = '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi' 
        THEN 'Hash matches expected value' 
        ELSE 'Hash does not match' 
    END as password_status
FROM users 
WHERE username = 'testuser';

COMMIT;