#!/usr/bin/env python3
"""Exporta SOMENTE a cobertura diretamente coletada. Nunca mistura bases."""
import collections,csv,json,pathlib,sqlite3,zlib
ROOT=pathlib.Path(__file__).resolve().parent

def main():
 db=sqlite3.connect(ROOT/'coleta.sqlite');rows=[];modes=collections.defaultdict(collections.Counter)
 for key,uf,mun,name,payload in db.execute('SELECT id,uf,mun,name,payload FROM sections WHERE status="ok"'):
  p=json.loads(zlib.decompress(payload));row=dict(key=key,uf=uf,mun=mun,name=name,p=p);rows.append(row)
  # Uma observação por seção: primeira abertura, evitando peso extra de reinícios.
  if p['openings']:
   hour=min(t for dev,t in p['openings'])//3600
   if hour in (6,7,8,9):modes[(uf,mun)][8-hour]+=1
 output=[]
 for r in rows:
  p=r['p'];mode=modes[(r['uf'],r['mun'])].most_common();shift=mode[0][0] if mode else None
  tied=len(mode)>1 and mode[0][1]==mode[1][1]
  reliable=shift is not None and not tied
  hist=p['histogram'];total=sum(hist.values());after=sum(n for t,n in hist.items() if int(t)+shift*3600>17*3600) if reliable else None
  flags=[]
  if not reliable:flags.append('fuso_indeterminado')
  if p['clock_backwards']:flags.append('relogio_retrocedeu')
  if len(p['devices'])>1:flags.append('multiplos_equipamentos')
  close=max((t for dev,t in p['closings']),default=None)
  output.append(dict(secao=r['key'],uf=r['uf'],municipio=r['name'],total=total,ate_17h=total-after if after is not None else None,apos_17h=after,ajuste_brasilia_h=shift,apoio_fuso=mode[0][1]/sum(n for _,n in mode) if mode else 0,encerramento_brt_segundos=close+shift*3600 if close is not None and reliable else None,alertas=';'.join(flags)))
 if output:
  with open(ROOT/'secoes-diretas.csv','w') as f:
   w=csv.DictWriter(f,fieldnames=output[0].keys());w.writeheader();w.writerows(output)
 status=dict(db.execute('SELECT status,count(*) FROM sections GROUP BY status'))
 result=dict(cobertura=status,secoes_processadas=len(output),votos_total=sum(r['total'] for r in output),apos_17h=sum(r['apos_17h'] or 0 for r in output),secoes_sem_fuso=sum(r['apos_17h'] is None for r in output),secoes_com_alertas=sum(bool(r['alertas']) for r in output),nota='Resultado parcial da coleta direta; fuso inferido pela moda municipal, pendente de validação geográfica. Não substitui o mapa automaticamente.')
 (ROOT/'resumo-direto.json').write_text(json.dumps(result,ensure_ascii=False,indent=2));print(json.dumps(result,ensure_ascii=False))
if __name__=='__main__':main()