897 lines
30 KiB
JavaScript
897 lines
30 KiB
JavaScript
import express from 'express';
|
|
import cors from 'cors';
|
|
import { createClient } from '@libsql/client';
|
|
import multer from 'multer';
|
|
import path from 'path';
|
|
import { fileURLToPath } from 'url';
|
|
import fs from 'fs';
|
|
|
|
const __filename = fileURLToPath(import.meta.url);
|
|
const __dirname = path.dirname(__filename);
|
|
|
|
const app = express();
|
|
const PORT = process.env.PORT || 3001;
|
|
|
|
// Middleware
|
|
app.use(cors());
|
|
app.use(express.json());
|
|
|
|
// Serve static files in production
|
|
if (process.env.NODE_ENV === 'production') {
|
|
app.use(express.static(path.join(__dirname, '../public')));
|
|
}
|
|
|
|
// Serve uploads
|
|
app.use('/uploads', express.static('uploads'));
|
|
|
|
// Ensure uploads directory exists
|
|
const uploadsDir = 'uploads';
|
|
if (!fs.existsSync(uploadsDir)) {
|
|
fs.mkdirSync(uploadsDir);
|
|
}
|
|
|
|
// Configure multer for file uploads
|
|
const storage = multer.diskStorage({
|
|
destination: (req, file, cb) => {
|
|
cb(null, 'uploads/');
|
|
},
|
|
filename: (req, file, cb) => {
|
|
const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
|
|
cb(null, file.fieldname + '-' + uniqueSuffix + path.extname(file.originalname));
|
|
}
|
|
});
|
|
|
|
const upload = multer({
|
|
storage: storage,
|
|
fileFilter: (req, file, cb) => {
|
|
if (file.mimetype.startsWith('image/')) {
|
|
cb(null, true);
|
|
} else {
|
|
cb(new Error('Only image files are allowed!'), false);
|
|
}
|
|
},
|
|
limits: {
|
|
fileSize: 5 * 1024 * 1024 // 5MB limit
|
|
}
|
|
});
|
|
|
|
// Initialize libsql database
|
|
const db = createClient({
|
|
url: 'file:db/lawn_scheduler.db'
|
|
});
|
|
|
|
// Create mowers table
|
|
await db.execute(`
|
|
CREATE TABLE IF NOT EXISTS mowers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
brand TEXT,
|
|
model TEXT,
|
|
isActive BOOLEAN DEFAULT 1,
|
|
createdAt TEXT DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Create zones table with updated schema
|
|
await db.execute(`
|
|
CREATE TABLE IF NOT EXISTS zones (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
imagePath TEXT,
|
|
lastMowedDate TEXT,
|
|
intervalDays INTEGER,
|
|
nextMowDate TEXT,
|
|
scheduleType TEXT DEFAULT 'interval',
|
|
area REAL DEFAULT 0,
|
|
createdAt TEXT DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Create mowing history table
|
|
await db.execute(`
|
|
CREATE TABLE IF NOT EXISTS mowing_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
zoneId INTEGER NOT NULL,
|
|
mowerId INTEGER,
|
|
mowedDate TEXT NOT NULL,
|
|
notes TEXT,
|
|
duration INTEGER, -- in minutes
|
|
weather TEXT,
|
|
activityType TEXT DEFAULT 'mowing', -- 'mowing' or 'trimming'
|
|
sessionId TEXT, -- for grouping bulk mowing sessions
|
|
createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (zoneId) REFERENCES zones (id) ON DELETE CASCADE,
|
|
FOREIGN KEY (mowerId) REFERENCES mowers (id) ON DELETE SET NULL
|
|
)
|
|
`);
|
|
|
|
// Add new columns to existing zones table if they don't exist
|
|
try {
|
|
await db.execute(`ALTER TABLE zones ADD COLUMN nextMowDate TEXT`);
|
|
} catch (error) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
try {
|
|
await db.execute(`ALTER TABLE zones ADD COLUMN scheduleType TEXT DEFAULT 'interval'`);
|
|
} catch (error) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add area column to existing table if it doesn't exist
|
|
try {
|
|
await db.execute(`ALTER TABLE zones ADD COLUMN area REAL DEFAULT 0`);
|
|
} catch (error) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add mowerId column to existing mowing_history table if it doesn't exist
|
|
try {
|
|
await db.execute(`ALTER TABLE mowing_history ADD COLUMN mowerId INTEGER REFERENCES mowers (id) ON DELETE SET NULL`);
|
|
} catch (error) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add sessionId column to existing mowing_history table if it doesn't exist
|
|
try {
|
|
await db.execute(`ALTER TABLE mowing_history ADD COLUMN sessionId TEXT`);
|
|
} catch (error) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add activityType column to existing mowing_history table if it doesn't exist
|
|
try {
|
|
await db.execute(`ALTER TABLE mowing_history ADD COLUMN activityType TEXT DEFAULT 'mowing'`);
|
|
} catch (error) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Insert default mowers if table is empty
|
|
const mowerCountResult = await db.execute('SELECT COUNT(*) as count FROM mowers');
|
|
const mowerCount = mowerCountResult.rows[0].count;
|
|
|
|
if (mowerCount === 0) {
|
|
await db.execute({
|
|
sql: 'INSERT INTO mowers (name, type, brand, model) VALUES (?, ?, ?, ?)',
|
|
args: ['Battery Mower', 'Battery', 'Generic', 'Battery Model']
|
|
});
|
|
|
|
await db.execute({
|
|
sql: 'INSERT INTO mowers (name, type, brand, model) VALUES (?, ?, ?, ?)',
|
|
args: ['Electric Mower', 'Electric', 'Generic', 'Electric Model']
|
|
});
|
|
}
|
|
|
|
// Insert sample data if table is empty
|
|
const countResult = await db.execute('SELECT COUNT(*) as count FROM zones');
|
|
const count = countResult.rows[0].count;
|
|
|
|
if (count === 0) {
|
|
const today = new Date();
|
|
const weekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);
|
|
const twoWeeksAgo = new Date(today.getTime() - 14 * 24 * 60 * 60 * 1000);
|
|
|
|
// Get mower IDs for sample data
|
|
const mowersResult = await db.execute('SELECT id FROM mowers LIMIT 2');
|
|
const batteryMowerId = mowersResult.rows[0]?.id || 1;
|
|
const electricMowerId = mowersResult.rows[1]?.id || 2;
|
|
|
|
// Insert zones with different schedule types
|
|
const frontYardResult = await db.execute({
|
|
sql: 'INSERT INTO zones (name, lastMowedDate, intervalDays, scheduleType, area) VALUES (?, ?, ?, ?, ?)',
|
|
args: ['Front Yard', weekAgo.toISOString(), 7, 'interval', 150.5]
|
|
});
|
|
|
|
const backYardResult = await db.execute({
|
|
sql: 'INSERT INTO zones (name, lastMowedDate, intervalDays, scheduleType, area) VALUES (?, ?, ?, ?, ?)',
|
|
args: ['Back Yard', twoWeeksAgo.toISOString(), 10, 'interval', 280.0]
|
|
});
|
|
|
|
const nextWeek = new Date(today.getTime() + 7 * 24 * 60 * 60 * 1000);
|
|
const sideGardenResult = await db.execute({
|
|
sql: 'INSERT INTO zones (name, lastMowedDate, nextMowDate, scheduleType, area) VALUES (?, ?, ?, ?, ?)',
|
|
args: ['Side Garden', today.toISOString(), nextWeek.toISOString(), 'specific', 75.25]
|
|
});
|
|
|
|
// Insert sample mowing history with mower assignments
|
|
const sampleHistory = [
|
|
{ zoneId: frontYardResult.lastInsertRowid, mowerId: batteryMowerId, date: today, duration: 45, weather: 'Sunny', notes: 'Perfect mowing conditions today' },
|
|
{ zoneId: frontYardResult.lastInsertRowid, mowerId: electricMowerId, date: weekAgo, duration: 40, weather: 'Cloudy', notes: 'Grass was a bit wet from morning dew' },
|
|
{ zoneId: backYardResult.lastInsertRowid, mowerId: electricMowerId, date: new Date(today.getTime() - 2 * 24 * 60 * 60 * 1000), duration: 60, weather: 'Partly cloudy', notes: 'Trimmed edges and cleaned up leaves' },
|
|
{ zoneId: sideGardenResult.lastInsertRowid, mowerId: batteryMowerId, date: new Date(today.getTime() - 3 * 24 * 60 * 60 * 1000), duration: 25, weather: 'Sunny', notes: 'Quick touch-up, looks great' },
|
|
{ zoneId: frontYardResult.lastInsertRowid, mowerId: batteryMowerId, date: new Date(today.getTime() - 14 * 24 * 60 * 60 * 1000), duration: 50, weather: 'Overcast', notes: 'First mow of the season' },
|
|
{ zoneId: backYardResult.lastInsertRowid, mowerId: electricMowerId, date: new Date(today.getTime() - 16 * 24 * 60 * 60 * 1000), duration: 65, weather: 'Sunny', notes: 'Had to go slow due to thick growth' },
|
|
{ zoneId: sideGardenResult.lastInsertRowid, mowerId: batteryMowerId, date: new Date(today.getTime() - 17 * 24 * 60 * 60 * 1000), duration: 30, weather: 'Windy', notes: 'Challenging conditions but got it done' },
|
|
{ zoneId: frontYardResult.lastInsertRowid, mowerId: electricMowerId, date: new Date(today.getTime() - 21 * 24 * 60 * 60 * 1000), duration: 42, weather: 'Cool', notes: 'Nice cool morning for mowing' },
|
|
{ zoneId: backYardResult.lastInsertRowid, mowerId: batteryMowerId, date: new Date(today.getTime() - 30 * 24 * 60 * 60 * 1000), duration: 55, weather: 'Humid', notes: 'Very humid day, took frequent breaks' },
|
|
{ zoneId: sideGardenResult.lastInsertRowid, mowerId: electricMowerId, date: new Date(today.getTime() - 31 * 24 * 60 * 60 * 1000), duration: 28, weather: 'Sunny', notes: 'Beautiful day for yard work' },
|
|
{ zoneId: frontYardResult.lastInsertRowid, mowerId: batteryMowerId, date: new Date(today.getTime() - 35 * 24 * 60 * 60 * 1000), duration: 38, weather: 'Partly cloudy', notes: 'Standard weekly maintenance' },
|
|
{ zoneId: backYardResult.lastInsertRowid, mowerId: electricMowerId, date: new Date(today.getTime() - 44 * 24 * 60 * 60 * 1000), duration: 70, weather: 'Hot', notes: 'Very hot day, started early morning' },
|
|
];
|
|
|
|
for (const history of sampleHistory) {
|
|
await db.execute({
|
|
sql: 'INSERT INTO mowing_history (zoneId, mowerId, mowedDate, duration, weather, notes) VALUES (?, ?, ?, ?, ?, ?)',
|
|
args: [history.zoneId, history.mowerId, history.date.toISOString(), history.duration, history.weather, history.notes]
|
|
});
|
|
}
|
|
}
|
|
|
|
// Helper function to calculate zone status
|
|
function calculateZoneStatus(zone) {
|
|
const today = new Date();
|
|
|
|
// For new zones that haven't been mowed yet
|
|
if (!zone.lastMowedDate) {
|
|
return {
|
|
...zone,
|
|
daysSinceLastMow: null,
|
|
daysUntilNext: null,
|
|
status: 'new',
|
|
isOverdue: false,
|
|
isDueToday: false,
|
|
isNew: true
|
|
};
|
|
}
|
|
|
|
const lastMowed = new Date(zone.lastMowedDate);
|
|
const daysSinceLastMow = Math.floor((today - lastMowed) / (1000 * 60 * 60 * 24));
|
|
|
|
let daysUntilNext;
|
|
let status = 'ok';
|
|
|
|
if (zone.scheduleType === 'specific' && zone.nextMowDate) {
|
|
// Specific date scheduling
|
|
const nextMowDate = new Date(zone.nextMowDate);
|
|
daysUntilNext = Math.floor((nextMowDate - today) / (1000 * 60 * 60 * 24));
|
|
} else {
|
|
// Interval-based scheduling
|
|
daysUntilNext = zone.intervalDays - daysSinceLastMow;
|
|
}
|
|
|
|
if (daysUntilNext < 0) {
|
|
status = 'overdue';
|
|
} else if (daysUntilNext <= 0) {
|
|
status = 'due';
|
|
} else if (daysUntilNext <= 1) {
|
|
status = 'due';
|
|
}
|
|
|
|
return {
|
|
...zone,
|
|
daysSinceLastMow,
|
|
daysUntilNext,
|
|
status,
|
|
isOverdue: daysUntilNext < 0,
|
|
isDueToday: daysUntilNext <= 0 && daysUntilNext >= -1,
|
|
isNew: false
|
|
};
|
|
}
|
|
|
|
// Helper function to calculate next mow date for interval scheduling
|
|
function calculateNextMowDate(lastMowedDate, intervalDays) {
|
|
if (!lastMowedDate || !intervalDays) return null;
|
|
const lastMowed = new Date(lastMowedDate);
|
|
const nextMow = new Date(lastMowed.getTime() + (intervalDays * 24 * 60 * 60 * 1000));
|
|
return nextMow.toISOString();
|
|
}
|
|
|
|
// Helper function to generate session ID
|
|
function generateSessionId() {
|
|
return `session_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
|
|
}
|
|
|
|
// Mower routes
|
|
app.get('/api/mowers', async (req, res) => {
|
|
try {
|
|
const result = await db.execute('SELECT * FROM mowers WHERE isActive = 1 ORDER BY name');
|
|
const mowers = result.rows.map(row => ({
|
|
id: row.id,
|
|
name: row.name,
|
|
type: row.type,
|
|
brand: row.brand,
|
|
model: row.model,
|
|
isActive: row.isActive,
|
|
createdAt: row.createdAt
|
|
}));
|
|
res.json(mowers);
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
app.post('/api/mowers', async (req, res) => {
|
|
try {
|
|
const { name, type, brand, model } = req.body;
|
|
|
|
const result = await db.execute({
|
|
sql: 'INSERT INTO mowers (name, type, brand, model) VALUES (?, ?, ?, ?)',
|
|
args: [name, type, brand || null, model || null]
|
|
});
|
|
|
|
const newMowerResult = await db.execute({
|
|
sql: 'SELECT * FROM mowers WHERE id = ?',
|
|
args: [result.lastInsertRowid]
|
|
});
|
|
|
|
const newMower = {
|
|
id: newMowerResult.rows[0].id,
|
|
name: newMowerResult.rows[0].name,
|
|
type: newMowerResult.rows[0].type,
|
|
brand: newMowerResult.rows[0].brand,
|
|
model: newMowerResult.rows[0].model,
|
|
isActive: newMowerResult.rows[0].isActive,
|
|
createdAt: newMowerResult.rows[0].createdAt
|
|
};
|
|
|
|
res.status(201).json(newMower);
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
// Routes
|
|
app.get('/api/zones', async (req, res) => {
|
|
try {
|
|
const result = await db.execute('SELECT * FROM zones ORDER BY name');
|
|
const zones = result.rows.map(row => ({
|
|
id: row.id,
|
|
name: row.name,
|
|
imagePath: row.imagePath,
|
|
lastMowedDate: row.lastMowedDate,
|
|
intervalDays: row.intervalDays,
|
|
nextMowDate: row.nextMowDate,
|
|
scheduleType: row.scheduleType || 'interval',
|
|
area: row.area || 0,
|
|
createdAt: row.createdAt
|
|
}));
|
|
const zonesWithStatus = zones.map(calculateZoneStatus);
|
|
res.json(zonesWithStatus);
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
app.get('/api/zones/:id', async (req, res) => {
|
|
try {
|
|
const result = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
if (result.rows.length === 0) {
|
|
return res.status(404).json({ error: 'Zone not found' });
|
|
}
|
|
|
|
const zone = {
|
|
id: result.rows[0].id,
|
|
name: result.rows[0].name,
|
|
imagePath: result.rows[0].imagePath,
|
|
lastMowedDate: result.rows[0].lastMowedDate,
|
|
intervalDays: result.rows[0].intervalDays,
|
|
nextMowDate: result.rows[0].nextMowDate,
|
|
scheduleType: result.rows[0].scheduleType || 'interval',
|
|
area: result.rows[0].area || 0,
|
|
createdAt: result.rows[0].createdAt
|
|
};
|
|
|
|
const zoneWithStatus = calculateZoneStatus(zone);
|
|
res.json(zoneWithStatus);
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
// Get mowing history for all zones or specific zone with pagination
|
|
app.get('/api/history', async (req, res) => {
|
|
try {
|
|
const { zoneId, limit = 10, offset = 0 } = req.query;
|
|
|
|
// Get total count for pagination
|
|
let countSql = `
|
|
SELECT COUNT(*) as total
|
|
FROM mowing_history mh
|
|
JOIN zones z ON mh.zoneId = z.id
|
|
LEFT JOIN mowers m ON mh.mowerId = m.id
|
|
`;
|
|
let countArgs = [];
|
|
|
|
if (zoneId) {
|
|
countSql += ' WHERE mh.zoneId = ?';
|
|
countArgs.push(zoneId);
|
|
}
|
|
|
|
const countResult = await db.execute({ sql: countSql, args: countArgs });
|
|
const total = countResult.rows[0].total;
|
|
|
|
// Get paginated results
|
|
let sql = `
|
|
SELECT
|
|
mh.*,
|
|
z.name as zoneName,
|
|
z.area as zoneArea,
|
|
m.name as mowerName,
|
|
m.type as mowerType
|
|
FROM mowing_history mh
|
|
JOIN zones z ON mh.zoneId = z.id
|
|
LEFT JOIN mowers m ON mh.mowerId = m.id
|
|
`;
|
|
let args = [];
|
|
|
|
if (zoneId) {
|
|
sql += ' WHERE mh.zoneId = ?';
|
|
args.push(zoneId);
|
|
}
|
|
|
|
sql += ' ORDER BY mh.mowedDate DESC LIMIT ? OFFSET ?';
|
|
args.push(parseInt(limit), parseInt(offset));
|
|
|
|
const result = await db.execute({ sql, args });
|
|
|
|
const history = result.rows.map(row => ({
|
|
id: row.id,
|
|
zoneId: row.zoneId,
|
|
zoneName: row.zoneName,
|
|
zoneArea: row.zoneArea,
|
|
mowerId: row.mowerId,
|
|
mowerName: row.mowerName,
|
|
mowerType: row.mowerType,
|
|
mowedDate: row.mowedDate,
|
|
notes: row.notes,
|
|
duration: row.duration,
|
|
weather: row.weather,
|
|
activityType: row.activityType || 'mowing',
|
|
sessionId: row.sessionId,
|
|
createdAt: row.createdAt
|
|
}));
|
|
|
|
// Calculate pagination info
|
|
const currentPage = Math.floor(parseInt(offset) / parseInt(limit)) + 1;
|
|
const totalPages = Math.ceil(total / parseInt(limit));
|
|
const hasNextPage = currentPage < totalPages;
|
|
const hasPrevPage = currentPage > 1;
|
|
|
|
res.json({
|
|
data: history,
|
|
pagination: {
|
|
total,
|
|
currentPage,
|
|
totalPages,
|
|
hasNextPage,
|
|
hasPrevPage,
|
|
limit: parseInt(limit),
|
|
offset: parseInt(offset)
|
|
}
|
|
});
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
// Get mowing statistics
|
|
app.get('/api/history/stats', async (req, res) => {
|
|
try {
|
|
const { period = '30' } = req.query; // days
|
|
const periodDays = parseInt(period);
|
|
const startDate = new Date();
|
|
startDate.setDate(startDate.getDate() - periodDays);
|
|
|
|
// Total mowing sessions
|
|
const totalResult = await db.execute({
|
|
sql: `
|
|
SELECT COUNT(*) as total FROM (
|
|
SELECT 1 FROM mowing_history
|
|
WHERE mowedDate >= ? AND sessionId IS NULL
|
|
UNION ALL
|
|
SELECT 1 FROM mowing_history
|
|
WHERE mowedDate >= ? AND sessionId IS NOT NULL
|
|
GROUP BY sessionId
|
|
) AS combined
|
|
`,
|
|
args: [startDate.toISOString(), startDate.toISOString()]
|
|
});
|
|
|
|
// Total time spent
|
|
const timeResult = await db.execute({
|
|
sql: 'SELECT SUM(duration) as totalMinutes FROM mowing_history WHERE mowedDate >= ? AND duration IS NOT NULL',
|
|
args: [startDate.toISOString()]
|
|
});
|
|
|
|
// Total area mowed
|
|
const areaResult = await db.execute({
|
|
sql: `
|
|
SELECT SUM(z.area) as totalArea
|
|
FROM mowing_history mh
|
|
JOIN zones z ON mh.zoneId = z.id
|
|
WHERE mh.mowedDate >= ?
|
|
`,
|
|
args: [startDate.toISOString()]
|
|
});
|
|
|
|
// Most active zone
|
|
const activeZoneResult = await db.execute({
|
|
sql: `
|
|
SELECT z.name, COUNT(*) as sessions
|
|
FROM mowing_history mh
|
|
JOIN zones z ON mh.zoneId = z.id
|
|
WHERE mh.mowedDate >= ?
|
|
GROUP BY mh.zoneId, z.name
|
|
ORDER BY sessions DESC
|
|
LIMIT 1
|
|
`,
|
|
args: [startDate.toISOString()]
|
|
});
|
|
|
|
// Most used mower
|
|
const activeMowerResult = await db.execute({
|
|
sql: `
|
|
SELECT m.name, m.type, COUNT(*) as sessions
|
|
FROM mowing_history mh
|
|
JOIN mowers m ON mh.mowerId = m.id
|
|
WHERE mh.mowedDate >= ?
|
|
GROUP BY mh.mowerId, m.name, m.type
|
|
ORDER BY sessions DESC
|
|
LIMIT 1
|
|
`,
|
|
args: [startDate.toISOString()]
|
|
});
|
|
|
|
// Daily activity for chart
|
|
const dailyResult = await db.execute({
|
|
sql: `
|
|
SELECT
|
|
DATE(mowedDate) as date,
|
|
COUNT(*) as sessions,
|
|
SUM(duration) as totalDuration,
|
|
SUM(z.area) as totalArea
|
|
FROM mowing_history mh
|
|
JOIN zones z ON mh.zoneId = z.id
|
|
WHERE mowedDate >= ?
|
|
GROUP BY DATE(mowedDate)
|
|
ORDER BY date DESC
|
|
LIMIT 30
|
|
`,
|
|
args: [startDate.toISOString()]
|
|
});
|
|
|
|
res.json({
|
|
period: periodDays,
|
|
totalSessions: totalResult.rows[0].total,
|
|
totalMinutes: timeResult.rows[0].totalMinutes || 0,
|
|
totalArea: areaResult.rows[0].totalArea || 0,
|
|
mostActiveZone: activeZoneResult.rows[0] || null,
|
|
mostUsedMower: activeMowerResult.rows[0] || null,
|
|
dailyActivity: dailyResult.rows.map(row => ({
|
|
date: row.date,
|
|
sessions: row.sessions,
|
|
duration: row.totalDuration || 0,
|
|
area: row.totalArea || 0
|
|
}))
|
|
});
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
app.post('/api/zones', upload.single('image'), async (req, res) => {
|
|
try {
|
|
const { name, intervalDays, nextMowDate, scheduleType, area } = req.body;
|
|
const imagePath = req.file ? `/uploads/${req.file.filename}` : null;
|
|
|
|
// For new zones, don't set lastMowedDate (they haven't been mowed yet)
|
|
let sql, args;
|
|
|
|
if (scheduleType === 'specific') {
|
|
sql = 'INSERT INTO zones (name, imagePath, nextMowDate, scheduleType, area) VALUES (?, ?, ?, ?, ?)';
|
|
args = [name, imagePath, nextMowDate, scheduleType, parseFloat(area) || 0];
|
|
} else {
|
|
sql = 'INSERT INTO zones (name, imagePath, intervalDays, scheduleType, area) VALUES (?, ?, ?, ?, ?)';
|
|
args = [name, imagePath, parseInt(intervalDays), scheduleType || 'interval', parseFloat(area) || 0];
|
|
}
|
|
|
|
const result = await db.execute({ sql, args });
|
|
|
|
const newZoneResult = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [result.lastInsertRowid]
|
|
});
|
|
|
|
const newZone = {
|
|
id: newZoneResult.rows[0].id,
|
|
name: newZoneResult.rows[0].name,
|
|
imagePath: newZoneResult.rows[0].imagePath,
|
|
lastMowedDate: newZoneResult.rows[0].lastMowedDate,
|
|
intervalDays: newZoneResult.rows[0].intervalDays,
|
|
nextMowDate: newZoneResult.rows[0].nextMowDate,
|
|
scheduleType: newZoneResult.rows[0].scheduleType || 'interval',
|
|
area: newZoneResult.rows[0].area || 0,
|
|
createdAt: newZoneResult.rows[0].createdAt
|
|
};
|
|
|
|
const zoneWithStatus = calculateZoneStatus(newZone);
|
|
res.status(201).json(zoneWithStatus);
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
app.put('/api/zones/:id', upload.single('image'), async (req, res) => {
|
|
try {
|
|
const { name, intervalDays, lastMowedDate, nextMowDate, scheduleType, area } = req.body;
|
|
|
|
const existingResult = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
if (existingResult.rows.length === 0) {
|
|
return res.status(404).json({ error: 'Zone not found' });
|
|
}
|
|
|
|
const existingZone = existingResult.rows[0];
|
|
const imagePath = req.file ? `/uploads/${req.file.filename}` : existingZone.imagePath;
|
|
|
|
let sql, args;
|
|
|
|
if (scheduleType === 'specific') {
|
|
sql = 'UPDATE zones SET name = ?, imagePath = ?, lastMowedDate = ?, nextMowDate = ?, scheduleType = ?, intervalDays = NULL, area = ? WHERE id = ?';
|
|
args = [name, imagePath, lastMowedDate || null, nextMowDate, scheduleType, parseFloat(area) || 0, req.params.id];
|
|
} else {
|
|
sql = 'UPDATE zones SET name = ?, imagePath = ?, lastMowedDate = ?, intervalDays = ?, scheduleType = ?, nextMowDate = NULL, area = ? WHERE id = ?';
|
|
args = [name, imagePath, lastMowedDate || null, parseInt(intervalDays), scheduleType || 'interval', parseFloat(area) || 0, req.params.id];
|
|
}
|
|
|
|
console.log('Updating zone with data:', { name, intervalDays, lastMowedDate, nextMowDate, scheduleType, area });
|
|
console.log('SQL:', sql);
|
|
console.log('ARGS:', args);
|
|
|
|
await db.execute({ sql, args });
|
|
|
|
// Delete old image if new one was provided
|
|
if (req.file && existingZone.imagePath) {
|
|
const oldImagePath = path.join(process.cwd(), existingZone.imagePath.substring(1));
|
|
if (fs.existsSync(oldImagePath)) {
|
|
fs.unlinkSync(oldImagePath);
|
|
}
|
|
}
|
|
|
|
const updatedResult = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
const updatedZone = {
|
|
id: updatedResult.rows[0].id,
|
|
name: updatedResult.rows[0].name,
|
|
imagePath: updatedResult.rows[0].imagePath,
|
|
lastMowedDate: updatedResult.rows[0].lastMowedDate,
|
|
intervalDays: updatedResult.rows[0].intervalDays,
|
|
nextMowDate: updatedResult.rows[0].nextMowDate,
|
|
scheduleType: updatedResult.rows[0].scheduleType || 'interval',
|
|
area: updatedResult.rows[0].area || 0,
|
|
createdAt: updatedResult.rows[0].createdAt
|
|
};
|
|
|
|
const zoneWithStatus = calculateZoneStatus(updatedZone);
|
|
res.json(zoneWithStatus);
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
app.delete('/api/zones/:id', async (req, res) => {
|
|
try {
|
|
const result = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
if (result.rows.length === 0) {
|
|
return res.status(404).json({ error: 'Zone not found' });
|
|
}
|
|
|
|
const zone = result.rows[0];
|
|
|
|
// Delete associated image
|
|
if (zone.imagePath) {
|
|
const imagePath = path.join(process.cwd(), zone.imagePath.substring(1));
|
|
if (fs.existsSync(imagePath)) {
|
|
fs.unlinkSync(imagePath);
|
|
}
|
|
}
|
|
|
|
await db.execute({
|
|
sql: 'DELETE FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
res.status(204).send();
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
app.post('/api/zones/:id/mow', async (req, res) => {
|
|
try {
|
|
const { notes, duration, weather, mowerId } = req.body;
|
|
const today = new Date().toISOString();
|
|
|
|
// Get current zone data
|
|
const zoneResult = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
if (zoneResult.rows.length === 0) {
|
|
return res.status(404).json({ error: 'Zone not found' });
|
|
}
|
|
|
|
const zone = zoneResult.rows[0];
|
|
|
|
// Update zone's last mowed date and calculate next mow date if using interval scheduling
|
|
let sql, args;
|
|
|
|
if (zone.scheduleType === 'interval' && zone.intervalDays) {
|
|
const nextMowDate = calculateNextMowDate(today, zone.intervalDays);
|
|
sql = 'UPDATE zones SET lastMowedDate = ?, nextMowDate = ? WHERE id = ?';
|
|
args = [today, nextMowDate, req.params.id];
|
|
} else {
|
|
// For specific date scheduling, just update last mowed date
|
|
sql = 'UPDATE zones SET lastMowedDate = ? WHERE id = ?';
|
|
args = [today, req.params.id];
|
|
}
|
|
|
|
await db.execute({ sql, args });
|
|
|
|
// Add to mowing history
|
|
await db.execute({
|
|
sql: 'INSERT INTO mowing_history (zoneId, mowerId, mowedDate, notes, duration, weather, activityType) VALUES (?, ?, ?, ?, ?, ?, ?)',
|
|
args: [req.params.id, mowerId || null, today, notes || null, duration || null, weather || null, 'mowing']
|
|
});
|
|
|
|
const updatedResult = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
const updatedZone = {
|
|
id: updatedResult.rows[0].id,
|
|
name: updatedResult.rows[0].name,
|
|
imagePath: updatedResult.rows[0].imagePath,
|
|
lastMowedDate: updatedResult.rows[0].lastMowedDate,
|
|
intervalDays: updatedResult.rows[0].intervalDays,
|
|
nextMowDate: updatedResult.rows[0].nextMowDate,
|
|
scheduleType: updatedResult.rows[0].scheduleType || 'interval',
|
|
area: updatedResult.rows[0].area || 0,
|
|
createdAt: updatedResult.rows[0].createdAt
|
|
};
|
|
|
|
const zoneWithStatus = calculateZoneStatus(updatedZone);
|
|
res.json(zoneWithStatus);
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
// Trimming endpoint (doesn't update zone schedule)
|
|
app.post('/api/zones/:id/trim', async (req, res) => {
|
|
try {
|
|
const { notes, duration, weather, mowerId } = req.body;
|
|
const today = new Date().toISOString();
|
|
|
|
// Get current zone data to verify it exists
|
|
const zoneResult = await db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [req.params.id]
|
|
});
|
|
|
|
if (zoneResult.rows.length === 0) {
|
|
return res.status(404).json({ error: 'Zone not found' });
|
|
}
|
|
|
|
// Add to mowing history with trimming activity type
|
|
// Note: We don't update the zone's lastMowedDate or nextMowDate for trimming
|
|
await db.execute({
|
|
sql: 'INSERT INTO mowing_history (zoneId, mowerId, mowedDate, notes, duration, weather, activityType) VALUES (?, ?, ?, ?, ?, ?, ?)',
|
|
args: [req.params.id, mowerId || null, today, notes || null, duration || null, weather || null, 'trimming']
|
|
});
|
|
|
|
res.json({ success: true, message: 'Trimming recorded successfully' });
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
// Bulk mowing endpoint
|
|
app.post('/api/zones/bulk-mow', async (req, res) => {
|
|
try {
|
|
const { selectedZoneIds, notes, totalDuration, weather, mowerId, activityType = 'mowing' } = req.body;
|
|
|
|
if (!selectedZoneIds || selectedZoneIds.length === 0) {
|
|
return res.status(400).json({ error: 'No zones selected' });
|
|
}
|
|
|
|
const today = new Date().toISOString();
|
|
const sessionId = generateSessionId();
|
|
|
|
// Get zone data for selected zones
|
|
const zoneResults = await Promise.all(
|
|
selectedZoneIds.map(id =>
|
|
db.execute({
|
|
sql: 'SELECT * FROM zones WHERE id = ?',
|
|
args: [id]
|
|
})
|
|
)
|
|
);
|
|
|
|
const zones = zoneResults.map(result => result.rows[0]).filter(Boolean);
|
|
|
|
if (zones.length === 0) {
|
|
return res.status(404).json({ error: 'No valid zones found' });
|
|
}
|
|
|
|
// Calculate total area for proportional time distribution
|
|
const totalArea = zones.reduce((sum, zone) => sum + (zone.area || 0), 0);
|
|
|
|
// Process each zone
|
|
for (const zone of zones) {
|
|
// Calculate proportional duration
|
|
let zoneDuration = null;
|
|
if (totalDuration && totalArea > 0 && zone.area > 0) {
|
|
const proportion = zone.area / totalArea;
|
|
zoneDuration = Math.round(totalDuration * proportion);
|
|
}
|
|
|
|
// Only update zone schedule for mowing, not trimming
|
|
if (activityType === 'mowing') {
|
|
// Update zone's last mowed date and calculate next mow date if using interval scheduling
|
|
let sql, args;
|
|
|
|
if (zone.scheduleType === 'interval' && zone.intervalDays) {
|
|
const nextMowDate = calculateNextMowDate(today, zone.intervalDays);
|
|
sql = 'UPDATE zones SET lastMowedDate = ?, nextMowDate = ? WHERE id = ?';
|
|
args = [today, nextMowDate, zone.id];
|
|
} else {
|
|
// For specific date scheduling, just update last mowed date
|
|
sql = 'UPDATE zones SET lastMowedDate = ? WHERE id = ?';
|
|
args = [today, zone.id];
|
|
}
|
|
|
|
await db.execute({ sql, args });
|
|
}
|
|
|
|
// Add to mowing history with session ID
|
|
await db.execute({
|
|
sql: 'INSERT INTO mowing_history (zoneId, mowerId, mowedDate, notes, duration, weather, sessionId, activityType) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
|
|
args: [zone.id, mowerId || null, today, notes || null, zoneDuration, weather || null, sessionId, activityType]
|
|
});
|
|
}
|
|
|
|
res.json({
|
|
success: true,
|
|
message: `Successfully recorded ${activityType} session for ${zones.length} zones`,
|
|
sessionId,
|
|
zonesUpdated: zones.length
|
|
});
|
|
} catch (error) {
|
|
res.status(500).json({ error: error.message });
|
|
}
|
|
});
|
|
|
|
// Serve React app in production
|
|
if (process.env.NODE_ENV === 'production') {
|
|
app.get('*', (req, res) => {
|
|
res.sendFile(path.join(__dirname, '../public/index.html'));
|
|
});
|
|
}
|
|
|
|
// Health check endpoint
|
|
app.get('/health', (req, res) => {
|
|
res.status(200).json({ status: 'healthy', timestamp: new Date().toISOString() });
|
|
});
|
|
|
|
app.listen(PORT, '0.0.0.0', () => {
|
|
console.log(`Server running on http://0.0.0.0:${PORT}`);
|
|
console.log(`Environment: ${process.env.NODE_ENV || 'development'}`);
|
|
}); |