EmailManager/server.js

import express from 'express';
import path from 'path';
import { fileURLToPath } from 'url';
import mysql from 'mysql2/promise';
import pg from 'pg';
import cors from 'cors';
import dotenv from 'dotenv';

dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const app = express();
const PORT = process.env.PORT || 3000;

app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname, 'dist')));

// --- LOGGING MIDDLEWARE ---
app.use((req, res, next) => {
  console.log(`[${new Date().toISOString()}] ${req.method} ${req.url}`);
  next();
});

// DB Configuration
const rawDbType = (process.env.DB_TYPE || 'mysql').toLowerCase().trim();
const DB_TYPE = (rawDbType === 'postgres' || rawDbType === 'postgresql') ? 'postgres' : 'mysql';

const dbConfig = {
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT || (DB_TYPE === 'postgres' ? 5432 : 3306),
};

// Debug Log
console.log('--- Database Configuration ---');
console.log(`Type: ${DB_TYPE}`);
console.log(`Host: ${dbConfig.host}`);
console.log(`User: ${dbConfig.user}`);
console.log(`Database: ${dbConfig.database}`);
console.log(`Port: ${dbConfig.port}`);
console.log('------------------------------');

let pool;

const initDB = async () => {
  try {
    if (!dbConfig.host || !dbConfig.user || !dbConfig.database) {
       throw new Error("Missing required database environment variables.");
    }

    if (DB_TYPE === 'postgres') {
      const { Pool } = pg;
      pool = new Pool(dbConfig);
      await pool.query(`
        CREATE TABLE IF NOT EXISTS email_templates (
          id VARCHAR(255) PRIMARY KEY,
          template_key VARCHAR(255) UNIQUE NOT NULL,
          name VARCHAR(255) NOT NULL,
          description TEXT,
          subject VARCHAR(255),
          header_html TEXT,
          body_html TEXT,
          footer_html TEXT,
          full_html TEXT,
          required_variables JSONB,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
      `);
    } else {
      pool = mysql.createPool(dbConfig);
      // Use MEDIUMTEXT for MySQL to handle larger emails
      await pool.query(`
        CREATE TABLE IF NOT EXISTS email_templates (
          id VARCHAR(255) PRIMARY KEY,
          template_key VARCHAR(255) UNIQUE NOT NULL,
          name VARCHAR(255) NOT NULL,
          description TEXT,
          subject VARCHAR(255),
          header_html MEDIUMTEXT,
          body_html MEDIUMTEXT,
          footer_html MEDIUMTEXT,
          full_html MEDIUMTEXT,
          required_variables JSON,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );
      `);
    }
    console.log(`Connected to ${DB_TYPE} database successfully.`);
  } catch (err) {
    console.error('Database connection/init failed:', err);
    process.exit(1);
  }
};

initDB();

app.get('/api/templates', async (req, res) => {
  try {
    let rows;
    if (DB_TYPE === 'postgres') {
      const result = await pool.query('SELECT * FROM email_templates ORDER BY updated_at DESC');
      rows = result.rows;
    } else {
      const [result] = await pool.query('SELECT * FROM email_templates ORDER BY updated_at DESC');
      rows = result;
    }
    
    const templates = rows.map(row => ({
      id: row.id,
      name: row.name,
      description: row.description,
      subject: row.subject,
      header: row.header_html,
      body: row.body_html,
      footer: row.footer_html,
      variables: typeof row.required_variables === 'string' ? JSON.parse(row.required_variables) : (row.required_variables || []),
      updatedAt: row.updated_at
    }));

    res.json(templates);
  } catch (err) {
    console.error("Fetch Error:", err);
    res.status(500).json({ error: 'Failed to fetch templates' });
  }
});

app.post('/api/templates', async (req, res) => {
  const t = req.body;
  const header = t.header || '';
  const body = t.body || '';
  const footer = t.footer || '';
  const fullHtml = `${header}${body}${footer}`;
  const templateKey = (t.name || '').trim().toLowerCase().replace(/\s+/g, '_').replace(/[^a-z0-9_]/g, '');
  const variablesJson = JSON.stringify(t.variables || []);

  const params = [
    t.id, 
    templateKey, 
    t.name, 
    t.description || '', 
    t.subject || '', 
    header, 
    body, 
    footer, 
    fullHtml, 
    variablesJson
  ];

  try {
    if (DB_TYPE === 'postgres') {
      const query = `
        INSERT INTO email_templates (id, template_key, name, description, subject, header_html, body_html, footer_html, full_html, required_variables, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::jsonb, NOW())
        ON CONFLICT (id) DO UPDATE SET
          template_key = EXCLUDED.template_key,
          name = EXCLUDED.name,
          description = EXCLUDED.description,
          subject = EXCLUDED.subject,
          header_html = EXCLUDED.header_html,
          body_html = EXCLUDED.body_html,
          footer_html = EXCLUDED.footer_html,
          full_html = EXCLUDED.full_html,
          required_variables = EXCLUDED.required_variables,
          updated_at = NOW();
      `;
      await pool.query(query, params);
    } else {
      const query = `
        INSERT INTO email_templates (id, template_key, name, description, subject, header_html, body_html, footer_html, full_html, required_variables)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
          template_key = VALUES(template_key),
          name = VALUES(name),
          description = VALUES(description),
          subject = VALUES(subject),
          header_html = VALUES(header_html),
          body_html = VALUES(body_html),
          footer_html = VALUES(footer_html),
          full_html = VALUES(full_html),
          required_variables = VALUES(required_variables);
      `;
      await pool.query(query, params);
    }
    console.log(`Template saved: ${t.name} (${t.id})`);
    res.json({ success: true });
  } catch (err) {
    console.error("SAVE ERROR:", err.message);
    res.status(500).json({ error: 'Failed to save template', details: err.message });
  }
});

app.delete('/api/templates/:id', async (req, res) => {
  try {
    if (DB_TYPE === 'postgres') {
      await pool.query('DELETE FROM email_templates WHERE id = $1', [req.params.id]);
    } else {
      await pool.query('DELETE FROM email_templates WHERE id = ?', [req.params.id]);
    }
    res.json({ success: true });
  } catch (err) {
    console.error("Delete Error:", err);
    res.status(500).json({ error: 'Failed to delete template' });
  }
});

app.get('*', (req, res) => {
  res.sendFile(path.join(__dirname, 'dist', 'index.html'));
});

app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});