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

// Test SQLite database path
const testSqliteDbPath = path.join(__dirname, '..', 'test-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
};

// Create test SQLite database with sample data
const createTestSQLiteDB = () => {
  return new Promise((resolve, reject) => {
    console.log('🔄 Creating test SQLite database...');
    
    // Remove existing test database if it exists
    if (fs.existsSync(testSqliteDbPath)) {
      fs.unlinkSync(testSqliteDbPath);
    }
    
    const db = new sqlite3.Database(testSqliteDbPath, (err) => {
      if (err) {
        console.error('❌ Error opening SQLite database:', err.message);
        reject(err);
      } else {
        console.log('✅ Connected to test SQLite database');
      }
    });
    
    // Create tables
    const createTablesSQL = `
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        phone_number TEXT UNIQUE NOT NULL,
        first_name TEXT,
        last_name TEXT,
        role TEXT DEFAULT 'user',
        permission_level INTEGER DEFAULT 1,
        company_id INTEGER,
        status TEXT DEFAULT 'active',
        profile_image TEXT,
        created_by INTEGER,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      );

      CREATE TABLE IF NOT EXISTS companies (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        description TEXT,
        admin_phone TEXT NOT NULL,
        status TEXT DEFAULT 'active',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      );

      CREATE TABLE IF NOT EXISTS projects (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        description TEXT,
        company_id INTEGER,
        status TEXT DEFAULT 'planning',
        priority TEXT DEFAULT 'medium',
        start_date DATE,
        end_date DATE,
        budget DECIMAL(15,2),
        owner_id INTEGER NOT NULL,
        created_by INTEGER NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      );
    `;
    
    db.exec(createTablesSQL, (err) => {
      if (err) {
        console.error('❌ Error creating tables:', err.message);
        reject(err);
      } else {
        console.log('✅ Test tables created successfully');
        
        // Insert sample data
        const sampleDataSQL = `
          INSERT INTO companies (id, name, description, admin_phone, status)
          VALUES 
            (1, 'Test Company', 'A test company for migration', '1234567890', 'active'),
            (2, 'Another Company', 'Another test company', '0987654321', 'active');

          INSERT INTO users (id, username, phone_number, first_name, last_name, role, permission_level, company_id, status, created_by)
          VALUES 
            (1, 'admin_user', '1234567890', 'Admin', 'User', 'admin', 2, 1, 'active', NULL),
            (2, 'regular_user', '0987654321', 'Regular', 'User', 'user', 1, 1, 'active', 1),
            (3, 'another_user', '1122334455', 'Another', 'User', 'user', 1, 2, 'active', 1);

          INSERT INTO projects (id, name, description, company_id, status, priority, start_date, end_date, budget, owner_id, created_by)
          VALUES 
            (1, 'Test Project 1', 'A test project', 1, 'active', 'high', '2023-01-01', '2023-12-31', 10000.00, 1, 1),
            (2, 'Test Project 2', 'Another test project', 2, 'planning', 'medium', '2023-06-01', '2024-06-30', 5000.00, 1, 1);
        `;
        
        db.exec(sampleDataSQL, (err) => {
          if (err) {
            console.error('❌ Error inserting sample data:', err.message);
            reject(err);
          } else {
            console.log('✅ Sample data inserted successfully');
            db.close((err) => {
              if (err) {
                console.error('❌ Error closing SQLite database:', err.message);
                reject(err);
              } else {
                console.log('✅ Test SQLite database created and populated');
                resolve();
              }
            });
          }
        });
      }
    });
  });
};

// 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 get row count from MySQL table
const getMySQLTableRowCount = async (pool, tableName) => {
  const [rows] = await pool.execute(`SELECT COUNT(*) as count FROM ${tableName}`);
  return rows[0].count;
};

// Function to verify data integrity
const verifyDataIntegrity = async (sqliteDb, mysqlPool) => {
  console.log('🔄 Verifying data integrity...');
  
  const tables = ['users', 'companies', 'projects'];
  
  for (const tableName of tables) {
    try {
      // Get data from SQLite
      const sqliteData = await getAllFromSQLiteTable(sqliteDb, tableName);
      const sqliteCount = sqliteData.length;
      
      // Get count from MySQL
      const mysqlCount = await getMySQLTableRowCount(mysqlPool, tableName);
      
      console.log(`📋 Table ${tableName}: SQLite=${sqliteCount}, MySQL=${mysqlCount}`);
      
      if (sqliteCount !== mysqlCount) {
        throw new Error(`Data mismatch in table ${tableName}: SQLite=${sqliteCount}, MySQL=${mysqlCount}`);
      }
      
      console.log(`✅ Data integrity verified for table ${tableName}`);
    } catch (error) {
      console.error(`❌ Error verifying data integrity for table ${tableName}:`, error.message);
      throw error;
    }
  }
  
  console.log('🎉 All data integrity checks passed!');
};

// Main test function
const testMigration = async () => {
  console.log('🔄 Starting migration test...');
  
  let sqliteDb = null;
  let mysqlPool = null;
  
  try {
    // Step 1: Create test SQLite database
    await createTestSQLiteDB();
    
    // Step 2: Connect to SQLite
    sqliteDb = new sqlite3.Database(testSqliteDbPath, (err) => {
      if (err) {
        console.error('❌ Error opening test SQLite database:', err.message);
        throw err;
      } else {
        console.log('✅ Connected to test SQLite database for migration');
      }
    });
    
    // Step 3: Connect to MySQL
    mysqlPool = mysql.createPool(mysqlConfig);
    console.log('✅ Connected to MySQL database');
    
    // Step 4: Test our migration function
    // We'll simulate the migration by directly transferring data
    
    // List of tables to migrate (in order of dependencies)
    const tables = ['companies', 'users', 'projects'];
    
    // Migrate each table
    for (const tableName of tables) {
      try {
        console.log(`🔄 Migrating table: ${tableName}...`);
        const data = await getAllFromSQLiteTable(sqliteDb, tableName);
        
        if (!data || data.length === 0) {
          console.log(`⚠️  No data to migrate for table: ${tableName}`);
          continue;
        }
        
        // 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(', ');
        
        // Prepare the INSERT statement
        const insertSQL = `INSERT INTO \`${tableName}\` (${columnNames}) VALUES (${placeholders})`;
        
        // Insert each row
        const connection = await mysqlPool.getConnection();
        try {
          await connection.beginTransaction();
          
          for (const row of data) {
            const values = columns.map(col => row[col]);
            await connection.execute(insertSQL, values);
          }
          
          await connection.commit();
          console.log(`✅ Migrated ${data.length} rows to ${tableName}`);
        } catch (error) {
          await connection.rollback();
          console.error(`❌ Failed to migrate data to ${tableName}:`, error.message);
          throw error;
        } finally {
          connection.release();
        }
      } 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;
        }
      }
    }
    
    // Step 5: Verify data integrity
    await verifyDataIntegrity(sqliteDb, mysqlPool);
    
    console.log('🎉 Migration test completed successfully!');
    return true;
  } catch (error) {
    console.error('❌ Migration test failed:', error.message);
    return false;
  } finally {
    // Clean up connections
    if (sqliteDb) {
      sqliteDb.close((err) => {
        if (err) {
          console.error('❌ Error closing SQLite database:', err.message);
        } else {
          console.log('✅ SQLite database connection closed');
        }
      });
    }
    
    if (mysqlPool) {
      await mysqlPool.end();
      console.log('✅ MySQL connection pool closed');
    }
    
    // Remove test database file
    if (fs.existsSync(testSqliteDbPath)) {
      fs.unlinkSync(testSqliteDbPath);
      console.log('✅ Test SQLite database file removed');
    }
  }
};

// Run test if this script is executed directly
if (require.main === module) {
  testMigration()
    .then((success) => {
      if (success) {
        console.log('✅ Migration test completed successfully');
        process.exit(0);
      } else {
        console.log('❌ Migration test failed');
        process.exit(1);
      }
    })
    .catch((error) => {
      console.error('💥 Migration test failed with error:', error.message);
      process.exit(1);
    });
}

module.exports = { testMigration };