/** @jsxImportSource https://esm.sh/react@18.2.0 */ import { createRoot } from "https://esm.sh/react-dom@18.2.0/client"; import React, { useEffect, useRef, useState } from "https://esm.sh/react@18.2.0"; function LoginScreen({ onLogin }) { const [password, setPassword] = useState(""); const [error, setError] = useState(""); const handleSubmit = async (e) => { e.preventDefault(); try { const response = await fetch("/login", { method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ password }), }); if (response.ok) { onLogin(); setError(""); } else { const result = await response.json(); setError(result.message || "Incorrect password"); } } catch (error) { setError("Login failed. Please try again."); } }; return (

); } function CustomerInteractionForm({ onSubmit }) { const [email, setEmail] = useState(""); const [firstName, setFirstName] = useState(""); const [lastName, setLastName] = useState(""); const [interactionDate, setInteractionDate] = useState(new Date().toISOString().split("T")[0]); const [notes, setNotes] = useState(""); const [additionalNotesLink, setAdditionalNotesLink] = useState(""); const formRef = useRef(null); const emailInputRef = useRef(null); const [tags, setTags] = useState([]); const [tagInput, setTagInput] = useState(""); // Fetch previous customer details when email changes useEffect(() => { const fetchCustomerDetails = async () => { if (!email) return; try { const response = await fetch(`/get-customer-details?email=${encodeURIComponent(email)}`); if (response.ok) { const customerDetails = await response.json(); if (customerDetails) { setFirstName(customerDetails.first_name || ""); setLastName(customerDetails.last_name || ""); } } } catch (error) { console.error("Error fetching customer details:", error); } }; fetchCustomerDetails(); }, [email]); // Add keyboard shortcut for form submission // Inside CustomerInteractionForm component useEffect(() => { const handleKeyDown = (e) => { // If we're in the tag input, let handleTagInput handle cmd-k if (e.target.className === "tag-input") { return; } // For other inputs, handle cmd-k normally if ((e.metaKey || e.ctrlKey) && e.key.toLowerCase() === "k") { e.preventDefault(); formRef.current?.requestSubmit(); } }; // Add event listener to window window.addEventListener("keydown", handleKeyDown, true); // Cleanup event listener return () => { window.removeEventListener("keydown", handleKeyDown, true); }; }, []); useEffect(() => { emailInputRef.current?.focus(); }, []); const handleTagInput = (e) => { // Stop cmd-k from removing tags if ((e.metaKey || e.ctrlKey) && e.key.toLowerCase() === "k") { e.preventDefault(); formRef.current?.requestSubmit(); return; } if (e.key === "Enter" || e.key === ",") { e.preventDefault(); const newTag = tagInput.trim(); if (newTag && !tags.includes(newTag)) { setTags([...tags, newTag]); } setTagInput(""); } }; const removeTag = (tagToRemove, event) => { // If this was triggered by a keyboard event, don't remove the tag if (event && (event.metaKey || event.ctrlKey)) { return; } setTags(tags.filter(tag => tag !== tagToRemove)); }; const handleSubmit = async (e) => { e.preventDefault(); try { const response = await fetch("/add-interaction", { method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ firstName, lastName, email, interactionDate, notes, additionalNotesLink, tags, // Add this line }), }); if (response.ok) { setFirstName(""); setLastName(""); setEmail(""); setNotes(""); setAdditionalNotesLink(""); setTags([]); // Reset tags setInteractionDate(new Date().toLocaleDateString("en-US")); onSubmit(); } } catch (error) { console.error("Error submitting interaction:", error); } }; return (

setEmail(e.target.value)} required />

setFirstName(e.target.value)} required /> setLastName(e.target.value)} required />

setInteractionDate(e.target.value)} required /> {/* Move the Key Takeaway and Additional Notes fields BEFORE the tags */} setNotes(e.target.value)} required /> setAdditionalNotesLink(e.target.value)} /> {/* Tags section moved to the end */}

{tags.map(tag => ( {tag} ))}

setTagInput(e.target.value)} onKeyDown={handleTagInput} className="tag-input" />

); } function InteractionList() { const [customerInteractions, setCustomerInteractions] = useState({}); const [copiedEmail, setCopiedEmail] = useState(null); const [currentPage, setCurrentPage] = useState(1); const [totalInteractions, setTotalInteractions] = useState(0); const [selectedTags, setSelectedTags] = useState([]); const [availableTags, setAvailableTags] = useState([]); const [tagCounts, setTagCounts] = useState({}); const [uniqueCustomerCount, setUniqueCustomerCount] = useState(0); const fetchUniqueCustomerCount = async () => { try { const response = await fetch("/get-unique-customer-count"); const data = await response.json(); setUniqueCustomerCount(data.count); } catch (error) { console.error("Error fetching unique customer count:", error); } }; useEffect(() => { fetchInteractions(); fetchUniqueCustomerCount(); }, [currentPage, selectedTags]); useEffect(() => { setCurrentPage(1); }, [selectedTags]); const handleCopyEmail = async (email) => { try { await navigator.clipboard.writeText(email); setCopiedEmail(email); setTimeout(() => setCopiedEmail(null), 2000); } catch (err) { console.error("Failed to copy email:", err); } }; const handleDeleteInteraction = async (id) => { try { const response = await fetch("/delete-interaction", { method: "DELETE", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ id }), }); if (response.ok) { fetchInteractions(); } } catch (error) { console.error("Error deleting interaction:", error); } }; const handleTagSelect = (tag) => { setSelectedTags(prev => prev.includes(tag) ? prev.filter(t => t !== tag) : [...prev, tag] ); }; const fetchInteractions = async () => { try { const response = await fetch(`/get-interactions?page=${currentPage}`); const data = await response.json(); // Set available tags and counts from server data setTagCounts(data.tagCounts); setAvailableTags(Object.keys(data.tagCounts)); // Filter interactions based on selected tags const filteredInteractions = selectedTags.length > 0 ? data.interactions.filter(interaction => { if (!interaction.tags) return false; const interactionTags = interaction.tags.split(",").map(tag => tag.trim()); return selectedTags.every(tag => interactionTags.includes(tag)); }) : data.interactions; // Group filtered interactions by customer const groupedInteractions = filteredInteractions.reduce((acc, interaction) => { const customerKey = `${interaction.first_name} ${interaction.last_name}`; if (!acc[customerKey]) { acc[customerKey] = []; } acc[customerKey].push(interaction); return acc; }, {}); setCustomerInteractions(groupedInteractions); // Update total count based on whether filters are applied if (selectedTags.length > 0) { // Need to get total count of filtered interactions from server const filteredCountResponse = await fetch(`/get-filtered-count?tags=${selectedTags.join(",")}`); const filteredCountData = await filteredCountResponse.json(); setTotalInteractions(filteredCountData.count); } else { setTotalInteractions(data.total); } } catch (error) { console.error("Error fetching interactions:", error); } }; useEffect(() => { fetchInteractions(); }, [currentPage, selectedTags]); return (

📋 Customer Conversations

We've spoken to {uniqueCustomerCount} customers 🙂‍↕️ {/* Tag Filter Section */}

Filter by Tags

{availableTags.map(tag => ( ))}

{selectedTags.length > 0 && ( )}

{/* Display when no results match filters */} {Object.keys(customerInteractions).length === 0 && selectedTags.length > 0 && (

No interactions found with the selected tags.

)} {Object.entries(customerInteractions).map(([customerName, interactions]) => (

🧑 {customerName}

📧{" "} handleCopyEmail(interactions[0].email)} className="copyable-email" > {interactions[0].email} {" "} {copiedEmail === interactions[0].email && "✓"} {interactions.map((interaction) => (

📝 {interaction.notes}

{interaction.additional_notes_link && (

🔗{" "} Notes

)} {interaction.tags && (

{interaction.tags.split(",").map(tag => ( {tag.trim()} ))}

)}

📅 {new Date(interaction.interaction_date + "T00:00:00").toLocaleDateString("en-US", { month: "short", day: "numeric", year: "numeric", timeZone: Intl.DateTimeFormat().resolvedOptions().timeZone, })} handleDeleteInteraction(interaction.id)} title="Delete interaction" > 🗑️

))}

Page {currentPage} of {Math.ceil(totalInteractions / 10)}

); } function App() { const [isAuthenticated, setIsAuthenticated] = useState(false); const [refreshList, setRefreshList] = useState(0); useEffect(() => { // Check authentication status when component mounts checkAuth(); }, []); const checkAuth = async () => { try { const response = await fetch("/check-auth"); const data = await response.json(); setIsAuthenticated(data.authenticated); } catch (error) { console.error("Error checking authentication:", error); setIsAuthenticated(false); } }; const handleLogin = () => { setIsAuthenticated(true); }; const handleLogout = () => { setIsAuthenticated(false); }; const handleInteractionSubmit = () => { setRefreshList(prev => prev + 1); }; if (!isAuthenticated) { return ; } return (

CRM

); } function client() { createRoot(document.getElementById("root")).render(); } if (typeof document !== "undefined") { client(); } export default async function server(request: Request): Promise { const { sqlite } = await import("https://esm.town/v/stevekrouse/sqlite"); const KEY = new URL(import.meta.url).pathname.split("/").at(-1); const SCHEMA_VERSION = 4; // Increment schema version // Retrieve the admin password from environment variable const ADMIN_PASSWORD = Deno.env.get("CRM_TEMPLATE_PASSWORD") || "default_fallback_password"; await sqlite.execute(` CREATE TABLE IF NOT EXISTS ${KEY}_interactions_${SCHEMA_VERSION} ( id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL, interaction_date DATE NOT NULL, notes TEXT NOT NULL, additional_notes_link TEXT, timestamp DATETIME DEFAULT CURRENT_TIMESTAMP ) `); await sqlite.execute(` CREATE TABLE IF NOT EXISTS ${KEY}_tags_${SCHEMA_VERSION} ( interaction_id INTEGER, tag TEXT NOT NULL, FOREIGN KEY (interaction_id) REFERENCES ${KEY}_interactions_${SCHEMA_VERSION}(id), PRIMARY KEY (interaction_id, tag) ) `); const isAuthenticated = (request) => { const cookie = request.headers.get("cookie"); if (!cookie) return false; const authCookie = cookie.split(";") .find(c => c.trim().startsWith("auth=")); if (!authCookie) return false; const [_, token] = authCookie.split("="); // You might want to use a more secure comparison method in production return token === ADMIN_PASSWORD; }; // New endpoint to get customer details by email if (request.method === "GET" && new URL(request.url).pathname === "/get-customer-details") { if (!isAuthenticated(request)) { return new Response(JSON.stringify({ error: "Unauthorized" }), { status: 401, headers: { "Content-Type": "application/json" }, }); } const url = new URL(request.url); const email = url.searchParams.get("email"); if (!email) { return new Response(JSON.stringify(null), { headers: { "Content-Type": "application/json" }, }); } const result = await sqlite.execute( ` SELECT first_name, last_name FROM ${KEY}_interactions_${SCHEMA_VERSION} WHERE email = ? ORDER BY timestamp DESC LIMIT 1 `, [email], ); return new Response(JSON.stringify(result.rows[0] || null), { headers: { "Content-Type": "application/json" }, }); } if (request.method === "GET" && new URL(request.url).pathname === "/get-unique-customer-count") { if (!isAuthenticated(request)) { return new Response(JSON.stringify({ error: "Unauthorized" }), { status: 401, headers: { "Content-Type": "application/json" }, }); } const result = await sqlite.execute(` SELECT COUNT(DISTINCT email) as count FROM ${KEY}_interactions_${SCHEMA_VERSION} `); return new Response( JSON.stringify({ count: result.rows[0].count }), { headers: { "Content-Type": "application/json" }, }, ); } if (request.method === "GET" && new URL(request.url).pathname === "/get-filtered-count") { if (!isAuthenticated(request)) { return new Response(JSON.stringify({ error: "Unauthorized" }), { status: 401, headers: { "Content-Type": "application/json" }, }); } const url = new URL(request.url); const tags = url.searchParams.get("tags")?.split(",") || []; if (tags.length === 0) { return new Response( JSON.stringify({ count: 0 }), { headers: { "Content-Type": "application/json" } }, ); } // Build a query that counts interactions that have ALL the specified tags const placeholders = tags.map(() => "?").join(","); const result = await sqlite.execute( ` WITH tagged_interactions AS ( SELECT interaction_id FROM ${KEY}_tags_${SCHEMA_VERSION} WHERE tag IN (${placeholders}) GROUP BY interaction_id HAVING COUNT(DISTINCT tag) = ? ) SELECT COUNT(*) as count FROM ${KEY}_interactions_${SCHEMA_VERSION} i INNER JOIN tagged_interactions ti ON i.id = ti.interaction_id `, [...tags, tags.length], ); return new Response( JSON.stringify({ count: result.rows[0].count }), { headers: { "Content-Type": "application/json" } }, ); } if (request.method === "GET" && new URL(request.url).pathname === "/check-auth") { return new Response( JSON.stringify({ authenticated: isAuthenticated(request) }), { headers: { "Content-Type": "application/json" } }, ); } if (request.method === "POST" && new URL(request.url).pathname === "/login") { const { password } = await request.json(); if (password === ADMIN_PASSWORD) { return new Response(JSON.stringify({ success: true }), { headers: { "Content-Type": "application/json", // Set cookie to expire in 30 days "Set-Cookie": `auth=${password}; Path=/; Max-Age=2592000; SameSite=Strict; HttpOnly`, }, }); } else { return new Response(JSON.stringify({ message: "Incorrect password" }), { status: 401, headers: { "Content-Type": "application/json" }, }); } } if (request.method === "POST" && new URL(request.url).pathname === "/add-interaction") { if (!isAuthenticated(request)) { return new Response(JSON.stringify({ error: "Unauthorized" }), { status: 401, headers: { "Content-Type": "application/json" }, }); } const { firstName, lastName, email, interactionDate, notes, additionalNotesLink, tags = [] } = await request.json(); try { // Insert interaction const result = await sqlite.execute( `INSERT INTO ${KEY}_interactions_${SCHEMA_VERSION} (first_name, last_name, email, interaction_date, notes, additional_notes_link) VALUES (?, ?, ?, ?, ?, ?) RETURNING id`, [firstName, lastName, email, interactionDate, notes, additionalNotesLink || null], ); const interactionId = result.rows[0].id; // Insert tags if there are any if (tags && tags.length > 0) { for (const tag of tags) { await sqlite.execute( `INSERT INTO ${KEY}_tags_${SCHEMA_VERSION} (interaction_id, tag) VALUES (?, ?)`, [interactionId, tag], ); } } return new Response(JSON.stringify({ success: true }), { headers: { "Content-Type": "application/json" }, }); } catch (error) { console.error("Error in add-interaction:", error); return new Response( JSON.stringify({ success: false, error: "Failed to save interaction", }), { status: 500, headers: { "Content-Type": "application/json" }, }, ); } } if (request.method === "GET" && new URL(request.url).pathname === "/get-interactions") { if (!isAuthenticated(request)) { return new Response(JSON.stringify({ error: "Unauthorized" }), { status: 401, headers: { "Content-Type": "application/json" }, }); } const url = new URL(request.url); const page = parseInt(url.searchParams.get("page") || "1"); const limit = 10; const offset = (page - 1) * limit; // First, get the paginated interactions const result = await sqlite.execute( ` SELECT i.*, GROUP_CONCAT(t.tag) as tags FROM ${KEY}_interactions_${SCHEMA_VERSION} i LEFT JOIN ${KEY}_tags_${SCHEMA_VERSION} t ON i.id = t.interaction_id GROUP BY i.id ORDER BY i.interaction_date DESC, i.timestamp DESC LIMIT ? OFFSET ? `, [limit, offset], ); // Second, get the total count of interactions const countResult = await sqlite.execute(` SELECT COUNT(*) as total FROM ${KEY}_interactions_${SCHEMA_VERSION} `); // Third, get the total count for each tag across ALL interactions const tagCountResult = await sqlite.execute(` SELECT tag, COUNT(DISTINCT interaction_id) as count FROM ${KEY}_tags_${SCHEMA_VERSION} GROUP BY tag `); // Convert tag counts array to an object for easier frontend consumption const tagCounts = {}; tagCountResult.rows.forEach(row => { tagCounts[row.tag] = row.count; }); return new Response( JSON.stringify({ interactions: result.rows, total: countResult.rows[0].total, tagCounts: tagCounts, // Add this to the response }), { headers: { "Content-Type": "application/json" }, }, ); } if (request.method === "DELETE" && new URL(request.url).pathname === "/delete-interaction") { if (!isAuthenticated(request)) { return new Response(JSON.stringify({ error: "Unauthorized" }), { status: 401, headers: { "Content-Type": "application/json" }, }); } const { id } = await request.json(); try { // First delete the tags associated with this interaction await sqlite.execute( `DELETE FROM ${KEY}_tags_${SCHEMA_VERSION} WHERE interaction_id = ?`, [id], ); // Then delete the interaction await sqlite.execute( `DELETE FROM ${KEY}_interactions_${SCHEMA_VERSION} WHERE id = ?`, [id], ); return new Response(JSON.stringify({ success: true }), { headers: { "Content-Type": "application/json" }, }); } catch (error) { console.error("Error deleting interaction:", error); return new Response( JSON.stringify({ success: false, error: "Failed to delete interaction", }), { status: 500, headers: { "Content-Type": "application/json" }, }, ); } } return new Response( ` CRM

View Source `, { headers: { "Content-Type": "text/html" }, }, ); } const css = ` body { font-family: 'Inter', -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, Cantarell, 'Open Sans', 'Helvetica Neue', sans-serif; max-width: 600px; margin: 0 auto; padding: 20px; background-color: #1a1a1a; color: #e0e0e0; } .login-container { display: flex; justify-content: center; align-items: center; height: 100vh; background-color: #1a1a1a; } .login-form { background-color: #2d2d2d; padding: 30px; border-radius: 8px; box-shadow: 0 4px 6px rgba(0,0,0,0.3); width: 100%; max-width: 350px; display: flex; flex-direction: column; gap: 15px; } .login-form input { padding: 10px; border: 1px solid #404040; border-radius: 4px; background-color: #333333; color: #e0e0e0; } .login-form button { background-color: #0066cc; color: white; border: none; padding: 10px; border-radius: 4px; cursor: pointer; } .error-message { color: #ff6b6b; margin: 0; text-align: center; } .crm-header { display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px; } .crm-container { background-color: #2d2d2d; border-radius: 8px; box-shadow: 0 4px 6px rgba(0,0,0,0.1); padding: 20px; } .interaction-form { display: flex; flex-direction: column; gap: 10px; margin-bottom: 20px; } .name-inputs { display: flex; gap: 10px; } .name-inputs input { flex: 1; } .interaction-form input, .interaction-form textarea { padding: 10px; border: 1px solid #404040; border-radius: 4px; background-color: #333333; color: #e0e0e0; } .interaction-form input[type="date"]::-webkit-calendar-picker-indicator { filter: invert(1); opacity: 0.7; cursor: pointer; } .interaction-form input[type="date"]::-webkit-calendar-picker-indicator:hover { opacity: 1; } .interaction-form > button { background-color: #0066cc; color: white; border: none; padding: 10px; border-radius: 4px; cursor: pointer; } .interaction-list { display: flex; flex-direction: column; gap: 15px; } .customer-interaction-group { background-color: #333333; border-radius: 4px; padding: 15px; border: 1px solid #404040; } .customer-interaction-group h3 { margin-top: 0; margin-bottom: 10px; color: #e0e0e0; } .interaction-card { margin-top: 10px; background-color: #2d2d2d; border-radius: 4px; padding: 10px; margin-bottom: 10px; box-shadow: 0 2px 4px rgba(0,0,0,0.2); } .interaction-card small { color: #a0a0a0; display: block; margin-top: 5px; } .interaction-card a { color: #5c9eff; text-decoration: none; } .interaction-card a:hover { text-decoration: underline; } .copyable-email { cursor: pointer; } .copyable-email:hover { text-decoration: underline; } .interaction-footer { display: flex; justify-content: space-between; align-items: center; margin-top: 5px; } .delete-button { cursor: pointer; } .pagination-controls { display: flex; justify-content: center; align-items: center; gap: 15px; margin-top: 20px; padding: 10px; } .pagination-button { background-color: #404040; color: #e0e0e0; border: none; padding: 8px 16px; border-radius: 4px; cursor: pointer; margin-top: 10px; } .pagination-button:disabled { background-color: #333333; color: #666666; cursor: not-allowed; } .tags-input-container { margin: 10px 0; } .tags-list { display: flex; flex-wrap: wrap; gap: 5px; margin-bottom: 5px; } .tag-input { width: 100%; padding: 8px; border: 1px solid #404040; border-radius: 4px; background-color: #333333; color: #e0e0e0; } .tag-pill { background-color: #404040; color: #e0e0e0; padding: 4px 12px; border-radius: 16px; font-size: 14px; display: inline-flex; align-items: center; margin-right: 5px; cursor: pointer; border: none; transition: all 0.2s ease; } .tag-pill:hover { background-color: #505050; } .tag-pill.selected { background-color: #0066cc; color: white; } .tag-pill.selected:hover { background-color: #0056b3; } .tag-remove { border: none; background-color: transparent; color: #fff; } .customer-conversations { margin: 0; } `;

Welcome to Your CRM

📋 Customer Conversations

Filter by Tags

🧑 {customerName}

CRM

crmtemplate