#!/usr/bin/env python3
"""
Coleta empresas REAIS da Minha Receita API por cidade (sem filtro CNAE)
Depois filtra localmente pelos CNAEs alvo B2B/B2C
Mantém estrutura do dashboard (B2B/B2C) com dados 100% reais
"""

import requests
import json
import csv
import time
from pathlib import Path
from datetime import datetime
from typing import List, Dict, Set, Optional
import pandas as pd

# ============ CONFIG ============
MINHA_RECEITA_API = "https://minhareceita.org"
DATA_DIR = Path("/root/lelyon_prospecting/data")
DATA_DIR.mkdir(exist_ok=True)

# 8 cidades do Grande ABC + SP (códigos IBGE)
TARGET_CITIES = {
    "3550308": "São Paulo",
    "3547000": "São Bernardo do Campo",
    "3548701": "Santo André",
    "3548800": "São Caetano do Sul",
    "3513606": "Diadema",
    "3529305": "Mauá",
    "3542607": "Ribeirão Pires",
    "3543506": "Rio Grande da Serra",
}

# CNAEs alvo B2B (serviços LELYON)
B2B_CNAES = {
    "6201501", "6201502", "6202300", "6203100", "6204000",
    "6209100", "6311900", "6312200", "6201500", "6202301",
    "7311400", "7319002", "7319003", "7319004", "7319099",
    "7320300", "7020400", "8211300", "8299799", "4791302",
    "4791303", "4789099", "8211300", "8299799"
}

# CNAEs alvo B2C (varejo/serviços locais)
B2C_CNAES = {
    "4711302", "4712100", "4721102", "4722100", "4723700",
    "4730201", "4741300", "4742201", "4744001", "4744002",
    "4751201", "4752100", "4753100", "4754701", "4755501",
    "4761001", "4762100", "4763800", "4771701", "4772500",
    "4781400", "4782201", "4783100", "4784900", "4789099",
    "4791302", "4791303", "5611201", "5611203", "5612100",
    "5620101", "5630900", "9602501", "9602502", "9609202",
    "9609299", "9601000", "9603300", "9609201", "9609299"
}

ALL_TARGET_CNAES = B2B_CNAES | B2C_CNAES

# Rate limiting - API permite 50 req/s, usamos 10/s para segurança
DELAY_BETWEEN_REQUESTS = 0.1
MAX_PAGES_PER_CITY = 50  # 100 por página = 5000 empresas por cidade
MAX_RETRIES = 3
TIMEOUT = 30

session = requests.Session()
session.headers.update({
    "User-Agent": "LELYON-Prospecting/1.0",
    "Accept": "application/json"
})

# ============ HELPERS ============
def save_progress(progress: Dict, filename: str):
    filepath = DATA_DIR / filename
    with open(filepath, "w", encoding="utf-8") as f:
        json.dump(progress, f, ensure_ascii=False, indent=2)

def load_progress(filename: str) -> Dict:
    filepath = DATA_DIR / filename
    if filepath.exists():
        with open(filepath, "r", encoding="utf-8") as f:
            return json.load(f)
    return {"processed_cities": [], "companies": {"b2b": {}, "b2c": {}}, "last_update": None}

def minha_receita_search(uf: str, municipio: str, cursor: str = None, limit: int = 100) -> Optional[Dict]:
    """Busca empresas na Minha Receita API por UF e município."""
    params = {"uf": uf, "municipio": municipio, "limit": limit}
    if cursor:
        params["cursor"] = cursor
    
    url = f"{MINHA_RECEITA_API}/"
    
    for attempt in range(3):
        try:
            resp = session.get(url, params=params, timeout=30)
            
            if resp.status_code == 200:
                return resp.json()
            elif resp.status_code == 429:
                wait = 2 ** attempt * 5
                print(f"   ⏳ Rate limited, aguardando {wait}s...")
                time.sleep(wait)
                continue
            else:
                print(f"   ⚠️ Erro {resp.status_code}: {resp.text[:200]}")
        except requests.Timeout:
            print(f"   ⏱️ Timeout (tentativa {attempt+1})")
        except Exception as e:
            print(f"   ❌ Erro: {e}")
        
        if attempt < 2:
            time.sleep(2 ** attempt)
    
    return None

def search_city_all_pages(city_code: str, city_name: str, max_pages: int = 50) -> List[Dict]:
    """Busca todas as páginas de resultados para uma cidade."""
    all_companies = []
    cursor = None
    page_count = 0
    
    print(f"\n🔍 Buscando empresas em {city_name} (código {city_code})...")
    
    while page_count < max_pages:
        result = minha_receita_search("SP", city_code, cursor)
        
        if not result:
            print(f"   ❌ Falha na busca")
            break
        
        companies = result.get("data", [])
        all_companies.extend(companies)
        page_count += 1
        
        cursor = result.get("cursor")
        if not cursor or not companies:
            break
        
        print(f"   📄 Página {page_count}: +{len(companies)} empresas (total: {len(all_companies)})")
        time.sleep(DELAY_BETWEEN_REQUESTS)
    
    print(f"   ✅ {city_name}: {len(all_companies)} empresas encontradas")
    return all_companies

def filter_by_cnae(companies: List[Dict]) -> Dict:
    """Filtra empresas por CNAE alvo e classifica B2B/B2C."""
    b2b = []
    b2c = []
    
    for c in companies:
        cnae = str(c.get("cnae_fiscal", "")).strip()
        if cnae in B2B_CNAES:
            b2b.append(c)
        elif cnae in B2C_CNAES:
            b2c.append(c)
    
    return {"b2b": b2b, "b2c": b2c}

def enrich_company_data(company: Dict, category: str) -> Dict:
    """Converte dados da Minha Receita para formato do dashboard."""
    cnpj_raw = company.get("cnpj", "").replace(".", "").replace("/", "").replace("-", "")
    cnpj_fmt = f"{cnpj_raw[:2]}.{cnpj_raw[2:5]}.{cnpj_raw[5:8]}/{cnpj_raw[8:12]}-{cnpj_raw[12:]}" if len(cnpj_raw) == 14 else company.get("cnpj", "")
    
    # Telefones
    phones = []
    for i in [1, 2]:
        ddd = company.get(f"ddd_telefone_{i}")
        tel = company.get(f"telefone_{i}")
        if ddd and tel:
            clean = str(tel).replace("-", "").replace(" ", "")
            phones.append(f"({ddd}) {clean[:4]}-{clean[4:]}" if len(clean) == 8 else f"({ddd}) {clean}")
    
    # Endereço
    endereco_parts = []
    if company.get("descricao_tipo_de_logradouro") and company.get("logradouro"):
        endereco_parts.append(f"{company['descricao_tipo_de_logradouro']} {company['logradouro']}")
    if company.get("numero"):
        endereco_parts.append(str(company["numero"]))
    if company.get("complemento"):
        endereco_parts.append(company["complemento"])
    if company.get("bairro"):
        endereco_parts.append(company["bairro"])
    if company.get("municipio") and company.get("uf"):
        endereco_parts.append(f"{company['municipio']}/{company['uf']}")
    if company.get("cep"):
        cep = str(company["cep"]).zfill(8)
        endereco_parts.append(f"CEP {cep[:5]}-{cep[5:]}")
    
    # Sócios
    qsa = company.get("qsa", [])
    socios = [s.get("nome_socio", "") for s in qsa if s.get("nome_socio")]
    
    # Capital
    capital = company.get("capital_social", 0)
    try:
        capital_val = float(str(capital).replace(",", "."))
    except:
        capital_val = 0
    
    # Situação
    situacao_map = {"ATIVA": "Ativa", "BAIXADA": "Baixada", "INAPTA": "Inapta", "SUSPENSA": "Suspensa", "NULA": "Nula"}
    situacao = situacao_map.get(company.get("descricao_situacao_cadastral", ""), company.get("situacao_cadastral", ""))
    
    # Porte
    porte_map = {"DEMAIS": "Grande", "EMPRESA DE PEQUENO PORTE": "EPP", "MICROEMPRESA": "ME", "MEI": "MEI"}
    porte = porte_map.get(company.get("porte", ""), company.get("porte", "ME"))
    
    # Simples
    simples = company.get("opcao_pelo_simples", "")
    simples_str = "Sim" if simples in ["S", "true", "True"] else "Não"
    
    # Natureza jurídica
    natureza = company.get("natureza_juridica", "")
    
    # CNAE
    cnae_raw = company.get("cnae_fiscal", "")
    cnae = str(cnae_raw) if cnae_raw else ""
    cnae_desc = company.get("cnae_fiscal_descricao", "")
    
    # CNAEs secundários
    cnaes_sec = company.get("cnaes_secundarios", [])
    cnaes_sec_str = ", ".join([str(c.get("codigo", "")) for c in cnaes_sec if c.get("codigo")])
    
    # Sócios formatados
    qsa_str = "; ".join([f"{s.get('nome_socio','')} ({s.get('qualificacao_socio','')})" for s in qsa if s.get("nome_socio")])
    
    # Map CNAE to service
    service_map = {
        "6201501": "PWA/App Development", "6201502": "PWA/App Development",
        "6202300": "PWA/App Development", "6203100": "PWA/App Development",
        "6204000": "Agente IA/Automação", "6209100": "Agente IA/Automação",
        "6311900": "Agente IA/Automação", "6312200": "Agente IA/Automação",
        "4791302": "Loja WordPress/WooCommerce", "4791303": "Loja WordPress/WooCommerce",
        "4789099": "Loja WordPress/WooCommerce", "7020400": "CRM & Estoque QR-Code",
        "7311400": "Marketing Digital/Ads", "7319002": "Marketing Digital/Ads",
        "7319003": "Marketing Digital/Ads", "7319004": "Marketing Digital/Ads",
        "7319099": "Marketing Digital/Ads", "7320300": "Marketing Digital/Ads",
        "7020400": "CRM & Estoque QR-Code", "8211300": "CRM & Estoque QR-Code",
        "8299799": "CRM & Estoque QR-Code", "6209100": "Segurança + SEO",
        "4791302": "Loja WordPress/WooCommerce", "5611201": "Loja WordPress/WooCommerce",
        "5611203": "Loja WordPress/WooCommerce", "9602501": "Loja WordPress/WooCommerce",
        "9602502": "Loja WordPress/WooCommerce", "4781400": "Loja WordPress/WooCommerce",
        "4771701": "Loja WordPress/WooCommerce", "4772500": "Loja WordPress/WooCommerce",
        "4711302": "Loja WordPress/WooCommerce",
    }
    
    # Fit score
    if cnae in B2B_CNAES:
        category = "B2B"
        b2c_cat = "N/A"
    else:
        category = "B2C"
        b2c_cat = guess_b2c_category(cnae)
    
    fit_scores = {
        "6201501": 95, "6201502": 90, "6202300": 85, "6203100": 80,
        "6204000": 95, "6209100": 85, "6311900": 80, "6312200": 80,
        "4791302": 95, "4791303": 90, "4789099": 85,
        "7020400": 90, "7311400": 95, "7319002": 90, "7319003": 85,
        "7319004": 85, "7319099": 80, "7320300": 70,
        "8211300": 85, "8299799": 80, "6209100": 75,
        "4791302": 90, "5611201": 70, "5611203": 70,
        "9602501": 60, "9602502": 60, "4781400": 75,
        "4771701": 80, "4772500": 75, "4711302": 85,
    }
    
    lelyon_fits = {
        "PWA/App Development": 0, "Agente IA/Automação": 0, "Loja WordPress/WooCommerce": 0,
        "CRM & Estoque QR-Code": 0, "Segurança + SEO": 0, "Marketing Digital/Ads": 0
    }
    
    lelyon_map = {
        "6201501": {"PWA/App Development": 95, "Agente IA/Automação": 80},
        "6201502": {"PWA/App Development": 90, "Agente IA/Automação": 75},
        "6202300": {"PWA/App Development": 85, "Agente IA/Automação": 70},
        "6203100": {"PWA/App Development": 80, "Agente IA/Automação": 65},
        "6204000": {"Agente IA/Automação": 95, "PWA/App Development": 70, "Segurança + SEO": 60},
        "6209100": {"Agente IA/Automação": 85, "Segurança + SEO": 70, "CRM & Estoque QR-Code": 50},
        "6311900": {"Agente IA/Automação": 80, "PWA/App Development": 60},
        "4791302": {"Loja WordPress/WooCommerce": 95, "CRM & Estoque QR-Code": 70},
        "7020400": {"CRM & Estoque QR-Code": 90, "Agente IA/Automação": 60},
        "7311400": {"Marketing Digital/Ads": 95, "PWA/App Development": 50},
        "7319002": {"Marketing Digital/Ads": 90}, "7319003": {"Marketing Digital/Ads": 85},
        "7319004": {"Marketing Digital/Ads": 85}, "7319099": {"Marketing Digital/Ads": 80},
        "7320300": {"Marketing Digital/Ads": 70, "Agente IA/Automação": 50},
        "8211300": {"CRM & Estoque QR-Code": 85}, "8299799": {"CRM & Estoque QR-Code": 80},
        "6209100": {"Segurança + SEO": 75, "Agente IA/Automação": 60},
        "4791302": {"Loja WordPress/WooCommerce": 90, "Marketing Digital/Ads": 60},
        "5611201": {"Loja WordPress/WooCommerce": 70, "CRM & Estoque QR-Code": 60},
        "5611203": {"Loja WordPress/WooCommerce": 70, "CRM & Estoque QR-Code": 55},
        "9602501": {"Loja WordPress/WooCommerce": 60, "Marketing Digital/Ads": 65},
        "9602502": {"Loja WordPress/WooCommerce": 60, "Marketing Digital/Ads": 70},
        "4781400": {"Loja WordPress/WooCommerce": 75, "Marketing Digital/Ads": 60},
        "4771701": {"Loja WordPress/WooCommerce": 80, "CRM & Estoque QR-Code": 65},
        "4772500": {"Loja WordPress/WooCommerce": 75, "Marketing Digital/Ads": 65},
        "4711302": {"Loja WordPress/WooCommerce": 85, "CRM & Estoque QR-Code": 80},
    }
    
    return {
        "cnpj": f"{cnpj_raw[:2]}.{cnpj_raw[2:5]}.{cnpj_raw[5:8]}/{cnpj_raw[8:12]}-{cnpj_raw[12:]}" if len(cnpj_raw) == 14 else "",
        "razao_social": company.get("razao_social", ""),
        "nome_fantasia": company.get("nome_fantasia", "") or company.get("razao_social", ""),
        "cidade": company.get("municipio", ""),
        "bairro": company.get("bairro", ""),
        "cnae": cnae,
        "cnae_desc": cnae_desc,
        "situacao": company.get("descricao_situacao_cadastral", ""),
        "porte": company.get("porte", ""),
        "capital_social": float(str(company.get("capital_social", "0")).replace(",", ".")) if company.get("capital_social") else 0,
        "data_abertura": company.get("data_inicio_atividade", ""),
        "tipo_prospeccao": category,
        "b2c_categoria": "N/A" if category == "B2B" else guess_b2c_category(cnae),
        "best_fit_service": service_map.get(cnae, "N/A"),
        "fit_score": fit_scores.get(cnae, 40 if category == "B2B" else 35),
        "lelyon_fit": lelyon_map.get(cnae, lelyon_fits),
        "email": "",
        "telefone": "; ".join(phones),
        "whatsapp": "",
        "website": "",
        "website_status": "pending",
        "contato_nome": socios[0] if socios else "",
        "contato_cargo": qsa[0].get("qualificacao_socio", "") if qsa else "",
        "linkedin": "",
        "instagram": "",
        "facebook": "",
        "fonte_enriquecimento": "minha_receita",
        "data_enriquecimento": datetime.now().isoformat(),
        "observacoes": f"Minha Receita API | CNAE: {cnae} | Cidade: {company.get('municipio','')}",
        "natureza_juridica": natureza,
        "simples_nacional": simples_str,
        "data_situacao": company.get("data_situacao_cadastral", ""),
        "cnaes_secundarios": cnaes_sec_str,
        "qsa": qsa_str,
        "natureza_juridica": natureza,
        "simples_nacional": simples_str,
        "data_situacao": company.get("data_situacao_cadastral", ""),
        "cnaes_secundarios": cnaes_sec_str,
        "qsa": qsa_str,
    }

def guess_b2c_category(cnae: str) -> str:
    cnae_str = str(cnae)
    categorias = {
        "47": "Varejo & Comércio", "56": "Alimentação & Bebidas",
        "96": "Beleza & Estética", "45": "Serviços Automotivos",
        "85": "Educação & Treinamento", "86": "Saúde & Bem-estar",
        "55": "Hospedagem & Turismo", "90": "Artes & Cultura",
        "93": "Lazer & Esportes", "97": "Serviços Domésticos"
    }
    return categorias.get(cnae_str[:2], "Outros")

def save_results(b2b: List[Dict], b2c: List[Dict]):
    """Salva resultados em JSON, CSV e Excel."""
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    
    combined = {"b2b": b2b, "b2c": b2c}
    all_companies = b2b + b2c
    
    # JSON
    json_file = DATA_DIR / f"lelyon_real_minhareceita_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json"
    with open(json_file, "w", encoding="utf-8") as f:
        json.dump(combined, f, ensure_ascii=False, indent=2)
    
    # CSV
    csv_file = DATA_DIR / f"lelyon_real_minhareceita_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
    if all_companies:
        fieldnames = list(all_companies[0].keys())
        with open(csv_file, "w", encoding="utf-8-sig", newline="") as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames)
            writer.writeheader()
            for c in all_companies:
                row = {k: c.get(k, "") for k in fieldnames}
                row["lelyon_fit"] = json.dumps(c.get("lelyon_fit", {}), ensure_ascii=False)
                writer.writerow(row)
    
    # Excel
    excel_file = DATA_DIR / f"lelyon_real_minhareceita_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
    with pd.ExcelWriter(excel_file, engine="openpyxl") as writer:
        df_all = pd.DataFrame(all_companies)
        df_all["lelyon_fit_str"] = df_all["lelyon_fit"].apply(lambda x: json.dumps(x, ensure_ascii=False) if isinstance(x, dict) else str(x))
        
        df_b2b = df_all[df_all["tipo_prospeccao"] == "B2B"]
        df_b2c = df_all[df_all["tipo_prospeccao"] == "B2C"]
        
        df_all.to_excel(writer, sheet_name="Todos", index=False)
        df_b2b.to_excel(writer, sheet_name="B2B", index=False)
        df_b2c.to_excel(writer, sheet_name="B2C", index=False)
        
        # Stats
        stats = [
            {"Métrica": "Total", "Valor": len(all_companies)},
            {"Métrica": "B2B", "Valor": len(b2b)},
            {"Métrica": "B2C", "Valor": len(b2c)},
        ]
        pd.DataFrame(stats).to_excel(writer, sheet_name="Estatisticas", index=False)
    
    # Symlinks
    for src, dst_name in [
        (json_file, "lelyon_real_minhareceita_latest.json"),
        (csv_file, "lelyon_real_minhareceita_latest.csv"),
        (excel_file, "lelyon_real_minhareceita_latest.xlsx"),
    ]:
        dst = DATA_DIR / dst_name
        if dst.exists():
            dst.unlink()
        dst.symlink_to(src.name)
    
    print(f"\n💾 Arquivos salvos:")
    print(f"   📄 JSON: {json_file}")
    print(f"   📊 CSV:  {csv_file}")
    print(f"   📈 Excel: {excel_file}")
    print(f"   🔗 Symlinks: *_latest.* atualizados")

# ============ MAIN ============
def main():
    print("=" * 70)
    print("COLETA REAL - MINHA RECEITA API (busca por cidade + filtro CNAE)")
    print("=" * 70)
    print(f"Cidades: {list(TARGET_CITIES.values())}")
    print(f"CNAEs B2B: {len(B2B_CNAES)} | B2C: {len(B2C_CNAES)}")
    print()
    
    all_b2b = []
    all_b2c = []
    total_found = 0
    total_filtered = 0
    
    for city_code, city_name in TARGET_CITIES.items():
        # Buscar todas as páginas
        companies = search_city_all_pages(city_code, city_name)
        total_found += len(companies)
        
        # Filtrar por CNAE alvo
        filtered = filter_by_cnae(companies)
        all_b2b.extend(filtered["b2b"])
        all_b2c.extend(filtered["b2c"])
        total_filtered += len(filtered["b2b"]) + len(filtered["b2c"])
        
        print(f"   📊 {city_name}: {len(companies)} total → {len(filtered['b2b'])} B2B + {len(filtered['b2c'])} B2C")
        
        # Pequena pausa entre cidades
        time.sleep(1)
    
    print(f"\n{'='*60}")
    print(f"COLETA CONCLUÍDA!")
    print(f"Total empresas consultadas: {total_found:,}")
    print(f"Empresas filtradas (CNAE alvo): {total_filtered:,}")
    print(f"  B2B: {len(all_b2b):,}")
    print(f"  B2C: {len(all_b2c):,}")
    
    if total_filtered == 0:
        print("⚠️ Nenhuma empresa encontrada com CNAEs alvo.")
        return
    
    # Enriquecer dados para formato dashboard
    print("\n🔧 Formatando dados para o dashboard...")
    b2b_formatted = [enrich_company_data(c, "B2B") for c in all_b2b]
    b2c_formatted = [enrich_company_data(c, "B2C") for c in all_b2c]
    
    # Salvar
    save_results(b2b_formatted, b2c_formatted)
    
    print("\n✅ CONCLUÍDO! Dados reais prontos para o dashboard.")

if __name__ == "__main__":
    main()