server.js

require('dotenv').config();
const express = require('express');
const cors = require('cors');
const path = require('path');
const { Pool } = require('pg');

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

// Neon (Vercel Postgres) sudah include SSL di connection string
// Jangan dobel-set ssl agar tidak konflik
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.DATABASE_URL?.includes('sslmode')
    ? undefined  // biarkan connection string yang handle SSL
    : { rejectUnauthorized: false }
});

app.use(cors());
app.use(express.json());
// index:false agar express.static tidak otomatis serve index.html
app.use(express.static(path.join(__dirname, 'public'), { index: false }));

// ─── INIT DATABASE ───────────────────────────────────────────────────────────
async function initDB() {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS spd_data (
        id SERIAL PRIMARY KEY,
        nama_pegawai VARCHAR(255) NOT NULL,
        nip VARCHAR(50),
        jabatan VARCHAR(255),
        tujuan VARCHAR(255),
        keperluan TEXT,
        tanggal_berangkat DATE,
        tanggal_kembali DATE,
        lama_perjalanan INT,
        biaya_transport NUMERIC(15, 2) DEFAULT 0,
        biaya_penginapan NUMERIC(15, 2) DEFAULT 0,
        biaya_harian NUMERIC(15, 2) DEFAULT 0,
        total_biaya NUMERIC(15, 2) DEFAULT 0,
        status VARCHAR(50) DEFAULT 'draft',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    await pool.query(`
      CREATE TABLE IF NOT EXISTS spd_sessions (
        id SERIAL PRIMARY KEY,
        judul_kegiatan VARCHAR(500),
        nomor_spt VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        data JSONB NOT NULL
      )
    `);
    console.log('✅ Database initialized');
  } catch (err) {
    console.error('❌ DB init error:', err.message);
  }
}

// ─── API ROUTES ───────────────────────────────────────────────────────────────

// ─── SESSIONS (full SPD state per kegiatan) ──────────────────────────────────

app.post('/api/sessions', async (req, res) => {
  try {
    const { header, pegawai, dpr } = req.body;
    if (!header || !Array.isArray(pegawai)) {
      return res.status(400).json({ success: false, error: 'data tidak valid' });
    }
    const { rows } = await pool.query(
      `INSERT INTO spd_sessions (judul_kegiatan, nomor_spt, data)
       VALUES ($1, $2, $3) RETURNING id, created_at`,
      [header.judulKegiatan || null, header.nomorSPT || null, JSON.stringify({ header, pegawai, dpr: dpr || null })]
    );
    res.status(201).json({ success: true, data: rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/sessions', async (req, res) => {
  try {
    const { rows } = await pool.query(
      'SELECT id, judul_kegiatan, nomor_spt, created_at, data FROM spd_sessions ORDER BY created_at DESC'
    );
    res.json({ success: true, data: rows });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.put('/api/sessions/:id', async (req, res) => {
  try {
    const { header, pegawai, dpr } = req.body;
    if (!header || !Array.isArray(pegawai)) {
      return res.status(400).json({ success: false, error: 'data tidak valid' });
    }
    const { rows } = await pool.query(
      `UPDATE spd_sessions SET judul_kegiatan=$1, nomor_spt=$2, data=$3
       WHERE id=$4 RETURNING id, created_at`,
      [header.judulKegiatan || null, header.nomorSPT || null,
       JSON.stringify({ header, pegawai, dpr: dpr || null }), req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ success: false, error: 'Sesi tidak ditemukan' });
    res.json({ success: true, data: rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.delete('/api/sessions/:id', async (req, res) => {
  try {
    await pool.query('DELETE FROM spd_sessions WHERE id = $1', [req.params.id]);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

// ─── SPD DATA ─────────────────────────────────────────────────────────────────

app.get('/api/spd', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT * FROM spd_data ORDER BY created_at DESC');
    res.json({ success: true, data: rows });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/spd/stats/summary', async (req, res) => {
  try {
    const { rows } = await pool.query(`
      SELECT
        COUNT(*) AS total_spd,
        SUM(total_biaya) AS total_anggaran,
        COUNT(CASE WHEN status = 'approved' THEN 1 END) AS total_approved,
        COUNT(CASE WHEN status = 'draft' THEN 1 END) AS total_draft
      FROM spd_data
    `);
    res.json({ success: true, data: rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/spd/:id', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT * FROM spd_data WHERE id = $1', [req.params.id]);
    if (rows.length === 0) return res.status(404).json({ success: false, error: 'Data tidak ditemukan' });
    res.json({ success: true, data: rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.post('/api/spd', async (req, res) => {
  try {
    const {
      nama_pegawai, nip, jabatan, tujuan, keperluan,
      tanggal_berangkat, tanggal_kembali, lama_perjalanan,
      biaya_transport, biaya_penginapan, biaya_harian,
      total_biaya, status
    } = req.body;

    const { rows } = await pool.query(`
      INSERT INTO spd_data (
        nama_pegawai, nip, jabatan, tujuan, keperluan,
        tanggal_berangkat, tanggal_kembali, lama_perjalanan,
        biaya_transport, biaya_penginapan, biaya_harian,
        total_biaya, status
      ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
      RETURNING *
    `, [nama_pegawai, nip, jabatan, tujuan, keperluan,
        tanggal_berangkat, tanggal_kembali, lama_perjalanan,
        biaya_transport, biaya_penginapan, biaya_harian,
        total_biaya, status || 'draft']);

    res.status(201).json({ success: true, data: rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.put('/api/spd/:id', async (req, res) => {
  try {
    const {
      nama_pegawai, nip, jabatan, tujuan, keperluan,
      tanggal_berangkat, tanggal_kembali, lama_perjalanan,
      biaya_transport, biaya_penginapan, biaya_harian,
      total_biaya, status
    } = req.body;

    const { rows } = await pool.query(`
      UPDATE spd_data SET
        nama_pegawai=$1, nip=$2, jabatan=$3, tujuan=$4, keperluan=$5,
        tanggal_berangkat=$6, tanggal_kembali=$7, lama_perjalanan=$8,
        biaya_transport=$9, biaya_penginapan=$10, biaya_harian=$11,
        total_biaya=$12, status=$13, updated_at=CURRENT_TIMESTAMP
      WHERE id=$14 RETURNING *
    `, [nama_pegawai, nip, jabatan, tujuan, keperluan,
        tanggal_berangkat, tanggal_kembali, lama_perjalanan,
        biaya_transport, biaya_penginapan, biaya_harian,
        total_biaya, status, req.params.id]);

    if (rows.length === 0) return res.status(404).json({ success: false, error: 'Data tidak ditemukan' });
    res.json({ success: true, data: rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

app.delete('/api/spd/:id', async (req, res) => {
  try {
    await pool.query('DELETE FROM spd_data WHERE id = $1', [req.params.id]);
    res.json({ success: true, message: 'Data berhasil dihapus' });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

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

// Panggil initDB saat startup (untuk lokal & Vercel)
initDB();

// Untuk local development
if (process.env.NODE_ENV !== 'production') {
  app.listen(PORT, () => {
    console.log(`🚀 Server running on http://localhost:${PORT}`);
  });
}

// Export untuk Vercel (serverless)
module.exports = app;