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',
  multipleStatements: true,
  charset: 'utf8mb4'
};

async function forceInsertAllDistricts() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Force Inserting All Bangladesh Districts');
    console.log('========================================');
    console.log('');
    
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    // First, ensure divisions exist
    console.log('Ensuring divisions exist...');
    await connection.query(`
      INSERT INTO divisions (id, country_id, name, code, is_active) VALUES
      (1, 1, 'Dhaka', 'DHK', 1),
      (2, 1, 'Chittagong', 'CTG', 1),
      (3, 1, 'Rajshahi', 'RAJ', 1),
      (4, 1, 'Khulna', 'KHL', 1),
      (5, 1, 'Barisal', 'BAR', 1),
      (6, 1, 'Sylhet', 'SYL', 1),
      (7, 1, 'Rangpur', 'RAN', 1),
      (8, 1, 'Mymensingh', 'MYM', 1)
      ON DUPLICATE KEY UPDATE name = VALUES(name), code = VALUES(code);
    `);
    console.log('✅ Divisions ensured');
    console.log('');
    
    // Delete all existing districts first (to avoid duplicates)
    console.log('Clearing existing districts...');
    await connection.query('DELETE FROM districts');
    console.log('✅ Existing districts cleared');
    console.log('');
    
    // Insert all 64 districts
    console.log('Inserting all 64 districts...');
    
    const districtsSQL = `
      -- Dhaka Division (13 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (1, 'Dhaka', 'DHK', 1),
      (1, 'Gazipur', 'GZP', 1),
      (1, 'Kishoreganj', 'KSG', 1),
      (1, 'Manikganj', 'MKG', 1),
      (1, 'Munshiganj', 'MSG', 1),
      (1, 'Narayanganj', 'NRG', 1),
      (1, 'Narsingdi', 'NSD', 1),
      (1, 'Tangail', 'TGL', 1),
      (1, 'Faridpur', 'FRP', 1),
      (1, 'Gopalganj', 'GPG', 1),
      (1, 'Madaripur', 'MDP', 1),
      (1, 'Rajbari', 'RJB', 1),
      (1, 'Shariatpur', 'SRP', 1);
      
      -- Chittagong Division (11 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (2, 'Chittagong', 'CTG', 1),
      (2, 'Cox''s Bazar', 'CXB', 1),
      (2, 'Bandarban', 'BDB', 1),
      (2, 'Rangamati', 'RGM', 1),
      (2, 'Khagrachhari', 'KGC', 1),
      (2, 'Feni', 'FNI', 1),
      (2, 'Lakshmipur', 'LKP', 1),
      (2, 'Noakhali', 'NKL', 1),
      (2, 'Chandpur', 'CDP', 1),
      (2, 'Comilla', 'CML', 1),
      (2, 'Brahmanbaria', 'BRB', 1);
      
      -- Rajshahi Division (8 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (3, 'Rajshahi', 'RAJ', 1),
      (3, 'Bogra', 'BGR', 1),
      (3, 'Joypurhat', 'JPH', 1),
      (3, 'Naogaon', 'NGN', 1),
      (3, 'Natore', 'NTR', 1),
      (3, 'Chapai Nawabganj', 'CNG', 1),
      (3, 'Pabna', 'PBN', 1),
      (3, 'Sirajganj', 'SRG', 1);
      
      -- Khulna Division (10 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (4, 'Khulna', 'KHL', 1),
      (4, 'Bagerhat', 'BGH', 1),
      (4, 'Chuadanga', 'CHD', 1),
      (4, 'Jashore', 'JSR', 1),
      (4, 'Jhenaidah', 'JND', 1),
      (4, 'Kushtia', 'KST', 1),
      (4, 'Magura', 'MGR', 1),
      (4, 'Meherpur', 'MHP', 1),
      (4, 'Narail', 'NRL', 1),
      (4, 'Satkhira', 'SKR', 1);
      
      -- Barisal Division (6 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (5, 'Barisal', 'BAR', 1),
      (5, 'Barguna', 'BGN', 1),
      (5, 'Bhola', 'BHL', 1),
      (5, 'Jhalokati', 'JHK', 1),
      (5, 'Patuakhali', 'PTK', 1),
      (5, 'Pirojpur', 'PRP', 1);
      
      -- Sylhet Division (4 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (6, 'Sylhet', 'SYL', 1),
      (6, 'Habiganj', 'HBG', 1),
      (6, 'Moulvibazar', 'MLV', 1),
      (6, 'Sunamganj', 'SNG', 1);
      
      -- Rangpur Division (8 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (7, 'Rangpur', 'RAN', 1),
      (7, 'Dinajpur', 'DNP', 1),
      (7, 'Gaibandha', 'GBD', 1),
      (7, 'Kurigram', 'KRM', 1),
      (7, 'Lalmonirhat', 'LMH', 1),
      (7, 'Nilphamari', 'NPM', 1),
      (7, 'Panchagarh', 'PCG', 1),
      (7, 'Thakurgaon', 'TKG', 1);
      
      -- Mymensingh Division (4 districts)
      INSERT INTO districts (division_id, name, code, is_active) VALUES
      (8, 'Mymensingh', 'MYM', 1),
      (8, 'Jamalpur', 'JMP', 1),
      (8, 'Netrokona', 'NTK', 1),
      (8, 'Sherpur', 'SHP', 1);
    `;
    
    await connection.query(districtsSQL);
    console.log('✅ All 64 districts inserted!');
    console.log('');
    
    // Verify
    const [districtCount] = await connection.query('SELECT COUNT(*) as total FROM districts WHERE is_active = 1');
    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('========================================');
    console.log('Verification:');
    console.log('========================================');
    console.log(`Total Districts: ${districtCount[0].total}`);
    console.log('');
    console.log('Districts by Division:');
    districtsByDivision.forEach(row => {
      console.log(`  ${row.division}: ${row.district_count} districts`);
    });
    console.log('');
    
    // Show all districts
    const [allDistricts] = await connection.query(`
      SELECT dt.name as district, d.name as division
      FROM districts dt
      JOIN divisions d ON dt.division_id = d.id
      WHERE dt.is_active = 1
      ORDER BY dt.division_id, dt.name
    `);
    
    console.log('All Districts:');
    allDistricts.forEach((dt, index) => {
      console.log(`${index + 1}. ${dt.district} (${dt.division})`);
    });
    console.log('');
    
    console.log('✅ SUCCESS: All districts inserted and verified!');
    
  } catch (error) {
    console.error('');
    console.error('========================================');
    console.error('❌ ERROR: Failed to insert districts!');
    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');
    }
  }
}

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

