import glob
from collections import defaultdict

SEC = {'A':'Agropecuaria','B':'Ind. extrativas','C':'Ind. de transformacao',
'D':'Eletricidade e gas','E':'Agua, esgoto e residuos','F':'Construcao','G':'Comercio',
'H':'Transporte e armazenagem','I':'Alojamento e alimentacao','J':'Informacao e comunicacao',
'K':'Financeiro/seguros','L':'Atividades imobiliarias','M':'Prof., cientificas e tecnicas',
'N':'Atividades administrativas','O':'Administracao publica','P':'Educacao',
'Q':'Saude humana e serv. sociais','R':'Artes, cultura e esporte','S':'Outros servicos',
'T':'Servicos domesticos','U':'Organismos internacionais'}
# posicoes fixas do layout
C_COMP,C_SEC,C_SALDO,C_SUB,C_IDADE,C_SEXO,C_SAL = 0,4,6,5,10,13,20

rows=[]
def load(pat, kind):
    for f in sorted(glob.glob(pat)):
        with open(f, encoding='utf-8', errors='replace') as fh:
            fh.readline()
            for line in fh:
                p=line.rstrip('\n').split(';')
                if len(p) < 21: continue
                rows.append({'kind':kind,'comp':p[C_COMP],'sec':p[C_SEC],'sub':p[C_SUB],
                    'saldo':int(p[C_SALDO]),'sal':p[C_SAL],'idade':p[C_IDADE],'sexo':p[C_SEXO],
                    'arq':f})
load('oeiras_CAGEDMOV*.csv','MOV'); load('oeiras_CAGEDFOR*.csv','FOR'); load('oeiras_CAGEDEXC*.csv','EXC')

M={1:'jan',2:'fev',3:'mar',4:'abr',5:'mai',6:'jun',7:'jul'}
agg=defaultdict(lambda:{'adm':0,'des':0}); aggmov=defaultdict(lambda:{'adm':0,'des':0})
for r in rows:
    s = -1 if r['kind']=='EXC' else 1
    k='adm' if r['saldo']==1 else 'des'
    agg[r['comp']][k]+=s
    if r['kind']=='MOV': aggmov[r['comp']][k]+=1

print("=== OEIRAS-PI (cod. 220700) — NOVO CAGED 2026 ===")
print("            declarado no mes      |   ajustado (com fora do prazo e exclusoes)")
print(f"{'Mes':>5} | {'Adm':>5} {'Desl':>5} {'Saldo':>6} | {'Adm':>5} {'Desl':>5} {'Saldo':>6}")
ta=td=0
for c in sorted(k for k in agg if k.startswith('2026')):
    a,d=aggmov[c]['adm'],aggmov[c]['des']; A,D=agg[c]['adm'],agg[c]['des']
    ta+=A; td+=D
    print(f"{M[int(c[4:])]:>5} | {a:5d} {d:5d} {a-d:+6d} | {A:5d} {D:5d} {A-D:+6d}")
print(f"{'TOTAL':>5} |                     | {ta:5d} {td:5d} {ta-td:+6d}")

ant={k:v for k,v in agg.items() if not k.startswith('2026')}
if ant:
    print("\nFora do prazo declarado em 2026 mas referente a 2025 (fora do acumulado do ano):")
    for c in sorted(ant): print(f"  {c}: adm {ant[c]['adm']}  desl {ant[c]['des']}  saldo {ant[c]['adm']-ant[c]['des']:+d}")

sect=defaultdict(lambda:{'adm':0,'des':0})
for r in rows:
    if not r['comp'].startswith('2026'): continue
    s=-1 if r['kind']=='EXC' else 1
    sect[r['sec']]['adm' if r['saldo']==1 else 'des']+=s
print("\n=== POR SETOR (jan-jul/2026) ===")
print(f"{'Setor':<32} {'Adm':>5} {'Desl':>5} {'Saldo':>6}")
for k,v in sorted(sect.items(), key=lambda x:-(x[1]['adm']-x[1]['des'])):
    print(f"{SEC.get(k,k):<32} {v['adm']:5d} {v['des']:5d} {v['adm']-v['des']:+6d}")

sals=[]
for r in rows:
    if r['comp'].startswith('2026') and r['kind']!='EXC' and r['saldo']==1:
        try:
            v=float(r['sal'].replace(',','.'))
            if v>0: sals.append(v)
        except: pass
sals.sort()
print(f"\nSalario medio de admissao jan-jul/2026: R$ {sum(sals)/len(sals):,.2f} | mediana R$ {sals[len(sals)//2]:,.2f} | n={len(sals)}")

sx=defaultdict(int)
for r in rows:
    if r['comp'].startswith('2026'):
        s=-1 if r['kind']=='EXC' else 1
        sx[r['sexo']]+= s*r['saldo']
print("Saldo por sexo (1=homem, 3=mulher):", dict(sx))