import express from 'express';
import path from 'path';
import { fileURLToPath } from 'url';
import fs from 'fs';
import multer from 'multer';
import mysql from 'mysql2/promise';
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 || 3003;

const buildPath = path.join(__dirname, 'dist');
const assetsPath = path.join(buildPath, 'assets');

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

// Test database connection on startup
async function testDatabaseConnection() {
  try {
    const connection = await pool.getConnection();
    console.log('✅ Database MySQL terhubung ke:', process.env.DB_NAME);
    connection.release();
    return true;
  } catch (error) {
    console.error('❌ Gagal terhubung ke database:', error.message);
    return false;
  }
}

// Initialize database tables
async function initializeDatabase() {
  try {
    const schemaPath = path.join(__dirname, 'database', 'schema.sql');
    if (fs.existsSync(schemaPath)) {
      const schema = fs.readFileSync(schemaPath, 'utf8');
      const statements = schema.split(';').filter(s => s.trim());

      for (const statement of statements) {
        if (statement.trim()) {
          await pool.query(statement);
        }
      }
      console.log('✅ Database schema initialized');
    }
  } catch (error) {
    console.error('⚠️ Error initializing database schema:', error.message);
  }
}

// Pemeriksaan Kunci: Pastikan folder 'dist' ada.
if (!fs.existsSync(buildPath)) {
  console.error('================================================================');
  console.error('ERROR: Folder "dist" tidak ditemukan.');
  console.error('Ini berarti aplikasi belum di-build untuk production.');
  console.error('Harap jalankan "npm run build" sebelum memulai server.');
  console.error('================================================================');
  process.exit(1);
}

// Pastikan folder assets ada
if (!fs.existsSync(assetsPath)) {
  fs.mkdirSync(assetsPath, { recursive: true });
}

// JSON body parser with increased limit for base64 images
app.use(express.json({ limit: '50mb' }));

// Configure multer for file uploads
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, assetsPath);
  },
  filename: function (req, file, cb) {
    const uploadType = req.params.type;
    const ext = path.extname(file.originalname).toLowerCase() || '.png';
    cb(null, uploadType + ext);
  }
});

const upload = multer({
  storage: storage,
  limits: { fileSize: 5 * 1024 * 1024 },
  fileFilter: function (req, file, cb) {
    const allowedTypes = /jpeg|jpg|png|gif|webp/;
    const extname = allowedTypes.test(path.extname(file.originalname).toLowerCase());
    const mimetype = allowedTypes.test(file.mimetype);
    if (extname && mimetype) {
      return cb(null, true);
    }
    cb(new Error('Hanya file gambar yang diperbolehkan (jpg, png, gif, webp)'));
  }
});

// ============================================
// API ENDPOINTS FOR MYSQL DATABASE
// ============================================

// Test database connection
app.get('/api/test', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    connection.release();
    res.json({ success: true, data: { message: 'Koneksi ke Database MySQL berhasil!' } });
  } catch (error) {
    res.json({ success: false, data: { error: 'Gagal terhubung ke database: ' + error.message } });
  }
});

// Get next document number
app.get('/api/next-nomor', async (req, res) => {
  try {
    const currentYear = new Date().getFullYear().toString();

    // Get or create counter for current year
    await pool.query(
      'INSERT INTO nomor_counter (tahun_anggaran, counter) VALUES (?, 0) ON DUPLICATE KEY UPDATE tahun_anggaran=tahun_anggaran',
      [currentYear]
    );

    // Increment and get new counter
    await pool.query(
      'UPDATE nomor_counter SET counter = counter + 1 WHERE tahun_anggaran = ?',
      [currentYear]
    );

    const [rows] = await pool.query(
      'SELECT counter FROM nomor_counter WHERE tahun_anggaran = ?',
      [currentYear]
    );

    const counter = rows[0]?.counter || 1;
    const formattedNumber = String(counter).padStart(3, '0');

    res.json({ success: true, data: formattedNumber });
  } catch (error) {
    res.json({ success: false, data: { error: error.message } });
  }
});

// Helper function to map table names
function getTableName(sheetName) {
  const tableMap = {
    'No. Kode': 'kode_kegiatan',
    'Pesanan': 'pesanan',
    'Berita Pemeriksaan': 'bap',
    'BAST': 'bast',
    'Nota': 'nota',
    'Manajemen': 'pengaturan',
    'Foto': 'foto',
    'RKS': 'rks',
    'SPJ Lomba': 'spj_lomba'
  };
  return tableMap[sheetName] || sheetName.toLowerCase();
}

// Helper function to map column names from frontend to database
function mapColumnsToDB(tableName, data) {
  const columnMaps = {
    'kode_kegiatan': {
      'Kode': 'kode',
      'Uraian': 'uraian'
    },
    'pengaturan': {
      'Nama Ketua Komite': 'nama_ketua_komite',
      'Bendahara': 'bendahara',
      'Nama Tim Teknis Pemeriksaan A': 'nama_tim_teknis_a',
      'Jabatan Tim Teknis Pemeriksaan A': 'jabatan_tim_teknis_a',
      'Nama Tim Teknis Pemeriksaan B': 'nama_tim_teknis_b',
      'Jabatan Tim Teknis Pemeriksaan B': 'jabatan_tim_teknis_b',
      'Nama Tim Teknis Pemeriksaan C': 'nama_tim_teknis_c',
      'Jabatan Tim Teknis Pemeriksaan C': 'jabatan_tim_teknis_c',
      'Tahun Anggaran': 'tahun_anggaran'
    },
    'pesanan': {
      'Tanggal Pesanan': 'tanggal_pesanan',
      'Nomor Pesanan': 'nomor_pesanan',
      'Penyedia': 'penyedia',
      'Nama/Jenis Barang': 'nama_jenis_barang',
      'Spesifikasi Teknis/Tipe': 'spesifikasi_teknis',
      'Volume': 'volume',
      'Satuan': 'satuan',
      'Keterangan': 'keterangan',
      'Waktu Pengerjaan': 'waktu_pengerjaan',
      'Batas Tanggal Pengerjaan': 'batas_tanggal_pengerjaan',
      'Penerima Pesanan': 'penerima_pesanan',
      'Yang Memesan': 'yang_memesan'
    },
    'bap': {
      'Nomor BAP': 'nomor_bap',
      'Pekerjaan': 'pekerjaan',
      'Tanggal Pemeriksaan': 'tanggal_pemeriksaan',
      'Nama Tim Teknis Pemeriksaan A': 'nama_tim_teknis_a',
      'Nama Tim Teknis Pemeriksaan B': 'nama_tim_teknis_b',
      'Nama Tim Teknis Pemeriksaan C': 'nama_tim_teknis_c',
      'Jabatan Tim Teknis Pemeriksaan A': 'jabatan_tim_teknis_a',
      'Jabatan Tim Teknis Pemeriksaan B': 'jabatan_tim_teknis_b',
      'Jabatan Tim Teknis Pemeriksaan C': 'jabatan_tim_teknis_c',
      'Tujuan': 'tujuan',
      'Alamat Penyedia': 'alamat_penyedia',
      'Ketua Komite': 'ketua_komite',
      'Nomor Pesanan': 'nomor_pesanan',
      'Kode Kegiatan': 'kode_kegiatan',
      'Kode Sub Kegiatan': 'kode_sub_kegiatan',
      'Kode Rekening': 'kode_rekening'
    },
    'bast': {
      'Nomor BAST': 'nomor_bast',
      'Tanggal BAST': 'tanggal_bast',
      'Nomor BAP': 'nomor_bap'
    },
    'nota': {
      'No.': 'nomor',
      'Volume': 'volume',
      'Satuan': 'satuan',
      'Nama Barang': 'nama_barang',
      'Harga Satuan': 'harga_satuan',
      'Jumlah': 'jumlah',
      'Nomor BAP': 'nomor_bap'
    },
    'foto': {
      'Nomor BAP': 'nomor_bap',
      'Related ID': 'related_id',
      'Filename': 'filename',
      'URL': 'url'
    },
    'rks': {
      'Pekerjaan': 'pekerjaan'
    },
    'spj_lomba': {
      'Nomor SPJ': 'nomor_spj',
      'Tanggal': 'tanggal',
      'Nama Kegiatan': 'nama_kegiatan',
      'Jenis Pembayaran': 'jenis_pembayaran',
      'Penerima': 'penerima',
      'Nama Bank': 'nama_bank',
      'Nomor Rekening': 'nomor_rekening',
      'Atas Nama': 'atas_nama',
      'Nominal': 'nominal',
      'Keterangan': 'keterangan',
      'Bukti Transfer': 'bukti_transfer',
      'Kode Kegiatan': 'kode_kegiatan',
      'Kode Sub Kegiatan': 'kode_sub_kegiatan',
      'Kode Rekening': 'kode_rekening'
    }
  };

  const map = columnMaps[tableName];
  if (!map) return data;

  const mapped = {};
  for (const [key, value] of Object.entries(data)) {
    const dbColumn = map[key];
    if (dbColumn) {
      mapped[dbColumn] = value;
    }
  }
  return mapped;
}

// Helper function to map column names from database to frontend
function mapColumnsToFrontend(tableName, data) {
  const columnMaps = {
    'kode_kegiatan': {
      'kode': 'Kode',
      'uraian': 'Uraian'
    },
    'pengaturan': {
      'nama_ketua_komite': 'Nama Ketua Komite',
      'bendahara': 'Bendahara',
      'nama_tim_teknis_a': 'Nama Tim Teknis Pemeriksaan A',
      'jabatan_tim_teknis_a': 'Jabatan Tim Teknis Pemeriksaan A',
      'nama_tim_teknis_b': 'Nama Tim Teknis Pemeriksaan B',
      'jabatan_tim_teknis_b': 'Jabatan Tim Teknis Pemeriksaan B',
      'nama_tim_teknis_c': 'Nama Tim Teknis Pemeriksaan C',
      'jabatan_tim_teknis_c': 'Jabatan Tim Teknis Pemeriksaan C',
      'tahun_anggaran': 'Tahun Anggaran'
    },
    'pesanan': {
      'tanggal_pesanan': 'Tanggal Pesanan',
      'nomor_pesanan': 'Nomor Pesanan',
      'penyedia': 'Penyedia',
      'nama_jenis_barang': 'Nama/Jenis Barang',
      'spesifikasi_teknis': 'Spesifikasi Teknis/Tipe',
      'volume': 'Volume',
      'satuan': 'Satuan',
      'keterangan': 'Keterangan',
      'waktu_pengerjaan': 'Waktu Pengerjaan',
      'batas_tanggal_pengerjaan': 'Batas Tanggal Pengerjaan',
      'penerima_pesanan': 'Penerima Pesanan',
      'yang_memesan': 'Yang Memesan'
    },
    'bap': {
      'nomor_bap': 'Nomor BAP',
      'pekerjaan': 'Pekerjaan',
      'tanggal_pemeriksaan': 'Tanggal Pemeriksaan',
      'nama_tim_teknis_a': 'Nama Tim Teknis Pemeriksaan A',
      'nama_tim_teknis_b': 'Nama Tim Teknis Pemeriksaan B',
      'nama_tim_teknis_c': 'Nama Tim Teknis Pemeriksaan C',
      'jabatan_tim_teknis_a': 'Jabatan Tim Teknis Pemeriksaan A',
      'jabatan_tim_teknis_b': 'Jabatan Tim Teknis Pemeriksaan B',
      'jabatan_tim_teknis_c': 'Jabatan Tim Teknis Pemeriksaan C',
      'tujuan': 'Tujuan',
      'alamat_penyedia': 'Alamat Penyedia',
      'ketua_komite': 'Ketua Komite',
      'nomor_pesanan': 'Nomor Pesanan',
      'kode_kegiatan': 'Kode Kegiatan',
      'kode_sub_kegiatan': 'Kode Sub Kegiatan',
      'kode_rekening': 'Kode Rekening'
    },
    'bast': {
      'nomor_bast': 'Nomor BAST',
      'tanggal_bast': 'Tanggal BAST',
      'nomor_bap': 'Nomor BAP'
    },
    'nota': {
      'nomor': 'No.',
      'volume': 'Volume',
      'satuan': 'Satuan',
      'nama_barang': 'Nama Barang',
      'harga_satuan': 'Harga Satuan',
      'jumlah': 'Jumlah',
      'nomor_bap': 'Nomor BAP'
    },
    'foto': {
      'nomor_bap': 'Nomor BAP',
      'related_id': 'Related ID',
      'filename': 'Filename',
      'url': 'URL'
    },
    'rks': {
      'pekerjaan': 'Pekerjaan'
    },
    'spj_lomba': {
      'nomor_spj': 'Nomor SPJ',
      'tanggal': 'Tanggal',
      'nama_kegiatan': 'Nama Kegiatan',
      'jenis_pembayaran': 'Jenis Pembayaran',
      'penerima': 'Penerima',
      'nama_bank': 'Nama Bank',
      'nomor_rekening': 'Nomor Rekening',
      'atas_nama': 'Atas Nama',
      'nominal': 'Nominal',
      'keterangan': 'Keterangan',
      'bukti_transfer': 'Bukti Transfer',
      'kode_kegiatan': 'Kode Kegiatan',
      'kode_sub_kegiatan': 'Kode Sub Kegiatan',
      'kode_rekening': 'Kode Rekening'
    }
  };

  const map = columnMaps[tableName];
  if (!map) return data;

  const mapped = {};
  if (data.id !== undefined) {
    mapped.id = data.id;
  }

  for (const [key, value] of Object.entries(data)) {
    const frontendColumn = map[key];
    if (frontendColumn) {
      mapped[frontendColumn] = value;
    }
  }
  return mapped;
}

// Get data from table
app.get('/api/data/:sheetName', async (req, res) => {
  try {
    const sheetName = req.params.sheetName;
    const tableName = getTableName(sheetName);

    const [rows] = await pool.query(`SELECT * FROM ${tableName}`);

    // Map column names back to frontend format
    const mappedRows = rows.map(row => mapColumnsToFrontend(tableName, row));

    res.json({ success: true, data: mappedRows });
  } catch (error) {
    console.error('Error getting data:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Save single data to table
app.post('/api/save-data', async (req, res) => {
  try {
    const { sheetName, data } = req.body;
    const tableName = getTableName(sheetName);
    const mappedData = mapColumnsToDB(tableName, data);

    const columns = Object.keys(mappedData);
    const values = Object.values(mappedData);
    const placeholders = columns.map(() => '?').join(', ');

    const query = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
    await pool.query(query, values);

    res.json({ success: true, data: { message: 'Data berhasil disimpan!' } });
  } catch (error) {
    console.error('Error saving data:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Save batch data to table
app.post('/api/save-batch-data', async (req, res) => {
  try {
    const { sheetName, data } = req.body;
    const tableName = getTableName(sheetName);

    if (!Array.isArray(data) || data.length === 0) {
      return res.json({ success: false, data: { error: 'Data harus berupa array dan tidak boleh kosong' } });
    }

    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
      for (const item of data) {
        const mappedData = mapColumnsToDB(tableName, item);
        const columns = Object.keys(mappedData);
        const values = Object.values(mappedData);
        const placeholders = columns.map(() => '?').join(', ');

        const query = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
        await connection.query(query, values);
      }

      await connection.commit();
      res.json({ success: true, data: { message: 'Data batch berhasil disimpan!' } });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('Error saving batch data:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Get nota items by BAP number
app.get('/api/nota/:nomorBap', async (req, res) => {
  try {
    const nomorBap = decodeURIComponent(req.params.nomorBap);
    const [rows] = await pool.query('SELECT * FROM nota WHERE nomor_bap = ?', [nomorBap]);

    const mappedData = rows.map(row => mapColumnsToFrontend('nota', row));
    res.json({ success: true, data: mappedData });
  } catch (error) {
    console.error('Error fetching nota:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Update/replace nota for a BAP (delete old and insert new)
app.put('/api/nota/:nomorBap', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    const nomorBap = decodeURIComponent(req.params.nomorBap);
    const { data } = req.body;

    if (!Array.isArray(data) || data.length === 0) {
      return res.json({ success: false, data: { error: 'Data tidak valid' } });
    }

    await connection.beginTransaction();

    // Delete existing nota for this BAP
    await connection.query('DELETE FROM nota WHERE nomor_bap = ?', [nomorBap]);

    // Insert new nota items
    for (const item of data) {
      const mappedData = mapColumnsToDB('nota', item);
      if (Object.keys(mappedData).length > 0) {
        const columns = Object.keys(mappedData);
        const values = Object.values(mappedData);
        const placeholders = columns.map(() => '?').join(', ');

        const query = `INSERT INTO nota (${columns.join(', ')}) VALUES (${placeholders})`;
        await connection.query(query, values);
      }
    }

    await connection.commit();
    res.json({ success: true, data: { message: 'Nota berhasil diperbarui!' } });
  } catch (error) {
    await connection.rollback();
    console.error('Error updating nota:', error);
    res.json({ success: false, data: { error: error.message } });
  } finally {
    connection.release();
  }
});

// Update pesanan pengerjaan details
app.patch('/api/pesanan/:nomor', async (req, res) => {
  try {
    const nomor = req.params.nomor;
    const { waktuPengerjaan, batasTanggalPengerjaan } = req.body;

    const query = `
      UPDATE pesanan 
      SET waktu_pengerjaan = ?, batas_tanggal_pengerjaan = ? 
      WHERE nomor_pesanan = ?
    `;

    await pool.query(query, [waktuPengerjaan, batasTanggalPengerjaan, nomor]);

    res.json({ success: true, data: { message: 'Data pengerjaan berhasil diperbarui!' } });
  } catch (error) {
    console.error('Error updating pesanan pengerjaan:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Delete pesanan and potentially revert counter
app.delete('/api/pesanan/:nomor', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    const nomor = req.params.nomor;
    await connection.beginTransaction();

    // 1. Extract counter from nomor (format: .../SP/00X/...)
    const parts = nomor.split('/');
    let counterPart = parts.find(p => p.match(/^\d+$/));

    if (counterPart) {
      const orderCounter = parseInt(counterPart);
      const currentYear = new Date().getFullYear().toString();

      // 2. Check current counter in DB
      const [rows] = await connection.query(
        'SELECT counter FROM nomor_counter WHERE tahun_anggaran = ?',
        [currentYear]
      );

      if (rows.length > 0 && rows[0].counter === orderCounter) {
        // Revert counter if this was the last issued number
        await connection.query(
          'UPDATE nomor_counter SET counter = counter - 1 WHERE tahun_anggaran = ? AND counter > 0',
          [currentYear]
        );
      }
    }

    // 3. Delete from pesanan table
    await connection.query('DELETE FROM pesanan WHERE nomor_pesanan = ?', [nomor]);

    await connection.commit();
    res.json({ success: true, message: 'Pesanan berhasil dihapus dan nomor counter disesuaikan.' });
  } catch (error) {
    await connection.rollback();
    console.error('Error deleting pesanan:', error);
    res.status(500).json({ success: false, error: error.message });
  } finally {
    connection.release();
  }
});

// Update pesanan batch (for editing)
app.put('/api/pesanan/:nomor', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    const originalNomor = decodeURIComponent(req.params.nomor);
    const { data } = req.body; // Array of pesanan items

    if (!Array.isArray(data) || data.length === 0) {
      return res.json({ success: false, data: { error: 'Data tidak valid' } });
    }

    await connection.beginTransaction();

    // 1. Delete old items
    await connection.query('DELETE FROM pesanan WHERE nomor_pesanan = ?', [originalNomor]);

    // 2. Insert new items
    for (const item of data) {
      const mappedData = mapColumnsToDB('pesanan', item);

      // Only insert if we have valid mapped data
      if (Object.keys(mappedData).length > 0) {
        const columns = Object.keys(mappedData);
        const values = Object.values(mappedData);
        const placeholders = columns.map(() => '?').join(', ');

        const query = `INSERT INTO pesanan (${columns.join(', ')}) VALUES (${placeholders})`;
        await connection.query(query, values);
      }
    }

    await connection.commit();
    res.json({ success: true, data: { message: 'Pesanan berhasil diperbarui!' } });
  } catch (error) {
    await connection.rollback();
    console.error('Error updating pesanan:', error);
    res.json({ success: false, data: { error: error.message } });
  } finally {
    connection.release();
  }
});

// Save BAP with photos
app.post('/api/save-bap-with-photos', async (req, res) => {
  try {
    const { bapData, photos } = req.body;

    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
      // Save BAP data
      const mappedBap = mapColumnsToDB('bap', bapData);
      const bapColumns = Object.keys(mappedBap);
      const bapValues = Object.values(mappedBap);
      const bapPlaceholders = bapColumns.map(() => '?').join(', ');

      const bapQuery = `INSERT INTO bap (${bapColumns.join(', ')}) VALUES (${bapPlaceholders})`;
      await connection.query(bapQuery, bapValues);

      // Save photos if any
      if (photos && photos.length > 0) {
        const nomorBap = bapData['Nomor BAP'];
        for (let i = 0; i < photos.length; i++) {
          const photo = photos[i];
          const photoQuery = `INSERT INTO foto (nomor_bap, related_id, filename, url) VALUES (?, ?, ?, ?)`;
          // Store as data URL format
          const dataUrl = `data:${photo.mimeType};base64,${photo.data}`;
          await connection.query(photoQuery, [nomorBap, photo.relatedId || photo.related_id, photo.filename, dataUrl]);
        }
      }

      await connection.commit();
      res.json({ success: true, data: { message: 'BAP dan foto berhasil disimpan!' } });
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('Error saving BAP with photos:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Update pengaturan
app.post('/api/update-pengaturan', async (req, res) => {
  try {
    const data = req.body;
    const mappedData = mapColumnsToDB('pengaturan', data);

    const setClause = Object.keys(mappedData)
      .map(key => `${key} = ?`)
      .join(', ');
    const values = [...Object.values(mappedData), 1];

    const query = `UPDATE pengaturan SET ${setClause} WHERE id = ?`;
    await pool.query(query, values);

    res.json({ success: true, data: { message: 'Pengaturan berhasil diperbarui!' } });
  } catch (error) {
    console.error('Error updating pengaturan:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Delete kode kegiatan
app.delete('/api/kode/:id', async (req, res) => {
  try {
    const id = req.params.id;
    await pool.query('DELETE FROM kode_kegiatan WHERE id = ?', [id]);
    res.json({ success: true, data: { message: 'Kode berhasil dihapus!' } });
  } catch (error) {
    console.error('Error deleting kode:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Delete all kode kegiatan (for reimport)
app.delete('/api/kode', async (req, res) => {
  try {
    await pool.query('DELETE FROM kode_kegiatan');
    res.json({ success: true, data: { message: 'Semua kode berhasil dihapus!' } });
  } catch (error) {
    console.error('Error deleting all kode:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Delete one RKS
app.delete('/api/rks/:id', async (req, res) => {
  try {
    const id = req.params.id;
    await pool.query('DELETE FROM rks WHERE id = ?', [id]);
    res.json({ success: true, data: { message: 'RKS berhasil dihapus!' } });
  } catch (error) {
    console.error('Error deleting rks:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Delete all RKS (for reimport)
app.delete('/api/rks', async (req, res) => {
  try {
    await pool.query('DELETE FROM rks');
    res.json({ success: true, data: { message: 'Semua RKS berhasil dihapus!' } });
  } catch (error) {
    console.error('Error deleting all rks:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Delete batch RKS
app.post('/api/rks/delete-batch', async (req, res) => {
  try {
    const { ids } = req.body;
    if (!Array.isArray(ids) || ids.length === 0) {
      return res.status(400).json({ success: false, data: { error: 'No IDs provided' } });
    }
    await pool.query('DELETE FROM rks WHERE id IN (?)', [ids]);
    res.json({ success: true, data: { message: `${ids.length} RKS berhasil dihapus!` } });
  } catch (error) {
    console.error('Error delete batch rks:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});



// Seed initial kode kegiatan data
async function seedKodeKegiatan() {
  try {
    const [rows] = await pool.query('SELECT COUNT(*) as count FROM kode_kegiatan');
    if (rows[0].count === 0) {
      const initialKodes = [
        { kode: '400.3.8.1', uraian: 'Kurikulum' },
        { kode: '400.3.8.2', uraian: 'Bahan Ajar' },
        { kode: '400.3.8.3', uraian: 'Pelatihan' },
        { kode: '400.3.8.4', uraian: 'Block grant' },
        { kode: '400.3.8.5', uraian: 'Bimbingan teknis/sosialisasi' },
        { kode: '400.3.8.6', uraian: 'Lomba, Sayembara, festival' },
        { kode: '400.3.8.8', uraian: 'Bantuan siswa miskin' },
        { kode: '400.3.13.1', uraian: 'Prasarana Pendidikan' },
        { kode: '400.3.13.2', uraian: 'Sarana Pendidikan' },
        { kode: '400.3.13.3', uraian: 'Monitoring dan Evaluasi Sarana dan Prasana Pendidikan' },
        { kode: '400.3.11', uraian: 'Penilaian Akademik' },
        { kode: '400.3.12', uraian: 'Penilaian Non Akademik' },
        { kode: '400.3.13', uraian: 'Analisis dan Sistem Informasi Penilaian' },
        { kode: '400.3.12.1', uraian: 'Data peserta didik, pendidik dan tenaga kependidikan' },
        { kode: '400.3.12.2', uraian: 'Data Satuan Pendidikan dan ProsesPembelajaran' },
        { kode: '400.3.10.6', uraian: 'Peningkatan kesejahteraan guru dan tenaga pendidik' }
      ];

      for (const kode of initialKodes) {
        await pool.query('INSERT INTO kode_kegiatan (kode, uraian) VALUES (?, ?)', [kode.kode, kode.uraian]);
      }
      console.log('✅ Kode kegiatan seed data inserted');
    }
  } catch (error) {
    console.error('⚠️ Error seeding kode kegiatan:', error.message);
  }
}

// Seed initial RKS data
async function seedRks() {
  try {
    const [rows] = await pool.query('SELECT COUNT(*) as count FROM rks');
    if (rows[0].count === 0) {
      const initialRks = [
        { pekerjaan: 'Pengadaan Alat Tulis Kantor (ATK) - ATK KBM' },
        { pekerjaan: 'Pengadaan Alat Tulis Kantor (ATK) - ATK Kantor' },
        { pekerjaan: 'Pengadaan Alat Tulis Kantor (ATK) - ATK Administrasi' },
        { pekerjaan: 'Pengadaan Bahan Praktik Siswa - Tata Boga' },
        { pekerjaan: 'Pengadaan Bahan Praktik Siswa - Tata Kecantikan' },
        { pekerjaan: 'Pengadaan Bahan Praktik Siswa - Multimedia' },
        { pekerjaan: 'Pengadaan Bahan Praktik Siswa - Tata Busana' },
        { pekerjaan: 'Pengadaan Alat Kebersihan dan Bahan Pembersih' },
        { pekerjaan: 'Pengadaan Suku Cadang Alat Kantor' },
        { pekerjaan: 'Pengadaan Bahan Cetak dan Penggandaan' },
        { pekerjaan: 'Pengadaan Pakaian Dinas/Seragam' },
        { pekerjaan: 'Pengadaan Komponen Listrik/Elektronik' },
        { pekerjaan: 'Pengadaan Barang Inventaris' },
        { pekerjaan: 'Pemeliharaan Bangunan Gedung' },
        { pekerjaan: 'Pemeliharaan Sarana dan Prasarana Pendukung' },
        { pekerjaan: 'Jasa Tenaga Kebersihan' },
        { pekerjaan: 'Jasa Tenaga Keamanan' },
        { pekerjaan: 'Jasa Langganan Daya dan Jasa' },
        { pekerjaan: 'Honorarium Panitia Kegiatan' },
        { pekerjaan: 'Belanja Perjalanan Dinas' },
        { pekerjaan: 'Belanja Kursus/Pelatihan' },
        { pekerjaan: 'Belanja Jasa Konsultansi' },
        { pekerjaan: 'Penyelenggaraan Rapat Koordinasi' }
      ];

      const values = initialRks.map(r => [r.pekerjaan]);
      await pool.query('INSERT INTO rks (pekerjaan) VALUES ?', [values]);
      console.log('✅ RKS data seeded successfully');
    }
  } catch (error) {
    console.error('⚠️ Error seeding RKS data:', error.message);
  }
}


// ============================================
// SPJ STATISTICS ENDPOINT
// ============================================

// API: Get SPJ statistics per month
app.get('/api/spj-stats', async (req, res) => {
  try {
    const year = req.query.year || new Date().getFullYear();

    // Get BAP data with month info
    const [bapRows] = await pool.query(`
      SELECT 
        nomor_bap,
        tanggal_pemeriksaan,
        pekerjaan,
        MONTH(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) as bulan,
        YEAR(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) as tahun
      FROM bap
      WHERE YEAR(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) = ?
      ORDER BY tanggal_pemeriksaan
    `, [year]);

    // Get nota totals per BAP
    const [notaRows] = await pool.query(`
      SELECT 
        nomor_bap,
        SUM(CAST(jumlah AS DECIMAL(15,2))) as total_nilai
      FROM nota
      GROUP BY nomor_bap
    `);

    // Get SPJ Lomba data with month info
    const [spjLombaRows] = await pool.query(`
      SELECT 
        nomor_spj,
        tanggal,
        nama_kegiatan,
        CAST(nominal AS DECIMAL(15,2)) as nominal,
        MONTH(STR_TO_DATE(tanggal, '%Y-%m-%d')) as bulan,
        YEAR(STR_TO_DATE(tanggal, '%Y-%m-%d')) as tahun
      FROM spj_lomba
      WHERE YEAR(STR_TO_DATE(tanggal, '%Y-%m-%d')) = ?
      ORDER BY tanggal
    `, [year]);

    // Create nota total map
    const notaTotals = {};
    for (const nota of notaRows) {
      notaTotals[nota.nomor_bap] = parseFloat(nota.total_nilai) || 0;
    }

    // Initialize monthly stats (1-12)
    const monthlyStats = {};
    const monthNames = [
      'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
      'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
    ];

    for (let i = 1; i <= 12; i++) {
      monthlyStats[i] = {
        bulan: i,
        namaBulan: monthNames[i - 1],
        jumlahSPJ: 0,
        totalNilai: 0,
        jumlahSPJLomba: 0,
        totalNilaiSPJLomba: 0,
        details: [],
        detailsSpjLomba: []
      };
    }

    // Populate stats from BAP data
    let grandTotalSPJ = 0;
    let grandTotalNilai = 0;

    for (const bap of bapRows) {
      const bulan = bap.bulan;
      if (bulan && monthlyStats[bulan]) {
        const totalNilai = notaTotals[bap.nomor_bap] || 0;

        monthlyStats[bulan].jumlahSPJ++;
        monthlyStats[bulan].totalNilai += totalNilai;
        monthlyStats[bulan].details.push({
          nomorBap: bap.nomor_bap,
          tanggal: bap.tanggal_pemeriksaan,
          pekerjaan: bap.pekerjaan,
          totalNilai: totalNilai
        });

        grandTotalSPJ++;
        grandTotalNilai += totalNilai;
      }
    }

    // Populate stats from SPJ Lomba data
    let grandTotalSPJLomba = 0;
    let grandTotalNilaiSPJLomba = 0;

    for (const spj of spjLombaRows) {
      const bulan = spj.bulan;
      if (bulan && monthlyStats[bulan]) {
        const nominal = parseFloat(spj.nominal) || 0;

        monthlyStats[bulan].jumlahSPJLomba++;
        monthlyStats[bulan].totalNilaiSPJLomba += nominal;
        monthlyStats[bulan].detailsSpjLomba.push({
          nomorSpj: spj.nomor_spj,
          tanggal: spj.tanggal,
          namaKegiatan: spj.nama_kegiatan,
          nominal: nominal
        });

        grandTotalSPJLomba++;
        grandTotalNilaiSPJLomba += nominal;
      }
    }

    // Get available years for dropdown (from both BAP and SPJ Lomba)
    const [yearRows] = await pool.query(`
      SELECT DISTINCT tahun FROM (
        SELECT YEAR(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) as tahun
        FROM bap
        WHERE tanggal_pemeriksaan IS NOT NULL AND tanggal_pemeriksaan != ''
        UNION
        SELECT YEAR(STR_TO_DATE(tanggal, '%Y-%m-%d')) as tahun
        FROM spj_lomba
        WHERE tanggal IS NOT NULL AND tanggal != ''
      ) as combined_years
      ORDER BY tahun DESC
    `);

    const availableYears = yearRows.map(r => r.tahun).filter(y => y);

    res.json({
      success: true,
      data: {
        year: parseInt(year),
        availableYears,
        monthly: Object.values(monthlyStats),
        grandTotal: {
          jumlahSPJ: grandTotalSPJ,
          totalNilai: grandTotalNilai,
          jumlahSPJLomba: grandTotalSPJLomba,
          totalNilaiSPJLomba: grandTotalNilaiSPJLomba,
          totalKeseluruhan: grandTotalNilai + grandTotalNilaiSPJLomba
        }
      }
    });
  } catch (error) {
    console.error('Error getting SPJ stats:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});


// ============================================
// DATABASE-BASED ASSET MANAGEMENT ENDPOINTS
// ============================================

// API: Get available assets from database (logo and kop)
app.get('/api/assets', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT tipe, data_url FROM aset_aplikasi');
    const assets = {
      logo: null,
      kop: null
    };

    for (const row of rows) {
      if (row.tipe === 'logo' && row.data_url) {
        assets.logo = row.data_url;
      } else if (row.tipe === 'kop' && row.data_url) {
        assets.kop = row.data_url;
      }
    }

    res.json(assets);
  } catch (error) {
    console.error('Error getting assets:', error);
    res.json({ logo: null, kop: null });
  }
});

// API: Upload/save asset to database (logo or kop)
app.post('/api/assets/:type', async (req, res) => {
  const assetType = req.params.type;

  if (assetType !== 'logo' && assetType !== 'kop') {
    return res.status(400).json({ error: 'Tipe asset tidak valid. Gunakan "logo" atau "kop".' });
  }

  const { dataUrl } = req.body;

  if (!dataUrl) {
    return res.status(400).json({ error: 'Data URL gambar diperlukan.' });
  }

  try {
    await pool.query(
      'INSERT INTO aset_aplikasi (tipe, data_url) VALUES (?, ?) ON DUPLICATE KEY UPDATE data_url = ?',
      [assetType, dataUrl, dataUrl]
    );

    res.json({
      success: true,
      message: assetType.toUpperCase() + ' berhasil disimpan ke database!'
    });
  } catch (error) {
    console.error('Error saving asset:', error);
    res.status(500).json({ error: 'Gagal menyimpan asset: ' + error.message });
  }
});

// API: Delete asset from database (logo or kop)
app.delete('/api/assets/:type', async (req, res) => {
  const assetType = req.params.type;

  if (assetType !== 'logo' && assetType !== 'kop') {
    return res.status(400).json({ error: 'Tipe asset tidak valid.' });
  }

  try {
    await pool.query(
      'UPDATE aset_aplikasi SET data_url = NULL WHERE tipe = ?',
      [assetType]
    );

    res.json({ success: true, message: assetType.toUpperCase() + ' berhasil dihapus dari database!' });
  } catch (error) {
    console.error('Error deleting asset:', error);
    res.status(500).json({ error: 'Gagal menghapus asset: ' + error.message });
  }
});

// ============================================
// PENYEDIA (SUPPLIER) MANAGEMENT ENDPOINTS
// ============================================

// API: Get all penyedia
app.get('/api/penyedia', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT id, nama, alamat, kop_url FROM penyedia ORDER BY nama');
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error getting penyedia:', error);
    res.json({ success: false, error: error.message });
  }
});

// API: Get single penyedia by name
app.get('/api/penyedia/by-name/:nama', async (req, res) => {
  try {
    const nama = req.params.nama;
    const [rows] = await pool.query('SELECT id, nama, alamat, kop_url FROM penyedia WHERE nama = ?', [nama]);
    if (rows.length > 0) {
      res.json({ success: true, data: rows[0] });
    } else {
      res.json({ success: true, data: null });
    }
  } catch (error) {
    console.error('Error getting penyedia by name:', error);
    res.json({ success: false, error: error.message });
  }
});

// API: Save or update penyedia
app.post('/api/penyedia', async (req, res) => {
  try {
    const { nama, alamat, kop_url } = req.body;

    if (!nama || !nama.trim()) {
      return res.status(400).json({ success: false, error: 'Nama penyedia diperlukan' });
    }

    // Check if penyedia already exists
    const [existing] = await pool.query('SELECT id FROM penyedia WHERE nama = ?', [nama.trim()]);

    if (existing.length > 0) {
      // Update existing penyedia
      const updateFields = ['alamat = ?'];
      const updateValues = [alamat || null];

      if (kop_url !== undefined) {
        updateFields.push('kop_url = ?');
        updateValues.push(kop_url);
      }

      updateValues.push(existing[0].id);

      await pool.query(
        `UPDATE penyedia SET ${updateFields.join(', ')} WHERE id = ?`,
        updateValues
      );

      res.json({ success: true, message: 'Penyedia berhasil diperbarui!', id: existing[0].id });
    } else {
      // Insert new penyedia
      const [result] = await pool.query(
        'INSERT INTO penyedia (nama, alamat, kop_url) VALUES (?, ?, ?)',
        [nama.trim(), alamat || null, kop_url || null]
      );

      res.json({ success: true, message: 'Penyedia baru berhasil disimpan!', id: result.insertId });
    }
  } catch (error) {
    console.error('Error saving penyedia:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Update penyedia KOP only
app.post('/api/penyedia/:id/kop', async (req, res) => {
  try {
    const id = req.params.id;
    const { kop_url } = req.body;

    await pool.query('UPDATE penyedia SET kop_url = ? WHERE id = ?', [kop_url, id]);

    res.json({ success: true, message: 'KOP penyedia berhasil diperbarui!' });
  } catch (error) {
    console.error('Error updating penyedia KOP:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// API: Delete penyedia
app.delete('/api/penyedia/:id', async (req, res) => {
  try {
    const id = req.params.id;
    await pool.query('DELETE FROM penyedia WHERE id = ?', [id]);
    res.json({ success: true, message: 'Penyedia berhasil dihapus!' });
  } catch (error) {
    console.error('Error deleting penyedia:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// ============================================
// SPJ LOMBA ENDPOINTS
// ============================================

// Update SPJ Lomba
app.put('/api/spj-lomba/:nomor', async (req, res) => {
  try {
    const originalNomor = decodeURIComponent(req.params.nomor);
    const { data } = req.body;

    if (!data) {
      return res.status(400).json({ success: false, data: { error: 'Data tidak valid' } });
    }

    const mappedData = mapColumnsToDB('spj_lomba', data);

    // Build UPDATE query dynamically
    const setClause = Object.keys(mappedData)
      .map(key => `${key} = ?`)
      .join(', ');
    const values = [...Object.values(mappedData), originalNomor];

    const query = `UPDATE spj_lomba SET ${setClause} WHERE nomor_spj = ?`;
    await pool.query(query, values);

    res.json({ success: true, data: { message: 'SPJ Lomba berhasil diperbarui!' } });
  } catch (error) {
    console.error('Error updating SPJ Lomba:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// Delete SPJ Lomba
app.delete('/api/spj-lomba/:nomor', async (req, res) => {
  try {
    const nomor = decodeURIComponent(req.params.nomor);
    await pool.query('DELETE FROM spj_lomba WHERE nomor_spj = ?', [nomor]);
    res.json({ success: true, data: { message: 'SPJ Lomba berhasil dihapus!' } });
  } catch (error) {
    console.error('Error deleting SPJ Lomba:', error);
    res.json({ success: false, data: { error: error.message } });
  }
});

// ============================================
// FILE-BASED ASSET FOR SUPPLIER KOP ONLY
// ============================================

// API: Upload supplier kop (file-based, stored locally)
app.post('/api/upload/:type', (req, res) => {
  const uploadType = req.params.type;

  // Only allow 'supplier' type for file-based upload
  if (uploadType !== 'supplier') {
    return res.status(400).json({ error: 'Tipe upload tidak valid. Gunakan API /api/assets/:type untuk logo dan kop.' });
  }

  const extensions = ['.png', '.jpg', '.jpeg', '.gif', '.webp'];
  for (const ext of extensions) {
    const filePath = path.join(assetsPath, uploadType + ext);
    if (fs.existsSync(filePath)) {
      fs.unlinkSync(filePath);
    }
  }

  upload.single('file')(req, res, function (err) {
    if (err instanceof multer.MulterError) {
      if (err.code === 'LIMIT_FILE_SIZE') {
        return res.status(400).json({ error: 'Ukuran file terlalu besar. Maksimal 5MB.' });
      }
      return res.status(400).json({ error: 'Upload error: ' + err.message });
    } else if (err) {
      return res.status(400).json({ error: err.message });
    }

    if (!req.file) {
      return res.status(400).json({ error: 'Tidak ada file yang diupload.' });
    }

    const fileUrl = '/assets/' + req.file.filename + '?t=' + Date.now();
    res.json({
      success: true,
      message: uploadType.toUpperCase() + ' berhasil diupload!',
      url: fileUrl
    });
  });
});

// Sajikan file statis dari direktori build React ('dist')
app.use(express.static(buildPath));

// Handler "catchall": untuk setiap request, kirim kembali file index.html React.
app.get('*', (req, res) => {
  res.sendFile(path.join(buildPath, 'index.html'));
});

// Start server
app.listen(port, async () => {
  console.log(`Server berjalan di http://localhost:${port}`);
  console.log(`Menyajikan konten dari: ${buildPath}`);
  console.log(`Assets tersedia di: ${assetsPath}`);

  // Test and initialize database
  const isConnected = await testDatabaseConnection();
  if (isConnected) {
    await initializeDatabase();
    await seedKodeKegiatan();
    await seedRks();
  }
});