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

// SQLite database path
const sqliteDbPath = path.join(__dirname, '..', 'database.sqlite');

// MySQL database configuration
const mysqlConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'project_management',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true
};

// Function to convert ISO datetime format to MySQL format
const convertDateTime = (dateTimeStr) => {
  if (!dateTimeStr) return null;
  
  // Handle ISO format with 'T' and 'Z'
  if (dateTimeStr.includes('T')) {
    // Replace 'T' with space and remove 'Z' if present
    return dateTimeStr.replace('T', ' ').replace('Z', '').split('.')[0];
  }
  
  return dateTimeStr;
};

// Function to get all data from SQLite table
const getAllFromSQLiteTable = (db, tableName) => {
  return new Promise((resolve, reject) => {
    db.all(`SELECT * FROM ${tableName}`, [], (err, rows) => {
      if (err) {
        reject(err);
      } else {
        resolve(rows);
      }
    });
  });
};

// Function to insert data into MySQL table
const insertIntoMySQLTable = async (connection, tableName, data) => {
  if (!data || data.length === 0) {
    console.log(`No data to migrate for table: ${tableName}`);
    return;
  }

  // Get column names from the first row
  const columns = Object.keys(data[0]);
  const placeholders = columns.map(() => '?').join(', ');
  const columnNames = columns.map(col => `\`${col}\``).join(', ');

  // Use INSERT IGNORE for all tables to handle duplicates
  const insertSQL = `INSERT IGNORE INTO \`${tableName}\` (${columnNames}) VALUES (${placeholders})`;
  
  try {
    let insertedCount = 0;
    
    // Insert each row
    for (const row of data) {
      // Process datetime fields
      const processedRow = { ...row };
      
      // Handle datetime columns
      const dateTimeColumns = ['created_at', 'updated_at', 'expires_at', 'due_date', 'completed_at', 'start_date', 'end_date'];
      dateTimeColumns.forEach(col => {
        if (processedRow[col]) {
          processedRow[col] = convertDateTime(processedRow[col]);
        }
      });
      
      const values = columns.map(col => processedRow[col]);
      const result = await connection.execute(insertSQL, values);
      insertedCount += result[0].affectedRows;
    }
    
    console.log(`✅ Migrated ${insertedCount} new rows to ${tableName} (ignored ${data.length - insertedCount} duplicates)`);
  } catch (error) {
    console.error(`❌ Failed to migrate data to ${tableName}:`, error.message);
    throw error;
  }
};

// Main migration function
const migrateSQLiteToMySQL = async () => {
  console.log('🔄 Starting SQLite to MySQL migration...');
  
  // Connect to SQLite
  const sqliteDb = new sqlite3.Database(sqliteDbPath, (err) => {
    if (err) {
      console.error('❌ Error opening SQLite database:', err.message);
      throw err;
    } else {
      console.log('✅ Connected to SQLite database');
    }
  });
  
  // Connect to MySQL
  const mysqlPool = mysql.createPool(mysqlConfig);
  console.log('✅ Connected to MySQL database');
  
  try {
    const connection = await mysqlPool.getConnection();
    
    // List of tables to migrate (in order of dependencies)
    const tables = [
      'companies',
      'users',
      'company_users',
      'otps',
      'refresh_tokens',
      'projects',
      'project_members',
      'project_permissions',
      'sprints',
      'tasks',
      'task_assignments',
      'user_permissions',
      'activity_logs'
    ];
    
    // Migrate each table
    for (const tableName of tables) {
      try {
        console.log(`🔄 Migrating table: ${tableName}...`);
        const data = await getAllFromSQLiteTable(sqliteDb, tableName);
        await insertIntoMySQLTable(connection, tableName, data);
      } catch (error) {
        if (error.message.includes('no such table')) {
          console.log(`⚠️  Table ${tableName} does not exist in SQLite, skipping...`);
        } else {
          console.error(`❌ Error migrating table ${tableName}:`, error.message);
          throw error;
        }
      }
    }
    
    connection.release();
    console.log('✅ All tables migrated successfully');
  } catch (error) {
    console.error('❌ Migration failed:', error.message);
    throw error;
  } finally {
    // Close connections
    sqliteDb.close((err) => {
      if (err) {
        console.error('❌ Error closing SQLite database:', err.message);
      } else {
        console.log('✅ SQLite database connection closed');
      }
    });
    
    await mysqlPool.end();
    console.log('✅ MySQL connection pool closed');
  }
};

// Run migration if this script is executed directly
if (require.main === module) {
  migrateSQLiteToMySQL()
    .then(() => {
      console.log('🎉 SQLite to MySQL migration completed successfully!');
      process.exit(0);
    })
    .catch((error) => {
      console.error('💥 Migration failed:', error.message);
      process.exit(1);
    });
}

module.exports = { migrateSQLiteToMySQL };