[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_238":239,"_240":241},"routes/_app.x.$handle.$name",{"_98":97,"_33":46,"_107":88,"_28":108,"_13":14,"_109":-5,"_11":110,"_111":112,"_113":114,"_115":116,"_117":118,"_20":-5,"_119":-5,"_120":-5,"_121":27,"_122":123,"_124":25,"_125":-5,"_126":-5,"_127":128},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_44":45,"_33":46,"_39":47,"_48":-5,"_49":27,"_50":51,"_52":51,"_53":-5,"_54":-5,"_18":55,"_56":-5,"_57":27,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":69,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":81,"_82":83,"_84":85,"_86":27},"version",81,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":27,"_28":29,"_30":29},"file",{"_31":32,"_33":29,"_34":35,"_36":38,"_39":40,"_41":14,"_42":43,"_24":27},"previous",{"_31":32,"_33":29,"_34":35,"_36":37},"deleted",true,"deletedHere",false,"path","esmBackfill.ts","latestPath","fileId","fe57e388-6b7f-11f1-807a-1607ee4eb77e","name","fileType","http","content","/**\n * esmBackfill.ts (2026-06-18) — ESM 매출 DB 30일 백필 (일회용 HTTP val)\n * 최신 폴더의 30일치 파일을 읽어 모든 날짜를 ESM Notion DB에 upsert.\n * 실행: 이 val URL 1회 호출. 여러 번 호출해도 안전(중복 생성 없음).\n */\nimport * as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst ESM_NOTION_DB_ID = \"5ed8388163b1468fa24b0e966291fd35\";\nconst BLOB_RATIO_KEY = \"esm_brand_ratio_latest\";\n\nfunction dateKST(o) {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v) {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction normDate(v) {\n if (v === null || v === undefined || v === \"\") return null;\n let s;\n if (v instanceof Date) {\n s = v.toLocaleDateString(\"sv-SE\", { timeZone: \"Asia/Seoul\" });\n } else {\n s = String(v).trim();\n const compact = s.match(/^(\\d{4})(\\d{2})(\\d{2})$/);\n if (compact) s = `${compact[1]}-${compact[2]}-${compact[3]}`;\n }\n return /^\\d{4}-\\d{2}-\\d{2}$/.test(s) ? s : null;\n}\n\nasync function getDriveAccessToken() {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\"),\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\"),\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\"),\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId, token) {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownload(fileId, token) {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.arrayBuffer();\n}\nasync function findLatestDailyFolder(rootId, targetDate, token) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n ).sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\nfunction parseXlsxFind(buf, mustHaveCol) {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sheet = wb.Sheets[wb.SheetNames[0]];\n const aoa = XLSX.utils.sheet_to_json(sheet, {\n header: 1, defval: \"\", raw: false, dateNF: \"yyyy-mm-dd\",\n });\n let hdr = -1;\n for (let i = 0; i < Math.min(aoa.length, 15); i++) {\n if ((aoa[i] || []).some((c) => String(c).trim() === mustHaveCol)) { hdr = i; break; }\n }\n if (hdr === -1) return [];\n const headers = (aoa[hdr] || []).map((c) => String(c).trim());\n const out = [];\n for (let i = hdr + 1; i < aoa.length; i++) {\n const row = {};\n (aoa[i] || []).forEach((v, j) => { if (headers[j]) row[headers[j]] = v; });\n out.push(row);\n }\n return out;\n}\nasync function notionUpsert(dbId, targetDate, label, properties, status, insight) {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n const headers = {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n };\n const q = await fetch(`https://api.notion.com/v1/databases/${dbId}/query`, {\n method: \"POST\", headers,\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n });\n const existing = (await q.json()).results?.[0];\n const fullProps = {\n ...properties,\n \"날짜\": { title: [{ text: { content: `${targetDate} ${label} 일일 리포트` } }] },\n \"분석일\": { date: { start: targetDate } },\n \"상태\": { select: { name: status } },\n \"핵심 인사이트\": { rich_text: [{ text: { content: insight.slice(0, 2000) } }] },\n };\n if (existing) {\n const r = await fetch(`https://api.notion.com/v1/pages/${existing.id}`, {\n method: \"PATCH\", headers, body: JSON.stringify({ properties: fullProps }),\n });\n if (!r.ok) throw new Error(`Notion update ${r.status}: ${(await r.text()).slice(0, 200)}`);\n return \"updated\";\n } else {\n const r = await fetch(`https://api.notion.com/v1/pages`, {\n method: \"POST\", headers,\n body: JSON.stringify({ parent: { database_id: dbId }, properties: fullProps }),\n });\n if (!r.ok) throw new Error(`Notion create ${r.status}: ${(await r.text()).slice(0, 200)}`);\n return \"created\";\n }\n}\n\nexport default async function (req) {\n try {\n const url = new URL(req.url);\n const accessToken = await getDriveAccessToken();\n const targetDate = url.searchParams.get(\"date\") || dateKST(-1);\n\n let folderId = url.searchParams.get(\"folderId\");\n if (!folderId) {\n const folder = await findLatestDailyFolder(ESM_DRIVE_FOLDER_ID, targetDate, accessToken);\n if (!folder) throw new Error(\"ESM 최신 폴더 없음\");\n folderId = folder.id;\n }\n const files = await driveList(folderId, accessToken);\n\n const salesFiles = files.filter((f) => f.name.startsWith(\"일간 판매실적 현황\"));\n const pcAdFiles = files.filter((f) => f.name.startsWith(\"woojooware_report_daily_\"));\n const aiAdFiles = files.filter((f) => f.name.startsWith(\"AI매출업_일별리포트\"));\n\n const dl = async (f) => ({ buf: await driveDownload(f.id, accessToken) });\n const [salesB, pcB, aiB] = await Promise.all([\n Promise.all(salesFiles.map(dl)),\n Promise.all(pcAdFiles.map(dl)),\n Promise.all(aiAdFiles.map(dl)),\n ]);\n\n const byDate = new Map();\n const get = (d) => {\n let a = byDate.get(d);\n if (!a) { a = { rev: 0, orders: 0, ad: 0 }; byDate.set(d, a); }\n return a;\n };\n for (const x of salesB) {\n for (const r of parseXlsxFind(x.buf, \"기준일\")) {\n const d = normDate(r[\"기준일\"]); if (!d) continue;\n const a = get(d); a.rev += num(r[\"판매금액\"]); a.orders += num(r[\"판매건수\"]);\n }\n }\n for (const x of pcB) {\n for (const r of parseXlsxFind(x.buf, \"날짜\")) {\n const d = normDate(r[\"날짜\"]); if (!d) continue;\n get(d).ad += num(r[\"총비용\"]);\n }\n }\n for (const x of aiB) {\n for (const r of parseXlsxFind(x.buf, \"날짜별\")) {\n const d = normDate(r[\"날짜별\"]); if (!d) continue;\n get(d).ad += num(r[\"총비용\"]);\n }\n }\n\n let pnRatio = 1, ratioSource = \"기본값(매핑 없음)\";\n try {\n const ratio = await blob.getJSON(BLOB_RATIO_KEY);\n if (ratio && typeof ratio.pnRatio === \"number\") {\n pnRatio = ratio.pnRatio;\n ratioSource = `30일 누적 (계산일 ${ratio.calculatedAt?.slice(0, 10) || \"?\"})`;\n }\n } catch (e) { /* 기본값 */ }\n\n const dates = [...byDate.keys()].sort();\n const results = [];\n for (const d of dates) {\n const a = byDate.get(d);\n const pnRev = Math.round(a.rev * pnRatio);\n const tsRev = a.rev - pnRev;\n const pnOrders = Math.round(a.orders * pnRatio);\n const tsOrders = a.orders - pnOrders;\n const roas = a.ad > 0 ? Math.round(a.rev / a.ad * 100) : 0;\n let status = \"정상\";\n if (a.rev === 0) status = \"데이터 부족\";\n else if (a.ad > 0 && roas < 200) status = \"경고\";\n else if (a.ad > 0 && roas < 500) status = \"검토 필요\";\n const insight = `통합 매출 ₩${a.rev.toLocaleString()} (풍년 ${(pnRatio * 100).toFixed(1)}%, 비율 출처: ${ratioSource}), 주문 ${a.orders}건. 광고비 ₩${a.ad.toLocaleString()}${a.ad > 0 ? `, ROAS ${roas}%` : \"\"}. [백필]`;\n const props = {\n \"풍년보감 매출\": { number: pnRev },\n \"풍년보감 주문수\": { number: pnOrders },\n \"텅스페이스 매출\": { number: tsRev },\n \"텅스페이스 주문수\": { number: tsOrders },\n \"통합 매출\": { number: a.rev },\n \"통합 주문수\": { number: a.orders },\n \"광고비\": { number: a.ad },\n \"ROAS (%)\": { number: roas },\n };\n const action = await notionUpsert(ESM_NOTION_DB_ID, d, \"ESM 매출\", props, status, insight);\n results.push({ date: d, rev: a.rev, ad: a.ad, roas, action });\n }\n\n return Response.json({\n ok: true,\n folderId,\n daysProcessed: results.length,\n created: results.filter((r) => r.action === \"created\").length,\n updated: results.filter((r) => r.action === \"updated\").length,\n rows: results,\n }, { headers: { \"Cache-Control\": \"no-store\" } });\n } catch (err) {\n return Response.json({ ok: false, error: err.message, stack: err.stack }, { status: 500 });\n }\n}","/**\n * esmBackfill.ts (2026-06-18) — ESM 매출 DB 30일 백필 (일회용 HTTP val)\n * 최신 폴더의 30일치 파일을 읽어 모든 날짜를 ESM Notion DB에 upsert.\n * 실행: 이 val URL 1회 호출. 여러 번 호출해도 안전(중복 생성 없음).\n */\nimport * as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst ESM_NOTION_DB_ID = \"5ed8388163b1468fa24b0e966291fd35\";\nconst BLOB_RATIO_KEY = \"esm_brand_ratio_latest\";\n\nfunction dateKST(o) {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v) {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction normDate(v) {\n if (v === null || v === undefined || v === \"\") return null;\n let s;\n if (v instanceof Date) {\n s = v.toLocaleDateString(\"sv-SE\", { timeZone: \"Asia/Seoul\" });\n } else {\n s = String(v).trim();\n const compact = s.match(/^(\\d{4})(\\d{2})(\\d{2})$/);\n if (compact) s = `${compact[1]}-${compact[2]}-${compact[3]}`;\n }\n return /^\\d{4}-\\d{2}-\\d{2}$/.test(s) ? s : null;\n}\n\nasync function getDriveAccessToken() {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\"),\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\"),\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\"),\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId, token) {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownload(fileId, token) {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.arrayBuffer();\n}\nasync function findLatestDailyFolder(rootId, targetDate, token) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n ).sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\nfunction parseXlsxFind(buf, mustHaveCol) {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sheet = wb.Sheets[wb.SheetNames[0]];\n const aoa = XLSX.utils.sheet_to_json(sheet, {\n header: 1,\n defval: \"\",\n raw: false,\n dateNF: \"yyyy-mm-dd\",\n });\n let hdr = -1;\n for (let i = 0; i < Math.min(aoa.length, 15); i++) {\n if ((aoa[i] || []).some((c) => String(c).trim() === mustHaveCol)) {\n hdr = i;\n break;\n }\n }\n if (hdr === -1) return [];\n const headers = (aoa[hdr] || []).map((c) => String(c).trim());\n const out = [];\n for (let i = hdr + 1; i < aoa.length; i++) {\n const row = {};\n (aoa[i] || []).forEach((v, j) => {\n if (headers[j]) row[headers[j]] = v;\n });\n out.push(row);\n }\n return out;\n}\nasync function notionUpsert(\n dbId,\n targetDate,\n label,\n properties,\n status,\n insight,\n) {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n const headers = {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n };\n const q = await fetch(`https://api.notion.com/v1/databases/${dbId}/query`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n });\n const existing = (await q.json()).results?.[0];\n const fullProps = {\n ...properties,\n \"날짜\": {\n title: [{ text: { content: `${targetDate} ${label} 일일 리포트` } }],\n },\n \"분석일\": { date: { start: targetDate } },\n \"상태\": { select: { name: status } },\n \"핵심 인사이트\": {\n rich_text: [{ text: { content: insight.slice(0, 2000) } }],\n },\n };\n if (existing) {\n const r = await fetch(`https://api.notion.com/v1/pages/${existing.id}`, {\n method: \"PATCH\",\n headers,\n body: JSON.stringify({ properties: fullProps }),\n });\n if (!r.ok) {\n throw new Error(\n `Notion update ${r.status}: ${(await r.text()).slice(0, 200)}`,\n );\n }\n return \"updated\";\n } else {\n const r = await fetch(`https://api.notion.com/v1/pages`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n parent: { database_id: dbId },\n properties: fullProps,\n }),\n });\n if (!r.ok) {\n throw new Error(\n `Notion create ${r.status}: ${(await r.text()).slice(0, 200)}`,\n );\n }\n return \"created\";\n }\n}\n\nexport default async function (req) {\n try {\n const url = new URL(req.url);\n const accessToken = await getDriveAccessToken();\n const targetDate = url.searchParams.get(\"date\") || dateKST(-1);\n\n let folderId = url.searchParams.get(\"folderId\");\n if (!folderId) {\n const folder = await findLatestDailyFolder(\n ESM_DRIVE_FOLDER_ID,\n targetDate,\n accessToken,\n );\n if (!folder) throw new Error(\"ESM 최신 폴더 없음\");\n folderId = folder.id;\n }\n const files = await driveList(folderId, accessToken);\n\n const salesFiles = files.filter((f) =>\n f.name.startsWith(\"일간 판매실적 현황\")\n );\n const pcAdFiles = files.filter((f) =>\n f.name.startsWith(\"woojooware_report_daily_\")\n );\n const aiAdFiles = files.filter((f) =>\n f.name.startsWith(\"AI매출업_일별리포트\")\n );\n\n const dl = async (f) => ({ buf: await driveDownload(f.id, accessToken) });\n const [salesB, pcB, aiB] = await Promise.all([\n Promise.all(salesFiles.map(dl)),\n Promise.all(pcAdFiles.map(dl)),\n Promise.all(aiAdFiles.map(dl)),\n ]);\n\n const byDate = new Map();\n const get = (d) => {\n let a = byDate.get(d);\n if (!a) {\n a = { rev: 0, orders: 0, ad: 0 };\n byDate.set(d, a);\n }\n return a;\n };\n for (const x of salesB) {\n for (const r of parseXlsxFind(x.buf, \"기준일\")) {\n const d = normDate(r[\"기준일\"]);\n if (!d) continue;\n const a = get(d);\n a.rev += num(r[\"판매금액\"]);\n a.orders += num(r[\"판매건수\"]);\n }\n }\n for (const x of pcB) {\n for (const r of parseXlsxFind(x.buf, \"날짜\")) {\n const d = normDate(r[\"날짜\"]);\n if (!d) continue;\n get(d).ad += num(r[\"총비용\"]);\n }\n }\n for (const x of aiB) {\n for (const r of parseXlsxFind(x.buf, \"날짜별\")) {\n const d = normDate(r[\"날짜별\"]);\n if (!d) continue;\n get(d).ad += num(r[\"총비용\"]);\n }\n }\n\n let pnRatio = 1, ratioSource = \"기본값(매핑 없음)\";\n try {\n const ratio = await blob.getJSON(BLOB_RATIO_KEY);\n if (ratio && typeof ratio.pnRatio === \"number\") {\n pnRatio = ratio.pnRatio;\n ratioSource = `30일 누적 (계산일 ${\n ratio.calculatedAt?.slice(0, 10) || \"?\"\n })`;\n }\n } catch (e) { /* 기본값 */ }\n\n const dates = [...byDate.keys()].sort();\n const results = [];\n for (const d of dates) {\n const a = byDate.get(d);\n if (a.rev === 0) continue; // 매출 없는 날(불완전 폴더의 광고전용 등) 건너뜀 — 잡row 방지\n const pnRev = Math.round(a.rev * pnRatio);\n const tsRev = a.rev - pnRev;\n const pnOrders = Math.round(a.orders * pnRatio);\n const tsOrders = a.orders - pnOrders;\n const roas = a.ad > 0 ? Math.round(a.rev / a.ad * 100) : 0;\n let status = \"정상\";\n if (a.rev === 0) status = \"데이터 부족\";\n else if (a.ad > 0 && roas < 200) status = \"경고\";\n else if (a.ad > 0 && roas < 500) status = \"검토 필요\";\n const insight = `통합 매출 ₩${a.rev.toLocaleString()} (풍년 ${\n (pnRatio * 100).toFixed(1)\n }%, 비율 출처: ${ratioSource}), 주문 ${a.orders}건. 광고비 ₩${a.ad.toLocaleString()}${\n a.ad > 0 ? `, ROAS ${roas}%` : \"\"\n }. [백필]`;\n const props = {\n \"풍년보감 매출\": { number: pnRev },\n \"풍년보감 주문수\": { number: pnOrders },\n \"텅스페이스 매출\": { number: tsRev },\n \"텅스페이스 주문수\": { number: tsOrders },\n \"통합 매출\": { number: a.rev },\n \"통합 주문수\": { number: a.orders },\n \"광고비\": { number: a.ad },\n \"ROAS (%)\": { number: roas },\n };\n const action = await notionUpsert(\n ESM_NOTION_DB_ID,\n d,\n \"ESM 매출\",\n props,\n status,\n insight,\n );\n results.push({ date: d, rev: a.rev, ad: a.ad, roas, action });\n }\n\n return Response.json({\n ok: true,\n folderId,\n daysProcessed: results.length,\n created: results.filter((r) => r.action === \"created\").length,\n updated: results.filter((r) => r.action === \"updated\").length,\n rows: results,\n }, { headers: { \"Cache-Control\": \"no-store\" } });\n } catch (err) {\n return Response.json({ ok: false, error: err.message, stack: err.stack }, {\n status: 500,\n });\n }\n}","createdAt",["D",1781833939313],"branchVersion","branchId","ef84c82c-4cde-11f1-bca8-ee650bb23af1","id","ef82bd7a-4cde-11f1-bca8-ee650bb23af1","Marketingauto",["D",1778465580996],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","imageUrl","93449a43-3797-404d-b4ae-d751bcdd306b","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019e14ce-2bdb-768c-a9d4-d59a30b7544b","tursoName","ef82bd7a4cde11f1bca8ee650bb23af1","tursoHostname","ef82bd7a4cde11f1bca8ee650bb23af1-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789578655349],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_98":97,"_106":102},"org",{"_98":97,"_99":100,"_101":102,"_18":55,"_103":104,"_105":27},"branch",{"_13":87,"_33":88,"_44":43,"_89":45,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":46,"_96":97},"isOwned",134,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","joowoo","handle","displayName","joo woo","avatar","https://lh3.googleusercontent.com/a/ACg8ocJHI4ZtyHAcRli0zHzjn03GuJOb_BpuVst0ZtYzcbbGbLzMGA=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_44":45,"_33":46,"_39":233,"_48":-5,"_49":27,"_50":51,"_52":51,"_53":-5,"_54":-5,"_18":55,"_56":-5,"_57":27,"_58":-5,"_59":-5,"_60":61,"_62":63,"_64":65,"_66":67,"_68":234,"_70":-5,"_71":72,"_73":72,"_74":75,"_76":75,"_77":75,"_78":72,"_79":72,"_80":235,"_82":236,"_84":237,"_86":27},"tree",[204,205,206,207,208,209,210],"files",[204,205,206,207,208,209,210],"counts",{"_191":192,"_193":194,"_195":61,"_196":197,"_198":199},"contents",[129,130,131,132,133,134,135],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_44":184,"_33":185,"_31":186,"_139":-5,"_42":43,"_41":187,"_141":25,"_39":188,"_13":173,"_34":144,"_36":189,"_24":27,"_146":-5,"_28":190},{"_44":176,"_33":177,"_31":178,"_139":-5,"_42":43,"_41":179,"_141":25,"_39":180,"_13":181,"_34":144,"_36":182,"_24":27,"_146":-5,"_28":183},{"_44":168,"_33":169,"_31":170,"_139":-5,"_42":43,"_41":171,"_141":25,"_39":172,"_13":173,"_34":144,"_36":174,"_24":27,"_146":-5,"_28":175},{"_44":164,"_33":29,"_31":32,"_139":-5,"_42":43,"_41":14,"_141":27,"_39":165,"_13":166,"_34":35,"_36":38,"_24":27,"_146":-5,"_28":167},{"_44":156,"_33":157,"_31":158,"_139":-5,"_42":43,"_41":159,"_141":25,"_39":160,"_13":161,"_34":35,"_36":162,"_24":27,"_146":-5,"_28":163},{"_44":148,"_33":149,"_31":150,"_139":-5,"_42":43,"_41":151,"_141":27,"_39":152,"_13":153,"_34":144,"_36":154,"_24":27,"_146":-5,"_28":155},{"_44":136,"_33":137,"_31":138,"_139":-5,"_42":43,"_41":140,"_141":25,"_39":142,"_13":143,"_34":144,"_36":145,"_24":27,"_146":-5,"_28":147},"66dbd250-d3f5-412c-aec5-961e637df67b","weeklyBrandRatioSync.ts","2b7f02ba-6a31-11f1-b377-1607ee4eb77e","parentId",51,"latest",["D",1781744680319],10,"interval","/**\n * weeklyBrandRatioSync.ts v4 (2026-06-17 패치) — 주 1회 일요일 02:00 KST\n *\n * v3 대비 변경:\n * - Slack 알림 완전 제거 (불필요)\n * - 결과는 console.log 및 return 값으로만 확인. 필요 시 val.town Logs 에서 직접 확인.\n * - 실패해도 Slack 안 보내고 그냥 throw\n *\n * ⚠️ Cron 설정 주의:\n * val.town 에서 스케줄을 반드시 \"0 17 * * 6\" (UTC 토요일 17:00 = KST 일요일 02:00) 으로 설정.\n * 현재 \"0 * * * *\" (매시간) 으로 잘못 등록되어 있으면 매시간 비율 재계산이 발생함.\n *\n * Blob key: esm_brand_ratio_latest\n */\nimport * as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst BLOB_RATIO_KEY = \"esm_brand_ratio_latest\";\nconst TONGSPACE_KEYWORDS = [\n \"텅스\",\n \"곤약\",\n \"쫀드기\",\n \"쫀디기\",\n \"쫀뜨기\",\n \"스파이크샷\",\n];\n\nfunction dateKST(o: number): string {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v: any): number {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction classifyBrand(name: string): \"풍년보감\" | \"텅스페이스\" {\n for (const kw of TONGSPACE_KEYWORDS) {\n if (name.includes(kw)) return \"텅스페이스\";\n }\n return \"풍년보감\";\n}\n\nasync function getDriveAccessToken(): Promise {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\")!,\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\")!,\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\")!,\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId: string, token: string): Promise {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownload(\n fileId: string,\n token: string,\n): Promise {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.arrayBuffer();\n}\nasync function findLatestDailyFolder(\n rootId: string,\n targetDate: string,\n token: string,\n) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n ).sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\n\nfunction parseXlsx(buf: ArrayBuffer): Record[] {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sheet = wb.Sheets[wb.SheetNames[0]];\n return XLSX.utils.sheet_to_json(sheet, {\n defval: \"\",\n raw: false,\n dateNF: \"yyyy-mm-dd\",\n });\n}\n\nexport default async function () {\n const targetDate = dateKST(-1);\n const accessToken = await getDriveAccessToken();\n\n const folder = await findLatestDailyFolder(\n ESM_DRIVE_FOLDER_ID,\n targetDate,\n accessToken,\n );\n if (!folder) throw new Error(\"ESM 어제 폴더 없음\");\n const files = await driveList(folder.id, accessToken);\n const productReportFile = files.find((f) =>\n f.name.startsWith(\"AI매출업_상품별리포트_\")\n );\n if (!productReportFile) throw new Error(\"AI매출업_상품별리포트 파일 없음\");\n\n const esmRoot = await driveList(ESM_DRIVE_FOLDER_ID, accessToken);\n const productListFile = esmRoot.find((f) =>\n f.name.startsWith(\"goods-list-result_\")\n );\n if (!productListFile) {\n throw new Error(\"goods-list-result_상품리스트 파일 없음\");\n }\n\n const [productReportBuf, productListBuf] = await Promise.all([\n driveDownload(productReportFile.id, accessToken),\n driveDownload(productListFile.id, accessToken),\n ]);\n\n const productList = parseXlsx(productListBuf);\n const skuToName = new Map();\n for (const r of productList) {\n const id = String(r[\"상품번호\"] || \"\").trim();\n const name = String(r[\"상품명\"] || \"\").trim();\n if (id && name) skuToName.set(id, name);\n }\n\n const productReport = parseXlsx(productReportBuf);\n let pn30 = 0, ts30 = 0, matchedCount = 0, unmatchedCount = 0;\n for (const r of productReport) {\n const sku = String(r[\"상품번호\"] || \"\").trim();\n const nameFromMaster = skuToName.get(sku);\n const name = nameFromMaster || String(r[\"그룹명\"] || \"\");\n const brand = classifyBrand(name);\n const rev = num(r[\"판매자기준-구매금액\"]);\n if (rev <= 0) continue;\n if (brand === \"텅스페이스\") ts30 += rev;\n else pn30 += rev;\n if (nameFromMaster) matchedCount++;\n else unmatchedCount++;\n }\n\n const sum30 = pn30 + ts30;\n if (sum30 === 0) throw new Error(\"30일 누적 매출이 0 — 데이터 점검 필요\");\n const pnRatio = pn30 / sum30;\n const tsRatio = 1 - pnRatio;\n\n const payload = {\n pnRatio,\n tsRatio,\n pn30,\n ts30,\n calculatedAt: new Date().toISOString(),\n targetDate,\n productCount: productReport.length,\n masterMatchedCount: matchedCount,\n masterUnmatchedCount: unmatchedCount,\n };\n await blob.setJSON(BLOB_RATIO_KEY, payload);\n\n console.log(\n `[weeklyBrandRatioSync v4] ${targetDate} | pn ${\n (pnRatio * 100).toFixed(1)\n }% / ts ${\n (tsRatio * 100).toFixed(1)\n }% | 매핑 ${matchedCount}/${unmatchedCount}`,\n );\n return { ok: true, ...payload };\n}","label",[137],"140188f3-8315-4188-9ea2-45c36139dcac","slackDailyCron.ts","0dd272b4-4f66-11f1-be74-ee650bb23af1",71,["D",1781760297576],13,"/**\n * 네이버 마케팅 일일 슬랙 발송 — val.town Cron Val\n *\n * v3 변경 (2026-06-18):\n * - ESM 매출 버튼이 노션 페이지로 열리던 문제 수정 → ESM HTML 리포트(esmReport.ts)로 연결\n * - ESM_REPORT_URL 상수 추가 (env 우선, 기본값 코드에 박힘)\n * - esmUrl(노션 페이지/DB)은 로그·리턴값 용도로만 유지 (버튼은 더 이상 사용 안 함)\n *\n * v2 변경 (2026-06-17):\n * - ESM 매출 버튼 추가 (4번째 버튼) → ESM Notion DB 또는 어제 ESM row로 링크\n * - 어제 ESM row가 있으면 직접 그 페이지로, 없으면 DB 자체로 fallback\n *\n * 배포 방법:\n * 1. val.town → 기존 slackDailyCron.ts 열기\n * 2. 이 코드 전체로 교체\n * 3. (선택) 환경변수: ESM_REPORT_URL (기본값 코드에 박혀 있어 생략 가능)\n * - ESM_NOTION_DB_ID = \"5ed8388163b1468fa24b0e966291fd35\" (기본값 코드에 박혀 있어 생략 가능)\n * 4. main 브랜치 저장 + cron 스케줄 \"0 23 * * *\"(08:00 KST) 그대로인지 확인\n */\nimport { blob } from \"https://esm.town/v/std/blob\";\nconst API_BASE = \"https://api.searchad.naver.com\";\nconst BLOB_AI_KEY = \"ai_analysis_latest\";\nconst TONGSPACE_KEYWORDS = [\n \"텅스\",\n \"곤약\",\n \"쫀드기\",\n \"쫀디기\",\n \"쫀뜨기\",\n \"스파이크샷\",\n];\nconst NOTION_DB_DEFAULT = \"5828d1bf-aa95-4662-bdf2-fae160d0654f\";\nconst ESM_NOTION_DB_DEFAULT = \"5ed8388163b1468fa24b0e966291fd35\"; // 신규\nconst REPORT_URL =\n \"https://joowoo--f970e0684cf811f1801eee650bb23af1.web.val.run\";\nconst TOSS_REPORT_URL = Deno.env.get(\"TOSS_REPORT_URL\") ||\n \"https://joowoo--8a0b13c04eb911f1b7d6ee650bb23af1.web.val.run\";\nconst ESM_REPORT_URL = Deno.env.get(\"ESM_REPORT_URL\") ||\n \"https://joowoo--53facbda6a2b11f1b8741607ee4eb77e.web.val.run\"; // 신규 — ESM HTML 리포트\nconst SLACK_CHANNEL_ID = Deno.env.get(\"SLACK_CHANNEL_ID\") || \"C09B0J6QXN1\"; // #마케팅-data\nconst MARKETING_CELL_SUBTEAM = Deno.env.get(\"MARKETING_CELL_SUBTEAM\") ||\n \"S09PHRXUR0W\";\nasync function fetchTossKPI(targetDate: string): Promise {\n try {\n const resp = await fetch(`${TOSS_REPORT_URL}?kpi=1&date=${targetDate}`);\n if (!resp.ok) return null;\n return await resp.json();\n } catch (e) {\n return null;\n }\n}\nfunction classifyBrand(name: string): \"풍년보감\" | \"텅스페이스\" {\n for (const kw of TONGSPACE_KEYWORDS) {\n if (name.includes(kw)) return \"텅스페이스\";\n }\n return \"풍년보감\";\n}\nfunction dateKST(o: number): string {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction stripTags(s: string): string {\n return s.replace(/<[^>]+>/g, \"\").replace(/ /g, \" \").replace(\n /&/g,\n \"&\",\n ).replace(/</g, \"<\").replace(/>/g, \">\").replace(/"/g, '\"').trim();\n}\nfunction fmt(n: number): string {\n return n.toLocaleString(\"ko-KR\") + \"원\";\n}\nasync function makeSignature(\n secretKey: string,\n ts: string,\n method: string,\n uri: string,\n): Promise {\n const enc = new TextEncoder();\n const key = await crypto.subtle.importKey(\n \"raw\",\n enc.encode(secretKey),\n { name: \"HMAC\", hash: \"SHA-256\" },\n false,\n [\"sign\"],\n );\n const sig = await crypto.subtle.sign(\n \"HMAC\",\n key,\n enc.encode(`${ts}.${method}.${uri}`),\n );\n return btoa(String.fromCharCode(...new Uint8Array(sig)));\n}\nasync function callNaver(\n uri: string,\n qs: Record = {},\n): Promise {\n const apiKey = Deno.env.get(\"NAVER_SA_API_KEY\")!;\n const secretKey = Deno.env.get(\"NAVER_SA_SECRET_KEY\")!;\n const customerId = Deno.env.get(\"NAVER_SA_CUSTOMER_ID\")!;\n const ts = String(Date.now());\n const sig = await makeSignature(secretKey, ts, \"GET\", uri);\n const url = new URL(`${API_BASE}${uri}`);\n for (const [k, v] of Object.entries(qs)) url.searchParams.set(k, v);\n const res = await fetch(url.toString(), {\n headers: {\n \"X-Timestamp\": ts,\n \"X-API-KEY\": apiKey,\n \"X-Customer\": customerId,\n \"X-Signature\": sig,\n },\n });\n if (!res.ok) throw new Error(`${uri} ${res.status}`);\n return res.json();\n}\nasync function fetchYesterdayKPI(targetDate: string) {\n const campaigns: any[] = await callNaver(\"/ncc/campaigns\");\n const cmap = new Map(\n campaigns.map((\n c: any,\n ) => [c.nccCampaignId, { ...c, brand: classifyBrand(c.name) }]),\n );\n const ids = campaigns.map((c: any) => c.nccCampaignId).join(\",\");\n const stats = await callNaver(\"/stats\", {\n ids,\n fields: '[\"impCnt\",\"clkCnt\",\"salesAmt\",\"ccnt\",\"ctr\",\"cpc\",\"convAmt\"]',\n timeRange: JSON.stringify({ since: targetDate, until: targetDate }),\n });\n const kpi: Record = {\n \"풍년보감\": { spend: 0, rev: 0, conv: 0 },\n \"텅스페이스\": { spend: 0, rev: 0, conv: 0 },\n };\n for (const s of (stats?.data || [])) {\n const c = cmap.get(s.id);\n if (!c) continue;\n kpi[c.brand].spend += s.salesAmt || 0;\n kpi[c.brand].rev += s.convAmt || 0;\n kpi[c.brand].conv += s.ccnt || 0;\n }\n const pn = kpi[\"풍년보감\"];\n const ts = kpi[\"텅스페이스\"];\n const total = {\n spend: pn.spend + ts.spend,\n rev: pn.rev + ts.rev,\n conv: pn.conv + ts.conv,\n };\n return {\n pn,\n ts,\n total,\n pnRoas: pn.spend > 0 ? Math.round(pn.rev / pn.spend * 100) : 0,\n tsRoas: ts.spend > 0 ? Math.round(ts.rev / ts.spend * 100) : 0,\n totalRoas: total.spend > 0 ? Math.round(total.rev / total.spend * 100) : 0,\n };\n}\nfunction extractSectionItems(\n html: string,\n sectionTitle: string,\n max: number = 3,\n): string[] {\n const re = new RegExp(`

${sectionTitle}

([\\\\s\\\\S]*?)(?=

|$)`, \"\");\n const m = html.match(re);\n if (!m) return [];\n const content = m[1];\n const items: string[] = [];\n const liRe = /
([\\s\\S]?)<\\/li>/g;\n let lm;\n while ((lm = liRe.exec(content)) !== null) {\n const t = stripTags(lm[1]);\n if (t) items.push(t);\n if (items.length >= max) return items;\n }\n if (items.length > 0) return items;\n const rowRe = /([\\s\\S]?)<\\/tr>/g;\n let rm;\n let isFirst = true;\n while ((rm = rowRe.exec(content)) !== null) {\n if (isFirst) {\n isFirst = false;\n continue;\n }\n const cellRe = /]>([\\s\\S]?)<\\/t[hd]>/g;\n const cells: string[] = [];\n let cm;\n while ((cm = cellRe.exec(rm[1])) !== null) cells.push(stripTags(cm[1]));\n if (cells.length) items.push(cells.join(\" · \"));\n if (items.length >= max) return items;\n }\n return items;\n}\nfunction extractInsight(html: string): string {\n const re = /
핵심 인사이트<\\/h3>\\s*
([\\s\\S]?)<\\/p>/;\n const m = html.match(re);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html: string): string {\n const re = /
어제 권장 액션 후속 추적<\\/h3>([\\s\\S]?)(?=
|$)/;\n const m = html.match(re);\n if (!m) return \"전일 분석 데이터 없음\";\n const text = stripTags(m[1]).slice(0, 400);\n return text || \"전일 분석 데이터 없음\";\n}\nasync function findYesterdayNotionPage(\n targetDate: string,\n): Promise {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n const dbId = Deno.env.get(\"NOTION_DB_ID\") || NOTION_DB_DEFAULT;\n if (!token) return null;\n try {\n const resp = await fetch(\n `https://api.notion.com/v1/databases/${dbId}/query`,\n {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n },\n );\n if (!resp.ok) return null;\n const data = await resp.json();\n return data.results?.[0]?.url || null;\n } catch (e) {\n return null;\n }\n}\n// ━━━ ESM Notion DB에서 어제 row 페이지 URL 찾기 (로그/리턴 참고용) ━━━\nasync function findYesterdayESMPage(targetDate: string): Promise {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n const dbId = Deno.env.get(\"ESM_NOTION_DB_ID\") || ESM_NOTION_DB_DEFAULT;\n const dbUrl = \"https://www.notion.so/\" + dbId.replace(/-/g, \"\");\n if (!token) return dbUrl;\n try {\n const resp = await fetch(\n `https://api.notion.com/v1/databases/${dbId}/query`,\n {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n },\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n },\n );\n if (!resp.ok) return dbUrl;\n const data = await resp.json();\n return data.results?.[0]?.url || dbUrl; // row 있으면 그 페이지, 없으면 DB 자체\n } catch (e) {\n return dbUrl;\n }\n}\nasync function slackPost(\n method: string,\n body: any,\n token: string,\n): Promise {\n const resp = await fetch(`https://slack.com/api/${method}`, {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Content-Type\": \"application/json; charset=utf-8\",\n },\n body: JSON.stringify(body),\n });\n const data = await resp.json();\n if (!data.ok) throw new Error(`Slack ${method} failed: ${data.error}`);\n return data;\n}\nexport default async function () {\n const yesterday = dateKST(-1);\n const slackToken = Deno.env.get(\"SLACK_BOT_TOKEN\");\n if (!slackToken) throw new Error(\"SLACK_BOT_TOKEN not set\");\n let aiAnalysis: any = null;\n try {\n aiAnalysis = await blob.getJSON(BLOB_AI_KEY);\n } catch (e) {}\n const aiHtml = aiAnalysis?.html || \"\";\n // 어제 KPI 페치 (네이버 SA + 토스 + ESM Notion 페이지 URL 병렬)\n const [k, tossKpi, notionUrl, esmUrl] = await Promise.all([\n fetchYesterdayKPI(yesterday),\n fetchTossKPI(yesterday),\n findYesterdayNotionPage(yesterday).then((u) =>\n u ||\n \"https://www.notion.so/\" +\n (Deno.env.get(\"NOTION_DB_ID\") || NOTION_DB_DEFAULT).replace(/-/g, \"\")\n ),\n findYesterdayESMPage(yesterday), // 로그/리턴 참고용 (버튼은 ESM_REPORT_URL 사용)\n ]);\n const insight = extractInsight(aiHtml);\n const signals = extractSectionItems(aiHtml, \"핵심 인사이트\", 0).length === 0\n ? extractSectionItems(aiHtml, \"캠페인 단위 데일리 액션\", 3)\n : [];\n const signalsFinal = signals.length > 0\n ? signals\n : (insight\n ? [insight]\n : [\"AI 분석 데이터 없음 — 오늘 07:30 정기 실행 후 적재 예정\"]);\n const top3 = extractSectionItems(aiHtml, \"오늘 즉시 실행 우선순위\", 3);\n const top3Final = top3.length > 0\n ? top3\n : [\"AI 분석 미작성 — 데이터 확인 후 수동 액션 검토 필요\"];\n const followup = extractFollowup(aiHtml);\n // 메인 메시지 — Block Kit actions 에 ESM 버튼 1개 추가 (4번째)\n const mainBlocks = [\n {\n type: \"section\",\n text: {\n type: \"mrkdwn\",\n text:\n ` 좋은 아침! 어제 (${yesterday}) 마케팅 리포트가 준비됐어요.\\n\\nPNBG 풍년보감 + 텅스페이스의 검색광고 성과, 토스쇼핑 매출, ESM(G마켓+옥션) 매출을 한눈에 확인하세요.`,\n },\n },\n {\n type: \"actions\",\n elements: [\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"네이버 검색광고 열기\" },\n url: REPORT_URL,\n style: \"primary\",\n },\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"토스 매출 열기\" },\n url: TOSS_REPORT_URL,\n },\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"ESM 매출 열기\" },\n url: ESM_REPORT_URL,\n }, // v3: 노션(esmUrl) → ESM HTML 리포트로 변경\n {\n type: \"button\",\n text: { type: \"plain_text\", text: \"노션 페이지\" },\n url: notionUrl,\n },\n ],\n },\n ];\n const mainResp = await slackPost(\"chat.postMessage\", {\n channel: SLACK_CHANNEL_ID,\n text: `${yesterday} 마케팅 리포트 준비됨`,\n blocks: mainBlocks,\n unfurl_links: false,\n unfurl_media: false,\n }, slackToken);\n const threadTs = mainResp.ts;\n const cell = (text: string, bold = false) => ({\n type: \"rich_text\",\n elements: [{\n type: \"rich_text_section\",\n elements: [{\n type: \"text\",\n text,\n ...(bold ? { style: { bold: true } } : {}),\n }],\n }],\n });\n const signalsBlock = `핵심 시그널 ${signalsFinal.length}가지\\n` +\n signalsFinal.slice(0, 3).map((s, i) => `${i + 1}. ${s.slice(0, 250)}`).join(\n \"\\n\",\n );\n const top3Block = `오늘 실행 TOP 3\\n` +\n top3Final.slice(0, 3).map((s, i) => `${i + 1}. ${s.slice(0, 250)}`).join(\n \"\\n\",\n );\n const followupBlock = `어제 집행률\\n${followup.slice(0, 300)}`;\n const blocks: any[] = [\n {\n type: \"section\",\n text: { type: \"mrkdwn\", text: `네이버 검색광고 KPI (${yesterday})` },\n },\n {\n type: \"table\",\n rows: [\n [\n cell(\"구분\", true),\n cell(\"광고비\", true),\n cell(\"매출\", true),\n cell(\"ROAS\", true),\n ],\n [\n cell(\"통합\"),\n cell(fmt(k.total.spend)),\n cell(fmt(k.total.rev)),\n cell(`${k.totalRoas.toLocaleString()}%`),\n ],\n [\n cell(\"풍년보감\"),\n cell(fmt(k.pn.spend)),\n cell(fmt(k.pn.rev)),\n cell(`${k.pnRoas.toLocaleString()}%`),\n ],\n [\n cell(\"텅스페이스\"),\n cell(fmt(k.ts.spend)),\n cell(fmt(k.ts.rev)),\n cell(`${k.tsRoas.toLocaleString()}%`),\n ],\n ],\n },\n ];\n if (tossKpi && !tossKpi.error) {\n blocks.push({ type: \"divider\" });\n blocks.push({\n type: \"section\",\n text: { type: \"mrkdwn\", text: `토스쇼핑 매출 KPI (${yesterday})` },\n });\n blocks.push({\n type: \"table\",\n rows: [\n [cell(\"구분\", true), cell(\"매출\", true), cell(\"주문수\", true)],\n [\n cell(\"통합\"),\n cell(fmt(tossKpi.totalRev || 0)),\n cell(`${(tossKpi.totalOrders || 0).toLocaleString()}건`),\n ],\n [\n cell(\"풍년보감\"),\n cell(fmt(tossKpi.pn?.rev || 0)),\n cell(`${(tossKpi.pn?.orders || 0).toLocaleString()}건`),\n ],\n [\n cell(\"텅스페이스\"),\n cell(fmt(tossKpi.ts?.rev || 0)),\n cell(`${(tossKpi.ts?.orders || 0).toLocaleString()}건`),\n ],\n ],\n });\n }\n blocks.push({ type: \"divider\" });\n blocks.push({\n type: \"section\",\n text: { type: \"mrkdwn\", text: signalsBlock },\n });\n blocks.push({ type: \"section\", text: { type: \"mrkdwn\", text: top3Block } });\n blocks.push({\n type: \"section\",\n text: { type: \"mrkdwn\", text: followupBlock },\n });\n blocks.push({\n type: \"context\",\n elements: [{\n type: \"mrkdwn\",\n text:\n \"_액션플랜을 수정하시려면 이 스레드에 ✏️수정 액션플랜: 으로 시작하는 댓글을 작성하세요. 5분 내 보고서와 노션에 자동 반영됩니다. ESM/GFA 데이터는 11:00 영업팀 업로드 후 별도 댓글로 추가됩니다._\",\n }],\n });\n await slackPost(\"chat.postMessage\", {\n channel: SLACK_CHANNEL_ID,\n text: `요약 KPI ${yesterday} — ${k.totalRoas}% ROAS`,\n blocks,\n thread_ts: threadTs,\n unfurl_links: false,\n unfurl_media: false,\n }, slackToken);\n console.log(\n `[OK] Slack 발송 완료 — ${yesterday} | main_ts=${threadTs} | notion=${notionUrl} | esm_report=${ESM_REPORT_URL} | esm_notion=${esmUrl}`,\n );\n return {\n ok: true,\n date: yesterday,\n mainTs: threadTs,\n notionUrl,\n esmReportUrl: ESM_REPORT_URL,\n esmNotionUrl: esmUrl,\n };\n}",[149],"7baac7b2-b886-4172-9b49-6acdff97e727","removeSlack.ts","0d1d2c9a-6acf-11f1-9a5f-1607ee4eb77e",72,["D",1781760512003],12,"/**\n * cleanupPNBGBot.ts — \"PNBG 마케팅 인사이트\" 봇 메시지 일괄 삭제\n \n 핵심:\n * - bot_id = \"B0B3FM7G2CW\" (PNBG 마케팅 인사이트) 메시지만 대상\n * - 다른 봇 (\"항목 업데이트 알림\" 등) 은 건드리지 않음 — 안전\n * - 스레드 답글까지 모두 삭제\n \n 사용:\n * 1. val.town → 기존 코드 자리에 이 코드 교체 → Save\n * 2. [▶ Run] → 응답 확인\n * 3. found = 0 나올 때까지 반복 Run\n * 4. 작업 끝나면 이 Val 자체 삭제\n \n 환경변수: SLACK_BOT_TOKEN\n /\n\nconst SLACK_CHANNEL = \"C09B0J6QXN1\";\n\n// 삭제 대상 봇 ID (PNBG 마케팅 인사이트만)\nconst TARGET_BOT_IDS = [\"B0B3FM7G2CW\"];\n\n// 추가 안전장치: app_id 도 매칭\nconst TARGET_APP_IDS = [\"A0B328T36PR\"];\n\nfunction isTargetBot(msg: any): boolean {\n if (msg.bot_id && TARGET_BOT_IDS.includes(msg.bot_id)) return true;\n if (msg.app_id && TARGET_APP_IDS.includes(msg.app_id)) return true;\n // username 으로도 한 번 더 매칭 (혹시 bot_id 가 다를 때 대비)\n const name = msg.username || msg.bot_profile?.name || \"\";\n if (\n name.includes(\"PNBG 마케팅 인사이트\") || name.includes(\"마케팅 인사이트 봇\")\n ) return true;\n return false;\n}\n\nasync function slackApi(\n method: string,\n params: Record,\n token: string,\n): Promise {\n const url = `https://slack.com/api/${method}`;\n const isGet = method === \"conversations.history\" ||\n method === \"conversations.replies\";\n if (isGet) {\n const qs = new URLSearchParams(params as any).toString();\n const r = await fetch(`${url}?${qs}`, {\n headers: { \"Authorization\": `Bearer ${token}` },\n });\n return r.json();\n } else {\n const r = await fetch(url, {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Content-Type\": \"application/json; charset=utf-8\",\n },\n body: JSON.stringify(params),\n });\n return r.json();\n }\n}\n\nexport default async function (): Promise {\n const token = Deno.env.get(\"SLACK_BOT_TOKEN\");\n if (!token) {\n return Response.json({ error: \"SLACK_BOT_TOKEN not set\" }, { status: 500 });\n }\n\n const hist = await slackApi(\n \"conversations.history\",\n { channel: SLACK_CHANNEL, limit: 200 },\n token,\n );\n if (!hist.ok) {\n return Response.json(\n { error: \"history fetch failed\", details: hist.error },\n { status: 500 },\n );\n }\n\n const allTargets: Array<\n {\n ts: string;\n text: string;\n time: string;\n isReply: boolean;\n username: string;\n }\n > = [];\n\n for (const parent of (hist.messages || [])) {\n if (isTargetBot(parent)) {\n allTargets.push({\n ts: parent.ts,\n text: (parent.text || \"\").slice(0, 100),\n time: new Date(parseFloat(parent.ts) 1000).toLocaleString(\"ko-KR\", {\n timeZone: \"Asia/Seoul\",\n }),\n isReply: false,\n username: parent.username || parent.bot_profile?.name || \"unknown\",\n });\n }\n\n if (parent.reply_count && parent.reply_count > 0) {\n const replies = await slackApi(\n \"conversations.replies\",\n { channel: SLACK_CHANNEL, ts: parent.ts, limit: 100 },\n token,\n );\n if (replies.ok) {\n for (const r of (replies.messages || []).slice(1)) {\n if (isTargetBot(r)) {\n allTargets.push({\n ts: r.ts,\n text: (r.text || \"\").slice(0, 100),\n time: new Date(parseFloat(r.ts) * 1000).toLocaleString(\"ko-KR\", {\n timeZone: \"Asia/Seoul\",\n }),\n isReply: true,\n username: r.username || r.bot_profile?.name || \"unknown\",\n });\n }\n }\n }\n await new Promise((res) => setTimeout(res, 100));\n }\n }\n\n if (allTargets.length === 0) {\n return Response.json({\n ok: true,\n channel: SLACK_CHANNEL,\n deleted: 0,\n message: \"삭제 대상 없음 — 정리 완료. 이 Val 자체를 삭제하세요.\",\n });\n }\n\n const results: any[] = [];\n for (const t of allTargets) {\n const del = await slackApi(\n \"chat.delete\",\n { channel: SLACK_CHANNEL, ts: t.ts },\n token,\n );\n results.push({\n time: t.time,\n isReply: t.isReply,\n username: t.username,\n preview: t.text,\n ok: del.ok,\n error: del.error || null,\n });\n await new Promise((res) => setTimeout(res, 200));\n }\n\n const okCount = results.filter((r) => r.ok).length;\n const failCount = results.length - okCount;\n\n return Response.json({\n ok: true,\n channel: SLACK_CHANNEL,\n targetBotIds: TARGET_BOT_IDS,\n found: allTargets.length,\n deleted: okCount,\n failed: failCount,\n results,\n note: failCount > 0\n ? \"일부 실패 — 1분 후 다시 Run.\"\n : \"모두 삭제 완료. found=0 나올 때까지 한 번 더 Run 후 이 Val 자체 삭제.\",\n });\n}",[157],"bf1b1046-7ad7-423f-8c68-292545bee643",["D",1781833939313],5,[29],"3d3656fb-7d28-42ed-91e2-c13aed41c6ac","dailyDriveSync_esm.ts","1a40308c-6a31-11f1-8870-1607ee4eb77e",75,["D",1781833269129],8,"/**\n * dailyDriveSync_esm.ts v5 (2026-06-18 패치) — 매일 14:00 KST\n \n v5 변경 (실제 파일 헤더 검증 후):\n * - [버그] 매출 파일 매칭 구분자 수정: \"일간 판매실적 현황-\"(하이픈) -> \"일간 판매실적 현황\"(언더스코어 포함 모두 매칭).\n * - [버그] 매출 xlsx는 1행이 제목, 2행이 헤더. parseXlsxFind 로 헤더행 자동 탐색.\n * - [갭] 광고비에 AI매출업_일별리포트(컬럼 날짜별/총비용) 합산 추가.\n * - ESM = 옥션 + G마켓 통합.\n \n Schedule: 0 5 * * * (UTC = 14:00 KST)\n /\nimport as XLSX from \"https://esm.sh/xlsx@0.18.5\";\nimport { blob } from \"https://esm.town/v/std/blob\";\n\nconst ESM_DRIVE_FOLDER_ID = Deno.env.get(\"ESM_DRIVE_FOLDER_ID\") ||\n \"1GLzcTPZvJTVHc-gwz-99lLQnm4fj93yP\";\nconst ESM_NOTION_DB_ID = \"5ed8388163b1468fa24b0e966291fd35\";\nconst BLOB_RATIO_KEY = \"esm_brand_ratio_latest\";\n\nfunction dateKST(o) {\n return new Date(Date.now() + o * 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v) {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction matchDate(v, targetDate) {\n if (v === null || v === undefined || v === \"\") return false;\n let s;\n if (v instanceof Date) {\n s = v.toLocaleDateString(\"sv-SE\", { timeZone: \"Asia/Seoul\" });\n } else {\n s = String(v).trim();\n }\n const compact = targetDate.replace(/-/g, \"\");\n return s === targetDate || s === compact || s.startsWith(targetDate) ||\n s.includes(targetDate) || s.includes(compact);\n}\n\nasync function getDriveAccessToken() {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\"),\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\"),\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\"),\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId, token) {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownload(fileId, token) {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n { headers: { \"Authorization\": `Bearer ${token}` } },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.arrayBuffer();\n}\nasync function findLatestDailyFolder(rootId, targetDate, token) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n ).sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\n\n// 헤더행 자동 탐색 파서 (매출 파일은 1행 제목/2행 헤더)\nfunction parseXlsxFind(buf, mustHaveCol) {\n const wb = XLSX.read(buf, { type: \"array\", cellDates: true });\n const sheet = wb.Sheets[wb.SheetNames[0]];\n const aoa = XLSX.utils.sheet_to_json(sheet, {\n header: 1,\n defval: \"\",\n raw: false,\n dateNF: \"yyyy-mm-dd\",\n });\n let hdr = -1;\n for (let i = 0; i < Math.min(aoa.length, 15); i++) {\n if ((aoa[i] || []).some((c) => String(c).trim() === mustHaveCol)) {\n hdr = i;\n break;\n }\n }\n if (hdr === -1) return [];\n const headers = (aoa[hdr] || []).map((c) => String(c).trim());\n const out = [];\n for (let i = hdr + 1; i < aoa.length; i++) {\n const row = {};\n (aoa[i] || []).forEach((v, j) => {\n if (headers[j]) row[headers[j]] = v;\n });\n out.push(row);\n }\n return out;\n}\n\nasync function notionUpsert(\n dbId,\n targetDate,\n label,\n properties,\n status,\n insight,\n) {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n const headers = {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n };\n const q = await fetch(`https://api.notion.com/v1/databases/${dbId}/query`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n });\n const existing = (await q.json()).results?.[0];\n const fullProps = {\n ...properties,\n \"날짜\": {\n title: [{ text: { content: `${targetDate} ${label} 일일 리포트` } }],\n },\n \"분석일\": { date: { start: targetDate } },\n \"상태\": { select: { name: status } },\n \"핵심 인사이트\": {\n rich_text: [{ text: { content: insight.slice(0, 2000) } }],\n },\n };\n if (existing) {\n const r = await fetch(`https://api.notion.com/v1/pages/${existing.id}`, {\n method: \"PATCH\",\n headers,\n body: JSON.stringify({ properties: fullProps }),\n });\n if (!r.ok) throw new Error(`Notion update ${r.status}`);\n return (await r.json()).url;\n } else {\n const r = await fetch(`https://api.notion.com/v1/pages`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n parent: { database_id: dbId },\n properties: fullProps,\n }),\n });\n if (!r.ok) {\n throw new Error(\n `Notion create ${r.status}: ${(await r.text()).slice(0, 300)}`,\n );\n }\n return (await r.json()).url;\n }\n}\n\nexport default async function () {\n const targetDate = dateKST(-1);\n const accessToken = await getDriveAccessToken();\n\n const folder = await findLatestDailyFolder(\n ESM_DRIVE_FOLDER_ID,\n targetDate,\n accessToken,\n );\n if (!folder) throw new Error(\"ESM 어제 폴더 없음\");\n const files = await driveList(folder.id, accessToken);\n\n // ESM = 옥션 + G마켓 통합\n const salesFiles = files.filter((f) =>\n f.name.startsWith(\"일간 판매실적 현황\")\n );\n const pcAdFiles = files.filter((f) =>\n f.name.startsWith(\"woojooware_report_daily_\")\n );\n const aiAdFiles = files.filter((f) =>\n f.name.startsWith(\"AI매출업_일별리포트\")\n );\n\n const dl = async (f) => ({\n name: f.name,\n buf: await driveDownload(f.id, accessToken),\n });\n const [salesB, pcB, aiB] = await Promise.all([\n Promise.all(salesFiles.map(dl)),\n Promise.all(pcAdFiles.map(dl)),\n Promise.all(aiAdFiles.map(dl)),\n ]);\n\n let totalRev = 0, totalOrders = 0, totalAdSpend = 0;\n\n for (const x of salesB) {\n for (const r of parseXlsxFind(x.buf, \"기준일\")) {\n if (!matchDate(r[\"기준일\"], targetDate)) continue;\n totalRev += num(r[\"판매금액\"]);\n totalOrders += num(r[\"판매건수\"]);\n }\n }\n for (const x of pcB) {\n for (const r of parseXlsxFind(x.buf, \"날짜\")) {\n if (!matchDate(r[\"날짜\"], targetDate)) continue;\n totalAdSpend += num(r[\"총비용\"]);\n }\n }\n for (const x of aiB) {\n for (const r of parseXlsxFind(x.buf, \"날짜별\")) {\n if (!matchDate(r[\"날짜별\"], targetDate)) continue;\n totalAdSpend += num(r[\"총비용\"]);\n }\n }\n\n let pnRatio = 1, tsRatio = 0, ratioSource = \"기본값(매핑 없음)\";\n try {\n const ratio = await blob.getJSON(BLOB_RATIO_KEY);\n if (ratio && typeof ratio.pnRatio === \"number\") {\n pnRatio = ratio.pnRatio;\n tsRatio = 1 - pnRatio;\n ratioSource = `30일 누적 (계산일 ${\n ratio.calculatedAt?.slice(0, 10) || \"?\"\n })`;\n }\n } catch (e) { /* 기본값 / }\n\n const pnRev = Math.round(totalRev pnRatio);\n const tsRev = totalRev - pnRev;\n const pnOrders = Math.round(totalOrders * pnRatio);\n const tsOrders = totalOrders - pnOrders;\n const roas = totalAdSpend > 0 ? Math.round(totalRev / totalAdSpend * 100) : 0;\n\n let status = \"정상\";\n if (totalRev === 0) status = \"데이터 부족\";\n else if (totalAdSpend > 0 && roas < 200) status = \"경고\";\n else if (totalAdSpend > 0 && roas < 500) status = \"검토 필요\";\n\n const insight = `통합 매출 ₩${totalRev.toLocaleString()} (풍년 ${\n (pnRatio * 100).toFixed(1)\n }%, 텅스 ${\n (tsRatio * 100).toFixed(1)\n }%, 비율 출처: ${ratioSource}), 주문 ${totalOrders}건. 광고비 ₩${totalAdSpend.toLocaleString()}${\n totalAdSpend > 0 ? `, ROAS ${roas}%` : \"\"\n }. (광고비 = 파워클릭 + AI매출업, 옥션+G마켓 통합)`;\n const props = {\n \"풍년보감 매출\": { number: pnRev },\n \"풍년보감 주문수\": { number: pnOrders },\n \"텅스페이스 매출\": { number: tsRev },\n \"텅스페이스 주문수\": { number: tsOrders },\n \"통합 매출\": { number: totalRev },\n \"통합 주문수\": { number: totalOrders },\n \"광고비\": { number: totalAdSpend },\n \"ROAS (%)\": { number: roas },\n };\n const url = await notionUpsert(\n ESM_NOTION_DB_ID,\n targetDate,\n \"ESM 매출\",\n props,\n status,\n insight,\n );\n\n console.log(\n `[dailyDriveSync_esm v5] ${targetDate} | rev ₩${totalRev.toLocaleString()} | ad ₩${totalAdSpend.toLocaleString()} | roas ${roas}% | sales:${salesB.length} pc:${pcB.length} ai:${aiB.length} | ${url}`,\n );\n return { ok: true, date: targetDate, totalRev, totalAdSpend, roas, url };\n}",[169],"5c7be5a5-3540-4c60-941f-1ba83f7eeaea","dailyDriveSyncSA.ts","3bf49eda-6a2b-11f1-b922-1607ee4eb77e",49,["D",1781690233842],6,"/**\n * dailyDriveSync_sa.ts — 매일 11:00 KST\n \n 네이버 폴더의 일별_raw_js,934268_.csv 1개만 처리 → \"네이버 SA 캠페인유형별 일일 리포트\" DB\n 단일 파일이라 빠름 (~10초 안에 끝남)\n \n Schedule: 0 2 * * * (UTC = 11:00 KST)\n /\n\nconst NAVER_DRIVE_FOLDER_ID = Deno.env.get(\"NAVER_DRIVE_FOLDER_ID\") ||\n \"1WOPgcs8YD80s7beD4Ev0IVT7VhTwXie6\";\nconst SA_DETAIL_NOTION_DB_ID = \"2829fbc585c74004b3ae33771f106d2a\";\nconst SLACK_CHANNEL = Deno.env.get(\"SLACK_CHANNEL_ID\") || \"C09B0J6QXN1\";\n\nfunction dateKST(o: number): string {\n return new Date(Date.now() + o 86400000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n}\nfunction num(v: any): number {\n if (v === null || v === undefined) return 0;\n return Number(String(v).replace(/[,원\\s%]/g, \"\")) || 0;\n}\nfunction matchDate(v: string, targetDate: string): boolean {\n if (!v) return false;\n const compact = targetDate.replace(/-/g, \"\");\n const dotted = targetDate.replace(/-/g, \".\") + \".\";\n const x = v.trim();\n return x === targetDate || x === compact || x === dotted ||\n x.includes(targetDate) || x.includes(compact);\n}\n\nasync function getDriveAccessToken(): Promise {\n const resp = await fetch(\"https://oauth2.googleapis.com/token\", {\n method: \"POST\",\n headers: { \"Content-Type\": \"application/x-www-form-urlencoded\" },\n body: new URLSearchParams({\n client_id: Deno.env.get(\"GOOGLE_CLIENT_ID\")!,\n client_secret: Deno.env.get(\"GOOGLE_CLIENT_SECRET\")!,\n refresh_token: Deno.env.get(\"GOOGLE_REFRESH_TOKEN\")!,\n grant_type: \"refresh_token\",\n }),\n });\n if (!resp.ok) throw new Error(`Token refresh ${resp.status}`);\n return (await resp.json()).access_token;\n}\nasync function driveList(parentId: string, token: string): Promise {\n const q = encodeURIComponent(`'${parentId}' in parents and trashed = false`);\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,mimeType,modifiedTime)&pageSize=100`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive list ${resp.status}`);\n return (await resp.json()).files || [];\n}\nasync function driveDownloadText(\n fileId: string,\n token: string,\n): Promise {\n const resp = await fetch(\n `https://www.googleapis.com/drive/v3/files/${fileId}?alt=media`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n if (!resp.ok) throw new Error(`Drive download ${resp.status}`);\n return resp.text();\n}\nasync function findLatestDailyFolder(\n rootId: string,\n targetDate: string,\n token: string,\n) {\n const compact = targetDate.replace(/-/g, \"\");\n const [y, m] = targetDate.split(\"-\");\n const monthName = `${y}년 ${parseInt(m, 10)}월`;\n const root = await driveList(rootId, token);\n const monthFolder = root.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" && f.name === monthName\n );\n if (!monthFolder) return null;\n const daily = await driveList(monthFolder.id, token);\n const match = daily.find((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\" &&\n f.name.endsWith(`~${compact}`)\n );\n if (match) return match;\n const all = daily.filter((f) =>\n f.mimeType === \"application/vnd.google-apps.folder\"\n )\n .sort((a, b) => (b.modifiedTime > a.modifiedTime ? 1 : -1));\n return all[0] || null;\n}\n\nfunction parseCSV(text: string): Record[] {\n const clean = text.replace(/^/, \"\").trim();\n const lines = clean.split(/\\r?\\n/).filter((l) => l.trim());\n if (lines.length < 2) return [];\n let headerLineIdx = 0;\n if (!lines[0].includes(\",\") || lines[0].split(\",\").length < 3) {\n headerLineIdx = 1;\n }\n const headers = lines[headerLineIdx].split(\",\").map((h) =>\n h.replace(/^\"|\"$/g, \"\").trim()\n );\n return lines.slice(headerLineIdx + 1).map((line) => {\n const cells = line.split(\",\").map((c) => c.replace(/^\"|\"$/g, \"\").trim());\n const row: Record = {};\n headers.forEach((h, i) => row[h] = cells[i] || \"\");\n return row;\n });\n}\n\nasync function notionUpsert(\n dbId: string,\n targetDate: string,\n label: string,\n properties: any,\n status: string,\n insight: string,\n): Promise {\n const token = Deno.env.get(\"NOTION_TOKEN\")!;\n const headers = {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": \"2022-06-28\",\n \"Content-Type\": \"application/json\",\n };\n const q = await fetch(`https://api.notion.com/v1/databases/${dbId}/query`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n filter: { property: \"분석일\", date: { equals: targetDate } },\n page_size: 1,\n }),\n });\n const existing = (await q.json()).results?.[0];\n const fullProps = {\n ...properties,\n \"날짜\": {\n title: [{ text: { content: `${targetDate} ${label} 일일 리포트` } }],\n },\n \"분석일\": { date: { start: targetDate } },\n \"상태\": { select: { name: status } },\n \"핵심 인사이트\": {\n rich_text: [{ text: { content: insight.slice(0, 2000) } }],\n },\n };\n if (existing) {\n const r = await fetch(`https://api.notion.com/v1/pages/${existing.id}`, {\n method: \"PATCH\",\n headers,\n body: JSON.stringify({ properties: fullProps }),\n });\n if (!r.ok) throw new Error(`Notion update ${r.status}`);\n return (await r.json()).url;\n } else {\n const r = await fetch(`https://api.notion.com/v1/pages`, {\n method: \"POST\",\n headers,\n body: JSON.stringify({\n parent: { database_id: dbId },\n properties: fullProps,\n }),\n });\n if (!r.ok) {\n throw new Error(\n `Notion create ${r.status}: ${(await r.text()).slice(0, 300)}`,\n );\n }\n return (await r.json()).url;\n }\n}\n\nasync function findTodayBotThreadTs(): Promise {\n const token = Deno.env.get(\"SLACK_BOT_TOKEN\")!;\n const resp = await fetch(\n `https://slack.com/api/conversations.history?channel=${SLACK_CHANNEL}&limit=20`,\n {\n headers: { \"Authorization\": `Bearer ${token}` },\n },\n );\n const data = await resp.json();\n if (!data.ok) return null;\n const today = dateKST(0);\n for (const m of data.messages || []) {\n const d = new Date(parseFloat(m.ts) * 1000).toLocaleDateString(\"sv-SE\", {\n timeZone: \"Asia/Seoul\",\n });\n if (d === today && m.bot_id) return m.ts;\n }\n return null;\n}\nasync function slackPost(text: string, threadTs: string | null): Promise {\n const token = Deno.env.get(\"SLACK_BOT_TOKEN\")!;\n const body: any = {\n channel: SLACK_CHANNEL,\n text,\n unfurl_links: false,\n unfurl_media: false,\n };\n if (threadTs) body.thread_ts = threadTs;\n const r = await fetch(\"https://slack.com/api/chat.postMessage\", {\n method: \"POST\",\n headers: {\n \"Authorization\": `Bearer ${token}`,\n \"Content-Type\": \"application/json; charset=utf-8\",\n },\n body: JSON.stringify(body),\n });\n if (!(await r.json()).ok) throw new Error(\"Slack post failed\");\n}\n\nexport default async function () {\n const targetDate = dateKST(-1);\n const accessToken = await getDriveAccessToken();\n\n let msg = \"\";\n try {\n const folder = await findLatestDailyFolder(\n NAVER_DRIVE_FOLDER_ID,\n targetDate,\n accessToken,\n );\n if (!folder) throw new Error(\"네이버 어제 폴더 없음\");\n const files = await driveList(folder.id, accessToken);\n const csv = files.find((f) =>\n f.name.startsWith(\"일별_raw_js,934268_\") && f.name.endsWith(\".csv\")\n );\n if (!csv) throw new Error(\"일별 raw csv 파일 없음\");\n const text = await driveDownloadText(csv.id, accessToken);\n const rows = parseCSV(text);\n\n const byType: Record = {\n \"브랜드검색\": { spend: 0, rev: 0 },\n \"쇼핑검색\": { spend: 0, rev: 0 },\n \"파워링크\": { spend: 0, rev: 0 },\n \"파워컨텐츠\": { spend: 0, rev: 0 },\n };\n for (const r of rows) {\n if (!matchDate(r[\"일별\"] || r[\"날짜\"] || \"\", targetDate)) continue;\n const tp = String(r[\"캠페인유형\"] || \"\").trim();\n let k = \"\";\n if (tp.includes(\"브랜드검색\")) k = \"브랜드검색\";\n else if (tp.includes(\"쇼핑검색\")) k = \"쇼핑검색\";\n else if (tp.includes(\"파워링크\")) k = \"파워링크\";\n else if (tp.includes(\"파워컨텐츠\")) k = \"파워컨텐츠\";\n if (!k) continue;\n byType[k].spend += num(r[\"총비용\"]);\n byType[k].rev += num(\n r[\"구매완료 전환매출액(원)\"] || r[\"구매완료 전환매출액\"],\n );\n }\n const totalSpend = Object.values(byType).reduce((s, v) => s + v.spend, 0);\n const totalRev = Object.values(byType).reduce((s, v) => s + v.rev, 0);\n const totalRoas = totalSpend > 0\n ? Math.round(totalRev / totalSpend * 100)\n : 0;\n let status = \"정상\";\n if (totalSpend === 0 && totalRev === 0) status = \"데이터 부족\";\n else if (totalSpend > 0 && totalRoas < 300) status = \"경고\";\n else if (totalSpend > 0 && totalRoas < 700) status = \"검토 필요\";\n const roas = (b: typeof byType[string]) =>\n b.spend > 0 ? Math.round(b.rev / b.spend * 100) : 0;\n const insight = `브랜드검색 ROAS ${roas(byType[\"브랜드검색\"])}%, 쇼핑검색 ${\n roas(byType[\"쇼핑검색\"])\n }%, 파워링크 ${roas(byType[\"파워링크\"])}%, 파워컨텐츠 ${\n roas(byType[\"파워컨텐츠\"])\n }%. 통합 ROAS ${totalRoas}%.`;\n const props = {\n \"브랜드검색 비용\": { number: byType[\"브랜드검색\"].spend },\n \"브랜드검색 매출\": { number: byType[\"브랜드검색\"].rev },\n \"브랜드검색 ROAS (%)\": { number: roas(byType[\"브랜드검색\"]) },\n \"쇼핑검색 비용\": { number: byType[\"쇼핑검색\"].spend },\n \"쇼핑검색 매출\": { number: byType[\"쇼핑검색\"].rev },\n \"쇼핑검색 ROAS (%)\": { number: roas(byType[\"쇼핑검색\"]) },\n \"파워링크 비용\": { number: byType[\"파워링크\"].spend },\n \"파워링크 매출\": { number: byType[\"파워링크\"].rev },\n \"파워링크 ROAS (%)\": { number: roas(byType[\"파워링크\"]) },\n \"파워컨텐츠 비용\": { number: byType[\"파워컨텐츠\"].spend },\n \"파워컨텐츠 매출\": { number: byType[\"파워컨텐츠\"].rev },\n \"파워컨텐츠 ROAS (%)\": { number: roas(byType[\"파워컨텐츠\"]) },\n \"통합 비용\": { number: totalSpend },\n \"통합 매출\": { number: totalRev },\n \"통합 ROAS (%)\": { number: totalRoas },\n };\n const url = await notionUpsert(\n SA_DETAIL_NOTION_DB_ID,\n targetDate,\n \"SA 캠페인유형별\",\n props,\n status,\n insight,\n );\n const emoji = status === \"정상\"\n ? \"🟢\"\n : status === \"검토 필요\"\n ? \"🟡\"\n : status === \"경고\"\n ? \"🔴\"\n : \"⬜\";\n msg =\n `${emoji} [SA 캠페인유형별] ${targetDate} · 비용 ₩${totalSpend.toLocaleString()} · 매출 ₩${totalRev.toLocaleString()} · ROAS ${totalRoas}% · <${url}|상세>`;\n } catch (e: any) {\n msg = `🔴 [SA 캠페인유형별] ${targetDate} 처리 실패 — ${e.message}`;\n }\n\n try {\n const threadTs = await findTodayBotThreadTs();\n if (threadTs) await slackPost(msg, threadTs);\n else await slackPost(`📊 ${dateKST(0)} Drive 동기화 — SA\\n${msg}`, null);\n } catch (e: any) {\n console.error(\"Slack notify failed:\", e.message);\n }\n\n console.log(`[dailyDriveSync_sa] ${targetDate}`);\n return { ok: true, date: targetDate, msg };\n}",[177],"d5222c1b-05a0-4826-b551-2f577db19e87","actionPlanSync.ts","7dd53b42-53f2-11f1-8c1a-ee650bb23af1",24,["D",1781679945496],"// Learn more: https://docs.val.town/vals/cron/\nexport default async function (interval: Interval) {\n console.log(interval);\n}",[185],"pulls",1,"branches",2,"forks","envVars",{"_200":202,"_203":61},"connections",{"_200":61,"_201":61},"count","unfilledCount",23,"emptyCount",{"_44":184,"_33":185,"_31":186,"_139":-5,"_42":43,"_41":187,"_141":25,"_39":230,"_13":173,"_34":144,"_36":189,"_24":27,"_146":-5,"_212":231,"_28":232},{"_44":176,"_33":177,"_31":178,"_139":-5,"_42":43,"_41":179,"_141":25,"_39":227,"_13":181,"_34":144,"_36":182,"_24":27,"_146":-5,"_212":228,"_28":229},{"_44":168,"_33":169,"_31":170,"_139":-5,"_42":43,"_41":171,"_141":25,"_39":224,"_13":173,"_34":144,"_36":174,"_24":27,"_146":-5,"_212":225,"_28":226},{"_44":164,"_33":29,"_31":32,"_139":-5,"_42":43,"_41":14,"_141":27,"_39":221,"_13":166,"_34":35,"_36":38,"_24":27,"_146":-5,"_212":222,"_28":223},{"_44":156,"_33":157,"_31":158,"_139":-5,"_42":43,"_41":159,"_141":25,"_39":218,"_13":161,"_34":35,"_36":162,"_24":27,"_146":-5,"_212":219,"_28":220},{"_44":148,"_33":149,"_31":150,"_139":-5,"_42":43,"_41":151,"_141":27,"_39":215,"_13":153,"_34":144,"_36":154,"_24":27,"_146":-5,"_212":216,"_28":217},{"_44":136,"_33":137,"_31":138,"_139":-5,"_42":43,"_41":140,"_141":25,"_39":211,"_13":143,"_34":144,"_36":145,"_24":27,"_146":-5,"_212":213,"_28":214},["D",1781744680319],"children",[],[137],["D",1781760297576],[],[149],["D",1781760512003],[],[157],["D",1781833939313],[],[29],["D",1781833269129],[],[169],["D",1781690233842],[],[177],["D",1781679945496],[],[185],["D",1778465580996],["D",1789578655349],{"_98":97,"_106":102},{"_98":97,"_99":100,"_101":102,"_18":55,"_103":104,"_105":27},{"_13":87,"_33":88,"_44":43,"_89":45,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":46,"_96":97},"ctx",{"_80":-5,"_126":271,"_272":273,"_274":275,"_276":27,"_277":27,"_278":27,"_279":25,"_280":-5,"_281":-7,"_282":-7,"_283":61,"_284":27,"_285":27,"_286":-5,"_287":27,"_288":27,"_289":27,"_290":-5},"ENV",{"_242":243,"_244":245,"_246":88,"_247":248,"_249":250,"_251":252,"_253":254,"_255":256,"_257":258,"_259":260,"_261":262,"_263":264,"_265":266,"_267":268,"_269":270},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","811aa22498c9b0b5a47e8d778de579cdb990f332","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",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

${sectionTitle}

핵심 인사이트<\\/h3>\\s*
([\\s\\S]*?)<\\/p>/;\n const m = html.match(re);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html: string): string {\n const re = /

어제 권장 액션 후속 추적<\\/h3>([\\s\\S]*?)(?=

${sectionTitle}

핵심 인사이트<\\/h3>\\s*([\\s\\S]*?)<\\/p>/;\n const m = html.match(re);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html: string): string {\n const re = /

어제 권장 액션 후속 추적<\\/h3>([\\s\\S]*?)(?=

핵심 인사이트<\\/h3>\\s*
([\\s\\S]*?)<\\/p>/;\n const m = html.match(re);\n return m ? stripTags(m[1]) : \"\";\n}\nfunction extractFollowup(html: string): string {\n const re = /

Diff · v81