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 importAllDistricts() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Importing All Bangladesh Districts');
    console.log('========================================');
    console.log('');
    
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    const sqlFile = path.join(__dirname, 'add_all_districts.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 districts...');
    await connection.query(sql);
    
    console.log('');
    console.log('========================================');
    console.log('✅ SUCCESS: All districts imported!');
    console.log('========================================');
    console.log('');
    
    // Verify counts
    const [districtCount] = await connection.query('SELECT COUNT(*) as total FROM districts WHERE is_active = 1');
    const [divisionCount] = await connection.query('SELECT COUNT(*) as total FROM divisions WHERE is_active = 1');
    
    console.log('Verification:');
    console.log(`- Divisions: ${divisionCount[0].total}`);
    console.log(`- Districts: ${districtCount[0].total}`);
    console.log('');
    
    // Show districts by division
    const [districtsByDivision] = await connection.query(`
      SELECT d.name as division, COUNT(dt.id) as district_count
      FROM divisions d
      LEFT JOIN districts dt ON d.id = dt.division_id AND dt.is_active = 1
      WHERE d.is_active = 1
      GROUP BY d.id, d.name
      ORDER BY d.id
    `);
    
    console.log('Districts by Division:');
    districtsByDivision.forEach(row => {
      console.log(`  ${row.division}: ${row.district_count} districts`);
    });
    console.log('');
    
  } catch (error) {
    console.error('');
    console.error('========================================');
    console.error('❌ ERROR: Failed to import districts!');
    console.error('========================================');
    console.error('');
    console.error('Error details:');
    console.error(error.message);
    console.error('');
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Database connection closed');
    }
  }
}

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

