const express = require('express');
const db = require('../config/database');
const router = express.Router();

const formatOptions = (rows, fallbackFields = []) => {
  return rows.map((row) => {
    const fallbackCandidates = [row.name, ...fallbackFields.map((field) => row[field]), row.id];
    const rawName = fallbackCandidates.find((value) => value !== undefined && value !== null && `${value}`.trim() !== '');
    const name = rawName !== undefined && rawName !== null ? String(rawName).trim() : String(row.id);

    return {
      id: Number(row.id),
      name
    };
  });
};

// Get all standards
router.get('/standards', async (req, res) => {
  try {
    const [rows] = await db.execute(
      'SELECT id, name FROM standards WHERE status = ? ORDER BY display_order',
      ['active']
    );
    res.json(formatOptions(rows));
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Get all divisions
router.get('/divisions', async (req, res) => {
  try {
    const [rows] = await db.execute(
      'SELECT id, name FROM divisions WHERE status = ? ORDER BY display_order',
      ['active']
    );
    res.json(formatOptions(rows));
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Get roll numbers from database
router.get('/roll-numbers', async (req, res) => {
  try {
    const [rows] = await db.execute(
      'SELECT id, number as name FROM roll_numbers WHERE status = ? ORDER BY display_order',
      ['active']
    );
    res.json(formatOptions(rows));
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

module.exports = router;