POP-SORTE // SYSTEM ARCHITECTURE

--:--:-- WIB

2 WORKERS

2 GAS DEPLOYMENTS

15+ GOOGLE SHEETS

5 PLATFORMS

Ctrl+K SEARCH

⚡System Overview

Domain: popsorte.vip Hosting: GitHub Pages + Cloudflare Workers

Tech Stack

Layer	Technology	Purpose
Static Hosting	GitHub Pages	Serve HTML/CSS/JS — auto-deploy on push to repo
Domain	`popsorte.vip` (CNAME)	Custom domain pointing to GitHub Pages
Backend API	Cloudflare Workers ×2	Member API + Agent Bulk API (edge serverless)
Data Storage	Google Sheets ×15+	Primary database — CSV export for frontend, Sheets API for writes
Data Processing	Google Apps Script ×2	Member ticket handler + Agent bulk writer (doGet/doPost endpoints)
KV Store	Cloudflare KV	`AGENT_TOKENS` — 30-min session tokens for bulk registration
Queue	Cloudflare Queue	`popsorte-bulk-queue` — async batch ticket writes + DLQ
Notifications	Telegram Bot API	Kirim notifikasi setiap ada ticket baru masuk
Future Target	Supabase PostgreSQL	Planned migration dari Google Sheets (see Tab 9)

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐ │ POPSORTE.VIP │ │ (GitHub Pages — Static) │ ├──────────┬──────────┬──────────┬──────────┬──────────┬──────────────────────┤ │ index │ luz │ n1 │ zoe │ bilhete │ agente/ (index+bulk) │ │ .html │ .html │ .html │ .html │ .html │ .html │ ├──────────┴──────────┴──────────┴──────────┴──────────┴──────────────────────┤ │ pop-sorte.js + pop-sorte.css │ │ CSV fetch (Google Sheets public export) ← READ ONLY │ └────────────────────────────────┬────────────────────────────────────────────┘ │ POST /api/tickets/create ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ CLOUDFLARE WORKERS (Edge) │ ├────────────────────────────────┬────────────────────────────────────────────┤ │ worker-member.js │ worker-agent.js │ │ ───────────────── │ ───────────────── │ │ • /api/tickets/create │ • /api/agent/token/create │ │ • /api/auth/login │ • /api/agent/token/validate │ │ • /api/admin/entries │ • /api/agent/bulk/generate │ │ • /api/admin/results │ ───────────────── │ │ • /api/admin/recharge │ Bindings: │ │ • /api/admin/sorte │ • KV: AGENT_TOKENS │ │ • /api/admin/winners-summary │ • Queue: popsorte-bulk-queue │ │ • /api/admin/cache/clear │ • DLQ: popsorte-bulk-dlq │ ├────────────────────────────────┴────────────────────────────────────────────┤ │ Auth: Google Service Account JWT → OAuth2 Token (Sheets API scope) │ │ Telegram: HTML notification on each new ticket │ └────────────────┬───────────────────────────────────┬────────────────────────┘ │ Sheets API (append) │ Queue Consumer → POST ▼ ▼ ┌────────────────────────────────┐ ┌────────────────────────────────────────┐ │ GOOGLE APPS SCRIPT (Member) │ │ GOOGLE APPS SCRIPT (Agent Bulk) │ │ ─────────────────────────── │ │ ─────────────────────────── │ │ doGet: saveAndGetBilhete │ │ doPost: saveBulkBilhetes │ │ doGet: getWinnersSummary │ │ Target: Bulk Agent Sheet (SORTE tab) │ │ Target: Primary Sheet │ │ │ │ Failover: Backup Sheet │ │ │ │ (auto at 900K rows) │ │ │ └────────────────┬───────────────┘ └──────────────────┬─────────────────────┘ │ │ ▼ ▼ ┌─────────────────────────────────────────────────────────────────────────────┐ │ GOOGLE SHEETS (15+ Sheets) │ ├─────────────────────┬───────────────────────┬───────────────────────────────┤ │ Core Data │ Recharge │ Agent / Display │ │ • NEW POP SORTE │ • Recharge POPN1 │ • Agent Display │ │ • OLD POP SORTE │ • Recharge POPLUZ │ • Agent Ticket Tracking │ │ • Backup Sheet │ • Recharge POPZOE │ • Bulk Agent Registrations │ │ • WA Masking Sheet │ • Recharge POPSUR │ • Historical Data │ │ • DATA PARTICIPANT │ • Recharge POPBEA │ • VLD (per-platform tabs) │ │ • Auth Credentials │ • Recharge POPFOI │ • Public Display (OLD) │ └─────────────────────┴───────────────────────┴───────────────────────────────┘ │ IMPORTRANGE chains ▼ Website popsorte.vip (CSV public export → frontend render)

Platform Reference

Platform	System ID	Code (Worker)	Lottery	Prize Pool
POPN1	1123	800	Quina (5 from 1-80)	R$900
POPLUZ	1125	723	Quina (5 from 1-80)	R$900
POPZOE	1128	2382	Quina (5 from 1-80)	R$900
POPSUR	N/A	N/A	Quina (5 from 1-80)	R$900
POPBEA	N/A	N/A	Quina (5 from 1-80)	R$900
POPFOI	1135	2470	Quina (5 from 1-80)	R$900

POPSUR dan POPBEA sudah aktif di frontend publik, VLD, agent lookup, dan validasi admin. POPFOI adalah platform ke-6 (Sistem ID: 1135, Code: 2470). Nilai System ID / worker code untuk POPSUR dan POPBEA belum terdokumentasi secara eksplisit pada snapshot repo saat ini.

Draw schedule: Senin–Sabtu (no draw on Sundays). Cutoff: 20:00 BRT (normally), 17:00 BRT on Dec 24/31. No draw: Dec 25, Jan 1.

Key URLs

Service	URL
Website (Public)	`https://popsorte.vip`
Agent Page	`https://popsorte.vip/agente`
Admin Dashboard	`https://popsorte.vip/admin` (geo-blocked)
Worker — Member API	`https://popsorte-api.danilla-vargas1923.workers.dev`
Worker — Agent API	`https://popsorte-agent.danilla-vargas1923.workers.dev`
Worker — Staging	`https://popsorte-staging.danilla-vargas1923.workers.dev`

⚙️Cloudflare Workers

Runtime: V8 Isolates (Edge) 2 deployments

worker-member.js — Main Member API

Deployed: popsorte-api.danilla-vargas1923.workers.dev 724 lines export default { fetch }

Routes

Method	Path	Auth	Purpose
GET	`/` or `/health`	Public	Health check — returns version, timestamp
POST	`/api/tickets/submit` `/api/tickets/create`	Public	Submit lottery ticket → write to BACKUP_SHEET via Sheets API → send Telegram notification
POST	`/api/auth/login`	Public	Admin login — validate against `ADMIN_ACCOUNTS_JSON` secret + auth CSV fallback → return base64 token (12h TTL)
GET	`/api/admin/entries`	Skip*	Fetch all SORTE entries from PRIVATE_SHEET via Sheets API → CSV
GET	`/api/admin/results`	Skip*	Fetch results CSV from PUBLIC_SHEET
GET	`/api/admin/recharge`	Admin	Fetch + combine recharge CSVs from 5 platform sheets
GET	`/api/admin/sorte`	Admin	Fetch SORTE tab via Sheets API (JSON or CSV)
POST	`/api/admin/winners-summary`	Admin	Proxy to GAS `getWinnersSummary` action with retry (2×)
POST	`/api/admin/cache/clear`	Admin	Purge Cloudflare Cache API entries
OPTIONS	`*`	Public	CORS preflight handler

* /api/admin/entries dan /api/admin/results skip auth check dalam code (noted in source).

Key Technical Details

Google Sheets API Auth: Service Account JWT → OAuth2 token dengan scope spreadsheets. Token di-cache in-memory per isolate dengan 60s safety margin. Kalau expired (401), auto-clear cache dan retry 1×.
Write Target: Tiket ditulis ke BACKUP_SHEET_ID (bukan primary!) via Sheets API append method.
Caching: Pakai caches.default (Cloudflare Cache API) dengan TTL 30–60 detik.
Telegram Notification: HTML-formatted message dikirim via Bot API setiap ada ticket baru berhasil dibuat.
Retry Logic: retryWithBackoff — 2 attempts pada 401 token expiry; clear token cache lalu retry.

worker-agent.js — Agent Bulk Registration API

Deployed: popsorte-agent.danilla-vargas1923.workers.dev 531 lines export default { fetch, queue }

Config file: wrangler-agent.toml

Routes

Method	Path	Auth	Purpose
GET	`/` or `/health`	Public	Health check
POST	`/api/agent/token/create`	Public	Create 30-min session token di KV — validasi remaining tickets dari Sheet B
GET	`/api/agent/token/validate`	Token	Validate KV token + return live remaining count
POST	`/api/agent/bulk/generate`	Token	Generate batch tickets (5/25/50/100), enqueue untuk Sheet write via Queue

Queue Consumer

Receive ticket batch dari BULK_QUEUE
POST ke AGENT_APPS_SCRIPT_URL dengan { action: "saveBulkBilhetes", tickets: [...] }
Retry up to 3×, lalu masuk ke dead-letter queue (popsorte-bulk-dlq)

Bindings (dari wrangler-agent.toml)

Binding	Type	ID / Name
`AGENT_TOKENS`	KV Namespace	`717d1e5b950049b98b8ea1ccbb414641`
`BULK_QUEUE`	Queue Producer	`popsorte-bulk-queue`
(consumer)	Queue Consumer	max_batch=10, max_retries=3, DLQ=`popsorte-bulk-dlq`

Key Logic

Number Generation: Fisher-Yates shuffle, pick 5 from 1–80 (Quina format)
Bilhete ID Format: AGT{last3GameId}-{PlatformShort}{Concurso}-{3random}-{day}{monthInitial}
Remaining Tickets: Fetched dari Sheet B (1tt2XYr1...) via public gviz CSV, column D

⚠️ CONCURSO_REFERENCE — CRITICAL SYNC POINT

Nilai CONCURSO_REFERENCE = { number: 6955, date: '2026-02-18' } HARUS sama persis di pop-sorte.js DAN worker-agent.js. Kalau beda → concurso number salah → tiket masuk ke draw yang salah. Update kedua file bersamaan!

📜Google Apps Script

2 Deployments (Web App) Trigger: doGet / doPost HTTP

google-apps-script.gs — Member Ticket Handler

Target Sheet: 1mcOH3L0w_Gq_si3BhTOw7OcAHqerRlPoI2JEkFvWUvE (NEW POP SORTE, tab SORTE)

Backup Sheet: 1h5yMB5CliN9ITkwcG3vrzySb963Rq9hlzACffOlSZyk (auto-failover)

Actions (via doGet parameter)

Action	Trigger	Description
`saveAndGetBilhete`	doGet	Validate fields → `appendRow()` ke primary sheet → auto-failover ke backup sheet kalau primary penuh (900K rows) atau error. Returns bilhete data as JSON.
`getWinnersSummary`	doGet	Read dari BOTH primary + backup sheets, filter by drawDate/concurso, match numbers, hitung per-platform winners (R$900 pool masing-masing)
(legacy)	doPost	Direct append ke primary sheet (legacy, masih aktif)

Column Mapping (A–I)

Column	A	B	C	D	E	F	G	H	I
Field	Data/Hora	Platform	Game ID	WhatsApp	Números	Data Sorteio	Concurso	Bilhete #	Status

Failover Logic

Kalau primary sheet punya ≥ 900,000 rows atau appendRow() throws error → otomatis failover ke BACKUP_SHEET_ID. Ini silent — tidak ada alert. Cek kedua sheet kalau data "hilang".

google-apps-script-agent.gs — Agent Bulk Writer

Target Sheet: 1neXG1rE-3i9MuIrE_v9eF8_mqKF3hz7btc7Q7mvCej8 (Bulk Agent Registrations, tab SORTE)

Action	Trigger	Description
Health check	doGet	Returns status OK
`saveBulkBilhetes`	doPost	Receive ticket array dari Queue consumer → `appendRow()` setiap tiket ke bulk sheet. Same column mapping (A–I).

// Flow: Worker Queue Consumer → POST to this GAS
{
  "action": "saveBulkBilhetes",
  "tickets": [
    {
      "timestamp": "06/03/2026 14:30:00",
      "platform": "POPN1",
      "gameId": "3038178594",
      "whatsapp": "+5511999887766",
      "numbers": "05,12,33,47,68",
      "drawDate": "06/03/2026",
      "contest": "6971",
      "bilheteNumber": "AGT594-N16971-X2K-06M",
      "status": "PENDING"
    }
  ]
}

Deployment Notes

Deploy type: Web App (Execute as: Me, Access: Anyone)
URL pattern: https://script.google.com/macros/s/{DEPLOYMENT_ID}/exec
Setiap kali edit GAS → harus New Deployment (bukan Update) untuk get new URL
Update URL di Worker env: APPS_SCRIPT_URL (member) atau AGENT_APPS_SCRIPT_URL (agent)
Quotas: Google Apps Script punya daily execution limits (~6 min/exec, 90 min/day for free accounts). Bulk writes bisa hit limit kalau volume tinggi.

📊Google Sheets — Database Catalog

15+ Active Sheets IMPORTRANGE chains between sheets

📦 Core Data Sheets (5 sheets)

Sheet Name	Sheet ID	Access	Writer	Reader
NEW POP SORTE Database master semua tiket	`1mcOH3L0w_Gq_si3BhTOw7OcAHqerRlPoI2JEkFvWUvE`	Service Account	GAS (member), Manual (validasi status)	IMPORTRANGE → OLD POP SORTE
Backup Sheet Failover write target	`1h5yMB5CliN9ITkwcG3vrzySb963Rq9hlzACffOlSZyk`	Service Account	Worker (Sheets API append), GAS failover	GAS (getWinnersSummary)
OLD POP SORTE Display di website (WA masked)	`1OttNYHiecAuGG6IRX7lW6lkG5ciEcL8gp3g6lNrN9H8`	Public Export	IMPORTRANGE (auto)	Frontend CSV fetch (VLD ALL)
WA NO Masking Sistem hide nomor WhatsApp	`1Reh89WBwn34tEMw60wUe6QU-IcNWg23mqpDrmrMFiro`	IMPORTRANGE	Formula auto	IMPORTRANGE → OLD POP SORTE
DATA PARTICIPANT Admin export untuk validasi	`1PDu2_XMnAOIg2Xfb7l6z1qkGbiws00MuRrGO5OhmwX4`	Manual	Manual paste (dari admin CSV export)	Sheet4 formula (status matching)

IMPORTRANGE Chain — Core Data

NEW POP SORTE (data lengkap, WA full)
    ↓ IMPORTRANGE
WA NO Masking Sheet (formula mask: +55***456**89)
    ↓ IMPORTRANGE
OLD POP SORTE (WA masked, public-safe)
    ↓ CSV Public Export (gid=0)
Website popsorte.vip → VLD section

💰 Recharge & Admin Sheets (8+ sheets)

Sheet Name	Sheet ID	Access	CSV Schema
Recharge POPN1	`1c6gnCngs2wFOvVayd5XpM9D3LOlKUxtSjl7gfszXcMg`	Public Export	DATE \| TIME \| Member ID \| Order Number \| Change Amount \| Balance After
Recharge POPLUZ	`12GcjRtG23ro4aQ5N-Psh9G0lr0dZ2-qS6C129gGEoQo`	Public Export
Recharge POPZOE	`1teEHuYWSCK89Fd1nAWu8b9rosE7F87aetnVnxBdquAg`	Public Export
Recharge POPSUR	`184VgNiY76XEJ3Mb_8wQnDU34NWRDEYIQTXcGFowpvlM`	Public Export
Recharge POPBEA	`1mARjkx55oNcTI_vMvJxRqrwasw31g9Xz49vDUrJewJs`	Public Export
Recharge POPFOI	`1kwwyXcoUSxS1FF4h68Y2EbJbR_s68QIowiEszRZvjQM`	Public Export
N1 ADMIN	`1KcIhrL3EvgdkgHAD-5E2jSK2W1mEZlJ9-D5DBGdxXRU`	IMPORTRANGE	IMPORTRANGE dari Recharge sheets. Monitoring data per-platform.
LUZ ADMIN	`1H68xaO7xjR-o7ECklQT1oZkT7lkMj5FNydq3nVPimgM`	IMPORTRANGE
ZOE ADMIN	`1teEHuYWSCK89Fd1nAWu8b9rosE7F87aetnVnxBdquAg`	IMPORTRANGE

POPSUR, POPBEA dan POPFOI saat ini tervalidasi langsung dari recharge sheet dedicated. Tidak ada separate admin mirror sheet yang terdokumentasi di repo snapshot ini.

🏆 Agent & Free Ticket Sheets (5 sheets)

Sheet Name	Sheet ID	Purpose
Agent Display	`1iY9CeUKilVSftMYRjeEZ1yvDhw3DdqdD1UfBHCyJF6I`	Public display card data per platform. Read by `agente/index.html` via gviz CSV.
Agent Ticket Tracking READ ONLY	`1tt2XYr1_PY4MYKRFYEhH3yIZIVvqy_uRI_tjU0uAVpQ`	Col A: Game ID, Col B: total tickets, Col C: registered (formula), Col D: remaining (B−C). Worker reads col D.
Bulk Agent Registrations	`1neXG1rE-3i9MuIrE_v9eF8_mqKF3hz7btc7Q7mvCej8`	Tab SORTE — setiap bulk generate masuk sini via GAS agent. Platform GIDs: POPN1=0, POPLUZ=1439646488, POPZOE=1494410655, POPSUR=2068012496, POPBEA=391959141, POPFOI=89902206
Historical Data	`1xoThWSyoqskJGx1z_f8R3MGQC6CXCg0Ebvsqsho8nKM`	Archive agent referrals. Tabs: DATA INDICATE, ALL ID REPORTED per platform.
FREE BILHETE	`1TmgN6eMw-SxATY3wnvVrLp14SbiKfCDdMX-HYy8u2A0`	Data event join group (bot @Popsortebot). Diproses manual lalu dipaste ke Recharge Sheet.

🔍 VLD / Display & Other Sheets (5+ sheets)

Sheet Name	Sheet ID	Usage
VLD per-platform	`1b_VAYANY_XUsO0...`	Pages: luz.html, n1.html, zoe.html, sur.html, bea.html. Sources: `sheet=LUZ`, `sheet=N1`, `gid=1985526052`, `gid=13116568`, `gid=1337969693`.
VLD ALL	`1OttNYHiecAuGG6...`	Combined export (gid=0) — used by index.html VLD with platform selector
Auth Credentials	`1PK0qI9PRWaleD6...`	Admin login CSV (gid=1360466037) — fetched by auth.js in browser + Worker fallback
Old Dashboard Data	`14f_ipSqAq8KCP7...`	Entries (gid=0) + Results (gid=1178367669) — used by DATA ADMIN DASHBOARD LAMA
Historical Analytics	`1a1M0fAAao7s28V...`	Monthly sheets (Dec25-Mar26) — used by script.js historical data section

🔄Data Flow Diagrams

3 Primary Flows End-to-end data pipeline

Flow A — Member Ticket Submission

USER (Browser — popsorte.vip) │ ├── 1. Pilih platform (POPN1/POPLUZ/POPZOE/POPSUR/POPBEA/POPFOI) ├── 2. Input Game ID (10 digits) + WhatsApp ├── 3. Pilih 5 angka (1-80) atau klik "Surpresinha" (random) │ │ pop-sorte.js: │ ├── Validate inputs │ ├── Calculate drawDate + concurso dari CONCURSO_REFERENCE │ ├── Generate bilhete number │ └── retryWithBackoff(POST /api/tickets/create, 3 attempts) │ ▼ worker-member.js │ ├── Parse request body ├── Get OAuth2 token (Service Account JWT → Google OAuth2) ├── Sheets API: spreadsheets.values.append → BACKUP_SHEET_ID │ └── (Row: timestamp, platform, gameId, whatsapp, numbers, drawDate, concurso, bilhete#, "PENDING") ├── Send Telegram notification (HTML format via TG_BOT_TOKEN) └── Return { success: true, bilheteId: "..." } │ ▼ Browser redirect → bilhete.html?platform=...&bilhete=...&numbers=... │ ├── Display ticket visual ├── html2canvas → auto-download PNG └── Live countdown to next draw

Flow B — Agent Bulk Registration

AGENT (Browser — popsorte.vip/agente/) │ ├── 1. Input Game ID + select platform ├── 2. agente/index.html fetches 2 sheets: │ Sheet A (Agent Display) → card info │ Sheet B (Ticket Tracking) → remaining count (col D) │ ├── 3. If remaining ≥ 5 → show "REGISTRO EM MASSA" button │ ▼ POST /api/agent/token/create → worker-agent.js │ ├── Validate remaining from Sheet B (live check) ├── Generate UUID token → store in KV (TTL: 30 min) │ KV value: { gameId, platform, remaining, createdAt } └── Return { token: "uuid-..." } │ ▼ agente/bulk.html (token-gated page) │ ├── Agent inputs WhatsApp, selects batch size (5/25/50/100) │ ▼ POST /api/agent/bulk/generate → worker-agent.js │ ├── Validate KV token (exists + not expired) ├── Generate N tickets: │ ├── Fisher-Yates shuffle → 5 unique numbers (1-80) │ ├── Calculate concurso + drawDate │ └── Build bilhete ID: AGT{gameId}-{platform}{concurso}-{random}-{date} ├── Enqueue to BULK_QUEUE (Cloudflare Queue) ├── Update KV remaining └── Return ticket data immediately (no wait for Sheet write) │ ▼ Queue Consumer (async, same worker) │ ├── POST to AGENT_APPS_SCRIPT_URL │ body: { action: "saveBulkBilhetes", tickets: [...] } │ ├── On success: message.ack() ├── On failure: message.retry() (up to 3×) └── After 3 failures: → popsorte-bulk-dlq (Dead Letter Queue)

Flow C — Ticket Validation Pipeline

ADMIN (popsorte.vip/admin) │ ├── 1. Login → fetch entries via Worker API ├── 2. Export ENTRIES as CSV │ ▼ DATA PARTICIPANT (Google Sheet — manual paste) │ ├── 3. Paste exported CSV data ├── 4. Sheet4 tab → formula auto-matches: │ ├── Entry Game ID ↔ Recharge Member ID │ ├── Entry platform ↔ Recharge platform sheet │ ├── Recharge date within 2-day eligible window │ ├── Cutoff: 20:00 BRT (16:00 on Dec 24/31) │ └── 1 recharge = 1 valid ticket (binding) │ ├── 5. Output kolom B: "VALID" atau "TIDAK VALID" │ ▼ Copy kolom B dari Sheet4 (dari PALING ATAS) │ ▼ NEW POP SORTE — kolom I (Ctrl+Shift+V = paste values only!) │ ├── Status tiket ter-update: VALID / INVALID │ ▼ IMPORTRANGE chain: NEW → WA Masking → OLD POP SORTE │ ▼ Website popsorte.vip → VLD section → auto-refresh setiap 30 detik │ └── Cards tampil: ✅ VÁLIDO / ❌ INVÁLIDO / ⏳ PENDENTE

Validation Rules (Rule B — 2-Day Window)

Tiket dianggap VALID jika: Platform + Game ID cocok dengan recharge data, recharge terjadi dalam 2-day eligible window sebelum draw, sebelum cutoff time, dan 1 recharge hanya bisa bind ke 1 tiket.

Flow D — Winner Calculation

Quina Draw Results (from loterias.caixa.gov.br) │ ├── Admin inputs 5 winning numbers + concurso │ ▼ GAS getWinnersSummary (via Worker proxy) │ ├── Read ALL tickets from primary + backup sheets ├── Filter: drawDate matches + concurso matches ├── Match each ticket's 5 numbers against winning numbers │ │ Prize Tiers (cascading — checks highest first): │ ├── 5 acertos → GRAND PRIZE │ ├── 4 acertos → 2nd PRIZE │ ├── 3 acertos → 3rd PRIZE │ ├── 2 acertos → CONSOLATION │ └── 1 acerto → No prize │ ├── Group winners by platform + contest ├── Split R$900 pool per platform among winners at highest tier └── Return summary JSON → Admin dashboard renders

🖥️Frontend Architecture

Vanilla JS — No Framework 10 HTML pages

pop-sorte.js — Main JavaScript (~2,300 lines)

Lines	Block	Purpose
`1–45`	Config + Retry	`API_BASE_URL`, `retryWithBackoff()` — exponential backoff, 3 attempts (1s/2s/3s delays)
`48–330`	Concurso/Date System	`CONCURSO_REFERENCE = { number: 6955, date: '2026-02-18' }`. Brazil timezone (BRT UTC-3) helpers, draw schedule calculation, concurso number computation, holiday handling.
`375–880`	Results Display	`fetchAndPopulateResults()` — fetch results CSV from public sheet, render draw results + winners announcements.
`882–1050`	CSV Parsing + Latest 5	CSV parsing helpers, `initLatestFiveWidget()` — shows 5 most recent tickets.
`1053–1350`	Form/UI Logic	Game ID validation (10 digits), WhatsApp validation/masking, platform radio select, number grid (1–80 balls), "Surpresinha" random pick, submit button state.
`1350–1650`	Submission Flow	`confirmEntry()` — validates everything, POSTs to Worker, handles retries, redirects to bilhete.html with URL params.
`1650–1750`	Countdown Timer	`initCountdown()` — live countdown ke draw cutoff (20:00 BRT, 17:00 on Dec 24/31).
`1750–2050`	VLD Ticket Consultation (IIFE)	Self-contained: fetch per-platform Google Sheet CSVs, render card grid, search (Game ID / Ticket ID), filter (All/Valid/Invalid/Pending), pagination, auto-refresh every 30s.
`2050–2160`	Mobile Nav (IIFE)	Bottom navigation — switches sections: home / rules / search / help / popluz. References `.vld-section` class.
`2160–2230`	POPLUZ Slider (IIFE)	Image slider with dots, auto-advance interval.
`2230–2300`	Anti-Debugging (IIFE)	Blocks F12, right-click, Ctrl+Shift+I. Secret override: hold Q+2 for 2 seconds

pop-sorte.css — Styles (~5,180 lines)

Single monolithic CSS file — no preprocessor, no modules
CSS custom properties di :root — purple/cyan/pink/gold theme
Fonts: Inter + Space Grotesk (Google Fonts)
Sections: Hero, selection grid (1–80 balls with 10 color classes), platform cards, mobile bottom nav, VLD compact cards (.vc-* classes), POPLUZ template sections, toast notifications, popups, bilhete display
Responsive breakpoints: 768px (tablet), 480px (mobile)
Old VLD CSS (lines ~2785–3880) masih ada tapi unused — bisa di-cleanup untuk reduce file size

HTML Pages (13 files)

File	Lines	Purpose	Key Features
index.html	~2,548	Main lottery page (all platforms)	Platform selector, number picker, VLD with 5 platform pills, POPLUZ design section
luz.html	~1,576	POPLUZ-specific	"Golden Gala" theme, VLD filtered to POPLUZ only
n1.html	~1,575	POPN1-specific	Same Golden Gala theme, VLD filtered to POPN1
zoe.html	~1,577	POPZOE-specific	Same theme, VLD filtered to POPZOE
sur.html	~1,577	POPSUR-specific	Same theme, VLD filtered to POPSUR
bea.html	~1,577	POPBEA-specific	Same theme, VLD filtered to POPBEA
foi.html	~1,577	POPFOI-specific	Same theme, VLD filtered to POPFOI
bilhete.html	~948	Ticket confirmation	Read URL params, display visual, html2canvas → auto-download PNG, countdown
404.html	~221	404 / geo-block landing	Animated purple background, "page not found"
agente/index.html	~1,353	Agent lookup	Input Game ID + platform, fetch 2 sheets, show bulk button if ≥5 remaining
agente/bulk.html	~992	Agent bulk registration	Token-gated, WhatsApp input, batch selector (5/25/50/100), generate + auto-download PNG
admin/index.html	~1,103	Admin dashboard	Sidebar nav, Chart.js, geo-blocked, entries/results/winners/historical sections
admin/login.html	~63	Admin login	Fetch credentials from auth sheet CSV, set sessionStorage

CSV URLs Used by Frontend

Page	CSV Source	Purpose
index.html (VLD)	`1OttNYHiecAuGG6.../export?format=csv&gid=0`	All platforms combined — VLD with platform selector
luz.html (VLD)	`1b_VAYANY_.../gviz/tq?tqx=out:csv&sheet=LUZ`	POPLUZ entries only
n1.html (VLD)	`1b_VAYANY_.../gviz/tq?tqx=out:csv&sheet=N1`	POPN1 entries only
zoe.html (VLD)	`1b_VAYANY_.../gviz/tq?tqx=out:csv&gid=1985526052`	POPZOE entries only
sur.html (VLD)	`1b_VAYANY_.../gviz/tq?tqx=out:csv&gid=13116568`	POPSUR entries only
bea.html (VLD)	`1b_VAYANY_.../gviz/tq?tqx=out:csv&gid=1337969693`	POPBEA entries only
foi.html (VLD)	`1b_VAYANY_.../gviz/tq?tqx=out:csv&gid=1814105408`	POPFOI entries only
Results section	Via Worker API or PUBLIC_SHEET (gid=300277644)	Draw results display
agente/index.html	Sheet A (`1iY9CeUKilVSftM...`) + Sheet B (`1tt2XYr1...`)	Agent daily info + remaining tickets

🔒Authentication & Security

3 Auth Mechanisms Geo-blocking active

1 — Worker API Token (Admin Endpoints)

Property	Value
Login endpoint	`POST /api/auth/login` (worker-member.js)
Credential sources	1. `ADMIN_ACCOUNTS_JSON` secret (primary) 2. Auth Sheet CSV fallback (`AUTH_SHEET_ID`)
Token format	`btoa(account + ":" + timestamp)` — base64 encoded
TTL	12 hours
Transport	`Authorization: Bearer <token>` header
Validation logic	Decode token → extract account name → check exists → check age < 12h

// Token generation (worker-member.js)
const token = btoa(account + ":" + Date.now());
// → returns e.g. "YWRtaW46MTcwOTcyMDAyMDAwMA=="

// Token validation
const decoded = atob(token);          // "admin:1709720020000"
const [acct, ts] = decoded.split(":"); 
const age = Date.now() - parseInt(ts);
if (age > 12 * 60 * 60 * 1000) → 401 Unauthorized

2 — Admin Dashboard Session (Browser-side)

Property	Value
Login page	`admin/login.html`
Credential source	Auth Sheet CSV fetched directly in browser (`1PK0qI9PRWaleD6...` gid=1360466037)
Session storage	`sessionStorage` key: `ps_admin_session`
TTL	12 hours (checked by `ensureAuthenticated()`)
Security note	CLIENT-SIDE ONLY — no server-side session. Credentials are in a public Google Sheet.

3 — Agent KV Token (Bulk Registration)

Property	Value
Create endpoint	`POST /api/agent/token/create`
Token format	UUID v4 — stored in Cloudflare KV namespace `AGENT_TOKENS`
TTL	30 minutes
KV value	`{ gameId, platform, remaining, createdAt }`
Transport	URL query param or request body
On expiry	KV returns null → 401 → redirect to `agente/index.html`

Geo-Blocking

File: geo-block.js (loaded by admin dashboard)

API: ipapi.co — lookup visitor IP → get country code

Blocked countries:

const BLOCKED = ['BR','AR','UY','PY','BO','PE','CO','VE','GY','SR','EC','CL','GF'];
// South American countries → redirect to /404.html

Purpose: Admin dashboard is geo-blocked dari South America. Hanya bisa diakses dari luar (contoh: Asia).

Anti-Debugging (Frontend)

Defined in pop-sorte.js (IIFE, lines ~2230–2300):

Blocked: F12, Ctrl+Shift+I, Ctrl+Shift+J, Ctrl+U, right-click context menu
Secret Override: Hold Q + 2 simultaneously for 2 seconds → enables DevTools

🔧Environment Variables & Configuration

wrangler secret put <KEY> wrangler.toml for non-secrets

worker-member.js — Environment Variables

Variable	Type	Value / Purpose
`PRIVATE_SHEET_ID`	env	`1mcOH3L...` — Primary entries sheet (NEW POP SORTE)
`BACKUP_SHEET_ID`	env	`1h5yMB5...` — Backup sheet (WRITE TARGET for tickets)
`PUBLIC_SHEET_ID`	env	`1yy-G41...` — Public data (entries CSV gid=0, results CSV gid=300277644)
`AUTH_SHEET_ID`	env	`1PK0qI9...` — Admin credentials CSV (gid=1360466037)
`RECHARGE_SHEET_ID`	env	`1c6gnCn...` — POPN1 recharge
`RECHARGE_POPLUZ_SHEET_ID`	env	`12GcjRt...` — POPLUZ recharge
`RECHARGE_POPZOE_SHEET_ID`	env	`1teEHuY...` — POPZOE recharge
`RECHARGE_POPSUR_SHEET_ID`	env	`184VgNi...` — POPSUR recharge
`RECHARGE_POPBEA_SHEET_ID`	env	`1mARjkx...` — POPBEA recharge
`RECHARGE_POPFOI_SHEET_ID`	env	`1kwwyXc...` — POPFOI recharge
`APPS_SCRIPT_URL`	env	GAS Web App URL for member handler
`TG_CHAT_ID`	env/secret	Telegram chat ID for notifications

Secrets (wrangler secret put)

Secret	Format	Purpose
`GSERVICE_ACCOUNT_JSON`	JSON string	Google Service Account credentials — used for JWT → OAuth2 token. Must have Sheets API scope. If missing → error `"Service account JSON missing"`
`ADMIN_ACCOUNTS_JSON`	JSON string `{"admin":"pass"}`	Admin login credentials. Parsed as object: key = account name, value = password.
`TG_BOT_TOKEN`	String	Telegram Bot API token for sending notifications

worker-agent.js — Environment Variables

Config defined in wrangler-agent.toml:

Variable / Binding	Type	Value / Purpose
`AGENT_TOKENS`	KV Namespace	ID: `717d1e5b950049b98b8ea1ccbb414641`
`BULK_QUEUE`	Queue Producer	Name: `popsorte-bulk-queue`
`AGENT_APPS_SCRIPT_URL`	Secret	GAS Web App URL for agent bulk writer. Set via `wrangler secret put`

# wrangler-agent.toml (key sections)
name = "popsorte-agent"
main = "worker-agent.js"
compatibility_date = "2024-01-01"

[[kv_namespaces]]
binding = "AGENT_TOKENS"
id = "717d1e5b950049b98b8ea1ccbb414641"

[[queues.producers]]
binding = "BULK_QUEUE"
queue = "popsorte-bulk-queue"

[[queues.consumers]]
queue = "popsorte-bulk-queue"
max_batch_size = 10
max_retries = 3
dead_letter_queue = "popsorte-bulk-dlq"

⚠️ CONCURSO_REFERENCE — Must Stay In Sync

INI HARUS SELALU SAMA DI KEDUA FILE:

Kalau CONCURSO_REFERENCE berbeda antara pop-sorte.js dan worker-agent.js, tiket member dan tiket agent akan punya concurso number yang berbeda → masuk draw yang salah.

// MUST BE IDENTICAL in both files:

// pop-sorte.js (frontend)
const CONCURSO_REFERENCE = { number: 6955, date: '2026-02-18' };

// worker-agent.js (backend)
const CONCURSO_REFERENCE = { number: 6955, date: '2026-02-18' };

// Cara hitung concurso:
// 1. Hitung jumlah draw days antara reference date dan target date
// 2. Skip Sundays, Dec 25, Jan 1
// 3. concurso = reference.number + drawDayCount

Deployment Commands

# Deploy member worker
wrangler deploy worker-member.js

# Deploy agent worker (uses wrangler-agent.toml)
wrangler deploy --config wrangler-agent.toml

# Set/update secrets
wrangler secret put GSERVICE_ACCOUNT_JSON
wrangler secret put ADMIN_ACCOUNTS_JSON
wrangler secret put TG_BOT_TOKEN
wrangler secret put AGENT_APPS_SCRIPT_URL --config wrangler-agent.toml

# Check KV namespace
wrangler kv:key list --namespace-id=717d1e5b950049b98b8ea1ccbb414641

# Check Queue
wrangler queues list

# Static site (frontend) — just git push to GitHub
git add . && git commit -m "update" && git push origin main
# GitHub Pages auto-deploys on push

🐛Troubleshooting & Error Reference

❌ Google Sheets API 401 — Token Expired

Where: worker-member.js — any Sheets API call

Cause: Cached OAuth2 token expired (usually after ~1 hour)

Auto-remedy: Worker auto-clears token cache and retries once with fresh JWT

If persists:

Check GSERVICE_ACCOUNT_JSON secret — is it valid JSON?
Verify service account has Sheet access (share sheets with service account email)
Check Google Cloud project — API enabled? Billing OK?

// Error log pattern:
"Sheets API error: 401" → auto-retry
"Sheets API error: 401" (2nd attempt) → throw error

❌ "Service account JSON missing"

Where: worker-member.js — startup / first Sheets API call

Cause: GSERVICE_ACCOUNT_JSON secret not set or empty

Fix:

wrangler secret put GSERVICE_ACCOUNT_JSON
# Paste the full JSON content of the service account key file
# Must include: client_email, private_key, etc.

❌ Sheet Full — Auto-Failover ke Backup

Where: google-apps-script.gs — saveAndGetBilhete

Trigger: Primary sheet has ≥ 900,000 rows OR appendRow() throws error

Behavior: Silent failover — writes to BACKUP_SHEET_ID instead. No alert, no log visible to user.

How to detect:

Check primary sheet row count — kalau mendekati 900K, data baru akan masuk ke backup
getWinnersSummary reads BOTH sheets — jadi winner calc masih benar
Tapi validasi pipeline (Flow C) mungkin miss data di backup — perlu manual check

❌ GAS Timeout / 500 Error

Where: worker-member.js — /api/admin/winners-summary route

Cause: GAS execution timeout (max 6 min per execution) or server error

Auto-remedy: Worker retries 2× with 1.5s delay between attempts

If persists:

Check GAS execution logs: Google Apps Script Dashboard
Hitung volume data — kalau rows sangat banyak, getWinnersSummary bisa timeout
Optimize: filter by concurso range dulu sebelum full scan

❌ Agent Token Expired — 401

Where: worker-agent.js — /api/agent/bulk/generate or /api/agent/token/validate

Cause: KV token has expired (TTL: 30 min) or token UUID not found in KV

Fix: Agent harus kembali ke /agente/, re-enter Game ID, dan create token baru

❌ Queue Consumer Failure → DLQ

Where: worker-agent.js — queue handler

Cause: GAS agent endpoint down, network error, or GAS quota exceeded

Behavior: message.retry() up to 3× → after 3 failures → message goes to popsorte-bulk-dlq

How to check:

# Check DLQ for failed messages
wrangler queues list
# Look for popsorte-bulk-dlq message count

# Re-process DLQ messages (manual)
# You'll need to read from DLQ and re-enqueue to main queue

❌ Network Error on Ticket Submit — Frontend

Where: pop-sorte.js — confirmEntry()

Behavior: retryWithBackoff() — 3 attempts with 1s/2s/3s delays

If all 3 fail: Error toast shown to user

Debug:

Check Worker health: GET /health
Check Cloudflare status: cloudflarestatus.com
Check browser console (needs Q+2 secret to open DevTools)

❌ Invalid Concurso Date (Sunday/Holiday)

Where: pop-sorte.js + worker-agent.js — concurso date calculation

Behavior: Auto-skips to next valid draw day (searches up to 14 days ahead)

No-draw days: Sundays, December 25, January 1

Special cutoffs: December 24 and 31 → cutoff at 17:00 BRT (instead of 20:00)

If concurso seems wrong: Check CONCURSO_REFERENCE value in both files — must be identical.

❌ IMPORTRANGE Error (#REF!) di Sheets

Where: Any sheet that uses IMPORTRANGE (OLD POP SORTE, Admin Sheets, etc.)

Causes:

Permission not granted — klik cell error, klik "Allow access"
Source sheet renamed or deleted
Google Sheets service disruption

Fix:

Klik cell error → lihat formula: =IMPORTRANGE("URL", "range")
Buka URL source sheet di tab baru
Kembali ke cell error → "Allow access" kalau muncul
Hard refresh: F5 atau Ctrl+Shift+R

❌ Worker Deployment Failed

Common causes:

Syntax error in JS — check with node --check worker-member.js
Wrangler not logged in — wrangler login
Wrong account — wrangler whoami
KV namespace ID wrong — check wrangler kv:namespace list

# Debug deployment
wrangler deploy --dry-run worker-member.js
wrangler tail  # live logs for deployed worker

🚀Migration Plan — Supabase PostgreSQL

Status: PLANNED (not implemented) Target: Replace Google Sheets

Status: Planning Phase

Migration ke Supabase PostgreSQL sudah di-design tapi belum diimplementasi. Dokumentasi lengkap ada di GUIDELINES/PLAN MIGRASI.md (~2,345 lines) dan GUIDELINES/POPSORTE_DATABASE_SCHEMA.md (~943 lines).

Planned Database Schema (7 Tables)

Table	Purpose	Key Columns
`platforms`	Platform master data	id, name (POPN1/POPLUZ/POPZOE/POPSUR/POPBEA/POPFOI), system_id, code, prize_pool
`tickets`	All lottery tickets	id, platform_id, game_id, whatsapp, numbers[], draw_date, concurso, bilhete_number, status, created_at
`recharges`	Recharge/deposit records	id, platform_id, member_id, order_number, amount, balance_after, record_time
`draws`	Draw results	id, concurso, draw_date, winning_numbers[], created_at
`winners`	Calculated winners	id, ticket_id, draw_id, matching_numbers, tier, prize_amount
`concurso_calendar`	Draw schedule	concurso_number, draw_date, is_active
`holidays`	Configurable no-draw dates	date, description, special_cutoff
`validation_logs`	Audit trail	id, ticket_id, recharge_id, result, validated_at

Validation Rules (Rule B — 2-Day Eligible Window)

Tiket dianggap VALID kalau memenuhi SEMUA kondisi:

Platform + Game ID match — Game ID di tiket harus ada di recharge data platform yang sama
Recharge timing — Recharge harus terjadi dalam 2-day eligible window sebelum draw date
Before cutoff — Recharge timestamp harus sebelum cutoff time (20:00 BRT, atau 17:00 on Dec 24/31)
1:1 binding — 1 recharge record hanya bisa mem-validasi 1 tiket (no double-dipping)

Prize Distribution

Acertos	Tier	Prize
5 angka benar	GRAND PRIZE	Full pool R$900 (split if multiple winners)
4 angka benar	2nd Prize	Pool R$900 (only if no 5-acerto winner)
3 angka benar	3rd Prize	Pool R$900 (cascading down)
2 angka benar	Consolation	Pool R$900 (cascading down)
1 atau 0	No Prize	—

Cascading logic: Prize pool R$900 per platform. Awarded to winners at the highest matching tier. If 2 orang sama-sama 5 acertos → split R$900/2 = R$450 each. Kalau tidak ada 5 acertos → cek 4 acertos, dst.

Migration Goals & Improvements

Replace Google Sheets bottleneck: Sheets punya row limits (900K), slow IMPORTRANGE, rate limits on API calls
Real-time validation: Instead of manual Sheet4 formula → automated server-side validation on ticket submit
Configurable holidays: holidays table replaces hardcoded Dec 25 / Jan 1 checks
Proper auth: Replace client-side sessionStorage with Supabase Auth (JWT + RLS)
Audit trail: validation_logs table untuk debugging validation issues
Performance: PostgreSQL queries vs CSV parsing — orders of magnitude faster for admin dashboard

📋 Quick Reference — Key Points

1. CONCURSO_REFERENCE: Must be identical in pop-sorte.js AND worker-agent.js — currently { number: 6955, date: '2026-02-18' }

2. TICKET WRITES: Worker writes to BACKUP_SHEET (not primary). GAS writes to primary. Both read both sheets for winners.

3. AUTH: Worker API uses base64 token (12h). Dashboard uses sessionStorage (12h). Agent uses KV UUID (30min).

4. QUEUE: Bulk tickets go through Cloudflare Queue → GAS agent. DLQ (popsorte-bulk-dlq) catches failures after 3 retries.

5. SECRETS: GSERVICE_ACCOUNT_JSON, ADMIN_ACCOUNTS_JSON, TG_BOT_TOKEN, AGENT_APPS_SCRIPT_URL — all set via wrangler secret put.

6. ANTI-DEBUG: F12/right-click blocked on public site. Override: hold Q+2 for 2 seconds.

7. GEO-BLOCK: Admin dashboard blocked from South America (BR, AR, UY, PY, etc.) via ipapi.co.

This is a living document — update when system changes are deployed.

ACCESS RESTRICTED

POP-SORTE

⚡System Overview

Tech Stack

Architecture Diagram

Platform Reference

Key URLs

⚙️Cloudflare Workers

worker-member.js — Main Member API

Routes

Key Technical Details

worker-agent.js — Agent Bulk Registration API

Routes

Queue Consumer

Bindings (dari wrangler-agent.toml)

Key Logic

📜Google Apps Script

google-apps-script.gs — Member Ticket Handler

Actions (via doGet parameter)

Column Mapping (A–I)

google-apps-script-agent.gs — Agent Bulk Writer

Deployment Notes

📊Google Sheets — Database Catalog

IMPORTRANGE Chain — Core Data

🔄Data Flow Diagrams

Flow A — Member Ticket Submission

Flow B — Agent Bulk Registration

Flow C — Ticket Validation Pipeline

Flow D — Winner Calculation

🖥️Frontend Architecture

pop-sorte.js — Main JavaScript (~2,300 lines)

pop-sorte.css — Styles (~5,180 lines)

HTML Pages (13 files)

CSV URLs Used by Frontend

🔒Authentication & Security

1 — Worker API Token (Admin Endpoints)

2 — Admin Dashboard Session (Browser-side)

3 — Agent KV Token (Bulk Registration)

Geo-Blocking

Anti-Debugging (Frontend)

🔧Environment Variables & Configuration

worker-member.js — Environment Variables

Secrets (wrangler secret put)

worker-agent.js — Environment Variables

⚠️ CONCURSO_REFERENCE — Must Stay In Sync

Deployment Commands

🐛Troubleshooting & Error Reference

🚀Migration Plan — Supabase PostgreSQL

Planned Database Schema (7 Tables)

Validation Rules (Rule B — 2-Day Eligible Window)

Prize Distribution

Migration Goals & Improvements