[{"_1":2,"_178":-5,"_179":-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","isPaidOrgMember","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","5239845a9d28bb87e603f0cf2ef63fd4c7134223","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,"_156":157,"_175":176,"_177":-5},"handle","abhishekplating","name","PlatingProBeads","path","main.ts","project",{"_105":106,"_99":100,"_107":108,"_109":-5,"_110":20,"_111":112,"_113":-5,"_114":-5,"_12":115,"_116":-5,"_117":20,"_118":-5,"_119":-5,"_120":33,"_14":121,"_122":-5,"_123":-5,"_124":-5,"_125":126,"_127":126,"_128":129,"_130":129,"_131":129,"_132":126,"_133":126,"_23":134,"_137":138,"_142":143,"_155":20},"id","1fffa610-245e-11f1-b73d-42dde27851f2","createdAt",["D",1774012210756],"deletedAt","legacyEnvVars","privacy","public","description","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,"_135":136},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzNBS1hsRkxLQ2d3eHdUMVYzU3p3YTNmakRRbSJ9","org",{"_97":98,"_139":136,"_12":115,"_140":141},"avatar","tier","free","branch",{"_144":145,"_99":45,"_105":146,"_147":106,"_148":-5,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":100,"_154":98},"version",22,"20008210-245e-11f1-b73d-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_105":158,"_99":102,"_159":160,"_161":-5,"_162":146,"_163":145,"_164":165,"_107":166,"_144":167,"_168":169,"_170":171,"_172":20,"_173":-5,"_101":174},"1be013ae-261a-11f1-a2a9-42dde27851f2","fileId","200494d6-245e-11f1-b73d-42dde27851f2","parentId","branchId","branchVersion","latest",true,["D",1774202900364],21,"fileType","http","content","// PlatingPro Backend V15 — QA Bug Fixes\n// Fix1: PROD. AMOUNT column mapped correctly\n// Fix2: Rejection QTY/RATE/AMOUNT mapped correctly\n// Fix3: DD.MM.YYYY date parsing + reject dates > today+1month\n// Fix4: All aggregation in SQL, parallel queries\n// Fix5 (V15): prev-day SQL fix — use MAX(prod_date) instead of DISTINCT+ORDER BY\n// Fix6 (V15): frequent-items window expanded to 90 days with 30-day fallback first\nimport { neon } from \"https://esm.sh/@neondatabase/serverless@0.9.5\";\nconst DATABASE_URL =\n \"postgresql://neondb_owner:npg_ScVTuh3mo8Na@ep-falling-snow-a1x1ceh4-pooler.ap-southeast-1.aws.neon.tech/neondb?sslmode=require\";\nconst sql = neon(DATABASE_URL);\nconst CORS: Record = {\n \"Content-Type\": \"application/json\",\n \"Access-Control-Allow-Origin\": \"*\",\n \"Access-Control-Allow-Methods\": \"GET,POST,PUT,DELETE,OPTIONS\",\n \"Access-Control-Allow-Headers\": \"Content-Type, Authorization\",\n};\nfunction ok(d: any) {\n return new Response(JSON.stringify(d), { status: 200, headers: CORS });\n}\nfunction err(m: string, c = 400) {\n return new Response(JSON.stringify({ error: m }), {\n status: c,\n headers: CORS,\n });\n}\n\n// AUTH\nasync function login(b: any) {\n const { username, password } = b;\n if (!username || !password) return err(\"Required\");\n const r =\n await sql`SELECT id,username,display_name,role FROM pp_users WHERE username=${username.toLowerCase().trim()} AND password=${password} AND active=true`;\n if (!r || r.length === 0) return err(\"Invalid credentials\", 401);\n const u = r[0];\n const t = crypto.randomUUID();\n await sql`INSERT INTO pp_sessions(token,user_name,role)VALUES(${t},${u.username},${u.role})`;\n return ok({\n token: t,\n user: { username: u.username, name: u.display_name, role: u.role },\n });\n}\nasync function verifyToken(t: string) {\n if (!t) return null;\n const r =\n await sql`SELECT user_name,role FROM pp_sessions WHERE token=${t} AND expires_at>NOW()`;\n return r?.[0] || null;\n}\n\n// DASHBOARD — parallel SQL\nasync function getDashboard(p: URLSearchParams) {\n const m = p.get(\"month\") || \"\";\n if (!m) {\n return ok({\n kpi: {\n total_prod: 0,\n total_value: 0,\n total_rej: 0,\n total_rej_cost: 0,\n rej_rate: 0,\n working_days: 0,\n },\n plating: [],\n categories: [],\n items: [],\n reasons: [],\n topProduct: null,\n topRejection: null,\n });\n }\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 TO_CHAR(prod_date,'YYYY-MM')=${m}`,\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 TO_CHAR(prod_date,'YYYY-MM')=${m} 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 TO_CHAR(prod_date,'YYYY-MM')=${m} 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 TO_CHAR(prod_date,'YYYY-MM')=${m} 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 TO_CHAR(prod_date,'YYYY-MM')=${m} 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 TO_CHAR(prod_date,'YYYY-MM')=${m} 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 TO_CHAR(prod_date,'YYYY-MM')=${m} 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({\n kpi: kpi[0],\n plating: plat,\n categories: cats,\n items,\n reasons,\n topProduct: tp[0] || null,\n topRejection: tr[0] || null,\n });\n}\n\nasync function getDailyData(p: URLSearchParams) {\n const m = p.get(\"month\") || \"\";\n if (!m) return ok({ days: [] });\n const r =\n 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 TO_CHAR(prod_date,'YYYY-MM')=${m} GROUP BY prod_date ORDER BY prod_date`;\n return ok({ days: r });\n}\nasync function compareMonths(p: URLSearchParams) {\n const m1 = p.get(\"month1\") || \"\", m2 = p.get(\"month2\") || \"\";\n if (!m1 || !m2) return ok({ summary: [] });\n const r =\n 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 TO_CHAR(prod_date,'YYYY-MM')IN(${m1},${m2})GROUP BY TO_CHAR(prod_date,'YYYY-MM')`;\n return ok({ summary: r });\n}\nasync function getTargets() {\n return ok({\n targets: await sql`SELECT*FROM pp_targets ORDER BY plating_type`,\n });\n}\nasync function updateTarget(b: any) {\n if (b.id) {\n await sql`UPDATE pp_targets SET target_rej_pct=${\n Number(b.target_rej_pct)\n },set_by=${b.set_by || \"user\"} WHERE id=${b.id}`;\n return ok({ success: true });\n }\n const r =\n await sql`INSERT INTO pp_targets(plating_type,target_rej_pct,set_by)VALUES(${b.plating_type},${\n Number(b.target_rej_pct)\n },${b.set_by || \"user\"})RETURNING*`;\n return ok({ target: r[0] });\n}\nasync function deleteTarget(b: any) {\n if (!b.id) return err(\"id required\");\n await sql`DELETE FROM pp_targets WHERE id=${b.id}`;\n return ok({ success: true });\n}\nasync function getUtilization(p: URLSearchParams) {\n const m = p.get(\"month\") || \"\";\n const [cap, prod] = await Promise.all([\n sql`SELECT*FROM pp_line_capacity`,\n m\n ? sql`SELECT UPPER(TRIM(plating_type))as pt,SUM(prod_qty)::float as actual FROM pp_production WHERE TO_CHAR(prod_date,'YYYY-MM')=${m} GROUP BY UPPER(TRIM(plating_type))`\n : Promise.resolve([]),\n ]);\n return ok({\n utilization: cap.map((c: any) => {\n const p2 = (prod as any[]).find((x: any) =>\n x.pt === c.plating_type.toUpperCase()\n );\n const a = p2 ? p2.actual : 0;\n const cp = Number(c.monthly_capacity_kg);\n return {\n plating_type: c.plating_type,\n capacity: cp,\n actual: a,\n util_pct: cp > 0 ? Math.round(a / cp * 100) : 0,\n };\n }),\n });\n}\nasync function updateCapacity(b: any) {\n if (b.plating_type === \"_total\") {\n await sql`INSERT INTO pp_settings(key,value)VALUES('total_plant_capacity',${\n String(b.capacity)\n })ON CONFLICT(key)DO UPDATE SET value=${String(b.capacity)}`;\n return ok({ success: true });\n }\n await sql`UPDATE pp_line_capacity SET monthly_capacity_kg=${\n Number(b.capacity)\n } WHERE plating_type=${b.plating_type}`;\n return ok({ success: true });\n}\nasync function getDailyDetail(p: URLSearchParams) {\n let dateStr = p.get(\"day\") || \"\";\n if (!dateStr) {\n const y = new Date();\n y.setDate(y.getDate() - 1);\n y.setMinutes(y.getMinutes() + 330);\n dateStr = y.toISOString().slice(0, 10);\n }\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 TO_CHAR(prod_date,'YYYY-MM-DD')=${dateStr} 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 TO_CHAR(prod_date,'YYYY-MM-DD')=${dateStr}`,\n ]);\n return ok({ date: dateStr, kpi: kpi[0], items: r });\n}\nasync function getGrowth() {\n const r =\n 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 });\n}\nasync function getPareto(p: URLSearchParams) {\n const m = p.get(\"month\") || \"\";\n if (!m) return ok({ pareto: [], totalRej: 0 });\n const r =\n await sql`SELECT item_name,SUM(rej_qty)::float as rej FROM pp_production WHERE TO_CHAR(prod_date,'YYYY-MM')=${m} 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({\n pareto: r.map((x: any) => {\n cum += x.rej;\n return {\n item: x.item_name,\n rej: x.rej,\n cumPct: tot > 0 ? Math.round(cum / tot * 100) : 0,\n };\n }),\n totalRej: tot,\n });\n}\nasync function getActions() {\n return ok({\n actions:\n 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`,\n });\n}\nasync function createAction(b: any) {\n const r =\n await sql`INSERT INTO pp_actions(item_name,mould_no,issue,severity,assignee,action_text,due_date)VALUES(${b.item_name},${\n b.mould_no || null\n },${b.issue},${b.severity || \"warning\"},${b.assignee || null},${\n b.action_text || null\n },${b.due_date || null})RETURNING*`;\n return ok({ action: r[0] });\n}\nasync function updateAction(b: any) {\n if (b.status === \"resolved\") {\n 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 });\n}\nasync function getHealthLog() {\n return ok({\n issues:\n await sql`SELECT*FROM pp_health_log ORDER BY created_at DESC LIMIT 50`,\n });\n}\nasync function getInsights(p: URLSearchParams) {\n const m = p.get(\"month\");\n return ok({\n insights: m\n ? await sql`SELECT*FROM pp_insights WHERE month=${m} ORDER BY CASE severity WHEN 'critical' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END`\n : await sql`SELECT*FROM pp_insights ORDER BY created_at DESC LIMIT 30`,\n });\n}\nasync function getMonths() {\n const r =\n 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) });\n}\nasync function getMoulds() {\n // Get unique moulds with their details from production data\n const r =\n 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 });\n}\nasync function getSettings() {\n const r = await sql`SELECT key,value FROM pp_settings`;\n const s: any = {};\n r.forEach((x: any) => {\n s[x.key] = x.value;\n });\n return ok({ settings: s });\n}\nasync function updateSetting(b: any) {\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 return ok({ success: true });\n}\nasync function getWhatsAppSummary(p: URLSearchParams) {\n const m = p.get(\"month\") || \"\";\n if (!m) return ok({ text: \"No month\" });\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 TO_CHAR(prod_date,'YYYY-MM')=${m}`,\n sql`SELECT item_name,SUM(rej_qty)::float as rej FROM pp_production WHERE TO_CHAR(prod_date,'YYYY-MM')=${m} 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 =\n `📊 *PlatingPro — ${m}*\\n━━━━━━━━━━━━━━━━━\\n🏭 *Arihant Enterprises*\\n\\n*Production:* ${\n (k.prod || 0).toLocaleString(\"en-IN\")\n } kg\\n*Value:* ₹${(k.value || 0).toLocaleString(\"en-IN\")}\\n*Rejection:* ${\n (k.rej || 0).toLocaleString(\"en-IN\")\n } kg (${\n k.prod > 0 ? ((k.rej || 0) / k.prod * 100).toFixed(2) : \"0\"\n }%)\\n*Rej Cost:* ₹${\n (k.rej_cost || 0).toLocaleString(\"en-IN\")\n }\\n\\n⚠️ *Top Rejections:*\\n`;\n top3.forEach((r: any, i: number) => {\n t += `${i + 1}. ${r.item_name} — ${r.rej.toLocaleString(\"en-IN\")} kg\\n`;\n });\n return ok({ text: t });\n}\n\n// User management\nasync function getUsers() {\n return ok({\n users:\n await sql`SELECT id,username,display_name,role,active,created_at FROM pp_users ORDER BY id`,\n });\n}\nasync function addUser(b: any) {\n if (!b.username || !b.password || !b.display_name) {\n return err(\"username, password, display_name required\");\n }\n try {\n const r =\n await sql`INSERT INTO pp_users(username,password,display_name,role)VALUES(${b.username.toLowerCase().trim()},${b.password},${b.display_name},${\n b.role || \"viewer\"\n })RETURNING id,username,display_name,role`;\n return ok({ user: r[0] });\n } catch (e: any) {\n return err(\"Username already exists\");\n }\n}\nasync function deleteUser(b: any) {\n if (!b.id) return err(\"id required\");\n await sql`DELETE FROM pp_sessions WHERE user_name=(SELECT username FROM pp_users WHERE id=${b.id})`;\n await sql`DELETE FROM pp_users WHERE id=${b.id}`;\n return ok({ success: true });\n}\nasync function updateUser(b: any) {\n if (!b.id) return err(\"id required\");\n if (b.password) {\n await sql`UPDATE pp_users SET display_name=COALESCE(${b.display_name},display_name),role=COALESCE(${b.role},role),password=${b.password},active=COALESCE(${b.active},active)WHERE id=${b.id}`;\n } else\n await sql`UPDATE pp_users SET display_name=COALESCE(${b.display_name},display_name),role=COALESCE(${b.role},role),active=COALESCE(${b.active},active)WHERE id=${b.id}`;\n return ok({ success: true });\n}\n\n// ============================================\n// UPLOAD — FIXED column mapping + date validation\n// ============================================\nasync function uploadChunk(body: any) {\n const {\n data,\n fileName,\n uploadedBy,\n uploadId: existingId,\n chunkIndex,\n totalChunks,\n } = body;\n if (!data || !Array.isArray(data) || data.length === 0) return err(\"No data\");\n\n let uploadId = existingId;\n if (!uploadId || chunkIndex === 0) {\n const r =\n await sql`INSERT INTO pp_uploads(file_name,month,uploaded_by,status)VALUES(${\n fileName || \"unknown\"\n },${\"detecting\"},${uploadedBy || \"system\"},'processing')RETURNING id`;\n uploadId = r[0].id;\n }\n\n let imported = 0, rejected = 0, issuesFixed = 0, detectedMonth = \"\";\n\n // Max valid date: today + 1 month\n const maxDate = new Date();\n maxDate.setMonth(maxDate.getMonth() + 1);\n const maxDateStr = maxDate.toISOString().slice(0, 10);\n\n for (const row of data) {\n try {\n // FLEXIBLE COLUMN GETTER — matches exact, trimmed, newline-stripped, and case-insensitive\n const rowKeys = Object.keys(row);\n // Normalize key: strip newlines, trim spaces\n const norm = (s: string) =>\n s.replace(/\\n/g, \" \").replace(/\\s+/g, \" \").trim().toUpperCase();\n const g = (names: string[]) => {\n // Exact match\n for (const n of names) {\n if (\n row[n] !== undefined && row[n] !== null &&\n String(row[n]).trim() !== \"\"\n ) return row[n];\n }\n // Normalized match (handles newlines, extra spaces, case)\n for (const n of names) {\n const nNorm = norm(n);\n const found = rowKeys.find((k) => norm(k) === nNorm);\n if (\n found && row[found] !== undefined && row[found] !== null &&\n String(row[found]).trim() !== \"\"\n ) return row[found];\n }\n return null;\n };\n // Parse number: handles commas, spaces, string numbers\n const num = (v: any): number => {\n if (v === null || v === undefined) return 0;\n if (typeof v === \"number\") return v;\n const s = String(v).replace(/,/g, \"\").replace(/\\s/g, \"\");\n const n = parseFloat(s);\n return isNaN(n) ? 0 : n;\n };\n\n const dateRaw = g([\"Date\", \"date\", \"DATE\"]);\n const itemName = String(\n g([\"Item Name\", \"ITEM NAME\", \"item_name\", \"Item\"]) || \"\",\n ).trim();\n let platingType = String(\n g([\"Plating\", \"PLATING TYPE\", \"Plating Type\", \"plating_type\"]) || \"\",\n ).trim();\n\n if (\n !itemName || !dateRaw || itemName === \"null\" || itemName === \"undefined\"\n ) {\n rejected++;\n continue;\n }\n\n // ===== DATE PARSING — handle ALL formats =====\n let prodDate: string = \"\";\n const ds = String(dateRaw);\n\n if (ds.includes(\"T\")) {\n // ISO: \"2025-12-01T00:00:00.000Z\"\n prodDate = ds.slice(0, 10);\n } else if (ds.includes(\".\") && ds.split(\".\").length === 3) {\n // DD.MM.YYYY: \"01.01.2026\"\n const [dd, mm, yyyy] = ds.split(\".\");\n const y = parseInt(yyyy), mo = parseInt(mm), d = parseInt(dd);\n if (\n y >= 2024 && y <= 2027 && mo >= 1 && mo <= 12 && d >= 1 && d <= 31\n ) {\n prodDate = `${yyyy}-${mm.padStart(2, \"0\")}-${dd.padStart(2, \"0\")}`;\n } else {\n rejected++;\n continue;\n }\n } else if (ds.includes(\"-\") && ds.length === 10) {\n // Already YYYY-MM-DD\n prodDate = ds;\n } else if (!isNaN(Number(ds)) && Number(ds) > 40000) {\n // Excel serial number\n const d2 = new Date((Number(ds) - 25569) * 86400000);\n prodDate = d2.toISOString().slice(0, 10);\n } else {\n try {\n const d2 = new Date(ds);\n if (!isNaN(d2.getTime())) prodDate = d2.toISOString().slice(0, 10);\n else {\n rejected++;\n continue;\n }\n } catch {\n rejected++;\n continue;\n }\n }\n\n // VALIDATE DATE: must be between 2024-01-01 and maxDate\n if (!prodDate || prodDate < \"2024-01-01\" || prodDate > maxDateStr) {\n rejected++;\n continue;\n }\n\n if (!detectedMonth) detectedMonth = prodDate.slice(0, 7);\n\n // Normalize plating type\n const ptL = platingType.toLowerCase().trim();\n if (ptL.includes(\"gold\")) platingType = \"Gold\";\n else if (ptL.includes(\"copper\")) platingType = \"Copper\";\n else if (ptL.includes(\"nickel\")) platingType = \"Nickel\";\n else if (ptL.includes(\"silver\")) platingType = \"Silver\";\n else if (ptL.includes(\"black\")) platingType = \"Black\";\n else {platingType = platingType.charAt(0).toUpperCase() +\n platingType.slice(1).toLowerCase();}\n\n const category = String(g([\"Category\", \"CATEGORY\", \"category\"]) || \"\")\n .toUpperCase().trim();\n const mouldNo = String(\n g([\"Mould No.\", \"Mould\\nNo.\", \"MOULD NO.\", \"mould_no\"]) || \"\",\n ).trim();\n const mouldType = String(\n g([\n \"Moulding Type\",\n \"Moulding\\nType\",\n \"HIS/OUR MOULDING\",\n \"moulding_type\",\n ]) || \"OUR MOULDING\",\n ).trim();\n\n // ===== PRODUCTION — new template has Prod Qty (kg), Rate (₹/kg), Prod Amount (₹) =====\n const prodQty = num(\n g([\n \"Prod Qty (kg)\",\n \"Prod Qty\\n(kg)\",\n \"PROD.QTY\",\n \"Prod.Qty\",\n \"prod_qty\",\n ]),\n );\n const prodRate = num(\n g([\n \"Rate (₹/kg)\",\n \"Rate\\n(₹/kg)\",\n \"PROD. RATE\",\n \"Prod. Rate\",\n \"prod_rate\",\n ]),\n );\n const prodAmount = num(\n g([\n \"Prod Amount (₹)\",\n \"Prod Amount\\n(₹)\",\n \"PROD. AMOUNT\",\n \"Prod. Amount\",\n \"prod_amount\",\n ]),\n );\n\n // ===== REJECTION — new template has Rej Qty (kg), Rej Rate (₹/kg), Rej Amount (₹) =====\n const rejQty = num(\n g([\n \"Rej Qty (kg)\",\n \"Rej Qty\\n(kg)\",\n \"Rejection QTY\",\n \"REJECTION QTY\",\n \"rej_qty\",\n ]),\n );\n const rejRate = num(\n g([\n \"Rej Rate (₹/kg)\",\n \"Rej Rate\\n(₹/kg)\",\n \"REJECTION RATE\",\n \"Rejection Rate\",\n \"rej_rate\",\n ]),\n );\n const rejAmount = num(\n g([\n \"Rej Amount (₹)\",\n \"Rej Amount\\n(₹)\",\n \"REJECTION AMOUNT\",\n \"Rejection Amount\",\n \"rej_amount\",\n ]),\n );\n const rejMark = String(\n g([\n \"Rejection Reason\",\n \"Rejection\\nReason\",\n \"Rejection Mark\",\n \"REJECTION MARK\",\n \"rej_mark\",\n ]) || \"\",\n ).trim();\n\n // Use calculated amount if column is 0 but qty and rate exist\n const finalProdAmount = prodAmount > 0 ? prodAmount : prodQty * prodRate;\n const finalRejAmount = rejAmount > 0 ? rejAmount : rejQty * rejRate;\n\n // SMART MERGE: check if this row already exists (same date + mould + item + plating)\n const existing =\n await sql`SELECT id FROM pp_production WHERE prod_date=${prodDate} AND COALESCE(mould_no,'')=${\n mouldNo || \"\"\n } AND item_name=${itemName} AND UPPER(TRIM(plating_type))=UPPER(TRIM(${platingType})) LIMIT 1`;\n\n if (existing.length > 0) {\n // UPDATE existing row\n await sql`UPDATE pp_production SET upload_id=${uploadId},category=${category},moulding_type=${mouldType},prod_qty=${prodQty},prod_rate=${prodRate},prod_amount=${finalProdAmount},rej_qty=${rejQty},rej_rate=${rejRate},rej_amount=${finalRejAmount},rej_mark=${\n rejMark || null\n } WHERE id=${existing[0].id}`;\n imported++; // count as processed\n } else {\n // INSERT new row\n await sql`INSERT INTO pp_production(upload_id,prod_date,mould_no,item_name,plating_type,category,moulding_type,prod_qty,prod_rate,prod_amount,rej_qty,rej_rate,rej_amount,rej_mark)VALUES(${uploadId},${prodDate},${mouldNo},${itemName},${platingType},${category},${mouldType},${prodQty},${prodRate},${finalProdAmount},${rejQty},${rejRate},${finalRejAmount},${\n rejMark || null\n })`;\n imported++;\n }\n } catch (e) {\n rejected++;\n }\n }\n\n const isLast = !totalChunks || chunkIndex === totalChunks - 1;\n if (isLast && detectedMonth) {\n if (issuesFixed > 0) {\n await sql`INSERT INTO pp_health_log(upload_id,issue_type,title,description,status,auto_fixed)VALUES(${uploadId},'format','Normalization',${\n issuesFixed + \" fixes\"\n },'auto-fixed',true)`;\n }\n const anomalies =\n await sql`SELECT item_name,mould_no,SUM(prod_qty)::float as prod,SUM(rej_qty)::float as rej FROM pp_production WHERE upload_id=${uploadId} GROUP BY item_name,mould_no HAVING SUM(rej_qty)>SUM(prod_qty)AND SUM(prod_qty)>0`;\n for (const a of anomalies) {\n await sql`INSERT INTO pp_health_log(upload_id,issue_type,title,description,status)VALUES(${uploadId},'anomaly','Rejection>Production',${\n a.item_name + \": \" + a.rej + \" rej vs \" + a.prod + \" prod\"\n },'flagged')`;\n }\n await sql`DELETE FROM pp_insights WHERE month=${detectedMonth}`;\n const topR =\n await 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 TO_CHAR(prod_date,'YYYY-MM')=${detectedMonth} GROUP BY item_name,mould_no HAVING SUM(rej_qty)>0 ORDER BY CASE WHEN SUM(prod_qty)>0 THEN SUM(rej_qty)/SUM(prod_qty)ELSE 0 END DESC LIMIT 10`;\n for (const it of topR) {\n const sev = it.rej_pct > 100\n ? \"critical\"\n : it.rej_pct > 10\n ? \"warning\"\n : \"info\";\n await sql`INSERT INTO pp_insights(month,insight_type,severity,title,description,item_name,mould_no,confidence)VALUES(${detectedMonth},'alert',${sev},${\n it.item_name + \" at \" + it.rej_pct + \"% rejection\"\n },${\n it.rej + \" kg. \" + (it.rej_pct > 100\n ? \"CRITICAL!\"\n : it.rej_pct > 10\n ? \"High.\"\n : \"Monitor.\")\n },${it.item_name},${it.mould_no},${it.rej_pct > 100 ? 98 : 85})`;\n }\n // Plating insights\n const plR =\n await sql`SELECT UPPER(TRIM(plating_type))as pt,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 TO_CHAR(prod_date,'YYYY-MM')=${detectedMonth} GROUP BY UPPER(TRIM(plating_type))HAVING CASE WHEN SUM(prod_qty)>0 THEN SUM(rej_qty)/SUM(prod_qty)*100 ELSE 0 END>2`;\n for (const p of plR) {\n await sql`INSERT INTO pp_insights(month,insight_type,severity,title,description,confidence)VALUES(${detectedMonth},'alert','warning',${\n p.pt + \" plating at \" + p.rej_pct + \"% rej\"\n },${p.pt + \" above 2% target.\"},85)`;\n }\n await sql`UPDATE pp_uploads SET month=${detectedMonth},rows_imported=${imported},rows_rejected=${rejected},issues_found=${anomalies.length},issues_auto_fixed=${issuesFixed},status='complete'WHERE id=${uploadId}`;\n }\n return ok({\n uploadId,\n month: detectedMonth,\n imported,\n rejected,\n issuesFixed,\n isLast,\n });\n}\n\n// ============================================\n// TREND PREDICTION — regression + Gemini\n// ============================================\nasync function getTrendPrediction() {\n // Get all monthly data\n const months =\n 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\n if (months.length < 2) {\n return ok({\n prediction: null,\n message: \"Need at least 2 months of data for prediction\",\n months: [],\n });\n }\n\n // Simple linear regression for each metric\n const predict = (values: number[]): number => {\n const n = values.length;\n if (n < 2) return values[0] || 0;\n let sumX = 0, sumY = 0, sumXY = 0, sumX2 = 0;\n values.forEach((y, x) => {\n sumX += x;\n sumY += y;\n sumXY += x * y;\n sumX2 += x * x;\n });\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\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\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\n // Trends\n const prodTrend = prodValues.length >= 2\n ? (prodValues[prodValues.length - 1] > prodValues[prodValues.length - 2]\n ? \"increasing\"\n : \"decreasing\")\n : \"stable\";\n const rejTrend = rejValues.length >= 2\n ? (rejValues[rejValues.length - 1] > rejValues[rejValues.length - 2]\n ? \"worsening\"\n : \"improving\")\n : \"stable\";\n\n const prediction = {\n next_month_prod: nextProd,\n next_month_rej_rate: nextRejRate,\n next_month_value: nextValue,\n next_month_rej_kg: nextRej,\n prod_trend: prodTrend,\n rej_trend: rejTrend,\n confidence: Math.min(95, 50 + months.length * 10),\n based_on: months.length + \" months\",\n };\n\n // Try Gemini for natural language insights\n let geminiInsight = \"\";\n try {\n const settingsRes =\n await sql`SELECT value FROM pp_settings WHERE key='gemini_api_key'`;\n const apiKey = settingsRes[0]?.value;\n if (apiKey) {\n const prompt =\n `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: ${\n JSON.stringify(\n months.map((m: any) => ({\n month: m.month,\n prod_kg: m.prod,\n value: m.value,\n rej_rate: m.rej_rate + \"%\",\n days: m.days,\n })),\n )\n }. Regression predicts: Production ${nextProd} kg, Rejection ${nextRejRate}%, Value ₹${nextValue}. Keep response under 200 words.`;\n\n const gResp = await fetch(\n `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=${apiKey}`,\n {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n contents: [{ parts: [{ text: prompt }] }],\n generationConfig: { temperature: 0.3, maxOutputTokens: 500 },\n }),\n },\n );\n if (gResp.ok) {\n const gData = await gResp.json();\n geminiInsight = gData.candidates?.[0]?.content?.parts?.[0]?.text || \"\";\n }\n }\n } catch (e) { /* Gemini optional */ }\n\n return ok({ prediction, months, geminiInsight });\n}\n\n// ============================================\n// WHATSAPP SEND (CallMeBot or manual)\n// ============================================\nasync function sendWhatsApp(body: any) {\n const { phone, apikey, month } = body;\n const m = month || \"\";\n\n // Generate summary text\n const kpi =\n 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 TO_CHAR(prod_date,'YYYY-MM')=${m}`;\n const top3 =\n await sql`SELECT item_name, SUM(rej_qty)::float as rej FROM pp_production WHERE TO_CHAR(prod_date,'YYYY-MM')=${m} AND rej_qty>0 GROUP BY item_name ORDER BY SUM(rej_qty) DESC LIMIT 3`;\n\n const k = kpi[0] || {};\n const prod = k.prod || 0;\n const rej = k.rej || 0;\n const rejRate = prod > 0 ? (rej / prod * 100).toFixed(2) : \"0\";\n\n let text =\n `📊 *PlatingPro — ${m}*\\n━━━━━━━━━━━━━━━\\n🏭 *Arihant Enterprises*\\n\\n`;\n text += `*Production:* ${prod.toLocaleString(\"en-IN\")} kg\\n`;\n text += `*Value:* ₹${(k.value || 0).toLocaleString(\"en-IN\")}\\n`;\n text += `*Rejection:* ${rej.toLocaleString(\"en-IN\")} kg (${rejRate}%)\\n`;\n text += `*Rej Cost:* ₹${(k.rej_cost || 0).toLocaleString(\"en-IN\")}\\n\\n`;\n text += `⚠️ *Top Rejections:*\\n`;\n top3.forEach((r: any, i: number) => {\n text += `${i + 1}. ${r.item_name} — ${r.rej.toLocaleString(\"en-IN\")} kg\\n`;\n });\n\n if (phone && apikey) {\n // Send via CallMeBot\n try {\n const encoded = encodeURIComponent(text);\n const waResp = await fetch(\n `https://api.callmebot.com/whatsapp.php?phone=${phone}&text=${encoded}&apikey=${apikey}`,\n );\n const waText = await waResp.text();\n return ok({ sent: true, method: \"callmebot\", response: waText, text });\n } catch (e: any) {\n return ok({ sent: false, error: e.message, text });\n }\n }\n\n return ok({ sent: false, method: \"manual\", text });\n}\n\nasync function addProduction(b: any) {\n if (!b.item_name || !b.prod_date || !b.plating_type) {\n return err(\"Required fields missing\");\n }\n const pa = (Number(b.prod_qty) || 0) * (Number(b.prod_rate) || 0);\n const ra = (Number(b.rej_qty) || 0) * (Number(b.rej_rate) || 0);\n const r =\n await sql`INSERT INTO pp_production(prod_date,mould_no,item_name,plating_type,category,moulding_type,prod_qty,prod_rate,prod_amount,rej_qty,rej_rate,rej_amount,rej_mark)VALUES(${b.prod_date},${\n b.mould_no || null\n },${b.item_name},${b.plating_type},${b.category || null},${\n b.moulding_type || \"OUR MOULDING\"\n },${Number(b.prod_qty) || 0},${Number(b.prod_rate) || 0},${pa},${\n Number(b.rej_qty) || 0\n },${Number(b.rej_rate) || 0},${ra},${b.rej_mark || null})RETURNING*`;\n return ok({ row: r[0] });\n}\n\n// BATCH ADD — save 1-50 rows at once\nasync function addProductionBatch(b: any) {\n const rows = b.rows;\n if (!rows || !Array.isArray(rows) || rows.length === 0) {\n return err(\"No rows provided\");\n }\n if (rows.length > 50) return err(\"Max 50 rows per batch\");\n let saved = 0, failed = 0;\n const errors: string[] = [];\n for (const r of rows) {\n try {\n if (!r.item_name || !r.prod_date || !r.plating_type) {\n failed++;\n errors.push(r.item_name || \"unknown: missing fields\");\n continue;\n }\n const pa = (Number(r.prod_qty) || 0) * (Number(r.prod_rate) || 0);\n const ra = (Number(r.rej_qty) || 0) * (Number(r.rej_rate) || 0);\n await sql`INSERT INTO pp_production(prod_date,mould_no,item_name,plating_type,category,moulding_type,prod_qty,prod_rate,prod_amount,rej_qty,rej_rate,rej_amount,rej_mark)VALUES(${r.prod_date},${\n r.mould_no || null\n },${r.item_name},${r.plating_type},${r.category || null},${\n r.moulding_type || \"OUR MOULDING\"\n },${Number(r.prod_qty) || 0},${Number(r.prod_rate) || 0},${pa},${\n Number(r.rej_qty) || 0\n },${Number(r.rej_rate) || 0},${ra},${r.rej_mark || null})`;\n saved++;\n } catch (e: any) {\n failed++;\n errors.push((r.item_name || \"unknown\") + \": \" + e.message);\n }\n }\n return ok({ saved, failed, errors: errors.slice(0, 5) });\n}\n\n// DELETE single production record\nasync function deleteProduction(b: any) {\n if (!b.id) return err(\"id required\");\n await sql`DELETE FROM pp_production WHERE id=${b.id}`;\n return ok({ success: true });\n}\n\n// BULK DELETE production records\nasync function deleteProductionBatch(b: any) {\n const ids = b.ids;\n if (!ids || !Array.isArray(ids) || ids.length === 0) {\n return err(\"No ids provided\");\n }\n if (ids.length > 100) return err(\"Max 100 deletions per batch\");\n let deleted = 0;\n for (const id of ids) {\n try {\n await sql`DELETE FROM pp_production WHERE id=${id}`;\n deleted++;\n } catch (e) {}\n }\n return ok({ deleted });\n}\n\n// GET daily detail with IDs for deletion support\nasync function getDailyDetailWithIds(p: URLSearchParams) {\n let dateStr = p.get(\"day\") || \"\";\n if (!dateStr) {\n const y = new Date();\n y.setDate(y.getDate() - 1);\n y.setMinutes(y.getMinutes() + 330);\n dateStr = y.toISOString().slice(0, 10);\n }\n const [r, kpi] = await Promise.all([\n sql`SELECT id,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,prod_rate::float as rate,rej_qty::float as rej,rej_amount::float as rej_cost,rej_rate::float as rej_rate_val,rej_mark FROM pp_production WHERE TO_CHAR(prod_date,'YYYY-MM-DD')=${dateStr} ORDER BY id 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 TO_CHAR(prod_date,'YYYY-MM-DD')=${dateStr}`,\n ]);\n return ok({ date: dateStr, kpi: kpi[0], items: r });\n}\n\n// GET frequent items — try last 30 days, fall back to last 90 days if empty\nasync function getFrequentItems() {\n let r =\n await sql`SELECT item_name,mould_no,UPPER(TRIM(plating_type))as plating_type,UPPER(TRIM(category))as category,moulding_type,MAX(prod_rate)::float as rate,MAX(rej_rate)::float as rej_rate_val,COUNT(*)::int as freq FROM pp_production WHERE prod_date>=CURRENT_DATE-30 GROUP BY item_name,mould_no,UPPER(TRIM(plating_type)),UPPER(TRIM(category)),moulding_type ORDER BY COUNT(*)DESC LIMIT 20`;\n if (!r || r.length === 0) {\n r =\n await sql`SELECT item_name,mould_no,UPPER(TRIM(plating_type))as plating_type,UPPER(TRIM(category))as category,moulding_type,MAX(prod_rate)::float as rate,MAX(rej_rate)::float as rej_rate_val,COUNT(*)::int as freq FROM pp_production WHERE prod_date>=CURRENT_DATE-90 GROUP BY item_name,mould_no,UPPER(TRIM(plating_type)),UPPER(TRIM(category)),moulding_type ORDER BY COUNT(*)DESC LIMIT 20`;\n }\n return ok({ items: r });\n}\n\n// GET previous day entries (for \"copy from yesterday\" feature)\nasync function getPrevDayEntries(p: URLSearchParams) {\n let dateStr = p.get(\"day\") || \"\";\n if (!dateStr) {\n const y = new Date();\n y.setDate(y.getDate() - 1);\n y.setMinutes(y.getMinutes() + 330);\n dateStr = y.toISOString().slice(0, 10);\n }\n // Find the most recent date before the given date that has data\n const prevDate =\n await sql`SELECT TO_CHAR(MAX(prod_date),'YYYY-MM-DD')as d FROM pp_production WHERE prod_date<${dateStr}::date`;\n if (!prevDate || prevDate.length === 0 || !prevDate[0].d) {\n return ok({ date: null, items: [] });\n }\n const pd = prevDate[0].d;\n const r =\n await sql`SELECT item_name,mould_no,UPPER(TRIM(plating_type))as plating_type,UPPER(TRIM(category))as category,moulding_type,prod_qty::float as prod,prod_rate::float as rate,rej_qty::float as rej,rej_rate::float as rej_rate_val,rej_mark FROM pp_production WHERE TO_CHAR(prod_date,'YYYY-MM-DD')=${pd} ORDER BY id`;\n return ok({ date: pd, items: r });\n}\n\n// Q&A — ask Gemini with live data context\nasync function askQuestion(b: any) {\n const question = b.question;\n if (!question || !question.trim()) return err(\"Question required\");\n const m = b.month || \"\";\n // Build context from live data\n const [kpi, plat, topItems, topRej, reasons] = await Promise.all([\n m\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 TO_CHAR(prod_date,'YYYY-MM')=${m}`\n : Promise.resolve([{ total_prod: 0 }]),\n m\n ? 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 TO_CHAR(prod_date,'YYYY-MM')=${m} GROUP BY UPPER(TRIM(plating_type))ORDER BY SUM(prod_qty)DESC`\n : Promise.resolve([]),\n m\n ? 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 TO_CHAR(prod_date,'YYYY-MM')=${m} GROUP BY item_name,mould_no ORDER BY SUM(prod_qty)DESC LIMIT 30`\n : Promise.resolve([]),\n m\n ? 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 TO_CHAR(prod_date,'YYYY-MM')=${m} GROUP BY item_name,mould_no HAVING SUM(rej_qty)>0 ORDER BY SUM(rej_qty)DESC LIMIT 15`\n : Promise.resolve([]),\n m\n ? sql`SELECT UPPER(TRIM(rej_mark))as reason,COUNT(*)::int as count,SUM(rej_qty)::float as total FROM pp_production WHERE TO_CHAR(prod_date,'YYYY-MM')=${m} AND rej_mark IS NOT NULL AND TRIM(rej_mark)!='' GROUP BY UPPER(TRIM(rej_mark))ORDER BY COUNT(*)DESC`\n : 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: ₹${\n k.total_value || 0\n }, Working Days: ${k.working_days || 0}\\n`;\n context += `Rejection: ${k.total_rej || 0} kg (${k.rej_rate || 0}%), Cost: ₹${\n k.total_rej_cost || 0\n }\\n\\n`;\n context += `Plating Types:\\n${\n (plat as any[]).map((p: any) =>\n ` ${p.plating_type}: ${p.prod} kg prod, ${p.rej} kg rej (${p.rej_pct}%)`\n ).join(\"\\n\")\n }\\n\\n`;\n context += `Top Items by Production:\\n${\n (topItems as any[]).map((t: any) =>\n ` ${t.item_name} (M${\n t.mould_no || \"?\"\n }): ${t.prod} kg, rej ${t.rej_pct}%`\n ).join(\"\\n\")\n }\\n\\n`;\n context += `Top Rejections:\\n${\n (topRej as any[]).map((t: any) =>\n ` ${t.item_name} (M${t.mould_no || \"?\"}): ${t.rej} kg (${t.rej_pct}%)`\n ).join(\"\\n\")\n }\\n\\n`;\n context += `Rejection Reasons:\\n${\n (reasons as any[]).map((r: any) =>\n ` ${r.reason}: ${r.count} times, ${r.total} kg`\n ).join(\"\\n\")\n }\\n`;\n\n // Call Gemini\n try {\n const settingsRes =\n await sql`SELECT value FROM pp_settings WHERE key='gemini_api_key'`;\n const apiKey = settingsRes[0]?.value;\n if (!apiKey) {\n return ok({\n answer:\n \"Gemini API key not configured. Go to Settings and add your key.\",\n source: \"no_key\",\n });\n }\n const prompt =\n `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. If the data doesn't have enough info, say so.\\n\\nLive Factory Data:\\n${context}\\n\\nUser Question: ${question}`;\n const gResp = await fetch(\n `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=${apiKey}`,\n {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/json\" },\n body: JSON.stringify({\n contents: [{ parts: [{ text: prompt }] }],\n generationConfig: { temperature: 0.3, maxOutputTokens: 800 },\n }),\n },\n );\n if (!gResp.ok) {\n const t = await gResp.text();\n return ok({ answer: \"Gemini error: \" + t, source: \"error\" });\n }\n const gData = await gResp.json();\n const answer = gData.candidates?.[0]?.content?.parts?.[0]?.text ||\n \"No response from Gemini\";\n return ok({ answer, source: \"gemini\" });\n } catch (e: any) {\n return ok({ answer: \"Error: \" + e.message, source: \"error\" });\n }\n}\n\n// EXPORT — return raw production rows for Excel export (date range)\nasync function getExportData(p: URLSearchParams) {\n const from = p.get(\"from\") || \"\";\n const to = p.get(\"to\") || \"\";\n if (!from || !to) return err(\"from and to dates required\");\n const r =\n 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 });\n}\n\n// ROUTER\nexport default async function (req: Request): Promise {\n if (req.method === \"OPTIONS\") return new Response(\"\", { headers: CORS });\n const url = new URL(req.url),\n path = url.pathname.replace(/\\/+$/, \"\"),\n params = url.searchParams,\n method = req.method;\n try {\n if (path === \"/api/login\" && method === \"POST\") {\n return login(await req.json());\n }\n if (path === \"/api/ping\") {\n return ok({ status: \"ok\", app: \"PlatingPro\", v: 12 });\n }\n const token = req.headers.get(\"Authorization\")?.replace(\"Bearer \", \"\");\n const user = await verifyToken(token || \"\");\n if (!user && path.startsWith(\"/api/\")) return err(\"Unauthorized\", 401);\n if (path === \"/api/dashboard\") return getDashboard(params);\n if (path === \"/api/daily\") return getDailyData(params);\n if (path === \"/api/compare\") return compareMonths(params);\n if (path === \"/api/targets\" && method === \"GET\") return getTargets();\n if (path === \"/api/targets\" && method === \"POST\") {\n return updateTarget(await req.json());\n }\n if (path === \"/api/targets\" && method === \"DELETE\") {\n return deleteTarget(await req.json());\n }\n if (path === \"/api/users\" && method === \"GET\") return getUsers();\n if (path === \"/api/users\" && method === \"POST\") {\n return addUser(await req.json());\n }\n if (path === \"/api/users\" && method === \"DELETE\") {\n return deleteUser(await req.json());\n }\n if (path === \"/api/users\" && method === \"PUT\") {\n return updateUser(await req.json());\n }\n if (path === \"/api/utilization\") return getUtilization(params);\n if (path === \"/api/capacity\" && method === \"POST\") {\n return updateCapacity(await req.json());\n }\n if (path === \"/api/daily-detail\") return getDailyDetail(params);\n if (path === \"/api/growth\") return getGrowth();\n if (path === \"/api/pareto\") return getPareto(params);\n if (path === \"/api/actions\" && method === \"GET\") return getActions();\n if (path === \"/api/actions\" && method === \"POST\") {\n return createAction(await req.json());\n }\n if (path === \"/api/actions\" && method === \"PUT\") {\n return updateAction(await req.json());\n }\n if (path === \"/api/health\") return getHealthLog();\n if (path === \"/api/insights\") return getInsights(params);\n if (path === \"/api/months\") return getMonths();\n if (path === \"/api/moulds\") return getMoulds();\n if (path === \"/api/settings\" && method === \"GET\") return getSettings();\n if (path === \"/api/settings\" && method === \"POST\") {\n return updateSetting(await req.json());\n }\n if (path === \"/api/upload\" && method === \"POST\") {\n return uploadChunk(await req.json());\n }\n if (path === \"/api/whatsapp\") return getWhatsAppSummary(params);\n if (path === \"/api/whatsapp-send\" && method === \"POST\") {\n return sendWhatsApp(await req.json());\n }\n if (path === \"/api/prediction\") return getTrendPrediction();\n if (path === \"/api/production\" && method === \"POST\") {\n return addProduction(await req.json());\n }\n if (path === \"/api/production/batch\" && method === \"POST\") {\n return addProductionBatch(await req.json());\n }\n if (path === \"/api/production\" && method === \"DELETE\") {\n return deleteProduction(await req.json());\n }\n if (path === \"/api/production/batch-delete\" && method === \"POST\") {\n return deleteProductionBatch(await req.json());\n }\n if (path === \"/api/daily-detail-ids\") return getDailyDetailWithIds(params);\n if (path === \"/api/frequent-items\") return getFrequentItems();\n if (path === \"/api/prev-day\") return getPrevDayEntries(params);\n if (path === \"/api/ask\" && method === \"POST\") {\n return askQuestion(await req.json());\n }\n if (path === \"/api/export\") return getExportData(params);\n return err(\"Not found\", 404);\n } catch (e: any) {\n return err(\"Server error: \" + (e.message || String(e)), 500);\n }\n}","deleted","label",[102],"permalink","https://www.val.town/x/abhishekplating/PlatingProBeads/code/main.ts","extension","actionData","errors"]