alexandre grondin
566 lines
24 KiB
JavaScript
566 lines
24 KiB
JavaScript
const express = require("express");
|
|
const { Pool } = require("pg");
|
|
const cors = require("cors");
|
|
const jwt = require("jsonwebtoken");
|
|
const bcrypt = require("bcryptjs");
|
|
const path = require("path");
|
|
|
|
const app = express();
|
|
app.use(cors());
|
|
app.use(express.json());
|
|
|
|
const JWT_SECRET = process.env.JWT_SECRET || "budget-commun-secret-change-me";
|
|
const JWT_EXPIRY = "30d";
|
|
const ADMIN_USER = process.env.ADMIN_USER || "user1";
|
|
|
|
// ── Database ──────────────────────────────────────────────────
|
|
const pool = new Pool({
|
|
connectionString: process.env.DATABASE_URL || "postgres://budget:budget@localhost:5432/budget",
|
|
});
|
|
|
|
async function waitForDb(retries = 15, delay = 2000) {
|
|
for (let i = 0; i < retries; i++) {
|
|
try {
|
|
await pool.query("SELECT 1");
|
|
console.log("PostgreSQL connecté.");
|
|
return;
|
|
} catch {
|
|
console.log(`Attente de la base de données... (${i + 1}/${retries})`);
|
|
await new Promise(r => setTimeout(r, delay));
|
|
}
|
|
}
|
|
throw new Error("Impossible de se connecter à PostgreSQL.");
|
|
}
|
|
|
|
async function initDb() {
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS budgets (
|
|
id SERIAL PRIMARY KEY,
|
|
nom TEXT NOT NULL,
|
|
type TEXT NOT NULL CHECK(type IN ('commun','perso')),
|
|
owner_user_id INTEGER REFERENCES users(id)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS categories (
|
|
id SERIAL PRIMARY KEY,
|
|
slug TEXT UNIQUE NOT NULL,
|
|
label TEXT NOT NULL,
|
|
emoji TEXT NOT NULL DEFAULT '📦',
|
|
active INTEGER NOT NULL DEFAULT 1,
|
|
sort INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE TABLE IF NOT EXISTS expenses (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
amount REAL NOT NULL,
|
|
cat TEXT NOT NULL DEFAULT 'autre',
|
|
freq TEXT NOT NULL DEFAULT 'mensuel',
|
|
month TEXT,
|
|
budget_id INTEGER NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS pointage (
|
|
id SERIAL PRIMARY KEY,
|
|
expense_id INTEGER NOT NULL,
|
|
month TEXT NOT NULL,
|
|
pointed INTEGER NOT NULL DEFAULT 0,
|
|
pointed_at TEXT,
|
|
amount REAL NOT NULL,
|
|
name TEXT NOT NULL,
|
|
budget_id INTEGER NOT NULL DEFAULT 1,
|
|
UNIQUE(expense_id, month)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS savings (
|
|
id SERIAL PRIMARY KEY,
|
|
budget_id INTEGER NOT NULL REFERENCES budgets(id),
|
|
name TEXT NOT NULL,
|
|
emoji TEXT NOT NULL DEFAULT '💰',
|
|
monthly_amount REAL NOT NULL DEFAULT 0,
|
|
balance REAL NOT NULL DEFAULT 0,
|
|
target REAL,
|
|
closed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
expense_id INTEGER,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS savings_transactions (
|
|
id SERIAL PRIMARY KEY,
|
|
saving_id INTEGER NOT NULL REFERENCES savings(id) ON DELETE CASCADE,
|
|
amount REAL NOT NULL,
|
|
note TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
`);
|
|
await pool.query(`
|
|
ALTER TABLE expenses ADD COLUMN IF NOT EXISTS installments INTEGER;
|
|
ALTER TABLE expenses ADD COLUMN IF NOT EXISTS installments_paid INTEGER NOT NULL DEFAULT 0;
|
|
`);
|
|
|
|
// Seed users
|
|
const { rows: [{ c: userCount }] } = await pool.query("SELECT COUNT(*) AS c FROM users");
|
|
if (parseInt(userCount) === 0) {
|
|
await pool.query("INSERT INTO users (username,password) VALUES ($1,$2)", ["user1", bcrypt.hashSync("password1", 10)]);
|
|
await pool.query("INSERT INTO users (username,password) VALUES ($1,$2)", ["user2", bcrypt.hashSync("password2", 10)]);
|
|
console.log("Utilisateurs créés : user1 / user2");
|
|
}
|
|
|
|
// Seed budgets
|
|
const { rows: [{ c: budgetCount }] } = await pool.query("SELECT COUNT(*) AS c FROM budgets");
|
|
if (parseInt(budgetCount) === 0) {
|
|
const { rows: [u1] } = await pool.query("SELECT id FROM users WHERE username='user1'");
|
|
const { rows: [u2] } = await pool.query("SELECT id FROM users WHERE username='user2'");
|
|
await pool.query("INSERT INTO budgets (id,nom,type,owner_user_id) VALUES (1,'Commun','commun',NULL) ON CONFLICT DO NOTHING");
|
|
await pool.query("INSERT INTO budgets (id,nom,type,owner_user_id) VALUES (2,'Perso A','perso',$1) ON CONFLICT DO NOTHING", [u1.id]);
|
|
await pool.query("INSERT INTO budgets (id,nom,type,owner_user_id) VALUES (3,'Perso B','perso',$1) ON CONFLICT DO NOTHING", [u2.id]);
|
|
await pool.query("SELECT setval('budgets_id_seq', 3)");
|
|
console.log("Budgets créés : Commun, Perso A, Perso B");
|
|
}
|
|
|
|
// Seed categories
|
|
const { rows: [{ c: catCount }] } = await pool.query("SELECT COUNT(*) AS c FROM categories");
|
|
if (parseInt(catCount) === 0) {
|
|
const defaults = [
|
|
{ slug:"logement", label:"Logement", emoji:"🏠", sort:1 },
|
|
{ slug:"alimentation", label:"Courses", emoji:"🛒", sort:2 },
|
|
{ slug:"transport", label:"Transport", emoji:"🚗", sort:3 },
|
|
{ slug:"abonnements", label:"Abonnements", emoji:"📱", sort:4 },
|
|
{ slug:"sante", label:"Santé", emoji:"💊", sort:5 },
|
|
{ slug:"loisirs", label:"Loisirs", emoji:"🎉", sort:6 },
|
|
{ slug:"enfants", label:"Enfants", emoji:"👶", sort:7 },
|
|
{ slug:"autre", label:"Autre", emoji:"📦", sort:8 },
|
|
];
|
|
for (const c of defaults) {
|
|
await pool.query("INSERT INTO categories (slug,label,emoji,active,sort) VALUES ($1,$2,$3,1,$4)", [c.slug, c.label, c.emoji, c.sort]);
|
|
}
|
|
}
|
|
|
|
await pool.query(
|
|
"INSERT INTO categories (slug,label,emoji,active,sort) VALUES ('epargne','Épargne','💰',1,9) ON CONFLICT DO NOTHING"
|
|
);
|
|
|
|
// Seed settings
|
|
const defs = { nameA:"", nameB:"", incomeA:"", incomeB:"", sliderVal:"63", sliderLocked:"false", setupDone:"false" };
|
|
for (const [k, v] of Object.entries(defs)) {
|
|
await pool.query("INSERT INTO settings (key,value) VALUES ($1,$2) ON CONFLICT (key) DO NOTHING", [k, v]);
|
|
}
|
|
}
|
|
|
|
// ── Middleware auth ───────────────────────────────────────────
|
|
function requireAuth(req, res, next) {
|
|
const h = req.headers.authorization;
|
|
if (!h || !h.startsWith("Bearer ")) return res.status(401).json({ error: "Non authentifié" });
|
|
try { req.user = jwt.verify(h.slice(7), JWT_SECRET); next(); }
|
|
catch { res.status(401).json({ error: "Token invalide ou expiré" }); }
|
|
}
|
|
|
|
function requireAdmin(req, res, next) {
|
|
requireAuth(req, res, () => {
|
|
if (req.user.username !== ADMIN_USER) return res.status(403).json({ error: "Accès réservé à l'administrateur" });
|
|
next();
|
|
});
|
|
}
|
|
|
|
// ── Auth routes ───────────────────────────────────────────────
|
|
app.post("/api/auth/login", async (req, res) => {
|
|
try {
|
|
const { username, password } = req.body;
|
|
if (!username || !password) return res.status(400).json({ error: "Champs manquants" });
|
|
const { rows } = await pool.query("SELECT * FROM users WHERE username=$1", [username]);
|
|
const user = rows[0];
|
|
if (!user || !bcrypt.compareSync(password, user.password))
|
|
return res.status(401).json({ error: "Identifiants incorrects" });
|
|
const token = jwt.sign({ id: user.id, username: user.username }, JWT_SECRET, { expiresIn: JWT_EXPIRY });
|
|
res.json({ token, username: user.username, isAdmin: user.username === ADMIN_USER });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.get("/api/auth/me", requireAuth, async (req, res) => {
|
|
try {
|
|
const { rows } = await pool.query(
|
|
"SELECT username FROM users WHERE id=$1 AND username=$2",
|
|
[req.user.id, req.user.username]
|
|
);
|
|
if (!rows[0]) return res.status(401).json({ error: "Session expirée" });
|
|
res.json({ username: rows[0].username, isAdmin: rows[0].username === ADMIN_USER });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.post("/api/auth/change-password", requireAuth, async (req, res) => {
|
|
try {
|
|
const { currentPassword, newPassword } = req.body;
|
|
if (!currentPassword || !newPassword) return res.status(400).json({ error: "Champs manquants" });
|
|
if (newPassword.length < 6) return res.status(400).json({ error: "6 caractères minimum" });
|
|
const { rows } = await pool.query("SELECT * FROM users WHERE id=$1", [req.user.id]);
|
|
if (!bcrypt.compareSync(currentPassword, rows[0].password))
|
|
return res.status(401).json({ error: "Mot de passe actuel incorrect" });
|
|
await pool.query("UPDATE users SET password=$1 WHERE id=$2", [bcrypt.hashSync(newPassword, 10), req.user.id]);
|
|
res.json({ ok: true });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
// ── Settings ──────────────────────────────────────────────────
|
|
app.get("/api/settings", requireAuth, async (req, res) => {
|
|
try {
|
|
const { rows } = await pool.query("SELECT key,value FROM settings");
|
|
const obj = {};
|
|
rows.forEach(r => obj[r.key] = r.value);
|
|
res.json(obj);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.post("/api/settings", requireAuth, async (req, res) => {
|
|
try {
|
|
for (const [k, v] of Object.entries(req.body)) {
|
|
await pool.query(
|
|
"INSERT INTO settings (key,value) VALUES ($1,$2) ON CONFLICT (key) DO UPDATE SET value=$2",
|
|
[k, String(v)]
|
|
);
|
|
}
|
|
res.json({ ok: true });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
// ── Categories ────────────────────────────────────────────────
|
|
app.get("/api/categories", requireAuth, async (req, res) => {
|
|
try {
|
|
const { rows } = await pool.query("SELECT * FROM categories ORDER BY sort ASC");
|
|
res.json(rows);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.post("/api/categories", requireAdmin, async (req, res) => {
|
|
try {
|
|
const { label, emoji } = req.body;
|
|
if (!label) return res.status(400).json({ error: "label requis" });
|
|
const slug = label.toLowerCase().normalize("NFD").replace(/[\u0300-\u036f]/g,"").replace(/[^a-z0-9]+/g,"-").replace(/^-|-$/g,"") + "-" + Date.now();
|
|
const { rows: [{ m }] } = await pool.query("SELECT MAX(sort) AS m FROM categories");
|
|
const maxSort = m || 0;
|
|
const { rows } = await pool.query(
|
|
"INSERT INTO categories (slug,label,emoji,active,sort) VALUES ($1,$2,$3,1,$4) RETURNING *",
|
|
[slug, label.trim(), emoji || "📦", maxSort + 1]
|
|
);
|
|
res.json(rows[0]);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.put("/api/categories/:id", requireAdmin, async (req, res) => {
|
|
try {
|
|
const { label, emoji, active } = req.body;
|
|
const { rows } = await pool.query("SELECT * FROM categories WHERE id=$1", [req.params.id]);
|
|
if (!rows[0]) return res.status(404).json({ error: "Catégorie introuvable" });
|
|
const cat = rows[0];
|
|
const { rows: updated } = await pool.query(
|
|
"UPDATE categories SET label=$1,emoji=$2,active=$3 WHERE id=$4 RETURNING *",
|
|
[label ?? cat.label, emoji ?? cat.emoji, active !== undefined ? (active ? 1 : 0) : cat.active, req.params.id]
|
|
);
|
|
res.json(updated[0]);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.delete("/api/categories/:id", requireAdmin, async (req, res) => {
|
|
try {
|
|
const { rows } = await pool.query("SELECT * FROM categories WHERE id=$1", [req.params.id]);
|
|
if (!rows[0]) return res.status(404).json({ error: "Catégorie introuvable" });
|
|
await pool.query("UPDATE expenses SET cat='autre' WHERE cat=$1", [rows[0].slug]);
|
|
await pool.query("DELETE FROM categories WHERE id=$1", [req.params.id]);
|
|
res.json({ ok: true });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
// ── Expenses ──────────────────────────────────────────────────
|
|
app.get("/api/expenses", requireAuth, async (req, res) => {
|
|
try {
|
|
const budgetId = parseInt(req.query.budget_id || req.headers["x-budget-id"]) || 1;
|
|
const { rows } = await pool.query("SELECT * FROM expenses WHERE budget_id=$1 ORDER BY created_at ASC", [budgetId]);
|
|
res.json(rows);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.post("/api/expenses", requireAuth, async (req, res) => {
|
|
try {
|
|
const { name, amount, cat, freq, month, installments } = req.body;
|
|
if (!name || !amount) return res.status(400).json({ error: "name and amount required" });
|
|
const finalFreq = month ? "ponctuel" : (freq || "mensuel");
|
|
const budgetId = parseInt(req.headers["x-budget-id"]) || 1;
|
|
const { rows } = await pool.query(
|
|
"INSERT INTO expenses (name,amount,cat,freq,month,budget_id,installments) VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING *",
|
|
[name, amount, cat || "autre", finalFreq, month || null, budgetId, installments || null]
|
|
);
|
|
res.json(rows[0]);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.put("/api/expenses/:id", requireAuth, async (req, res) => {
|
|
try {
|
|
const { name, amount, cat, freq, month, installments, installments_paid } = req.body;
|
|
if (!name || !amount) return res.status(400).json({ error: "name and amount required" });
|
|
const { rows } = await pool.query("SELECT * FROM expenses WHERE id=$1", [req.params.id]);
|
|
if (!rows[0]) return res.status(404).json({ error: "Dépense introuvable" });
|
|
const existing = rows[0];
|
|
const finalFreq = month ? "ponctuel" : (freq || existing.freq);
|
|
const { rows: updated } = await pool.query(
|
|
"UPDATE expenses SET name=$1,amount=$2,cat=$3,freq=$4,month=$5,installments=$6,installments_paid=$7 WHERE id=$8 RETURNING *",
|
|
[name, amount, cat || existing.cat, finalFreq, month || null,
|
|
installments ?? existing.installments,
|
|
installments_paid !== undefined ? installments_paid : existing.installments_paid,
|
|
req.params.id]
|
|
);
|
|
res.json(updated[0]);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.post("/api/expenses/:id/pay-installment", requireAuth, async (req, res) => {
|
|
try {
|
|
const { rows: [exp] } = await pool.query("SELECT * FROM expenses WHERE id=$1", [req.params.id]);
|
|
if (!exp) return res.status(404).json({ error: "Dépense introuvable" });
|
|
if (!exp.installments || exp.installments_paid >= exp.installments)
|
|
return res.status(400).json({ error: "Toutes les mensualités sont déjà payées" });
|
|
const { rows: [updated] } = await pool.query(
|
|
"UPDATE expenses SET installments_paid = installments_paid + 1 WHERE id=$1 RETURNING *",
|
|
[req.params.id]
|
|
);
|
|
res.json(updated);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.delete("/api/expenses/:id", requireAuth, async (req, res) => {
|
|
try {
|
|
await pool.query("DELETE FROM expenses WHERE id=$1", [req.params.id]);
|
|
res.json({ ok: true });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
// ── Savings ───────────────────────────────────────────────────
|
|
app.get("/api/savings", requireAuth, async (req, res) => {
|
|
const budgetId = req.query.budget_id || req.headers["x-budget-id"] || 1;
|
|
const { rows } = await pool.query(
|
|
"SELECT * FROM savings WHERE budget_id=$1 ORDER BY closed, created_at DESC",
|
|
[budgetId]
|
|
);
|
|
res.json(rows);
|
|
});
|
|
|
|
app.post("/api/savings", requireAuth, async (req, res) => {
|
|
const budgetId = req.headers["x-budget-id"] || 1;
|
|
const { name, emoji = "💰", monthly_amount = 0, target } = req.body;
|
|
if (!name) return res.status(400).json({ error: "Nom requis" });
|
|
|
|
// Create linked expense if monthly_amount > 0
|
|
let expenseId = null;
|
|
if (parseFloat(monthly_amount) > 0) {
|
|
const { rows: [exp] } = await pool.query(
|
|
"INSERT INTO expenses (name,amount,cat,freq,budget_id) VALUES ($1,$2,'epargne','mensuel',$3) RETURNING *",
|
|
[emoji + " " + name, parseFloat(monthly_amount), budgetId]
|
|
);
|
|
expenseId = exp.id;
|
|
}
|
|
|
|
const { rows: [saving] } = await pool.query(
|
|
"INSERT INTO savings (budget_id,name,emoji,monthly_amount,balance,target,expense_id) VALUES ($1,$2,$3,$4,0,$5,$6) RETURNING *",
|
|
[budgetId, name, emoji, parseFloat(monthly_amount) || 0, target ? parseFloat(target) : null, expenseId]
|
|
);
|
|
res.json(saving);
|
|
});
|
|
|
|
app.put("/api/savings/:id", requireAuth, async (req, res) => {
|
|
const { name, emoji, monthly_amount, target } = req.body;
|
|
const { rows: [saving] } = await pool.query("SELECT * FROM savings WHERE id=$1", [req.params.id]);
|
|
if (!saving) return res.status(404).json({ error: "Introuvable" });
|
|
|
|
// Update linked expense amount if changed
|
|
if (saving.expense_id && monthly_amount !== undefined) {
|
|
if (parseFloat(monthly_amount) > 0) {
|
|
await pool.query(
|
|
"UPDATE expenses SET amount=$1, name=$2 WHERE id=$3",
|
|
[parseFloat(monthly_amount), (emoji || saving.emoji) + " " + (name || saving.name), saving.expense_id]
|
|
);
|
|
} else {
|
|
await pool.query("DELETE FROM expenses WHERE id=$1", [saving.expense_id]);
|
|
await pool.query("UPDATE savings SET expense_id=NULL WHERE id=$1", [saving.id]);
|
|
}
|
|
} else if (!saving.expense_id && monthly_amount && parseFloat(monthly_amount) > 0) {
|
|
// Create expense if now has monthly amount
|
|
const { rows: [exp] } = await pool.query(
|
|
"INSERT INTO expenses (name,amount,cat,freq,budget_id) VALUES ($1,$2,'epargne','mensuel',$3) RETURNING *",
|
|
[(emoji || saving.emoji) + " " + (name || saving.name), parseFloat(monthly_amount), saving.budget_id]
|
|
);
|
|
await pool.query("UPDATE savings SET expense_id=$1 WHERE id=$2", [exp.id, saving.id]);
|
|
}
|
|
|
|
const { rows: [updated] } = await pool.query(
|
|
`UPDATE savings SET
|
|
name = COALESCE($1, name),
|
|
emoji = COALESCE($2, emoji),
|
|
monthly_amount = COALESCE($3, monthly_amount),
|
|
target = $4
|
|
WHERE id=$5 RETURNING *`,
|
|
[name, emoji, monthly_amount !== undefined ? parseFloat(monthly_amount) : null, target ? parseFloat(target) : null, req.params.id]
|
|
);
|
|
res.json(updated);
|
|
});
|
|
|
|
app.post("/api/savings/:id/transaction", requireAuth, async (req, res) => {
|
|
const { amount, note = "" } = req.body;
|
|
if (!amount || isNaN(parseFloat(amount))) return res.status(400).json({ error: "Montant invalide" });
|
|
|
|
await pool.query(
|
|
"INSERT INTO savings_transactions (saving_id,amount,note) VALUES ($1,$2,$3)",
|
|
[req.params.id, parseFloat(amount), note]
|
|
);
|
|
const { rows: [saving] } = await pool.query(
|
|
"UPDATE savings SET balance = balance + $1 WHERE id=$2 RETURNING *",
|
|
[parseFloat(amount), req.params.id]
|
|
);
|
|
res.json(saving);
|
|
});
|
|
|
|
app.get("/api/savings/:id/transactions", requireAuth, async (req, res) => {
|
|
const { rows } = await pool.query(
|
|
"SELECT * FROM savings_transactions WHERE saving_id=$1 ORDER BY created_at DESC LIMIT 20",
|
|
[req.params.id]
|
|
);
|
|
res.json(rows);
|
|
});
|
|
|
|
app.post("/api/savings/:id/close", requireAuth, async (req, res) => {
|
|
const { rows: [saving] } = await pool.query("SELECT * FROM savings WHERE id=$1", [req.params.id]);
|
|
if (!saving) return res.status(404).json({ error: "Introuvable" });
|
|
|
|
// Delete linked expense
|
|
if (saving.expense_id) {
|
|
await pool.query("DELETE FROM expenses WHERE id=$1", [saving.expense_id]);
|
|
}
|
|
const { rows: [updated] } = await pool.query(
|
|
"UPDATE savings SET closed=TRUE, expense_id=NULL WHERE id=$1 RETURNING *",
|
|
[req.params.id]
|
|
);
|
|
res.json(updated);
|
|
});
|
|
|
|
app.delete("/api/savings/:id", requireAuth, async (req, res) => {
|
|
const { rows: [saving] } = await pool.query("SELECT * FROM savings WHERE id=$1", [req.params.id]);
|
|
if (!saving) return res.status(404).json({ error: "Introuvable" });
|
|
if (saving.expense_id) {
|
|
await pool.query("DELETE FROM expenses WHERE id=$1", [saving.expense_id]);
|
|
}
|
|
await pool.query("DELETE FROM savings WHERE id=$1", [req.params.id]);
|
|
res.json({ ok: true });
|
|
});
|
|
|
|
// ── Admin ─────────────────────────────────────────────────────
|
|
app.get("/api/admin/users", requireAdmin, async (req, res) => {
|
|
try {
|
|
const { rows } = await pool.query("SELECT id,username FROM users");
|
|
res.json(rows);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
// ── Pointage ──────────────────────────────────────────────────
|
|
const POINTAGE_START_DAY = 26;
|
|
|
|
function currentMonth() {
|
|
const d = new Date();
|
|
if (d.getDate() < POINTAGE_START_DAY) {
|
|
const prev = new Date(d.getFullYear(), d.getMonth() - 1, 1);
|
|
return prev.getFullYear() + '-' + String(prev.getMonth() + 1).padStart(2, '0');
|
|
}
|
|
return d.getFullYear() + '-' + String(d.getMonth() + 1).padStart(2, '0');
|
|
}
|
|
|
|
function prevMonth(m) {
|
|
const [y, mo] = m.split('-').map(Number);
|
|
const d = new Date(y, mo - 2, 1);
|
|
return d.getFullYear() + '-' + String(d.getMonth() + 1).padStart(2, '0');
|
|
}
|
|
|
|
async function generatePointageForMonth(month, budgetId = 1) {
|
|
const { rows: recurring } = await pool.query(
|
|
"SELECT * FROM expenses WHERE freq != 'ponctuel' AND budget_id=$1",
|
|
[budgetId]
|
|
);
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query("BEGIN");
|
|
for (const e of recurring) {
|
|
await client.query(
|
|
"INSERT INTO pointage (expense_id,month,pointed,amount,name,budget_id) VALUES ($1,$2,0,$3,$4,$5) ON CONFLICT (expense_id,month) DO NOTHING",
|
|
[e.id, month, e.amount, e.name, budgetId]
|
|
);
|
|
}
|
|
await client.query("COMMIT");
|
|
} catch (err) {
|
|
await client.query("ROLLBACK");
|
|
throw err;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
app.get("/api/pointage", requireAuth, async (req, res) => {
|
|
try {
|
|
const month = req.query.month || currentMonth();
|
|
const budgetId = parseInt(req.headers["x-budget-id"]) || 1;
|
|
|
|
await generatePointageForMonth(month, budgetId);
|
|
const { rows } = await pool.query(`
|
|
SELECT p.*,e.cat,e.freq FROM pointage p
|
|
LEFT JOIN expenses e ON p.expense_id=e.id
|
|
WHERE p.month=$1 AND p.budget_id=$2 ORDER BY p.pointed ASC,p.name ASC
|
|
`, [month, budgetId]);
|
|
|
|
const prev = prevMonth(month);
|
|
await generatePointageForMonth(prev, budgetId);
|
|
const { rows: unpointedPrev } = await pool.query(`
|
|
SELECT p.*,e.cat,e.freq FROM pointage p
|
|
LEFT JOIN expenses e ON p.expense_id=e.id
|
|
WHERE p.month=$1 AND p.pointed=0 AND p.budget_id=$2 ORDER BY p.name ASC
|
|
`, [prev, budgetId]);
|
|
|
|
res.json({ month, rows, unpointedPrev, prevMonth: prev });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.put("/api/pointage/:id", requireAuth, async (req, res) => {
|
|
try {
|
|
const { pointed } = req.body;
|
|
await pool.query(
|
|
"UPDATE pointage SET pointed=$1,pointed_at=$2 WHERE id=$3",
|
|
[pointed ? 1 : 0, pointed ? new Date().toISOString() : null, req.params.id]
|
|
);
|
|
const { rows } = await pool.query("SELECT * FROM pointage WHERE id=$1", [req.params.id]);
|
|
res.json(rows[0]);
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
app.post("/api/pointage/generate", requireAuth, async (req, res) => {
|
|
try {
|
|
const month = req.body.month || currentMonth();
|
|
const budgetId = parseInt(req.headers["x-budget-id"]) || 1;
|
|
await generatePointageForMonth(month, budgetId);
|
|
res.json({ ok: true, month });
|
|
} catch (err) { res.status(500).json({ error: err.message }); }
|
|
});
|
|
|
|
// ── Serve frontend ────────────────────────────────────────────
|
|
app.use(express.static(path.join(__dirname, "public")));
|
|
app.get("*", (req, res) => res.sendFile(path.join(__dirname, "public", "index.html")));
|
|
|
|
// ── Start ─────────────────────────────────────────────────────
|
|
async function start() {
|
|
await waitForDb();
|
|
await initDb();
|
|
app.listen(3000, () => console.log("Budget Commun running on :3000"));
|
|
}
|
|
|
|
start().catch(err => {
|
|
console.error("Erreur au démarrage :", err);
|
|
process.exit(1);
|
|
});
|