-- =================
-- MySQL Database Schema for Login System
-- Database: mobileapp (on appdev.adnsmsgw.com)
-- Updated with your specific database credentials
-- =================

-- 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, -- Supports both IPv4 and IPv6
    `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;

-- =================
-- Stored Procedures for Maintenance
-- =================

-- Procedure to clean old login attempts
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS `CleanOldLoginAttempts`()
BEGIN
    DELETE FROM `login_attempts` 
    WHERE `attempt_time` < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
DELIMITER ;

-- Show recent login attempts
SELECT ip_address, username, success, attempt_time FROM login_attempts ORDER BY attempt_time DESC LIMIT 10;

-- =================
-- IMPORTANT: Update these credentials in your PHP files!
-- =================

/*
Your database configuration:
- Host: appdev.adnsmsgw.com
- Database: mobileapp  
- Username: root
- Password: adnipt123456

Make sure to:
1. Upload api-login.php to: https://appdev.adnsmsgw.com/api/login.php
2. Upload api-verify-token.php to: https://appdev.adnsmsgw.com/api/verify-token.php
3. Run this SQL schema in your mobileapp database
4. Test the login with: testuser / password123

Security Notes:
- Change the JWT_SECRET in production
- Consider creating a dedicated database user instead of using root
- Enable SSL/HTTPS for all connections
*/