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

// Database configuration - use same config as main app
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 importBangladeshLocations() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Bangladesh Location Data Import Script');
    console.log('========================================');
    console.log('');
    
    // Connect to database
    console.log('Connecting to database...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    // Read SQL file
    const sqlFile = path.join(__dirname, 'insert_bangladesh_locations.sql');
    console.log('Reading SQL file:', sqlFile);
    const sql = fs.readFileSync(sqlFile, 'utf8');
    console.log('✅ SQL file read successfully');
    console.log('');
    
    // Execute SQL
    console.log('Importing Bangladesh location data...');
    console.log('This may take a few moments...');
    console.log('');
    
    await connection.query(sql);
    
    console.log('');
    console.log('========================================');
    console.log('✅ SUCCESS: Bangladesh location data imported!');
    console.log('========================================');
    console.log('');
    console.log('Data imported:');
    console.log('- 1 Country (Bangladesh)');
    console.log('- 8 Divisions');
    console.log('- 64 Districts');
    console.log('- Major Thanas for all districts');
    console.log('');
    
    // Get counts
    const [countryCount] = await connection.query('SELECT COUNT(*) as count FROM countries WHERE is_active = 1');
    const [divisionCount] = await connection.query('SELECT COUNT(*) as count FROM divisions WHERE is_active = 1');
    const [districtCount] = await connection.query('SELECT COUNT(*) as count FROM districts WHERE is_active = 1');
    const [thanaCount] = await connection.query('SELECT COUNT(*) as count FROM thanas WHERE is_active = 1');
    
    console.log('Verification:');
    console.log(`- Countries: ${countryCount[0].count}`);
    console.log(`- Divisions: ${divisionCount[0].count}`);
    console.log(`- Districts: ${districtCount[0].count}`);
    console.log(`- Thanas: ${thanaCount[0].count}`);
    console.log('');
    
  } catch (error) {
    console.error('');
    console.error('========================================');
    console.error('❌ ERROR: Failed to import data!');
    console.error('========================================');
    console.error('');
    console.error('Error details:');
    console.error(error.message);
    console.error('');
    console.error('Please check:');
    console.error('1. Database connection settings');
    console.error('2. Database name is correct');
    console.error('3. MySQL is running');
    console.error('4. Database user has proper permissions');
    console.error('');
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Database connection closed');
    }
  }
}

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

