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 checkColumnExists(connection, tableName, columnName) {
  try {
    const [rows] = await connection.query(
      `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
       WHERE TABLE_SCHEMA = DATABASE() 
       AND TABLE_NAME = ? 
       AND COLUMN_NAME = ?`,
      [tableName, columnName]
    );
    return rows.length > 0;
  } catch (error) {
    return false;
  }
}

async function addLocationColumns() {
  let connection;
  
  try {
    console.log('========================================');
    console.log('Adding Location Columns to Contacts Table');
    console.log('========================================');
    console.log('');
    
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ Connected to database');
    console.log('');
    
    const columnsToAdd = [
      // Present Address
      { name: 'present_country_id', after: 'present_address' },
      { name: 'present_division_id', after: 'present_country_id' },
      { name: 'present_district_id', after: 'present_division_id' },
      { name: 'present_thana_id', after: 'present_district_id' },
      // Office Address
      { name: 'office_country_id', after: 'office_address' },
      { name: 'office_division_id', after: 'office_country_id' },
      { name: 'office_district_id', after: 'office_division_id' },
      { name: 'office_thana_id', after: 'office_district_id' },
      // Permanent Address
      { name: 'permanent_country_id', after: 'permanent_address' },
      { name: 'permanent_division_id', after: 'permanent_country_id' },
      { name: 'permanent_district_id', after: 'permanent_division_id' },
      { name: 'permanent_thana_id', after: 'permanent_district_id' }
    ];
    
    // Add columns
    for (const col of columnsToAdd) {
      const exists = await checkColumnExists(connection, 'contacts', col.name);
      if (!exists) {
        try {
          await connection.query(
            `ALTER TABLE contacts ADD COLUMN \`${col.name}\` INT NULL AFTER \`${col.after}\``
          );
          console.log(`✅ Added column: ${col.name}`);
        } catch (error) {
          console.error(`❌ Error adding ${col.name}:`, error.message);
        }
      } else {
        console.log(`⚠️  Column ${col.name} already exists, skipping...`);
      }
    }
    
    // Add indexes
    const indexMappings = [
      { name: 'idx_present_country', column: 'present_country_id' },
      { name: 'idx_present_division', column: 'present_division_id' },
      { name: 'idx_present_district', column: 'present_district_id' },
      { name: 'idx_present_thana', column: 'present_thana_id' },
      { name: 'idx_office_country', column: 'office_country_id' },
      { name: 'idx_office_division', column: 'office_division_id' },
      { name: 'idx_office_district', column: 'office_district_id' },
      { name: 'idx_office_thana', column: 'office_thana_id' },
      { name: 'idx_permanent_country', column: 'permanent_country_id' },
      { name: 'idx_permanent_division', column: 'permanent_division_id' },
      { name: 'idx_permanent_district', column: 'permanent_district_id' },
      { name: 'idx_permanent_thana', column: 'permanent_thana_id' }
    ];
    
    for (const idx of indexMappings) {
      try {
        await connection.query(
          `CREATE INDEX \`${idx.name}\` ON contacts (\`${idx.column}\`)`
        );
        console.log(`✅ Added index: ${idx.name}`);
      } catch (error) {
        if (error.message.includes('Duplicate key') || error.message.includes('already exists')) {
          console.log(`⚠️  Index ${idx.name} already exists, skipping...`);
        } else {
          console.error(`❌ Error adding index ${idx.name}:`, error.message);
        }
      }
    }
    
    console.log('');
    console.log('========================================');
    console.log('✅ Migration completed!');
    console.log('========================================');
    
  } catch (error) {
    console.error('');
    console.error('========================================');
    console.error('❌ ERROR: Migration failed!');
    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');
    }
  }
}

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