[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8},"actionData","errors","root",{"_107":108,"_109":110},"routes/embed.x.$handle.$name.$",{"_9":10,"_11":12,"_13":14,"_15":16,"_17":18,"_19":20,"_21":-5},"handle","shubhamsingh","name","europe-careers-crm","path","index.ts","project",{"_22":46,"_11":12,"_33":47,"_48":-5,"_49":42,"_50":51,"_52":51,"_53":54,"_55":56,"_57":58,"_59":25,"_60":32,"_61":-5,"_62":-5,"_63":64,"_65":66,"_67":68,"_69":70,"_71":72,"_73":-5,"_74":75,"_76":75,"_77":78,"_79":78,"_80":78,"_81":75,"_82":75,"_83":84,"_85":86,"_87":88,"_89":42},"file",{"_22":23,"_11":14,"_24":25,"_26":-5,"_27":28,"_29":30,"_31":32,"_33":34,"_35":36,"_37":38,"_39":40,"_41":42,"_43":44,"_13":45},"permalink","https://www.val.town/x/shubhamsingh/europe-careers-crm/code/index.ts","extension","id","d883e019-80ee-40cd-aa97-4b901b0d3637","fileId","01a08a70-b0f3-7058-9d1e-0aa6e04fe885","parentId","branchId","398d1cc9-8d86-4c4c-b719-baeac445feeb","branchVersion",401,"latest",true,"createdAt",["D",1789465572620],"version",100,"fileType","http","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","deleted",false,"label","europecrm",[14],"c2c4de1c-ac75-46c6-bae3-140bce9b41c9",["D",1789029036131],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Europe Careers call-focused CRM","imageUrl","https://imagedelivery.net/iHX6Ovru0O7AjmyT5yZRoA/89e2c936-3779-44fe-ac05-33566fc05a01/square512?exp=1789585199&sig=968ace19d25ef4646ba912ca6f8a6a830b08f186a01346910eb7bb397e00ebfe","userId","f007d3fe-0142-4134-8bc5-d309aa62c030","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","01a085c1-b560-76ae-8210-c20c2a71a6a5","tursoName","c2c4de1cac7546c6bae3140bce9b41c9","tursoHostname","c2c4de1cac7546c6bae3140bce9b41c9-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789550457365],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_9":10,"_106":102},"org",{"_9":10,"_99":100,"_101":102,"_57":58,"_103":104,"_105":42},"branch",{"_35":30,"_11":90,"_22":28,"_91":46,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":-5,"_97":12,"_98":10},"isOwned","main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","displayName","Shubham Singh","avatar","https://lh3.googleusercontent.com/a/ACg8ocJsbxTyyQF93TMC5EMaDyttNcoees_fgoJx-kjJsyD3ARH3vXQ=s96-c","tier","free","canRestrictVals","profileImageUrl","ctx",{"_83":-5,"_140":141,"_142":143,"_144":145,"_146":42,"_147":42,"_148":42,"_149":32,"_150":-5,"_151":-7,"_152":-7,"_153":64,"_154":42,"_155":42,"_156":-5,"_157":42,"_158":42,"_159":42,"_160":-5},"ENV",{"_111":112,"_113":114,"_115":90,"_116":117,"_118":119,"_120":121,"_122":123,"_124":125,"_126":127,"_128":129,"_130":131,"_132":133,"_134":135,"_136":137,"_138":139},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","fdb67791413f4386b4dbf18f354029a617f6160f","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town","orgs",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]