backupService/upload.js

require('dotenv').config();
const sql = require('mssql');
const fs = require('fs');
const path = require('path');
const { sendTelegramBroadcast, formatBytes } = require('./index.js');

// MSSQL Configuration - allow override via command line arguments
const getConfig = () => {
  // Check for command line arguments to override server/port
  const args = process.argv.slice(2);
  let serverOverride = null;
  let portOverride = null;
  
  args.forEach((arg, index) => {
    if (arg === '--server' && args[index + 1]) {
      serverOverride = args[index + 1];
    }
    if (arg === '--port' && args[index + 1]) {
      portOverride = parseInt(args[index + 1]);
    }
  });

  return {
    user: process.env.MSSQL_USER,
    password: process.env.MSSQL_PASSWORD,
    server: serverOverride || process.env.MSSQL_SERVER,
    port: portOverride || parseInt(process.env.MSSQL_PORT) || 1433,
    database: 'master', // Connect to master for restore operations
    requestTimeout: 3600000, // 1 hour for restore operations
    options: {
      encrypt: false,
      trustServerCertificate: true
    }
  };
};

// Function to check if backup file exists
function checkBackupFile(backupPath) {
  if (!fs.existsSync(backupPath)) {
    throw new Error(`Backup file not found: ${backupPath}`);
  }
  
  const stats = fs.statSync(backupPath);
  console.log(`Backup file found: ${backupPath}`);
  console.log(`File size: ${formatBytes(stats.size)}`);
  return stats;
}


// Function to get database name from backup file
async function getDatabaseNameFromBackup(pool, backupPath) {
  try {
    console.log('Reading database name from backup file...');
    
    const query = `
      RESTORE HEADERONLY 
      FROM DISK = @backupPath
    `;
    
    const request = pool.request();
    request.input('backupPath', sql.NVarChar, backupPath);
    const result = await request.query(query);
    
    if (result.recordset && result.recordset.length > 0) {
      const databaseName = result.recordset[0].DatabaseName;
      console.log(`Database name from backup: ${databaseName}`);
      return databaseName;
    } else {
      throw new Error('Unable to read database name from backup file');
    }
  } catch (err) {
    console.error('Error reading backup header:', err.message);
    throw err;
  }
}

// Function to get logical file names from backup
async function getLogicalFileNames(pool, backupPath) {
  try {
    console.log('Reading logical file names from backup...');
    
    const query = `
      RESTORE FILELISTONLY 
      FROM DISK = @backupPath
    `;
    
    const request = pool.request();
    request.input('backupPath', sql.NVarChar, backupPath);
    const result = await request.query(query);
    
    if (result.recordset && result.recordset.length > 0) {
      const files = result.recordset.map(file => ({
        LogicalName: file.LogicalName,
        Type: file.Type,
        PhysicalName: file.PhysicalName
      }));
      
      console.log('Logical files in backup:');
      files.forEach(file => {
        console.log(`  - ${file.LogicalName} (${file.Type}): ${file.PhysicalName}`);
      });
      
      return files;
    } else {
      throw new Error('Unable to read file list from backup');
    }
  } catch (err) {
    console.error('Error reading backup file list:', err.message);
    throw err;
  }
}

// Function to restore database using the proven working SQL commands
async function restoreDatabase(pool, backupPath, databaseName) {
  try {
    console.log(`Starting restore of database: ${databaseName}`);
    
    // Step 1: Set database to single user mode (this kills connections)
    console.log('Setting database to single-user mode and killing connections...');
    const singleUserQuery = `ALTER DATABASE [${databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE`;
    
    try {
      const singleUserRequest = pool.request();
      singleUserRequest.timeout = 60000; // 1 minute for preparation
      await singleUserRequest.query(singleUserQuery);
      console.log('Database set to single-user mode');
    } catch (err) {
      // Database might not exist yet, that's okay
      console.log('Note: Could not set to single-user mode (database may not exist yet)');
    }
    
    // Step 2: Execute the restore with proven working parameters
    console.log('Executing restore command with tested parameters...');
    console.log('Note: This operation may take several minutes depending on database size.');
    
    const restoreQuery = `
      RESTORE DATABASE [${databaseName}] 
      FROM DISK = @backupPath 
      WITH FILE = 1,  
           MOVE N'eazybusiness' TO N'/var/opt/mssql/data/eazybusiness.mdf',  
           MOVE N'eazybusiness_log' TO N'/var/opt/mssql/data/eazybusiness_log.ldf',  
           NOUNLOAD,  
           REPLACE,  
           STATS = 5
    `;
    
    const request = pool.request();
    request.input('backupPath', sql.NVarChar, backupPath);
    
    // Set longer timeout for restore operations (in milliseconds)
    request.timeout = 3600000; // 1 hour
    
    // Also set pool timeout
    pool.config.requestTimeout = 3600000;
    
    const result = await request.query(restoreQuery);
    
    console.log('Database restore completed successfully!');
    
    // Step 3: Set database back to multi user mode
    console.log('Setting database back to multi-user mode...');
    const multiUserQuery = `ALTER DATABASE [${databaseName}] SET MULTI_USER`;
    const multiUserRequest = pool.request();
    multiUserRequest.timeout = 60000;
    await multiUserRequest.query(multiUserQuery);
    
    console.log('Database is now ready for use!');
    return result;
    
  } catch (err) {
    console.error('Error during database restore:', err.message);
    
    // Try to set database back to multi-user mode even if restore failed
    try {
      console.log('Attempting to set database back to multi-user mode...');
      const multiUserQuery = `ALTER DATABASE [${databaseName}] SET MULTI_USER`;
      const multiUserRequest = pool.request();
      multiUserRequest.timeout = 60000;
      await multiUserRequest.query(multiUserQuery);
    } catch (cleanupErr) {
      console.warn('Could not reset database to multi-user mode:', cleanupErr.message);
    }
    
    throw err;
  }
}

// Main restore function
async function restoreBackupFile(backupFileName = 'ez.bak', sqlServerPath = null) {
  let pool = null;
  
  try {
    const config = getConfig();
    console.log(`Connecting to SQL Server: ${config.server}:${config.port}`);
    console.log(`User: ${config.user}`);
    
    let fileStats = null;
    let serverBackupPath;
    
    if (sqlServerPath) {
      // Using remote path - no local validation needed
      serverBackupPath = sqlServerPath;
      console.log(`SQL Server will access backup from: ${serverBackupPath}`);
    } else {
      // Using local path - validate it exists
      const localBackupPath = path.resolve(backupFileName);
      fileStats = checkBackupFile(localBackupPath);
      serverBackupPath = localBackupPath;
      console.log(`SQL Server will access backup from: ${serverBackupPath}`);
    }
    
    // Connect to SQL Server
    pool = await sql.connect(config);
    console.log('Connected to SQL Server successfully');
    
    // Get database name from backup
    const databaseName = await getDatabaseNameFromBackup(pool, serverBackupPath);
    
    // Perform the restore
    await restoreDatabase(pool, serverBackupPath, databaseName);
    
    console.log('Restore process completed successfully!');
    console.log(`Database '${databaseName}' has been restored from ${backupFileName}`);
    
    // Send success notification
    const when = new Date().toISOString();
    const sizeInfo = fileStats ? `\nSize: ${formatBytes(fileStats.size)}` : '';
    const msg = `Database restore completed ✅\nDB: ${databaseName}\nFrom: ${serverBackupPath}${sizeInfo}\nServer: ${config.server}:${config.port}\nTime: ${when}`;
    await sendTelegramBroadcast('all', msg);
    
    return {
      success: true,
      databaseName,
      server: `${config.server}:${config.port}`,
      backupFile: serverBackupPath,
      fileSize: fileStats ? fileStats.size : null
    };
    
  } catch (err) {
    console.error('Restore process failed:', err.message);
    
    // Send error notification
    const config = getConfig();
    const when = new Date().toISOString();
    const msg = `Database restore failed 🔴\nFile: ${backupFileName}\nServer: ${config.server}:${config.port}\nTime: ${when}\nError: ${err.message}`;
    await sendTelegramBroadcast('errors', msg);
    
    throw err;
  } finally {
    // Always close the connection
    if (pool) {
      try {
        await pool.close();
        console.log('Database connection closed');
      } catch (closeErr) {
        console.warn('Error closing database connection:', closeErr.message);
      }
    }
  }
}

// Run if called directly
if (require.main === module) {
  // Parse command line arguments
  const args = process.argv.slice(2);
  let backupFile = 'ez.bak';
  let sqlServerPath = null;
  
  // Look for backup file argument (first non-flag argument)
  const fileArg = args.find(arg => !arg.startsWith('--'));
  if (fileArg) {
    backupFile = fileArg;
  }
  
  // Look for SQL Server path argument
  const pathIndex = args.indexOf('--sql-path');
  if (pathIndex !== -1 && args[pathIndex + 1]) {
    sqlServerPath = args[pathIndex + 1];
  }
  
  console.log('='.repeat(50));
  console.log('DATABASE RESTORE UTILITY');
  console.log('='.repeat(50));
  console.log(`Backup file: ${backupFile}`);
  if (sqlServerPath) {
    console.log(`SQL Server path: ${sqlServerPath}`);
  } else {
    console.log('WARNING: Using local path - SQL Server on remote host may not access it!');
    console.log('Use --sql-path to specify the path as SQL Server sees it');
  }
  console.log('Starting restore process...');
  console.log('');
  
  restoreBackupFile(backupFile, sqlServerPath)
    .then((result) => {
      console.log('');
      console.log('='.repeat(50));
      console.log('RESTORE COMPLETED SUCCESSFULLY');
      console.log('='.repeat(50));
      console.log(`Database: ${result.databaseName}`);
      console.log(`Server: ${result.server}`);
      console.log(`File: ${result.backupFile}`);
      if (result.fileSize) {
        console.log(`Size: ${formatBytes(result.fileSize)}`);
      }
      process.exit(0);
    })
    .catch((error) => {
      console.error('');
      console.error('='.repeat(50));
      console.error('RESTORE FAILED');
      console.error('='.repeat(50));
      console.error('Error:', error.message);
      console.error('');
      console.error('TROUBLESHOOTING:');
      console.error('- If SQL Server is on a remote host, it cannot access local files');
      console.error('- Use --sql-path to specify the path as SQL Server sees it');
      console.error('- Example: --sql-path "F:\\ez.bak"');
      process.exit(1);
    });
}

module.exports = { restoreBackupFile, getConfig };