// Production Server - Serves both Frontend and API
import express from 'express';
import cors from 'cors';
import path from 'path';
import { fileURLToPath } from 'url';
import dotenv from 'dotenv';
import mysql from 'mysql2/promise';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// Optional compression for better bandwidth efficiency
let compression;
try {
  compression = (await import('compression')).default;
  console.log('✅ Compression enabled');
} catch (e) {
  console.log('ℹ️ Compression not found. Run "npm install compression" for better performance.');
}

dotenv.config({ path: path.join(__dirname, '.env') });

const app = express();
if (compression) {
  app.use(compression());
}
const PORT = process.env.API_PORT || process.env.PORT || 3001;

// Database connection pool
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD?.replace(/^['"]|['"]$/g, '') || '',
  database: process.env.DB_NAME || 'jurnal_db',
  port: process.env.DB_PORT || 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Test database connection and initialize tables
const initializeDatabase = async () => {
  let connection;
  try {
    connection = await pool.getConnection();
    console.log('✅ Database connected successfully!');

    // 1. Users Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        identifier VARCHAR(50) UNIQUE,
        password VARCHAR(255) NOT NULL,
        role ENUM('ADMIN', 'GURU', 'SEKRETARIS') NOT NULL DEFAULT 'GURU',
        class_name VARCHAR(20) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_identifier (identifier)
      ) ENGINE=InnoDB
    `);

    // 2. Teachers Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS teachers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        nip VARCHAR(30) UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_nip (nip)
      ) ENGINE=InnoDB
    `);

    // 3. Students Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        nis VARCHAR(20) UNIQUE,
        class_name VARCHAR(20) NOT NULL,
        gender ENUM('L', 'P') NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_nis (nis),
        INDEX idx_class (class_name)
      ) ENGINE=InnoDB
    `);

    // 4. Subjects Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS subjects (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB
    `);

    // 5. Journal Entries Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS journal_entries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        journal_id VARCHAR(50) UNIQUE NOT NULL,
        date DATE NOT NULL,
        teacher_name VARCHAR(100) NOT NULL,
        nip VARCHAR(30),
        class_name VARCHAR(20) NOT NULL,
        subject VARCHAR(100) NOT NULL,
        topic TEXT,
        start_time VARCHAR(10),
        end_time VARCHAR(10),
        students_present INT DEFAULT 0,
        sakit INT DEFAULT 0,
        izin INT DEFAULT 0,
        alpha INT DEFAULT 0,
        dispen INT DEFAULT 0,
        notes TEXT,
        photo_url LONGTEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_journal_id (journal_id),
        INDEX idx_date (date),
        INDEX idx_class (class_name)
      ) ENGINE=InnoDB
    `);

    // 6. Class Journal Entries Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS class_journal_entries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        journal_id VARCHAR(50) UNIQUE NOT NULL,
        date DATE NOT NULL,
        teacher_name VARCHAR(100) NOT NULL,
        subject VARCHAR(100) NOT NULL,
        class_name VARCHAR(20) NOT NULL,
        teacher_presence ENUM('Hadir', 'Tugas', 'Tidak Hadir') DEFAULT 'Hadir',
        start_time VARCHAR(10),
        end_time VARCHAR(10),
        students_present INT DEFAULT 0,
        sakit INT DEFAULT 0,
        izin INT DEFAULT 0,
        alpha INT DEFAULT 0,
        dispen INT DEFAULT 0,
        notes TEXT,
        class_secretary VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_journal_id (journal_id),
        INDEX idx_date (date),
        INDEX idx_class (class_name)
      ) ENGINE=InnoDB
    `);

    // 7. Attendance Details Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS attendance_details (
        id INT AUTO_INCREMENT PRIMARY KEY,
        journal_type ENUM('teacher', 'class') NOT NULL,
        journal_id VARCHAR(50) NOT NULL,
        student_name VARCHAR(100) NOT NULL,
        status CHAR(1) NOT NULL DEFAULT 'H',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_journal (journal_type, journal_id)
      ) ENGINE=InnoDB
    `);

    // 8. School Settings Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS school_settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(50) UNIQUE NOT NULL,
        setting_value TEXT,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB
    `);

    // 9. Classes Table (for class management)
    await connection.query(`
      CREATE TABLE IF NOT EXISTS classes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE,
        grade VARCHAR(10) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_name (name),
        INDEX idx_grade (grade)
      ) ENGINE=InnoDB
    `);

    // Ensure default settings exist
    await connection.query(`
      INSERT IGNORE INTO school_settings (setting_key, setting_value) VALUES
      ('semester', 'Ganjil'), ('academic_year', '2025/2026'),
      ('headmaster_name', ''), ('headmaster_nip', ''),
      ('logo_url', ''), ('kop_url', '')
    `);

    // Alter photo_url column to LONGTEXT if it exists (for existing databases)
    try {
      await connection.query(`ALTER TABLE journal_entries MODIFY COLUMN photo_url LONGTEXT`);
      console.log('✅ photo_url column altered to LONGTEXT');
    } catch (alterErr) {
      // Column might already be LONGTEXT or table doesn't exist yet - ignore
      console.log('ℹ️ photo_url column alteration skipped (may already be correct type)');
    }

    // Ensure Admin user exists
    await connection.query(`
      INSERT IGNORE INTO users (name, identifier, password, role) VALUES
      ('Administrator', 'AdminJurnal#1', 'Smanab100%', 'ADMIN')
    `);

    console.log('✅ Tables initialized/verified successfully');
    connection.release();
    return true;
  } catch (error) {
    if (connection) connection.release();
    console.error('❌ Database initialization failed:', error.message);
    return false;
  }
};

// Middleware
app.use(cors({
  origin: true,
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS'],
  allowedHeaders: ['Content-Type', 'Authorization'],
  credentials: true
}));

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

// Logging middleware
app.use((req, res, next) => {
  console.log(`${new Date().toISOString()} - ${req.method} ${req.url}`);
  next();
});

// Serve static files
app.use('/images', express.static(path.join(__dirname, 'public/images')));
app.use('/uploads', express.static(path.join(__dirname, 'public/uploads')));

// ==================== CACHE IMPLEMENTATION ====================
// Simple in-memory cache for frequently accessed data
const cache = {
  data: {},
  ttl: {},
  set(key, value, ttlSeconds = 60) {
    this.data[key] = value;
    this.ttl[key] = Date.now() + (ttlSeconds * 1000);
  },
  get(key) {
    if (this.ttl[key] && Date.now() > this.ttl[key]) {
      delete this.data[key];
      delete this.ttl[key];
      return null;
    }
    return this.data[key] || null;
  },
  clear(pattern) {
    if (pattern) {
      Object.keys(this.data).forEach(key => {
        if (key.includes(pattern)) {
          delete this.data[key];
          delete this.ttl[key];
        }
      });
    } else {
      this.data = {};
      this.ttl = {};
    }
  }
};

// ==================== API ROUTES ====================

// Health check
app.get('/api/health', async (req, res) => {
  const dbConnected = await initializeDatabase();
  res.json({
    status: 'ok',
    database: dbConnected ? 'connected' : 'disconnected',
    timestamp: new Date().toISOString()
  });
});

// ==================== OPTIMIZED ENDPOINTS FOR FASTER LOADING ====================

// Fast login endpoint - only fetches user data, not entire database
app.post('/api/auth/login', async (req, res) => {
  try {
    const { identifier, password, role } = req.body;

    if (!identifier || !password) {
      return res.status(400).json({ status: 'error', message: 'Identifier dan password wajib diisi' });
    }

    // For ADMIN login
    if (role === 'ADMIN' || (!role && identifier.toLowerCase().includes('admin'))) {
      const [users] = await pool.query(
        'SELECT * FROM users WHERE identifier = ? AND password = ? AND role = ?',
        [identifier, password, 'ADMIN']
      );
      if (users.length > 0) {
        return res.json({
          status: 'success',
          data: {
            id: users[0].id,
            name: users[0].name,
            role: 'ADMIN',
            identifier: users[0].identifier
          }
        });
      }
    }

    // For GURU login (using NIP) - password is same as NIP
    if (role === 'GURU') {
      const [teachers] = await pool.query('SELECT * FROM teachers WHERE nip = ?', [identifier]);
      if (teachers.length > 0 && password === identifier) {
        return res.json({
          status: 'success',
          data: {
            id: teachers[0].id,
            name: teachers[0].name,
            role: 'GURU',
            identifier: teachers[0].nip
          }
        });
      }
    }

    // For SEKRETARIS (student class secretary) login (using NIS) - password is same as NIS
    if (role === 'SEKRETARIS') {
      const [students] = await pool.query('SELECT * FROM students WHERE nis = ?', [identifier]);
      if (students.length > 0 && password === identifier) {
        return res.json({
          status: 'success',
          data: {
            id: students[0].id,
            name: students[0].name,
            role: 'SEKRETARIS',
            identifier: students[0].nis,
            className: students[0].class_name
          }
        });
      }
    }

    res.status(401).json({ status: 'error', message: 'Identifier atau password salah' });
  } catch (error) {
    console.error('Error during login:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// Get student by NIS - fast lookup
app.get('/api/students/nis/:nis', async (req, res) => {
  try {
    const { nis } = req.params;
    const [students] = await pool.query(
      'SELECT * FROM students WHERE nis = ?',
      [nis]
    );

    if (students.length === 0) {
      return res.status(404).json({ status: 'error', message: 'Siswa tidak ditemukan' });
    }

    res.json({ status: 'success', data: students[0] });
  } catch (error) {
    console.error('Error fetching student by NIS:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// Get students by class with pagination - for large datasets
app.get('/api/students/class/:className', async (req, res) => {
  try {
    const { className } = req.params;
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 50;
    const offset = (page - 1) * limit;

    // Check cache first
    const cacheKey = `students_${className}_${page}_${limit}`;
    const cached = cache.get(cacheKey);
    if (cached) {
      return res.json(cached);
    }

    const [students] = await pool.query(
      'SELECT id, name, nis, class_name, gender FROM students WHERE class_name = ? ORDER BY name LIMIT ? OFFSET ?',
      [className, limit, offset]
    );

    const [countResult] = await pool.query(
      'SELECT COUNT(*) as total FROM students WHERE class_name = ?',
      [className]
    );

    const response = {
      status: 'success',
      data: students,
      pagination: {
        page,
        limit,
        total: countResult[0].total,
        totalPages: Math.ceil(countResult[0].total / limit)
      }
    };

    // Cache for 30 seconds
    cache.set(cacheKey, response, 30);

    res.json(response);
  } catch (error) {
    console.error('Error fetching students by class:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// ==================== ULTRA-LITE ENDPOINT - For Fast Initial Load ====================
// This endpoint returns ONLY the minimum data needed for the login screen
// NO journals, NO attendance details - those are loaded after login
app.get('/api/data/ultra-lite', async (req, res) => {
  try {
    // Check cache first - cache for 5 minutes since this is static data
    const cached = cache.get('data_ultra_lite');
    if (cached) {
      return res.json(cached);
    }

    // Parallel queries for essential data only
    const [
      [teachers],
      [classesFromTable],
      [classNamesFromStudents],
      [subjects],
      [settingsRows],
      [students]
    ] = await Promise.all([
      pool.query('SELECT id, name, nip FROM teachers ORDER BY name'),
      pool.query('SELECT id, name, grade FROM classes ORDER BY grade, name'),
      pool.query('SELECT DISTINCT class_name FROM students WHERE class_name IS NOT NULL AND class_name != "" ORDER BY class_name'),
      pool.query('SELECT name FROM subjects ORDER BY name'),
      pool.query('SELECT setting_key, setting_value FROM school_settings'),
      pool.query('SELECT id, name, nis, class_name, gender FROM students ORDER BY class_name, name')
    ]);

    const settings = {};
    settingsRows.forEach(row => {
      settings[row.setting_key] = row.setting_value;
    });

    // Merge classes
    const classSet = new Set();
    classesFromTable.forEach(c => classSet.add(c.name));
    classNamesFromStudents.forEach(c => {
      if (c.class_name) classSet.add(c.class_name);
    });
    const mergedClasses = Array.from(classSet).sort();

    const response = {
      status: 'success',
      data: {
        guru: teachers.map(t => ({ 'Nama Guru': t.name, 'NIP': t.nip || '' })),
        murid: students.map(s => ({ 'Nama Murid': s.name, 'NIS': s.nis || '', 'Kelas': s.class_name, 'L/P': s.gender || '' })),
        classes: mergedClasses,
        classesData: classesFromTable,
        mapel: subjects.map(s => ({ 'Mata Pelajaran': s.name })),
        // NO jurnal - loaded after login
        jurnal: [],
        jurnalKelas: [],
        settings: {
          'Semester': settings.semester || 'Ganjil',
          'TahunPelajaran': settings.academic_year || '2024/2025',
          'NamaKepalaSekolah': settings.headmaster_name || '',
          'NipKepalaSekolah': settings.headmaster_nip || '',
          'LogoUrl': settings.logo_url || '',
          'KopUrl': settings.kop_url || ''
        }
      }
    };

    // Cache for 5 minutes - this data rarely changes
    cache.set('data_ultra_lite', response, 300);

    res.json(response);
  } catch (error) {
    console.error('Error fetching ultra-lite data:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// Lightweight data endpoint - only essential data for initial load
app.get('/api/data/lite', async (req, res) => {
  try {
    // Check cache first
    const cached = cache.get('data_lite');
    if (cached) {
      return res.json(cached);
    }

    // Parallel queries for faster loading - now includes journals for recap
    const [
      [teachers],
      [classesFromTable],
      [classNamesFromStudents],
      [subjects],
      [settingsRows],
      [students],
      [journals],
      [classJournals]
    ] = await Promise.all([
      pool.query('SELECT id, name, nip FROM teachers ORDER BY name'),
      pool.query('SELECT id, name, grade FROM classes ORDER BY grade, name'),
      pool.query('SELECT DISTINCT class_name FROM students WHERE class_name IS NOT NULL AND class_name != "" ORDER BY class_name'),
      pool.query('SELECT name FROM subjects ORDER BY name'),
      pool.query('SELECT setting_key, setting_value FROM school_settings'),
      pool.query('SELECT id, name, nis, class_name, gender FROM students ORDER BY class_name, name'),
      // Load ALL journal entries - periodical data must be complete to prevent double entries
      pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM journal_entries ORDER BY created_at DESC"),
      pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM class_journal_entries ORDER BY created_at DESC")
    ]);

    const settings = {};
    settingsRows.forEach(row => {
      settings[row.setting_key] = row.setting_value;
    });

    // Merge classes from both sources (prefer classes table, supplement with student class names)
    const classSet = new Set();
    classesFromTable.forEach(c => classSet.add(c.name));
    classNamesFromStudents.forEach(c => {
      if (c.class_name) classSet.add(c.class_name);
    });
    const mergedClasses = Array.from(classSet).sort();

    // Fetch attendance details for journals
    const journalIds = journals.map(j => j.journal_id);
    const classJournalIds = classJournals.map(j => j.journal_id);

    let teacherAttendanceMap = {};
    let classAttendanceMap = {};

    const attendanceQueries = [];
    if (journalIds.length > 0) {
      attendanceQueries.push(
        pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['teacher', journalIds])
      );
    } else {
      attendanceQueries.push(Promise.resolve([[]]));
    }

    if (classJournalIds.length > 0) {
      attendanceQueries.push(
        pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['class', classJournalIds])
      );
    } else {
      attendanceQueries.push(Promise.resolve([[]]));
    }

    const [[teacherAttDetails], [classAttDetails]] = await Promise.all(attendanceQueries);

    teacherAttDetails.forEach(ad => {
      if (!teacherAttendanceMap[ad.journal_id]) teacherAttendanceMap[ad.journal_id] = [];
      teacherAttendanceMap[ad.journal_id].push({ studentName: ad.student_name, status: ad.status });
    });

    classAttDetails.forEach(ad => {
      if (!classAttendanceMap[ad.journal_id]) classAttendanceMap[ad.journal_id] = [];
      classAttendanceMap[ad.journal_id].push({ studentName: ad.student_name, status: ad.status });
    });

    const response = {
      status: 'success',
      data: {
        guru: teachers.map(t => ({ 'Nama Guru': t.name, 'NIP': t.nip || '' })),
        murid: students.map(s => ({ 'Nama Murid': s.name, 'NIS': s.nis || '', 'Kelas': s.class_name, 'L/P': s.gender || '' })),
        classes: mergedClasses,
        classesData: classesFromTable,
        mapel: subjects.map(s => ({ 'Mata Pelajaran': s.name })),
        jurnal: journals.map(j => ({
          'ID Jurnal': j.journal_id,
          'Tanggal': j.formatted_date || j.date,
          'Timestamp': j.created_at,
          'Nama Guru': j.teacher_name,
          'NIP': j.nip || '',
          'Kelas': j.class_name,
          'Mata Pelajaran': j.subject,
          'Materi': j.topic || '',
          'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
          'Jumlah Hadir': j.students_present,
          'Jumlah Sakit': j.sakit,
          'Jumlah Izin': j.izin,
          'Jumlah Alpha': j.alpha,
          'Jumlah Dispen': j.dispen,
          'Keterangan': j.notes || '',
          'Foto': j.photo_url || '',
          'Detail Absensi': teacherAttendanceMap[j.journal_id] || []
        })),
        jurnalKelas: classJournals.map(j => ({
          'ID Jurnal Kelas': j.journal_id,
          'Tanggal': j.formatted_date || j.date,
          'Timestamp': j.created_at,
          'Nama Guru': j.teacher_name,
          'Mata Pelajaran': j.subject,
          'Kelas': j.class_name,
          'Kehadiran Guru': j.teacher_presence,
          'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
          'Jumlah Hadir': j.students_present,
          'Jumlah Sakit': j.sakit,
          'Jumlah Izin': j.izin,
          'Jumlah Alpha': j.alpha,
          'Jumlah Dispen': j.dispen,
          'Keterangan': j.notes || '',
          'Sekretaris Kelas': j.class_secretary || '',
          'Detail Absensi': classAttendanceMap[j.journal_id] || []
        })),
        settings: {
          'Semester': settings.semester || 'Ganjil',
          'TahunPelajaran': settings.academic_year || '2024/2025',
          'NamaKepalaSekolah': settings.headmaster_name || '',
          'NipKepalaSekolah': settings.headmaster_nip || '',
          'LogoUrl': settings.logo_url || '',
          'KopUrl': settings.kop_url || ''
        }
      }
    };

    // Cache for 30 seconds (same as full data)
    cache.set('data_lite', response, 30);

    res.json(response);
  } catch (error) {
    console.error('Error fetching lite data:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});


// ==================== TEACHERS CRUD ====================

// GET all teachers
app.get('/api/teachers', async (req, res) => {
  try {
    const [teachers] = await pool.query('SELECT * FROM teachers ORDER BY name');
    res.json({ status: 'success', data: teachers });
  } catch (error) {
    console.error('Error fetching teachers:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// POST new teacher
app.post('/api/teachers', async (req, res) => {
  try {
    const { name, nip } = req.body;
    if (!name) {
      return res.status(400).json({ status: 'error', message: 'Nama guru wajib diisi' });
    }
    const [result] = await pool.query('INSERT INTO teachers (name, nip) VALUES (?, ?)', [name, nip || null]);
    res.json({ status: 'success', message: 'Guru berhasil ditambahkan', data: { id: result.insertId, name, nip } });
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(400).json({ status: 'error', message: 'NIP sudah terdaftar' });
    }
    console.error('Error adding teacher:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// PUT update teacher
app.put('/api/teachers/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, nip } = req.body;
    await pool.query('UPDATE teachers SET name = ?, nip = ? WHERE id = ?', [name, nip, id]);
    res.json({ status: 'success', message: 'Data guru berhasil diupdate' });
  } catch (error) {
    console.error('Error updating teacher:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// DELETE teacher
app.delete('/api/teachers/:id', async (req, res) => {
  try {
    const { id } = req.params;
    console.log(`[DELETE] Deleting teacher with ID: ${id}`);
    await pool.query('DELETE FROM teachers WHERE id = ?', [id]);
    res.json({ status: 'success', message: 'Guru berhasil dihapus' });
  } catch (error) {
    console.error('Error deleting teacher:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// ==================== STUDENTS CRUD ====================

// GET all students
app.get('/api/students', async (req, res) => {
  try {
    const { className } = req.query;
    let query = 'SELECT * FROM students ORDER BY class_name, name';
    let params = [];
    if (className) {
      query = 'SELECT * FROM students WHERE class_name = ? ORDER BY name';
      params = [className];
    }
    const [students] = await pool.query(query, params);
    res.json({ status: 'success', data: students });
  } catch (error) {
    console.error('Error fetching students:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// GET unique class names
app.get('/api/students/classes', async (req, res) => {
  try {
    const [classes] = await pool.query('SELECT DISTINCT class_name FROM students ORDER BY class_name');
    res.json({ status: 'success', data: classes.map(c => c.class_name) });
  } catch (error) {
    console.error('Error fetching classes:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// POST new student
app.post('/api/students', async (req, res) => {
  try {
    const { name, nis, class_name, gender } = req.body;
    if (!name || !class_name) {
      return res.status(400).json({ status: 'error', message: 'Nama dan kelas wajib diisi' });
    }
    const [result] = await pool.query(
      'INSERT INTO students (name, nis, class_name, gender) VALUES (?, ?, ?, ?)',
      [name, nis || null, class_name, gender || null]
    );
    res.json({ status: 'success', message: 'Siswa berhasil ditambahkan', data: { id: result.insertId, name, nis, class_name, gender } });
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(400).json({ status: 'error', message: 'NIS sudah terdaftar' });
    }
    console.error('Error adding student:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// PUT update student
app.put('/api/students/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, nis, class_name, gender } = req.body;
    await pool.query(
      'UPDATE students SET name = ?, nis = ?, class_name = ?, gender = ? WHERE id = ?',
      [name, nis, class_name, gender, id]
    );
    res.json({ status: 'success', message: 'Data siswa berhasil diupdate' });
  } catch (error) {
    console.error('Error updating student:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// DELETE student
app.delete('/api/students/:id', async (req, res) => {
  try {
    const { id } = req.params;
    console.log(`[DELETE] Deleting student with ID: ${id}`);
    await pool.query('DELETE FROM students WHERE id = ?', [id]);
    res.json({ status: 'success', message: 'Siswa berhasil dihapus' });
  } catch (error) {
    console.error('Error deleting student:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// ==================== CLASSES CRUD ====================

// GET all classes
app.get('/api/classes', async (req, res) => {
  try {
    const [classes] = await pool.query('SELECT * FROM classes ORDER BY grade, name');
    res.json({ status: 'success', data: classes });
  } catch (error) {
    console.error('Error fetching classes:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// POST new class
app.post('/api/classes', async (req, res) => {
  try {
    const { name, grade } = req.body;
    if (!name || !grade) {
      return res.status(400).json({ status: 'error', message: 'Nama dan tingkat kelas wajib diisi' });
    }
    const [result] = await pool.query('INSERT INTO classes (name, grade) VALUES (?, ?)', [name, grade]);
    res.json({ status: 'success', message: 'Kelas berhasil ditambahkan', data: { id: result.insertId, name, grade } });
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(400).json({ status: 'error', message: 'Nama kelas sudah ada' });
    }
    console.error('Error adding class:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// PUT update class
app.put('/api/classes/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, grade } = req.body;
    await pool.query('UPDATE classes SET name = ?, grade = ? WHERE id = ?', [name, grade, id]);
    res.json({ status: 'success', message: 'Data kelas berhasil diupdate' });
  } catch (error) {
    console.error('Error updating class:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

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

// POST promote classes (Naik Kelas)
app.post('/api/promote-classes', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    // 1. XII -> Lulus
    // Update students in classes with grade XII to class_name 'LULUS'
    await connection.query(`
      UPDATE students 
      SET class_name = 'LULUS' 
      WHERE class_name IN (SELECT name FROM classes WHERE grade = 'XII')
    `);

    // 2. XI -> XII
    // We need to find the "next" class name. 
    // Usually it's replacing 'XI' with 'XII' in the name.
    const [xiClasses] = await connection.query("SELECT name FROM classes WHERE grade = 'XI'");
    for (const cls of xiClasses) {
      const nextClassName = cls.name.replace('XI', 'XII');

      // Check if XII class exists
      const [exists] = await connection.query("SELECT id FROM classes WHERE name = ?", [nextClassName]);
      if (exists.length > 0) {
        await connection.query("UPDATE students SET class_name = ? WHERE class_name = ?", [nextClassName, cls.name]);
      }
    }

    // 3. X -> XI
    const [xClasses] = await connection.query("SELECT name FROM classes WHERE grade = 'X'");
    for (const cls of xClasses) {
      const nextClassName = cls.name.replace('X', 'XI');

      // Check if XI class exists
      const [exists] = await connection.query("SELECT id FROM classes WHERE name = ?", [nextClassName]);
      if (exists.length > 0) {
        await connection.query("UPDATE students SET class_name = ? WHERE class_name = ?", [nextClassName, cls.name]);
      }
    }

    // 4. Update the grades of the classes themselves?
    // User said "mempermudah periodikal". 
    // Usually, we don't change class names (e.g., X MIPA 1 is always X MIPA 1).
    // But students move from X MIPA 1 to XI MIPA 1.
    // So my above logic of updating students' class_name is correct.

    await connection.commit();
    res.json({ status: 'success', message: 'Proses kenaikan kelas berhasil diselesaikan' });
  } catch (error) {
    await connection.rollback();
    console.error('Error promoting classes:', error);
    res.status(500).json({ status: 'error', message: error.message });
  } finally {
    connection.release();
  }
});

// ==================== SUBJECTS CRUD ====================

// GET all subjects
app.get('/api/subjects', async (req, res) => {
  try {
    const [subjects] = await pool.query('SELECT * FROM subjects ORDER BY name');
    res.json({ status: 'success', data: subjects });
  } catch (error) {
    console.error('Error fetching subjects:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// POST new subject
app.post('/api/subjects', async (req, res) => {
  try {
    const { name } = req.body;
    if (!name) {
      return res.status(400).json({ status: 'error', message: 'Nama mata pelajaran wajib diisi' });
    }
    const [result] = await pool.query('INSERT INTO subjects (name) VALUES (?)', [name]);
    res.json({ status: 'success', message: 'Mata pelajaran berhasil ditambahkan', data: { id: result.insertId, name } });
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(400).json({ status: 'error', message: 'Mata pelajaran sudah ada' });
    }
    console.error('Error adding subject:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// PUT update subject
app.put('/api/subjects/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name } = req.body;
    await pool.query('UPDATE subjects SET name = ? WHERE id = ?', [name, id]);
    res.json({ status: 'success', message: 'Mata pelajaran berhasil diupdate' });
  } catch (error) {
    console.error('Error updating subject:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// DELETE subject
app.delete('/api/subjects/:id', async (req, res) => {
  try {
    const { id } = req.params;
    console.log(`[DELETE] Deleting subject with ID: ${id}`);
    await pool.query('DELETE FROM subjects WHERE id = ?', [id]);
    res.json({ status: 'success', message: 'Mata pelajaran berhasil dihapus' });
  } catch (error) {
    console.error('Error deleting subject:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// ==================== DATA ENDPOINT ====================

app.get('/api/data', async (req, res) => {
  try {
    // Check cache first
    const cached = cache.get('data_full');
    if (cached) {
      return res.json(cached);
    }

    // Parallel queries for faster loading
    const [
      [teachers],
      [students],
      [subjects],
      [classes],
      [journals],
      [classJournals],
      [settingsRows]
    ] = await Promise.all([
      pool.query('SELECT id, name, nip FROM teachers ORDER BY name'),
      pool.query('SELECT id, name, nis, class_name, gender FROM students ORDER BY class_name, name'),
      pool.query('SELECT name FROM subjects ORDER BY name'),
      pool.query('SELECT * FROM classes ORDER BY grade, name'),
      pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM journal_entries ORDER BY created_at DESC"),
      pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM class_journal_entries ORDER BY created_at DESC"),
      pool.query('SELECT setting_key, setting_value FROM school_settings')
    ]);

    // Fetch attendance details efficiently only for the journals returned
    let teacherAttendanceMap = {};
    let classAttendanceMap = {};

    const journalIds = journals.map(j => j.journal_id);
    const classJournalIds = classJournals.map(j => j.journal_id);

    // Optimized attendance details fetching using IN clause instead of fetching everything
    const attendanceQueries = [];

    if (journalIds.length > 0) {
      attendanceQueries.push(
        pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['teacher', journalIds])
      );
    } else {
      attendanceQueries.push(Promise.resolve([[]]));
    }

    if (classJournalIds.length > 0) {
      attendanceQueries.push(
        pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['class', classJournalIds])
      );
    } else {
      attendanceQueries.push(Promise.resolve([[]]));
    }

    const [[teacherAttDetails], [classAttDetails]] = await Promise.all(attendanceQueries);

    // Group teacher attendance details
    teacherAttDetails.forEach(ad => {
      if (!teacherAttendanceMap[ad.journal_id]) teacherAttendanceMap[ad.journal_id] = [];
      teacherAttendanceMap[ad.journal_id].push({
        studentName: ad.student_name,
        status: ad.status
      });
    });

    // Group class attendance details
    classAttDetails.forEach(ad => {
      if (!classAttendanceMap[ad.journal_id]) classAttendanceMap[ad.journal_id] = [];
      classAttendanceMap[ad.journal_id].push({
        studentName: ad.student_name,
        status: ad.status
      });
    });

    const settings = {};
    settingsRows.forEach(row => {
      settings[row.setting_key] = row.setting_value;
    });

    const response = {
      status: 'success',
      data: {
        guru: teachers.map(t => ({ 'Nama Guru': t.name, 'NIP': t.nip || '' })),
        murid: students.map(s => ({ 'Nama Murid': s.name, 'NIS': s.nis || '', 'Kelas': s.class_name, 'L/P': s.gender || '' })),
        mapel: subjects.map(s => ({ 'Mata Pelajaran': s.name })),
        classes: classes,
        jurnal: journals.map(j => ({
          'ID Jurnal': j.journal_id,
          'Tanggal': j.formatted_date || j.date,
          'Timestamp': j.created_at,
          'Nama Guru': j.teacher_name,
          'NIP': j.nip || '',
          'Kelas': j.class_name,
          'Mata Pelajaran': j.subject,
          'Materi': j.topic || '',
          'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
          'Jumlah Hadir': j.students_present,
          'Jumlah Sakit': j.sakit,
          'Jumlah Izin': j.izin,
          'Jumlah Alpha': j.alpha,
          'Jumlah Dispen': j.dispen,
          'Keterangan': j.notes || '',
          'Foto': j.photo_url || '',
          'Detail Absensi': teacherAttendanceMap[j.journal_id] || []
        })),
        jurnalKelas: classJournals.map(j => ({
          'ID Jurnal Kelas': j.journal_id,
          'Tanggal': j.formatted_date || j.date,
          'Timestamp': j.created_at,
          'Nama Guru': j.teacher_name,
          'Mata Pelajaran': j.subject,
          'Kelas': j.class_name,
          'Kehadiran Guru': j.teacher_presence,
          'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
          'Jumlah Hadir': j.students_present,
          'Jumlah Sakit': j.sakit,
          'Jumlah Izin': j.izin,
          'Jumlah Alpha': j.alpha,
          'Jumlah Dispen': j.dispen,
          'Keterangan': j.notes || '',
          'Sekretaris Kelas': j.class_secretary || '',
          'Detail Absensi': classAttendanceMap[j.journal_id] || []
        })),
        settings: {
          'Semester': settings.semester || 'Ganjil',
          'TahunPelajaran': settings.academic_year || '2024/2025',
          'NamaKepalaSekolah': settings.headmaster_name || '',
          'NipKepalaSekolah': settings.headmaster_nip || '',
          'LogoUrl': settings.logo_url || '',
          'KopUrl': settings.kop_url || ''
        }
      }
    };

    // Cache the full data for 30 seconds to reduce DB load
    cache.set('data_full', response, 30);

    res.json(response);
  } catch (error) {
    console.error('Error fetching data:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// ==================== JOURNAL ENDPOINTS ====================

// POST save journal (guru)
app.post('/api/jurnal', async (req, res) => {
  try {
    const body = req.body;
    // Handle both direct data and nested payload format from frontend
    const data = body.payload || body;
    const journalId = data.idJurnal || `JRN-${Date.now()}`;

    // Parse jamKe if it's combined format "08:00 - 09:00"
    let startTime = data.startTime;
    let endTime = data.endTime;
    if (data.jamKe && data.jamKe.includes('-')) {
      const parts = data.jamKe.split('-').map(s => s.trim());
      startTime = parts[0] || '';
      endTime = parts[1] || '';
    }

    // Map field names from frontend format to database format
    const teacherName = data.teacherName || data.namaGuru || '';
    const nip = data.nip || '-';
    const className = data.className || data.kelas || '';
    const subject = data.subject || data.mapel || '';
    const topic = data.topic || data.materi || '';
    const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
    const sakit = data.sakit || data.jmlSakit || 0;
    const izin = data.izin || data.jmlIzin || 0;
    const alpha = data.alpha || data.jmlAlpha || 0;
    const dispen = data.dispen || data.jmlDispen || 0;
    const notes = data.notes || data.keterangan || '';
    const photoUrl = data.photoUrl || data.photoBase64 || null;
    const date = data.date || new Date().toISOString().split('T')[0];

    await pool.query(`
      INSERT INTO journal_entries 
      (journal_id, date, teacher_name, nip, class_name, subject, topic, start_time, end_time, 
       students_present, sakit, izin, alpha, dispen, notes, photo_url) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      journalId, date, teacherName, nip, className, subject,
      topic, startTime, endTime, studentsPresent,
      sakit, izin, alpha, dispen, notes, photoUrl
    ]);

    // Save attendance details if provided (wrapped in try-catch to not fail main journal save)
    const attendanceDetails = data.detailAbsensi || data.attendanceDetails || [];
    if (Array.isArray(attendanceDetails) && attendanceDetails.length > 0) {
      try {
        const attendanceValues = attendanceDetails.map(ad => [
          'teacher',
          journalId,
          ad.studentName || ad.nama || '',
          ad.status || 'H'
        ]);
        await pool.query(`
          INSERT INTO attendance_details (journal_type, journal_id, student_name, status)
          VALUES ?
        `, [attendanceValues]);
      } catch (attErr) {
        console.log('Could not save attendance details (table may not exist):', attErr.message);
      }
    }

    // Clear cache to ensure new data is visible immediately
    cache.clear('data');

    res.json({ status: 'success', message: 'Jurnal berhasil disimpan', journalId });
  } catch (error) {
    console.error('Error saving journal:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});


// POST save class journal (sekretaris)
app.post('/api/jurnal-kelas', async (req, res) => {
  try {
    const body = req.body;
    // Handle both direct data and nested payload format from frontend
    const data = body.payload || body;
    const journalId = data.idJurnal || `JKL-${Date.now()}`;

    // Parse jamKe if it's combined format "08:00 - 09:00"
    let startTime = data.startTime;
    let endTime = data.endTime;
    if (data.jamKe && data.jamKe.includes('-')) {
      const parts = data.jamKe.split('-').map(s => s.trim());
      startTime = parts[0] || '';
      endTime = parts[1] || '';
    }

    // Map field names from frontend format to database format
    const teacherName = data.teacherName || data.namaGuru || '';
    const subject = data.subject || data.mapel || '';
    const className = data.className || data.kelas || '';
    const teacherPresence = data.teacherPresence || data.kehadiranGuru || 'Hadir';
    const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
    const sakit = data.sakit || data.jmlSakit || 0;
    const izin = data.izin || data.jmlIzin || 0;
    const alpha = data.alpha || data.jmlAlpha || 0;
    const dispen = data.dispen || data.jmlDispen || 0;
    const notes = data.notes || data.keterangan || '';
    const classSecretary = data.classSecretary || data.sekretaris || '';
    const date = data.date || new Date().toISOString().split('T')[0];

    await pool.query(`
      INSERT INTO class_journal_entries 
      (journal_id, date, teacher_name, subject, class_name, teacher_presence, start_time, end_time,
       students_present, sakit, izin, alpha, dispen, notes, class_secretary) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      journalId, date, teacherName, subject, className,
      teacherPresence, startTime, endTime, studentsPresent,
      sakit, izin, alpha, dispen, notes, classSecretary
    ]);

    // Save attendance details if provided (wrapped in try-catch to not fail main journal save)
    const attendanceDetails = data.detailAbsensi || data.attendanceDetails || [];
    if (Array.isArray(attendanceDetails) && attendanceDetails.length > 0) {
      try {
        const attendanceValues = attendanceDetails.map(ad => [
          'class',
          journalId,
          ad.studentName || ad.nama || '',
          ad.status || 'H'
        ]);
        await pool.query(`
          INSERT INTO attendance_details (journal_type, journal_id, student_name, status)
          VALUES ?
        `, [attendanceValues]);
      } catch (attErr) {
        console.log('Could not save attendance details (table may not exist):', attErr.message);
      }
    }

    // Clear cache to ensure new data is visible immediately
    cache.clear('data');

    res.json({ status: 'success', message: 'Jurnal kelas berhasil disimpan', journalId });
  } catch (error) {
    console.error('Error saving class journal:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// DELETE journal
app.delete('/api/jurnal/:id', async (req, res) => {
  try {
    const { id } = req.params;
    // Delete attendance details first (ignore error if table doesn't exist)
    try {
      await pool.query('DELETE FROM attendance_details WHERE journal_type = ? AND journal_id = ?', ['teacher', id]);
    } catch (err) { /* ignore */ }
    await pool.query('DELETE FROM journal_entries WHERE journal_id = ? OR id = ?', [id, id]);
    res.json({ status: 'success', message: 'Jurnal berhasil dihapus' });
  } catch (error) {
    console.error('Error deleting journal:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// DELETE class journal
app.delete('/api/jurnal-kelas/:id', async (req, res) => {
  try {
    const { id } = req.params;
    // Delete attendance details first (ignore error if table doesn't exist)
    try {
      await pool.query('DELETE FROM attendance_details WHERE journal_type = ? AND journal_id = ?', ['class', id]);
    } catch (err) { /* ignore */ }
    await pool.query('DELETE FROM class_journal_entries WHERE journal_id = ? OR id = ?', [id, id]);
    res.json({ status: 'success', message: 'Jurnal kelas berhasil dihapus' });
  } catch (error) {
    console.error('Error deleting class journal:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// PUT update journal
app.put('/api/jurnal/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const body = req.body;
    // Handle both direct data and nested payload format from frontend
    const data = body.payload || body;

    // Parse jamKe if it's combined format "08:00 - 09:00"
    let startTime = data.startTime;
    let endTime = data.endTime;
    if (data.jamKe && data.jamKe.includes('-')) {
      const parts = data.jamKe.split('-').map(s => s.trim());
      startTime = parts[0] || '';
      endTime = parts[1] || '';
    }

    // Map field names from frontend format to database format
    const teacherName = data.teacherName || data.namaGuru || '';
    const nip = data.nip || '-';
    const className = data.className || data.kelas || '';
    const subject = data.subject || data.mapel || '';
    const topic = data.topic || data.materi || '';
    const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
    const sakit = data.sakit || data.jmlSakit || 0;
    const izin = data.izin || data.jmlIzin || 0;
    const alpha = data.alpha || data.jmlAlpha || 0;
    const dispen = data.dispen || data.jmlDispen || 0;
    const notes = data.notes || data.keterangan || '';
    const photoUrl = data.photoUrl || data.foto || '';
    const date = data.date || new Date().toISOString().split('T')[0];

    await pool.query(`
      UPDATE journal_entries SET 
        date = ?, teacher_name = ?, nip = ?, class_name = ?, subject = ?, topic = ?,
        start_time = ?, end_time = ?, students_present = ?, sakit = ?, izin = ?, 
        alpha = ?, dispen = ?, notes = ?, photo_url = ?
      WHERE journal_id = ? OR id = ?
    `, [
      date, teacherName, nip, className, subject, topic,
      startTime, endTime, studentsPresent, sakit, izin, alpha, dispen, notes, photoUrl,
      id, id
    ]);

    // Clear cache to reflect changes
    cache.clear('data');

    res.json({ status: 'success', message: 'Jurnal berhasil diupdate' });
  } catch (error) {
    console.error('Error updating journal:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});


// PUT update class journal
app.put('/api/jurnal-kelas/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const body = req.body;
    // Handle both direct data and nested payload format from frontend
    const data = body.payload || body;

    // Parse jamKe if it's combined format "1 - 2"
    let startTime = data.startTime;
    let endTime = data.endTime;
    if (data.jamKe && data.jamKe.includes('-')) {
      const parts = data.jamKe.split('-').map(s => s.trim());
      startTime = parts[0] || '';
      endTime = parts[1] || '';
    }

    // Map field names from frontend format to database format
    const teacherName = data.teacherName || data.namaGuru || '';
    const subject = data.subject || data.mapel || '';
    const className = data.className || data.kelas || '';
    const teacherPresence = data.teacherPresence || data.kehadiranGuru || 'Hadir';
    const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
    const sakit = data.sakit || data.jmlSakit || 0;
    const izin = data.izin || data.jmlIzin || 0;
    const alpha = data.alpha || data.jmlAlpha || 0;
    const dispen = data.dispen || data.jmlDispen || 0;
    const notes = data.notes || data.keterangan || '';
    const classSecretary = data.classSecretary || data.sekretaris || '';
    const date = data.date || new Date().toISOString().split('T')[0];

    await pool.query(`
      UPDATE class_journal_entries SET 
        date = ?, teacher_name = ?, subject = ?, class_name = ?, teacher_presence = ?,
        start_time = ?, end_time = ?, students_present = ?, sakit = ?, izin = ?, 
        alpha = ?, dispen = ?, notes = ?, class_secretary = ?
      WHERE journal_id = ? OR id = ?
    `, [
      date, teacherName, subject, className, teacherPresence,
      startTime, endTime, studentsPresent, sakit, izin, alpha, dispen, notes, classSecretary,
      id, id
    ]);

    // Clear cache to reflect changes
    cache.clear('data');

    res.json({ status: 'success', message: 'Jurnal kelas berhasil diupdate' });
  } catch (error) {
    console.error('Error updating class journal:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});


// ==================== SETTINGS ENDPOINTS ====================

app.get('/api/settings', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT setting_key, setting_value FROM school_settings');
    const settings = {};
    rows.forEach(row => {
      settings[row.setting_key] = row.setting_value;
    });
    res.json({ status: 'success', data: settings });
  } catch (error) {
    console.error('Error fetching settings:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

app.post('/api/settings', async (req, res) => {
  try {
    const body = req.body;
    // Handle both direct data and nested payload format from frontend
    const data = body.payload || body;

    // Map field names from frontend format (Indonesian) to database format
    const semester = data.semester || data.Semester;
    const academicYear = data.academicYear || data.TahunPelajaran;
    const headmasterName = data.headmasterName || data.NamaKepalaSekolah;
    const headmasterNip = data.headmasterNip || data.NipKepalaSekolah;
    const logoUrl = data.logoUrl || data.LogoUrl;
    const kopUrl = data.kopUrl || data.KopUrl;

    const settingsToSave = [
      ['semester', semester],
      ['academic_year', academicYear],
      ['headmaster_name', headmasterName],
      ['headmaster_nip', headmasterNip],
      ['logo_url', logoUrl],
      ['kop_url', kopUrl]
    ];

    for (const [key, value] of settingsToSave) {
      if (value !== undefined) {
        await pool.query(
          'INSERT INTO school_settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = ?',
          [key, value, value]
        );
      }
    }

    res.json({ status: 'success', message: 'Settings berhasil disimpan' });
  } catch (error) {
    console.error('Error saving settings:', error);
    res.status(500).json({ status: 'error', message: error.message });
  }
});

// ==================== END API ROUTES ====================

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

// SPA fallback - all other routes serve index.html
app.use((req, res) => {
  if (req.path.startsWith('/api')) {
    return res.status(404).json({ error: 'API endpoint not found' });
  }
  res.sendFile(path.join(__dirname, 'dist', 'index.html'));
});

// Start server
const startServer = async () => {
  const dbConnected = await initializeDatabase();

  if (!dbConnected) {
    console.warn('⚠️  Warning: Database connection failed.');
  }

  app.listen(PORT, () => {
    console.log(`
╔═══════════════════════════════════════════════════════════╗
║     🏫 Jurnal Kelas & Mengajar - Production Server        ║
╠═══════════════════════════════════════════════════════════╣
║  Server running on: http://localhost:${PORT}               ║
║  Database: ${dbConnected ? '✅ Connected' : '❌ Disconnected'}                           ║
║                                                           ║
║  API Endpoints (ALL INLINE - FULL CRUD):                  ║
║  - GET/POST/PUT/DELETE /api/students                      ║
║  - GET/POST/PUT/DELETE /api/teachers                      ║
║  - GET/POST/PUT/DELETE /api/subjects                      ║
║  - GET/POST/PUT/DELETE /api/jurnal                        ║
║  - GET/POST/PUT/DELETE /api/jurnal-kelas                  ║
║  - GET/POST /api/settings                                 ║
║  - GET  /api/data                                         ║
║  - GET  /api/health                                       ║
╚═══════════════════════════════════════════════════════════╝
        `);
  });
};

startServer();