const express = require('express');
const db = require('./db');
const { requireAdmin, isValidAdminKey } = require('./auth');

const router = express.Router();

function todayStr() {
  const d = new Date();
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, '0');
  const day = String(d.getDate()).padStart(2, '0');
  return `${y}-${m}-${day}`;
}

const DATE_RE = /^\d{4}-\d{2}-\d{2}$/;

// Resolves a caller-supplied date, falling back to today and rejecting
// malformed or future dates (returns null on invalid input).
function resolveDate(raw) {
  if (!raw) return todayStr();
  if (!DATE_RE.test(raw)) return null;
  if (raw > todayStr()) return null;
  return raw;
}

// ---- Players -----------------------------------------------------------

router.get('/players', (req, res) => {
  const date = resolveDate(req.query.date) || todayStr();
  const rows = db
    .prepare(
      `SELECT p.id, p.name,
              COUNT(d.id) AS total,
              SUM(CASE WHEN d.date = ? THEN 1 ELSE 0 END) AS dateCount
       FROM players p
       LEFT JOIN doses d ON d.player_id = p.id
       GROUP BY p.id
       ORDER BY total DESC, p.name COLLATE NOCASE ASC`
    )
    .all(date);
  res.json(rows.map((r) => ({ ...r, dateCount: r.dateCount || 0 })));
});

router.post('/players', requireAdmin, (req, res) => {
  const name = String(req.body?.name || '').trim();
  if (!name) {
    return res.status(400).json({ error: 'Le nom est requis' });
  }
  if (name.length > 40) {
    return res.status(400).json({ error: 'Le nom est trop long (40 caractères maximum)' });
  }
  try {
    const info = db.prepare('INSERT INTO players (name) VALUES (?)').run(name);
    res.status(201).json({ id: info.lastInsertRowid, name, total: 0, dateCount: 0 });
  } catch (err) {
    if (err.code === 'ERR_SQLITE_ERROR' && /UNIQUE constraint failed/.test(err.message)) {
      return res.status(409).json({ error: 'Un joueur avec ce nom existe déjà' });
    }
    throw err;
  }
});

router.delete('/players/:id', requireAdmin, (req, res) => {
  const playerId = Number(req.params.id);
  const player = db.prepare('SELECT id FROM players WHERE id = ?').get(playerId);
  if (!player) {
    return res.status(404).json({ error: 'Joueur introuvable' });
  }
  db.exec('BEGIN');
  db.prepare('DELETE FROM doses WHERE player_id = ?').run(playerId);
  db.prepare('DELETE FROM players WHERE id = ?').run(playerId);
  db.exec('COMMIT');
  res.status(204).end();
});

// ---- Timeseries (doses per player, per date) -----------------------------

router.get('/timeseries', (req, res) => {
  const players = db.prepare('SELECT id, name FROM players ORDER BY id ASC').all();
  const dates = db
    .prepare('SELECT DISTINCT date FROM doses ORDER BY date ASC')
    .all()
    .map((r) => r.date);

  const counts = db
    .prepare('SELECT player_id, date, COUNT(*) AS c FROM doses GROUP BY player_id, date')
    .all();

  const byPlayerDate = new Map(); // `${playerId}|${date}` -> count
  for (const row of counts) {
    byPlayerDate.set(`${row.player_id}|${row.date}`, row.c);
  }

  const series = players.map((p) => ({
    playerId: p.id,
    name: p.name,
    counts: dates.map((date) => byPlayerDate.get(`${p.id}|${date}`) || 0),
  }));

  res.json({ dates, series });
});

// ---- Admin auth ----------------------------------------------------------

router.post('/admin/verify', (req, res) => {
  const password = String(req.body?.password || '');
  res.json({ ok: isValidAdminKey(password) });
});

// ---- Doses (admin only) --------------------------------------------------

router.post('/doses', requireAdmin, (req, res) => {
  const playerId = Number(req.body?.playerId);
  const player = db.prepare('SELECT id FROM players WHERE id = ?').get(playerId);
  if (!player) {
    return res.status(404).json({ error: 'Joueur introuvable' });
  }

  const date = resolveDate(req.body?.date);
  if (!date) {
    return res.status(400).json({ error: 'Date invalide (elle ne peut pas être dans le futur)' });
  }

  db.prepare('INSERT INTO doses (player_id, date) VALUES (?, ?)').run(playerId, date);

  const total = db.prepare('SELECT COUNT(*) AS c FROM doses WHERE player_id = ?').get(playerId).c;
  const dateCount = db
    .prepare('SELECT COUNT(*) AS c FROM doses WHERE player_id = ? AND date = ?')
    .get(playerId, date).c;

  res.status(201).json({ playerId, total, dateCount });
});

router.delete('/doses/latest', requireAdmin, (req, res) => {
  const playerId = Number(req.query.playerId);
  const player = db.prepare('SELECT id FROM players WHERE id = ?').get(playerId);
  if (!player) {
    return res.status(404).json({ error: 'Joueur introuvable' });
  }

  const date = resolveDate(req.query.date);
  if (!date) {
    return res.status(400).json({ error: 'Date invalide (elle ne peut pas être dans le futur)' });
  }

  const latest = db
    .prepare('SELECT id FROM doses WHERE player_id = ? AND date = ? ORDER BY id DESC LIMIT 1')
    .get(playerId, date);

  if (!latest) {
    return res.status(404).json({ error: 'Aucune dose enregistrée pour ce joueur à cette date' });
  }
  db.prepare('DELETE FROM doses WHERE id = ?').run(latest.id);

  const total = db.prepare('SELECT COUNT(*) AS c FROM doses WHERE player_id = ?').get(playerId).c;
  const dateCount = db
    .prepare('SELECT COUNT(*) AS c FROM doses WHERE player_id = ? AND date = ?')
    .get(playerId, date).c;

  res.json({ playerId, total, dateCount });
});

module.exports = router;