const mysql = require('mysql2/promise');
require('dotenv').config();

async function testShopFiltering() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'tuc'
  });

  try {
    console.log('🔍 Testing shop filtering setup...\n');
    
    // Check users with assigned shops
    const [users] = await connection.execute(`
      SELECT id, name, email, role, assignedShopIds, assignedShopNames 
      FROM users 
      WHERE role = 'Shopkeeper' AND isDeleted = 0
    `);
    
    console.log('👥 Shopkeeper users:');
    users.forEach(user => {
      console.log(`  ID: ${user.id}, Name: ${user.name}, Email: ${user.email}`);
      console.log(`      Assigned Shop IDs: ${user.assignedShopIds || '[]'}`);
      console.log(`      Assigned Shop Names: ${user.assignedShopNames || '[]'}\n`);
    });

    // Check shops
    const [shops] = await connection.execute(`
      SELECT id, name, location, shopkeeperIds, shopkeeperNames, isDeleted 
      FROM shops 
      WHERE isDeleted = 0
    `);
    
    console.log('🏪 Available shops:');
    shops.forEach(shop => {
      console.log(`  ID: ${shop.id}, Name: ${shop.name}, Location: ${shop.location}`);
      console.log(`      Shopkeeper IDs: ${shop.shopkeeperIds || '[]'}`);
      console.log(`      Shopkeeper Names: ${shop.shopkeeperNames || '[]'}\n`);
    });

    // Check shop products
    const [shopProducts] = await connection.execute(`
      SELECT id, shopId, shopName, shopProductName, isDeleted 
      FROM shop_products 
      WHERE isDeleted = 0
    `);
    
    console.log('📦 Shop products:');
    shopProducts.forEach(product => {
      console.log(`  ID: ${product.id}, Shop ID: ${product.shopId}, Shop: ${product.shopName}`);
      console.log(`      Product: ${product.shopProductName}\n`);
    });

    // Check shop purchases
    const [shopPurchases] = await connection.execute(`
      SELECT id, shopId, shopName, productName, quantity, isDeleted 
      FROM shop_purchases 
      WHERE isDeleted = 0
    `);
    
    console.log('🛒 Shop purchases:');
    shopPurchases.forEach(purchase => {
      console.log(`  ID: ${purchase.id}, Shop ID: ${purchase.shopId}, Shop: ${purchase.shopName}`);
      console.log(`      Product: ${purchase.productName}, Quantity: ${purchase.quantity}\n`);
    });

    console.log('✅ Shop filtering test completed!');
    console.log('\n📋 Summary:');
    console.log(`   - ${users.length} shopkeeper users found`);
    console.log(`   - ${shops.length} shops found`);
    console.log(`   - ${shopProducts.length} shop products found`);
    console.log(`   - ${shopPurchases.length} shop purchases found`);
    
  } catch (error) {
    console.error('❌ Test failed:', error.message);
    throw error;
  } finally {
    await connection.end();
  }
}

// Run the test
if (require.main === module) {
  testShopFiltering()
    .then(() => {
      console.log('\nTest script completed');
      process.exit(0);
    })
    .catch((error) => {
      console.error('Test script failed:', error);
      process.exit(1);
    });
}

module.exports = testShopFiltering;