VPS 4 Builder
306 lines
13 KiB
JavaScript
306 lines
13 KiB
JavaScript
#!/usr/bin/env node
|
|
// pg-convert.js — Converte queries MySQL/adapter → PostgreSQL nativo
|
|
// Executa: node pg-convert.js
|
|
import { readFileSync, writeFileSync } from 'fs';
|
|
|
|
let src = readFileSync('./server.js', 'utf8');
|
|
|
|
// ── 1. Substituir ? por $n seqencial em cada pool.query / client.query ─────────
|
|
function replacePlaceholders(sql) {
|
|
let n = 0;
|
|
return sql.replace(/\?/g, () => `$${++n}`);
|
|
}
|
|
|
|
// Aplica em strings simples: pool.query('...?...', [...])
|
|
src = src.replace(/(pool|client)\.query\(('(?:[^'\\]|\\.)*')\s*,/g, (m, obj, str) => {
|
|
const raw = str.slice(1, -1);
|
|
if (raw.includes('SET ?') || raw.includes('DUPLICATE') || raw.includes('DATE_FORMAT')) return m;
|
|
return `${obj}.query('${replacePlaceholders(raw)}',`;
|
|
});
|
|
|
|
// Aplica em template literals: pool.query(`...?...`, [...])
|
|
src = src.replace(/(pool|client)\.query\((`(?:[^`\\]|\\.)*`)\s*,/g, (m, obj, tpl) => {
|
|
const inner = tpl.slice(1, -1);
|
|
if (inner.includes('SET ?') || inner.includes('DUPLICATE') || inner.includes('DATE_FORMAT')) return m;
|
|
return `${obj}.query(\`${replacePlaceholders(inner)}\`,`;
|
|
});
|
|
|
|
// ── 2. Corrigir lida = 1 / lida = 0 para booleano PG ──────────────────────────
|
|
src = src.replace(/SET lida = 1/g, 'SET lida = TRUE');
|
|
src = src.replace(/WHERE lida = 0/g, 'WHERE lida = FALSE');
|
|
|
|
// ── 3. Corrigir erro MySQL → PG ────────────────────────────────────────────────
|
|
src = src.replace(/'ER_DUP_ENTRY'/g, "'23505'");
|
|
|
|
// ── 4. DATE_FORMAT / CURDATE / DATE_SUB → PG equivalentes ────────────────────
|
|
src = src.replace(
|
|
/DATE_FORMAT\s*\(\s*"?dataVencimento"?\s*,\s*'%Y-%m'\s*\)/g,
|
|
`TO_CHAR("dataVencimento", 'YYYY-MM')`
|
|
);
|
|
src = src.replace(
|
|
/dataVencimento\s*>=\s*DATE_SUB\(CURDATE\(\)\s*,\s*INTERVAL\s*6\s*MONTH\)/g,
|
|
`"dataVencimento" >= NOW() - INTERVAL '6 months'`
|
|
);
|
|
src = src.replace(/GROUP BY mes/g, 'GROUP BY TO_CHAR("dataVencimento", \'YYYY-MM\')');
|
|
|
|
// ── 5. Remover SET FOREIGN_KEY_CHECKS / SET NAMES (MySQL-only) ─────────────────
|
|
src = src.replace(/\s*await pool\.query\('SET FOREIGN_KEY_CHECKS = [01]'\);\r?\n/g, '');
|
|
src = src.replace(/\s*await pool\.query\('SET NAMES utf8mb4'\);\r?\n/g, '');
|
|
src = src.replace(/\s*await pool\.query\('SET CHARACTER SET utf8mb4'\);\r?\n/g, '');
|
|
|
|
// ── 6. INSERT INTO table SET ? → buildInsert ───────────────────────────────────
|
|
// pacientes
|
|
src = src.replace(
|
|
"await pool.query('INSERT INTO pacientes SET ?', p);",
|
|
"await pool.query(buildInsert('pacientes', p));"
|
|
);
|
|
// dentistas
|
|
src = src.replace(
|
|
"await pool.query('INSERT INTO dentistas SET ?', { ...d, ordem });",
|
|
"await pool.query(buildInsert('dentistas', { ...d, ordem }));"
|
|
);
|
|
// especialidades
|
|
src = src.replace(
|
|
"await pool.query('INSERT INTO especialidades SET ?', { ...req.body, ordem });",
|
|
"await pool.query(buildInsert('especialidades', { ...req.body, ordem }));"
|
|
);
|
|
// planos
|
|
src = src.replace(
|
|
"await pool.query('INSERT INTO planos SET ?', req.body);",
|
|
"await pool.query(buildInsert('planos', req.body));"
|
|
);
|
|
// financeiro
|
|
src = src.replace(
|
|
"await pool.query('INSERT INTO financeiro SET ?', req.body);",
|
|
"await pool.query(buildInsert('financeiro', req.body));"
|
|
);
|
|
// leads
|
|
src = src.replace(
|
|
"await pool.query('INSERT INTO leads SET ?', req.body);",
|
|
"await pool.query(buildInsert('leads', req.body));"
|
|
);
|
|
// gto_items
|
|
src = src.replace(
|
|
"await pool.query('INSERT INTO gto_items SET ?', item);",
|
|
"await pool.query(buildInsert('gto_items', item));"
|
|
);
|
|
// procedimentos (inside connection)
|
|
src = src.replace(
|
|
"await connection.query('INSERT INTO procedimentos SET ?', payload);",
|
|
"await client.query(buildInsert('procedimentos', payload));"
|
|
);
|
|
src = src.replace(
|
|
/await connection\.query\('INSERT INTO procedimentos_valores_planos SET \?',\s*\{[^}]+\}\);\s*/g,
|
|
(m) => m.replace('connection', 'client').replace("SET ?', {", "SET ?', {").replace(
|
|
"await client.query('INSERT INTO procedimentos_valores_planos SET ?',",
|
|
"await client.query(buildInsert('procedimentos_valores_planos',"
|
|
)
|
|
);
|
|
|
|
// ── 7. UPDATE table SET ? WHERE id = ? → buildUpdate ─────────────────────────
|
|
src = src.replace(
|
|
"await pool.query('UPDATE pacientes SET ? WHERE id = ?', [req.body, req.params.id]);",
|
|
"await pool.query(buildUpdate('pacientes', req.body, 'id', req.params.id));"
|
|
);
|
|
src = src.replace(
|
|
"await pool.query('UPDATE dentistas SET ? WHERE id = ?', [req.body, req.params.id]);",
|
|
"await pool.query(buildUpdate('dentistas', req.body, 'id', req.params.id));"
|
|
);
|
|
src = src.replace(
|
|
"await pool.query('UPDATE especialidades SET ? WHERE id = ?', [req.body, req.params.id]);",
|
|
"await pool.query(buildUpdate('especialidades', req.body, 'id', req.params.id));"
|
|
);
|
|
src = src.replace(
|
|
"await pool.query('UPDATE planos SET ? WHERE id = ?', [req.body, req.params.id]);",
|
|
"await pool.query(buildUpdate('planos', req.body, 'id', req.params.id));"
|
|
);
|
|
src = src.replace(
|
|
"await pool.query('UPDATE financeiro SET ? WHERE id = ?', [req.body, req.params.id]);",
|
|
"await pool.query(buildUpdate('financeiro', req.body, 'id', req.params.id));"
|
|
);
|
|
src = src.replace(
|
|
"await connection.query('UPDATE procedimentos SET ? WHERE id = ?', [updateData, req.params.id]);",
|
|
"await client.query(buildUpdate('procedimentos', updateData, 'id', req.params.id));"
|
|
);
|
|
|
|
// ── 8. Corrigir pool.getConnection() → withTransaction pattern ────────────────
|
|
// dentistas/reorder
|
|
src = src.replace(
|
|
`app.put('/api/dentistas/reorder', async (req, res) => {
|
|
const { orders } = req.body; // Array of {id, ordem}
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
for (const item of orders) {
|
|
await connection.query('UPDATE dentistas SET ordem = ? WHERE id = ?', [item.ordem, item.id]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
await connection.rollback();
|
|
res.status(500).json({ error: err.message });
|
|
} finally { connection.release(); }
|
|
});`,
|
|
`app.put('/api/dentistas/reorder', async (req, res) => {
|
|
const { orders } = req.body;
|
|
try {
|
|
await withTransaction(async (client) => {
|
|
for (const item of orders) {
|
|
await client.query('UPDATE dentistas SET ordem = $1 WHERE id = $2', [item.ordem, item.id]);
|
|
}
|
|
});
|
|
res.json({ success: true });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});`
|
|
);
|
|
|
|
// especialidades/reorder
|
|
src = src.replace(
|
|
`app.put('/api/especialidades/reorder', async (req, res) => {
|
|
const { orders } = req.body; // Array of {id, ordem}
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
for (const item of orders) {
|
|
await connection.query('UPDATE especialidades SET ordem = ? WHERE id = ?', [item.ordem, item.id]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
await connection.rollback();
|
|
res.status(500).json({ error: err.message });
|
|
} finally { connection.release(); }
|
|
});`,
|
|
`app.put('/api/especialidades/reorder', async (req, res) => {
|
|
const { orders } = req.body;
|
|
try {
|
|
await withTransaction(async (client) => {
|
|
for (const item of orders) {
|
|
await client.query('UPDATE especialidades SET ordem = $1 WHERE id = $2', [item.ordem, item.id]);
|
|
}
|
|
});
|
|
res.json({ success: true });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});`
|
|
);
|
|
|
|
// procedimentos/reorder
|
|
src = src.replace(
|
|
`app.put('/api/procedimentos/reorder', async (req, res) => {
|
|
const { orders } = req.body; // Array of {id, ordem}
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
await connection.beginTransaction();
|
|
for (const item of orders) {
|
|
await connection.query('UPDATE procedimentos SET ordem = ? WHERE id = ?', [item.ordem, item.id]);
|
|
}
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
await connection.rollback();
|
|
res.status(500).json({ error: err.message });
|
|
} finally { connection.release(); }
|
|
});`,
|
|
`app.put('/api/procedimentos/reorder', async (req, res) => {
|
|
const { orders } = req.body;
|
|
try {
|
|
await withTransaction(async (client) => {
|
|
for (const item of orders) {
|
|
await client.query('UPDATE procedimentos SET ordem = $1 WHERE id = $2', [item.ordem, item.id]);
|
|
}
|
|
});
|
|
res.json({ success: true });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});`
|
|
);
|
|
|
|
// DELETE dentista (transaction)
|
|
src = src.replace(
|
|
`app.delete('/api/dentistas/:id', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
const { id } = req.params;
|
|
await connection.beginTransaction();
|
|
|
|
// 1. Remove Google Tokens associated with this dentist
|
|
await connection.query('DELETE FROM google_tokens WHERE owner_id = ?', [id]);
|
|
|
|
// 2. Remove clinical links (if any)
|
|
await connection.query('DELETE FROM vinculos WHERE usuario_id = ?', [id]);
|
|
|
|
// 3. Set to NULL in agendamentos if it exists
|
|
await connection.query('UPDATE agendamentos SET dentistaId = NULL WHERE dentistaId = ?', [id]);
|
|
|
|
// 4. Delete from dentistas table
|
|
await connection.query('DELETE FROM dentistas WHERE id = ?', [id]);
|
|
|
|
await connection.commit();
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
await connection.rollback();
|
|
console.error('Error deleting dentist:', err);
|
|
res.status(500).json({ error: err.message });
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
});`,
|
|
`app.delete('/api/dentistas/:id', async (req, res) => {
|
|
const { id } = req.params;
|
|
try {
|
|
await withTransaction(async (client) => {
|
|
await client.query('DELETE FROM google_tokens WHERE owner_id = $1', [id]);
|
|
await client.query('DELETE FROM vinculos WHERE usuario_id = $1', [id]);
|
|
await client.query('UPDATE agendamentos SET "dentistaId" = NULL WHERE "dentistaId" = $1', [id]);
|
|
await client.query('DELETE FROM dentistas WHERE id = $1', [id]);
|
|
});
|
|
res.json({ success: true });
|
|
} catch (err) {
|
|
console.error('Error deleting dentist:', err);
|
|
res.status(500).json({ error: err.message });
|
|
}
|
|
});`
|
|
);
|
|
|
|
// ── 9. Corrigir google/events pool.query sem placeholders ─────────────────────
|
|
src = src.replace(
|
|
"const [tokenRows] = await pool.query('SELECT * FROM google_tokens');",
|
|
"const { rows: tokenRows } = await pool.query('SELECT * FROM google_tokens');"
|
|
);
|
|
src = src.replace(
|
|
"const [dent] = await pool.query('SELECT nome FROM dentistas WHERE id = ?', [tokenData.owner_id]);",
|
|
"const { rows: dent } = await pool.query('SELECT nome FROM dentistas WHERE id = $1', [tokenData.owner_id]);"
|
|
);
|
|
src = src.replace(
|
|
"if (dent[0]) ownerName = dent[0].nome;",
|
|
"if (dent[0]) ownerName = dent[0].nome;"
|
|
);
|
|
src = src.replace(
|
|
"const [rows] = await pool.query('SELECT data FROM settings WHERE id = \"main\"');",
|
|
"const { rows } = await pool.query(\"SELECT data FROM settings WHERE id = 'main'\");"
|
|
);
|
|
src = src.replace(
|
|
"const [dName] = await pool.query('SELECT nome FROM dentistas WHERE id = ?', [dentistId]);",
|
|
"const { rows: dName } = await pool.query('SELECT nome FROM dentistas WHERE id = $1', [dentistId]);"
|
|
);
|
|
|
|
// ── 10. Corrigir destructuring [rows] → { rows } para pool.query simples ──────
|
|
// Algumas queries sem parâmetro ainda usam o padrão antigo [rows]
|
|
src = src.replace(
|
|
/const \[rows\] = await pool\.query\('SELECT \* FROM (\w+)'\);/g,
|
|
"const { rows } = await pool.query('SELECT * FROM $1');"
|
|
);
|
|
src = src.replace(
|
|
/const \[rows\] = await pool\.query\('SELECT \* FROM (\w+) ORDER BY ([^']+)'\);/g,
|
|
"const { rows } = await pool.query('SELECT * FROM $1 ORDER BY $2');"
|
|
);
|
|
|
|
// ── 11. Remover initTables (função morta MySQL DDL) ───────────────────────────
|
|
const initStart = src.indexOf('// Initialization: Create tables if not exist');
|
|
const initEnd = src.indexOf('\n// generateIntelligentNotifications');
|
|
if (initStart > 0 && initEnd > initStart) {
|
|
src = src.slice(0, initStart) + src.slice(initEnd + 1);
|
|
}
|
|
|
|
writeFileSync('./server.js', src, 'utf8');
|
|
console.log('[pg-convert] Done! server.js updated in place.');
|