const { pool } = require('./src/config/database');

async function testUsersAPI() {
  console.log('🧪 TESTING USERS API');
  console.log('===================');
  console.log('');

  try {
    // Test 1: Direct database query
    console.log('1. Direct database query...');
    const [users] = await pool.execute(
      `SELECT 
         u.id, u.name, u.email, u.service_no, u.is_active, u.last_seen,
         r.name AS role,
         un.name AS unit
       FROM users u
       LEFT JOIN roles r ON u.role_id = r.id
       LEFT JOIN units un ON u.unit_id = un.id
       ORDER BY u.id ASC`
    );

    console.log(`   ✅ Found ${users.length} users in database:`);
    users.forEach(user => {
      console.log(`     - ID: ${user.id}, Name: "${user.name}", Role: "${user.role || 'No Role'}", Service No: "${user.service_no || 'No Service No'}"`);
    });
    console.log('');

    // Test 2: Check if users table has required columns
    console.log('2. Checking users table structure...');
    const [columns] = await pool.execute(
      `DESCRIBE users`
    );

    console.log(`   ✅ Users table has ${columns.length} columns:`);
    columns.forEach(col => {
      console.log(`     - ${col.Field}: ${col.Type} ${col.Null === 'YES' ? '(nullable)' : '(not null)'}`);
    });
    console.log('');

    // Test 3: Check roles table
    console.log('3. Checking roles table...');
    const [roles] = await pool.execute(
      `SELECT id, name FROM roles WHERE is_active = 1 ORDER BY id ASC`
    );

    console.log(`   ✅ Found ${roles.length} active roles:`);
    roles.forEach(role => {
      console.log(`     - ID: ${role.id}, Name: "${role.name}"`);
    });
    console.log('');

    // Test 4: Check units table
    console.log('4. Checking units table...');
    const [units] = await pool.execute(
      `SELECT id, name FROM units ORDER BY id ASC LIMIT 5`
    );

    console.log(`   ✅ Found ${units.length} units (showing first 5):`);
    units.forEach(unit => {
      console.log(`     - ID: ${unit.id}, Name: "${unit.name}"`);
    });
    console.log('');

    // Test 5: Check user-role relationships
    console.log('5. Checking user-role relationships...');
    const [userRoles] = await pool.execute(
      `SELECT 
         u.id, u.name, u.role_id, r.name as role_name
       FROM users u
       LEFT JOIN roles r ON u.role_id = r.id
       ORDER BY u.id ASC`
    );

    console.log(`   ✅ User-Role relationships:`);
    userRoles.forEach(userRole => {
      console.log(`     - ${userRole.name} (ID: ${userRole.id}) -> Role ID: ${userRole.role_id}, Role Name: "${userRole.role_name || 'No Role'}"`);
    });
    console.log('');

    console.log('===================');
    console.log('✅ USERS API TEST COMPLETE');
    console.log('');
    console.log('📋 SUMMARY:');
    console.log(`   - Database users: ${users.length}`);
    console.log(`   - Active roles: ${roles.length}`);
    console.log(`   - Units available: ${units.length}`);
    console.log(`   - Users with roles: ${userRoles.filter(ur => ur.role_name).length}`);
    console.log(`   - Users without roles: ${userRoles.filter(ur => !ur.role_name).length}`);

  } catch (error) {
    console.error('❌ Error testing users API:', error);
  } finally {
    await pool.end();
  }
}

testUsersAPI();
