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

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || process.env.DB_PASS || '',
  database: process.env.DB_NAME || 'cms_db',
  charset: 'utf8mb4'
};

async function verifyLocations() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Verifying Bangladesh Location Data');
    console.log('========================================');
    console.log('');
    
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    // Check Countries
    const [countries] = await connection.query('SELECT * FROM countries WHERE is_active = 1');
    console.log(`📊 Countries: ${countries.length}`);
    if (countries.length > 0) {
      countries.forEach(c => console.log(`   - ${c.name} (${c.code || 'N/A'})`));
    }
    console.log('');
    
    // Check Divisions
    const [divisions] = await connection.query(`
      SELECT d.*, c.name as country_name 
      FROM divisions d 
      LEFT JOIN countries c ON d.country_id = c.id 
      WHERE d.is_active = 1 
      ORDER BY d.id
    `);
    console.log(`📊 Divisions: ${divisions.length}`);
    if (divisions.length > 0) {
      divisions.forEach(d => console.log(`   - ${d.name} (${d.code || 'N/A'}) - Country: ${d.country_name}`));
    }
    console.log('');
    
    // Check Districts
    const [districts] = await connection.query(`
      SELECT dt.*, d.name as division_name 
      FROM districts dt 
      LEFT JOIN divisions d ON dt.division_id = d.id 
      WHERE dt.is_active = 1 
      ORDER BY dt.division_id, dt.name
      LIMIT 20
    `);
    console.log(`📊 Districts: ${districts.length} (showing first 20)`);
    if (districts.length > 0) {
      districts.forEach(dt => console.log(`   - ${dt.name} (${dt.code || 'N/A'}) - Division: ${dt.division_name}`));
    }
    console.log('');
    
    // Get total district count
    const [districtCount] = await connection.query('SELECT COUNT(*) as total FROM districts WHERE is_active = 1');
    console.log(`📊 Total Districts: ${districtCount[0].total}`);
    console.log('');
    
    // Check Thanas
    const [thanas] = await connection.query(`
      SELECT t.*, dt.name as district_name 
      FROM thanas t 
      LEFT JOIN districts dt ON t.district_id = dt.id 
      WHERE t.is_active = 1 
      ORDER BY t.district_id, t.name
      LIMIT 20
    `);
    console.log(`📊 Thanas: ${thanas.length} (showing first 20)`);
    if (thanas.length > 0) {
      thanas.forEach(t => console.log(`   - ${t.name} (${t.code || 'N/A'}) - District: ${t.district_name}`));
    }
    console.log('');
    
    // Get total thana count
    const [thanaCount] = await connection.query('SELECT COUNT(*) as total FROM thanas WHERE is_active = 1');
    console.log(`📊 Total Thanas: ${thanaCount[0].total}`);
    console.log('');
    
    // Summary
    console.log('========================================');
    console.log('Summary:');
    console.log('========================================');
    console.log(`Countries: ${countries.length}`);
    console.log(`Divisions: ${divisions.length}`);
    console.log(`Districts: ${districtCount[0].total}`);
    console.log(`Thanas: ${thanaCount[0].total}`);
    console.log('');
    
    if (countries.length === 0 || divisions.length === 0) {
      console.log('⚠️  WARNING: No data found! Please run import script.');
    } else {
      console.log('✅ Data is present in database!');
    }
    
  } catch (error) {
    console.error('❌ Error:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

verifyLocations()
  .then(() => process.exit(0))
  .catch((error) => {
    console.error('Fatal error:', error);
    process.exit(1);
  });

