`, favicon, and body of `Root()` to\nmatch your app's existing `index.html`, and stamp anything else your shell\nreferences directly (images, css) with `immutableFileUrl()`.\n\n1. Create `frontend/root.tsx`:\n\n```tsx\n/** @jsxImportSource npm:hono@4/jsx */\nimport { raw } from \"npm:hono@4/html\";\nimport { immutableFileUrl } from \"https://esm.town/v/std/utils/index.ts\";\n\n// The HTML shell for the app. It is never cached, so immutableFileUrl can stamp\n// the current val version into each asset URL — the browser caches those\n// immutably, and publishing the val changes the URLs, refetching exactly once.\nexport function Root() {\n return (\n <>\n {raw(\"<!DOCTYPE html>\")}\n <html lang=\"en\">\n <head>\n <meta charSet=\"UTF-8\" />\n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\" />\n <title>React Hono Val Town Starter

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_275":276,"_277":278},"routes/_app.x.$handle.$name",{"_11":12,"_13":14,"_15":16,"_18":19,"_17":-7,"_207":-5,"_20":208,"_22":209,"_210":211,"_212":213,"_26":214,"_24":215,"_216":-5,"_217":-5,"_218":61,"_219":220,"_221":58,"_222":-5,"_223":-5,"_224":225},"routes/_app.x.$handle.$name.branch.$branch.code.$",{"_11":12,"_13":14,"_15":16,"_17":-7,"_18":19,"_20":21,"_22":23,"_24":25,"_26":27,"_28":-5,"_29":-5,"_30":-5,"_31":-5,"_32":-5},"handle","shubhamsingh","name","europe-careers-crm","branchName","main","version","path","index.ts","project",{"_33":148,"_13":14,"_45":149,"_150":-5,"_151":61,"_152":153,"_154":153,"_155":156,"_157":158,"_159":160,"_161":38,"_162":58,"_163":-5,"_164":-5,"_165":80,"_166":167,"_168":169,"_170":171,"_172":173,"_174":-5,"_175":176,"_177":176,"_178":179,"_180":179,"_181":179,"_182":176,"_183":176,"_184":185,"_186":187,"_188":189,"_190":61},"tree",[52,53,54,55,56],"file",{"_33":34,"_35":36,"_37":38,"_13":19,"_39":40,"_41":42,"_17":43,"_44":-5,"_45":46,"_47":48,"_49":50,"_18":51},"contents",[],"upstream","pull","ahead","behind","envVars","id","d883e019-80ee-40cd-aa97-4b901b0d3637","branchId","398d1cc9-8d86-4c4c-b719-baeac445feeb","fileId","01a08a70-b0f3-7058-9d1e-0aa6e04fe885","content","import { parseVal, serveImmutableFile } from \"https://esm.town/v/std/utils/index.ts\";\nimport { Hono } from \"npm:hono\";\nimport * as XLSX from \"npm:xlsx\";\nimport { Root } from \"./frontend/root.tsx\";\nimport { initDb, sqlite } from \"./db.ts\";\n\nconst app = new Hono();\nawait initDb();\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS activity_log (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER,lead_id INTEGER,action TEXT NOT NULL,details TEXT DEFAULT '',created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP)`);\nconst logActivity=async(user:any,action:string,leadId:number|null=null,details:string=\"\")=>{try{await sqlite.execute({sql:`INSERT INTO activity_log(user_id,lead_id,action,details,created_at) VALUES(?,?,?,?,datetime('now'))`,args:[Number(user?.user_id)||null,leadId,action,details]})}catch{}};\nconst STATUSES=[\"New\",\"Contacted\",\"Interested\",\"Follow-Up\",\"Qualified\",\"Converted\",\"Not Interested\",\"Irrelevant\",\"Not Reachable\",\"Lost\"];\nconst nowParts=()=>new Intl.DateTimeFormat(\"en-CA\",{timeZone:\"Asia/Kolkata\",year:\"numeric\",month:\"2-digit\",day:\"2-digit\",hour:\"2-digit\",minute:\"2-digit\",hour12:false}).formatToParts(new Date()).reduce((a:any,p)=>({...a,[p.type]:p.value}),{});\nconst todayIST=()=>{const p=nowParts();return `${p.year}-${p.month}-${p.day}`};\nconst timeIST=()=>{const p=nowParts();return `${p.hour}:${p.minute}`};\nconst nowIST=()=>`${todayIST()}T${timeIST()}:00+05:30`;\nconst validTime=(t:string)=>/^([01]\\d|2[0-3]):[0-5]\\d$/.test(t)&&t>=\"09:00\"&&t<=\"18:00\";const validPhone=(p:string)=>/^\\+[0-9]+$/.test(p.trim());\nconst futureSchedule=(d:string,t:string)=>{const td=todayIST(),tm=timeIST();return d>td||(d===td&&t>=tm)};const within15Days=(d:string,t:string)=>{const td=todayIST(),max=new Date(`${td}T00:00:00+05:30`);max.setDate(max.getDate()+15);return new Date(`${d}T${t}:00+05:30`)<=max};\nconst token=()=>crypto.randomUUID()+crypto.randomUUID();\nasync function auth(c:any,next:any){const sid=c.req.header(\"X-Session-ID\");if(!sid)return c.json({error:\"Authentication required\"},401);const r=await sqlite.execute({sql:`SELECT s.user_id,u.name,u.email,u.role,u.status FROM sessions s JOIN users u ON u.id=s.user_id WHERE s.token=? AND u.status='active' LIMIT 1`,args:[sid]});if(!r.rows[0])return c.json({error:\"Session expired. Please login again.\"},401);c.set(\"user\",r.rows[0]);return next()}\nconst admin=(c:any)=>c.get(\"user\")?.role===\"super_admin\";\napp.get(\"/\",c=>c.html(Root()));app.get(\"/__immutable/*\",c=>serveImmutableFile(c.req.path));app.get(\"/source\",c=>c.redirect(parseVal().links.self.val));\napp.post(\"/api/login\",async c=>{const b=await c.req.json();const id=String(b.identifier||\"\").trim().toLowerCase();const r=await sqlite.execute({sql:`SELECT id,name,email,role,status FROM users WHERE lower(email)=? AND password=? AND status='active' LIMIT 1`,args:[id,String(b.password||\"\")]});if(!r.rows[0])return c.json({error:\"Invalid login credentials\"},401);const sid=token();await sqlite.execute({sql:`CREATE TABLE IF NOT EXISTS sessions(token TEXT PRIMARY KEY,user_id INTEGER NOT NULL,created_at TEXT NOT NULL)`});await sqlite.execute({sql:`INSERT INTO sessions(token,user_id,created_at) VALUES(?,?,?)`,args:[sid,Number((r.rows[0] as any).id),nowIST()]});return c.json({user:r.rows[0],sessionId:sid})});\napp.post(\"/api/logout\",auth,async c=>{const sid=c.req.header(\"X-Session-ID\");await sqlite.execute({sql:`DELETE FROM sessions WHERE token=?`,args:[sid]});return c.json({ok:true})});\napp.get(\"/api/me\",auth,c=>c.json({user:c.get(\"user\"),today:todayIST(),time:timeIST()}));\napp.post(\"/api/admin/change-password\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const b=await c.req.json(),current=String(b.current_password||\"\"),next=String(b.new_password||\"\");if(next.length<12)return c.json({error:\"New password must be at least 12 characters\"},400);if(next===current)return c.json({error:\"New password must be different from the current password\"},400);const u=c.get(\"user\");const r=await sqlite.execute({sql:`SELECT id FROM users WHERE id=? AND password=? AND role='super_admin' AND status='active' LIMIT 1`,args:[Number(u.user_id),current]});if(!r.rows[0])return c.json({error:\"Current password is incorrect\"},400);await sqlite.execute({sql:`UPDATE users SET password=? WHERE id=? AND role='super_admin'`,args:[next,Number(u.user_id)]});return c.json({ok:true,message:\"Password changed successfully. Use the new password next time you sign in.\"})});\napp.get(\"/api/counsellors\",auth,async c=>{const u=c.get(\"user\");const r=u.role==='super_admin'?await sqlite.execute(`SELECT id,name,email,role,status,created_at FROM users WHERE role='counsellor' ORDER BY name`):await sqlite.execute({sql:`SELECT id,name,email,role,status,created_at FROM users WHERE id=? AND role='counsellor'`,args:[Number(u.user_id)]});return c.json({items:r.rows})});\napp.post(\"/api/counsellors\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const b=await c.req.json();if(!b.name||!b.email||!b.password)return c.json({error:\"Name, email and password required\"},400);try{await sqlite.execute({sql:`INSERT INTO users(name,email,password,role) VALUES(?,?,?,'counsellor')`,args:[b.name.trim(),b.email.trim().toLowerCase(),b.password]});return c.json({ok:true})}catch{return c.json({error:\"Email already exists\"},400)}});\napp.put(\"/api/counsellors/:id\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const id=Number(c.req.param(\"id\")),b=await c.req.json(),name=String(b.name||\"\").trim(),email=String(b.email||\"\").trim().toLowerCase(),password=String(b.password||\"\");if(!name||!email)return c.json({error:\"Name and email are required\"},400);const exists=await sqlite.execute({sql:`SELECT id FROM users WHERE lower(email)=? AND id<>? LIMIT 1`,args:[email,id]});if(exists.rows[0])return c.json({error:\"Email already exists\"},400);const status=b.status===\"inactive\"?\"inactive\":\"active\";try{const result=password?await sqlite.execute({sql:`UPDATE users SET name=?,email=?,password=?,status=? WHERE id=? AND role='counsellor'`,args:[name,email,password,status,id]}):await sqlite.execute({sql:`UPDATE users SET name=?,email=?,status=? WHERE id=? AND role='counsellor'`,args:[name,email,status,id]});if(Number(result.rowsAffected||0)!==1)return c.json({error:\"Counsellor not found or could not be updated\"},404);const updated=await sqlite.execute({sql:`SELECT id,name,email,role,status,created_at FROM users WHERE id=? AND role='counsellor' LIMIT 1`,args:[id]});return c.json({ok:true,user:updated.rows[0]})}catch{return c.json({error:\"Could not update counsellor\"},400)}});\napp.delete(\"/api/counsellors/:id\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const id=Number(c.req.param(\"id\"));const x=await sqlite.execute({sql:`SELECT COUNT(*) c FROM leads WHERE counsellor_id=?`,args:[id]});if(Number((x.rows[0] as any).c)>0)return c.json({error:\"Reassign all leads before deleting this counsellor\"},400);await sqlite.execute({sql:`DELETE FROM users WHERE id=? AND role='counsellor'`,args:[id]});return c.json({ok:true})});\napp.get(\"/api/leads\",auth,async c=>{const q=c.req.query(),u=c.get(\"user\"),p:any[]=[],w:string[]=[];if(u.role!==\"super_admin\"){w.push(`l.counsellor_id=?`);p.push(Number(u.user_id))}if(q.search){w.push(`(l.candidate_name LIKE ? OR l.ec_id LIKE ? OR l.phone LIKE ? OR l.email LIKE ?)`);const s=`%${q.search}%`;p.push(s,s,s,s)}for(const [k,col] of [[\"country\",\"l.country\"],[\"program\",\"l.program\"],[\"counsellor\",\"l.counsellor_id\"],[\"status\",\"l.status\"]] as any[])if(q[k]){w.push(`${col}=?`);p.push(q[k])}const page=Math.max(1,Number(q.page)||1),limit=Math.min(100,Math.max(10,Number(q.limit)||50)),where=w.length?`WHERE ${w.join(' AND ')}`:\"\";const total=await sqlite.execute({sql:`SELECT COUNT(*) total FROM leads l ${where}`,args:p});const r=await sqlite.execute({sql:`SELECT l.*,u.name counsellor_name,(SELECT scheduled_date||' '||scheduled_time FROM follow_ups f WHERE f.lead_id=l.id AND f.status='pending' ORDER BY f.scheduled_date,f.scheduled_time LIMIT 1) next_followup,(SELECT scheduled_date||' '||scheduled_time FROM follow_ups f WHERE f.lead_id=l.id AND f.status='completed' ORDER BY f.id DESC LIMIT 1) last_followup,(SELECT note FROM follow_ups f WHERE f.lead_id=l.id AND f.status='completed' ORDER BY f.id DESC LIMIT 1) latest_note FROM leads l LEFT JOIN users u ON u.id=l.counsellor_id ${where} ORDER BY l.created_at DESC,l.id DESC LIMIT ? OFFSET ?`,args:[...p,limit,(page-1)*limit]});const n=Number((total.rows[0] as any)?.total||0);return c.json({items:r.rows,page,limit,total:n,totalPages:Math.max(1,Math.ceil(n/limit))})});\napp.post(\"/api/leads\",auth,async c=>{const u=c.get(\"user\");const b=await c.req.json(),candidateName=String(b.candidate_name||\"\").trim(),phone=String(b.phone||\"\").trim(),status=String(b.status||\"New\"),cid=u.role==='super_admin'?Number(b.counsellor_id||0):Number(u.user_id),fd=String(b.first_followup_date||\"\"),ft=String(b.first_followup_time||\"\"),fn=String(b.first_followup_note||\"\").trim();if(!candidateName||!phone||phone===\"+91\"||!validPhone(phone))return c.json({error:\"Candidate Name and a valid phone number are required. Phone may contain only + and numbers.\"},400);if(!STATUSES.includes(status))return c.json({error:\"Invalid status\"},400);const terminal=[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(status);if(!terminal){if(!cid)return c.json({error:\"Counsellor and Next Follow-Up are required for every active lead\"},400);if(!/^\\d{4}-\\d{2}-\\d{2}$/.test(fd)||!ft||!validTime(ft))return c.json({error:\"Counsellor and Next Follow-Up Date/Time are required. Time must be 09:00–18:00 IST\"},400);if(fn.length<2)return c.json({error:\"What Happened Today? is required for the first follow-up\"},400);const td=todayIST(),tm=timeIST();if(fd{const raw=await c.req.json(),id=Number(c.req.param(\"id\")),u=c.get(\"user\"),candidateName=String(raw.candidate_name||\"\").trim(),phone=String(raw.phone||\"\").trim();if(u.role!==\"super_admin\")return c.json({error:\"Counsellors cannot edit lead records. Use the calling workflow only.\"},403);if(!candidateName||!phone||phone===\"+91\"||!validPhone(phone))return c.json({error:\"Candidate Name and a valid phone number are required. Phone may contain only + and numbers.\"},400);const own=await sqlite.execute({sql:`SELECT counsellor_id,ec_id FROM leads WHERE id=?`,args:[id]});if(!own.rows[0])return c.json({error:\"Lead not found\"},404);if(u.role!==\"super_admin\"&&Number((own.rows[0] as any).counsellor_id)!==Number(u.user_id))return c.json({error:\"You can only edit assigned leads\"},403);const ecId=String(raw.ec_id||\"\").trim()||String((own.rows[0] as any).ec_id||\"\")||`EC-${crypto.randomUUID().slice(0,6).toUpperCase()}`;if(!STATUSES.includes(raw.status))return c.json({error:\"Invalid status\"},400);const terminal=[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(String(raw.status));if(!terminal){const fd=String(raw.first_followup_date||\"\"),ft=String(raw.first_followup_time||\"\");if(/^\\d{4}-\\d{2}-\\d{2}$/.test(fd)&&validTime(ft)&&!within15Days(fd,ft))return c.json({error:\"Next Follow-Up cannot be scheduled more than 15 days ahead\"},400);if(!raw.counsellor_id||!/^\\d{4}-\\d{2}-\\d{2}$/.test(fd)||!validTime(ft)||!futureSchedule(fd,ft)||String(raw.first_followup_note||\"\").trim().length<2){const pending=await sqlite.execute({sql:`SELECT id FROM follow_ups WHERE lead_id=? AND status='pending' LIMIT 1`,args:[id]});if(!pending.rows[0])return c.json({error:\"Active leads must have a pending Follow-Up. Schedule the next call before saving this status.\"},400)}}await sqlite.execute({sql:`UPDATE leads SET candidate_name=?,ec_id=?,phone=?,email=?,country=?,program=?,counsellor_id=?,status=?,lead_type=?,dob=?,current_city=?,qualification=?,experience=?,language_level=?,preferred_country=?,source=?,lead_temperature=?,notes=?,updated_at=datetime('now') WHERE id=?`,args:[candidateName,ecId,phone,raw.email||\"\",raw.country||\"\",raw.program||\"\",raw.counsellor_id?Number(raw.counsellor_id):null,raw.status,raw.lead_type||\"\",raw.dob||\"\",raw.current_city||\"\",raw.qualification||\"\",raw.experience||\"\",raw.language_level||\"\",raw.preferred_country||\"\",raw.source||\"CRM\",raw.lead_temperature||\"Cold\",raw.notes||\"\",id]});if(terminal)await sqlite.execute({sql:`DELETE FROM follow_ups WHERE lead_id=? AND status='pending'`,args:[id]});await logActivity(u,'Lead updated',id,`${candidateName} · ${ecId} · ${raw.status}`);return c.json({ok:true,ec_id:ecId})});\napp.patch(\"/api/leads/:id/status\",auth,async c=>{const id=Number(c.req.param(\"id\")),b=await c.req.json(),u=c.get(\"user\"),status=String(b.status||\"\");if(!STATUSES.includes(status))return c.json({error:\"Invalid lead status\"},400);const r=await sqlite.execute({sql:`SELECT id,counsellor_id,candidate_name,ec_id FROM leads WHERE id=? LIMIT 1`,args:[id]});if(!r.rows[0])return c.json({error:\"Lead not found\"},404);const lead=r.rows[0] as any;if(u.role!==\"super_admin\"&&Number(lead.counsellor_id)!==Number(u.user_id))return c.json({error:\"You can only update status for assigned leads\"},403);const terminal=[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(status);await sqlite.execute({sql:`UPDATE leads SET status=?,updated_at=datetime('now') WHERE id=?`,args:[status,id]});if(terminal)await sqlite.execute({sql:`DELETE FROM follow_ups WHERE lead_id=? AND status='pending'`,args:[id]});await logActivity(u,'Lead status updated',id,`${lead.candidate_name} · ${lead.ec_id} · ${status}${terminal?' · no further follow-up':''}`);return c.json({ok:true,status})});\napp.delete(\"/api/leads/:id\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const id=Number(c.req.param(\"id\"));const exists=await sqlite.execute({sql:`SELECT id FROM leads WHERE id=? LIMIT 1`,args:[id]});if(!exists.rows[0])return c.json({error:\"Lead not found\"},404);await sqlite.batch([{sql:`DELETE FROM follow_ups WHERE lead_id=?`,args:[id]},{sql:`DELETE FROM leads WHERE id=?`,args:[id]}]);await logActivity(c.get('user'),'Lead deleted',id,`Lead ${id} and all associated follow-up history deleted by Super Admin`);return c.json({ok:true})});\napp.get(\"/api/leads/:id\",auth,async c=>{const id=Number(c.req.param(\"id\")),u=c.get(\"user\");const a=await sqlite.execute({sql:`SELECT l.*,u.name counsellor_name,(SELECT scheduled_date||' '||scheduled_time FROM follow_ups f WHERE f.lead_id=l.id AND f.status='pending' ORDER BY f.scheduled_date,f.scheduled_time LIMIT 1) next_followup FROM leads l LEFT JOIN users u ON u.id=l.counsellor_id WHERE l.id=?`,args:[id]});if(!a.rows[0])return c.json({error:\"Lead not found\"},404);if(u.role!==\"super_admin\"&&Number((a.rows[0] as any).counsellor_id)!==Number(u.user_id))return c.json({error:\"Access denied\"},403);const h=await sqlite.execute({sql:`SELECT f.*,u.name counsellor_name FROM follow_ups f JOIN users u ON u.id=f.counsellor_id WHERE f.lead_id=? ORDER BY f.id DESC`,args:[id]});return c.json({lead:a.rows[0],history:h.rows})});\napp.post(\"/api/leads/:id/follow-up-call\",auth,async c=>{const id=Number(c.req.param(\"id\")),b=await c.req.json(),u=c.get(\"user\"),note=String(b.note||\"\").trim(),nd=String(b.next_followup_date||\"\"),nt=String(b.next_followup_time||\"\"),leadStatus=String(b.lead_status||\"\");if(note.length<2)return c.json({error:\"What Happened Today is required\"},400);if(leadStatus&&!STATUSES.includes(leadStatus))return c.json({error:\"Invalid lead status\"},400);if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&(!nd||!nt||!validTime(nt)))return c.json({error:\"Next Follow-Up Date and Time are mandatory unless status is Not Interested or Irrelevant. Time must be 09:00–18:00 IST\"},400);if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&!futureSchedule(nd,nt))return c.json({error:\"Next Follow-Up cannot be scheduled in the past\"},400);if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&!within15Days(nd,nt))return c.json({error:\"Next Follow-Up cannot be scheduled more than 15 days ahead\"},400);if([\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&(nd||nt))return c.json({error:\"No further follow-up is allowed when status is Not Interested\"},400);const lead=await sqlite.execute({sql:`SELECT * FROM leads WHERE id=? LIMIT 1`,args:[id]});if(!lead.rows[0])return c.json({error:\"Lead not found\"},404);const l=lead.rows[0] as any;if(u.role!==\"super_admin\"&&Number(l.counsellor_id)!==Number(u.user_id))return c.json({error:\"You can only add follow-ups for assigned leads\"},403);const cid=Number(l.counsellor_id)||Number(u.user_id),d=todayIST(),t=timeIST();const pending=await sqlite.execute({sql:`SELECT * FROM follow_ups WHERE lead_id=? AND status='pending' ORDER BY scheduled_date,scheduled_time LIMIT 1`,args:[id]});const current=pending.rows[0] as any;const stm:any[]=[];if(current){const scheduledAt=`${current.scheduled_date}T${current.scheduled_time}:00+05:30`;if(Date.parse(scheduledAt)>Date.parse(nowIST()))return c.json({error:\"This follow-up is scheduled for the future and cannot be completed yet.\"},400);stm.push({sql:`UPDATE follow_ups SET completed_at=?,note=?,next_followup_date=?,next_followup_time=?,status='completed' WHERE id=?`,args:[nowIST(),note,[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?null:nd||null,[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?null:nt||null,Number(current.id)]})}else{stm.push({sql:`INSERT INTO follow_ups(lead_id,counsellor_id,scheduled_date,scheduled_time,completed_at,note,next_followup_date,next_followup_time,status,created_at) VALUES(?,?,?,?,?,?,?,?, 'completed',datetime('now'))`,args:[id,cid,d,t,nowIST(),note,[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?null:nd||null,[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?null:nt||null]})}if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus))stm.push({sql:`INSERT INTO follow_ups(lead_id,counsellor_id,scheduled_date,scheduled_time,status,created_at) VALUES(?,?,?,?, 'pending',datetime('now'))`,args:[id,cid,nd,nt]});stm.push({sql:`UPDATE leads SET counsellor_id=?,status=COALESCE(NULLIF(?,''),status),updated_at=datetime('now') WHERE id=?`,args:[cid,leadStatus,id]});await sqlite.batch(stm);await logActivity(u,'Follow-up call recorded',id,`${d} ${t} · status ${leadStatus||l.status}${[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?' · no further follow-up':nd?` · next ${nd} ${nt}`:''}`);return c.json({ok:true,called_date:d,called_time:t})});\napp.post(\"/api/follow-ups\",auth,async c=>{const b=await c.req.json(),u=c.get(\"user\"),d=String(b.scheduled_date||\"\"),t=String(b.scheduled_time||\"\");if(!b.lead_id||!/^\\d{4}-\\d{2}-\\d{2}$/.test(d)||!validTime(t))return c.json({error:\"Valid date and time (09:00–18:00 IST) are required\"},400);if(!futureSchedule(d,t))return c.json({error:\"Follow-Up cannot be scheduled in the past\"},400);if(!within15Days(d,t))return c.json({error:\"Follow-Up cannot be scheduled more than 15 days ahead\"},400);const lead=await sqlite.execute({sql:`SELECT counsellor_id,status FROM leads WHERE id=?`,args:[Number(b.lead_id)]});if(!lead.rows[0])return c.json({error:\"Lead not found\"},404);const leadRow=lead.rows[0] as any;if([\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(String(leadRow.status||\"\")))return c.json({error:\"No follow-up is allowed for a closed lead\"},400);const existing=await sqlite.execute({sql:`SELECT id FROM follow_ups WHERE lead_id=? AND status='pending' LIMIT 1`,args:[Number(b.lead_id)]});if(existing.rows[0])return c.json({error:\"This lead already has a pending follow-up. Complete it before scheduling another.\"},400);const cid=admin(c)?Number(b.counsellor_id||0):Number(u.user_id);if(!cid)return c.json({error:\"Counsellor is required\"},400);if(!admin(c)&&Number(leadRow.counsellor_id)!==Number(u.user_id))return c.json({error:\"You can only schedule follow-ups for assigned leads\"},403);await sqlite.execute({sql:`INSERT INTO follow_ups(lead_id,counsellor_id,scheduled_date,scheduled_time,status,created_at) VALUES(?,?,?,?, 'pending',datetime('now'))`,args:[Number(b.lead_id),cid,d,t]});await sqlite.execute({sql:`UPDATE leads SET counsellor_id=?,updated_at=datetime('now') WHERE id=?`,args:[cid,Number(b.lead_id)]});return c.json({ok:true})});\napp.post(\"/api/follow-ups/:id/complete\",auth,async c=>{const id=Number(c.req.param(\"id\")),b=await c.req.json(),u=c.get(\"user\"),note=String(b.note||\"\").trim(),nd=String(b.next_followup_date||\"\"),nt=String(b.next_followup_time||\"\"),leadStatus=String(b.lead_status||\"\");if(note.length<2)return c.json({error:\"Follow-Up Notes are required\"},400);if(leadStatus&&!STATUSES.includes(leadStatus))return c.json({error:\"Invalid lead status\"},400);if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&(!nd||!nt||!validTime(nt)))return c.json({error:\"Next Follow-Up Date and Time are mandatory unless status is Not Interested or Irrelevant\"},400);if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&!futureSchedule(nd,nt))return c.json({error:\"Next Follow-Up cannot be scheduled in the past\"},400);if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&!within15Days(nd,nt))return c.json({error:\"Next Follow-Up cannot be scheduled more than 15 days ahead\"},400);if([\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)&&(nd||nt))return c.json({error:\"No further follow-up is allowed when status is Not Interested\"},400);const r=await sqlite.execute({sql:`SELECT * FROM follow_ups WHERE id=? AND status='pending' LIMIT 1`,args:[id]});if(!r.rows[0])return c.json({error:\"Follow-up is missing or already completed\"},404);const f=r.rows[0] as any;if(u.role!==\"super_admin\"&&Number(f.counsellor_id)!==Number(u.user_id))return c.json({error:\"You can only complete your own follow-ups\"},403);const scheduledAt=`${f.scheduled_date}T${f.scheduled_time}:00+05:30`,scheduledMs=Date.parse(scheduledAt),nowMs=Date.parse(nowIST());if(Number.isFinite(scheduledMs)&&scheduledMs>nowMs)return c.json({error:\"This follow-up is scheduled for the future and cannot be completed yet.\"},400);const stm:any[]=[{sql:`UPDATE follow_ups SET completed_at=?,note=?,next_followup_date=?,next_followup_time=?,status='completed' WHERE id=?`,args:[nowIST(),note,[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?null:nd||null,[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?null:nt||null,id]},{sql:`UPDATE leads SET status=COALESCE(NULLIF(?,''),status),updated_at=datetime('now') WHERE id=?`,args:[leadStatus,Number(f.lead_id)]}];if(![\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus))stm.push({sql:`INSERT INTO follow_ups(lead_id,counsellor_id,scheduled_date,scheduled_time,status,created_at) VALUES(?,?,?,?, 'pending',datetime('now'))`,args:[f.lead_id,f.counsellor_id,nd,nt]});await sqlite.batch(stm);await logActivity(u,'Follow-up completed',Number(f.lead_id),`${f.scheduled_date} ${f.scheduled_time} · status ${leadStatus||'unchanged'}${[\"Converted\",\"Lost\",\"Not Interested\",\"Irrelevant\"].includes(leadStatus)?' · no further follow-up':` · next ${nd} ${nt}`}`);return c.json({ok:true})});\napp.get(\"/api/follow-ups\",auth,async c=>{const q=c.req.query(),u=c.get(\"user\"),p:any[]=[],w:string[]=[],today=todayIST(),now=timeIST(),mode=q.mode||\"today\";if(u.role!==\"super_admin\"){w.push(`f.counsellor_id=?`);p.push(Number(u.user_id))}if(mode==='today'){w.push(`f.scheduled_date=? AND f.status='pending'`);p.push(today)}else if(mode==='overdue'){w.push(`f.status='pending' AND (f.scheduled_date? OR (f.scheduled_date=? AND f.scheduled_time>=?))`);p.push(today,today,now)}else if(mode==='completed')w.push(`f.status='completed'`);else w.push(`1=1`);if(q.counsellor){w.push(`f.counsellor_id=?`);p.push(q.counsellor)}if(q.candidate){w.push(`(l.candidate_name LIKE ? OR l.ec_id LIKE ? OR l.phone LIKE ?)`);p.push(`%${q.candidate}%`,`%${q.candidate}%`,`%${q.candidate}%`)}const r=await sqlite.execute({sql:`SELECT f.*,l.candidate_name,l.ec_id,l.phone,l.country,l.program,l.status lead_status,u.name counsellor_name FROM follow_ups f JOIN leads l ON l.id=f.lead_id JOIN users u ON u.id=f.counsellor_id WHERE ${w.join(' AND ')} ORDER BY f.scheduled_date ASC,f.scheduled_time ASC,f.id DESC`,args:p});return c.json({items:r.rows,today,now})});\nconst csvCell=(v:any)=>`\"${String(v??\"\").replace(/\"/g,'\"\"')}\"`;\napp.get(\"/api/admin/export/leads.csv\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const r=await sqlite.execute(`SELECT l.*,u.name counsellor_name FROM leads l LEFT JOIN users u ON u.id=l.counsellor_id ORDER BY l.id ASC`);const rows:any[]=r.rows as any[];const header=[\"ID\",\"EC ID\",\"Candidate Name\",\"Phone\",\"Email\",\"Country\",\"Program\",\"Counsellor\",\"Status\",\"Lead Type\",\"Current City\",\"Qualification\",\"Experience\",\"Language Level\",\"Preferred Country\",\"Source\",\"Lead Temperature\",\"Notes\",\"Created At\",\"Updated At\"];const lines=[header.map(csvCell).join(\",\"),...rows.map(l=>[l.id,l.ec_id,l.candidate_name,l.phone,l.email,l.country,l.program,l.counsellor_name,l.status,l.lead_type,l.current_city,l.qualification,l.experience,l.language_level,l.preferred_country,l.source,l.lead_temperature,l.notes,l.created_at,l.updated_at].map(csvCell).join(\",\"))];return new Response(\"\\\\uFEFF\"+lines.join(\"\\\\r\\\\n\"),{headers:{\"Content-Type\":\"text/csv; charset=utf-8\",\"Content-Disposition\":\"attachment; filename=\\\"europe-careers-leads-\"+todayIST()+\".csv\\\"\"}})});\napp.get(\"/api/admin/backup.json\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const [leads,followUps,users,activity]=await Promise.all([sqlite.execute(`SELECT * FROM leads ORDER BY id ASC`),sqlite.execute(`SELECT * FROM follow_ups ORDER BY id ASC`),sqlite.execute(`SELECT id,name,email,role,status,created_at FROM users ORDER BY id ASC`),sqlite.execute(`SELECT * FROM activity_log ORDER BY id ASC`)]);return new Response(JSON.stringify({backup_version:1,exported_at:nowIST(),timezone:\"Asia/Kolkata\",leads:leads.rows,follow_ups:followUps.rows,users:users.rows,activity_log:activity.rows},null,2),{headers:{\"Content-Type\":\"application/json; charset=utf-8\",\"Content-Disposition\":\"attachment; filename=\\\"europe-careers-crm-backup-\"+todayIST()+\".json\\\"\"}})});\napp.get(\"/api/admin/export/leads.xlsx\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const [leads,followUps,activity,users]=await Promise.all([sqlite.execute(`SELECT l.*,u.name counsellor_name FROM leads l LEFT JOIN users u ON u.id=l.counsellor_id ORDER BY l.id ASC`),sqlite.execute(`SELECT f.*,l.candidate_name,l.ec_id,u.name counsellor_name FROM follow_ups f JOIN leads l ON l.id=f.lead_id JOIN users u ON u.id=f.counsellor_id ORDER BY f.id ASC`),sqlite.execute(`SELECT a.*,u.name user_name,l.candidate_name,l.ec_id FROM activity_log a LEFT JOIN users u ON u.id=a.user_id LEFT JOIN leads l ON l.id=a.lead_id ORDER BY a.id ASC`),sqlite.execute(`SELECT id,name,email,role,status,created_at FROM users WHERE role='counsellor' ORDER BY id ASC`)]);const leadRows=(leads.rows as any[]).map(l=>({\"ID\":l.id,\"EC ID\":l.ec_id,\"Candidate Name\":l.candidate_name,\"Phone\":l.phone,\"Email\":l.email,\"Country\":l.country,\"Program\":l.program,\"Counsellor\":l.counsellor_name||\"\",\"Status\":l.status,\"Lead Type\":l.lead_type,\"Current City\":l.current_city,\"Qualification\":l.qualification,\"Experience\":l.experience,\"Language Level\":l.language_level,\"Preferred Country\":l.preferred_country,\"Source\":l.source,\"Lead Temperature\":l.lead_temperature,\"Notes\":l.notes,\"Created At\":l.created_at,\"Updated At\":l.updated_at}));const followRows=(followUps.rows as any[]).map(f=>({\"ID\":f.id,\"Lead ID\":f.lead_id,\"EC ID\":f.ec_id,\"Candidate Name\":f.candidate_name,\"Counsellor\":f.counsellor_name,\"Scheduled Date\":f.scheduled_date,\"Scheduled Time\":f.scheduled_time,\"Status\":f.status,\"Completed At\":f.completed_at||\"\",\"Note\":f.note||\"\",\"Next Follow-Up Date\":f.next_followup_date||\"\",\"Next Follow-Up Time\":f.next_followup_time||\"\",\"Created At\":f.created_at}));const activityRows=(activity.rows as any[]).map(a=>({\"ID\":a.id,\"Time\":a.created_at,\"User\":a.user_name||\"System\",\"Action\":a.action,\"Lead\":a.candidate_name||\"\",\"EC ID\":a.ec_id||\"\",\"Details\":a.details||\"\"}));const counsellorRows=(users.rows as any[]).map(u=>({\"ID\":u.id,\"Name\":u.name,\"Email\":u.email,\"Status\":u.status,\"Created At\":u.created_at}));const wb=XLSX.utils.book_new();const add=(name:string,rows:any[],widths:number[])=>{const ws=XLSX.utils.json_to_sheet(rows);ws[\"!freeze\"]={xSplit:0,ySplit:1};ws[\"!autofilter\"]={ref:ws[\"!ref\"]||\"A1:A1\"};ws[\"!cols\"]=widths.map(w=>({wch:w}));XLSX.utils.book_append_sheet(wb,ws,name)};add(\"Leads\",leadRows,[8,18,24,16,28,16,24,22,18,14,20,24,18,18,22,16,16,45,22,22]);add(\"Follow-Up History\",followRows,[8,10,18,24,22,16,16,14,24,45,20,20,22]);add(\"Activity Log\",activityRows,[8,22,20,28,24,18,60]);add(\"Counsellors\",counsellorRows,[8,24,32,14,22]);const buf=XLSX.write(wb,{type:\"array\",bookType:\"xlsx\"});return new Response(buf,{headers:{\"Content-Type\":\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\",\"Content-Disposition\":\"attachment; filename=\\\"europe-careers-crm-\"+todayIST()+\".xlsx\\\"\"}})});\napp.get(\"/api/admin/insights\",auth,async c=>{if(!admin(c))return c.json({error:\"Admin access required\"},403);const today=todayIST(),now=timeIST();const r=await sqlite.execute({sql:`SELECT (SELECT COUNT(*) FROM leads WHERE counsellor_id IS NULL) unassigned,(SELECT COUNT(*) FROM leads l WHERE l.status NOT IN ('Converted','Lost','Not Interested','Irrelevant') AND NOT EXISTS(SELECT 1 FROM follow_ups f WHERE f.lead_id=l.id AND f.status='pending')) missing_followups,(SELECT COUNT(*) FROM leads l WHERE l.status NOT IN ('Converted','Lost','Not Interested','Irrelevant') AND julianday(datetime('now','+05:30'))-julianday(datetime(l.updated_at,'+05:30'))>=3) stale_3_days,(SELECT COUNT(*) FROM leads l WHERE l.status NOT IN ('Converted','Lost','Not Interested','Irrelevant') AND julianday(datetime('now','+05:30'))-julianday(datetime(l.updated_at,'+05:30'))>=7) stale_7_days,(SELECT COUNT(*) FROM leads WHERE status='Converted') converted,(SELECT COUNT(*) FROM leads WHERE status!='New') contacted,(SELECT COUNT(*) FROM activity_log WHERE date(datetime(created_at,'+05:30'))=date(datetime('now','+05:30'))) activities_today`});const missing=await sqlite.execute({sql:`SELECT l.id,l.candidate_name,l.ec_id,l.status,l.updated_at,u.name counsellor_name FROM leads l LEFT JOIN users u ON u.id=l.counsellor_id WHERE l.status NOT IN ('Converted','Lost','Not Interested','Irrelevant') AND NOT EXISTS(SELECT 1 FROM follow_ups f WHERE f.lead_id=l.id AND f.status='pending') ORDER BY l.updated_at ASC LIMIT 100`});const stale=await sqlite.execute({sql:`SELECT l.id,l.candidate_name,l.ec_id,l.status,l.updated_at,u.name counsellor_name FROM leads l LEFT JOIN users u ON u.id=l.counsellor_id WHERE l.status NOT IN ('Converted','Lost','Not Interested','Irrelevant') AND julianday(datetime('now','+05:30'))-julianday(datetime(l.updated_at,'+05:30'))>=3 ORDER BY l.updated_at ASC LIMIT 100`});const activity=await sqlite.execute({sql:`SELECT a.id,a.action,a.details,a.created_at,u.name user_name,l.candidate_name FROM activity_log a LEFT JOIN users u ON u.id=a.user_id LEFT JOIN leads l ON l.id=a.lead_id ORDER BY a.id DESC LIMIT 30`});const perf=await sqlite.execute({sql:`SELECT u.id,u.name,(SELECT COUNT(*) FROM leads l WHERE l.counsellor_id=u.id) assigned,(SELECT COUNT(*) FROM follow_ups f WHERE f.counsellor_id=u.id AND f.status='pending' AND f.scheduled_date=?) due,(SELECT COUNT(*) FROM follow_ups f WHERE f.counsellor_id=u.id AND f.status='completed' AND date(datetime(f.completed_at,'+05:30'))=date(datetime('now','+05:30'))) calls_completed,(SELECT COUNT(*) FROM follow_ups f WHERE f.counsellor_id=u.id AND f.status='pending' AND (f.scheduled_date{const u=c.get(\"user\"),today=todayIST(),now=timeIST(),p:any[]=[today,today,today,now],scope=u.role==='super_admin'?``:` AND f.counsellor_id=${Number(u.user_id)}`,leadScope=u.role==='super_admin'?``:` WHERE counsellor_id=${Number(u.user_id)}`,leadAnd=u.role==='super_admin'?``:` AND counsellor_id=${Number(u.user_id)}`;const k=await sqlite.execute({sql:`SELECT (SELECT COUNT(*) FROM leads${leadScope}) total,(SELECT COUNT(*) FROM leads WHERE date(datetime(created_at,'+05:30'))=date(datetime('now','+05:30'))${leadAnd}) new_leads,(SELECT COUNT(*) FROM follow_ups f WHERE f.status='pending' AND f.scheduled_date=?${scope}) today,(SELECT COUNT(*) FROM follow_ups f WHERE f.status='pending' AND (f.scheduled_date? OR (f.scheduled_date=? AND f.scheduled_time>=?))${scope} ORDER BY f.scheduled_date,f.scheduled_time LIMIT 8`,args:[today,today,now]});return c.json({counts:k.rows[0],performance:perf.rows,pipeline:pipe.rows,programs:programs.rows,countries:countries.rows,upcoming:upcoming.rows})});\napp.onError((err)=>{console.error(err);return new Response(\"Internal Server Error\",{status:500})});\nexport default app.fetch;\n","fileType","http",100,"parentId","createdAt",["D",1789465572620],"branchVersion",401,"label","europecrm",[19],{"_33":91,"_13":92,"_37":93,"_44":-5,"_35":36,"_47":78,"_57":58,"_45":94,"_17":80,"_41":95,"_39":-5,"_60":61,"_49":-5,"_62":96,"_18":97},{"_33":84,"_13":85,"_37":86,"_44":-5,"_35":36,"_47":78,"_57":58,"_45":87,"_17":80,"_41":24,"_39":88,"_60":61,"_49":-5,"_62":89,"_18":90},{"_33":75,"_13":76,"_37":77,"_44":-5,"_35":36,"_47":78,"_57":58,"_45":79,"_17":80,"_41":24,"_39":81,"_60":61,"_49":-5,"_62":82,"_18":83},{"_33":65,"_13":66,"_37":67,"_44":-5,"_35":36,"_47":68,"_57":58,"_45":69,"_17":70,"_41":71,"_39":72,"_60":61,"_49":-5,"_62":73,"_18":74},{"_33":34,"_13":19,"_37":38,"_44":-5,"_35":36,"_47":48,"_57":58,"_45":59,"_17":43,"_41":42,"_39":40,"_60":61,"_49":50,"_62":63,"_18":64},"latest",true,["D",1789465572620],"deleted",false,"children",[],[19],"16a9d69a-e87c-4a52-aaed-4db773992d53","db.ts","ef02d66e-acf1-11f1-8f7e-1607ee4eb77e",198,["D",1789106336812],4,"script","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nlet ready: Promise | undefined;\n\nexport function initDb() {\n if (!ready) ready = (async () => {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS users (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n email TEXT NOT NULL UNIQUE,\n password TEXT NOT NULL,\n role TEXT NOT NULL CHECK(role IN ('super_admin','counsellor')),\n status TEXT NOT NULL DEFAULT 'active',\n created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS leads (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n candidate_name TEXT NOT NULL,\n ec_id TEXT NOT NULL UNIQUE,\n phone TEXT NOT NULL,\n email TEXT DEFAULT '',\n country TEXT DEFAULT '',\n program TEXT DEFAULT '',\n counsellor_id INTEGER,\n status TEXT NOT NULL DEFAULT 'New',\n created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY(counsellor_id) REFERENCES users(id)\n )`);\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS follow_ups (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n lead_id INTEGER NOT NULL,\n counsellor_id INTEGER NOT NULL,\n scheduled_date TEXT NOT NULL,\n scheduled_time TEXT NOT NULL,\n completed_at TEXT,\n note TEXT,\n next_followup_date TEXT,\n next_followup_time TEXT,\n status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','completed')),\n created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY(lead_id) REFERENCES leads(id),\n FOREIGN KEY(counsellor_id) REFERENCES users(id)\n )`);\n // Lead profile extensions used by the Europe Careers enquiry form.\n for (const sql of [\n `ALTER TABLE leads ADD COLUMN lead_type TEXT DEFAULT ''`,\n `ALTER TABLE leads ADD COLUMN dob TEXT DEFAULT ''`,\n `ALTER TABLE leads ADD COLUMN current_city TEXT DEFAULT ''`,\n `ALTER TABLE leads ADD COLUMN qualification TEXT DEFAULT ''`,\n `ALTER TABLE leads ADD COLUMN experience TEXT DEFAULT ''`,\n `ALTER TABLE leads ADD COLUMN language_level TEXT DEFAULT ''`,\n `ALTER TABLE leads ADD COLUMN preferred_country TEXT DEFAULT ''`,\n `ALTER TABLE leads ADD COLUMN source TEXT DEFAULT 'CRM'`,\n `ALTER TABLE leads ADD COLUMN lead_temperature TEXT DEFAULT 'Cold'`,\n `ALTER TABLE leads ADD COLUMN notes TEXT DEFAULT ''`\n ]) { try { await sqlite.execute(sql); } catch {} }\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_followups_due ON follow_ups(scheduled_date, scheduled_time, status)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_followups_lead ON follow_ups(lead_id, created_at DESC)`);\n // Enforce the CRM invariant at database level: one pending follow-up per lead.\n try { await sqlite.execute(`CREATE UNIQUE INDEX IF NOT EXISTS uq_followups_one_pending_per_lead ON follow_ups(lead_id) WHERE status='pending'`); } catch {}\n // Triggers protect against duplicate pending rows even if legacy duplicates exist and the unique index cannot be created yet.\n await sqlite.execute(`CREATE TRIGGER IF NOT EXISTS trg_followup_one_pending_insert BEFORE INSERT ON follow_ups WHEN NEW.status='pending' AND EXISTS(SELECT 1 FROM follow_ups WHERE lead_id=NEW.lead_id AND status='pending') BEGIN SELECT RAISE(ABORT,'A lead can have only one pending follow-up'); END`);\n await sqlite.execute(`CREATE TRIGGER IF NOT EXISTS trg_followup_one_pending_update BEFORE UPDATE OF lead_id,status ON follow_ups WHEN NEW.status='pending' AND EXISTS(SELECT 1 FROM follow_ups WHERE lead_id=NEW.lead_id AND status='pending' AND id<>NEW.id) BEGIN SELECT RAISE(ABORT,'A lead can have only one pending follow-up'); END`);\n const count = await sqlite.execute(`SELECT COUNT(*) AS c FROM users`);\n if (Number((count.rows[0] as any)?.c || 0) === 0) {\n await sqlite.execute({sql:`INSERT INTO users (name,email,password,role) VALUES (?,?,?,?)`, args:[\"Super Admin\",\"admin@europecareers.com\",\"admin123\",\"super_admin\"]});\n await sqlite.execute({sql:`INSERT INTO users (name,email,password,role) VALUES (?,?,?,?)`, args:[\"Rahul Counsellor\",\"rahul@europecareers.com\",\"rahul123\",\"counsellor\"]});\n await sqlite.execute({sql:`INSERT INTO users (name,email,password,role) VALUES (?,?,?,?)`, args:[\"Priya Counsellor\",\"priya@europecareers.com\",\"priya123\",\"counsellor\"]});\n }\n })();\n return ready;\n}\n\nexport { sqlite };\n",[],[66],"c0eca97f-4777-4071-ae4c-127e6f7d808f","README.md","01a08a70-b0f3-7058-9d1e-07eb950f89b8",1,["D",1789029036131],0,"# React + Hono Starter\n\nA minimal starter template for building client-side React apps with a Hono\nbackend on Val Town. Click **Remix** to get your own copy.\n\n## Picking a starter\n\n| Starter | When to pick it |\n| --- | --- |\n| [`basic-html-starter`](https://www.val.town/x/templates/basic-html-starter) | Static HTML pages. Sprinkle of vanilla JS. No server-side rendering. |\n| [`hono-jsx-starter`](https://www.val.town/x/templates/hono-jsx-starter) | Dynamic server-rendered HTML with JSX. API routes. No React, no SPA. |\n| **[`react-hono-starter`](https://www.val.town/x/templates/react-hono-starter)** *(you're here)* | Client-side React SPA with a Hono API backend. |\n\n**Want to build UI?** Start writing React components in\n[`frontend/components/`](/frontend/components/). Import them into `App.tsx` and\nyou're good to go. Styling is [Twind](https://twind.style/) — just use Tailwind\nutility classes, zero config.\n\n**Want to add an API?** Add routes in [`index.ts`](/index.ts) (there's a\ncommented-out example). Fetch them from your React components.\n\n## Architecture\n\n```mermaid\ngraph LR\n Browser -->|\"GET /\"| Hono[\"index.ts
(Hono server)\"]\n Hono -->|serves| HTML[\"root.tsx\"]\n HTML -->|loads| Entry[\"index.tsx\"]\n Entry -->|renders| App[\"App.tsx\"]\n App -->|renders| Counter[\"Counter.tsx\"]\n```\n\n## File structure\n\n```\nindex.ts ← Hono backend (serves frontend + API routes)\nfrontend/\n root.tsx ← HTML shell (stamps immutable asset URLs)\n index.tsx ← React entrypoint (mounts )\n favicon.svg ← App icon\n components/\n App.tsx ← Root component (layout + view source link)\n Counter.tsx ← Example component with state\n```\n\n## How it works\n\n1. **`index.ts`** is the HTTP entrypoint — a Hono server that serves the\n frontend and any API routes you add.\n2. **`frontend/root.tsx`** is the HTML shell — it loads Twind and stamps\n immutable asset URLs for the favicon and React entrypoint.\n3. **`frontend/index.tsx`** mounts the React app.\n4. **`frontend/components/App.tsx`** is the root layout — add new components\n alongside it.\n5. **`frontend/components/Counter.tsx`** is an example stateful component —\n replace it with your own!\n\n## Next steps\n\n- Add API routes in `index.ts`\n- Add new components in `frontend/components/`\n- Add a SQLite database — see the\n [React Hono Example](https://www.val.town/x/stevekrouse/reactHonoExample) for\n a full-stack example with database, queries, and shared types\n\nTemplate updates and upgrade paths for existing remixes: [CHANGELOG.md](/CHANGELOG.md)\n",[],[76],"d8991706-f27d-4a75-8a01-f1f4e32c43b3","CHANGELOG.md","01a08a70-b0f3-7058-9d1e-010ffdae1146",["D",1789029036131],"# Changelog\n\n## 2026-07-30 — Immutable asset caching\n\nFrontend files are now served at version-addressed URLs\n(`/__immutable/45/frontend/index.tsx`) with\n`Cache-Control: public, max-age=31536000, immutable`, so repeat visits load the\nwhole client graph from the browser cache — roughly 3× faster (~665ms → ~157ms\nmeasured). Publishing your val bumps its version, which changes every URL, so\ncaches invalidate automatically. Old-version URLs return 404 after a publish\n(like Next.js build assets); a reload picks up the new version.\n\nWhat changed in the template:\n\n- `frontend/index.html` → [`frontend/root.tsx`](/frontend/root.tsx): the HTML\n shell is now JSX and stamps asset URLs with `immutableFileUrl()`\n- [`index.ts`](/index.ts): serves `Root()` at `/`, and one\n `app.get(\"/__immutable/*\", (c) => serveImmutableFile(c.req.path))` route\n serves all frontend files\n\n### Upgrading an existing remix\n\nYour copy of this template doesn't update automatically. Two options:\n\n**Minimal (most of the benefit):** point both routes at `serveImmutableFile` —\nyour existing bare URLs redirect into versioned space, the new route serves\nthem, and the whole module graph gets cached (costs one redirect per page view):\n\n```ts\nimport { serveImmutableFile } from \"https://esm.town/v/std/utils/index.ts\";\n\napp.get(\"/__immutable/*\", (c) => serveImmutableFile(c.req.path));\napp.get(\"/frontend/**/*\", (c) => serveImmutableFile(c.req.path)); // was serveFile\n```\n\n**Full upgrade (also removes the redirect):** three self-contained steps —\nno other files needed. Adjust the ``, favicon, and body of `Root()` to\nmatch your app's existing `index.html`, and stamp anything else your shell\nreferences directly (images, css) with `immutableFileUrl()`.\n\n1. Create `frontend/root.tsx`:\n\n```tsx\n/** @jsxImportSource npm:hono@4/jsx */\nimport { raw } from \"npm:hono@4/html\";\nimport { immutableFileUrl } from \"https://esm.town/v/std/utils/index.ts\";\n\n// The HTML shell for the app. It is never cached, so immutableFileUrl can stamp\n// the current val version into each asset URL — the browser caches those\n// immutably, and publishing the val changes the URLs, refetching exactly once.\nexport function Root() {\n return (\n <>\n {raw(\"<!DOCTYPE html>\")}\n <html lang=\"en\">\n <head>\n <meta charSet=\"UTF-8\" />\n <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\" />\n <title>React Hono Val Town Starter\n