Aeroklubben-IT/arbetsgrupp-live/server.js

const express = require('express');
const http = require('http');
const socketIo = require('socket.io');
const Database = require('better-sqlite3');
const QRCode = require('qrcode');
const path = require('path');
const cors = require('cors');

const app = express();
const server = http.createServer(app);
const io = socketIo(server, {
  cors: {
    origin: "*",
    methods: ["GET", "POST"]
  }
});

const PORT = process.env.PORT || 3000;

// Middleware
app.use(cors());
app.use(express.json());
app.use(express.static('public'));

// Initialize better-sqlite3 database
const db = new Database('./data/responses.db');

// Configure database for better performance
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');
db.pragma('cache_size = 1000000');
db.pragma('temp_store = memory');

// Initialize database tables
try {
  // Create responses table
  db.exec(`CREATE TABLE IF NOT EXISTS responses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    participant_name TEXT NOT NULL,
    response TEXT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);
  
  // Create options table for dynamic options
  db.exec(`CREATE TABLE IF NOT EXISTS options (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    option_text TEXT UNIQUE NOT NULL,
    is_predefined BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);
  
  // Insert predefined options
  const predefinedOptions = ['Landvetter', 'Backamo', 'Borås', 'Trollhättan'];
  const insertOption = db.prepare("INSERT OR IGNORE INTO options (option_text, is_predefined) VALUES (?, 1)");
  
  predefinedOptions.forEach(option => {
    insertOption.run(option);
  });
} catch (error) {
  console.error('Database initialization error:', error);
}

// Prepared statements for better performance
const getOptionsStmt = db.prepare("SELECT option_text FROM options ORDER BY is_predefined DESC, created_at ASC");
const getResponseCountsStmt = db.prepare(`
  SELECT response, COUNT(*) as count 
  FROM responses 
  GROUP BY response 
  ORDER BY response ASC
`);
const getAllResponsesStmt = db.prepare(`
  SELECT participant_name, response, timestamp 
  FROM responses 
  ORDER BY timestamp DESC
`);
const insertResponseStmt = db.prepare("INSERT INTO responses (participant_name, response) VALUES (?, ?)");
const insertOptionStmt = db.prepare("INSERT OR IGNORE INTO options (option_text) VALUES (?)");

// Get all available options
function getOptions() {
  try {
    return getOptionsStmt.all().map(row => row.option_text);
  } catch (error) {
    console.error('Error getting options:', error);
    return [];
  }
}

// Get response counts
function getResponseCounts() {
  try {
    return getResponseCountsStmt.all();
  } catch (error) {
    console.error('Error getting response counts:', error);
    return [];
  }
}

// Get all responses with participant names
function getAllResponses() {
  try {
    return getAllResponsesStmt.all();
  } catch (error) {
    console.error('Error getting all responses:', error);
    return [];
  }
}

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

app.get('/admin', (req, res) => {
  res.sendFile(path.join(__dirname, 'public', 'admin.html'));
});

app.get('/api/options', (req, res) => {
  try {
    const options = getOptions();
    res.json(options);
  } catch (error) {
    console.error('API error - options:', error);
    res.status(500).json({ error: 'Failed to fetch options' });
  }
});

app.get('/api/results', (req, res) => {
  try {
    const results = getResponseCounts();
    res.json(results);
  } catch (error) {
    console.error('API error - results:', error);
    res.status(500).json({ error: 'Failed to fetch results' });
  }
});

app.get('/api/all-responses', (req, res) => {
  try {
    const responses = getAllResponses();
    res.json(responses);
  } catch (error) {
    console.error('API error - all responses:', error);
    res.status(500).json({ error: 'Failed to fetch responses' });
  }
});

app.post('/api/submit', (req, res) => {
  const { responses, participantName } = req.body;
  
  if (!responses || !Array.isArray(responses) || responses.length === 0) {
    return res.status(400).json({ error: 'Invalid responses' });
  }

  if (!participantName || typeof participantName !== 'string' || participantName.trim().length === 0) {
    return res.status(400).json({ error: 'Participant name is required' });
  }

  const trimmedName = participantName.trim();
  
  try {
    // Use a transaction for better performance and data integrity
    const insertTransaction = db.transaction((responses, participantName) => {
      // Insert all responses
      responses.forEach(response => {
        insertResponseStmt.run(participantName, response);
        // Add new custom options to options table
        insertOptionStmt.run(response);
      });
    });

    insertTransaction(responses, trimmedName);
    
    // Emit updated data to all clients
    const options = getOptions();
    io.emit('optionsUpdated', options);
    
    const results = getResponseCounts();
    io.emit('resultsUpdated', results);

    const allResponses = getAllResponses();
    io.emit('allResponsesUpdated', allResponses);
    
    res.json({ success: true });
  } catch (error) {
    console.error('Error submitting response:', error);
    res.status(500).json({ error: 'Failed to submit response' });
  }
});

app.get('/api/qr', async (req, res) => {
  try {
    const url = `https://aeroklubben.hostux.fr`;
    const qrCode = await QRCode.toDataURL(url);
    res.json({ qrCode });
  } catch (err) {
    res.status(500).json({ error: 'Failed to generate QR code' });
  }
});

// Socket.IO connection handling
io.on('connection', (socket) => {
  console.log('Client connected');
  
  try {
    // Send current options and results to new client
    const options = getOptions();
    socket.emit('optionsUpdated', options);
    
    const results = getResponseCounts();
    socket.emit('resultsUpdated', results);

    const allResponses = getAllResponses();
    socket.emit('allResponsesUpdated', allResponses);
  } catch (error) {
    console.error('Error sending initial data to client:', error);
  }
  
  socket.on('disconnect', () => {
    console.log('Client disconnected');
  });
});

// Start server
server.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
  console.log(`Form: https://aeroklubben.hostux.fr`);
  console.log(`Admin: https://aeroklubben.hostux.fr/admin`);
});

// Graceful shutdown
process.on('SIGINT', () => {
  console.log('Shutting down gracefully...');
  try {
    db.close();
    console.log('Database connection closed.');
  } catch (error) {
    console.error('Error closing database:', error);
  }
  server.close(() => {
    console.log('Server closed.');
    process.exit(0);
  });
});

process.on('SIGTERM', () => {
  console.log('Received SIGTERM, shutting down gracefully...');
  try {
    db.close();
    console.log('Database connection closed.');
  } catch (error) {
    console.error('Error closing database:', error);
  }
  server.close(() => {
    console.log('Server closed.');
    process.exit(0);
  });
});