const express      = require('express');
const { execSync } = require('child_process');
const router       = express.Router();
const authMw       = require('../middleware/auth');

const ps = (cmd, timeout = 30000) =>
  execSync(`powershell -ExecutionPolicy Bypass -Command "${cmd}"`, { timeout }).toString().trim();

// Import SqlServer module (tries SqlServer, then SQLPS, then direct path)
const SQL_IMPORT = [
  `if(!(Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue)){`,
  `  $paths=@('C:\\Program Files\\WindowsPowerShell\\Modules\\SqlServer',`,
  `  'C:\\Program Files (x86)\\Microsoft SQL Server\\*\\Tools\\PowerShell\\Modules\\SQLPS',`,
  `  'C:\\Program Files\\Microsoft SQL Server\\*\\Tools\\PowerShell\\Modules\\SQLPS');`,
  `  $mod=$paths|ForEach-Object{Get-Item $_ -ErrorAction SilentlyContinue}|Select-Object -First 1;`,
  `  if($mod){Import-Module $mod.FullName -ErrorAction SilentlyContinue}`,
  `  else{Import-Module SqlServer -ErrorAction SilentlyContinue}`,
  `}`,
].join('');

const sql = query => ps(
  `${SQL_IMPORT} Invoke-Sqlcmd -Query '${query}' -ServerInstance 'localhost' | ConvertTo-Json`
);

// MySQL query via mysql CLI (legacy wrapper — delegates to mysqlExec)
function mysqlQuery(query, database) {
  const dbArg = database ? '-D "' + database + '"' : '';
  const escaped = query.replace(/\\/g, '\\\\').replace(/"/g, '\\"').replace(/\n/g, ' ');
  const raw = mysqlExec(dbArg + ' -e "' + escaped + '" --batch --silent');
  if (!raw) return { rows: [], message: 'Query executed successfully' };
  const lines = raw.split('\n').filter(Boolean);
  if (lines.length < 2) return { rows: [], message: raw };
  const headers = lines[0].split('\t');
  const rows = lines.slice(1).map(line => {
    const vals = line.split('\t');
    const obj = {};
    headers.forEach((h, i) => { obj[h] = vals[i] === 'NULL' ? null : (vals[i] || ''); });
    return obj;
  });
  return { rows };
}

// PostgreSQL query via psql CLI
function pgQuery(query, database) {
  const dbArg = database || 'postgres';
  const escaped = query.replace(/"/g, '\\"').replace(/\n/g, ' ');
  try {
    const raw = ps(`psql -U postgres -d "${dbArg}" -c "${escaped}" --csv --quiet --no-align -t 2>&1`);
    if (!raw) return { rows: [], message: 'Query executed successfully' };
    // Parse CSV output
    const lines = raw.split('\n').filter(Boolean);
    if (!lines.length) return { rows: [], message: 'Query executed' };
    // psql --csv with -t skips headers; use without -t for headers
    return { rows: [], message: raw }; // return raw for now
  } catch(e) {
    throw new Error('PostgreSQL error: ' + e.message);
  }
}

// ── Status & Install ───────────────────────────────────
router.get('/status', authMw, (req, res) => {
  const check = (names, paths) => {
    try {
      const r = ps(`Get-Service -Name ${names} -ErrorAction SilentlyContinue | Select-Object -First 1 -ExpandProperty Status`).trim();
      if (r === 'Running' || r === 'Stopped') return true;
    } catch {}
    // Fallback: check install path
    try {
      const found = ps(`$p=@(${paths.map(p=>`'${p}'`).join(',')});($p|Where-Object{Test-Path $_}|Select-Object -First 1) -ne $null`).trim();
      return found === 'True';
    } catch {}
    return false;
  };
  res.json({
    sqlserver: check(`'MSSQLSERVER','MSSQL$SQLEXPRESS'`, ['C:\\Program Files\\Microsoft SQL Server']),
    mysql:     check(`'MySQL','MySQL80','MySQL84','MySQL57','MySQL56'`, ['C:\\Program Files\\MySQL','C:\\tools\\mysql','C:\\ProgramData\\MySQL']),
    postgres:  check(`'postgresql','postgresql-x64-15','postgresql-x64-14','postgresql-x64-16','postgresql-x64-17'`, ['C:\\Program Files\\PostgreSQL'])
  });
});

router.post('/install/:engine', authMw, (req, res) => {
  const map = {
    sqlserver: 'choco install sql-server-express -y --no-progress',
    mysql:     'choco install mysql -y --no-progress',
    postgres:  'choco install postgresql -y --no-progress'
  };
  if (!map[req.params.engine]) return res.status(400).json({ error: 'Unknown engine' });

  // Installations take 5–15 minutes — run async and respond immediately.
  // The frontend should poll /api/db/status to detect when it completes.
  res.json({ ok: true, message: 'Installation started. This may take 5–15 minutes. Refresh status to check progress.' });

  const chocoInstall = `if(!(Get-Command choco -ErrorAction SilentlyContinue)){` +
    `Set-ExecutionPolicy Bypass -Scope Process -Force;` +
    `[Net.ServicePointManager]::SecurityProtocol=[Net.ServicePointManager]::SecurityProtocol -bor 3072;` +
    `iex((New-Object Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))` +
  `}`;

  const { exec } = require('child_process');
  exec(
    `powershell -ExecutionPolicy Bypass -Command "${chocoInstall} ${map[req.params.engine]}"`,
    { timeout: 20 * 60 * 1000 }, // 20 minute timeout
    (err) => {
      if (err) console.error(`[db/install/${req.params.engine}] failed:`, err.message);
      else     console.log(`[db/install/${req.params.engine}] completed successfully`);
    }
  );
});

// ── Databases ──────────────────────────────────────────
router.get('/databases', authMw, (req, res) => {
  try {
    const raw = sql(`SELECT name,state_desc,recovery_model_desc,CAST(size*8.0/1024 AS DECIMAL(10,2)) AS size_mb FROM sys.databases WHERE name NOT IN('master','tempdb','model','msdb') ORDER BY name`);
    if (!raw) return res.json([]);
    const list = JSON.parse(raw);
    res.json(Array.isArray(list)?list:[list]);
  } catch(e) { res.status(500).json({ error:'SQL Server error: '+e.message }); }
});

router.post('/databases', authMw, (req, res) => {
  const { name } = req.body;
  if (!name||!/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error:'Invalid name' });
  try { sql(`CREATE DATABASE [${name}]`); res.json({ ok:true }); }
  catch(e) { res.status(500).json({ error:e.message }); }
});

router.delete('/databases/:name', authMw, (req, res) => {
  const { name } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error:'Invalid name' });
  try { sql(`ALTER DATABASE [${name}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [${name}]`); res.json({ ok:true }); }
  catch(e) { res.status(500).json({ error:e.message }); }
});

router.get('/databases/:name/tables', authMw, (req, res) => {
  try {
    const raw = sql(`USE [${req.params.name}]; SELECT TABLE_NAME,TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME`);
    if (!raw) return res.json([]);
    const list = JSON.parse(raw);
    res.json(Array.isArray(list)?list:[list]);
  } catch(e) { res.status(500).json({ error:e.message }); }
});

// ── Query Runner ───────────────────────────────────────
router.post('/query', authMw, (req, res) => {
  const { query, database } = req.body;
  if (!query) return res.status(400).json({ error:'No query provided' });
  if (/xp_cmdshell|drop\s+database/i.test(query)) return res.status(400).json({ error:'Blocked for safety' });
  try {
    const raw = sql(`USE [${database||'master'}]; ${query}`);
    if (!raw) return res.json({ rows:[], message:'Query executed successfully' });
    try { const p=JSON.parse(raw); res.json({ rows:Array.isArray(p)?p:[p] }); }
    catch { res.json({ rows:[], message:raw }); }
  } catch(e) { res.status(500).json({ error:e.message }); }
});

// ── DB Users ───────────────────────────────────────────
router.get('/users', authMw, (req, res) => {
  try {
    const raw = sql(`SELECT name,type_desc,is_disabled,CONVERT(varchar,create_date,120) as created FROM sys.server_principals WHERE type IN('S','U') AND name NOT LIKE '##%' AND name NOT IN('sa','NT AUTHORITY\\SYSTEM','NT SERVICE\\MSSQLSERVER') ORDER BY name`);
    if (!raw) return res.json([]);
    const list = JSON.parse(raw);
    res.json(Array.isArray(list)?list:[list]);
  } catch(e) { res.status(500).json({ error:e.message }); }
});

router.post('/users', authMw, (req, res) => {
  const { username, password, database, role } = req.body;
  if (!username||!/^[a-zA-Z0-9_]+$/.test(username)) return res.status(400).json({ error:'Invalid username' });
  if (!password||password.length<8) return res.status(400).json({ error:'Password must be at least 8 characters' });
  try {
    sql(`CREATE LOGIN [${username}] WITH PASSWORD='${password.replace(/'/g,"''")}',CHECK_POLICY=OFF`);
    if (database && database!=='none') sql(`USE [${database}]; CREATE USER [${username}] FOR LOGIN [${username}]; EXEC sp_addrolemember '${role||'db_datareader'}','${username}'`);
    res.json({ ok:true });
  } catch(e) { res.status(500).json({ error:e.message }); }
});

router.delete('/users/:name', authMw, (req, res) => {
  const { name } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error:'Invalid name' });
  try { sql(`DROP LOGIN [${name}]`); res.json({ ok:true }); }
  catch(e) { res.status(500).json({ error:e.message }); }
});

router.post('/users/:name/permissions', authMw, (req, res) => {
  const { name } = req.params;
  const { database, role } = req.body;
  if (!database||!role) return res.status(400).json({ error:'Database and role required' });
  try {
    sql(`USE [${database}]; IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name='${name}') CREATE USER [${name}] FOR LOGIN [${name}]; EXEC sp_addrolemember '${role}','${name}'`);
    res.json({ ok:true });
  } catch(e) { res.status(500).json({ error:e.message }); }
});


// ── Table Viewer ───────────────────────────────────────

// List tables with row counts for a database
router.get('/databases/:name/tables', authMw, (req, res) => {
  const dbName = req.params.name;
  if (!/^[a-zA-Z0-9_]+$/.test(dbName)) return res.status(400).json({ error: 'Invalid database name' });
  try {
    const raw = sql(`
      USE [${dbName}];
      SELECT
        t.TABLE_NAME,
        t.TABLE_TYPE,
        p.rows AS RowCount
      FROM INFORMATION_SCHEMA.TABLES t
      LEFT JOIN sys.tables st ON st.name = t.TABLE_NAME
      LEFT JOIN sys.partitions p ON p.object_id = st.object_id AND p.index_id IN (0,1)
      WHERE t.TABLE_TYPE = 'BASE TABLE'
      ORDER BY t.TABLE_NAME
    `);
    if (!raw) return res.json([]);
    const list = JSON.parse(raw);
    res.json(Array.isArray(list) ? list : [list]);
  } catch(e) { res.status(500).json({ error: e.message }); }
});

// Get columns for a table
router.get('/databases/:name/tables/:table/columns', authMw, (req, res) => {
  const { name: dbName, table } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(dbName) || !/^[a-zA-Z0-9_\s]+$/.test(table))
    return res.status(400).json({ error: 'Invalid name' });
  try {
    const raw = sql(`
      USE [${dbName}];
      SELECT
        COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
        IS_NULLABLE, COLUMN_DEFAULT,
        COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = '${table.replace(/'/g, "''")}'
      ORDER BY ORDINAL_POSITION
    `);
    if (!raw) return res.json([]);
    const list = JSON.parse(raw);
    res.json(Array.isArray(list) ? list : [list]);
  } catch(e) { res.status(500).json({ error: e.message }); }
});

// Get paginated rows for a table
router.get('/databases/:name/tables/:table/rows', authMw, (req, res) => {
  const { name: dbName, table } = req.params;
  const page    = Math.max(1, parseInt(req.query.page)  || 1);
  const perPage = Math.min(200, parseInt(req.query.per)  || 50);
  const offset  = (page - 1) * perPage;
  const search  = req.query.search ? req.query.search.replace(/'/g, "''") : '';

  if (!/^[a-zA-Z0-9_]+$/.test(dbName) || !/^[a-zA-Z0-9_\s]+$/.test(table))
    return res.status(400).json({ error: 'Invalid name' });

  try {
    // Get total count
    const countRaw = sql(`USE [${dbName}]; SELECT COUNT(*) AS total FROM [${table}]`);
    const total = countRaw ? (JSON.parse(countRaw).total || 0) : 0;

    // Get rows with pagination
    const raw = sql(`
      USE [${dbName}];
      SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS __rn FROM [${table}]
      ) t WHERE __rn > ${offset} AND __rn <= ${offset + perPage}
      ORDER BY __rn
    `);

    let rows = [];
    if (raw) {
      const parsed = JSON.parse(raw);
      rows = Array.isArray(parsed) ? parsed : [parsed];
      // Remove the internal row number column
      rows = rows.map(r => { const {__rn, ...rest} = r; return rest; });
    }

    res.json({ rows, total, page, perPage, pages: Math.ceil(total / perPage) });
  } catch(e) { res.status(500).json({ error: e.message }); }
});




// ── MySQL databases & tables ───────────────────────────
const CRED_FILE = require('path').join(__dirname, '../data/db-credentials.json');

function getEngineCreds(engine) {
  try {
    if (require('fs').existsSync(CRED_FILE)) {
      const all = JSON.parse(require('fs').readFileSync(CRED_FILE, 'utf8'));
      if (all[engine]) return all[engine];
    }
  } catch {}
  const defaults = {
    mysql:    { host:'localhost', port:3306, user:'root',     pass:'' },
    postgres: { host:'localhost', port:5432, user:'postgres', pass:'' },
    sqlserver:{ host:'localhost', port:1433, user:'sa',       pass:'' }
  };
  return defaults[engine] || defaults.mysql;
}

function saveCreds(engine, host, port, user, pass) {
  const fs   = require('fs');
  const path = require('path');
  let all = {};
  try { if (fs.existsSync(CRED_FILE)) all = JSON.parse(fs.readFileSync(CRED_FILE,'utf8')); } catch {}
  const existing = all[engine] || {};
  all[engine] = { host, port, user, pass: pass || existing.pass || '' };
  fs.mkdirSync(path.dirname(CRED_FILE), { recursive: true });
  fs.writeFileSync(CRED_FILE, JSON.stringify(all, null, 2));
}


// ── MySQL execution — bypasses PowerShell entirely ───────────
// Uses Node execSync directly so the password never touches a shell.
// The .cnf temp file approach means special chars (@!#$'" etc) all work.
const childProcess = require('child_process');
const fsSync       = require('fs');
const pathSync     = require('path');
const osSync       = require('os');

function writeMyCnf(user, pass, host, port) {
  const cnfPath = pathSync.join(osSync.tmpdir(), 'sp_mysql_' + process.pid + '_' + Date.now() + '.cnf');
  // Use \n not \r\n — MySQL on Windows reads both but some builds choke on \r\n in cnf
  const lines = [
    '[client]',
    'user='     + (user || 'root'),
    'password=' + (pass || ''),
    'host='     + (host || 'localhost'),
    'port='     + (port || 3306),
  ];
  fsSync.writeFileSync(cnfPath, lines.join('\n'), { encoding: 'utf8' });
  return cnfPath;
}

function findMysqlBin() {
  // Try common Windows MySQL install locations
  const candidates = [
    'mysql',   // on PATH
    'C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin\\mysql.exe',
    'C:\\Program Files\\MySQL\\MySQL Server 8.4\\bin\\mysql.exe',
    'C:\\Program Files\\MySQL\\MySQL Server 5.7\\bin\\mysql.exe',
    'C:\\tools\\mysql\\current\\bin\\mysql.exe',
    'C:\\ProgramData\\chocolatey\\bin\\mysql.exe',
  ];
  for (const c of candidates) {
    try {
      childProcess.execSync('"' + c + '" --version', { stdio: 'pipe', timeout: 3000 });
      return c;
    } catch {}
  }
  return 'mysql'; // fallback
}

let _mysqlBin = null;

function mysqlExec(sqlArgs, overrideCreds) {
  const c   = overrideCreds || getEngineCreds('mysql');
  const cnf = writeMyCnf(c.user, c.pass, c.host, c.port);
  if (!_mysqlBin) _mysqlBin = findMysqlBin();
  try {
    // Run mysql.exe directly — NO PowerShell wrapper, NO shell quoting issues
    var cmd = '"' + _mysqlBin + '" --defaults-file="' + cnf + '" --connect-expired-password ' + sqlArgs;
    const result = childProcess.execSync(cmd, {
      timeout: 30000,
      stdio:   ['pipe', 'pipe', 'pipe']
    });
    return result.toString().trim();
  } catch(e) {
    const msg = (e.stderr ? e.stderr.toString() : e.message).split('\n')[0].substring(0, 300);
    throw new Error('MySQL error: ' + msg);
  } finally {
    try { fsSync.unlinkSync(cnf); } catch {}
  }
}

// testMysqlConnection — used by /test-connection and /connect routes
// Takes raw credentials, never goes through PowerShell
function testMysqlConnection(user, pass, host, port) {
  const cnf = writeMyCnf(user, pass, host, port);
  if (!_mysqlBin) _mysqlBin = findMysqlBin();
  try {
    var tcmd = '"' + _mysqlBin + '" --defaults-file="' + cnf + '" -e "SELECT 1;" --batch --silent';
    childProcess.execSync(tcmd, { timeout: 10000, stdio: ['pipe', 'pipe', 'pipe'] });
  } catch(e) {
    const msg = (e.stderr ? e.stderr.toString() : e.message).split('\n')[0].substring(0, 300);
    throw new Error(msg);
  } finally {
    try { fsSync.unlinkSync(cnf); } catch {}
  }
}

function pgExec(args) {
  const c   = getEngineCreds('postgres');
  const env = c.pass ? { ...process.env, PGPASSWORD: c.pass } : process.env;
  const cmd = `psql -U ${c.user} -h ${c.host} -p ${c.port||5432} ${args}`;
  return require('child_process').execSync(
    `powershell -ExecutionPolicy Bypass -Command "${cmd}"`,
    { timeout: 30000, env }
  ).toString().trim();
}

router.get('/mysql/databases', authMw, (req, res) => {
  try {
    const raw = mysqlExec('-e "SHOW DATABASES;" --batch --silent 2>&1');
    const system = ['information_schema','performance_schema','mysql','sys'];
    const lines = (raw || '').split(/\r?\n/).map(l => l.trim()).filter(l => l && !system.includes(l));
    res.json(lines.map(name => ({ name })));
  } catch(e) { res.status(500).json({ error: 'MySQL error: ' + e.message }); }
});

router.post('/mysql/databases', authMw, (req, res) => {
  const { name } = req.body;
  if (!name || !/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error: 'Invalid name' });
  try {
    mysqlExec('-e "CREATE DATABASE `' + name + '`;" 2>&1');
    res.json({ ok: true });
  } catch(e) { res.status(500).json({ error: 'MySQL error: ' + e.message }); }
});

router.delete('/mysql/databases/:name', authMw, (req, res) => {
  const { name } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error: 'Invalid name' });
  try {
    mysqlExec('-e "DROP DATABASE `' + name + '`;" 2>&1');
    res.json({ ok: true });
  } catch(e) { res.status(500).json({ error: 'MySQL error: ' + e.message }); }
});

router.get('/mysql/databases/:name/tables', authMw, (req, res) => {
  const { name } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error: 'Invalid name' });
  try {
    const raw = mysqlExec('-D "' + name + '" -e "SHOW TABLE STATUS;" --batch --silent 2>&1');
    if (!raw) return res.json([]);
    const lines = raw.split(/\r?\n/).filter(Boolean);
    if (lines.length < 2) return res.json([]);
    const headers = lines[0].split('\t');
    const tables = lines.slice(1).map(line => {
      const vals = line.split('\t');
      const obj = {};
      headers.forEach((h, i) => { obj[h] = vals[i] || ''; });
      return { TABLE_NAME: obj.Name, RowCount: parseInt(obj.Rows) || 0, Engine: obj.Engine };
    });
    res.json(tables);
  } catch(e) { res.status(500).json({ error: 'MySQL error: ' + e.message }); }
});

router.get('/mysql/databases/:name/tables/:table/columns', authMw, (req, res) => {
  const { name, table } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name) || !/^[a-zA-Z0-9_]+$/.test(table))
    return res.status(400).json({ error: 'Invalid name' });
  try {
    const raw = mysqlExec('-D "' + name + '" -e "DESCRIBE `' + table + '`;" --batch --silent 2>&1');
    if (!raw) return res.json([]);
    const lines = raw.split(/\r?\n/).filter(Boolean);
    if (lines.length < 2) return res.json([]);
    const rows = lines.slice(1).map(line => {
      const parts = line.split('\t');
      return {
        COLUMN_NAME:    parts[0] || '',
        DATA_TYPE:      parts[1] || '',
        IS_NULLABLE:    parts[2] || '',
        IS_IDENTITY:    parts[3] === 'PRI' ? 1 : 0,
        COLUMN_DEFAULT: parts[4] || null
      };
    });
    res.json(rows);
  } catch(e) { res.status(500).json({ error: 'MySQL error: ' + e.message }); }
});

router.get('/mysql/databases/:name/tables/:table/rows', authMw, (req, res) => {
  const { name, table } = req.params;
  const page    = Math.max(1, parseInt(req.query.page) || 1);
  const perPage = Math.min(200, parseInt(req.query.per) || 50);
  const offset  = (page - 1) * perPage;
  if (!/^[a-zA-Z0-9_]+$/.test(name) || !/^[a-zA-Z0-9_]+$/.test(table))
    return res.status(400).json({ error: 'Invalid name' });
  try {
    const countRaw = mysqlExec('-D "' + name + '" -e "SELECT COUNT(*) FROM `' + table + '`;" --batch --silent 2>&1');
    const total = parseInt((countRaw || '0').split(/\r?\n/).filter(Boolean).pop()) || 0;
    const raw = mysqlExec('-D "' + name + '" -e "SELECT * FROM `' + table + '` LIMIT ' + perPage + ' OFFSET ' + offset + ';" --batch --silent 2>&1');
    if (!raw) return res.json({ rows: [], total, page, perPage, pages: 1 });
    const lines = raw.split(/\r?\n/).filter(Boolean);
    if (lines.length < 2) return res.json({ rows: [], total, page, perPage, pages: 1 });
    const headers = lines[0].split('\t');
    const rows = lines.slice(1).map(line => {
      const vals = line.split('\t');
      const obj = {};
      headers.forEach((h, i) => { obj[h] = vals[i] === 'NULL' ? null : (vals[i] !== undefined ? vals[i] : ''); });
      return obj;
    });
    res.json({ rows, total, page, perPage, pages: Math.ceil(total / perPage) || 1 });
  } catch(e) { res.status(500).json({ error: 'MySQL error: ' + e.message }); }
});

router.post('/mysql/query', authMw, (req, res) => {
  const { query, database } = req.body;
  if (!query) return res.status(400).json({ error: 'No query' });
  if (/drop\s+database/i.test(query)) return res.status(400).json({ error: 'Blocked for safety' });
  try {
    const dbArg = database ? '-D "' + database + '"' : '';
    const escaped = query.replace(/\\/g, '\\\\').replace(/"/g, '\\"').replace(/\r?\n/g, ' ');
    let raw;
    try { raw = mysqlExec(dbArg + ' -e "' + escaped + '" --batch --silent'); }
    catch(e2) { raw = ''; }
    if (!raw) return res.json({ rows: [], message: 'Query executed successfully' });
    const lines = raw.split(/\r?\n/).filter(Boolean);
    if (lines.length < 2) return res.json({ rows: [], message: raw });
    const headers = lines[0].split('\t');
    const rows = lines.slice(1).map(line => {
      const vals = line.split('\t');
      const obj = {};
      headers.forEach((h, i) => { obj[h] = vals[i] === 'NULL' ? null : (vals[i] || ''); });
      return obj;
    });
    res.json({ rows });
  } catch(e) { res.status(500).json({ error: 'MySQL error: ' + e.message }); }
});

// ── PostgreSQL databases & tables ──────────────────────

router.get('/pg/databases', authMw, (req, res) => {
  try {
    const raw = pgExec('-c "SELECT datname FROM pg_database WHERE datistemplate=false AND datname<>\'\'\'\'postgres\'\'\'\';" --csv -t --quiet 2>&1');
    const lines = (raw || '').split(/\r?\n/).map(l => l.trim()).filter(Boolean);
    res.json(lines.map(name => ({ name })));
  } catch(e) { res.status(500).json({ error: 'PostgreSQL error: ' + e.message }); }
});

router.post('/pg/databases', authMw, (req, res) => {
  const { name } = req.body;
  if (!name || !/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error: 'Invalid name' });
  try {
    pgExec('-c "CREATE DATABASE \\"' + name + '\\";" 2>&1');
    res.json({ ok: true });
  } catch(e) { res.status(500).json({ error: 'PostgreSQL error: ' + e.message }); }
});

router.delete('/pg/databases/:name', authMw, (req, res) => {
  const { name } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error: 'Invalid name' });
  try {
    pgExec('-c "DROP DATABASE \\"' + name + '\\";" 2>&1');
    res.json({ ok: true });
  } catch(e) { res.status(500).json({ error: 'PostgreSQL error: ' + e.message }); }
});

router.get('/pg/databases/:name/tables', authMw, (req, res) => {
  const { name } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name)) return res.status(400).json({ error: 'Invalid name' });
  try {
    const raw = pgExec('-d "' + name + '" -c "SELECT tablename FROM pg_tables WHERE schemaname=\'\'\'\'public\'\'\'\'  ORDER BY tablename;" --csv -t --quiet 2>&1');
    if (!raw) return res.json([]);
    const tables = raw.split(/\r?\n/).map(l => l.trim()).filter(Boolean)
      .map(n => ({ TABLE_NAME: n, RowCount: null }));
    res.json(tables);
  } catch(e) { res.status(500).json({ error: 'PostgreSQL error: ' + e.message }); }
});

router.get('/pg/databases/:name/tables/:table/columns', authMw, (req, res) => {
  const { name, table } = req.params;
  if (!/^[a-zA-Z0-9_]+$/.test(name) || !/^[a-zA-Z0-9_]+$/.test(table))
    return res.status(400).json({ error: 'Invalid name' });
  try {
    const q = 'SELECT column_name,data_type,is_nullable,column_default FROM information_schema.columns WHERE table_name=\'\'\'\'' + table + '\'\'\'\' ORDER BY ordinal_position;';
    const raw = pgExec('-d "' + name + '" -c "' + q + '" --csv -t --quiet 2>&1');
    if (!raw) return res.json([]);
    const rows = raw.split(/\r?\n/).filter(Boolean).map(line => {
      const parts = line.split(',');
      return {
        COLUMN_NAME:    parts[0] || '',
        DATA_TYPE:      parts[1] || '',
        IS_NULLABLE:    parts[2] || '',
        IS_IDENTITY:    0,
        COLUMN_DEFAULT: parts[3] || null
      };
    });
    res.json(rows);
  } catch(e) { res.status(500).json({ error: 'PostgreSQL error: ' + e.message }); }
});

router.get('/pg/databases/:name/tables/:table/rows', authMw, (req, res) => {
  const { name, table } = req.params;
  const page    = Math.max(1, parseInt(req.query.page) || 1);
  const perPage = Math.min(200, parseInt(req.query.per) || 50);
  const offset  = (page - 1) * perPage;
  if (!/^[a-zA-Z0-9_]+$/.test(name) || !/^[a-zA-Z0-9_]+$/.test(table))
    return res.status(400).json({ error: 'Invalid name' });
  try {
    const countRaw = pgExec('-d "' + name + '" -c "SELECT COUNT(*) FROM \\"' + table + '\\";" --csv -t --quiet 2>&1');
    const total = parseInt((countRaw || '0').trim()) || 0;
    const raw = pgExec('-d "' + name + '" -c "SELECT * FROM \\"' + table + '\\" LIMIT ' + perPage + ' OFFSET ' + offset + ';" --csv --quiet 2>&1');
    if (!raw) return res.json({ rows: [], total, page, perPage, pages: 1 });
    const lines = raw.split(/\r?\n/).filter(Boolean);
    if (lines.length < 2) return res.json({ rows: [], total, page, perPage, pages: 1 });
    const headers = lines[0].split(',');
    const rows = lines.slice(1).map(line => {
      const vals = line.split(',');
      const obj = {};
      headers.forEach((h, i) => { obj[h] = vals[i] === '' ? null : (vals[i] || ''); });
      return obj;
    });
    res.json({ rows, total, page, perPage, pages: Math.ceil(total / perPage) || 1 });
  } catch(e) { res.status(500).json({ error: 'PostgreSQL error: ' + e.message }); }
});

router.post('/pg/query', authMw, (req, res) => {
  const { query, database } = req.body;
  if (!query) return res.status(400).json({ error: 'No query' });
  if (/drop\s+database/i.test(query)) return res.status(400).json({ error: 'Blocked for safety' });
  try {
    const db = database || 'postgres';
    const escaped = query.replace(/\\/g, '\\\\').replace(/"/g, '\\"').replace(/\r?\n/g, ' ');
    const raw = pgExec('-d "' + db + '" -c "' + escaped + '" --csv --quiet 2>&1');
    if (!raw) return res.json({ rows: [], message: 'Query executed' });
    const lines = raw.split(/\r?\n/).filter(Boolean);
    if (lines.length < 2) return res.json({ rows: [], message: raw });
    const headers = lines[0].split(',');
    const rows = lines.slice(1).map(line => {
      const vals = line.split(',');
      const obj = {};
      headers.forEach((h, i) => { obj[h] = vals[i] === '' ? null : (vals[i] || ''); });
      return obj;
    });
    res.json({ rows });
  } catch(e) { res.status(500).json({ error: 'PostgreSQL error: ' + e.message }); }
});


// GET /api/db/credentials/:engine
router.get('/credentials/:engine', authMw, (req, res) => {
  const creds = getEngineCreds(req.params.engine);
  res.json({ host: creds.host, port: creds.port, user: creds.user, hasPass: !!creds.pass });
});

// POST /api/db/test-connection
router.post('/test-connection', authMw, (req, res) => {
  const { engine, host, port, user, pass } = req.body;
  try {
    if (engine === 'mysql') {
      testMysqlConnection(user, pass, host, port);
    } else if (engine === 'postgres') {
      const env = pass ? { ...process.env, PGPASSWORD: pass } : process.env;
      require('child_process').execSync(
        `psql -U ${user} -h ${host} -p ${port||5432} -c "SELECT 1;" --csv -t --quiet`,
        { timeout: 10000, env }
      );
    } else if (engine === 'sqlserver') {
      const SQL_IMPORT = `if(!(Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue)){Import-Module SqlServer -ErrorAction SilentlyContinue}`;
      const ssHost2 = host || 'localhost';
      const ssAuth2 = user && pass
        ? `-Username '${user}' -Password '${(pass||'').replace(/'/g,"''")}'`
        : '';
      ps(`${SQL_IMPORT} Invoke-Sqlcmd -Query 'SELECT 1' -ServerInstance '${ssHost2}' ${ssAuth2} | Out-Null`);
    }
    res.json({ ok: true });
  } catch(e) {
    res.status(400).json({ error: e.message.split('\n')[0].substring(0, 200) });
  }
});

// POST /api/db/connect  (test + save credentials)
router.post('/connect', authMw, (req, res) => {
  const { engine, host, port, user, pass } = req.body;
  try {
    if (engine === 'mysql') {
      testMysqlConnection(user, pass, host, port);
    } else if (engine === 'postgres') {
      const env = pass ? { ...process.env, PGPASSWORD: pass } : process.env;
      require('child_process').execSync(
        `psql -U ${user} -h ${host} -p ${port||5432} -c "SELECT 1;" --csv -t --quiet`,
        { timeout: 10000, env }
      );
    } else if (engine === 'sqlserver') {
      const SQL_IMPORT = `if(!(Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue)){Import-Module SqlServer -ErrorAction SilentlyContinue}`;
      const ssHost = host || 'localhost';
      const ssAuth = user && pass
        ? `-Username '${user}' -Password '${(pass||'').replace(/'/g,"''")}'`
        : '';  // Windows auth if no credentials
      ps(`${SQL_IMPORT} Invoke-Sqlcmd -Query 'SELECT 1' -ServerInstance '${ssHost}' ${ssAuth} | Out-Null`);
    }
    // Connection successful — save credentials
    saveCreds(engine, host, port, user, pass);
    res.json({ ok: true });
  } catch(e) {
    res.status(400).json({ error: e.message.split('\n')[0].substring(0, 200) });
  }
});


module.exports = router;