[{"_1":2,"_180":-5,"_181":-5},"loaderData",{"_3":4,"_95":96},"root",{"_5":6,"_36":37,"_68":69},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesChatPanelState","notificationCount",0,"flagDenols","isBusinessOrgMember","ENV",{"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63,"_64":65,"_66":67},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","f33c6f02e3b9304fe5cb194294cedf6a64b697a5","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","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","API_URL","https://api.val.town","clerkState",{"_70":71},"__internal_clerk_state",{"_72":73,"_74":47,"_75":76,"_77":76,"_78":20,"_79":76,"_80":76,"_81":76,"_82":76,"_83":84,"_93":20,"_94":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_85":20,"_19":20,"_86":76,"_87":88,"_89":76,"_90":47,"_91":20,"_92":76},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_97":98,"_99":100,"_101":102,"_103":104,"_157":158,"_177":178,"_179":-5},"handle","abhishekplating","name","PlatingPro","path","ppAnalytics.ts","project",{"_105":106,"_99":100,"_107":108,"_109":-5,"_110":20,"_111":112,"_113":114,"_115":-5,"_12":116,"_117":-5,"_118":20,"_119":-5,"_120":-5,"_121":33,"_14":122,"_123":-5,"_124":-5,"_125":-5,"_126":127,"_128":127,"_129":130,"_131":130,"_132":130,"_133":127,"_134":127,"_23":135,"_138":139,"_143":144,"_156":20},"id","1fffa610-245e-11f1-b73d-42dde27851f2","createdAt",["D",1774012210756],"deletedAt","legacyEnvVars","privacy","public","description","V1","imageUrl","019ca7d6-bbec-76d6-822b-6e2c39ed1dbd","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","019ca7d6-bbec-76d6-822b-72f395b7c435","tursoName","tursoHostname","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_97":98,"_136":137},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBS1hsRkxLQ2d3eHdUMVYzU3p3YTNmakRRbSJ9","org",{"_97":98,"_140":137,"_12":116,"_141":142},"avatar","tier","free","branch",{"_145":146,"_99":45,"_105":147,"_148":106,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":-5,"_154":100,"_155":98},"version",104,"20008210-245e-11f1-b73d-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_105":159,"_99":102,"_160":161,"_162":-5,"_163":147,"_164":165,"_166":167,"_107":168,"_145":169,"_170":171,"_172":173,"_174":20,"_175":-5,"_101":176},"6d83e500-2c41-11f1-81d2-42dde27851f2","fileId","0dca08fe-2810-11f1-98ef-42dde27851f2","parentId","branchId","branchVersion",100,"latest",true,["D",1774879494695],10,"fileType","http","content","// PlatingPro — Analytics, Reports, AI (V20)\nimport { sql, ok, err, audit, SETTINGS_WHITELIST, rateLimit } from \"./ppShared.ts\";\n\nconst SENSITIVE_KEYS = new Set([\"gemini_api_key\",\"whatsapp_api_key\",\"gmail_credentials\",\"gmail_token\"]);\n\n// Helper: convert YYYY-MM to date range for index-friendly queries\nfunction monthRange(m: string): { start: string; end: string } {\n const [y, mo] = m.split(\"-\").map(Number);\n const start = `${m}-01`;\n const nextMo = mo === 12 ? `${y+1}-01-01` : `${y}-${String(mo+1).padStart(2,\"0\")}-01`;\n return { start, end: nextMo };\n}\n\nexport async function getDashboard(p: URLSearchParams, req?: Request): Promise {\n const m = p.get(\"month\") || \"\";\n if (!m) return ok({ kpi: { total_prod:0,total_value:0,total_rej:0,total_rej_cost:0,rej_rate:0,working_days:0 }, plating:[], categories:[], items:[], reasons:[], topProduct:null, topRejection:null }, req);\n const { start, end } = monthRange(m);\n const [kpi,plat,cats,items,reasons,tp,tr] = await Promise.all([\n sql`SELECT COALESCE(SUM(prod_qty),0)::float as total_prod,COALESCE(SUM(prod_amount),0)::float as total_value,COALESCE(SUM(rej_qty),0)::float as total_rej,COALESCE(SUM(rej_amount),0)::float as total_rej_cost,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_rate,COUNT(DISTINCT prod_date)::int as working_days FROM pp_production WHERE prod_date>=${start}::date AND prod_date<${end}::date`,\n sql`SELECT UPPER(TRIM(plating_type))as plating_type,SUM(prod_qty)::float as prod,SUM(rej_qty)::float as rej,SUM(prod_amount)::float as value,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${start}::date AND prod_date<${end}::date GROUP BY UPPER(TRIM(plating_type))ORDER BY SUM(prod_qty)DESC`,\n sql`SELECT UPPER(TRIM(category))as category,SUM(prod_qty)::float as prod,SUM(rej_qty)::float as rej,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${start}::date AND prod_date<${end}::date AND category IS NOT NULL AND TRIM(category)!=''GROUP BY UPPER(TRIM(category))ORDER BY SUM(prod_qty)DESC`,\n sql`SELECT item_name,mould_no,UPPER(TRIM(plating_type))as plating_type,SUM(prod_qty)::float as prod,SUM(rej_qty)::float as rej,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${start}::date AND prod_date<${end}::date GROUP BY item_name,mould_no,UPPER(TRIM(plating_type))HAVING SUM(rej_qty)>0 ORDER BY SUM(rej_qty)DESC LIMIT 20`,\n sql`SELECT UPPER(TRIM(rej_mark))as reason,COUNT(*)::int as count,SUM(rej_qty)::float as total_kg FROM pp_production WHERE prod_date>=${start}::date AND prod_date<${end}::date AND rej_mark IS NOT NULL AND TRIM(rej_mark)!=''GROUP BY UPPER(TRIM(rej_mark))ORDER BY COUNT(*)DESC`,\n sql`SELECT item_name,mould_no,SUM(prod_qty)::float as prod FROM pp_production WHERE prod_date>=${start}::date AND prod_date<${end}::date GROUP BY item_name,mould_no ORDER BY SUM(prod_qty)DESC LIMIT 1`,\n sql`SELECT item_name,mould_no,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${start}::date AND prod_date<${end}::date GROUP BY item_name,mould_no HAVING SUM(prod_qty)>=10 ORDER BY CASE WHEN SUM(prod_qty)>0 THEN SUM(rej_qty)/SUM(prod_qty)ELSE 0 END DESC LIMIT 1`,\n ]);\n return ok({ kpi: kpi[0], plating: plat, categories: cats, items, reasons, topProduct: tp[0]||null, topRejection: tr[0]||null }, req);\n}\n\nexport async function getDailyData(p: URLSearchParams, req?: Request): Promise {\n const m = p.get(\"month\") || \"\";\n if (!m) return ok({ days: [] }, req);\n const r = await sql`SELECT EXTRACT(DAY FROM prod_date)::int as day,SUM(prod_qty)::float as prod,SUM(rej_qty)::float as rej,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date GROUP BY prod_date ORDER BY prod_date`;\n return ok({ days: r }, req);\n}\n\nexport async function compareMonths(p: URLSearchParams, req?: Request): Promise {\n const m1 = p.get(\"month1\") || \"\", m2 = p.get(\"month2\") || \"\";\n if (!m1 || !m2) return ok({ summary: [] }, req);\n const r = await sql`SELECT TO_CHAR(prod_date,'YYYY-MM')as month,SUM(prod_qty)::float as prod,SUM(prod_amount)::float as value,SUM(rej_qty)::float as rej,SUM(rej_amount)::float as rej_cost,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_rate FROM pp_production WHERE prod_date>=${m1+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m2+'-01'}::date)+INTERVAL'1 month')::date GROUP BY TO_CHAR(prod_date,'YYYY-MM')`;\n return ok({ summary: r }, req);\n}\n\nexport async function getTargets(req?: Request): Promise {\n return ok({ targets: await sql`SELECT*FROM pp_targets ORDER BY plating_type` }, req);\n}\n\nexport async function updateTarget(b: any, req?: Request): Promise {\n if (b.id) { await sql`UPDATE pp_targets SET target_rej_pct=${Number(b.target_rej_pct)},set_by=${b.set_by||'user'} WHERE id=${b.id}`; return ok({ success: true }, req); }\n const r = await sql`INSERT INTO pp_targets(plating_type,target_rej_pct,set_by)VALUES(${b.plating_type},${Number(b.target_rej_pct)},${b.set_by||'user'})RETURNING*`;\n return ok({ target: r[0] }, req);\n}\n\nexport async function deleteTarget(b: any, req?: Request): Promise {\n if (!b.id) return err(\"id required\", 400, req);\n await sql`DELETE FROM pp_targets WHERE id=${b.id}`;\n return ok({ success: true }, req);\n}\n\nexport async function getUtilization(p: URLSearchParams, req?: Request): Promise {\n const m = p.get(\"month\") || \"\";\n // Auto-create table if missing\n await sql`CREATE TABLE IF NOT EXISTS pp_line_capacity (\n id SERIAL PRIMARY KEY,\n plating_type TEXT UNIQUE NOT NULL,\n monthly_capacity_kg NUMERIC NOT NULL DEFAULT 0\n )`;\n // Seed default plating types if table is empty\n const existing = await sql`SELECT COUNT(*)::int as cnt FROM pp_line_capacity`;\n if ((existing[0] as any).cnt === 0) {\n await sql`INSERT INTO pp_line_capacity(plating_type, monthly_capacity_kg) VALUES\n ('COPPER', 5000),('GOLD', 3000),('NICKEL', 4000),('SILVER', 3000)\n ON CONFLICT(plating_type) DO NOTHING`;\n }\n const [cap, prod] = await Promise.all([\n sql`SELECT * FROM pp_line_capacity ORDER BY plating_type`,\n m ? sql`SELECT UPPER(TRIM(plating_type))as pt,SUM(prod_qty)::float as actual FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date GROUP BY UPPER(TRIM(plating_type))` : Promise.resolve([])\n ]);\n return ok({ utilization: cap.map((c: any) => {\n const p2 = (prod as any[]).find((x: any) => x.pt === c.plating_type.toUpperCase());\n const a = p2 ? p2.actual : 0;\n const cp = Number(c.monthly_capacity_kg);\n return { plating_type: c.plating_type, capacity: cp, actual: a, util_pct: cp > 0 ? Math.round(a/cp*100) : 0 };\n })}, req);\n}\n\nexport async function updateCapacity(b: any, req?: Request): Promise {\n if (b.plating_type === '_total') {\n await sql`INSERT INTO pp_settings(key,value)VALUES('total_plant_capacity',${String(b.capacity)})ON CONFLICT(key)DO UPDATE SET value=${String(b.capacity)}`;\n return ok({ success: true }, req);\n }\n await sql`UPDATE pp_line_capacity SET monthly_capacity_kg=${Number(b.capacity)} WHERE plating_type=${b.plating_type}`;\n return ok({ success: true }, req);\n}\n\nexport async function getDailyDetail(p: URLSearchParams, req?: Request): Promise {\n let dateStr = p.get(\"day\") || \"\";\n if (!dateStr) { const y = new Date(); y.setDate(y.getDate()-1); y.setMinutes(y.getMinutes()+330); dateStr = y.toISOString().slice(0,10); }\n const [r, kpi] = await Promise.all([\n sql`SELECT item_name,mould_no,UPPER(TRIM(plating_type))as plating_type,UPPER(TRIM(category))as category,prod_qty::float as prod,prod_amount::float as value,rej_qty::float as rej,rej_amount::float as rej_cost,rej_mark FROM pp_production WHERE prod_date=${dateStr}::date ORDER BY prod_qty DESC`,\n sql`SELECT COALESCE(SUM(prod_qty),0)::float as total_prod,COALESCE(SUM(prod_amount),0)::float as total_value,COALESCE(SUM(rej_qty),0)::float as total_rej,COALESCE(SUM(rej_amount),0)::float as total_rej_cost,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_rate,COUNT(*)::int as entries FROM pp_production WHERE prod_date=${dateStr}::date`\n ]);\n return ok({ date: dateStr, kpi: kpi[0], items: r }, req);\n}\n\nexport async function getGrowth(req?: Request): Promise {\n const r = await sql`SELECT TO_CHAR(prod_date,'YYYY-MM')as month,SUM(prod_qty)::float as prod,SUM(prod_amount)::float as value,SUM(rej_qty)::float as rej,SUM(rej_amount)::float as rej_cost,COUNT(DISTINCT prod_date)::int as days,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_rate,CASE WHEN COUNT(DISTINCT prod_date)>0 THEN ROUND(SUM(prod_qty)/COUNT(DISTINCT prod_date))::int ELSE 0 END as avg_daily FROM pp_production GROUP BY TO_CHAR(prod_date,'YYYY-MM')ORDER BY month`;\n return ok({ months: r }, req);\n}\n\nexport async function getPareto(p: URLSearchParams, req?: Request): Promise {\n const m = p.get(\"month\") || \"\";\n if (!m) return ok({ pareto: [], totalRej: 0 }, req);\n const r = await sql`SELECT item_name,SUM(rej_qty)::float as rej FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date AND rej_qty>0 GROUP BY item_name ORDER BY SUM(rej_qty)DESC`;\n const tot = r.reduce((s: number, x: any) => s + x.rej, 0);\n let cum = 0;\n return ok({ pareto: r.map((x: any) => { cum += x.rej; return { item: x.item_name, rej: x.rej, cumPct: tot>0?Math.round(cum/tot*100):0 }; }), totalRej: tot });\n}\n\nexport async function getActions(req?: Request): Promise {\n return ok({ actions: await sql`SELECT*FROM pp_actions ORDER BY CASE status WHEN 'open' THEN 1 WHEN 'in-progress' THEN 2 ELSE 3 END,created_at DESC` }, req);\n}\n\nexport async function createAction(b: any, req?: Request): Promise {\n const r = await sql`INSERT INTO pp_actions(item_name,mould_no,issue,severity,assignee,action_text,due_date)VALUES(${b.item_name},${b.mould_no||null},${b.issue},${b.severity||'warning'},${b.assignee||null},${b.action_text||null},${b.due_date||null})RETURNING*`;\n return ok({ action: r[0] }, req);\n}\n\nexport async function updateAction(b: any, req?: Request): Promise {\n if (b.status === 'resolved') await sql`UPDATE pp_actions SET status=${b.status},resolved_at=NOW()WHERE id=${b.id}`;\n else await sql`UPDATE pp_actions SET status=${b.status}WHERE id=${b.id}`;\n return ok({ success: true }, req);\n}\n\nexport async function getHealthLog(req?: Request): Promise {\n return ok({ issues: await sql`SELECT*FROM pp_health_log ORDER BY created_at DESC LIMIT 50` }, req);\n}\n\nexport async function getInsights(p: URLSearchParams, req?: Request): Promise {\n const m = p.get(\"month\");\n return ok({ insights: m ? await sql`SELECT*FROM pp_insights WHERE month=${m} ORDER BY CASE severity WHEN 'critical' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END` : await sql`SELECT*FROM pp_insights ORDER BY created_at DESC LIMIT 30` }, req);\n}\n\nexport async function getMonths(req?: Request): Promise {\n const r = await sql`SELECT DISTINCT TO_CHAR(prod_date,'YYYY-MM')as month FROM pp_production ORDER BY month DESC`;\n return ok({ months: r.map((x: any) => x.month) }, req);\n}\n\nexport async function getMoulds(req?: Request): Promise {\n const r = await sql`SELECT DISTINCT ON (mould_no) mould_no, item_name, UPPER(TRIM(category)) as category, moulding_type, prod_rate as rate, rej_rate, UPPER(TRIM(plating_type)) as plating_type FROM pp_production WHERE mould_no IS NOT NULL AND mould_no!='' ORDER BY mould_no, id DESC`;\n return ok({ moulds: r }, req);\n}\n\nexport async function getSettings(user?: any, req?: Request): Promise {\n const r = await sql`SELECT key,value FROM pp_settings`;\n const s: any = {};\n r.forEach((x: any) => {\n s[x.key] = (SENSITIVE_KEYS.has(x.key) && user?.role !== \"admin\") ? \"****\" : x.value;\n });\n return ok({ settings: s }, req);\n}\n\nexport async function updateSetting(b: any, callerName = \"unknown\", req?: Request): Promise {\n if (!b.key || !SETTINGS_WHITELIST.has(b.key)) return err(\"Invalid or unknown setting key\", 400, req);\n await sql`INSERT INTO pp_settings(key,value)VALUES(${b.key},${b.value})ON CONFLICT(key)DO UPDATE SET value=${b.value},updated_at=NOW()`;\n await audit(callerName, \"UPDATE_SETTING\", \"settings\", { key: b.key });\n return ok({ success: true }, req);\n}\n\nexport async function getWhatsAppSummary(p: URLSearchParams, req?: Request): Promise {\n const m = p.get(\"month\") || \"\";\n if (!m) return ok({ text: \"No month\" }, req);\n const [kpi, top3] = await Promise.all([\n sql`SELECT SUM(prod_qty)::float as prod,SUM(prod_amount)::float as value,SUM(rej_qty)::float as rej,SUM(rej_amount)::float as rej_cost FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date`,\n sql`SELECT item_name,SUM(rej_qty)::float as rej FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date AND rej_qty>0 GROUP BY item_name ORDER BY SUM(rej_qty)DESC LIMIT 3`\n ]);\n const k = kpi[0] || {};\n let t = `📊 *PlatingPro — ${m}*\\n━━━━━━━━━━━━━━━━━\\n🏭 *Arihant Enterprises*\\n\\n*Production:* ${(k.prod||0).toLocaleString('en-IN')} kg\\n*Value:* ₹${(k.value||0).toLocaleString('en-IN')}\\n*Rejection:* ${(k.rej||0).toLocaleString('en-IN')} kg (${k.prod>0?((k.rej||0)/k.prod*100).toFixed(2):'0'}%)\\n*Rej Cost:* ₹${(k.rej_cost||0).toLocaleString('en-IN')}\\n\\n⚠️ *Top Rejections:*\\n`;\n top3.forEach((r: any, i: number) => { t += `${i+1}. ${r.item_name} — ${r.rej.toLocaleString('en-IN')} kg\\n`; });\n return ok({ text: t }, req);\n}\n\nexport async function sendWhatsApp(body: any, req?: Request): Promise {\n const { phone, apikey, month } = body;\n const m = month || '';\n const kpi = await sql`SELECT SUM(prod_qty)::float as prod,SUM(prod_amount)::float as value,SUM(rej_qty)::float as rej,SUM(rej_amount)::float as rej_cost FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date`;\n const top3 = await sql`SELECT item_name,SUM(rej_qty)::float as rej FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date AND rej_qty>0 GROUP BY item_name ORDER BY SUM(rej_qty)DESC LIMIT 3`;\n const k = kpi[0] || {};\n const prod = k.prod || 0; const rej = k.rej || 0;\n const rejRate = prod > 0 ? (rej/prod*100).toFixed(2) : '0';\n let text = `📊 *PlatingPro — ${m}*\\n━━━━━━━━━━━━━━━\\n🏭 *Arihant Enterprises*\\n\\n`;\n text += `*Production:* ${prod.toLocaleString('en-IN')} kg\\n*Value:* ₹${(k.value||0).toLocaleString('en-IN')}\\n*Rejection:* ${rej.toLocaleString('en-IN')} kg (${rejRate}%)\\n*Rej Cost:* ₹${(k.rej_cost||0).toLocaleString('en-IN')}\\n\\n⚠️ *Top Rejections:*\\n`;\n top3.forEach((r: any, i: number) => { text += `${i+1}. ${r.item_name} — ${r.rej.toLocaleString('en-IN')} kg\\n`; });\n if (phone && apikey) {\n try {\n const encoded = encodeURIComponent(text);\n const waResp = await fetch(`https://api.callmebot.com/whatsapp.php?phone=${phone}&text=${encoded}&apikey=${apikey}`);\n const waText = await waResp.text();\n return ok({ sent: true, method: 'callmebot', response: waText, text }, req);\n } catch (e: any) { return ok({ sent: false, error: e.message, text }, req); }\n }\n return ok({ sent: false, method: 'manual', text }, req);\n}\n\nexport async function getTrendPrediction(req?: Request): Promise {\n const months = await sql`SELECT TO_CHAR(prod_date,'YYYY-MM') as month,SUM(prod_qty)::float as prod,SUM(prod_amount)::float as value,SUM(rej_qty)::float as rej,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_rate,COUNT(DISTINCT prod_date)::int as days FROM pp_production GROUP BY TO_CHAR(prod_date,'YYYY-MM') ORDER BY month`;\n if (months.length < 2) return ok({ prediction: null, message: \"Need at least 2 months of data\", months: [] }, req);\n const predict = (values: number[]): number => {\n const n = values.length; if (n < 2) return values[0] || 0;\n let sumX=0,sumY=0,sumXY=0,sumX2=0;\n values.forEach((y, x) => { sumX+=x; sumY+=y; sumXY+=x*y; sumX2+=x*x; });\n const slope = (n*sumXY - sumX*sumY) / (n*sumX2 - sumX*sumX);\n const intercept = (sumY - slope*sumX) / n;\n return Math.max(0, Math.round((slope * n + intercept) * 100) / 100);\n };\n const prodValues = months.map((m: any) => m.prod);\n const rejValues = months.map((m: any) => m.rej_rate);\n const valueValues = months.map((m: any) => m.value);\n const nextProd = predict(prodValues);\n const nextRejRate = Math.max(0, Math.min(100, predict(rejValues)));\n const nextValue = predict(valueValues);\n const nextRej = Math.round(nextProd * nextRejRate / 100 * 100) / 100;\n const prodTrend = prodValues.length >= 2 ? (prodValues[prodValues.length-1] > prodValues[prodValues.length-2] ? 'increasing' : 'decreasing') : 'stable';\n const rejTrend = rejValues.length >= 2 ? (rejValues[rejValues.length-1] > rejValues[rejValues.length-2] ? 'worsening' : 'improving') : 'stable';\n const prediction = { next_month_prod: nextProd, next_month_rej_rate: nextRejRate, next_month_value: nextValue, next_month_rej_kg: nextRej, prod_trend: prodTrend, rej_trend: rejTrend, confidence: Math.min(95, 50 + months.length * 10), based_on: months.length + ' months' };\n let geminiInsight = '';\n try {\n const settingsRes = await sql`SELECT value FROM pp_settings WHERE key='gemini_api_key'`;\n const apiKey = settingsRes[0]?.value;\n if (apiKey) {\n const prompt = `You are a factory analytics expert. Based on this electroplating production data, give 3-4 bullet point predictions and recommendations for next month. Be specific with numbers. Data: ${JSON.stringify(months.map((m: any) => ({ month: m.month, prod_kg: m.prod, value: m.value, rej_rate: m.rej_rate+'%', days: m.days })))}. Regression predicts: Production ${nextProd} kg, Rejection ${nextRejRate}%, Value ₹${nextValue}. Keep response under 200 words.`;\n const gResp = await fetch(`https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=${apiKey}`, { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ contents: [{ parts: [{ text: prompt }] }], generationConfig: { temperature: 0.3, maxOutputTokens: 500 } }) });\n if (gResp.ok) { const gData = await gResp.json(); geminiInsight = gData.candidates?.[0]?.content?.parts?.[0]?.text || ''; }\n }\n } catch (e) { /* Gemini optional */ }\n return ok({ prediction, months, geminiInsight }, req);\n}\n\nexport async function askQuestion(b: any, callerName = \"anon\", req?: Request): Promise {\n const question = b.question;\n if (!question || !question.trim()) return err(\"Question required\", 400, req);\n const m = b.month || \"\";\n const [kpi,plat,topItems,topRej,reasons] = await Promise.all([\n m ? sql`SELECT COALESCE(SUM(prod_qty),0)::float as total_prod,COALESCE(SUM(prod_amount),0)::float as total_value,COALESCE(SUM(rej_qty),0)::float as total_rej,COALESCE(SUM(rej_amount),0)::float as total_rej_cost,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_rate,COUNT(DISTINCT prod_date)::int as working_days FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date` : Promise.resolve([{ total_prod: 0 }]),\n m ? sql`SELECT UPPER(TRIM(plating_type))as plating_type,SUM(prod_qty)::float as prod,SUM(rej_qty)::float as rej,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date GROUP BY UPPER(TRIM(plating_type))ORDER BY SUM(prod_qty)DESC` : Promise.resolve([]),\n m ? sql`SELECT item_name,mould_no,SUM(prod_qty)::float as prod,SUM(rej_qty)::float as rej,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date GROUP BY item_name,mould_no ORDER BY SUM(prod_qty)DESC LIMIT 30` : Promise.resolve([]),\n m ? sql`SELECT item_name,mould_no,SUM(rej_qty)::float as rej,CASE WHEN SUM(prod_qty)>0 THEN ROUND(SUM(rej_qty)/SUM(prod_qty)*100,2)::float ELSE 0 END as rej_pct FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date GROUP BY item_name,mould_no HAVING SUM(rej_qty)>0 ORDER BY SUM(rej_qty)DESC LIMIT 15` : Promise.resolve([]),\n m ? sql`SELECT UPPER(TRIM(rej_mark))as reason,COUNT(*)::int as count,SUM(rej_qty)::float as total FROM pp_production WHERE prod_date>=${m+'-01'}::date AND prod_date<(DATE_TRUNC('month',${m+'-01'}::date)+INTERVAL'1 month')::date AND rej_mark IS NOT NULL AND TRIM(rej_mark)!='' GROUP BY UPPER(TRIM(rej_mark))ORDER BY COUNT(*)DESC` : Promise.resolve([])\n ]);\n const k = kpi[0] || {};\n let context = `PlatingPro Data for ${m||'all time'}:\\n`;\n context += `Production: ${k.total_prod||0} kg, Value: ₹${k.total_value||0}, Working Days: ${k.working_days||0}\\n`;\n context += `Rejection: ${k.total_rej||0} kg (${k.rej_rate||0}%), Cost: ₹${k.total_rej_cost||0}\\n\\n`;\n context += `Plating Types:\\n${(plat as any[]).map((p: any) => ` ${p.plating_type}: ${p.prod} kg prod, ${p.rej} kg rej (${p.rej_pct}%)`).join('\\n')}\\n\\n`;\n context += `Top Items:\\n${(topItems as any[]).map((t: any) => ` ${t.item_name} (M${t.mould_no||'?'}): ${t.prod} kg, rej ${t.rej_pct}%`).join('\\n')}\\n\\n`;\n context += `Top Rejections:\\n${(topRej as any[]).map((t: any) => ` ${t.item_name} (M${t.mould_no||'?'}): ${t.rej} kg (${t.rej_pct}%)`).join('\\n')}\\n\\n`;\n context += `Rejection Reasons:\\n${(reasons as any[]).map((r: any) => ` ${r.reason}: ${r.count} times, ${r.total} kg`).join('\\n')}\\n`;\n try {\n const settingsRes = await sql`SELECT value FROM pp_settings WHERE key='gemini_api_key'`;\n const apiKey = settingsRes[0]?.value;\n if (!apiKey) return ok({ answer: \"Gemini API key not configured. Go to Settings.\", source: 'no_key' }, req);\n const prompt = `You are PlatingPro AI assistant for Arihant Enterprises electroplating factory. Answer the user's question using ONLY the data provided. Be concise, use numbers, format with bullet points.\\n\\nLive Factory Data:\\n${context}\\n\\nUser Question: ${question}`;\n const gResp = await fetch(`https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=${apiKey}`, { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ contents: [{ parts: [{ text: prompt }] }], generationConfig: { temperature: 0.3, maxOutputTokens: 800 } }) });\n if (!gResp.ok) { const t = await gResp.text(); return ok({ answer: 'Gemini error: ' + t, source: 'error' }, req); }\n const gData = await gResp.json();\n const answer = gData.candidates?.[0]?.content?.parts?.[0]?.text || 'No response from Gemini';\n return ok({ answer, source: 'gemini' }, req);\n } catch (e: any) { return ok({ answer: 'Error: ' + e.message, source: 'error' }, req); }\n}\n\nexport async function getExportData(p: URLSearchParams, req?: Request): Promise {\n const from = p.get(\"from\") || \"\", to = p.get(\"to\") || \"\";\n if (!from || !to) return err(\"from and to dates required\", 400, req);\n const r = await sql`SELECT TO_CHAR(prod_date,'DD.MM.YYYY') as date,mould_no,item_name,UPPER(TRIM(plating_type))as plating_type,UPPER(TRIM(category))as category,COALESCE(moulding_type,'OUR MOULDING')as moulding_type,prod_qty::float as prod_qty,prod_rate::float as prod_rate,prod_amount::float as prod_amount,rej_qty::float as rej_qty,rej_rate::float as rej_rate,rej_amount::float as rej_amount,COALESCE(rej_mark,'')as rej_mark FROM pp_production WHERE prod_date>=${from}::date AND prod_date<=${to}::date ORDER BY prod_date,item_name`;\n return ok({ from, to, rows: r }, req);\n}\n\nexport async function getAuditLog(p: URLSearchParams, req?: Request): Promise {\n const limit = Math.min(parseInt(p.get(\"limit\") || \"50\"), 200);\n const module = p.get(\"module\") || \"\";\n const rows = module\n ? await sql`SELECT id,user_name,action,module,data,created_at FROM pp_audit_log WHERE module=${module} ORDER BY id DESC LIMIT ${limit}`\n : await sql`SELECT id,user_name,action,module,data,created_at FROM pp_audit_log ORDER BY id DESC LIMIT ${limit}`;\n return ok({ log: rows }, req);\n}\n","deleted","label",[102],"permalink","https://www.val.town/x/abhishekplating/PlatingPro/code/ppAnalytics.ts","extension","actionData","errors"]