EEP Communication App

\n Access Denied. Reports are available for authorized personnel only.\n

\n );\n case \"task\":\n return (\n \n );\n case \"setting\":\n return [\"CEO\", \"Director\", \"Manager\", \"Admin\"].includes(user?.role)\n ? \n : (\n

\n Access Denied. Settings are available for authorized personnel only.\n

\n );\n case \"profile\":\n return ;\n default:\n setView(\"news\");\n return ;\n }\n };\n\n return (\n {\n setView(viewName);\n if (options?.taskId) {\n setHighlightedTaskId(options.taskId);\n }\n }}\n handleLogout={handleLogout}\n handleProfileDetail={handleProfileDetail}\n >\n {renderContent()}\n \n );\n}\n\nfunction client() {\n const rootElement = document.getElementById(\"root\");\n if (rootElement) {\n createRoot(rootElement).render();\n } else {\n console.error(\"Failed to find the root element\");\n }\n}\n\nif (typeof document !== \"undefined\") {\n client();\n}\nexport default async function server(request: Request) {\n try {\n const { sqlite } = await import(\"https://esm.town/v/stevekrouse/sqlite\");\n const KEY = \"MediaMonitoring\";\n\n // Employees\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_employees (\n employee_id INTEGER PRIMARY KEY,\n first_name TEXT,\n last_name TEXT,\n role TEXT,\n status TEXT DEFAULT 'active',\n email TEXT UNIQUE,\n phone TEXT,\n password TEXT,\n date_hired datetime,\n created_at datetime DEFAULT CURRENT_TIMESTAMP,\n updated_at datetime DEFAULT CURRENT_TIMESTAMP,\n user_name TEXT UNIQUE,\n address varchar(120),\n grade TEXT,\n sex varchar(10),\n supervisor_id INTEGER,\n department TEXT,\n job_title TEXT,\n date_of_birth datetime DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (supervisor_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE SET NULL\n )`);\n\n // Media Monitored\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_media_monitored (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n employee_id INTEGER,\n title TEXT,\n link TEXT,\n published_date DATETIME,\n main_idea TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n media_name TEXT,\n sentiment TEXT,\n platform TEXT,\n FOREIGN KEY (employee_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE SET NULL\n )`);\n\n // Contents\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_contents (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n employee_id INTEGER,\n reviewer_id INTEGER,\n title TEXT,\n main_idea TEXT,\n link TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n is_published INTEGER,\n comments TEXT,\n published_at DATETIME,\n FOREIGN KEY (employee_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE SET NULL,\n FOREIGN KEY (reviewer_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE SET NULL\n )`);\n\n // Tasks\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_tasks (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n assignee_id INTEGER,\n manager_id INTEGER,\n title TEXT,\n description TEXT,\n due_date DATETIME,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n completed_at DATETIME,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n status TEXT,\n priority TEXT,\n final_remark TEXT,\n incompletion_reason TEXT,\n attachments TEXT,\n FOREIGN KEY (assignee_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE SET NULL,\n FOREIGN KEY (manager_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE SET NULL\n )`);\n\n // Comments\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_comments (\n id TEXT PRIMARY KEY,\n employee_id INTEGER,\n comment TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n content_id TEXT,\n FOREIGN KEY (employee_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE CASCADE,\n FOREIGN KEY (content_id) REFERENCES ${KEY}_contents(id) ON DELETE CASCADE\n )`);\n\n // Logs\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_logs (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n employee_id INTEGER,\n action TEXT,\n table_name TEXT,\n description TEXT,\n record_id TEXT,\n ip_address TEXT,\n device TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (employee_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE CASCADE\n )`);\n\n // Notifications\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_notifications (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n employee_id INTEGER,\n message TEXT,\n type TEXT,\n related_id INTEGER,\n is_read INTEGER,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n read_at DATETIME,\n FOREIGN KEY (employee_id) REFERENCES ${KEY}_employees(employee_id) ON DELETE CASCADE,\n FOREIGN KEY (related_id) REFERENCES ${KEY}_tasks(id) ON DELETE SET NULL\n )`);\n\n const url = new URL(request.url);\n const path = url.pathname;\n const method = request.method;\n\n // Login endpoint\n if (path === \"/login\" && method === \"POST\") {\n try {\n const body = await request.json();\n if (!body.username || !body.password) {\n return new Response(JSON.stringify({ success: false, message: \"Missing username or password\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const hashedPassword = await hashPassword(body.password);\n\n const result = await sqlite.execute(\n `SELECT employee_id, user_name, role FROM ${KEY}_employees WHERE user_name = ? AND password = ?`,\n [body.username, hashedPassword],\n );\n\n if (result.rows.length > 0) {\n return new Response(\n JSON.stringify({\n success: true,\n user: {\n id: result.rows[0].employee_id, // Use employee_id as the user ID for frontend\n username: result.rows[0].user_name,\n role: result.rows[0].role,\n },\n }),\n {\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n } else {\n return new Response(JSON.stringify({ success: false, message: \"Invalid username or password\" }), {\n status: 401,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n } catch (error) {\n console.error(\"Error during login process:\", error);\n return new Response(\n JSON.stringify({ success: false, message: \"An internal server error occurred during login.\" }),\n {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n }\n\n // Submit Content\n if (path === \"/submit-news\" && method === \"POST\") {\n const body = await request.json();\n if (!body.title || !body.employee_id)\n return new Response(JSON.stringify({ success: false, message: \"Missing ID, title or employee_id\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n try {\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_contents (title, main_idea, link, employee_id, created_at, updated_at, is_published, comments, published_at) VALUES (?, ?, ?, ?, datetime('now'), datetime('now'), ?, ?, ?)`,\n [\n body.title,\n body.description || null,\n body.link || null,\n body.employee_id, // Use employee_id\n body.isPublished || 0, // Default to 0\n body.comments || null,\n body.publishedAt || null,\n ],\n );\n\n if (!result) {\n return new Response(JSON.stringify({ success: false, message: \"Failed to submit content\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n const { lastInsertRowid } = result;\n const newContent = await sqlite.execute(\n `SELECT * FROM ${KEY}_contents WHERE id = ?`,\n [lastInsertRowid],\n );\n return new Response(JSON.stringify({ success: true, article: newContent.rows[0] }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error: any) {\n console.error(\"Error submitting content:\", error);\n return new Response(JSON.stringify({ success: false, message: `Failed to submit content: ${error.message}` }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // --- Fetch Contents\n if (path === \"/fetch-news\" && method === \"GET\") {\n try {\n const contents = await sqlite.execute(`\n SELECT\n c.id, c.title, c.main_idea, c.link, c.employee_id, c.reviewer_id, c.created_at, c.updated_at, c.is_published, c.comments, c.published_at,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName\n FROM ${KEY}_contents c\n LEFT JOIN ${KEY}_employees e ON c.employee_id = e.employee_id\n ORDER BY c.created_at DESC\n `);\n return new Response(JSON.stringify({ success: true, articles: contents.rows }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error fetching contents:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to fetch contents\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n if (path === \"/delete-news\" && method === \"DELETE\") {\n const id = url.searchParams.get(\"id\");\n if (!id) {\n return new Response(JSON.stringify({ success: false, message: \"Missing content ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n try {\n await sqlite.execute(\"BEGIN TRANSACTION\");\n\n // 1. Delete associated comments\n await sqlite.execute(`DELETE FROM ${KEY}_comments WHERE content_id = ?`, [id]);\n // 2. Delete the content\n const result = await sqlite.execute(`DELETE FROM ${KEY}_contents WHERE id = ?`, [id]);\n await sqlite.execute(\"COMMIT\");\n if (result.rowsAffected > 0) {\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } else {\n return new Response(JSON.stringify({ success: false, message: \"Content not found\" }), {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n } catch (error) {\n console.error(\"Deletion error:\", error);\n\n // Only try to rollback if we haven't committed\n try {\n await sqlite.execute(\"ROLLBACK\");\n } catch (rollbackError) {\n console.error(\"Rollback failed:\", rollbackError);\n }\n const checkResult = await sqlite.execute(`SELECT COUNT(*) as count FROM ${KEY}_contents WHERE id = ?`, [id]);\n const stillExists = checkResult.rows[0].count > 0;\n return new Response(\n JSON.stringify({\n success: !stillExists, // If content is gone, consider it success\n message: stillExists ? \"Failed to delete content\" : \"Content deleted but with warnings\",\n error: error.message,\n }),\n {\n status: stillExists ? 500 : 200,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n }\n\n // Edit Content\n if (path === \"/edit-news\" && method === \"PUT\") {\n try {\n const body = await request.json();\n const { id, title, mainIdea, link, isPublished, comments, publishedAt } = body;\n\n if (!id || !title || !mainIdea) { // Link is conditional\n return new Response(\n JSON.stringify({\n success: false,\n message: \"Missing required fields (id, title, mainIdea) for content update\",\n }),\n {\n status: 400, // Bad Request\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n if (isPublished === 1 && !link) { // If published, link is required\n return new Response(\n JSON.stringify({ success: false, message: \"URL Link is required for published content.\" }),\n {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n if (isPublished === 1 && link) {\n try {\n new URL(link);\n } catch (_) {\n return new Response(\n JSON.stringify({ success: false, message: \"Invalid URL format for link\" }),\n {\n status: 400, // Bad Request\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n }\n const result = await sqlite.execute(\n `UPDATE ${KEY}_contents SET title = ?, main_idea = ?, link = ?, is_published = ?, comments = ?, published_at = ?, updated_at = datetime('now') WHERE id = ?`,\n [\n title,\n mainIdea,\n isPublished === 1 ? link : null,\n isPublished || 0,\n comments || null,\n isPublished === 1 ? publishedAt : null,\n id,\n ],\n );\n if (result.rowsAffected > 0) {\n const updatedContent = await sqlite.execute(\n `\n SELECT\n c.id, c.title, c.main_idea, c.link, c.employee_id, c.reviewer_id, c.created_at, c.updated_at, c.is_published, c.comments, c.published_at,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName\n FROM ${KEY}_contents c\n LEFT JOIN ${KEY}_employees e ON c.employee_id = e.employee_id\n WHERE c.id = ?`,\n [id],\n );\n\n if (updatedContent.rows.length > 0) {\n return new Response(JSON.stringify({ success: true, article: updatedContent.rows[0] }), {\n status: 200, // OK\n headers: { \"Content-Type\": \"application/json\" },\n });\n } else {\n return new Response(\n JSON.stringify({ success: false, message: \"Content updated but could not be refetched\" }),\n {\n status: 500, // Internal Server Error\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n } else {\n return new Response(\n JSON.stringify({ success: false, message: \"Content not found or no changes made\" }),\n {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n } catch (error) {\n console.error(\"Error updating content:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to update content\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Submit Media\n if (path === \"/submit-media\" && method === \"POST\") {\n const body = await request.json();\n if (!body.platform || !body.mediaName || !body.title || !body.mainIdea || !body.sentiment || !body.employee_id) {\n return new Response(JSON.stringify({ success: false, message: \"Missing required fields\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n try {\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_media_monitored \n (platform, media_name, title, main_idea, sentiment, link, employee_id, published_date, created_at, updated_at) \n VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'), datetime('now'))`,\n [\n body.platform,\n body.mediaName,\n body.title,\n body.mainIdea,\n body.sentiment,\n body.link || null,\n body.employee_id,\n body.publishedDate || null,\n ],\n );\n const { lastInsertRowid } = result;\n const newEntry = await sqlite.execute(\n `SELECT * FROM ${KEY}_media_monitored WHERE id = ?`,\n [lastInsertRowid],\n );\n\n return new Response(JSON.stringify({ success: true, entry: newEntry.rows[0] }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error submitting media:\", error);\n return new Response(\n JSON.stringify({ success: false, message: `Submission failed: ${error.message}` }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // Fetch Media\n if (path === \"/fetch-media\" && method === \"GET\") {\n const mediaEntries = await sqlite.execute(`SELECT * FROM ${KEY}_media_monitored ORDER BY created_at DESC`);\n return new Response(JSON.stringify({ entries: mediaEntries.rows }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n // Delete Media\n if (path === \"/delete-media\" && method === \"POST\") {\n const body = await request.json();\n if (!body.id)\n return new Response(JSON.stringify({ success: false, message: \"Missing media entry ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n const result = await sqlite.execute(`DELETE FROM ${KEY}_media_monitored WHERE id = ?`, [body.id]);\n if (result.rowsAffected > 0)\n return new Response(JSON.stringify({ success: true, id: body.id }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n return new Response(JSON.stringify({ success: false, message: \"Media entry not found\" }), {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n // Edit Media\n if (path === \"/edit-media\" && method === \"PUT\") {\n const body = await request.json();\n const { id, platform, mediaName, title, mainIdea, sentiment, link, publishedDate } = body;\n\n if (!id || !platform || !mediaName || !title || !mainIdea || !sentiment || !link) {\n return new Response(\n JSON.stringify({ success: false, message: \"Missing required fields for media entry update\" }),\n {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n try {\n const result = await sqlite.execute(\n `UPDATE ${KEY}_media_monitored SET platform = ?, media_name = ?, title = ?, main_idea = ?, sentiment = ?, link = ?, published_date = ?, updated_at = datetime('now') WHERE id = ?`,\n [platform, mediaName, title, mainIdea, sentiment, link, publishedDate || null, id],\n );\n\n if (result.rowsAffected > 0) {\n const updatedEntry = await sqlite.execute(`SELECT * FROM ${KEY}_media_monitored WHERE id = ?`, [id]);\n return new Response(JSON.stringify({ success: true, entry: updatedEntry.rows[0] }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } else {\n return new Response(JSON.stringify({ success: false, message: \"Media entry not found or no changes made\" }), {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n } catch (error) {\n console.error(\"Error updating media entry:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to update media entry\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Generate detail report\n if (path === \"/generate-report\" && method === \"GET\") {\n try {\n const newsArticlesResult = await sqlite.execute(`\n SELECT\n c.id,\n c.title,\n c.main_idea,\n c.link,\n c.is_published,\n c.created_at,\n c.employee_id AS author_id,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName\n FROM ${KEY}_contents c\n JOIN ${KEY}_employees e ON c.employee_id = e.employee_id\n `);\n\n const mediaEntriesResult = await sqlite.execute(`\n SELECT\n mm.id,\n mm.platform,\n mm.title,\n mm.sentiment,\n mm.created_at,\n mm.link,\n mm.employee_id AS author_id,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName\n FROM ${KEY}_media_monitored mm\n JOIN ${KEY}_employees e ON mm.employee_id = e.employee_id\n `);\n\n // Combine firstName and fatherName to create a fullName for each entry\n const newsArticles = newsArticlesResult.rows.map((row: any) => ({\n ...row,\n authorFullName: `${row.firstName} ${row.fatherName || \"\"}`.trim(), // Handle fatherName being null\n }));\n\n const mediaEntries = mediaEntriesResult.rows.map((row: any) => ({\n ...row,\n authorFullName: `${row.firstName} ${row.fatherName || \"\"}`.trim(), // Handle fatherName being null\n }));\n\n return new Response(\n JSON.stringify({\n newsArticles,\n mediaEntries,\n }),\n {\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n } catch (error) {\n console.error(\"Failed to fetch detailed report:\", error);\n return new Response(\n JSON.stringify({ success: false, message: \"Failed to fetch detailed report\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // generate-weekly-summary endpoint\n if (path === \"/generate-weekly-summary\" && method === \"GET\") {\n try {\n const url = new URL(request.url);\n const daysParam = url.searchParams.get(\"days\");\n let days = parseInt(daysParam || \"7\", 10); // Default to 7 if not provided or invalid\n\n if (isNaN(days) || days <= 0) {\n days = 7;\n console.warn(`Invalid or missing 'days' parameter, defaulting to ${days} days.`);\n }\n\n const relevantDate = new Date();\n relevantDate.setDate(relevantDate.getDate() - days);\n const relevantDateISO = relevantDate.toISOString();\n\n const newsCountsResult = await sqlite.execute(\n `\n SELECT\n c.employee_id AS author_id,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName,\n SUM(CASE WHEN c.is_published = 1 THEN 1 ELSE 0 END) as publishedContent,\n SUM(CASE WHEN c.is_published = 0 THEN 1 ELSE 0 END) as draftContent\n FROM ${KEY}_contents c\n JOIN ${KEY}_employees e ON c.employee_id = e.employee_id\n WHERE c.created_at >= ?\n GROUP BY c.employee_id, e.user_name, e.first_name, e.last_name\n `,\n [relevantDateISO],\n );\n const mediaCountsResult = await sqlite.execute(\n `\n SELECT\n mm.employee_id AS author_id,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName,\n COUNT(*) as monitoredMedia\n FROM ${KEY}_media_monitored mm\n JOIN ${KEY}_employees e ON mm.employee_id = e.employee_id\n WHERE mm.created_at >= ?\n GROUP BY mm.employee_id, e.user_name, e.first_name, e.last_name\n `,\n [relevantDateISO],\n );\n const weeklySummaryMap = new Map();\n\n for (const row of newsCountsResult.rows) {\n const fullName = `${(row as any).firstName} ${(row as any).fatherName || \"\"}`.trim();\n weeklySummaryMap.set((row as any).author_id, {\n author_id: (row as any).author_id,\n author_username: (row as any).author_username,\n authorFullName: fullName,\n publishedContent: (row as any).publishedContent,\n draftContent: (row as any).draftContent,\n monitoredMedia: 0,\n });\n }\n\n for (const row of mediaCountsResult.rows) {\n const fullName = `${(row as any).firstName} ${(row as any).fatherName || \"\"}`.trim();\n if (weeklySummaryMap.has((row as any).author_id)) {\n weeklySummaryMap.get((row as any).author_id).monitoredMedia = (row as any).monitoredMedia;\n } else {\n weeklySummaryMap.set((row as any).author_id, {\n author_id: (row as any).author_id,\n author_username: (row as any).author_username,\n authorFullName: fullName,\n publishedContent: 0,\n draftContent: 0,\n monitoredMedia: row.monitoredMedia,\n });\n }\n }\n\n const weeklySummary = Array.from(weeklySummaryMap.values());\n return new Response(JSON.stringify(weeklySummary), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Failed to generate weekly summary:\", error);\n return new Response(\n JSON.stringify({ success: false, message: \"Failed to generate weekly summary\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // create Employee\n if (path === \"/create-employee\" && method === \"POST\") {\n try {\n const body = await request.json();\n\n // Basic validation for required fields for creation\n const requiredFields = [\n \"employeeId\",\n \"username\",\n \"password\",\n \"firstName\",\n \"jobTitle\",\n \"email\",\n \"phoneNumber\",\n \"department\",\n \"dateOfEmployment\",\n \"status\",\n \"role\",\n ];\n\n for (const field of requiredFields) {\n if (!body[field]) {\n return new Response(\n JSON.stringify({\n success: false,\n message: `Missing required field: ${field}`,\n }),\n { status: 400, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // Email format validation\n if (!/\\S+@\\S+\\.\\S+/.test(body.email)) {\n return new Response(\n JSON.stringify({ success: false, message: \"Invalid email format\" }),\n { status: 400, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n // Hashed the password if found\n if (body.password) {\n body.password = await hashPassword(body.password);\n }\n\n // Supervisor validation for non-CEO roles\n if (body.role !== \"CEO\" && !body.supervisor) {\n return new Response(\n JSON.stringify({\n success: false,\n message: \"Supervisor is required for this role.\",\n }),\n { status: 400, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n // Insert into database\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_employees (\n employee_id, user_name, password, first_name, last_name, role, job_title, grade, sex,\n email, phone, department, date_hired, status, date_of_birth, address, supervisor_id\n ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,\n [\n body.employeeId,\n body.username,\n body.password,\n body.firstName,\n body.fatherName || null,\n body.role,\n body.jobTitle,\n body.grade || null,\n body.sex || null,\n body.email,\n body.phoneNumber,\n body.department,\n body.dateOfEmployment,\n body.status || \"active\",\n body.dateOfBirth,\n body.address,\n body.role === \"CEO\" ? null : (body.supervisor || null), // Supervisor is null if CEO, otherwise use provided or null\n ],\n );\n\n if (result.rowsAffected > 0) {\n // Fetch the newly created employee to return it with its ID\n const newEmployeeResult = await sqlite.execute(\n `SELECT employee_id AS employeeId, user_name AS username, first_name AS firstName, last_name AS fatherName,\n role, job_title AS jobTitle, grade, sex, email, phone AS phoneNumber, department,\n date_hired AS dateOfEmployment, status, date_of_birth AS dateOfBirth,\n address, supervisor_id AS supervisor, created_at, updated_at\n FROM ${KEY}_employees WHERE employee_id = ?`,\n [body.employeeId],\n );\n return new Response(\n JSON.stringify({\n success: true,\n message: \"Employee created successfully\",\n employee: newEmployeeResult.rows[0],\n }),\n { status: 201, headers: { \"Content-Type\": \"application/json\" } }, // 201 Created\n );\n } else {\n return new Response(\n JSON.stringify({ success: false, message: \"Failed to create employee\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n } catch (error) {\n console.error(\"Error creating employee:\", error);\n // Check for unique constraint errors\n if (\n error.message && (error.message.includes(\"UNIQUE constraint failed: MediaMonitoring_1_employees.employee_id\")\n || error.message.includes(\"UNIQUE constraint failed: MediaMonitoring_1_employees.user_name\")\n || error.message.includes(\"UNIQUE constraint failed: MediaMonitoring_1_employees.email\"))\n ) {\n return new Response(\n JSON.stringify({ success: false, message: \"Employee ID, Username, or Email already exists.\" }),\n { status: 409, headers: { \"Content-Type\": \"application/json\" } }, // 409 Conflict\n );\n }\n return new Response(\n JSON.stringify({ success: false, message: \"An error occurred while creating the employee.\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // Update Employee\n if (path.startsWith(\"/update-employee/\") && method === \"PUT\") {\n try {\n const originalEmployeeId = path.split(\"/\").pop(); // This is the ID from the URL, used for WHERE clause\n const body = await request.json();\n\n // Start building the dynamic update query\n const updates = [];\n const params = [];\n\n // Employee ID (can be changed by Admin/Director/CEO)\n if (body.employeeId !== undefined && body.employeeId !== originalEmployeeId) {\n updates.push(\"employee_id = ?\");\n params.push(body.employeeId);\n }\n\n // Username (can be changed by Admin/Director/CEO)\n if (body.username !== undefined) {\n updates.push(\"user_name = ?\");\n params.push(body.username);\n }\n\n // Password (can be changed by Admin/Director/CEO or user themselves)\n if (body.password) { // Only update if a new password is provided\n const hashedPassword = await hashPassword(body.password);\n updates.push(\"password = ?\");\n params.push(hashedPassword);\n }\n\n // Email (always validated if present)\n if (body.email !== undefined) {\n if (!/\\S+@\\S+\\.\\S+/.test(body.email)) {\n return new Response(\n JSON.stringify({ success: false, message: \"Invalid email format\" }),\n { status: 400, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n updates.push(\"email = ?\");\n params.push(body.email);\n }\n // Other fields (updated if present in the body)\n if (body.firstName !== undefined) {\n updates.push(\"first_name = ?\");\n params.push(body.firstName);\n }\n if (body.fatherName !== undefined) {\n updates.push(\"last_name = ?\");\n params.push(body.fatherName);\n }\n if (body.role !== undefined) {\n updates.push(\"role = ?\");\n params.push(body.role);\n }\n if (body.jobTitle !== undefined) {\n updates.push(\"job_title = ?\");\n params.push(body.jobTitle);\n }\n if (body.grade !== undefined) {\n updates.push(\"grade = ?\");\n params.push(body.grade);\n }\n if (body.sex !== undefined) {\n updates.push(\"sex = ?\");\n params.push(body.sex);\n }\n if (body.phoneNumber !== undefined) {\n updates.push(\"phone = ?\");\n params.push(body.phoneNumber);\n }\n if (body.department !== undefined) {\n updates.push(\"department = ?\");\n params.push(body.department);\n }\n if (body.dateOfEmployment !== undefined) {\n updates.push(\"date_hired = ?\");\n params.push(body.dateOfEmployment);\n }\n if (body.status !== undefined) {\n updates.push(\"status = ?\");\n params.push(body.status);\n }\n if (body.dateOfBirth !== undefined) {\n updates.push(\"date_of_birth = ?\");\n params.push(body.dateOfBirth);\n }\n if (body.address !== undefined) {\n updates.push(\"address = ?\");\n params.push(body.address);\n }\n\n let currentRole = null;\n if (body.role === undefined || body.supervisor !== undefined) { // Need current role if role not provided or supervisor is provided\n const currentEmployeeResult = await sqlite.execute(\n `SELECT role FROM ${KEY}_employees WHERE employee_id = ?`,\n [originalEmployeeId],\n );\n currentRole = currentEmployeeResult.rows[0]?.role;\n }\n if (body.role !== undefined) { // If role is being updated\n if (body.role === \"CEO\") {\n updates.push(\"supervisor_id = ?\");\n params.push(null);\n } else if (body.supervisor !== undefined) { // If role is not CEO AND supervisor is provided\n updates.push(\"supervisor_id = ?\");\n params.push(body.supervisor || null); // Use provided supervisor or null if empty string\n }\n // If role is not CEO and supervisor is NOT provided, we don't touch supervisor_id\n } else if (body.supervisor !== undefined) { // If role is NOT being updated, but supervisor IS provided\n if (currentRole !== \"CEO\") { // Only update supervisor if current role is not CEO\n updates.push(\"supervisor_id = ?\");\n params.push(body.supervisor || null);\n }\n }\n\n // If no fields are provided for update, return an error\n if (updates.length === 0) {\n return new Response(\n JSON.stringify({ success: false, message: \"No valid fields provided for update.\" }),\n { status: 400, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n // Add updated_at timestamp\n updates.push(\"updated_at = datetime('now')\");\n\n const query = `UPDATE ${KEY}_employees SET ${updates.join(\", \")} WHERE employee_id = ?`;\n params.push(originalEmployeeId);\n\n const result = await sqlite.execute(query, params);\n\n if (result.rowsAffected > 0) {\n const fetchId = body.employeeId !== undefined ? body.employeeId : originalEmployeeId;\n const updatedEmployeeResult = await sqlite.execute(\n `SELECT employee_id AS employeeId, user_name AS username, first_name AS firstName, last_name AS fatherName,\n role, job_title AS jobTitle, grade, sex, email, phone AS phoneNumber, department,\n date_hired AS dateOfEmployment, status, date_of_birth AS dateOfBirth,\n address, supervisor_id AS supervisor, created_at, updated_at\n FROM ${KEY}_employees WHERE employee_id = ?`,\n [fetchId],\n );\n return new Response(\n JSON.stringify({\n success: true,\n message: \"Employee updated successfully\",\n employee: updatedEmployeeResult.rows[0],\n }),\n { status: 200, headers: { \"Content-Type\": \"application/json\" } },\n );\n } else {\n return new Response(\n JSON.stringify({ success: false, message: \"Employee not found or no changes made\" }),\n { status: 404, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n } catch (error) {\n console.error(\"Error updating employee:\", error);\n if (\n error.message && (error.message.includes(\"UNIQUE constraint failed: MediaMonitoring_1_employees.employee_id\")\n || error.message.includes(\"UNIQUE constraint failed: MediaMonitoring_1_employees.user_name\")\n || error.message.includes(\"UNIQUE constraint failed: MediaMonitoring_1_employees.email\"))\n ) {\n return new Response(\n JSON.stringify({ success: false, message: \"Employee ID, Username, or Email already exists.\" }),\n { status: 409, headers: { \"Content-Type\": \"application/json\" } }, // 409 Conflict\n );\n }\n return new Response(\n JSON.stringify({ success: false, message: \"An error occurred while updating the employee.\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // Fetch Employee by ID\n if (path.startsWith(\"/fetch-employee/\") && method === \"GET\") {\n try {\n const employeeId = path.split(\"/\").pop();\n const result = await sqlite.execute(\n `SELECT employee_id AS employeeId, user_name AS username, password, first_name AS firstName, last_name AS fatherName,\n role, job_title AS jobTitle, grade, sex, email, phone AS phoneNumber, department,\n date_hired AS dateOfEmployment, status, date_of_birth AS dateOfBirth,\n address, supervisor_id AS supervisor, created_at, updated_at\n FROM ${KEY}_employees WHERE employee_id = ?`,\n [employeeId],\n );\n\n if (result.rows.length > 0) {\n return new Response(\n JSON.stringify({ success: true, employee: result.rows[0] }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n } else {\n return new Response(\n JSON.stringify({ success: false, message: \"Employee not found\" }),\n { status: 404, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n } catch (error) {\n console.error(\"Error fetching employee:\", error);\n return new Response(\n JSON.stringify({ success: false, message: \"An error occurred while fetching employee data.\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // Fetch All Employees\n if (path === \"/fetch-employees\" && method === \"GET\") {\n try {\n const employees = await sqlite.execute(\n `SELECT employee_id AS employeeId, user_name AS username, first_name AS firstName, last_name AS fatherName,\n role, job_title AS jobTitle, grade, sex, email, phone AS phoneNumber, department,\n date_hired AS dateOfEmployment, status, date_of_birth AS dateOfBirth,\n address, supervisor_id AS supervisor, created_at, updated_at\n FROM ${KEY}_employees ORDER BY first_name ASC`,\n );\n return new Response(\n JSON.stringify({ success: true, employees: employees.rows }),\n { headers: { \"Content-Type\": \"application/json\" } },\n );\n } catch (error) {\n console.error(\"Error fetching all employees:\", error);\n return new Response(\n JSON.stringify({ success: false, message: \"Failed to fetch employees.\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // Delete Employee\n if (path.startsWith(\"/delete-employee/\") && method === \"DELETE\") {\n try {\n const employeeId = path.split(\"/\").pop();\n if (!employeeId) {\n return new Response(\n JSON.stringify({ success: false, message: \"Missing employee ID\" }),\n { status: 400, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n\n const result = await sqlite.execute(\n `DELETE FROM ${KEY}_employees WHERE employee_id = ?`,\n [employeeId],\n );\n\n if (result.rowsAffected > 0) {\n return new Response(\n JSON.stringify({ success: true, message: \"Employee deleted successfully\" }),\n { status: 200, headers: { \"Content-Type\": \"application/json\" } },\n );\n } else {\n return new Response(\n JSON.stringify({ success: false, message: \"Employee not found\" }),\n { status: 404, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n } catch (error) {\n console.error(\"Error deleting employee:\", error);\n return new Response(\n JSON.stringify({ success: false, message: \"An error occurred while deleting the employee.\" }),\n { status: 500, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n }\n\n // submit task\n if (path === \"/submit-task\" && method === \"POST\") {\n const body = await request.json();\n\n // Basic validation\n if (!body.title || body.assignee_id === \"\" || body.manager_id === \"\" || !body.due_date)\n return new Response(JSON.stringify({ success: false, message: \"Missing required fields\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n\n const assigneeIdNum = Number(body.assignee_id);\n const managerIdNum = Number(body.manager_id);\n\n if (isNaN(assigneeIdNum) || isNaN(managerIdNum)) {\n return new Response(JSON.stringify({ success: false, message: \"Invalid Assignee ID or Manager ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n try {\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_tasks\n (title, description, assignee_id, manager_id, created_at, updated_at, due_date, status, priority, final_remark, incompletion_reason, attachments)\n VALUES (?, ?, ?, ?, datetime('now'), datetime('now'), ?, ?, ?, ?, ?, ?)`,\n [\n body.title,\n body.description || null,\n assigneeIdNum,\n managerIdNum,\n body.due_date,\n body.status || \"pending\",\n body.priority || \"medium\",\n body.final_remark || null,\n body.incompletion_reason || null,\n body.attachments || null,\n ],\n );\n\n const newTask = await sqlite.execute(`SELECT * FROM ${KEY}_tasks WHERE id = ?`, [\n result.lastInsertRowid,\n ]);\n\n return new Response(JSON.stringify({ success: true, task: newTask.rows[0] }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error: any) {\n console.error(\"Error creating task:\", error);\n if (error.message && error.message.includes(\"FOREIGN KEY constraint failed\")) {\n return new Response(\n JSON.stringify({ success: false, message: \"Invalid Assignee ID or Manager ID. Employee not found.\" }),\n {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n return new Response(JSON.stringify({ success: false, message: \"Database error creating task.\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Get tasks\n if (path === \"/tasks\" && method === \"GET\") {\n try {\n const statusFilter = url.searchParams.get(\"status\");\n const assigneeFilter = url.searchParams.get(\"assignee_id\");\n const userId = url.searchParams.get(\"user_id\");\n const userRole = url.searchParams.get(\"user_role\");\n\n // --- DEBUGGING LOGS START ---\n console.log(`[DEBUG] Fetching tasks for User ID: ${userId}, Role: ${userRole}`);\n console.log(`[DEBUG] Status Filter: ${statusFilter}, Assignee Filter: ${assigneeFilter}`);\n // --- DEBUGGING LOGS END ---\n\n let query = `SELECT * FROM ${KEY}_tasks`;\n const conditions = [];\n const values = [];\n\n if (statusFilter && statusFilter !== \"all\") {\n conditions.push(\"status = ?\");\n values.push(statusFilter);\n }\n if (userRole === \"Employee\") {\n conditions.push(\"assignee_id = ?\");\n values.push(Number(userId));\n console.log(`[DEBUG] Employee-specific condition: assignee_id = ${Number(userId)}`);\n } else if (userRole === \"Manager\") {\n if (assigneeFilter) {\n conditions.push(\"assignee_id = ?\");\n values.push(Number(assigneeFilter));\n console.log(\n `[DEBUG] Manager-specific condition (filtered by assignee): assignee_id = ${Number(assigneeFilter)}`,\n );\n } else {\n conditions.push(\"(assignee_id = ? OR manager_id = ?)\");\n values.push(Number(userId), Number(userId));\n console.log(\n `[DEBUG] Manager-specific condition (all): (assignee_id = ${Number(userId)} OR manager_id = ${\n Number(userId)\n })`,\n );\n }\n } else if (userRole === \"Admin\" || userRole === \"CEO\" || userRole === \"Director\") {\n if (assigneeFilter) {\n conditions.push(\"assignee_id = ?\");\n values.push(Number(assigneeFilter));\n console.log(\n `[DEBUG] Admin/CEO/Director-specific condition (filtered by assignee): assignee_id = ${\n Number(assigneeFilter)\n }`,\n );\n } else {\n console.log(`[DEBUG] Admin/CEO/Director-specific condition: No filter, showing all tasks.`);\n }\n }\n\n if (conditions.length > 0) {\n query += \" WHERE \" + conditions.join(\" AND \");\n }\n query += \" ORDER BY due_date ASC\";\n console.log(`[DEBUG] Final SQL Query: ${query}`);\n console.log(`[DEBUG] Query Values: ${JSON.stringify(values)}`);\n\n const result = await sqlite.execute(query, values);\n\n console.log(`[DEBUG] Query Result Rows: ${result.rows.length}`);\n if (result.rows.length > 0) {\n console.log(`[DEBUG] First 5 Result Rows: ${JSON.stringify(result.rows.slice(0, 5))}`);\n }\n\n return new Response(JSON.stringify({ success: true, tasks: result.rows }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error fetching tasks:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to fetch tasks\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // GET /tasks/:id ---\n if (path.startsWith(\"/tasks/\") && method === \"GET\") {\n const taskId = path.split(\"/\")[2];\n if (!taskId || isNaN(Number(taskId))) {\n return new Response(JSON.stringify({ success: false, message: \"Invalid task ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n try {\n const result = await sqlite.execute(`SELECT * FROM ${KEY}_tasks WHERE id = ?`, [\n Number(taskId),\n ]);\n if (result.rows.length === 0)\n return new Response(JSON.stringify({ success: false, message: \"Task not found\" }), {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n });\n return new Response(JSON.stringify({ success: true, task: result.rows[0] }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error fetching single task:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Database error fetching task.\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // PATCH /tasks/:id ---\n if (path.startsWith(\"/tasks/\") && method === \"PATCH\") {\n const taskId = path.split(\"/\")[2];\n if (!taskId || isNaN(Number(taskId))) {\n return new Response(JSON.stringify({ success: false, message: \"Invalid task ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const body = await request.json();\n const { userId, userRole, ...updateFieldsFromBody } = body;\n\n const requestingUserId = userId;\n const requestingUserRole = userRole;\n\n const currentTaskResult = await sqlite.execute(`SELECT * FROM ${KEY}_tasks WHERE id = ?`, [Number(taskId)]);\n if (currentTaskResult.rows.length === 0) {\n return new Response(JSON.stringify({ success: false, message: \"Task not found\" }), {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const currentTask = currentTaskResult.rows[0];\n const currentStatus = currentTask.status;\n const taskManagerId = currentTask.manager_id;\n const taskAssigneeId = currentTask.assignee_id;\n\n let updates = [];\n let values = [];\n\n const allowedEditableFields = [\n \"title\",\n \"description\",\n \"assignee_id\",\n \"manager_id\",\n \"due_date\",\n \"priority\",\n \"attachments\", // Attachments can be updated generally\n ];\n\n // Admin/CEO/Director can edit all general fields\n // Managers can also edit general task details if they are the task's manager\n const canEditGeneralFields = [\"Admin\", \"CEO\", \"Director\"].includes(requestingUserRole)\n || (requestingUserRole === \"Manager\" && requestingUserId === taskManagerId);\n\n if (canEditGeneralFields) {\n for (const field of allowedEditableFields) {\n if (updateFieldsFromBody.hasOwnProperty(field)) {\n updates.push(`${field} = ?`);\n values.push(updateFieldsFromBody[field]);\n }\n }\n }\n\n // --- Status Change Authorization Logic (Strict Workflow) ---\n if (updateFieldsFromBody.hasOwnProperty(\"status\")) {\n const newStatus = updateFieldsFromBody.status;\n let isAuthorizedStatusChange = false;\n\n console.log(`[DEBUG] Attempting status change for Task ${taskId}: ${currentStatus} -> ${newStatus}`);\n console.log(`[DEBUG] Requesting User: ${requestingUserId} (Role: ${requestingUserRole})`);\n console.log(`[DEBUG] Task Assignee: ${taskAssigneeId}, Task Manager: ${taskManagerId}`);\n\n switch (newStatus) {\n case \"in_progress\":\n // Rule: Only assignee can initiate (pending -> in_progress)\n // Rule: Only manager can reopen (completed/cancelled/completion_requested -> in_progress)\n if (currentStatus === \"pending\" && requestingUserId === taskAssigneeId) {\n isAuthorizedStatusChange = true;\n } else if (\n (currentStatus === \"completed\" || currentStatus === \"cancelled\"\n || currentStatus === \"completion_requested\")\n && requestingUserId === taskManagerId\n ) {\n isAuthorizedStatusChange = true;\n }\n break;\n case \"completion_requested\":\n // Rule: Only assignee can request completion (in_progress -> completion_requested)\n if (currentStatus === \"in_progress\" && requestingUserId === taskAssigneeId) {\n isAuthorizedStatusChange = true;\n }\n break;\n case \"completed\":\n // Rule: Only manager can approve completion (completion_requested -> completed)\n if (currentStatus === \"completion_requested\" && requestingUserId === taskManagerId) {\n isAuthorizedStatusChange = true;\n }\n break;\n case \"cancelled\":\n // Rule: Only manager can cancel (from pending, in_progress, completion_requested)\n if (\n (currentStatus === \"pending\" || currentStatus === \"in_progress\"\n || currentStatus === \"completion_requested\")\n && requestingUserId === taskManagerId\n ) {\n isAuthorizedStatusChange = true;\n }\n break;\n case \"pending\":\n // Rule: Only manager can revert to pending from completed/cancelled (if needed, though 'in_progress' is more common for reopening)\n if (\n (currentStatus === \"completed\" || currentStatus === \"cancelled\") && requestingUserId === taskManagerId\n ) {\n isAuthorizedStatusChange = true;\n }\n break;\n default:\n isAuthorizedStatusChange = false; // Disallow other direct status changes\n }\n\n // Admin/CEO/Director can override status changes (if not covered by specific rules above)\n if ([\"Admin\", \"CEO\", \"Director\"].includes(requestingUserRole)) {\n isAuthorizedStatusChange = true;\n }\n\n if (!isAuthorizedStatusChange) {\n console.warn(\n `Unauthorized status change attempt: User ${requestingUserId} (Role: ${requestingUserRole}) tried to change task ${taskId} from ${currentStatus} to ${newStatus}.`,\n );\n return new Response(\n JSON.stringify({\n success: false,\n message: \"You are not authorized to change the status of this task to this state.\",\n }),\n {\n status: 403,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n updates.push(\"status = ?\");\n values.push(newStatus);\n\n // Handle completed_at timestamp\n if (newStatus === \"completed\") {\n updates.push(\"completed_at = datetime('now')\");\n } else if (currentStatus === \"completed\" && newStatus !== \"completed\") {\n // If status is changed from completed to something else, clear completed_at\n updates.push(\"completed_at = NULL\");\n }\n\n // Handle final_remark and incompletion_reason based on new status\n if (newStatus === \"completed\") {\n // If completed, incompletion_reason must be NULL\n updates.push(\"incompletion_reason = NULL\");\n if (updateFieldsFromBody.hasOwnProperty(\"final_remark\")) {\n updates.push(\"final_remark = ?\");\n values.push(updateFieldsFromBody.final_remark || null);\n }\n } else if (newStatus === \"cancelled\") {\n // If cancelled, incompletion_reason is required\n if (!updateFieldsFromBody.incompletion_reason) {\n return new Response(\n JSON.stringify({ success: false, message: \"Incompletion reason is required for cancelled tasks.\" }),\n { status: 400, headers: { \"Content-Type\": \"application/json\" } },\n );\n }\n updates.push(\"incompletion_reason = ?\");\n values.push(updateFieldsFromBody.incompletion_reason);\n if (updateFieldsFromBody.hasOwnProperty(\"final_remark\")) {\n updates.push(\"final_remark = ?\");\n values.push(updateFieldsFromBody.final_remark || null);\n }\n } else {\n // For other statuses, clear both final_remark and incompletion_reason\n updates.push(\"final_remark = NULL\");\n updates.push(\"incompletion_reason = NULL\");\n }\n } else {\n // If status is NOT being changed, but final_remark/incompletion_reason are present\n // This allows managers/admins to update remarks without changing status,\n // but only if the task is already completed or cancelled.\n if (currentStatus === \"completed\" || currentStatus === \"cancelled\") {\n if (updateFieldsFromBody.hasOwnProperty(\"final_remark\")) {\n updates.push(\"final_remark = ?\");\n values.push(updateFieldsFromBody.final_remark || null);\n }\n if (updateFieldsFromBody.hasOwnProperty(\"incompletion_reason\")) {\n updates.push(\"incompletion_reason = ?\");\n values.push(updateFieldsFromBody.incompletion_reason || null);\n }\n }\n }\n\n if (updates.length === 0)\n return new Response(JSON.stringify({ success: false, message: \"No valid fields to update\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n\n updates.push(\"updated_at = datetime('now')\");\n const updateQuery = `UPDATE ${KEY}_tasks SET ${updates.join(\", \")} WHERE id = ?`;\n values.push(Number(taskId));\n\n try {\n const result = await sqlite.execute(updateQuery, values);\n\n if (result.rowsAffected === 0) {\n const unchangedTask = await sqlite.execute(`SELECT * FROM ${KEY}_tasks WHERE id = ?`, [\n Number(taskId),\n ]);\n return new Response(\n JSON.stringify({ success: true, message: \"No changes detected\", task: unchangedTask.rows[0] }),\n {\n status: 200,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n const updatedTask = await sqlite.execute(`SELECT * FROM ${KEY}_tasks WHERE id = ?`, [\n Number(taskId),\n ]);\n\n if (updatedTask.rows.length === 0) {\n return new Response(JSON.stringify({ success: false, message: \"Task updated but could not be retrieved.\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n return new Response(JSON.stringify({ success: true, task: updatedTask.rows[0] }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error: any) {\n console.error(\"Error patching task:\", error);\n if (error.message && error.message.includes(\"FOREIGN KEY constraint failed\")) {\n return new Response(\n JSON.stringify({ success: false, message: \"Invalid Assignee ID or Manager ID. Employee not found.\" }),\n {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n return new Response(JSON.stringify({ success: false, message: \"Database error updating task.\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // DELETE /tasks/:id ---\n if (path.startsWith(\"/tasks/\") && method === \"DELETE\") {\n const taskId = path.split(\"/\")[2];\n if (!taskId || isNaN(Number(taskId))) {\n return new Response(JSON.stringify({ success: false, message: \"Invalid task ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n const body = await request.json();\n const { userId, userRole } = body;\n\n const requestingUserId = userId;\n const requestingUserRole = userRole;\n\n const taskResult = await sqlite.execute(`SELECT manager_id FROM ${KEY}_tasks WHERE id = ?`, [\n Number(taskId),\n ]);\n if (taskResult.rows.length === 0) {\n return new Response(JSON.stringify({ success: false, message: \"Task not found\" }), {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n const taskManagerId = taskResult.rows[0].manager_id;\n\n const isAuthorizedToDelete = requestingUserRole === \"Admin\" || requestingUserRole === \"CEO\"\n || requestingUserRole === \"Director\" || requestingUserId === taskManagerId;\n\n if (!isAuthorizedToDelete) {\n console.warn(\n `Unauthorized delete attempt: User ${requestingUserId} (Role: ${requestingUserRole}) tried to delete task ${taskId}.`,\n );\n return new Response(\n JSON.stringify({ success: false, message: \"You are not authorized to delete this task.\" }),\n {\n status: 403,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n try {\n const result = await sqlite.execute(`DELETE FROM ${KEY}_tasks WHERE id = ?`, [Number(taskId)]);\n if (result.rowsAffected === 0) {\n return new Response(JSON.stringify({ success: false, message: \"Task not found after auth check?\" }), {\n status: 404,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n return new Response(JSON.stringify({ success: true, message: \"Task deleted\" }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error deleting task:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Database error deleting task.\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Fetch Comments\n if (path === \"/fetch-comments\" && method === \"GET\") {\n try {\n const contentId = url.searchParams.get(\"content_id\"); // Optional: fetch for specific content\n let query = `\n SELECT\n c.id, c.employee_id, c.comment, c.created_at, c.updated_at, c.content_id,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName\n FROM ${KEY}_comments c\n JOIN ${KEY}_employees e ON c.employee_id = e.employee_id\n `;\n const values = [];\n if (contentId) {\n query += ` WHERE c.content_id = ?`;\n values.push(contentId);\n }\n query += ` ORDER BY c.created_at ASC`;\n\n const comments = await sqlite.execute(query, values);\n return new Response(JSON.stringify({ success: true, comments: comments.rows }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error fetching comments:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to fetch comments\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Submit Comment\n if (path === \"/submit-comment\" && method === \"POST\") {\n const body = await request.json();\n const { content_id, employee_id, comment } = body;\n\n if (!content_id || !employee_id || !comment || comment.trim() === \"\") {\n return new Response(\n JSON.stringify({ success: false, message: \"Missing content ID, employee ID, or comment text\" }),\n {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n\n try {\n const newCommentId = crypto.randomUUID();\n\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_comments (id, content_id, employee_id, comment, created_at, updated_at) VALUES (?, ?, ?, ?, datetime('now'), datetime('now'))`,\n [newCommentId, content_id, employee_id, comment],\n );\n\n const newComment = await sqlite.execute(\n `\n SELECT\n c.id, c.employee_id, c.comment, c.created_at, c.updated_at, c.content_id,\n e.user_name AS author_username,\n e.first_name AS firstName,\n e.last_name AS fatherName\n FROM ${KEY}_comments c\n JOIN ${KEY}_employees e ON c.employee_id = e.employee_id\n WHERE c.id = ?\n `,\n [newCommentId],\n );\n\n if (newComment.rows.length > 0) {\n return new Response(JSON.stringify({ success: true, comment: newComment.rows[0] }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } else {\n throw new Error(\"Comment inserted but could not be retrieved.\");\n }\n } catch (error: any) {\n console.error(\"Error submitting comment:\", error);\n if (error.message && error.message.includes(\"FOREIGN KEY constraint failed\")) {\n return new Response(\n JSON.stringify({\n success: false,\n message: \"Invalid Content ID or Employee ID. Content or employee not found.\",\n }),\n {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n }\n return new Response(JSON.stringify({ success: false, message: `Failed to submit comment: ${error.message}` }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Add to your server endpoints\n if (path === \"/create-notification\" && method === \"POST\") {\n const body = await request.json();\n if (!body.employee_id || !body.message || !body.type) {\n return new Response(JSON.stringify({ success: false, message: \"Missing required fields\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n try {\n const result = await sqlite.execute(\n `INSERT INTO ${KEY}_notifications \n (employee_id, message, type, related_id, is_read, created_at) \n VALUES (?, ?, ?, ?, 0, datetime('now'))`,\n [\n body.employee_id,\n body.message,\n body.type,\n body.related_id || null,\n ],\n );\n\n return new Response(\n JSON.stringify({\n success: true,\n notification_id: result.lastInsertRowid,\n }),\n {\n headers: { \"Content-Type\": \"application/json\" },\n },\n );\n } catch (error) {\n console.error(\"Error creating notification:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to create notification\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Add to your server endpoints\n if (path === \"/fetch-notifications\" && method === \"GET\") {\n const employeeId = url.searchParams.get(\"employee_id\");\n if (!employeeId) {\n return new Response(JSON.stringify({ success: false, message: \"Missing employee ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n try {\n const notifications = await sqlite.execute(\n `SELECT * FROM ${KEY}_notifications \n WHERE employee_id = ? \n ORDER BY created_at DESC \n LIMIT 50`,\n [employeeId],\n );\n return new Response(JSON.stringify({ success: true, notifications: notifications.rows }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error fetching notifications:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to fetch notifications\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n if (path === \"/mark-notification-read\" && method === \"POST\") {\n const body = await request.json();\n if (!body.notification_id || !body.employee_id) {\n return new Response(JSON.stringify({ success: false, message: \"Missing notification ID or employee ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n try {\n await sqlite.execute(\n `UPDATE ${KEY}_notifications \n SET is_read = 1, read_at = datetime('now') \n WHERE id = ? AND employee_id = ?`,\n [body.notification_id, body.employee_id],\n );\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error marking notification as read:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to mark notification as read\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n if (path === \"/mark-all-notifications-read\" && method === \"POST\") {\n const body = await request.json();\n if (!body.employee_id) {\n return new Response(JSON.stringify({ success: false, message: \"Missing employee ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n try {\n await sqlite.execute(\n `UPDATE ${KEY}_notifications \n SET is_read = 1, read_at = datetime('now') \n WHERE employee_id = ? AND is_read = 0`,\n [body.employee_id],\n );\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error marking all notifications as read:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to mark notifications as read\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Add this to your server endpoints\n if (path === \"/mark-notification-read\" && method === \"POST\") {\n const body = await request.json();\n if (!body.notification_id || !body.employee_id) {\n return new Response(JSON.stringify({ success: false, message: \"Missing notification ID or employee ID\" }), {\n status: 400,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n\n try {\n await sqlite.execute(\n `UPDATE ${KEY}_notifications \n SET is_read = 1, read_at = datetime('now') \n WHERE id = ? AND employee_id = ?`,\n [body.notification_id, body.employee_id],\n );\n return new Response(JSON.stringify({ success: true }), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n console.error(\"Error marking notification as read:\", error);\n return new Response(JSON.stringify({ success: false, message: \"Failed to mark notification as read\" }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n }\n\n // Root Path\n if (path === \"/\" && method === \"GET\") {\n return new Response(\n `\n \n \n \n EEP Communication App\n \n \n \n 📰\">\n \n \n \n

\n \n \n \n \n `,\n { headers: { \"Content-Type\": \"text/html; charset=utf-8\" } },\n );\n }\n\n // Fallback for unknown paths\n return new Response(\"Not Found\", { status: 404, headers: { \"Content-Type\": \"text/plain\" } });\n } catch (error: any) {\n console.error(\"Server error:\", error);\n return new Response(JSON.stringify({ success: false, message: `Internal Server Error: ${error.message}` }), {\n status: 500,\n headers: { \"Content-Type\": \"application/json\" },\n });\n }\n}","deleted","label","communicationportal",[99],"permalink","https://www.val.town/x/solomonferede/EEPPortal/code/App.tsx","extension","actionData","errors"]