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

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

-- =================
-- User Sessions Table (Optional - for advanced session management)
-- =================
CREATE TABLE IF NOT EXISTS `user_sessions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `token_hash` VARCHAR(64) NOT NULL, -- SHA-256 hash of the token
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME NOT NULL,
    `last_activity` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
    
    PRIMARY KEY (`id`),
    INDEX `idx_user_token` (`user_id`, `token_hash`),
    INDEX `idx_expires_at` (`expires_at`),
    INDEX `idx_last_activity` (`last_activity`),
    INDEX `idx_is_active` (`is_active`),
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =================
-- Sample Data for Testing (with proper password hashes)
-- Password for all test users is: password123
-- =================

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

-- =================
-- Updated Test Users with Fresh Password Hashes
-- Run this if the above passwords don't work
-- =================

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

-- Insert fresh test users with password: password123
INSERT INTO `users` (`username`, `email`, `password_hash`, `full_name`, `status`) VALUES
('testuser', 'test@appdev.adnsmsgw.com', '$2y$10$YourHashHere', 'Test User', 'active') ON DUPLICATE KEY UPDATE password_hash = '$2y$10$YourHashHere';

-- =================
-- 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);
    
    DELETE FROM `user_sessions` 
    WHERE `expires_at` < NOW() OR `last_activity` < DATE_SUB(NOW(), INTERVAL 7 DAY);
END //
DELIMITER ;

-- =================
-- Quick Setup Commands
-- =================

-- Show current users
SELECT id, username, email, full_name, status, created_at FROM users;

-- 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
*/