smanab/app_tamu/server.js

const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');
const path = require('path');
require('dotenv').config();

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

// Middleware
app.use(cors());
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ extended: true, limit: '50mb' }));

// Serve static files from dist folder
app.use(express.static(path.join(__dirname, 'dist')));

// Database connection pool
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASS || '',
  database: process.env.DB_NAME || 'db_bukutamu',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Initialize database with table creation
async function initializeDatabase() {
  try {
    const connection = await pool.getConnection();
    console.log('✅ Database connected successfully!');
    console.log(`   Host: ${process.env.DB_HOST || 'localhost'}`);
    console.log(`   Database: ${process.env.DB_NAME || 'db_bukutamu'}`);
    
    // Create guests table if not exists
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS guests (
        id VARCHAR(36) PRIMARY KEY,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        date VARCHAR(100) NOT NULL,
        name VARCHAR(255) NOT NULL,
        nip VARCHAR(50),
        position VARCHAR(255),
        purpose TEXT,
        suggestions TEXT,
        notes TEXT,
        signature LONGTEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    console.log('✅ Table "guests" is ready!');
    
    connection.release();
  } catch (error) {
    console.error('❌ Database connection failed:', error.message);
    process.exit(1);
  }
}

// ===== API ENDPOINTS =====

// GET all guests
app.get('/api/guests', async (req, res) => {
  try {
    const [rows] = await pool.execute(
      'SELECT * FROM guests ORDER BY timestamp DESC'
    );
    
    // Format data for frontend
    const guests = rows.map(row => ({
      id: row.id,
      timestamp: row.timestamp,
      date: row.date,
      name: row.name,
      nip: row.nip || '',
      position: row.position || '',
      purpose: row.purpose || '',
      suggestions: row.suggestions || '',
      notes: row.notes || '',
      signature: row.signature || ''
    }));
    
    res.json(guests);
  } catch (error) {
    console.error('Error fetching guests:', error);
    res.status(500).json({ error: 'Failed to fetch guests', message: error.message });
  }
});

// POST new guest
app.post('/api/guests', async (req, res) => {
  try {
    const { id, timestamp, date, name, nip, position, purpose, suggestions, notes, signature } = req.body;
    
    await pool.execute(
      `INSERT INTO guests (id, timestamp, date, name, nip, position, purpose, suggestions, notes, signature)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [id, new Date(timestamp), date, name, nip || '', position || '', purpose || '', suggestions || '', notes || '', signature || '']
    );
    
    res.json({ status: 'success', message: 'Guest entry saved successfully' });
  } catch (error) {
    console.error('Error saving guest:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// DELETE guest by id
app.delete('/api/guests/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    const [result] = await pool.execute(
      'DELETE FROM guests WHERE id = ?',
      [id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ status: 'error', message: 'Guest not found' });
    }
    
    res.json({ status: 'success', message: 'Guest deleted successfully' });
  } catch (error) {
    console.error('Error deleting guest:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// UPDATE guest by id
app.put('/api/guests/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, nip, position, purpose, suggestions, notes, signature } = req.body;
    
    const [result] = await pool.execute(
      `UPDATE guests SET name = ?, nip = ?, position = ?, purpose = ?, suggestions = ?, notes = ?, signature = ?
       WHERE id = ?`,
      [name, nip || '', position || '', purpose || '', suggestions || '', notes || '', signature || '', id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ status: 'error', message: 'Guest not found' });
    }
    
    res.json({ status: 'success', message: 'Guest updated successfully' });
  } catch (error) {
    console.error('Error updating guest:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// GET single guest by id
app.get('/api/guests/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    const [rows] = await pool.execute(
      'SELECT * FROM guests WHERE id = ?',
      [id]
    );
    
    if (rows.length === 0) {
      return res.status(404).json({ status: 'error', message: 'Guest not found' });
    }
    
    const row = rows[0];
    res.json({
      id: row.id,
      timestamp: row.timestamp,
      date: row.date,
      name: row.name,
      nip: row.nip || '',
      position: row.position || '',
      purpose: row.purpose || '',
      suggestions: row.suggestions || '',
      notes: row.notes || '',
      signature: row.signature || ''
    });
  } catch (error) {
    console.error('Error fetching guest:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// Health check endpoint
app.get('/api/health', async (req, res) => {
  try {
    await pool.execute('SELECT 1');
    res.json({ status: 'ok', database: 'connected' });
  } catch (error) {
    res.status(500).json({ status: 'error', database: 'disconnected', message: error.message });
  }
});

// SPA Fallback - serve index.html for all other routes
app.get('*', (req, res) => {
  res.sendFile(path.join(__dirname, 'dist', 'index.html'));
});

// Start server
initializeDatabase().then(() => {
  app.listen(port, '0.0.0.0', () => {
    console.log(`\n🚀 Server running at http://localhost:${port}`);
    console.log(`   API endpoint: http://localhost:${port}/api/guests`);
  });
});