require('dotenv').config();
const sql = require('mssql');
// Replace aws-sdk v2 with modular v3 S3 client
const { S3Client, PutObjectCommand, ListObjectsV2Command } = require('@aws-sdk/client-s3');
const fs = require('fs');
const path = require('path');
const os = require('os');
const zlib = require('zlib');
const { pipeline } = require('stream/promises');
const SambaClient = require('samba-client');
const { Client } = require('ssh2');

// AWS S3 Configuration (v3 client)
const s3 = new S3Client({
  region: process.env.AWS_REGION,
  // Use explicit credentials if provided; otherwise let default provider chain resolve
  credentials: (process.env.AWS_ACCESS_KEY_ID && process.env.AWS_SECRET_ACCESS_KEY)
    ? {
        accessKeyId: process.env.AWS_ACCESS_KEY_ID,
        secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY,
      }
    : undefined,
});

// MSSQL Configuration
const config = {
  user: process.env.MSSQL_USER,
  password: process.env.MSSQL_PASSWORD,
  server: process.env.MSSQL_SERVER,
  port: parseInt(process.env.MSSQL_PORT) || 1433,
  database: process.env.MSSQL_DATABASE,
  options: {
    encrypt: false, // Use this if you're on Windows Azure
    trustServerCertificate: true // Change to true for local dev / self-signed certs
  }
};

// Backup file path
const backupFilePath = process.env.BACKUP_FILE_PATH;

// SCP Configuration (SSH key authentication)
const scpConfig = {
  host: process.env.SCP_HOST,
  port: parseInt(process.env.SCP_PORT) || 22,
  username: process.env.SCP_USERNAME,
  privateKey: fs.readFileSync(path.resolve(process.env.SCP_KEY_PATH.replace(/^~/, os.homedir())))
};

// Legacy SMB Configuration (deprecated)
const smbConfig = {
  address: process.env.SMB_ADDRESS,
  username: process.env.SMB_USERNAME,
  password: process.env.SMB_PASSWORD,
  domain: process.env.SMB_DOMAIN || '', // optional
};

// Ensure download directory exists
const downloadFile = process.env.SCP_REMOTE_PATH || process.env.SMB_DOWNLOAD_FILE;
const localDownloadFile = process.env.SCP_LOCAL_PATH || process.env.SMB_LOCAL_DOWNLOAD_FILE;


// Admin Telegram Broadcast (env-configured)
const BASE_URL = (process.env.BASE_URL || '').trim();
const ADMIN_API_KEY = (process.env.ADMIN_API_KEY || '').trim();

async function sendTelegramBroadcast(target, message) {
  try {
    if (!BASE_URL || !ADMIN_API_KEY) {
      console.warn('Skipping admin broadcast: missing BASE_URL or ADMIN_API_KEY', { hasBaseUrl: !!BASE_URL, hasKey: !!ADMIN_API_KEY });
      return null;
    }
    const endpoint = `${BASE_URL.replace(/\/$/, '')}/api/admin/telegram/broadcast`;
    console.log(`Broadcast → ${endpoint} target=${target}`);

    const res = await fetch(endpoint, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'X-API-Key': ADMIN_API_KEY,
      },
      body: JSON.stringify({ target, message }),
    });

    let bodyText = '';
    try { bodyText = await res.text(); } catch {}

    if (!res.ok) {
      let parsed;
      try { parsed = JSON.parse(bodyText); } catch {}
      const detail = parsed && parsed.error ? parsed.error : (bodyText ? bodyText.slice(0, 300) : '');
      throw new Error(`Broadcast failed: ${res.status} ${res.statusText} - ${detail}`);
    }

    return bodyText ? JSON.parse(bodyText) : {};
  } catch (e) {
    console.error('Admin broadcast error:', e.message);
    return null;
  }
}

// Helper to format bytes to human-readable
function formatBytes(bytes) {
  if (bytes === 0 || bytes == null) return '0 B';
  const k = 1024;
  const sizes = ['B', 'KB', 'MB', 'GB', 'TB'];
  const i = Math.floor(Math.log(bytes) / Math.log(k));
  const val = bytes / Math.pow(k, i);
  return `${val.toFixed(val >= 100 ? 0 : val >= 10 ? 1 : 2)} ${sizes[i]}`;
}

// Query DB sizes (data + log) in bytes
async function getDbSizeBytes() {
  try {
    // Assumes active connection exists
    const dbName = process.env.MSSQL_DATABASE;
    const sizeQuery = `
      ;WITH s AS (
        SELECT
          type_desc,
          size_mb = SUM(size) * 8.0 / 1024.0
        FROM sys.master_files
        WHERE database_id = DB_ID(@db)
        GROUP BY type_desc
      )
      SELECT
        data_bytes = CAST(COALESCE((SELECT size_mb FROM s WHERE type_desc = 'ROWS'), 0) * 1024 * 1024 AS BIGINT),
        log_bytes  = CAST(COALESCE((SELECT size_mb FROM s WHERE type_desc = 'LOG'), 0) * 1024 * 1024 AS BIGINT);
    `;
    const request = new sql.Request();
    request.input('db', sql.NVarChar, dbName);
    const r = await request.query(sizeQuery);
    const row = r.recordset && r.recordset[0];
    return {
      dataBytes: row ? Number(row.data_bytes) : null,
      logBytes: row ? Number(row.log_bytes) : null,
    };
  } catch (e) {
    console.warn('Could not query DB size:', e.message);
    return { dataBytes: null, logBytes: null };
  }
}

// Function to create database backup
async function createDatabaseBackup() {
  try {
    console.log('Connecting to database...');
    
    // Close any existing connection to avoid conflicts
    try {
      await sql.close();
    } catch (closeErr) {
      // Ignore error if no connection exists
    }
    
    await sql.connect(config);

    console.log('Creating database backup...');
    const backupQuery = `
      BACKUP DATABASE [${process.env.MSSQL_DATABASE}] 
      TO DISK = N'${backupFilePath}' 
      WITH FORMAT, INIT, COMPRESSION, NAME = N'${process.env.MSSQL_DATABASE}-Vollständig Datenbank Sichern', 
      SKIP, NOREWIND, NOUNLOAD, STATS = 10
    `;

    const result = await sql.query(backupQuery);
    console.log('Database backup created successfully');
    return backupFilePath;
  } catch (err) {
    console.error('Error creating database backup:', err);
    throw err;
  }
}

// Function to download backup file via SCP
async function downloadBackupFile() {
  try {
    console.log('Downloading backup file via SCP...');

    const remotePath = process.env.SCP_REMOTE_PATH || '/sharemnt/ez.bak';
    const localPath = process.env.SCP_LOCAL_PATH || 'ez.bak';

    console.log(`From: ${scpConfig.username}@${scpConfig.host}:${remotePath}`);
    console.log(`To: ${localPath}`);

    return new Promise((resolve, reject) => {
      const conn = new Client();

      conn.on('ready', () => {
        console.log('SSH connection established');

        conn.sftp((err, sftp) => {
          if (err) {
            console.error('SFTP error:', err);
            conn.end();
            reject(err);
            return;
          }

          const readStream = sftp.createReadStream(remotePath);
          const writeStream = fs.createWriteStream(localPath);

          readStream.on('error', (err) => {
            console.error('Read stream error:', err);
            conn.end();
            reject(err);
          });

          writeStream.on('error', (err) => {
            console.error('Write stream error:', err);
            conn.end();
            reject(err);
          });

          writeStream.on('finish', () => {
            console.log('Backup file downloaded successfully to:', localPath);
            conn.end();
            resolve(localPath);
          });

          readStream.pipe(writeStream);
        });
      });

      conn.on('error', (err) => {
        console.error('SSH connection error:', err);
        reject(err);
      });

      conn.connect(scpConfig);
    });

  } catch (err) {
    console.error('Error downloading backup file via SCP:', err);
    throw err;
  }
}

// Legacy function to download backup file from SMB share (deprecated)
async function downloadBackupFileSMB() {
  try {
    console.log('Downloading backup file from SMB share...');

    // Create SMB client
    const client = new SambaClient(smbConfig);

    // Download file from SMB share
    await client.getFile(process.env.SMB_DOWNLOAD_FILE, process.env.SMB_LOCAL_DOWNLOAD_FILE);

    console.log('Backup file downloaded successfully to:', process.env.SMB_LOCAL_DOWNLOAD_FILE);
    return process.env.SMB_LOCAL_DOWNLOAD_FILE;
  } catch (err) {
    console.error('Error downloading backup file from SMB share:', err);
    throw err;
  }
}

// Function to compress backup file with gzip
async function compressBackupFile(inputPath) {
  try {
    console.log('Compressing backup file...');
    
    const outputPath = inputPath + '.gz';
    const readStream = fs.createReadStream(inputPath);
    const writeStream = fs.createWriteStream(outputPath);
    const gzipStream = zlib.createGzip();
    
    // Use pipeline for better error handling and cleanup
    await pipeline(readStream, gzipStream, writeStream);
    
    console.log('Backup file compressed successfully:', outputPath);
    return outputPath;
  } catch (err) {
    console.error('Error compressing backup file:', err);
    throw err;
  }
}

// Function to upload backup to S3
async function uploadBackupToS3(filePath) {
  try {
    console.log('Uploading backup to S3...');
    
    // Create a read stream for the file
    const fileStream = fs.createReadStream(filePath);
    
    // Handle stream errors
    fileStream.on('error', (err) => {
      console.error('File stream error:', err);
      throw err;
    });
    
    // Create S3 upload parameters using stream - add .gz extension to key
    const key = `backups/${process.env.MSSQL_DATABASE}_${new Date().toISOString().replace(/[:.]/g, '-')}.bak.gz`;
    const params = {
      Bucket: process.env.S3_BUCKET_NAME,
      Key: key,
      Body: fileStream,
      ContentEncoding: 'gzip',
      ContentType: 'application/gzip'
    };
    
    // Upload file to S3 using v3 client
    await s3.send(new PutObjectCommand(params));
    const location = `s3://${process.env.S3_BUCKET_NAME}/${key}`;
    console.log('Backup uploaded successfully to S3:', location);
    return location;
  } catch (err) {
    console.error('Error uploading backup to S3:', err);
    throw err;
  }
}

// Function to get latest backup timestamp from S3
async function getLatestBackupTime() {
  try {
    const params = {
      Bucket: process.env.S3_BUCKET_NAME,
      Prefix: 'backups/',
    };
    const command = new ListObjectsV2Command(params);
    const response = await s3.send(command);
    if (!response.Contents || response.Contents.length === 0) {
      return null;
    }
    // Sort by LastModified descending
    const sorted = response.Contents.sort((a, b) => b.LastModified - a.LastModified);
    return sorted[0].LastModified;
  } catch (err) {
    console.error('Error checking latest backup:', err);
    throw err;
  }
}

// Function to run backup process
async function runBackupProcess() {
  try {
    console.log('Starting backup process at', new Date().toISOString());

    // Create database backup (opens connection)
    await createDatabaseBackup();

    // While connected, get DB size info
    const sizes = await getDbSizeBytes();

    // Close connection once done with SQL-related work
    await sql.close().catch(() => {});

    // Download backup file from SMB share
    const localBackupFile = await downloadBackupFile();

    // Compress backup file with gzip
    const compressedBackupFile = await compressBackupFile(localBackupFile);

    // Upload compressed backup to S3
    const s3Url = await uploadBackupToS3(compressedBackupFile);

    // Clean up temporary files
    try {
      if (fs.existsSync(localBackupFile)) {
        fs.unlinkSync(localBackupFile);
        console.log('Cleaned up original backup file:', localBackupFile);
      }
      if (fs.existsSync(compressedBackupFile)) {
        fs.unlinkSync(compressedBackupFile);
        console.log('Cleaned up compressed backup file:', compressedBackupFile);
      }
    } catch (cleanupErr) {
      console.warn('Error cleaning up temporary files:', cleanupErr.message);
    }

    console.log('Backup process completed successfully at', new Date().toISOString());
    console.log('Backup available at:', s3Url);

    // Notify admins via Telegram broadcast with size
    const fileName = path.basename(compressedBackupFile || 'backup.bak.gz');
    const when = new Date().toISOString();
    const sizeLine = sizes && (sizes.dataBytes != null || sizes.logBytes != null)
      ? `\nDB Size: ${sizes.dataBytes != null ? formatBytes(sizes.dataBytes) : '?'} (data), ${sizes.logBytes != null ? formatBytes(sizes.logBytes) : '?'} (log)`
      : '';
    const msg = `Backup completed ✅\nDB: ${process.env.MSSQL_DATABASE}\nFile: ${fileName}\nS3: ${s3Url}${sizeLine}\nTime: ${when}`;
    await sendTelegramBroadcast('all', msg);
  } catch (err) {
    console.error('Backup process failed:', err);
    // Ensure connection closed on failure
    try { await sql.close(); } catch {}
    // Optional: notify error channel on failure (best-effort)
    const when = new Date().toISOString();
    const msg = `Backup failed 🔴\nDB: ${process.env.MSSQL_DATABASE}\nTime: ${when}\nError: ${err && err.message ? err.message : err}`;
    await sendTelegramBroadcast('errors', msg);
  } finally {
    setTimeout(runBackupProcess, 86400000);
  }
}

// Only run startup logic if this file is executed directly (not imported)
if (require.main === module) {
  // Run backup immediately when starting
  (async () => {
    try {
      const latestBackupTime = await getLatestBackupTime();
      const now = new Date();
      let delay = 0;

      if (latestBackupTime) {
        const nextBackupTime = new Date(latestBackupTime.getTime() + 86400000);
        if (nextBackupTime > now) {
          delay = nextBackupTime - now;
          console.log(`Scheduling first backup in ${Math.floor(delay / 3600000)} hours.`);
        }
      }

      if (delay > 0) {
        setTimeout(runBackupProcess, delay);
      } else {
        runBackupProcess();
      }
    } catch (err) {
      console.error('Error during startup check, proceeding with backup:', err);
      runBackupProcess();
    }
  })();

  console.log('Database backup service started. Running backups every 24 hours.');

  // Startup health notification
  (async () => {
    try {
      const when = new Date().toISOString();
      const host = require('os').hostname();
      const region = process.env.AWS_REGION || 'n/a';
      const bucket = process.env.S3_BUCKET_NAME || 'n/a';
      await sendTelegramBroadcast('all', `Backup service started ✅\nDB: ${process.env.MSSQL_DATABASE}\nHost: ${host}\nAWS: ${region}/${bucket}\nTime: ${when}`);
    } catch (e) {
      console.warn('Startup broadcast failed:', e.message);
    }
  })();
}

// Export functions for reuse in other scripts
module.exports = {
  createDatabaseBackup,
  downloadBackupFile,
  downloadBackupFileSMB, // Legacy SMB function
  compressBackupFile,
  formatBytes,
  sendTelegramBroadcast,
  getDbSizeBytes,
  // Export configurations that might be needed
  scpConfig,
  smbConfig, // Legacy SMB config
  config: config, // MSSQL config
  s3 // S3 client
};