// server.js
const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const session = require('express-session');
const multer = require('multer');
const xlsx = require('xlsx');
const fs = require('fs');
const PDFDocument = require('pdfkit');

const app = express();
const PORT = 3010;

app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));
app.use(session({ secret: 'mattressSecret', resave: false, saveUninitialized: true }));
const upload = multer({ dest: 'uploads/' });

function isAuthenticated(req, res, next) {
  if (req.session?.user) return next();
  res.redirect('/login.html');
}

const db = new sqlite3.Database('./mattress.db');


db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE,
    password TEXT,
    role TEXT
  )`);
  

 db.get(`SELECT * FROM users WHERE username = 'admin'`, (err, row) => {
    if (!row) {
      db.run(`INSERT INTO users (username, password, role) VALUES ('admin', 'admin123', 'admin')`);
    }
  });

db.run(`CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    serial TEXT,
    title TEXT,
    surname TEXT,
    middle_name TEXT,
    first_name TEXT,
    phone TEXT,
    email TEXT,
    deanery TEXT,
    parish TEXT,
    mattress TEXT,
    block TEXT,
    room TEXT,
    mattress_code TEXT,
    customer_code TEXT
  )`);
  
  db.run(`CREATE TABLE IF NOT EXISTS deaneries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    location TEXT,
    code TEXT UNIQUE
  )`);
  


// Place table creation code here:
db.run(`
  CREATE TABLE IF NOT EXISTS member_code (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_code TEXT UNIQUE,
    customer_id INTEGER,
    deanery_code TEXT,
    parish_code TEXT,
    serial TEXT,
    mattress_r TEXT,
    created_at TEXT DEFAULT (datetime('now'))
  )
`);


  // Dynamically add new columns to 'customers' table if they don't exist
const newCustomerColumns = [
  { name: 'customer_id', type: 'TEXT' },
  { name: 'mattress_r', type: 'TEXT' },
  { name: 'on_time_late', type: 'TEXT' },
  { name: 'hotel_r', type: 'TEXT' },
  { name: 'cmo_status', type: 'TEXT' },
  { name: 'official', type: 'TEXT' },
  { name: 'above_65yrs', type: 'TEXT' }
];

db.all("PRAGMA table_info(customers);", (err, columns) => {
  if (err) {
    console.error("❌ Failed to fetch columns:", err.message);
    return;
  }

  const existingColumns = columns.map(col => col.name);
  newCustomerColumns.forEach(col => {
    if (!existingColumns.includes(col.name)) {
      db.run(`ALTER TABLE customers ADD COLUMN ${col.name} ${col.type}`, (alterErr) => {
        if (alterErr) {
          console.error(`❌ Failed to add column ${col.name}:`, alterErr.message);
        } else {
          console.log(`✅ Column '${col.name}' added to 'customers' table.`);
        }
      });
    }
  });
});

  
 
  db.run(`CREATE TABLE IF NOT EXISTS parishes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT,
    deanery_code TEXT,
    parish_name TEXT,
    parish_code TEXT
  )`);

 db.run(`CREATE TABLE IF NOT EXISTS suppliers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    supplier_name TEXT,
    contact TEXT,
    location TEXT,
    date_received TEXT,
    date_returned TEXT,
    qty_collected INTEGER,
    mattress_code TEXT,
    assignment_location TEXT
  )`);
  

db.run(`CREATE TABLE IF NOT EXISTS mattress_locations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  building TEXT,
  floor TEXT,
  room TEXT,
  mattress_id INTEGER,
  allocation_date TEXT,
  remarks TEXT,
  FOREIGN KEY (mattress_id) REFERENCES mattresses(id)
)`);


 db.run(`CREATE TABLE IF NOT EXISTS mattresses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    mattress_code TEXT,
    assigned INTEGER DEFAULT 0
  )`);
  
  
  db.run(`CREATE TABLE IF NOT EXISTS customer_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    parish_code TEXT,
    surname TEXT,
    given_name TEXT,
    customer_code_1 TEXT,
    customer_code_2 TEXT
  )`);
  
 });
 
 db.run(`CREATE TABLE IF NOT EXISTS rooms (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  hall_name TEXT,
  code TEXT UNIQUE,
  bed_spaces INTEGER
)`);


  
// Authentication
app.post('/login', (req, res) => {
  const { username, password } = req.body;
  db.get('SELECT * FROM users WHERE username = ? AND password = ?', [username, password], (err, row) => {
    if (row) {
      req.session.user = row;
      res.json({ success: true });
    } else {
      res.json({ success: false });
    }
  });
});


// 🔹 POST a new room
app.post('/api/rooms', isAuthenticated, (req, res) => {
  const { hall_name, code, bed_spaces } = req.body;
  db.run(
    'INSERT INTO rooms (hall_name, code, bed_spaces) VALUES (?, ?, ?)',
    [hall_name, code, bed_spaces],
    function (err) {
      if (err) return res.status(500).json({ error: 'Room code must be unique or something went wrong.' });
      res.json({ success: true, id: this.lastID });
    }
  );
});

// Create new Member Code
app.post('/api/member-code', isAuthenticated, (req, res) => {
  const { member_code, mattress_code } = req.body;
  db.run(`INSERT INTO member_code (member_code, mattress_code) VALUES (?, ?)`, 
    [member_code, mattress_code], function (err) {
      if (err) return res.status(500).json({ success: false, error: err.message });
      res.json({ success: true, id: this.lastID });
  });
});

// List all Member Codes
// List all member codes with member names
app.get('/api/member-codes', isAuthenticated, (req, res) => {
  db.all(
    `SELECT mc.*, c.first_name, c.middle_name, c.surname
     FROM member_code mc
     LEFT JOIN customers c ON mc.customer_id = c.id
     ORDER BY mc.id DESC`,
    (err, rows) => {
      if (err) return res.status(500).json([]);
      res.json(rows);
    }
  );
});

// Ensure your mattresses table has: id, code, room_code, mattress_number, supplier_code, supplier_serial, assigned INTEGER DEFAULT 0

app.post('/api/generate-mattress-codes', isAuthenticated, (req, res) => {
  const { room_code, supplier_code } = req.body;
  if (!room_code || !supplier_code) return res.json({ success: false, error: "Room and Supplier required." });

  db.get(`SELECT * FROM rooms WHERE code = ?`, [room_code], (err, room) => {
    if (err || !room) return res.json({ success: false, error: "Room not found." });

    db.get(`SELECT * FROM suppliers WHERE mattress_code = ?`, [supplier_code], (err2, supplier) => {
      if (err2 || !supplier) return res.json({ success: false, error: "Supplier not found." });

      const bedSpaces = Number(room.bed_spaces);
      const totalAvailable = Number(supplier.qty_collected);

      // Count assigned mattresses for this room
      db.all(`SELECT * FROM mattresses WHERE room_code = ?`, [room_code], (err3, assignedRows) => {
        const assignedCount = assignedRows ? assignedRows.length : 0;
        const remainingBeds = bedSpaces - assignedCount;
        if (remainingBeds <= 0) {
          return res.json({ success: false, error: `Room ${room_code} is already full.` });
        }

        // Find all assigned for this supplier (across all rooms)
        db.all(`SELECT * FROM mattresses WHERE supplier_code = ?`, [supplier_code], (err4, supplierRows) => {
          // Max supplier_serial already used for this supplier
          let maxSupplierSerial = 0;
          if (supplierRows && supplierRows.length > 0) {
            maxSupplierSerial = Math.max(...supplierRows.map(r => Number(r.supplier_serial) || 0));
          }
          // For room: max mattress_number already assigned
          let maxMattressNumber = 0;
          if (assignedRows && assignedRows.length > 0) {
            maxMattressNumber = Math.max(...assignedRows.map(r => Number(r.mattress_number) || 0));
          }
          // Calculate how many this supplier can still assign
          const supplierRemaining = totalAvailable - maxSupplierSerial;
          const assignCount = Math.min(remainingBeds, supplierRemaining);
          if (assignCount <= 0) {
            return res.json({ success: false, error: `No more beds available for supplier ${supplier_code}.` });
          }

          let insertMany = db.prepare(`INSERT INTO mattresses (code, room_code, mattress_number, supplier_code, supplier_serial, assigned) VALUES (?, ?, ?, ?, ?, ?)`);
          let codes = [];
          for (let i = 1; i <= assignCount; i++) {
            let mattressSerial = maxSupplierSerial + i;
            let mattressSerialStr = String(mattressSerial).padStart(4, '0');
            let mattressNumber = maxMattressNumber + i;
            let mattressCode = `${room_code} ${String(mattressNumber).padStart(3, '0')} ${supplier_code} ${mattressSerialStr}`;
            insertMany.run(mattressCode, room_code, mattressNumber, supplier_code, mattressSerialStr, 0);
            codes.push({
              code: mattressCode,
              room: room_code,
              mattress_number: mattressNumber,
              supplier: supplier_code,
              supplier_serial: mattressSerialStr,
              assigned: false
            });
          }
          insertMany.finalize(() => {
            res.json({ success: true, codes });
          });
        });
      });
    });
  });
});


// Get mattress codes for room/supplier (with optional search filter)
app.get('/api/mattress-codes', isAuthenticated, (req, res) => {
  const { room, supplier, search } = req.query;
  let q = `SELECT * FROM mattresses WHERE 1=1`;
  let params = [];
  if (room) { q += ` AND room_code = ?`; params.push(room); }
  if (supplier) { q += ` AND supplier_code = ?`; params.push(supplier); }
  if (search) { q += ` AND code LIKE ?`; params.push('%'+search+'%'); }
  db.all(q, params, (err, rows) => {
    if (err) return res.json([]);
    res.json(rows);
  });
});

// Delete mattress code
app.post('/api/delete-mattress-code', isAuthenticated, (req, res) => {
  const { id } = req.body;
  if (!id) return res.json({ success: false, error: "ID required" });
  db.run(`DELETE FROM mattresses WHERE id = ?`, [id], function(err) {
    if (err) return res.json({ success: false, error: err.message });
    res.json({ success: true });
  });
});

// Update/modify mattress code (for example, change assigned status or code)
app.post('/api/update-mattress-code', isAuthenticated, (req, res) => {
  const { id, code, assigned } = req.body;
  if (!id) return res.json({ success: false, error: "ID required" });
  db.run(`UPDATE mattresses SET code = ?, assigned = ? WHERE id = ?`, [code, assigned, id], function(err) {
    if (err) return res.json({ success: false, error: err.message });
    res.json({ success: true });
  });
});


// Delete selected member codes
app.post('/api/member-codes/delete', isAuthenticated, (req, res) => {
  const { ids } = req.body; // expects array of IDs
  if (!Array.isArray(ids) || !ids.length) return res.json({ success: false, error: "No IDs provided" });
  const placeholders = ids.map(() => '?').join(',');
  db.run(`DELETE FROM member_code WHERE id IN (${placeholders})`, ids, function(err) {
    if (err) return res.status(500).json({ success: false, error: err.message });
    res.json({ success: true, deleted: this.changes });
  });
});


// Delete Member Code
app.delete('/api/member-code/:id', isAuthenticated, (req, res) => {
  db.run('DELETE FROM member_code WHERE id = ?', [req.params.id], function(err) {
    if (err) return res.status(500).json({ success: false });
    res.json({ success: true });
  });
});




app.post('/api/mattress-locations', isAuthenticated, (req, res) => {
  const { building, floor, room, mattress_id, remarks } = req.body;
  const allocation_date = new Date().toISOString().slice(0, 10);

  db.run(`INSERT INTO mattress_locations (building, floor, room, mattress_id, allocation_date, remarks)
          VALUES (?, ?, ?, ?, ?, ?)`,
    [building, floor, room, mattress_id, allocation_date, remarks],
    function (err) {
      if (err) return res.status(500).json({ success: false });
      res.json({ success: true, id: this.lastID });
    });
});


// --- Excel export (optional, needs xlsx module) ---
app.get('/api/export-mattress-codes', isAuthenticated, (req, res) => {
  const { room, supplier } = req.query;
  let q = `SELECT * FROM mattresses WHERE 1=1`;
  let params = [];
  if (room) { q += ` AND room_code = ?`; params.push(room); }
  if (supplier) { q += ` AND supplier_code = ?`; params.push(supplier); }
  db.all(q, params, (err, rows) => {
    if (err || !rows) return res.status(500).send('Failed');
    const xlsx = require('xlsx');
    const ws = xlsx.utils.json_to_sheet(rows);
    const wb = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(wb, ws, "Mattresses");
    const buf = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });
    res.setHeader('Content-Disposition', 'attachment; filename="mattresses.xlsx"');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buf);
  });
});


app.post('/api/customers', isAuthenticated, (req, res) => {
  const { serial, title, surname, middle_name, first_name, deanery, parish, mattress, phone, email } = req.body;
  db.run(`INSERT INTO customers (serial, title, surname, middle_name, first_name, phone, email, deanery, parish, mattress,
  customer_id, mattress_r, on_time_late, hotel_r, cmo_status, official, above_65yrs)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [serial, title, surname, middle_name, first_name, deanery, parish, mattress, phone, email], function (err) {
      res.json({ success: !err, id: this.lastID });
    });
});


app.post('/api/print-label', (req, res) => {
  const { label } = req.body;

  if (!label || typeof label !== 'string') {
    return res.status(400).json({ success: false, error: 'Label text is required.' });
  }

  const escaped = label.replace(/"/g, '\\"');  // escape quotes
  const command = `python3 print_label.py "${escaped}"`;

  exec(command, (error, stdout, stderr) => {
    if (error) {
      console.error('❌ Print Error:', stderr);
      return res.status(500).json({ success: false, error: stderr });
    }
    console.log('✅ Print Success:', stdout);
    res.json({ success: true, output: stdout });
  });
});


app.get('/logout', (req, res) => {
  req.session.destroy();
  res.redirect('/login.html');
});

// 🔹 GET all rooms
app.get('/api/rooms', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM rooms ORDER BY id ASC', (err, rows) => {
    if (err) return res.status(500).json({ error: 'Failed to fetch rooms' });
    res.json(rows);
  });
});

app.get('/api/room-availability', (req, res) => {
  const sql = `
    SELECT 
      r.code AS block_code,
      r.bed_spaces,
      r.bed_spaces - COUNT(c.id) AS remaining_beds
    FROM rooms r
    LEFT JOIN customer_codes c ON r.code = c.block
    GROUP BY r.code
  `;

  db.all(sql, [], (err, rows) => {
    if (err) {
      console.error("Error fetching room availability:", err.message);
      return res.status(500).json({ error: "Internal server error" });
    }
    res.json(rows);
  });
});


// API to get current count of customers for a specific parish


app.get('/api/customer-count/:parishCode', (req, res) => {
  const { parishCode } = req.params;

  db.get(
    'SELECT COUNT(*) AS count FROM customer_codes WHERE parish_id = ?',
    [parishCode],
    (err, row) => {
      if (err) {
        console.error('Error counting customer codes:', err);
        res.status(500).json({ error: 'Database error' });
      } else {
        res.json({ count: row.count });
      }
    }
  );
});

app.get('/api/filter-codes', isAuthenticated, (req, res) => {
  const { deanery, parish } = req.query;
  console.log('📡 Incoming filter params:', { deanery, parish });

  let query = `
    SELECT code1 AS code, mattress_id, first_name, surname
    FROM customer_codes
    WHERE 1 = 1
  `;
  const params = [];

  if (deanery && deanery.trim()) {
    query += ` AND deanery = ?`;
    params.push(deanery.trim());
  }
  if (parish && parish.trim()) {
    query += ` AND parish_code = ?`;
    params.push(parish.trim());
  }

  console.log('🧾 Executing SQL Query:', query, params);

  db.all(query, params, (err, rows) => {
    if (err) {
      console.error('❌ Filter DB error:', err.message);
      return res.status(500).json({ error: err.message });
    }

    const formattedRows = rows.map(row => ({
      member: `${row.first_name || ''} ${row.surname || ''}`.trim() || '-',
      code: row.code || 'N/A',
      mattress: row.mattress_id || 'N/A'
    }));

    console.log('✅ Query returned rows:', formattedRows.length);
    res.json(formattedRows);
  });
});




app.get('/api/check-auth', (req, res) => {
  res.json(req.session.user ? { authenticated: true, role: req.session.user.role } : { authenticated: false });
});

app.get('/api/mattress-locations', isAuthenticated, (req, res) => {
  db.all(`SELECT ml.*, m.tracking_code 
          FROM mattress_locations ml
          LEFT JOIN mattresses m ON ml.mattress_id = m.id
          ORDER BY ml.id DESC`, (err, rows) => {
    if (err) return res.status(500).json({ success: false });
    res.json(rows);
  });
});

app.get("/api/members/:deanery/:parish", (req, res) => {
  const { deanery, parish } = req.params;
  const query = `SELECT id, title, surname, middle_name, first_name 
                 FROM customers 
                 WHERE deanery = ? AND parish = ?`;

  db.all(query, [deanery, parish], (err, rows) => {
    if (err) {
      console.error("Error fetching members:", err);
      res.status(500).json({ error: "Internal server error" });
    } else {
      res.json(rows);
    }
  });
});


app.get("/api/mattresses", (req, res) => {
  db.all("SELECT mattress_code, COUNT(*) as quantity FROM mattresses WHERE assigned = 0 GROUP BY mattress_code", (err, rows) => {
    if (err) return res.status(500).json({ success: false, error: err.message });
    res.json(rows);
  });
});



// ✅ GET members by deanery and parish (for dropdown)
app.get('/api/members/:deaneryCode/:parishCode', isAuthenticated, (req, res) => {
  const { deaneryCode, parishCode } = req.params;
});


app.get('/api/customers', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM customers ORDER BY id ASC', (err, rows) => {
    if (err) return res.status(500).json({ success: false });
    res.json(rows);
  });
});


// Deanery APIs
app.get('/api/deanery-code', isAuthenticated, (req, res) => {
  db.get(`SELECT MAX(CAST(code AS INTEGER)) AS maxCode FROM deaneries`, (err, row) => {
    if (err) return res.status(500).json({ error: 'Failed to generate deanery code' });
    const nextCode = row?.maxCode ? String(parseInt(row.maxCode) + 1).padStart(2, '0') : '01';
    res.json({ code: nextCode });
  });
});

app.get('/api/deanery/max-code', isAuthenticated, (req, res) => {
  db.get("SELECT MAX(CAST(code AS INTEGER)) as maxCode FROM deaneries", (err, row) => {
    if (err) {
      console.error("DB Error:", err.message);
      return res.status(500).json({ error: err.message });
    }
    console.log("Fetched maxCode:", row?.maxCode);
    res.json(row || { maxCode: 0 });
  });
});

app.get('/api/deanery-list', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM deaneries ORDER BY id ASC', (err, rows) => {
    if (err) return res.status(500).json({ error: 'Failed to fetch deanery list' });
    res.json(rows);
  });
});


app.get('/api/deanery', isAuthenticated, (req, res) => {
  console.log("Deanery list API called");
  db.all("SELECT * FROM deaneries", (err, rows) => {
    if (err) {
      console.error("Error fetching deaneries:", err.message);
      return res.status(500).json({ success: false, error: err.message });
    }
    console.log("Deaneries loaded:", rows.length);
    res.json(rows);
  });
});

// Route to get assigned and unassigned member counts
app.get('/api/member-summary', isAuthenticated, (req, res) => {
  const { deaneryName, parishName } = req.query;

  // If no deaneryName or parishName, return global summary (as per your dashboard.html logic)
  if (!deaneryName || deaneryName === 'undefined' || !parishName || parishName === 'undefined') {
    db.get(`
      SELECT
        (SELECT COUNT(*) FROM customer_codes) AS assigned,
        (SELECT COUNT(*) FROM customers WHERE NOT EXISTS (
            SELECT 1 FROM customer_codes cc
            WHERE cc.surname = customers.surname AND cc.first_name = customers.first_name
        )) AS unassigned
    `, [], (err, row) => {
      if (err) {
        console.error('Error fetching general member summary:', err.message);
        return res.status(500).json({ error: 'Failed to fetch general member summary.', details: err.message });
      }
      return res.json(row);
    });
    return;
  }

  // Sanitize inputs to prevent SQL injection or unexpected behavior with special characters
  const safeDeaneryName = decodeURIComponent(deaneryName);
  const safeParishName = decodeURIComponent(parishName);

  // Queries for specific deanery/parish
  const assignedQuery = `
    SELECT COUNT(*) AS assigned
    FROM customer_codes cc
    JOIN customers c ON cc.surname = c.surname AND cc.first_name = c.first_name
    WHERE c.deanery = ? AND c.parish = ?
  `;

  const unassignedQuery = `
    SELECT COUNT(*) AS unassigned
    FROM customers c
    WHERE c.deanery = ? AND c.parish = ?
      AND NOT EXISTS (
          SELECT 1 FROM customer_codes cc
          WHERE cc.surname = c.surname AND cc.first_name = c.first_name
      )
  `;

  db.get(assignedQuery, [safeDeaneryName, safeParishName], (err, assignedRow) => {
    if (err) {
      console.error("Error fetching assigned count:", err.message);
      return res.status(500).json({ error: err.message });
    }

    db.get(unassignedQuery, [safeDeaneryName, safeParishName], (err, unassignedRow) => {
      if (err) {
        console.error("Error fetching unassigned count:", err.message);
        return res.status(500).json({ error: err.message });
      }

      res.json({
        assigned: assignedRow.assigned,
        unassigned: unassignedRow.unassigned
      });
    });
  });
});


app.post('/api/member-codes/bulk-generate', isAuthenticated, (req, res) => {
  // Get all from frontend
  const { deanery_name, parish_name, deanery_code, parish_code } = req.body;
  if (!deanery_name || !parish_name || !deanery_code || !parish_code)
    return res.status(400).json({ success: false, error: 'Deanery and Parish code and name required' });

  // Use NAME to fetch members (from customers)
  db.all(`
    SELECT c.id, c.mattress_r FROM customers c
    WHERE c.deanery = ?
    AND c.parish = ?
    AND c.id NOT IN (SELECT customer_id FROM member_code)
    ORDER BY c.id ASC
  `, [deanery_name, parish_name], (err, members) => {
    if (err) return res.status(500).json({ success: false, error: 'DB error' });

    if (!members.length) return res.json({ success: true, count: 0, message: 'No members left to generate codes for.' });

    db.get(
      'SELECT COUNT(*) AS cnt FROM member_code WHERE deanery_code = ? AND parish_code = ?',
      [deanery_code, parish_code],
      (err2, row) => {
        if (err2) return res.status(500).json({ success: false, error: 'DB error' });
        let serial = row?.cnt || 0;
        const stmt = db.prepare(`
          INSERT INTO member_code (member_code, customer_id, deanery_code, parish_code, serial, mattress_r, created_at)
          VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
        `);

        members.forEach(m => {
          serial += 1;
          const serialStr = String(serial).padStart(3, '0');
          const yn = (m.mattress_r || '').toUpperCase().startsWith('Y') ? 'Y' : 'N';
          // -- USE CODE for member_code --
          const code = `${deanery_code} ${parish_code} ${serialStr} ${yn}`;
          stmt.run(code, m.id, deanery_code, parish_code, serialStr, yn);
        });

        stmt.finalize();
        res.json({ success: true, count: members.length });
      }
    );
  });
});

app.get('/api/member-codes/:deaneryCode/:parishCode', (req, res) => {
  const { deaneryCode, parishCode } = req.params;
  // Fetch the names for given codes
  db.get('SELECT name FROM deaneries WHERE code = ?', [deaneryCode], (err, dRow) => {
    if (err || !dRow) return res.json([]);
    db.get('SELECT parish_name FROM parishes WHERE parish_code = ?', [parishCode], (err, pRow) => {
      if (err || !pRow) return res.json([]);
      // Now filter customers by name
      db.all(`
        SELECT * FROM customers 
        WHERE deanery = ? AND parish = ? 
        AND id NOT IN (SELECT customer_id FROM allocations)
      `, [dRow.name, pRow.parish_name], (err, members) => {
        if (err) return res.json([]);
        // Return any unallocated members
        res.json(members);
      });
    });
  });
});


// New API for Members Code Allocation page
app.get('/api/member-codes-by-location/:deaneryCode/:parishCode', (req, res) => {
  const { deaneryCode, parishCode } = req.params;
  db.all(
    `SELECT member_code FROM member_code WHERE deanery_code = ? AND parish_code = ?`,
    [deaneryCode, parishCode],
    (err, rows) => {
      if (err) {
        console.error(err);
        return res.json([]);
      }
      res.json(rows); // [{member_code: "..."}]
    }
  );
});


app.post('/api/allocate', (req, res) => {
  const { deanery, parish, member, mattress } = req.body;
  if (!deanery || !parish || !member || !mattress) {
    return res.json({ success: false, error: "Missing required fields" });
  }
  db.run(
    `INSERT INTO allocations (deanery, parish, member, mattress, allocated_at, allocated_code)
     VALUES (?, ?, ?, ?, datetime('now'), ?)`,
    [deanery, parish, member, mattress, member + ' ' + mattress],  // <--- here!
    function (err) {
      if (err) {
        console.error(err);
        return res.json({ success: false, error: "DB error" });
      }
      res.json({ success: true, id: this.lastID });
    }
  );
});


app.get('/api/allocations', (req, res) => {
  db.all(
    `SELECT 
        a.id,
        a.deanery,
        a.parish,
        a.member,
        a.mattress,
        a.allocated_code,
        c.surname,
        c.first_name,
        c.middle_name
     FROM allocations a
     LEFT JOIN member_code mc ON a.member = mc.member_code
     LEFT JOIN customers c ON mc.customer_id = c.id
     ORDER BY a.id ASC`,
    [],
    (err, rows) => {
      if (err) {
        console.error(err);
        return res.json([]);
      }
      res.json(rows);
    }
  );
});


app.delete('/api/allocations/:id', (req, res) => {
  db.run(`DELETE FROM allocations WHERE id = ?`, [req.params.id], function (err) {
    if (err) {
      console.error(err);
      return res.json({ success: false, error: "DB error" });
    }
    res.json({ success: true });
  });
});


app.post('/api/deanery', isAuthenticated, (req, res) => {
  const { name, location, code } = req.body;
  if (!name || !location || !code) return res.status(400).json({ success: false });
  db.run('INSERT INTO deaneries (name, location, code) VALUES (?, ?, ?)', [name, location, code], function (err) {
    if (err) return res.status(500).json({ success: false, message: err.message });
    res.json({ success: true, id: this.lastID });
  });
});


app.post('/api/save-customer-code', (req, res) => {
  const { deanery, parish_id, surname, first_name, code1, code2, mattress_id, block } = req.body;
  const created_at = new Date().toISOString();

  const sql = `
    INSERT INTO customer_codes (deanery, parish_id, surname, first_name, code1, code2, mattress_id, block, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  const params = [deanery, parish_id, surname, first_name, code1, code2, mattress_id, block, created_at];

  db.run(sql, params, function (err) {
    if (err) {
      console.error('Insert error:', err.message);
      res.status(500).json({ error: err.message });
    } else {
      res.json({ success: true, id: this.lastID });
    }
  });
});


// API to generate customer code based on room capacity and mattress supplier

app.post('/api/generate-customer-code', (req, res) => {
  const { deanery_id, parish_id, parish_code, deanery_code, surname, first_name, block, mattress_id } = req.body;

  // STEP 1: Check if customer already exists
  db.get(`SELECT * FROM customer_codes WHERE parish_id = ? AND surname = ? AND first_name = ?`,
    [parish_id, surname, first_name], (err, existing) => {
      if (err) return res.status(500).json({ error: err.message });
      if (existing) return res.status(400).json({ error: 'Customer code already exists for this member.' });

      // STEP 2: Count how many customers are already in this block
      db.get(`SELECT COUNT(*) as count FROM customer_codes WHERE block = ?`, [block], (err, blockCountResult) => {
        if (err) return res.status(500).json({ error: err.message });

        // STEP 3: Fetch the room's max bed space from 'rooms' table
        db.get(`SELECT bed_spaces FROM rooms WHERE code = ?`, [block], (err, roomData) => {
          if (err) return res.status(500).json({ error: err.message });
          if (!roomData) return res.status(400).json({ error: 'Room not found.' });

          const maxBeds = roomData.bed_spaces;
          const blockCount = blockCountResult.count;

          if (blockCount >= maxBeds) {
            return res.status(400).json({ error: `Block ${block} has reached its maximum bed capacity of ${maxBeds}.` });
          }

          const blockSerial = (blockCount + 1).toString().padStart(3, '0'); // A1 001, A1 002...

          // STEP 4: Generate supplier-prefixed mattress ID
          const supplierPrefix = mattress_id.charAt(0);
          db.get(`SELECT COUNT(*) as count FROM customer_codes WHERE mattress_id LIKE ?`, [`${supplierPrefix}%`], (err, supplierCount) => {
            if (err) return res.status(500).json({ error: err.message });

            const mattressSerial = (supplierCount.count + 1).toString().padStart(4, '0'); // C0001
            const fullMattressId = `${supplierPrefix} ${mattressSerial}`; 

            // STEP 5: Get sequential customer serial for this parish
            db.get(`SELECT COUNT(*) as count FROM customer_codes WHERE parish_id = ?`, [parish_id], (err, parishCount) => {
              if (err) return res.status(500).json({ error: err.message });

              const customerSerial = (parishCount.count + 1).toString().padStart(3, '0'); // 0001, 0002, etc.

              // STEP 6: Format final code
              const fullCode = `${deanery_code} ${parish_code} ${customerSerial} ${block} ${blockSerial} ${fullMattressId}`;

              // STEP 7: Save to DB
		      db.run(
                `INSERT INTO customer_codes (parish_id, surname, first_name, code1, code2, deanery, block, mattress_id)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
                [parish_id, surname, first_name, fullCode, fullCode, deanery_code, block, fullMattressId],
                function (err) {
                  if (err) return res.status(500).json({ error: err.message });

                  db.run(`UPDATE suppliers 
            SET available_qty = COALESCE(available_qty, qty_collected) - 1 
            WHERE mattress_code = ?`, 
      [supplierPrefix], 
      (err) => {
        if (err) return res.status(500).json({ error: err.message });

        res.json({ code: fullCode });
      });
  }
);
            });
          });
        });
      });
    });
});

// Endpoint to add a new supplier

app.post('/api/add-supplier', (req, res) => {
  const {
    supplier_name,
    contact,
    location,
    date_received,
    date_returned,
    qty_collected,
    mattress_code,
    assignment_location
  } = req.body;

  const available_qty = qty_collected;

  const stmt = db.prepare(`
    INSERT INTO suppliers (
      supplier_name,
      contact,
      location,
      date_received,
      date_returned,
      qty_collected,
      mattress_code,
      assignment_location,
      available_qty
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  stmt.run(
    supplier_name,
    contact,
    location,
    date_received,
    date_returned,
    qty_collected,
    mattress_code,
    assignment_location,
    available_qty,
    function (err) {
      if (err) return res.status(500).json({ error: err.message });
      res.json({ message: 'Supplier added successfully', supplier_id: this.lastID });
    }
  );
});

app.post('/api/auto-generate-all', isAuthenticated, (req, res) => {
  const { deaneryCode, parishCode, parishName, deaneryName, block, roomCode } = req.body;

  db.get(`SELECT bed_spaces FROM rooms WHERE code = ?`, [roomCode], (err1, room) => {
    if (err1 || !room) return res.status(400).json({ error: 'Invalid room code' });

    const bedSpaces = room.bed_spaces;

    // New: Count already assigned customers for this block
    db.get(`SELECT COUNT(*) as count FROM customer_codes WHERE block = ?`, [block], (err, blockCountResult) => {
      if (err) return res.status(500).json({ error: err.message });

      const assignedCount = blockCountResult.count || 0;
      const availableSlots = bedSpaces - assignedCount;

      if (availableSlots <= 0) {
        return res.status(400).json({ error: 'Block is already full. No available beds.' });
      }

      // Fetch ONLY up to the availableSlots number of members
      const getMembersQuery = `
        SELECT * FROM customers 
        WHERE deanery = ? AND parish = ?
        AND id NOT IN (SELECT customer_id FROM customer_codes)
        LIMIT ?
      `;
      db.all(getMembersQuery, [deaneryName, parishName, availableSlots], (err2, members) => {
        if (err2 || members.length === 0) return res.status(400).json({ error: 'No members to assign' });

        const mattressNeeded = members.filter(m => m.mattress_r === 'Yes');
        const mattressQuery = `SELECT mattress_code FROM mattresses WHERE assigned = 0`;

        db.all(mattressQuery, [], (err3, mattressRows) => {
          if (err3 || mattressRows.length < mattressNeeded.length) {
            return res.status(400).json({ error: 'Not enough available mattresses' });
          }

          db.all(`SELECT mattress_code FROM customer_codes`, [], (err4, assignedRows) => {
            const assignedCounts = {};
            if (!err4 && assignedRows) {
              assignedRows.forEach(r => {
                const prefix = r.mattress_code?.charAt(0);
                if (prefix) assignedCounts[prefix] = (assignedCounts[prefix] || 0) + 1;
              });
            }

            const mattressPool = {};
            mattressRows.forEach(row => {
              const prefix = row.mattress_code;
              mattressPool[prefix] = (mattressPool[prefix] || 0) + 1;
            });

            const sortedPrefixes = Object.keys(mattressPool).sort();
            let mattressCodesGenerated = [];

            for (let m = 0; m < mattressNeeded.length; m++) {
              let found = false;
              for (let prefix of sortedPrefixes) {
                const used = assignedCounts[prefix] || 0;
                const available = mattressPool[prefix];
                if (used < available) {
                  assignedCounts[prefix] = used + 1;
                  const padded = assignedCounts[prefix].toString().padStart(4, '0');
                  mattressCodesGenerated.push(`${prefix}${padded}`);
                  found = true;
                  break;
                }
              }
              if (!found) return res.status(400).json({ error: 'No mattress stock available' });
            }

            const getLastCodeQuery = `
              SELECT customer_code FROM customer_codes 
              WHERE parish_code = ?
              ORDER BY id DESC LIMIT 1
            `;
            db.get(getLastCodeQuery, [parishCode], (err5, lastRow) => {
              let serial = 0;
              if (lastRow?.customer_code) {
                const parts = lastRow.customer_code.match(/\d{4}$/);
                if (parts && parts[0]) serial = parseInt(parts[0]);
              }

              const insertStmt = db.prepare(`
                INSERT INTO customer_codes (
                  customer_id, deanery, parish_code, code1, code2, block,
                  mattress_id, created_at, parish_id, surname, first_name,
                  customer_code, customer_code_1, customer_code_2,
                  room, room_number, deanery_id, mattress_code
                )
                VALUES (?, ?, ?, ?, ?, ?, NULL, datetime('now'), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
              `);

              let mattressIndex = 0;
              let bedCounter = assignedCount + 1; // Start after current assigned beds

              members.forEach((member) => {
                serial++;
                const customerSerial = serial.toString().padStart(4, '0');

                let fullCode;
                let roomNumber = null;
                let mattressCode = null;
                let assignedRoom = null;
                let assignedBlock = null;

                if (member.mattress_r === 'Yes') {
                  const bedSuffix = `${block} ${String(bedCounter).padStart(3, '0')}`;
                  mattressCode = mattressCodesGenerated[mattressIndex++];
                  fullCode = `${deaneryCode} ${parishCode} ${customerSerial} ${bedSuffix} ${mattressCode}`;
                  roomNumber = bedSuffix;
                  assignedRoom = roomCode;
                  assignedBlock = block;
                  bedCounter++;
                } else {
                  fullCode = `${deaneryCode} ${parishCode} ${customerSerial}`;
                }

                insertStmt.run(
                  member.id,
                  deaneryName,
                  parishCode,
                  fullCode,
                  fullCode,
                  assignedBlock,  // ✅ null if not needed
                  member.parish_id,
                  member.surname,
                  member.first_name,
                  customerSerial,
                  fullCode,
                  fullCode,
                  assignedRoom,
                  roomNumber,
                  member.deanery_id,
                  mattressCode
                );
              });

              insertStmt.finalize();
              return res.json({ success: true, assigned: members.length });
            });
          });
        });
      });
    });
  });
});


//const PORT = 3010;

app.listen(PORT, () => {
  console.log(`Server running at http://localhost:${PORT}`);
});


// Parish APIs
app.get('/api/parish-code/:deanery_code', isAuthenticated, (req, res) => {
  const { deanery_code } = req.params;
  db.get('SELECT COUNT(*) AS count FROM parishes WHERE deanery_code = ?', [deanery_code], (err, row) => {
    if (err) return res.status(500).json({ success: false });
    const parish_code = deanery_code + '/' + (row.count + 1).toString().padStart(2, '0');
    res.json({ parish_code });
  });
});

app.post('/api/parish', async (req, res) => {
  const { name, location, deanery_code, parish_name, parish_code, mattress_allocation } = req.body;
  try {
    const result = await db.run(
      `INSERT INTO parishes (name, location, deanery_code, parish_name, parish_code, mattress_allocation)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [name, location, deanery_code, parish_name, parish_code, mattress_allocation]
    );
    res.json({ success: true });
  } catch (error) {
    console.error('Insert failed:', error);
    res.status(500).json({ success: false, error: 'Insert failed' });
  }
});


app.put('/api/parish/:id', (req, res) => {
  const { id } = req.params;
  const { mattress_allocation } = req.body;

  db.run('UPDATE parishes SET mattress_allocation = ? WHERE id = ?', [mattress_allocation, id], function (err) {
    if (err) {
      console.error("Update failed:", err.message);
      return res.status(500).json({ success: false, error: err.message });
    }
    res.json({ success: true });
  });
});


app.get('/api/parishes/:deaneryCode', isAuthenticated, (req, res) => {
  db.all('SELECT parish_code, parish_name FROM parishes WHERE deanery_code = ?', [req.params.deaneryCode], (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});

app.get('/api/parishes', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM parishes', (err, rows) => {
    if (err) return res.status(500).json({ success: false });
    res.json(rows);
  });
});

app.delete('/api/parish/:id', isAuthenticated, (req, res) => {
  db.run('DELETE FROM parishes WHERE id = ?', [req.params.id], err => {
    if (err) return res.json({ success: false });
    res.json({ success: true });
  });
});

app.get('/api/parish/export', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM parishes', (err, rows) => {
    if (err) return res.status(500).json({ success: false });
    const ws = xlsx.utils.json_to_sheet(rows);
    const wb = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(wb, ws, "Parishes");
    const buffer = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });
    res.setHeader('Content-Disposition', 'attachment; filename="parishes.xlsx"');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buffer);
  });
});

// Supplier APIs
app.get('/api/suppliers', isAuthenticated, (req, res) => {
 db.all(`
    SELECT *, 
      COALESCE(available_qty, qty_collected) AS display_qty 
    FROM suppliers
  `, (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});

app.get('/api/suppliers', isAuthenticated, (req, res) => {
  db.all(`SELECT code, available_qty FROM suppliers`, (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});


 app.get('/api/suppliers/names', isAuthenticated, (req, res) => {
  db.all('SELECT id, supplier_name FROM suppliers ORDER BY supplier_name', (err, rows) => {
    if (err) return res.status(500).json({ error: 'Failed to load suppliers' });
    res.json(rows);
  });
});

app.get('/api/mattresses', isAuthenticated, (req, res) => {
  db.all(`
    SELECT 
      mattress_code, 
      COUNT(*) AS total_qty,
      SUM(CASE WHEN assigned = 0 THEN 1 ELSE 0 END) AS available_qty
    FROM mattresses
    GROUP BY mattress_code
  `, (err, rows) => {
    if (err) return res.status(500).json({ success: false, error: err.message });
    res.json(rows);
  });
});

// API to fetch existing customer code by first name, surname, and parish code
app.get('/api/customer-code/:first_name/:surname/:parish_code', (req, res) => {
  const { first_name, surname, parish_code } = req.params;

  db.get(`SELECT code1 AS code, mattress_id FROM customer_codes WHERE first_name = ? AND surname = ? AND parish_id = ?`,
    [first_name, surname, parish_code],
    (err, row) => {
      if (err) return res.status(500).json({ error: err.message });
      if (row) {
        return res.json({ code: row.code, mattress_id: row.mattress_id });
      } else {
        return res.json({ code: null });
      }
    }
  );
});


app.get('/api/mattress-stats', isAuthenticated, (req, res) => {
  db.all(`SELECT mattress_code, SUM(qty_collected) AS total_qty, 
                 COALESCE(SUM(available_qty), SUM(qty_collected)) AS available_qty 
          FROM suppliers 
          GROUP BY mattress_code`, [], (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});


app.post('/api/suppliers', isAuthenticated, (req, res) => {
  const {
    supplier_name,
    contact,
    location,
    date_received,
    date_returned,
    qty_collected,
    mattress_code,
    assignment_location
  } = req.body;

  const formattedAssignment = Array.isArray(assignment_location)
    ? assignment_location.join(', ')
    : assignment_location;

  db.run(
    `INSERT INTO suppliers (supplier_name, contact, location, date_received, date_returned, qty_collected, mattress_code, assignment_location)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      supplier_name,
      contact,
      location,
      date_received,
      date_returned,
      qty_collected,
      mattress_code,
      formattedAssignment,
    ],
    function (err) {
      if (err) {
        return res.json({ success: false, error: err.message });
      }

      // ✅ Insert one mattress row per unit
      const qty = parseInt(qty_collected, 10);
      if (qty > 0) {
        const stmt = db.prepare(
          `INSERT INTO mattresses (mattress_code, assigned) VALUES (?, 0)`
        );
        for (let i = 0; i < qty; i++) {
          stmt.run(mattress_code);
        }
        stmt.finalize();
      }

      res.json({ success: true, id: this.lastID });
    }
  );
});

app.delete('/api/suppliers/:id', isAuthenticated, (req, res) => {
  const supplierId = req.params.id;

  // Step 1: Get the mattress_code and qty_collected for this supplier
  db.get('SELECT mattress_code, qty_collected FROM suppliers WHERE id = ?', [supplierId], (err, supplier) => {
    if (err || !supplier) {
      return res.status(500).json({ success: false, message: 'Supplier not found' });
    }

    const { mattress_code, qty_collected } = supplier;

    // Step 2: Delete mattresses related to this supplier (unassigned only)
    db.run(
      `DELETE FROM mattresses 
       WHERE id IN (
         SELECT id FROM mattresses 
         WHERE mattress_code = ? AND assigned = 0 
         LIMIT ?
       )`,
      [mattress_code, qty_collected],
      function (err2) {
        if (err2) {
          return res.status(500).json({ success: false, message: 'Failed to delete mattresses' });
        }

        // Step 3: Delete supplier record
        db.run('DELETE FROM suppliers WHERE id = ?', [supplierId], function (err3) {
          if (err3) return res.status(500).json({ success: false, message: 'Failed to delete supplier' });

          res.json({ success: true });
        });
      }
    );
  });
});




// 🔹 DELETE a room
app.delete('/api/rooms/:id', isAuthenticated, (req, res) => {
  db.run('DELETE FROM rooms WHERE id = ?', [req.params.id], function (err) {
    if (err) return res.status(500).json({ error: 'Failed to delete room' });
    res.json({ success: true });
  });
});

// ✅ NEW: Save two customer codes
app.post('/api/customer-codes', isAuthenticated, (req, res) => {
  const { parish_code, surname, given_name, code1, code2 } = req.body;

  db.get(`SELECT * FROM customer_codes WHERE parish_code = ? AND surname = ? AND given_name = ?`, 
    [parish_code, surname, given_name], (err, row) => {
    if (err) return res.status(500).json({ success: false, error: err.message });
    if (row) return res.json({ success: false, message: "Duplicate entry exists" });

    db.run(`INSERT INTO customer_codes (parish_code, surname, given_name, customer_code_1, customer_code_2) VALUES (?, ?, ?, ?, ?)`,
      [parish_code, surname, given_name, code1, code2],
      function (err) {
        if (err) return res.status(500).json({ success: false, error: err.message });
        res.json({ success: true, id: this.lastID });
      });
  });
});



// ✅ Get all customer codes
app.get('/api/customer-codes', isAuthenticated, (req, res) => {
  db.all(`SELECT * FROM customer_codes ORDER BY id DESC`, (err, rows) => {
    if (err) return res.status(500).json({ success: false });
    res.json(rows);
  });
});

// ✅ Delete customer code by ID

app.delete('/api/customer-codes/:id', isAuthenticated, (req, res) => {
  const codeId = req.params.id;

  // Step 1: Get the mattress_id and mattress_code
  db.get(`SELECT mattress_id FROM customer_codes WHERE id = ?`, [codeId], (err, row) => {
    if (err || !row) {
      return res.status(500).json({ success: false, message: 'Customer code not found' });
    }

    const mattressId = row.mattress_id;

    // Step 2: Delete the customer code
    db.run(`DELETE FROM customer_codes WHERE id = ?`, [codeId], function(err2) {
      if (err2) {
        return res.status(500).json({ success: false, message: 'Failed to delete customer code' });
      }

      // Step 3: Mark the mattress as unassigned
      db.run(`UPDATE mattresses SET assigned = 0 WHERE mattress_code = ?`, [mattressId], function(err3) {
        if (err3) {
          return res.status(500).json({ success: false, message: 'Failed to release mattress' });
        }

        // Step 4: Restore available_qty in supplier
        const supplierPrefix = mattressId?.charAt(0);
        db.run(`UPDATE suppliers SET available_qty = available_qty + 1 WHERE mattress_code = ?`, [supplierPrefix], function(err4) {
          if (err4) {
            return res.status(500).json({ success: false, message: 'Failed to update supplier stock' });
          }

          res.json({ success: true });
        });
      });
    });
  });
});


// ✅ Export Excel
app.get('/api/customer-codes/export/excel', isAuthenticated, (req, res) => {
  db.all(`SELECT * FROM customer_codes ORDER BY id ASC`, (err, rows) => {
    if (err) return res.status(500).json({ success: false });

    const ws = xlsx.utils.json_to_sheet(rows);
    const wb = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(wb, ws, "CustomerCodes");
    const buffer = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });

    res.setHeader('Content-Disposition', 'attachment; filename="customer_codes.xlsx"');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buffer);
  });
});

app.get('/api/customers/download/excel', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM customers ORDER BY id ASC', (err, rows) => {
    if (err) return res.status(500).send("Error exporting Excel");

    const ws = xlsx.utils.json_to_sheet(rows);
    const wb = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(wb, ws, 'Customers');

    const filePath = './public/customers.xlsx';
    xlsx.writeFile(wb, filePath);

    res.download(filePath);
  });
});



// ✅ Export PDF
app.get('/api/customer-codes/export/pdf', isAuthenticated, (req, res) => {
  db.all(`SELECT * FROM customer_codes ORDER BY id ASC`, (err, rows) => {
    if (err) return res.status(500).json({ success: false });

    const doc = new PDFDocument();
    const filename = "customer_codes.pdf";
    res.setHeader('Content-disposition', `attachment; filename="${filename}"`);
    res.setHeader('Content-type', 'application/pdf');
    doc.pipe(res);

    doc.fontSize(14).text('Customer Codes', { align: 'center' });
    doc.moveDown();

    rows.forEach(row => {
      doc.fontSize(10).text(`ID: ${row.id}, Parish: ${row.parish_code}, Surname: ${row.surname}, Given Name: ${row.given_name}`);
      doc.text(`Code1: ${row.customer_code_1}, Code2: ${row.customer_code_2}`);
      doc.moveDown();
    });

    doc.end();
  });
});

app.get('/api/customers/download/pdf', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM customers ORDER BY id ASC', (err, rows) => {
    if (err) return res.status(500).send("Error exporting PDF");

    const doc = new PDFDocument();
    const filePath = './public/customers.pdf';
    doc.pipe(fs.createWriteStream(filePath));

    doc.fontSize(14).text('Customer List', { align: 'center' }).moveDown();
    rows.forEach(r => {
      doc.fontSize(10).text(`Name: ${r.first_name} ${r.surname}, Code: ${r.customer_code}, Mattress: ${r.mattress_code}, Block: ${r.block}, Room: ${r.room}`);
    });

    doc.end();
    doc.on('finish', () => res.download(filePath));
  });
});


// 🔹 Export room list to Excel
app.get('/api/export-rooms/excel', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM rooms', (err, rows) => {
    if (err) return res.status(500).json({ success: false });

    const ws = xlsx.utils.json_to_sheet(rows);
    const wb = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(wb, ws, "Rooms");

    const buffer = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });

    res.setHeader('Content-Disposition', 'attachment; filename="rooms.xlsx"');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buffer);
  });
});

// 🔹 Export room list to PDF
app.get('/api/export-rooms/pdf', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM rooms', (err, rows) => {
    if (err) return res.status(500).send("Error exporting PDF");

    const doc = new PDFDocument();
    const filePath = './public/rooms.pdf';
    doc.pipe(fs.createWriteStream(filePath));

    doc.fontSize(14).text('Room and Bed Space List', { align: 'center' }).moveDown();

    rows.forEach(room => {
      doc.fontSize(10).text(`Name: ${room.hall_name}, Code: ${room.code}, Bed Spaces: ${room.bed_spaces}`);
    });

    doc.end();
    doc.on('finish', () => res.download(filePath));
  });
});


// Delete customer
app.delete('/api/customers/:id', isAuthenticated, (req, res) => {
  db.run('DELETE FROM customers WHERE id = ?', [req.params.id], (err) => {
    if (err) return res.json({ success: false });
    res.json({ success: true });
  });
});

app.get('/api/mattress-locations/export/excel', isAuthenticated, (req, res) => {
  db.all(`SELECT ml.*, m.tracking_code 
          FROM mattress_locations ml
          LEFT JOIN mattresses m ON ml.mattress_id = m.id`, (err, rows) => {
    if (err) return res.status(500).json({ success: false });

    const sheet = xlsx.utils.json_to_sheet(rows);
    const wb = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(wb, sheet, "MattressLocations");

    const buffer = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });
    res.setHeader('Content-Disposition', 'attachment; filename="mattress_locations.xlsx"');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buffer);
  });
});

app.post('/api/upload-customers', isAuthenticated, upload.single('file'), (req, res) => {
  const workbook = xlsx.readFile(req.file.path);
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const rawRows = xlsx.utils.sheet_to_json(sheet, { defval: '' });

  // Normalize headers to lowercase for consistency
  const rows = rawRows.map(originalRow => {
    const normalized = {};
    Object.keys(originalRow).forEach(key => {
      normalized[key.trim().toLowerCase()] = originalRow[key];
    });
    return normalized;
  });

  const stmt = db.prepare(`
    INSERT INTO customers (
      serial, title, surname, middle_name, first_name, phone, email,
      deanery, parish, mattress, customer_id, mattress_r, on_time_late,
      hotel_r, cmo_status, official, above_65yrs
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  rows.forEach(row => {
    console.log("Row received:", row); // ✅ Debug log

    stmt.run(
      row['s/n'],
      row['title'],
      row['surname'],
      row['middle name'],
      row['first name'],
      row['phone number'],
      '', // Email not available in Excel
      row['deanery name'],
      row['parish name'],
      '', // Mattress not available in Excel
      row['customer_id'],
      row['mattress_r'],
      row['on time/late'],
      row['hotel_r'],
      row['cmo_status'],
      row['official'],
      row['above 65yrs']
    );
  });

  stmt.finalize();
  fs.unlinkSync(req.file.path); // Clean up uploaded file

  res.json({ success: true, count: rows.length });
});




// Start Server
app.listen(PORT, () => console.log(`✅ Server running at http://localhost:${PORT}`));

app.get('/api/suppliers', isAuthenticated, (req, res) => {
app.get('/api/suppliers', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM suppliers ORDER BY id DESC', (err, rows) => {
    if (err) return res.status(500).json({ success: false });
    res.json(rows);
  });
});
});


app.post('/api/suppliers', isAuthenticated, (req, res) => {
  const { supplier_name, contact, location, date_received, date_returned, qty_collected, mattress_code, assignment_location } = req.body;

  const formattedAssignment = Array.isArray(assignment_location) ? assignment_location.join(', ') : assignment_location;

  db.run('INSERT INTO suppliers (supplier_name, contact, location, date_received, date_returned, qty_collected, mattress_code, assignment_location) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
    [supplier_name, contact, location, date_received, date_returned, qty_collected, mattress_code, formattedAssignment],
    function (err) {
      if (err) return res.json({ success: false, error: err.message });

      const qty = parseInt(qty_collected, 10);
      if (!isNaN(qty) && qty > 0) {
        const stmt = db.prepare('INSERT INTO mattresses (mattress_code, assigned) VALUES (?, 0)');
        for (let i = 0; i < qty; i++) {
          stmt.run(mattress_code);
        }
        stmt.finalize();
      }

      res.json({ success: true, id: this.lastID });
    });
});