const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const cors = require('cors');

const app = express();
const port = 3001;

app.use(cors());
app.use(express.json());

const db = new sqlite3.Database('./attendance.db', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the attendance database.');
});

db.run(`CREATE TABLE IF NOT EXISTS attendance (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  employee_id TEXT,
  check_in DATETIME,
  check_out DATETIME
)`);

app.post('/check-in', (req, res) => {
  const { employee_id } = req.body;
  const check_in = new Date().toISOString();

  db.run(`INSERT INTO attendance (employee_id, check_in) VALUES (?, ?)`, [employee_id, check_in], function(err) {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    res.json({ id: this.lastID, employee_id, check_in });
  });
});

app.post('/check-out', (req, res) => {
  const { employee_id } = req.body;
  const check_out = new Date().toISOString();

  db.run(`UPDATE attendance SET check_out = ? WHERE employee_id = ? AND check_out IS NULL`, [check_out, employee_id], function(err) {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    res.json({ employee_id, check_out });
  });
});

app.get('/attendance/:employee_id', (req, res) => {
  const { employee_id } = req.params;

  db.all(`SELECT * FROM attendance WHERE employee_id = ? ORDER BY check_in DESC LIMIT 7`, [employee_id], (err, rows) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    res.json(rows);
  });
});

app.get('/report/:employee_id', (req, res) => {
  const { employee_id } = req.params;

  db.all(`SELECT * FROM attendance WHERE employee_id = ? AND check_in >= date('now', '-7 days')`, [employee_id], (err, rows) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }

    const report = rows.reduce((acc, row) => {
      if (row.check_in && row.check_out) {
        const duration = new Date(row.check_out) - new Date(row.check_in);
        acc.total_hours += duration / (1000 * 60 * 60);
        acc.days_worked += 1;
      }
      return acc;
    }, { total_hours: 0, days_worked: 0 });

    res.json(report);
  });
});

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