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 checkDistricts() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Checking Districts in Database');
    console.log('========================================');
    console.log('');
    
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    // Check all districts
    const [districts] = await connection.query(`
      SELECT dt.id, dt.name, dt.code, dt.division_id, d.name as division_name, dt.is_active
      FROM districts dt
      LEFT JOIN divisions d ON dt.division_id = d.id
      ORDER BY dt.division_id, dt.name
    `);
    
    console.log(`📊 Total Districts Found: ${districts.length}`);
    console.log('');
    
    if (districts.length === 0) {
      console.log('❌ NO DISTRICTS FOUND!');
      console.log('Importing districts now...');
      console.log('');
      
      // Import districts
      const fs = require('fs');
      const path = require('path');
      const sqlFile = path.join(__dirname, 'add_all_districts.sql');
      const sql = fs.readFileSync(sqlFile, 'utf8');
      
      await connection.query(sql);
      console.log('✅ Districts imported!');
      
      // Check again
      const [newDistricts] = await connection.query(`
        SELECT COUNT(*) as total FROM districts WHERE is_active = 1
      `);
      console.log(`📊 Districts after import: ${newDistricts[0].total}`);
    } else {
      console.log('Districts by Division:');
      const divisions = {};
      districts.forEach(dt => {
        const divName = dt.division_name || 'Unknown';
        if (!divisions[divName]) {
          divisions[divName] = [];
        }
        divisions[divName].push(dt.name);
      });
      
      Object.keys(divisions).sort().forEach(div => {
        console.log(`\n${div} (${divisions[div].length} districts):`);
        divisions[div].forEach(dist => {
          console.log(`  - ${dist}`);
        });
      });
      
      console.log('');
      console.log('✅ All districts are present in database!');
    }
    
  } catch (error) {
    console.error('❌ Error:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

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

