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 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');
}
function isAdmin(req, res, next) {
  if (req.session.user?.role === 'admin') return next();
  res.status(403).json({ success: false, message: 'Unauthorized' });
}

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 deaneries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    location TEXT,
    code TEXT UNIQUE
  )`);

  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
)`);


// Authentication routes
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 });
    }
  });
});

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

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

// Deanery APIs
app.get('/api/deanery-code', isAuthenticated, (req, res) => {
  db.get('SELECT COUNT(*) as count FROM deaneries', (err, row) => {
    const next = row.count + 1;
    const code = next.toString().padStart(2, '0');
    res.json({ code });
  });
});

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) => {
  db.all("SELECT code, name FROM deaneries", [], (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});

app.get('/api/supplier-codes', isAuthenticated, (req, res) => {
  db.all("SELECT id, supplier_name, mattress_code FROM suppliers", [], (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});

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


app.get('/api/deanery/export', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM deaneries 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, "Deaneries");
    const buffer = xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });
    res.setHeader('Content-Disposition', 'attachment; filename="deaneries.xlsx"');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buffer);
  });
});

// Parish APIs
app.get('/api/parish-code/:deanery_code', isAuthenticated, (req, res) => {
  const deanery_code = req.params.deanery_code;
  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 next = row.count + 1;
      const parish_code = deanery_code + '/' + next.toString().padStart(2, '0');
      res.json({ parish_code });
    }
  );
});

app.post('/api/parish', isAuthenticated, (req, res) => {
  const { name, location, deanery_code, parish_name } = req.body;
  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 code = deanery_code + '/' + (row.count + 1).toString().padStart(2, '0');
    db.run(
      `INSERT INTO parishes (name, location, deanery_code, parish_name, parish_code)
       VALUES (?, ?, ?, ?, ?)`,
      [name, location, deanery_code, parish_name, code],
      function (err) {
        if (err) return res.json({ success: false });
        res.json({ success: true });
      }
    );
  });
});

app.get('/api/parishes', isAuthenticated, (req, res) => {
  db.all('SELECT * FROM parishes ORDER BY id DESC', (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 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, "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 * 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 } = req.body;
  const mattress_code = location.slice(0, 2).toUpperCase();
  db.run(`INSERT INTO suppliers (supplier_name, contact, location, date_received, date_returned, qty_collected, mattress_code)
          VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [supplier_name, contact, location, date_received, date_returned, qty_collected, mattress_code],
    function (err) {
      if (err) return res.json({ success: false });
      res.json({ success: true, id: this.lastID });
    });
});

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


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