// @ts-nocheck
/**
 * extract_mssql_defs_to_files.js
 *
 * Connects to a MSSQL database, for each user table:
 *  1. Extracts the CREATE TABLE DDL.
 *  2. Selects the TOP 10 rows by primary key DESC (or just TOP 10 if no PK).
 *  3. Writes both the DDL and the rows (as INSERT statements) into
 *     tables/<schema>.<table>.sql
 *
 * Usage:    
 *   npm install mssql
 *   node extract_mssql_defs_to_files.js
 */

const sql = require('mssql');
const fs = require('fs');
const path = require('path');

// --- CONFIGURE YOUR DATABASE CONNECTION HERE ---
const config = {
  user: 'sa',
  password: 'sa_tekno23',
  server: '10.10.10.3',
  database: 'eazybusiness',
  options: { encrypt: false, trustServerCertificate: true }
};

async function ensureDir(dir) {
  if (!fs.existsSync(dir)) {
    fs.mkdirSync(dir, { recursive: true });
  }
}

function escapeSqlString(val) {
  if (val === null || val === undefined) return 'NULL';
  if (typeof val === 'number') return val;
  if (typeof val === 'boolean') return val ? 1 : 0;
  return "'" + String(val).replace(/'/g, "''") + "'";
}

async function getTables(pool) {
  const tables = await pool.request().query(`
    SELECT t.object_id, s.name AS schema_name, t.name AS table_name
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    ORDER BY s.name, t.name
  `);
  return tables.recordset;
}

async function getColumns(pool, object_id) {
  const columns = await pool.request().query(`
    SELECT c.name AS column_name, 
           TYPE_NAME(c.user_type_id) AS data_type,
           c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity
    FROM sys.columns c
    WHERE c.object_id = ${object_id}
    ORDER BY c.column_id
  `);
  return columns.recordset;
}

async function getPrimaryKey(pool, object_id) {
  const pk = await pool.request().query(`
    SELECT k.name AS pk_name, c.name AS column_name
    FROM sys.key_constraints k
    INNER JOIN sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id
    INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE k.parent_object_id = ${object_id} AND k.type = 'PK'
    ORDER BY ic.key_ordinal
  `);
  return pk.recordset;
}

function buildCreateTable(schema_name, table_name, columns, pk) {
  let createStmt = `CREATE TABLE [${schema_name}].[${table_name}] (\n`;
  createStmt += columns.map(col => {
    let line = `  [${col.column_name}] ${col.data_type}`;
    if (col.data_type.match(/char|binary|text|nchar|nvarchar|varbinary/i) && col.max_length > 0) {
      line += `(${col.max_length === -1 ? 'MAX' : col.max_length})`;
    } else if (col.data_type.match(/decimal|numeric/i)) {
      line += `(${col.precision},${col.scale})`;
    }
    if (col.is_identity) line += ' IDENTITY(1,1)';
    line += col.is_nullable ? ' NULL' : ' NOT NULL';
    return line;
  }).join(',\n');
  if (pk.length > 0) {
    const pkCols = pk.map(r => `[${r.column_name}]`).join(', ');
    createStmt += `,\n  CONSTRAINT [${pk[0].pk_name}] PRIMARY KEY (${pkCols})`;
  }
  createStmt += '\n);\n\n';
  return createStmt;
}

async function getLatestRows(pool, schema_name, table_name, pkCols, columns) {
  let orderBy = '';
  if (pkCols.length > 0) {
    orderBy = pkCols.map(col => `[${col}] DESC`).join(', ');
  }
  const colList = columns.map(c => `[${c.column_name}]`).join(', ');
  const query = `
    SELECT TOP 10 ${colList}
    FROM [${schema_name}].[${table_name}]
    ${orderBy ? `ORDER BY ${orderBy}` : ''}
  `;
  try {
    const rows = await pool.request().query(query);
    return rows.recordset;
  } catch (e) {
    // Table may be empty or inaccessible
    return [];
  }
}

function buildInsertStatements(schema_name, table_name, columns, rows) {
  if (!rows || rows.length === 0) return '-- No data rows found\n';
  let inserts = '-- Latest 10 rows:\n';
  const colNames = columns.map(c => `[${c.column_name}]`).join(', ');
  for (const row of rows) {
    const vals = columns.map(c => escapeSqlString(row[c.column_name])).join(', ');
    inserts += `INSERT INTO [${schema_name}].[${table_name}] (${colNames}) VALUES (${vals});\n`;
  }
  return inserts;
}

async function getViews(pool) {
  const views = await pool.request().query(`
    SELECT s.name AS schema_name, v.name AS view_name, OBJECT_DEFINITION(v.object_id) AS definition
    FROM sys.views v
    INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
    ORDER BY s.name, v.name
  `);
  return views.recordset;
}

async function getProcedures(pool) {
  const procs = await pool.request().query(`
    SELECT s.name AS schema_name, p.name AS proc_name, OBJECT_DEFINITION(p.object_id) AS definition
    FROM sys.procedures p
    INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
    ORDER BY s.name, p.name
  `);
  return procs.recordset;
}

async function main() {
  await ensureDir(path.join(__dirname, 'tables'));
  let pool;
  try {
    pool = await sql.connect(config);
    const tables = await getTables(pool);

    // TABLES
    for (const t of tables) {
      const { object_id, schema_name, table_name } = t;
      const columns = await getColumns(pool, object_id);
      const pk = await getPrimaryKey(pool, object_id);
      const pkCols = pk.map(r => r.column_name);

      const ddl = buildCreateTable(schema_name, table_name, columns, pk);
      const rows = await getLatestRows(pool, schema_name, table_name, pkCols, columns);
      const inserts = buildInsertStatements(schema_name, table_name, columns, rows);

      const fileName = `${schema_name}.${table_name}.sql`;
      const outPath = path.join(__dirname, 'tables', fileName);
      fs.writeFileSync(outPath, ddl + inserts, 'utf8');
      console.log('Written:', fileName);
    }

    // VIEWS
    const views = await getViews(pool);
    for (const v of views) {
      const { schema_name, view_name, definition } = v;
      const viewDef = `CREATE VIEW [${schema_name}].[${view_name}] AS\n${definition}\nGO\n`;
      const fileName = `${schema_name}.${view_name}.sql`;
      const outPath = path.join(__dirname, 'tables', fileName);
      fs.writeFileSync(outPath, viewDef, 'utf8');
      console.log('Written:', fileName);
    }

    // PROCEDURES
    const procs = await getProcedures(pool);
    for (const p of procs) {
      const { schema_name, proc_name, definition } = p;
      const procDef = `CREATE PROCEDURE [${schema_name}].[${proc_name}]\nAS\n${definition}\nGO\n`;
      const fileName = `${schema_name}.${proc_name}.sql`;
      const outPath = path.join(__dirname, 'tables', fileName);
      fs.writeFileSync(outPath, procDef, 'utf8');
      console.log('Written:', fileName);
    }

    await pool.close();
    console.log('Done.');
  } catch (err) {
    if (pool) await pool.close();
    console.error('Error:', err);
  }
}

main();