<?php
// ENT Hospital Login Debug Script
// This will help identify exactly what's wrong with your login

echo "=== ENT HOSPITAL LOGIN DEBUG ===\n\n";

// Include database connection
include 'layouts/dbconn.php';

// Test credentials
$test_username = 'admin';
$test_password = 'admin';

echo "Testing login with:\n";
echo "Username: '$test_username'\n";
echo "Password: '$test_password'\n\n";

echo "Step 1: Database Connection Test\n";
echo "--------------------------------\n";

if ($conn->connect_error) {
    echo "❌ Database connection FAILED: " . $conn->connect_error . "\n";
    exit;
} else {
    echo "✅ Database connection successful\n\n";
}

echo "Step 2: Check if users table exists\n";
echo "-----------------------------------\n";

$table_check = $conn->query("SHOW TABLES LIKE 'users'");
if ($table_check->num_rows === 0) {
    echo "❌ 'users' table does not exist!\n";
    exit;
} else {
    echo "✅ 'users' table exists\n\n";
}

echo "Step 3: Check users table structure\n";
echo "-----------------------------------\n";

$structure = $conn->query("DESCRIBE users");
echo "Users table columns:\n";
while ($row = $structure->fetch_assoc()) {
    echo "- {$row['Field']} ({$row['Type']})\n";
}
echo "\n";

echo "Step 4: Check if user_roles table exists\n";
echo "----------------------------------------\n";

$roles_check = $conn->query("SHOW TABLES LIKE 'user_roles'");
if ($roles_check->num_rows === 0) {
    echo "❌ 'user_roles' table does not exist!\n";
    exit;
} else {
    echo "✅ 'user_roles' table exists\n\n";
}

echo "Step 5: List all users in database\n";
echo "----------------------------------\n";

$users_query = "SELECT user_id, username, email, full_name, status, role_id FROM users";
$users_result = $conn->query($users_query);

if ($users_result->num_rows > 0) {
    echo "Found " . $users_result->num_rows . " user(s):\n";
    while ($user = $users_result->fetch_assoc()) {
        echo "- ID: {$user['user_id']}, Username: '{$user['username']}', Email: '{$user['email']}', Status: '{$user['status']}', Role ID: {$user['role_id']}\n";
    }
} else {
    echo "❌ No users found in database!\n";
    exit;
}
echo "\n";

echo "Step 6: Test the exact login query\n";
echo "----------------------------------\n";

$login_sql = "SELECT u.*, ur.role_name, ur.role_code,
               CASE 
                   WHEN p.patient_id IS NOT NULL THEN 'PATIENT'
                   WHEN d.doctor_id IS NOT NULL THEN 'DOCTOR' 
                   WHEN n.nurse_id IS NOT NULL THEN 'NURSE'
                   WHEN lt.tech_id IS NOT NULL THEN 'TECHNICIAN'
                   ELSE 'STAFF'
               END as user_category
               FROM users u 
               INNER JOIN user_roles ur ON u.role_id = ur.role_id 
               LEFT JOIN patients p ON u.user_id = p.user_id AND p.is_active = 1
               LEFT JOIN doctors d ON u.user_id = d.user_id AND d.is_active = 1
               LEFT JOIN nurses n ON u.user_id = n.user_id AND n.is_active = 1
               LEFT JOIN lab_technicians lt ON u.user_id = lt.user_id AND lt.is_active = 1
               WHERE (u.username = ? OR u.email = ?) AND u.status = 'ACTIVE' AND ur.is_active = 1";

$stmt = $conn->prepare($login_sql);
if (!$stmt) {
    echo "❌ Failed to prepare login query: " . $conn->error . "\n";
    exit;
}

$stmt->bind_param("ss", $test_username, $test_username);
$stmt->execute();
$result = $stmt->get_result();

echo "Query executed. Found " . $result->num_rows . " matching user(s).\n";

if ($result->num_rows > 0) {
    $user = $result->fetch_assoc();
    echo "✅ User found:\n";
    echo "- User ID: {$user['user_id']}\n";
    echo "- Username: {$user['username']}\n";
    echo "- Email: {$user['email']}\n";
    echo "- Full Name: {$user['full_name']}\n";
    echo "- Status: {$user['status']}\n";
    echo "- Role: {$user['role_name']} ({$user['role_code']})\n";
    echo "- User Category: {$user['user_category']}\n";
    echo "- Login Attempts: {$user['login_attempts']}\n";
    
    echo "\nStep 7: Test password verification\n";
    echo "----------------------------------\n";
    
    $stored_hash = $user['password_hash'];
    echo "Stored hash: $stored_hash\n";
    echo "Testing password '$test_password': ";
    
    if (password_verify($test_password, $stored_hash)) {
        echo "✅ PASSWORD MATCHES!\n";
        echo "\n🎉 LOGIN SHOULD WORK!\n";
        echo "\nYour login credentials are:\n";
        echo "Username: admin\n";
        echo "Password: admin\n";
    } else {
        echo "❌ PASSWORD DOES NOT MATCH\n";
        
        echo "\nTesting other common passwords:\n";
        $common_passwords = ['password', '123456', 'admin123', 'hospital', 'welcome'];
        
        foreach ($common_passwords as $pwd) {
            if (password_verify($pwd, $stored_hash)) {
                echo "✅ Found matching password: '$pwd'\n";
                echo "\nYour correct login credentials are:\n";
                echo "Username: admin\n";
                echo "Password: $pwd\n";
                break;
            } else {
                echo "❌ '$pwd' does not match\n";
            }
        }
        
        echo "\n🔧 SOLUTION: Generate new password hash\n";
        $new_hash = password_hash('admin', PASSWORD_DEFAULT);
        echo "Run this SQL to set password to 'admin':\n";
        echo "UPDATE users SET password_hash = '$new_hash' WHERE user_id = {$user['user_id']};\n";
    }
    
} else {
    echo "❌ No user found matching username '$test_username' with status 'ACTIVE'\n";
    
    echo "\nChecking what's wrong:\n";
    
    // Check if user exists but with different status
    $status_check = $conn->query("SELECT username, email, status FROM users WHERE username = '$test_username' OR email = '$test_username'");
    if ($status_check->num_rows > 0) {
        $user = $status_check->fetch_assoc();
        echo "❌ User exists but status is: '{$user['status']}' (needs to be 'ACTIVE')\n";
        echo "🔧 Fix: UPDATE users SET status = 'ACTIVE' WHERE username = '{$user['username']}';\n";
    } else {
        echo "❌ User '$test_username' does not exist at all\n";
        echo "🔧 Available users:\n";
        $all_users = $conn->query("SELECT username, email, status FROM users LIMIT 5");
        while ($u = $all_users->fetch_assoc()) {
            echo "   - {$u['username']} ({$u['email']}) - Status: {$u['status']}\n";
        }
    }
}

echo "\n" . str_repeat("=", 50) . "\n";
echo "DEBUG COMPLETE\n";
echo str_repeat("=", 50) . "\n";

$conn->close();
?>