const express = require('express');
const Database = require('better-sqlite3');
const bcrypt = require('bcryptjs');
const jwt = require('jsonwebtoken');
const path = require('path');
const cors = require('cors');

const app = express();
const PORT = process.env.PORT || 3007;
const JWT_SECRET = process.env.JWT_SECRET || 'portugais-secret-key-2026-change-me';
const ADMIN_PASSWORD = process.env.ADMIN_PASSWORD || 'Pierre2026!';

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

// ===== DATABASE =====
const db = new Database('/data/portugais.db');
db.pragma('journal_mode = WAL');

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    display_name TEXT NOT NULL,
    is_admin INTEGER DEFAULT 0,
    avatar TEXT DEFAULT '🇵🇹',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS points (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    points INTEGER DEFAULT 0,
    activity TEXT,
    activity_type TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS daily_progress (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    date TEXT NOT NULL,
    exercises_done INTEGER DEFAULT 0,
    games_played INTEGER DEFAULT 0,
    points_earned INTEGER DEFAULT 0,
    streak INTEGER DEFAULT 0,
    UNIQUE(user_id, date),
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS favorites (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    phrase TEXT NOT NULL,
    UNIQUE(user_id, phrase),
    FOREIGN KEY (user_id) REFERENCES users(id)
  );
`);

// Create default admin
const existingAdmin = db.prepare('SELECT id FROM users WHERE username = ?').get('Pierre');
if (!existingAdmin) {
  const hash = bcrypt.hashSync(ADMIN_PASSWORD, 10);
  db.prepare('INSERT INTO users (username, password, display_name, is_admin, avatar) VALUES (?, ?, ?, ?, ?)').run('Pierre', hash, 'Pierre', 1, '👨‍💼');
  console.log('Admin user Pierre created');
}

// ===== AUTH MIDDLEWARE =====
function authMiddleware(req, res, next) {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'Token requis' });
  try {
    const decoded = jwt.verify(token, JWT_SECRET);
    const user = db.prepare('SELECT id, username, display_name, is_admin, avatar FROM users WHERE id = ?').get(decoded.userId);
    if (!user) return res.status(401).json({ error: 'Utilisateur introuvable' });
    req.user = user;
    next();
  } catch {
    res.status(401).json({ error: 'Token invalide' });
  }
}

function adminMiddleware(req, res, next) {
  if (!req.user.is_admin) return res.status(403).json({ error: 'Accès admin requis' });
  next();
}

// ===== AUTH ROUTES =====
app.post('/api/login', (req, res) => {
  const { username, password } = req.body;
  const user = db.prepare('SELECT * FROM users WHERE username = ?').get(username);
  if (!user || !bcrypt.compareSync(password, user.password)) {
    return res.status(401).json({ error: 'Identifiant ou mot de passe incorrect' });
  }
  const token = jwt.sign({ userId: user.id }, JWT_SECRET, { expiresIn: '30d' });
  res.json({
    token,
    user: { id: user.id, username: user.username, display_name: user.display_name, is_admin: user.is_admin, avatar: user.avatar }
  });
});

app.post('/api/register', authMiddleware, adminMiddleware, (req, res) => {
  const { username, password, display_name, avatar } = req.body;
  if (!username || !password || !display_name) {
    return res.status(400).json({ error: 'Tous les champs sont requis' });
  }
  const existing = db.prepare('SELECT id FROM users WHERE username = ?').get(username);
  if (existing) return res.status(400).json({ error: 'Ce nom d\'utilisateur existe déjà' });
  const hash = bcrypt.hashSync(password, 10);
  const result = db.prepare('INSERT INTO users (username, password, display_name, avatar) VALUES (?, ?, ?, ?)').run(username, hash, display_name, avatar || '🇵🇹');
  res.json({ id: result.lastInsertRowid, username, display_name, avatar: avatar || '🇵🇹' });
});

app.get('/api/me', authMiddleware, (req, res) => {
  res.json({ user: req.user });
});

// ===== ADMIN ROUTES =====
app.get('/api/admin/users', authMiddleware, adminMiddleware, (req, res) => {
  const users = db.prepare(`
    SELECT u.id, u.username, u.display_name, u.is_admin, u.avatar, u.created_at,
           COALESCE(SUM(p.points), 0) as total_points
    FROM users u
    LEFT JOIN points p ON p.user_id = u.id
    GROUP BY u.id
    ORDER BY total_points DESC
  `).all();
  res.json({ users });
});

app.delete('/api/admin/users/:id', authMiddleware, adminMiddleware, (req, res) => {
  const userId = parseInt(req.params.id);
  if (userId === req.user.id) return res.status(400).json({ error: 'Vous ne pouvez pas vous supprimer' });
  db.prepare('DELETE FROM favorites WHERE user_id = ?').run(userId);
  db.prepare('DELETE FROM daily_progress WHERE user_id = ?').run(userId);
  db.prepare('DELETE FROM points WHERE user_id = ?').run(userId);
  db.prepare('DELETE FROM users WHERE id = ?').run(userId);
  res.json({ ok: true });
});

app.put('/api/admin/users/:id/toggle-admin', authMiddleware, adminMiddleware, (req, res) => {
  const userId = parseInt(req.params.id);
  const user = db.prepare('SELECT is_admin FROM users WHERE id = ?').get(userId);
  if (!user) return res.status(404).json({ error: 'Utilisateur introuvable' });
  db.prepare('UPDATE users SET is_admin = ? WHERE id = ?').run(user.is_admin ? 0 : 1, userId);
  res.json({ is_admin: !user.is_admin });
});

// ===== POINTS ROUTES =====
app.post('/api/points/add', authMiddleware, (req, res) => {
  const { points, activity, activity_type } = req.body;
  const today = new Date().toISOString().split('T')[0];

  db.prepare('INSERT INTO points (user_id, points, activity, activity_type) VALUES (?, ?, ?, ?)').run(req.user.id, points, activity, activity_type);

  const existing = db.prepare('SELECT * FROM daily_progress WHERE user_id = ? AND date = ?').get(req.user.id, today);
  if (existing) {
    db.prepare('UPDATE daily_progress SET exercises_done = exercises_done + ?, points_earned = points_earned + ? WHERE user_id = ? AND date = ?').run(activity_type === 'exercise' ? 1 : 0, points, req.user.id, today);
  } else {
    const yesterday = new Date(Date.now() - 86400000).toISOString().split('T')[0];
    const yesterdayProgress = db.prepare('SELECT streak FROM daily_progress WHERE user_id = ? AND date = ?').get(req.user.id, yesterday);
    const newStreak = (yesterdayProgress && yesterdayProgress.streak > 0) ? yesterdayProgress.streak + 1 : 1;
    db.prepare('INSERT INTO daily_progress (user_id, date, exercises_done, games_played, points_earned, streak) VALUES (?, ?, ?, ?, ?, ?)').run(req.user.id, today, activity_type === 'exercise' ? 1 : 0, activity_type === 'game' ? 1 : 0, points, newStreak);
  }

  const total = db.prepare('SELECT COALESCE(SUM(points), 0) as total FROM points WHERE user_id = ?').get(req.user.id);
  res.json({ total_points: total.total });
});

app.get('/api/points/leaderboard', authMiddleware, (req, res) => {
  const leaderboard = db.prepare(`
    SELECT u.id, u.display_name, u.avatar,
           COALESCE(SUM(p.points), 0) as total_points
    FROM users u
    LEFT JOIN points p ON p.user_id = u.id
    GROUP BY u.id
    ORDER BY total_points DESC
    LIMIT 20
  `).all();
  res.json({ leaderboard });
});

app.get('/api/points/me', authMiddleware, (req, res) => {
  const total = db.prepare('SELECT COALESCE(SUM(points), 0) as total FROM points WHERE user_id = ?').get(req.user.id);
  const today = new Date().toISOString().split('T')[0];
  const progress = db.prepare('SELECT * FROM daily_progress WHERE user_id = ? AND date = ?').get(req.user.id, today);
  const recentPoints = db.prepare('SELECT * FROM points WHERE user_id = ? ORDER BY created_at DESC LIMIT 10').all(req.user.id);
  res.json({ total_points: total.total, today: progress || { exercises_done: 0, games_played: 0, points_earned: 0, streak: 0 }, recent: recentPoints });
});

app.get('/api/points/history', authMiddleware, (req, res) => {
  const history = db.prepare(`
    SELECT date, exercises_done, games_played, points_earned, streak
    FROM daily_progress WHERE user_id = ?
    ORDER BY date DESC LIMIT 30
  `).all(req.user.id);
  res.json({ history });
});

// ===== FAVORITES =====
app.get('/api/favorites', authMiddleware, (req, res) => {
  const favs = db.prepare('SELECT phrase FROM favorites WHERE user_id = ?').all(req.user.id);
  res.json({ favorites: favs.map(f => f.phrase) });
});

app.post('/api/favorites/toggle', authMiddleware, (req, res) => {
  const { phrase } = req.body;
  const existing = db.prepare('SELECT id FROM favorites WHERE user_id = ? AND phrase = ?').get(req.user.id, phrase);
  if (existing) {
    db.prepare('DELETE FROM favorites WHERE id = ?').run(existing.id);
    res.json({ favorited: false });
  } else {
    db.prepare('INSERT INTO favorites (user_id, phrase) VALUES (?, ?)').run(req.user.id, phrase);
    res.json({ favorited: true });
  }
});

// ===== SPA FALLBACK =====
app.get('*', (req, res) => {
  res.sendFile(path.join(__dirname, '../public/index.html'));
});

app.listen(PORT, '0.0.0.0', () => {
  console.log(`Portugais app running on port ${PORT}`);
});