scoreodonto.com/backend/pg-convert.js

#!/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.');