const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
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',
  multipleStatements: true,
  charset: 'utf8mb4'
};

async function importAllThanas() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Importing All Bangladesh Thanas (Upazilas)');
    console.log('========================================');
    console.log('');
    
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    const sqlFile = path.join(__dirname, 'add_all_thanas.sql');
    console.log('Reading SQL file:', sqlFile);
    const sql = fs.readFileSync(sqlFile, 'utf8');
    console.log('✅ SQL file read successfully');
    console.log('');
    
    console.log('Importing all thanas...');
    console.log('This may take a few moments...');
    console.log('');
    
    await connection.query(sql);
    
    console.log('');
    console.log('========================================');
    console.log('✅ SUCCESS: All thanas imported!');
    console.log('========================================');
    console.log('');
    
    // Verify counts
    const [thanaCount] = await connection.query('SELECT COUNT(*) as total FROM thanas WHERE is_active = 1');
    const [districtCount] = await connection.query('SELECT COUNT(*) as total FROM districts WHERE is_active = 1');
    
    console.log('Verification:');
    console.log(`- Districts: ${districtCount[0].total}`);
    console.log(`- Thanas: ${thanaCount[0].total}`);
    console.log('');
    
    // Show thanas by district (sample)
    const [thanasByDistrict] = await connection.query(`
      SELECT dt.name as district, COUNT(t.id) as thana_count
      FROM districts dt
      LEFT JOIN thanas t ON dt.id = t.district_id AND t.is_active = 1
      WHERE dt.is_active = 1
      GROUP BY dt.id, dt.name
      ORDER BY dt.division_id, dt.name
      LIMIT 20
    `);
    
    console.log('Sample - Thanas by District (first 20):');
    thanasByDistrict.forEach(row => {
      console.log(`  ${row.district}: ${row.thana_count} thanas`);
    });
    console.log('');
    
    // Show thanas by division
    const [thanasByDivision] = await connection.query(`
      SELECT d.name as division, COUNT(t.id) as thana_count
      FROM divisions d
      LEFT JOIN districts dt ON d.id = dt.division_id AND dt.is_active = 1
      LEFT JOIN thanas t ON dt.id = t.district_id AND t.is_active = 1
      WHERE d.is_active = 1
      GROUP BY d.id, d.name
      ORDER BY d.id
    `);
    
    console.log('Thanas by Division:');
    thanasByDivision.forEach(row => {
      console.log(`  ${row.division}: ${row.thana_count} thanas`);
    });
    console.log('');
    
  } catch (error) {
    console.error('');
    console.error('========================================');
    console.error('❌ ERROR: Failed to import thanas!');
    console.error('========================================');
    console.error('');
    console.error('Error details:');
    console.error(error.message);
    if (error.stack) {
      console.error('Stack:', error.stack);
    }
    console.error('');
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Database connection closed');
    }
  }
}

importAllThanas()
  .then(() => {
    console.log('Import completed successfully!');
    process.exit(0);
  })
  .catch((error) => {
    console.error('Fatal error:', error);
    process.exit(1);
  });

