ffxi-trusts/server.js

/**
 * Express server to serve FFXI Trust character data from PostgreSQL database
 */

const express = require('express');
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');

// Database configuration
let dbConfig = {};

// Try to read from environment variables first
if (process.env.PSQL_HOST && process.env.PSQL_USER && process.env.PSQL_DBNAME) {
  console.log('Using database configuration from environment variables');
  dbConfig = {
    PSQL_USER: process.env.PSQL_USER,
    PSQL_HOST: process.env.PSQL_HOST,
    PSQL_DBNAME: process.env.PSQL_DBNAME,
    PSQL_PASSWORD: process.env.PSQL_PASSWORD,
    PSQL_PORT: process.env.PSQL_PORT || '5432',
  };
} else {
  // Fall back to db.conf file
  try {
    console.log('Reading database configuration from db.conf file');
    const dbConfFile = fs.readFileSync('db.conf', 'utf8');
    
    // Parse the db.conf file
    dbConfFile.split('\n').forEach(line => {
      if (line.trim() === '') return;
      
      const [key, value] = line.split('=');
      if (key && value) {
        // Remove quotes if present
        const cleanValue = value.replace(/^['"]|['"]$/g, '');
        dbConfig[key] = cleanValue;
      }
    });
  } catch (err) {
    console.error('Error reading db.conf file:', err.message);
    console.error('Please ensure db.conf exists or provide environment variables');
    process.exit(1);
  }
}

// Configure PostgreSQL connection
const pool = new Pool({
  user: dbConfig.PSQL_USER,
  host: dbConfig.PSQL_HOST,
  database: dbConfig.PSQL_DBNAME,
  password: dbConfig.PSQL_PASSWORD,
  port: dbConfig.PSQL_PORT,
});

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

// Middleware to parse JSON bodies
app.use(express.json());

// Set up EJS as the template engine
app.set('view engine', 'ejs');
app.set('views', path.join(__dirname, '.'));

// Serve static files (except index.html which will be rendered with EJS)
app.use(express.static('.', {
  index: false
}));

// Render index.ejs with environment variables
app.get('/', (req, res) => {
  res.render('index.ejs', {
    process: {
      env: {
        PSQL_HOST: dbConfig.PSQL_HOST,
        PSQL_PORT: dbConfig.PSQL_PORT,
        PSQL_USER: dbConfig.PSQL_USER,
        PSQL_PASSWORD: dbConfig.PSQL_PASSWORD,
        PSQL_DBNAME: dbConfig.PSQL_DBNAME
      }
    }
  });
});

// API endpoint to get all trusts
app.get('/api/trusts', async (req, res) => {
  try {
    const client = await pool.connect();
    const result = await client.query('SELECT * FROM trusts ORDER BY name');
    const trusts = result.rows;
    client.release();
    
    res.json(trusts);
  } catch (err) {
    console.error('Error executing query', err);
    res.status(500).json({ error: 'Database error' });
  }
});

// API endpoint to get a specific trust by ID
app.get('/api/trusts/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const client = await pool.connect();
    const result = await client.query('SELECT * FROM trusts WHERE id = $1', [id]);
    
    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Trust not found' });
    } else {
      res.json(result.rows[0]);
    }
    
    client.release();
  } catch (err) {
    console.error('Error executing query', err);
    res.status(500).json({ error: 'Database error' });
  }
});

// API endpoint to get trusts by role
app.get('/api/trusts/role/:role', async (req, res) => {
  try {
    const role = req.params.role;
    const client = await pool.connect();
    const result = await client.query('SELECT * FROM trusts WHERE role = $1 ORDER BY name', [role]);
    const trusts = result.rows;
    client.release();
    
    res.json(trusts);
  } catch (err) {
    console.error('Error executing query', err);
    res.status(500).json({ error: 'Database error' });
  }
});

// API endpoint to search trusts
app.get('/api/trusts/search/:query', async (req, res) => {
  try {
    const query = req.params.query;
    const client = await pool.connect();
    const result = await client.query(`
      SELECT * FROM trusts 
      WHERE 
        name ILIKE $1 OR 
        role ILIKE $1 OR 
        job ILIKE $1 OR 
        spells ILIKE $1 OR 
        abilities ILIKE $1
      ORDER BY name
    `, [`%${query}%`]);
    
    const trusts = result.rows;
    client.release();
    
    res.json(trusts);
  } catch (err) {
    console.error('Error executing query', err);
    res.status(500).json({ error: 'Database error' });
  }
});

// API endpoint to toggle the acquired status of a trust
app.put('/api/trusts/:id/toggle-acquired', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const client = await pool.connect();
    
    // Get the current acquired status
    const getResult = await client.query('SELECT acquired FROM trusts WHERE id = $1', [id]);
    
    if (getResult.rows.length === 0) {
      client.release();
      return res.status(404).json({ error: 'Trust not found' });
    }
    
    // Toggle the acquired status
    const currentStatus = getResult.rows[0].acquired;
    const newStatus = !currentStatus;
    
    // Update the acquired status
    await client.query('UPDATE trusts SET acquired = $1 WHERE id = $2', [newStatus, id]);
    
    // Get the updated trust
    const result = await client.query('SELECT * FROM trusts WHERE id = $1', [id]);
    client.release();
    
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error executing query', err);
    res.status(500).json({ error: 'Database error' });
  }
});

// Health check endpoint
app.get('/health', async (req, res) => {
  try {
    // Check database connection
    const client = await pool.connect();
    await client.query('SELECT 1');
    client.release();
    
    res.status(200).json({
      status: 'ok',
      message: 'Service is healthy',
      timestamp: new Date().toISOString(),
      version: process.env.npm_package_version || '1.0.0',
      database: 'connected'
    });
  } catch (err) {
    console.error('Health check failed:', err);
    res.status(500).json({
      status: 'error',
      message: 'Service is unhealthy',
      timestamp: new Date().toISOString(),
      error: err.message,
      database: 'disconnected'
    });
  }
});

// Start the server
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
  console.log(`Access the web app at http://localhost:${PORT}`);
  console.log(`API available at http://localhost:${PORT}/api/trusts`);
});