// server.js
const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const http = require('http');
const { Server } = require('socket.io');
const fs = require('fs');

const app = express();
const server = http.createServer(app);
const io = new Server(server);

app.use(bodyParser.json());
app.use(express.static(path.join(__dirname, 'public')));

// open (or create) the SQLite database file
const db = new sqlite3.Database('positions.db', err => {
  if (err) {
    console.error('Could not open DB', err);
    process.exit(1);
  }
});

// create table if not exists
db.run(
  `CREATE TABLE IF NOT EXISTS positions (
     square_id TEXT PRIMARY KEY,
     fruit     TEXT
   )`,
  (err) => {
    if (err) console.error('Could not ensure table', err);
  }
);

// get all saved positions
app.get('/api/positions', (req, res) => {
  db.all('SELECT square_id, fruit FROM positions', (err, rows) => {
    if (err) return res.status(500).json({ error: err.message });
    // convert to an object. should look like this: { "1": "Apple", "2": "Banana", … }
    const mapping = {};
    rows.forEach(r => (mapping[r.square_id] = r.fruit));
    res.json(mapping);
  });
});

// save (or update) a single square’s item
app.post('/api/positions', (req, res) => {
  const { squareId, fruit } = req.body;
  if (!squareId || typeof fruit !== 'string') {
    return res.status(400).json({ error: 'squareId and fruit required' });
  }
  db.run(
    `INSERT INTO positions (square_id, fruit)
     VALUES (?, ?)
     ON CONFLICT(square_id) DO UPDATE SET fruit=excluded.fruit`,
    [squareId, fruit],
    function (err) {
      if (err) return res.status(500).json({ error: err.message });
      // broadcast update via Socket.io
      io.emit('update', { squareId, fruit });
      res.json({ success: true });
    }
  );
});

// Serve categories and fruits from a JSON file
app.get('/api/categories', (req, res) => {
  const categoriesPath = path.join(__dirname, 'categories.json');
  fs.readFile(categoriesPath, 'utf8', (err, data) => {
    if (err) return res.status(500).json({ error: 'Could not load categories' });
    try {
      const categories = JSON.parse(data);
      res.json(categories);
    } catch (e) {
      res.status(500).json({ error: 'Invalid categories file' });
    }
  });
});

// Add a fruit to a category in categories.json
app.post('/api/add-fruit', (req, res) => {
  const { category, fruit } = req.body;
  if (!category || !fruit) {
    return res.status(400).json({ error: 'category and fruit required' });
  }
  const categoriesPath = path.join(__dirname, 'categories.json');
  fs.readFile(categoriesPath, 'utf8', (err, data) => {
    if (err) return res.status(500).json({ error: 'Could not load categories' });
    let categories;
    try {
      categories = JSON.parse(data);
    } catch (e) {
      return res.status(500).json({ error: 'Invalid categories file' });
    }
    const cat = categories.find(c => c.name === category);
    if (!cat) return res.status(404).json({ error: 'Category not found' });
    // Prevent duplicates
    if (cat.fruits.includes(fruit)) {
      return res.status(400).json({ error: 'Fruit already exists in category' });
    }
    cat.fruits.push(fruit);
    fs.writeFile(categoriesPath, JSON.stringify(categories, null, 2), err2 => {
      if (err2) return res.status(500).json({ error: 'Could not save categories' });
      res.json({ success: true });
    });
  });
});

// Delete a fruit from a category in categories.json
app.post('/api/delete-fruit', (req, res) => {
  const { category, fruit } = req.body;
  if (!category || !fruit) {
    return res.status(400).json({ error: 'category and fruit required' });
  }
  const categoriesPath = path.join(__dirname, 'categories.json');
  fs.readFile(categoriesPath, 'utf8', (err, data) => {
    if (err) return res.status(500).json({ error: 'Could not load categories' });
    let categories;
    try {
      categories = JSON.parse(data);
    } catch (e) {
      return res.status(500).json({ error: 'Invalid categories file' });
    }
    const cat = categories.find(c => c.name === category);
    if (!cat) return res.status(404).json({ error: 'Category not found' });
    const idx = cat.fruits.indexOf(fruit);
    if (idx === -1) return res.status(404).json({ error: 'Fruit not found in category' });
    cat.fruits.splice(idx, 1);
    fs.writeFile(categoriesPath, JSON.stringify(categories, null, 2), err2 => {
      if (err2) return res.status(500).json({ error: 'Could not save categories' });
      res.json({ success: true });
    });
  });
});

// start server
const PORT = process.env.PORT || 3085;
server.listen(PORT, () => {
  console.log(`Listening on http://localhost:${PORT}`);
});