Locmouth / api /server.js
AByGCreation's picture
feat: implement user authentication and profile management
9daebd1
Raw History Blame Contribute Delete
30 kB
const express = require("express");
const Database = require("better-sqlite3");
const { installAuth } = require("./auth");
const path = require("path");
const fs = require("fs");
const multer = require("multer");
const app = express();
const PORT = process.env.PORT || 7860;
const APP_ROOT = path.resolve(__dirname, "..");
const SQLITE_PATH =
process.env.SQLITE_PATH || path.join(APP_ROOT, "data", "immobilier.sqlite");
const UPLOAD_DIR =
process.env.UPLOAD_DIR || path.join(APP_ROOT, "resources", "uploads");
const PUBLIC_UPLOAD_DIR = "resources/uploads";
const MAX_FILE_COUNT = process.env.MAX_FILE_COUNT || 20;
if (!fs.existsSync(UPLOAD_DIR)) {
fs.mkdirSync(UPLOAD_DIR, { recursive: true });
}
const storage = multer.diskStorage({
destination: (_req, _file, cb) => cb(null, UPLOAD_DIR),
filename: (_req, file, cb) => {
const safeBase =
path
.basename(file.originalname, path.extname(file.originalname))
.replace(/[^a-z0-9_-]+/gi, "_")
.replace(/^_+|_+$/g, "")
.slice(0, 40) || "image";
const uniqueSuffix = `${Date.now()}-${Math.round(Math.random() * 1e9)}`;
cb(
null,
`${safeBase}-${uniqueSuffix}${path.extname(file.originalname).toLowerCase()}`,
);
},
});
const upload = multer({
storage,
limits: {
fileSize: 10 * 1024 * 1024,
files: MAX_FILE_COUNT,
},
fileFilter: (_req, file, cb) => {
cb(null, file.mimetype.startsWith("image/"));
},
});
// ─── Middleware ───────────────────────────────────────────────────────────────
app.use(express.json());
// Only application assets are public; never expose the database or server sources.
for (const asset of ["index.html", "index.js", "style.css"]) {
app.get(asset === "index.html" ? ["/", "/index.html"] : `/${asset}`, (_req, res) => {
res.sendFile(path.join(APP_ROOT, asset));
});
}
// ─── Base de donnΓ©es SQLite ───────────────────────────────────────────────────
fs.mkdirSync(path.dirname(SQLITE_PATH), { recursive: true });
const db = new Database(SQLITE_PATH);
db.pragma("foreign_keys = ON");
// ===== CONFIGURATEUR DE COΓ›T DE CHAUFFAGE =====
/**
* Estime le coΓ»t mensuel moyen de chauffage en fonction :
* - Type d'Γ©nergie (gaz, Γ©lectricitΓ©, bois, fioul)
* - Superficie (mΒ²)
* - Classe DPE (A Γ  G)
*
* @param {string} typeEnergie - 'gaz', 'electricite_direct', 'electricite_pac', 'bois', 'fioul'
* @param {number} superficie - Surface en mΒ²
* @param {string} classeDpe - 'A', 'B', 'C', 'D', 'E', 'F', 'G'
* @returns {object} { coutMensuel: number, coutAnnuel: number, details: object }
*/
function estimerCoutChauffage(typeEnergie, superficie, classeDpe) {
// DonnΓ©es de consommation par classe DPE (kWhEP/mΒ²/an)
const dpeConsumption = {
A: 70,
B: 110,
C: 180,
D: 250,
E: 330,
F: 420,
G: 450 // Seuil minimal pour la classe G
};
// Profils Γ©nergΓ©tiques (rendement et prix en €/kWhEF)
// Les prix sont estimΓ©s pour 2026 et peuvent Γͺtre ajustΓ©s
const energyProfiles = {
gaz: { rendement: 0.92, prixKwh: 0.14 }, // Chaudière gaz condensation
electricite_direct: { rendement: 1.0, prixKwh: 0.22 }, // Radiateurs Γ©lectriques
electricite_pac: { rendement: 3.0, prixKwh: 0.22 }, // Pompe Γ  chaleur (COP = 3)
bois: { rendement: 0.8, prixKwh: 0.08 }, // PoΓͺle ou chaudiΓ¨re Γ  granulΓ©s
fioul: { rendement: 0.85, prixKwh: 0.12 } // Chaudière fioul
};
// VΓ©rification des entrΓ©es
if (!dpeConsumption.hasOwnProperty(classeDpe)) {
throw new Error("Classe DPE invalide. Utilisez 'A', 'B', 'C', 'D', 'E', 'F' ou 'G'.");
}
if (!energyProfiles.hasOwnProperty(typeEnergie)) {
throw new Error("Type d'Γ©nergie invalide. Utilisez 'gaz', 'electricite_direct', 'electricite_pac', 'bois' ou 'fioul'.");
}
if (superficie <= 0) {
throw new Error("La superficie doit Γͺtre positive.");
}
// Calcul de la consommation annuelle
const kwhEpParM2 = dpeConsumption[classeDpe];
const kwhEpTotal = kwhEpParM2 * superficie;
// Conversion en Γ©nergie finale (kWhEF) et coΓ»t
const profile = energyProfiles[typeEnergie];
const kwhEfTotal = kwhEpTotal / profile.rendement;
const coutAnnuel = kwhEfTotal * profile.prixKwh;
const coutMensuel = coutAnnuel / 12;
// DΓ©tails pour l'utilisateur
const details = {
consommationEpAnnuelle: kwhEpTotal,
consommationEfAnnuelle: parseFloat(kwhEfTotal.toFixed(2)),
rendement: profile.rendement,
prixKwhEf: profile.prixKwh,
typeEnergie: typeEnergie,
classeDpe: classeDpe,
superficie: superficie
};
return {
coutMensuel: parseFloat(coutMensuel.toFixed(2)),
coutAnnuel: parseFloat(coutAnnuel.toFixed(2)),
details: details
};
}
// ===== FIN DU CONFIGURATEUR =====
function parseJsonArray(value) {
if (Array.isArray(value)) return value;
if (!value) return [];
if (typeof value !== "string") return [];
try {
const parsed = JSON.parse(value);
return Array.isArray(parsed) ? parsed : [];
} catch {
return [];
}
}
function parseEnergyItems(value) {
if (Array.isArray(value)) {
return value
.map((entry) => ({
type: String(entry?.type || "").trim(),
amount: Number(entry?.amount) || 0,
}))
.filter((entry) => entry.amount > 0);
}
if (!value) return [];
if (typeof value !== "string") return [];
try {
const parsed = JSON.parse(value);
return parseEnergyItems(parsed);
} catch {
return [];
}
}
function legacyEnergyItemsFromFiche(fiche) {
const amount =
fiche.facture_energie_mensuelle != null
? Number(fiche.facture_energie_mensuelle)
: Number(String(fiche.facture_energie_exemple).match(/\d+/)?.[0] || 0);
if (!(amount > 0)) return [];
const type = String(fiche.facture_energie_type || "").trim();
return [{ type, amount }];
}
function hydrateFiche(fiche, imageRows = []) {
if (!fiche) return fiche;
const energyItems = parseEnergyItems(fiche.facture_energie_items);
const normalizedEnergyItems = energyItems.length
? energyItems
: legacyEnergyItemsFromFiche(fiche);
return {
...fiche,
facture_energie_items: normalizedEnergyItems,
images: imageRows.map((row) => row.path),
image_ids: imageRows.map((row) => row.id),
};
}
function parseRequestPayload(req) {
if (typeof req.body?.payload === "string") {
const payload = JSON.parse(req.body.payload || "{}");
return {
payload,
existingImages: parseJsonArray(req.body.existing_images),
removedImages: parseJsonArray(req.body.removed_images),
};
}
return {
payload: req.body || {},
existingImages: parseJsonArray(req.body?.existing_images),
removedImages: parseJsonArray(req.body?.removed_images),
};
}
function toPublicUploadPath(filename) {
return `${PUBLIC_UPLOAD_DIR}/${filename}`;
}
function resolveStoredImagePath(relativePath) {
const normalizedPath = String(relativePath || "").replace(/^\/+/, "");
const uploadPrefix = `${PUBLIC_UPLOAD_DIR}/`;
if (normalizedPath.startsWith(uploadPrefix)) {
const filename = normalizedPath.slice(uploadPrefix.length);
if (filename && filename === path.basename(filename)) return path.join(UPLOAD_DIR, filename);
}
return null;
}
function mergeImages(existingImages, files = []) {
return [
...parseJsonArray(existingImages),
...files.map((file) => toPublicUploadPath(file.filename)),
];
}
function normalizeFicheStatus(status) {
const allowed = new Set([
"a_traiter",
"en_cours",
"dossier_envoye",
"visite_prevue",
"valide",
"archive",
"abandonne",
]);
return allowed.has(status) ? status : "a_traiter";
}
function getImagesByFicheId(ficheId) {
return db
.prepare(
`
SELECT id, path, sort_order
FROM fiche_images
WHERE fiche_id = ?
ORDER BY sort_order ASC, created_at ASC, id ASC
`,
)
.all(ficheId);
}
function replaceFicheImages(ficheId, imagePaths) {
const normalized = Array.from(
new Set(parseJsonArray(imagePaths).filter(Boolean)),
);
const deleteStmt = db.prepare("DELETE FROM fiche_images WHERE fiche_id = ?");
const insertStmt = db.prepare(
"INSERT INTO fiche_images (fiche_id, path, sort_order) VALUES (?, ?, ?)",
);
const tx = db.transaction(() => {
deleteStmt.run(ficheId);
normalized.forEach((pathValue, index) => {
insertStmt.run(ficheId, pathValue, index);
});
});
tx();
}
function removeStoredImages(imageList) {
parseJsonArray(imageList).forEach((relativePath) => {
const absolutePath = resolveStoredImagePath(relativePath);
const stillUsed = db.prepare("SELECT 1 FROM fiche_images WHERE path = ? LIMIT 1").get(relativePath);
if (absolutePath && !stillUsed && fs.existsSync(absolutePath)) {
fs.unlinkSync(absolutePath);
}
});
}
db.exec(`
CREATE TABLE IF NOT EXISTS fiches (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created_at TEXT DEFAULT (datetime('now', 'localtime')),
-- IdentitΓ© du bien
adresse TEXT,
date_visite TEXT,
heure_visite TEXT,
contact_nom TEXT,
contact_tel TEXT,
-- Recherche, type & surface
type_search TEXT DEFAULT '', -- location / achat
type_logement TEXT, -- studio / t1t2 / t3plus
surface REAL,
etage TEXT,
-- Bail
type_bail TEXT, -- meuble / vide
-- Achat
taxe_fonciere REAL,
-- Financier
loyer_hc REAL,
charges REAL,
charges_eau_froide INTEGER DEFAULT 0,
charges_eau_chaude INTEGER DEFAULT 0,
charges_chauffage INTEGER DEFAULT 0,
charges_elec INTEGER DEFAULT 0,
loyer_cc REAL,
depot_garantie REAL,
frais_agence REAL,
disponibilite TEXT,
-- Chauffage
chauffage_type TEXT, -- electrique / gaz / fioul
-- Γ‰nergie & Γ©quipements
facture_energie_type TEXT,
facture_energie_mensuelle REAL,
facture_energie_items TEXT DEFAULT '[]',
facture_energie_exemple TEXT,
fibre_optique INTEGER DEFAULT 0, -- 0/1
-- Stationnement
parking_garage INTEGER DEFAULT 0,
parking_reserve INTEGER DEFAULT 0,
parking_rue INTEGER DEFAULT 0,
-- Stockage
cave INTEGER DEFAULT 0,
grenier INTEGER DEFAULT 0,
-- Notes (1-5)
note_etat_general INTEGER,
note_luminosite INTEGER,
note_isolation INTEGER,
note_cuisine INTEGER,
note_rangements INTEGER,
note_emplacement INTEGER,
note_flexibilite INTEGER,
-- Commentaires par critère
comment_etat_general TEXT,
comment_luminosite TEXT,
comment_isolation TEXT,
comment_cuisine TEXT,
comment_rangements TEXT,
comment_emplacement TEXT,
comment_flexibilite TEXT,
-- Synthèse
points_positifs TEXT,
points_negatifs TEXT,
statut TEXT DEFAULT 'a_traiter', -- a_traiter / en_cours / dossier_envoye / visite_prevue / valide / archive / abandonne
priorite TEXT, -- priorite_absolue / plan_b / non_classe
images TEXT DEFAULT '[]'
)
`);
function ensureColumn(table, column, definition) {
const columns = db.prepare(`PRAGMA table_info(${table})`).all();
if (!columns.some((entry) => entry.name === column)) {
db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
}
}
// Legacy JSON column kept for backward compatibility / migration.
ensureColumn("fiches", "images", "TEXT DEFAULT '[]'");
ensureColumn("fiches", "type_search", "TEXT DEFAULT ''");
ensureColumn("fiches", "taxe_fonciere", "REAL");
ensureColumn("fiches", "statut", "TEXT DEFAULT 'a_traiter'");
ensureColumn("fiches", "facture_energie_type", "TEXT");
ensureColumn("fiches", "facture_energie_mensuelle", "REAL");
ensureColumn("fiches", "facture_energie_items", "TEXT DEFAULT '[]'");
db.prepare(
"UPDATE fiches SET statut = 'a_traiter' WHERE statut IS NULL OR statut = ''",
).run();
db.exec(`
CREATE TABLE IF NOT EXISTS fiche_images (
id INTEGER PRIMARY KEY AUTOINCREMENT,
fiche_id INTEGER NOT NULL,
path TEXT NOT NULL,
sort_order INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now', 'localtime')),
FOREIGN KEY (fiche_id) REFERENCES fiches(id) ON DELETE CASCADE
)
`);
ensureColumn("fiche_images", "sort_order", "INTEGER DEFAULT 0");
db.exec(`
CREATE UNIQUE INDEX IF NOT EXISTS idx_fiche_images_fiche_path
ON fiche_images (fiche_id, path)
`);
db.exec(`
CREATE TABLE IF NOT EXISTS immo_sites (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nom TEXT NOT NULL,
url TEXT NOT NULL UNIQUE,
favicon_url TEXT GENERATED ALWAYS AS (
'https://www.google.com/s2/favicons?domain=' || url || '&sz=32'
) VIRTUAL,
categorie TEXT CHECK(categorie IN (
'gΓ©nΓ©raliste','agence','particulier',
'meublΓ©','social','neuf','location courte durΓ©e'
)),
sort_order INTEGER DEFAULT 0,
actif INTEGER DEFAULT 1,
notes TEXT,
created_at TEXT DEFAULT (datetime('now','localtime'))
)
`);
function normalizeListingSites(rawSites) {
if (!Array.isArray(rawSites)) return [];
const ALLOWED_CATEGORIES = new Set([
"gΓ©nΓ©raliste",
"agence",
"particulier",
"meublΓ©",
"social",
"neuf",
"location courte durΓ©e",
]);
const seen = new Set();
return rawSites
.map((site, index) => {
const label = String(site?.label || "").trim();
const url = String(site?.url || "").trim();
const rawCat = String(site?.categorie || "").trim();
const categorie = ALLOWED_CATEGORIES.has(rawCat) ? rawCat : "gΓ©nΓ©raliste";
const sort_order = Number.isFinite(Number(site?.sort_order))
? Number(site.sort_order)
: index;
const enabled = site?.enabled === false || site?.enabled === 0 ? 0 : 1;
const normUrlKey = url
.replace(/^https?:\/\//i, "")
.replace(/^www\./i, "")
.toLowerCase();
return {
id: site?.id ? Number(site.id) : null,
label,
url,
categorie,
sort_order,
enabled,
_normUrlKey: normUrlKey,
};
})
.filter((site) => site.label && site.url)
.filter((site) => {
if (seen.has(site._normUrlKey)) return false;
seen.add(site._normUrlKey);
delete site._normUrlKey;
return true;
});
}
function buildFaviconUrl(url) {
const value = String(url || "").trim();
if (!value) return "";
return `https://www.google.com/s2/favicons?domain=${value}&sz=32`;
}
function migrateLegacyImagesToTable() {
const legacyRows = db
.prepare(
`
SELECT id, images
FROM fiches
WHERE images IS NOT NULL
AND images != ''
AND images != '[]'
`,
)
.all();
const countStmt = db.prepare(
"SELECT COUNT(*) AS count FROM fiche_images WHERE fiche_id = ?",
);
const insertStmt = db.prepare(
"INSERT OR IGNORE INTO fiche_images (fiche_id, path) VALUES (?, ?)",
);
const tx = db.transaction(() => {
legacyRows.forEach((row) => {
const existingCount = countStmt.get(row.id)?.count || 0;
if (existingCount > 0) return;
parseJsonArray(row.images).forEach((pathValue) => {
if (pathValue) insertStmt.run(row.id, pathValue);
});
});
});
tx();
}
migrateLegacyImagesToTable();
function migrateImageSortOrder() {
const rows = db
.prepare(
`
SELECT id, fiche_id, sort_order, created_at
FROM fiche_images
ORDER BY fiche_id ASC, created_at ASC, id ASC
`,
)
.all();
if (!rows.length) return;
if (rows.some((row) => Number(row.sort_order) > 0)) return;
const updateStmt = db.prepare(
"UPDATE fiche_images SET sort_order = ? WHERE id = ?",
);
const tx = db.transaction(() => {
let currentFicheId = null;
let position = 0;
rows.forEach((row) => {
if (row.fiche_id !== currentFicheId) {
currentFicheId = row.fiche_id;
position = 0;
}
updateStmt.run(position, row.id);
position += 1;
});
});
tx();
}
migrateImageSortOrder();
const { requireAuth } = installAuth(app, db);
app.get(`/${PUBLIC_UPLOAD_DIR}/:filename`, requireAuth, (req, res) => {
res.set("Cache-Control", "private, no-store");
res.set("Content-Security-Policy", "default-src 'none'; sandbox");
res.set("X-Content-Type-Options", "nosniff");
const imagePath = toPublicUploadPath(req.params.filename);
const owned = db.prepare(`SELECT 1 FROM fiche_images JOIN fiches ON fiches.id = fiche_images.fiche_id
WHERE fiches.user_id = ? AND fiche_images.path = ?`).get(req.user.id, imagePath);
const storedPath = resolveStoredImagePath(imagePath);
if (!owned || !storedPath) return res.status(404).json({ error: "Image introuvable" });
res.sendFile(storedPath);
});
function getListingSites(userId) {
return db
.prepare(
`
SELECT id, nom, url, categorie, sort_order, actif, notes, created_at
FROM immo_sites
WHERE user_id = ?
ORDER BY sort_order ASC, id ASC
`,
)
.all(userId)
.map((site) => ({
...site,
enabled: site.actif,
favicon_url: buildFaviconUrl(site.url),
}));
}
app.get("/api/listing-sites", (req, res) => {
res.json(getListingSites(req.user.id));
});
app.put("/api/listing-sites", (req, res) => {
const sites = normalizeListingSites(req.body?.sites);
if (!Array.isArray(req.body?.sites)) return res.status(400).json({ error: "Liste de sites invalide" });
const deleteStmt = db.prepare("DELETE FROM immo_sites WHERE user_id = ?");
const insertStmt = db.prepare(
`
INSERT INTO immo_sites (user_id, nom, url, categorie, sort_order, actif, notes)
VALUES (@user_id, @nom, @url, @categorie, @sort_order, @actif, @notes)
`,
);
const tx = db.transaction(() => {
deleteStmt.run(req.user.id);
sites.forEach((site, index) => {
insertStmt.run({
user_id: req.user.id,
nom: site.label,
url: site.url,
categorie: site.categorie,
sort_order: site.sort_order ?? index,
actif: site.enabled,
notes: site.notes || null,
});
});
});
tx();
res.json({
message: "Sites d'annonces mis Γ  jour",
sites: getListingSites(req.user.id),
});
});
// ===== NOUVELLE ROUTE : ESTIMER COΓ›T DE CHAUFFAGE =====
/**
* Route POST /api/estimer-cout-chauffage
* Calcule le coΓ»t mensuel et annuel de chauffage
*/
app.post("/api/estimer-cout-chauffage", (req, res) => {
try {
const { typeEnergie, superficie, classeDpe } = req.body;
// Validation des paramètres
if (!typeEnergie || !superficie || !classeDpe) {
return res.status(400).json({
error: "Paramètres manquants. Veuillez fournir typeEnergie, superficie et classeDpe."
});
}
// Appel du configurateur
const result = estimerCoutChauffage(typeEnergie, superficie, classeDpe);
res.json({
success: true,
coutMensuel: result.coutMensuel,
coutAnnuel: result.coutAnnuel,
details: result.details
});
} catch (error) {
console.error("Erreur dans le calcul du coΓ»t de chauffage:", error);
res.status(400).json({
success: false,
error: error.message
});
}
});
// ===== FIN DE LA NOUVELLE ROUTE =====
// ─── Routes existantes (inchangΓ©es) ──────────────────────────────────────────
// GET β€” Liste toutes les fiches (rΓ©sumΓ©)
app.get("/api/fiches", (req, res) => {
const rawFiches = db
.prepare(
`
SELECT id, adresse, date_visite, type_logement, surface,
loyer_cc, priorite, statut, type_search, type_bail, taxe_fonciere, created_at
FROM fiches
WHERE user_id = ?
ORDER BY created_at DESC
`,
)
.all(req.user.id);
const fiches = rawFiches.map((fiche) =>
hydrateFiche(fiche, getImagesByFicheId(fiche.id)),
);
res.json(fiches);
});
// GET β€” DΓ©tail d'une fiche
app.get("/api/fiches/:id", (req, res) => {
const fiche = db
.prepare("SELECT * FROM fiches WHERE id = ? AND user_id = ?")
.get(req.params.id, req.user.id);
if (!fiche) return res.status(404).json({ error: "Fiche introuvable" });
res.json(hydrateFiche(fiche, getImagesByFicheId(req.params.id)));
});
// POST β€” CrΓ©er une fiche
function ficheData(payload, userId) {
if (!payload || typeof payload !== "object" || Array.isArray(payload) ||
typeof payload.adresse !== "string" || !payload.adresse.trim()) {
const error = new Error("Veuillez renseigner l'adresse du bien.");
error.status = 400;
throw error;
}
const data = {};
for (const column of db.prepare("PRAGMA table_info(fiches)").all()) {
if (!["id", "created_at"].includes(column.name)) data[column.name] = payload[column.name] ?? null;
}
data.user_id = userId;
return data;
}
// Authenticate and check ownership before accepting any files.
app.use("/api/fiches", (req, res, next) => {
res.on("finish", () => {
if (res.statusCode >= 400) removeStoredImages((req.files || []).map((file) => toPublicUploadPath(file.filename)));
});
next();
});
app.param("id", (req, res, next, id) => {
const fiche = db.prepare("SELECT * FROM fiches WHERE id = ? AND user_id = ?").get(id, req.user.id);
if (!fiche) return res.status(404).json({ error: "Fiche introuvable" });
req.fiche = fiche;
next();
});
app.post("/api/fiches", upload.array("images", 10), (req, res) => {
const { payload, existingImages } = parseRequestPayload(req);
const data = ficheData(payload, req.user.id);
if (existingImages.length) return res.status(400).json({ error: "Images existantes non autorisΓ©es pour une nouvelle fiche" });
data.statut = normalizeFicheStatus(data.statut);
const legacyEnergyItems = legacyEnergyItemsFromFiche(data);
data.facture_energie_items = JSON.stringify(
parseEnergyItems(data.facture_energie_items).length
? parseEnergyItems(data.facture_energie_items)
: legacyEnergyItems,
);
// Calcul automatique loyer CC si non fourni
if (!data.loyer_cc && data.loyer_hc && data.charges) {
data.loyer_cc = parseFloat(data.loyer_hc) + parseFloat(data.charges);
}
const mergedImages = mergeImages(existingImages, req.files);
data.images = JSON.stringify(mergedImages);
const stmt = db.prepare(`
INSERT INTO fiches (
user_id,
adresse, date_visite, heure_visite, contact_nom, contact_tel,
type_logement, surface, etage, type_search, type_bail, taxe_fonciere,
loyer_hc, charges,
charges_eau_froide, charges_eau_chaude, charges_chauffage, charges_elec,
loyer_cc, depot_garantie, frais_agence, disponibilite,
chauffage_type, facture_energie_type, facture_energie_mensuelle, facture_energie_items, fibre_optique,
parking_garage, parking_reserve, parking_rue,
cave, grenier,
note_etat_general, note_luminosite, note_isolation,
note_cuisine, note_rangements, note_emplacement, note_flexibilite,
comment_etat_general, comment_luminosite, comment_isolation,
comment_cuisine, comment_rangements, comment_emplacement, comment_flexibilite,
points_positifs, points_negatifs, statut, priorite, images
) VALUES (
@user_id,
@adresse, @date_visite, @heure_visite, @contact_nom, @contact_tel,
@type_logement, @surface, @etage, @type_search, @type_bail, @taxe_fonciere,
@loyer_hc, @charges,
@charges_eau_froide, @charges_eau_chaude, @charges_chauffage, @charges_elec,
@loyer_cc, @depot_garantie, @frais_agence, @disponibilite,
@chauffage_type, @facture_energie_type, @facture_energie_mensuelle, @facture_energie_items, @fibre_optique,
@parking_garage, @parking_reserve, @parking_rue,
@cave, @grenier,
@note_etat_general, @note_luminosite, @note_isolation,
@note_cuisine, @note_rangements, @note_emplacement, @note_flexibilite,
@comment_etat_general, @comment_luminosite, @comment_isolation,
@comment_cuisine, @comment_rangements, @comment_emplacement, @comment_flexibilite,
@points_positifs, @points_negatifs, @statut, @priorite, @images
)
`);
const result = db.transaction(() => {
const inserted = stmt.run(data);
replaceFicheImages(inserted.lastInsertRowid, mergedImages);
return inserted;
})();
res.status(201).json({ id: result.lastInsertRowid, message: "Fiche créée" });
});
// PUT β€” Modifier une fiche
app.put("/api/fiches/:id", upload.array("images", 10), (req, res) => {
const { payload, existingImages } = parseRequestPayload(req);
const data = { ...ficheData(payload, req.user.id), id: req.params.id };
const previousImages = getImagesByFicheId(req.params.id).map((row) => row.path);
if (existingImages.some((image) => !previousImages.includes(image))) {
return res.status(400).json({ error: "Une image n'appartient pas Γ  cette fiche" });
}
data.statut = normalizeFicheStatus(data.statut);
const legacyEnergyItems = legacyEnergyItemsFromFiche(data);
data.facture_energie_items = JSON.stringify(
parseEnergyItems(data.facture_energie_items).length
? parseEnergyItems(data.facture_energie_items)
: legacyEnergyItems,
);
if (!data.loyer_cc && data.loyer_hc && data.charges) {
data.loyer_cc = parseFloat(data.loyer_hc) + parseFloat(data.charges);
}
const mergedImages = mergeImages(existingImages, req.files);
data.images = JSON.stringify(mergedImages);
const stmt = db.prepare(`
UPDATE fiches SET
adresse=@adresse, date_visite=@date_visite, heure_visite=@heure_visite,
contact_nom=@contact_nom, contact_tel=@contact_tel,
type_logement=@type_logement, surface=@surface, etage=@etage,
type_search=@type_search, type_bail=@type_bail, taxe_fonciere=@taxe_fonciere,
loyer_hc=@loyer_hc, charges=@charges,
charges_eau_froide=@charges_eau_froide, charges_eau_chaude=@charges_eau_chaude,
charges_chauffage=@charges_chauffage, charges_elec=@charges_elec,
loyer_cc=@loyer_cc, depot_garantie=@depot_garantie,
frais_agence=@frais_agence, disponibilite=@disponibilite,
chauffage_type=@chauffage_type, facture_energie_type=@facture_energie_type,
facture_energie_mensuelle=@facture_energie_mensuelle,
facture_energie_items=@facture_energie_items,
fibre_optique=@fibre_optique,
parking_garage=@parking_garage, parking_reserve=@parking_reserve,
parking_rue=@parking_rue, cave=@cave, grenier=@grenier,
note_etat_general=@note_etat_general, note_luminosite=@note_luminosite,
note_isolation=@note_isolation, note_cuisine=@note_cuisine,
note_rangements=@note_rangements, note_emplacement=@note_emplacement,
note_flexibilite=@note_flexibilite,
comment_etat_general=@comment_etat_general, comment_luminosite=@comment_luminosite,
comment_isolation=@comment_isolation, comment_cuisine=@comment_cuisine,
comment_rangements=@comment_rangements, comment_emplacement=@comment_emplacement,
comment_flexibilite=@comment_flexibilite,
points_positifs=@points_positifs, points_negatifs=@points_negatifs,
statut=@statut,
priorite=@priorite, images=@images
WHERE id=@id AND user_id=@user_id
`);
db.transaction(() => {
stmt.run(data);
replaceFicheImages(req.params.id, mergedImages);
})();
removeStoredImages(previousImages.filter((image) => !mergedImages.includes(image)));
res.json({ message: "Fiche mise Γ  jour" });
});
// DELETE β€” Supprimer une fiche
app.delete("/api/fiches/:id", (req, res) => {
const fiche = db
.prepare("SELECT id FROM fiches WHERE id = ? AND user_id = ?")
.get(req.params.id, req.user.id);
if (!fiche) return res.status(404).json({ error: "Fiche introuvable" });
const imageRows = getImagesByFicheId(req.params.id);
const imagePaths = imageRows.map((row) => row.path);
db.prepare("DELETE FROM fiches WHERE id = ? AND user_id = ?").run(req.params.id, req.user.id);
removeStoredImages(imagePaths);
res.json({ message: "Fiche supprimΓ©e" });
});
// GET β€” Export CSV
app.get("/api/export/csv", (req, res) => {
const fiches = db
.prepare("SELECT * FROM fiches WHERE user_id = ? ORDER BY created_at DESC")
.all(req.user.id);
const headers = Object.keys(fiches[0] || {}).join(";");
const rows = fiches.map((f) =>
Object.values(f)
.map((v) => (typeof v === "string" && v.includes(";") ? `"${v}"` : v))
.join(";"),
);
res.setHeader("Content-Type", "text/csv; charset=utf-8");
res.setHeader(
"Content-Disposition",
'attachment; filename="fiches_immo.csv"',
);
res.send("\uFEFF" + [headers, ...rows].join("\n")); // BOM pour Excel
});
// Error handling middleware
app.use((err, _req, res, _next) => {
if (err.code === "LIMIT_FILE_SIZE") {
return res
.status(413)
.json({ error: "Fichier trop volumineux (max 10 Mo)" });
}
console.error(err);
const status = err.status || (err instanceof SyntaxError ? 400 : 500);
res.status(status).json({ error: status < 500 ? err.message : "Erreur serveur" });
});
// ─── Start ────────────────────────────────────────────────────────────────────
if (require.main === module) app.listen(PORT, "0.0.0.0", () => {
console.log(`βœ… Serveur lancΓ© β†’ http://0.0.0.0:${PORT}`);
console.log(`πŸ“ Base SQLite : ${SQLITE_PATH}`);
console.log(`πŸ“‚ Dossier uploads : ${UPLOAD_DIR}`);
});
module.exports = { app, db };