/**
 * Database Manager for MCP SSH Manager
 * Provides database operations for MySQL, PostgreSQL, and MongoDB
 */

// Supported database types
export const DB_TYPES = {
  MYSQL: 'mysql',
  POSTGRESQL: 'postgresql',
  MONGODB: 'mongodb'
};

// Default ports
export const DB_PORTS = {
  mysql: 3306,
  postgresql: 5432,
  mongodb: 27017
};

/**
 * Build MySQL dump command
 */
export function buildMySQLDumpCommand(options) {
  const {
    database,
    user,
    password,
    host = 'localhost',
    port = 3306,
    outputFile,
    compress = true,
    tables = null
  } = options;

  let command = 'mysqldump';

  if (user) command += ` -u${user}`;
  if (password) command += ` -p'${password}'`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -P ${port}`;

  command += ' --single-transaction --routines --triggers';
  command += ` ${database}`;

  if (tables && Array.isArray(tables)) {
    command += ` ${tables.join(' ')}`;
  }

  if (compress) {
    command += ` | gzip > "${outputFile}"`;
  } else {
    command += ` > "${outputFile}"`;
  }

  return command;
}

/**
 * Build PostgreSQL dump command
 */
export function buildPostgreSQLDumpCommand(options) {
  const {
    database,
    user,
    password,
    host = 'localhost',
    port = 5432,
    outputFile,
    compress = true,
    tables = null
  } = options;

  let command = '';
  if (password) {
    command = `PGPASSWORD='${password}' `;
  }

  command += 'pg_dump';
  if (user) command += ` -U ${user}`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -p ${port}`;
  command += ' --format=custom --clean --if-exists';

  if (tables && Array.isArray(tables)) {
    for (const table of tables) {
      command += ` -t ${table}`;
    }
  }

  command += ` ${database}`;

  if (compress) {
    command += ` | gzip > "${outputFile}"`;
  } else {
    command += ` > "${outputFile}"`;
  }

  return command;
}

/**
 * Build MongoDB dump command
 */
export function buildMongoDBDumpCommand(options) {
  const {
    database,
    user,
    password,
    host = 'localhost',
    port = 27017,
    outputDir,
    compress = true,
    collections = null
  } = options;

  let command = 'mongodump';
  if (host) command += ` --host ${host}`;
  if (port) command += ` --port ${port}`;
  if (user) command += ` --username ${user}`;
  if (password) command += ` --password '${password}'`;
  if (database) command += ` --db ${database}`;

  if (collections && Array.isArray(collections)) {
    for (const collection of collections) {
      command += ` --collection ${collection}`;
    }
  }

  command += ` --out "${outputDir}"`;

  if (compress) {
    command += ` && tar -czf "${outputDir}.tar.gz" -C "$(dirname ${outputDir})" "$(basename ${outputDir})"`;
    command += ` && rm -rf "${outputDir}"`;
  }

  return command;
}

/**
 * Build MySQL import command
 */
export function buildMySQLImportCommand(options) {
  const {
    database,
    user,
    password,
    host = 'localhost',
    port = 3306,
    inputFile
  } = options;

  let command = '';

  if (inputFile.endsWith('.gz')) {
    command = `gunzip -c "${inputFile}" | `;
  } else {
    command = `cat "${inputFile}" | `;
  }

  command += 'mysql';
  if (user) command += ` -u${user}`;
  if (password) command += ` -p'${password}'`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -P ${port}`;
  command += ` ${database}`;

  return command;
}

/**
 * Build PostgreSQL import command
 */
export function buildPostgreSQLImportCommand(options) {
  const {
    database,
    user,
    password,
    host = 'localhost',
    port = 5432,
    inputFile
  } = options;

  let command = '';
  if (password) {
    command = `PGPASSWORD='${password}' `;
  }

  command += 'pg_restore';
  if (user) command += ` -U ${user}`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -p ${port}`;
  command += ' --clean --if-exists';
  command += ` -d ${database}`;

  if (inputFile.endsWith('.gz')) {
    command = `gunzip -c "${inputFile}" | ${command}`;
  } else {
    command += ` "${inputFile}"`;
  }

  return command;
}

/**
 * Build MongoDB restore command
 */
export function buildMongoDBRestoreCommand(options) {
  const {
    database,
    user,
    password,
    host = 'localhost',
    port = 27017,
    inputPath,
    drop = true
  } = options;

  let command = '';

  if (inputPath.endsWith('.tar.gz')) {
    const extractDir = inputPath.replace('.tar.gz', '');
    command = `tar -xzf "${inputPath}" -C "$(dirname ${inputPath})" && `;
    command += 'mongorestore';
    if (drop) command += ' --drop';
    if (host) command += ` --host ${host}`;
    if (port) command += ` --port ${port}`;
    if (user) command += ` --username ${user}`;
    if (password) command += ` --password '${password}'`;
    command += ` "${extractDir}"`;
    command += ` && rm -rf "${extractDir}"`;
  } else {
    command = 'mongorestore';
    if (drop) command += ' --drop';
    if (host) command += ` --host ${host}`;
    if (port) command += ` --port ${port}`;
    if (user) command += ` --username ${user}`;
    if (password) command += ` --password '${password}'`;
    command += ` "${inputPath}"`;
  }

  return command;
}

/**
 * Build MySQL list databases command
 */
export function buildMySQLListDatabasesCommand(options) {
  const { user, password, host = 'localhost', port = 3306 } = options;

  let command = 'mysql';
  if (user) command += ` -u${user}`;
  if (password) command += ` -p'${password}'`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -P ${port}`;
  command += ' -e "SHOW DATABASES;" | tail -n +2';

  return command;
}

/**
 * Build MySQL list tables command
 */
export function buildMySQLListTablesCommand(options) {
  const { database, user, password, host = 'localhost', port = 3306 } = options;

  let command = 'mysql';
  if (user) command += ` -u${user}`;
  if (password) command += ` -p'${password}'`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -P ${port}`;
  command += ` -e "USE ${database}; SHOW TABLES;" | tail -n +2`;

  return command;
}

/**
 * Build PostgreSQL list databases command
 */
export function buildPostgreSQLListDatabasesCommand(options) {
  const { user, password, host = 'localhost', port = 5432 } = options;

  let command = '';
  if (password) {
    command = `PGPASSWORD='${password}' `;
  }

  command += 'psql';
  if (user) command += ` -U ${user}`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -p ${port}`;
  command += ' -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" | sed \'/^$/d\' | sed \'s/^[ \\t]*//\'';

  return command;
}

/**
 * Build PostgreSQL list tables command
 */
export function buildPostgreSQLListTablesCommand(options) {
  const { database, user, password, host = 'localhost', port = 5432 } = options;

  let command = '';
  if (password) {
    command = `PGPASSWORD='${password}' `;
  }

  command += 'psql';
  if (user) command += ` -U ${user}`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -p ${port}`;
  command += ` -d ${database}`;
  command += ' -t -c "SELECT tablename FROM pg_tables WHERE schemaname = \'public\';" | sed \'/^$/d\' | sed \'s/^[ \\t]*//\'';

  return command;
}

/**
 * Build MongoDB list databases command
 */
export function buildMongoDBListDatabasesCommand(options) {
  const { user, password, host = 'localhost', port = 27017 } = options;

  let command = 'mongo';
  if (host) command += ` --host ${host}`;
  if (port) command += ` --port ${port}`;
  if (user) command += ` --username ${user}`;
  if (password) command += ` --password '${password}'`;
  command += ' --quiet --eval "db.adminCommand(\'listDatabases\').databases.forEach(function(d){print(d.name)})"';

  return command;
}

/**
 * Build MongoDB list collections command
 */
export function buildMongoDBListCollectionsCommand(options) {
  const { database, user, password, host = 'localhost', port = 27017 } = options;

  let command = 'mongo';
  if (host) command += ` --host ${host}`;
  if (port) command += ` --port ${port}`;
  if (user) command += ` --username ${user}`;
  if (password) command += ` --password '${password}'`;
  command += ` ${database}`;
  command += ' --quiet --eval "db.getCollectionNames().forEach(function(c){print(c)})"';

  return command;
}

/**
 * Build MySQL query command (SELECT only)
 */
export function buildMySQLQueryCommand(options) {
  const { database, query, user, password, host = 'localhost', port = 3306, format = 'json' } = options;

  // Validate query is SELECT only
  if (!isSafeQuery(query)) {
    throw new Error('Only SELECT queries are allowed');
  }

  let command = 'mysql';
  if (user) command += ` -u${user}`;
  if (password) command += ` -p'${password}'`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -P ${port}`;
  command += ` ${database}`;

  if (format === 'json') {
    // Use JSON output if MySQL 5.7.8+
    command += ` -e "${query}" --batch --skip-column-names | awk 'BEGIN{print "["} {if(NR>1)print ","; printf "{\\"row\\":%d,\\"data\\":\\"%s\\"}", NR, $0} END{print "]"}'`;
  } else {
    command += ` -e "${query}"`;
  }

  return command;
}

/**
 * Build PostgreSQL query command (SELECT only)
 */
export function buildPostgreSQLQueryCommand(options) {
  const { database, query, user, password, host = 'localhost', port = 5432 } = options;

  if (!isSafeQuery(query)) {
    throw new Error('Only SELECT queries are allowed');
  }

  let command = '';
  if (password) {
    command = `PGPASSWORD='${password}' `;
  }

  command += 'psql';
  if (user) command += ` -U ${user}`;
  if (host) command += ` -h ${host}`;
  if (port) command += ` -p ${port}`;
  command += ` -d ${database}`;
  command += ` -c "${query}"`;

  return command;
}

/**
 * Build MongoDB query command
 */
export function buildMongoDBQueryCommand(options) {
  const { database, collection, query, user, password, host = 'localhost', port = 27017 } = options;

  let command = 'mongo';
  if (host) command += ` --host ${host}`;
  if (port) command += ` --port ${port}`;
  if (user) command += ` --username ${user}`;
  if (password) command += ` --password '${password}'`;
  command += ` ${database}`;
  command += ` --quiet --eval "db.${collection}.find(${query || '{}'}).forEach(printjson)"`;

  return command;
}

/**
 * Validate query is safe (SELECT only)
 */
export function isSafeQuery(query) {
  const trimmedQuery = query.trim().toLowerCase();

  // Must start with SELECT
  if (!trimmedQuery.startsWith('select')) {
    return false;
  }

  // Block dangerous keywords
  const dangerousKeywords = [
    'insert', 'update', 'delete', 'drop', 'create', 'alter',
    'truncate', 'grant', 'revoke', 'exec', 'execute'
  ];

  for (const keyword of dangerousKeywords) {
    if (trimmedQuery.includes(keyword)) {
      return false;
    }
  }

  return true;
}

/**
 * Parse database list output
 */
export function parseDatabaseList(output, type) {
  const lines = output.trim().split('\n').filter(l => l.trim());

  // Filter out system databases
  return lines.filter(db => {
    const dbLower = db.toLowerCase();
    if (type === DB_TYPES.MYSQL) {
      return !['information_schema', 'performance_schema', 'mysql', 'sys'].includes(dbLower);
    } else if (type === DB_TYPES.POSTGRESQL) {
      return !['template0', 'template1', 'postgres'].includes(dbLower);
    } else if (type === DB_TYPES.MONGODB) {
      return !['admin', 'config', 'local'].includes(dbLower);
    }
    return true;
  });
}

/**
 * Parse table/collection list output
 */
export function parseTableList(output) {
  return output.trim().split('\n').filter(l => l.trim());
}

/**
 * Estimate dump size command
 */
export function buildEstimateSizeCommand(type, database, options = {}) {
  const { user, password, host = 'localhost', port } = options;

  switch (type) {
  case DB_TYPES.MYSQL: {
    let command = 'mysql';
    if (user) command += ` -u${user}`;
    if (password) command += ` -p'${password}'`;
    if (host) command += ` -h ${host}`;
    if (port) command += ` -P ${port}`;
    command += ` -e "SELECT SUM(data_length + index_length) FROM information_schema.TABLES WHERE table_schema='${database}';" | tail -n 1`;
    return command;
  }

  case DB_TYPES.POSTGRESQL: {
    let command = '';
    if (password) {
      command = `PGPASSWORD='${password}' `;
    }
    command += 'psql';
    if (user) command += ` -U ${user}`;
    if (host) command += ` -h ${host}`;
    if (port) command += ` -p ${port}`;
    command += ` -d ${database}`;
    command += ` -t -c "SELECT pg_database_size('${database}');" | sed 's/^[ \\t]*//'`;
    return command;
  }

  case DB_TYPES.MONGODB: {
    let command = 'mongo';
    if (host) command += ` --host ${host}`;
    if (port) command += ` --port ${port}`;
    if (user) command += ` --username ${user}`;
    if (password) command += ` --password '${password}'`;
    command += ` ${database}`;
    command += ' --quiet --eval "db.stats().dataSize"';
    return command;
  }

  default:
    throw new Error(`Unknown database type: ${type}`);
  }
}

/**
 * Parse size output to bytes
 */
export function parseSize(output) {
  const size = parseInt(output.trim());
  return isNaN(size) ? 0 : size;
}

/**
 * Format bytes to human readable
 */
export function formatBytes(bytes) {
  if (bytes === 0) return '0 Bytes';
  const k = 1024;
  const sizes = ['Bytes', 'KB', 'MB', 'GB', 'TB'];
  const i = Math.floor(Math.log(bytes) / Math.log(k));
  return parseFloat((bytes / Math.pow(k, i)).toFixed(2)) + ' ' + sizes[i];
}

/**
 * Get database connection info
 */
export function getConnectionInfo(type, options) {
  const { host = 'localhost', port, user, database } = options;
  const defaultPort = DB_PORTS[type];

  return {
    type,
    host,
    port: port || defaultPort,
    user: user || 'default',
    database: database || 'all'
  };
}
