Atualizar SQL das Loterias #57
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Atualizar SQL das Loterias | |
| on: | |
| schedule: | |
| - cron: '59 2 * * *' # todo dia às 23h59 Brasília (02h59 UTC) | |
| workflow_dispatch: # permite rodar manualmente | |
| jobs: | |
| update: | |
| runs-on: ubuntu-latest | |
| permissions: | |
| contents: write # necessário para upload na release | |
| steps: | |
| - name: Instalar PHP | |
| run: sudo apt-get install -y php-cli | |
| - name: Baixar SQLs atuais da release | |
| run: | | |
| mkdir -p loterias | |
| for f in megasena lotofacil quina lotomania timemania duplasena diadesorte supersete maismilionaria; do | |
| echo "Baixando $f.sql..." | |
| curl -L --silent --output "loterias/$f.sql" \ | |
| "https://github.com/dantetesta/LotoLogic/releases/download/data/$f.sql" | |
| echo " $(du -sh loterias/$f.sql | cut -f1)" | |
| done | |
| - name: Atualizar concursos novos | |
| run: | | |
| php -r " | |
| \$API_BASE = 'https://loteriascaixa-api.herokuapp.com/api'; | |
| \$OUTPUT_DIR = 'loterias'; | |
| \$DELAY_MS = 200; | |
| \$LOTERIAS = [ | |
| ['type'=>'megasena', 'api'=>'megasena'], | |
| ['type'=>'lotofacil', 'api'=>'lotofacil'], | |
| ['type'=>'quina', 'api'=>'quina'], | |
| ['type'=>'lotomania', 'api'=>'lotomania'], | |
| ['type'=>'maismilionaria', 'api'=>'maismilionaria'], | |
| ['type'=>'duplasena', 'api'=>'duplasena'], | |
| ['type'=>'timemania', 'api'=>'timemania'], | |
| ['type'=>'diadesorte', 'api'=>'diadesorte'], | |
| ['type'=>'supersete', 'api'=>'supersete'], | |
| ]; | |
| function fetch_json(\$url) { | |
| \$ctx = stream_context_create(['http'=>['timeout'=>30,'header'=>'User-Agent: LotoLogic-Updater/1.0\r\n']]); | |
| \$json = @file_get_contents(\$url, false, \$ctx); | |
| return \$json ? json_decode(\$json, true) : null; | |
| } | |
| function escape_sql(\$val) { | |
| if (\$val === null) return 'NULL'; | |
| return \"'\" . str_replace(\"'\", \"''\", (string)\$val) . \"'\"; | |
| } | |
| function get_last_contest(\$filepath) { | |
| if (!file_exists(\$filepath)) return 0; | |
| \$content = file_get_contents(\$filepath); | |
| if (preg_match('/last_imported_contest\s*=\s*(\d+)/', \$content, \$m)) return (int)\$m[1]; | |
| preg_match_all('/VALUES\s*\([^,]+,\s*(\d+)\s*,/', \$content, \$m); | |
| return !empty(\$m[1]) ? (int)max(\$m[1]) : 0; | |
| } | |
| function contest_to_sql(\$game_type, \$c) { | |
| \$concurso = (int)\$c['concurso']; | |
| \$dezenas = \$c['dezenas'] ?? []; | |
| \$dezenasOrdem = \$c['dezenasOrdemSorteio'] ?? \$dezenas; | |
| \$nums = array_map('intval', \$dezenas); sort(\$nums); | |
| \$nums_draw = array_map('intval', \$dezenasOrdem); | |
| \$acumulou = !empty(\$c['acumulou']) ? 1 : 0; | |
| \$trevos = \$c['trevosSorteados'] ?? \$c['trevos'] ?? []; | |
| \$trevos_json = !empty(\$trevos) ? escape_sql(json_encode(\$trevos)) : 'NULL'; | |
| \$time_coracao = \$c['timeCoracao'] ?? \$c['nomeTimeCoracao'] ?? null; | |
| \$time_coracao = (\$time_coracao && trim(\$time_coracao) !== '') ? escape_sql(\$time_coracao) : 'NULL'; | |
| \$mes_sorte = \$c['mesSorte'] ?? \$c['nomesMesDaSorte'] ?? null; | |
| \$mes_sorte = (\$mes_sorte && trim(\$mes_sorte) !== '') ? escape_sql(\$mes_sorte) : 'NULL'; | |
| \$nums_text = implode(', ', array_map(fn(\$n) => str_pad(\$n,2,'0',STR_PAD_LEFT), \$nums)); | |
| \$sql = 'INSERT OR IGNORE INTO contests (game_type,contest_number,contest_date,location,numbers_draw_order_json,numbers_sorted_json,numbers_sorted_text,accumulated,next_contest_number,next_contest_date,estimated_next_prize,amount_collected,raw_json,trevos_json,time_coracao,mes_sorte) VALUES ('; | |
| \$sql .= escape_sql(\$game_type).','; | |
| \$sql .= \$concurso.','; | |
| \$sql .= escape_sql(\$c['data'] ?? '').','; | |
| \$sql .= (\$c['local'] ?? \$c['localSorteio'] ?? null ? escape_sql(\$c['local'] ?? \$c['localSorteio']) : 'NULL').','; | |
| \$sql .= escape_sql(json_encode(\$nums_draw)).','; | |
| \$sql .= escape_sql(json_encode(\$nums)).','; | |
| \$sql .= escape_sql(\$nums_text).','; | |
| \$sql .= \$acumulou.','; | |
| \$sql .= (\$c['proximoConcurso'] ?? null) !== null ? (int)\$c['proximoConcurso'] : 'NULL'; \$sql .= ','; | |
| \$sql .= (\$c['dataProximoConcurso'] ?? null) ? escape_sql(\$c['dataProximoConcurso']) : 'NULL'; \$sql .= ','; | |
| \$sql .= (\$c['valorEstimadoProximoConcurso'] ?? null) !== null ? (float)\$c['valorEstimadoProximoConcurso'] : 'NULL'; \$sql .= ','; | |
| \$sql .= (\$c['valorArrecadado'] ?? null) !== null ? (float)\$c['valorArrecadado'] : 'NULL'; \$sql .= ','; | |
| \$sql .= escape_sql(json_encode(\$c, JSON_UNESCAPED_UNICODE)).','; | |
| \$sql .= \$trevos_json.','; | |
| \$sql .= \$time_coracao.','; | |
| \$sql .= \$mes_sorte.');'; | |
| foreach (\$c['premiacoes'] ?? \$c['premiacao'] ?? [] as \$p) { | |
| \$sql .= '\nINSERT OR IGNORE INTO contest_prizes (contest_id,description,range_number,winners_count,prize_value) SELECT id,'.escape_sql(\$p['descricao']??null).','.(\$p['faixa']??null !== null?(int)\$p['faixa']:'NULL').','.(int)(\$p['ganhadores']??0).','.(float)(\$p['valorPremio']??0).' FROM contests WHERE game_type='.escape_sql(\$game_type).' AND contest_number='.\$concurso.';'; | |
| } | |
| return \$sql; | |
| } | |
| \$total_new = 0; | |
| foreach (\$LOTERIAS as \$lot) { | |
| \$type = \$lot['type']; \$api = \$lot['api']; | |
| \$filepath = \"\$OUTPUT_DIR/\$type.sql\"; | |
| \$local_last = get_last_contest(\$filepath); | |
| \$latest = fetch_json(\"\$API_BASE/\$api/latest\"); | |
| if (!\$latest || !isset(\$latest['concurso'])) { echo \"[ERRO] \$type: API indisponivel\n\"; continue; } | |
| \$remote_last = (int)\$latest['concurso']; | |
| echo \"\$type: local=#\$local_last api=#\$remote_last\n\"; | |
| if (\$local_last >= \$remote_last) { echo \" ja atualizado\n\"; continue; } | |
| \$missing = \$remote_last - \$local_last; | |
| echo \" faltam \$missing concursos...\n\"; | |
| \$new_sql = '\n-- Atualizacao: '.date('Y-m-d H:i:s').'\n'; | |
| \$imported = 0; | |
| for (\$n = \$local_last + 1; \$n <= \$remote_last; \$n++) { | |
| \$contest = fetch_json(\"\$API_BASE/\$api/\$n\"); | |
| if (!\$contest || !isset(\$contest['concurso'])) continue; | |
| \$new_sql .= contest_to_sql(\$type, \$contest).'\n'; | |
| \$imported++; | |
| usleep(\$DELAY_MS * 1000); | |
| } | |
| \$new_sql .= \"\nUPDATE sync_state SET last_imported_contest=\$remote_last,last_synced_at=datetime('now') WHERE game_type='\$type';\n\"; | |
| \$content = file_get_contents(\$filepath); | |
| \$content = preg_replace('/\nUPDATE sync_state SET last_imported_contest=\d+.*WHERE game_type=\\''.preg_quote(\$type,'/').'\\';?\s*$/', '', \$content); | |
| file_put_contents(\$filepath, \$content.\$new_sql); | |
| echo \" +\$imported concursos adicionados\n\"; | |
| \$total_new += \$imported; | |
| } | |
| echo \"Total: \$total_new novos concursos\n\"; | |
| " | |
| - name: Subir SQLs atualizados na release | |
| env: | |
| GH_TOKEN: ${{ secrets.GITHUB_TOKEN }} | |
| run: | | |
| for f in loterias/*.sql; do | |
| filename=$(basename "$f") | |
| echo "Enviando $filename ($(du -sh $f | cut -f1))..." | |
| gh release upload data "$f" --clobber --repo dantetesta/LotoLogic | |
| done | |
| echo "Concluido" | |
| - name: Sincronizar dados com Supabase PostgreSQL | |
| env: | |
| SUPABASE_DB_HOST: ${{ secrets.SUPABASE_DB_HOST }} | |
| SUPABASE_DB_PASSWORD: ${{ secrets.SUPABASE_DB_PASSWORD }} | |
| run: | | |
| sudo apt-get install -y postgresql-client -qq | |
| LOTERIAS="megasena lotofacil quina lotomania maismilionaria duplasena timemania diadesorte supersete" | |
| for LOT in $LOTERIAS; do | |
| echo "=== Sincronizando $LOT com Supabase ===" | |
| python3 - "$LOT" << 'PYEOF' | |
| import re, json, sys | |
| lot = sys.argv[1] | |
| sql_file = f"loterias/{lot}.sql" | |
| out_file = f"/tmp/{lot}_pg.sql" | |
| with open(sql_file, "r") as f: | |
| content = f.read() | |
| lines = content.split("\n") | |
| count = 0 | |
| sql_out = [] | |
| for line in lines: | |
| if not line.startswith("INSERT OR IGNORE INTO contests"): | |
| continue | |
| m = re.search(r"VALUES\('([^']+)',(\d+),'([^']*)',(?:'([^']*)'|NULL),(?:'([^']*)'|NULL),'(\[[^\]]+\])','([^']*)',(\d+)", line) | |
| if not m: | |
| continue | |
| game_type = m.group(1) | |
| concurso = int(m.group(2)) | |
| data_str = m.group(3) | |
| nums_json = m.group(6) | |
| acumulou = m.group(8) == "1" | |
| try: | |
| nums = json.loads(nums_json) | |
| except: | |
| continue | |
| raw_match = re.search(r",'(\{.*?\})',(NULL|'[^']*'),(NULL|'[^']*'),(NULL|'[^']*')\);", line) | |
| ganhadores_list = [] | |
| premio = 0 | |
| if raw_match: | |
| try: | |
| raw = json.loads(raw_match.group(1).replace("''", "'")) | |
| for p in raw.get("premiacoes", []): | |
| ganhadores_list.append({ | |
| "faixa": p.get("descricao", ""), | |
| "ganhadores": int(p.get("ganhadores", 0)), | |
| "premio": float(p.get("valorPremio", 0)) | |
| }) | |
| except: | |
| pass | |
| data_pg = "NULL" | |
| if data_str and "/" in data_str: | |
| parts = data_str.split("/") | |
| if len(parts) == 3: | |
| data_pg = f"'{parts[2]}-{parts[1]}-{parts[0]}'" | |
| nums_pg = json.dumps(nums) | |
| ganh_pg = json.dumps(ganhadores_list).replace("'", "''") | |
| sql_out.append( | |
| f"INSERT INTO resultados (loteria, concurso, data, numeros, premio_acumulado, acumulou, ganhadores) " | |
| f"VALUES ('{game_type}', {concurso}, {data_pg}, '{nums_pg}', {premio}, {str(acumulou).lower()}, '{ganh_pg}') " | |
| f"ON CONFLICT (loteria, concurso) DO NOTHING;" | |
| ) | |
| count += 1 | |
| with open(out_file, "w") as f: | |
| f.write("\n".join(sql_out)) | |
| print(f" {count} concursos preparados para {lot}") | |
| PYEOF | |
| PGPASSWORD="$SUPABASE_DB_PASSWORD" psql \ | |
| -h "$SUPABASE_DB_HOST" \ | |
| -p 54320 \ | |
| -U postgres \ | |
| -d postgres \ | |
| -f "/tmp/${LOT}_pg.sql" \ | |
| -q 2>&1 | tail -1 | |
| echo " $LOT sincronizado!" | |
| done | |
| echo "" | |
| echo "=== TOTAIS NO SUPABASE ===" | |
| PGPASSWORD="$SUPABASE_DB_PASSWORD" psql \ | |
| -h "$SUPABASE_DB_HOST" \ | |
| -p 54320 \ | |
| -U postgres \ | |
| -d postgres \ | |
| -c "SELECT loteria, COUNT(*) as total FROM resultados GROUP BY loteria ORDER BY total DESC;" |