#!/usr/bin/env python3
"""
Coleta de dados REAIS de empresas para prospecção da LELYON
Fonte: Minha Receita API (minhareceita.org) - busca paginada por CNAE + UF
Gera bases B2B (serviços LELYON) e B2C (varejo/serviços locais) reais
"""

import requests
import json
import csv
import time
from pathlib import Path
from datetime import datetime
from typing import List, Dict, 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
CITIES_IBGE = {
    "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",
}

# CNAE Groups - usando a mesma categorização do dashboard
CNAE_GROUPS = [
    {
        "label": "LELYON - Serviços Alvo (B2B)",
        "category": "B2B",
        "cnaes": [
            "6204000", "6201501", "6201502", "6202300", "6203100",
            "6209100", "6311900", "7311400", "7319002", "7319003",
            "7319004", "7319099", "7020400", "8211300", "4791302"
        ]
    },
    {
        "label": "Tecnologia da Informação (B2B)",
        "category": "B2B",
        "cnaes": [
            "6201500", "6202300", "6203100", "6204000", "6209100",
            "6311900", "6312200", "6201502", "6202301", "6209101"
        ]
    },
    {
        "label": "Publicidade, Marketing e Mídia (B2B)",
        "category": "B2B",
        "cnaes": [
            "7311400", "7319002", "7319003", "7319004", "7319099",
            "7320300", "5811500", "5812301", "5813100", "5911100",
            "5912000", "6010700", "6020400", "6020401"
        ]
    },
    {
        "label": "Gestão, Consultoria e Serviços Administrativos (B2B)",
        "category": "B2B",
        "cnaes": [
            "7020400", "8211300", "7810800", "7820500", "7830200",
            "8299799", "6920601", "6920602", "7010100"
        ]
    },
    {
        "label": "Comércio Eletrônico e Varejo (B2C)",
        "category": "B2C",
        "cnaes": [
            "4791302", "4791303", "4789099", "4711302", "4712100",
            "4721102", "4741300", "4751201", "4752100", "4753100",
            "4754701", "4771701", "4772500", "4781400", "4782201"
        ]
    },
    {
        "label": "Alimentação e Bebidas (B2C)",
        "category": "B2C",
        "cnaes": [
            "5611201", "5611203", "5612100", "5620101", "5630900",
            "4721102", "4722100", "4723700"
        ]
    },
    {
        "label": "Serviços Pessoais, Beleza e Bem-estar (B2C)",
        "category": "B2C",
        "cnaes": [
            "9602501", "9602502", "9609202", "9609299",
            "9601000", "9603300"
        ]
    },
    {
        "label": "Saúde e Serviços Sociais (B2C)",
        "category": "B2C",
        "cnaes": [
            "8610101", "8610102", "8620800", "8630500", "8640200",
            "8650000", "8710100", "8720900", "8730600", "8800100"
        ]
    },
    {
        "label": "Educação e Treinamento (B2C)",
        "category": "B2C",
        "cnaes": [
            "8511000", "8512800", "8513600", "8520900", "8531400",
            "8532200", "8541100", "8542000", "8550300", "8599600"
        ]
    },
    {
        "label": "Hospedagem, Turismo e Lazer (B2C)",
        "category": "B2C",
        "cnaes": [
            "5510800", "5590600", "7911200", "7912100", "7990900",
            "9001900", "9002700", "9003500", "9101000", "9102800",
            "9103600", "9200100", "9311000", "9312800", "9329000"
        ]
    },
]

# Rate limiting
DELAY_BETWEEN_REQUESTS = 0.5  # seconds
MAX_RETRIES = 3
TIMEOUT = 30
MAX_PAGES_PER_SEARCH = 50  # safety limit

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

# ============ HELPERS ============
def save_progress(progress: Dict, filename: str):
    """Salva progresso incremental."""
    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:
    """Carrega progresso salvo."""
    filepath = DATA_DIR / filename
    if filepath.exists():
        with open(filepath, "r", encoding="utf-8") as f:
            return json.load(f)
    return {"processed": [], "companies": {}, "last_update": None}

def minha_receita_search(uf: str, cnae: str, municipio: str = None, cursor: str = None, limit: int = 100) -> Optional[Dict]:
    """Busca empresas na Minha Receita API."""
    params = {"uf": uf, "cnae": cnae, "limit": limit}
    if municipio:
        params["municipio"] = municipio
    if cursor:
        params["cursor"] = cursor
    
    url = f"{MINHA_RECEITA_API}/"
    
    for attempt in range(MAX_RETRIES):
        try:
            resp = session.get(url, params=params, timeout=TIMEOUT)
            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 < MAX_RETRIES - 1:
            time.sleep(2 ** attempt)
    
    return None

def search_all_pages(uf: str, cnae: str, municipio: str = None, max_pages: int = MAX_PAGES_PER_SEARCH) -> List[Dict]:
    """Busca todas as páginas de resultados para uma combinação UF+CNAE."""
    all_companies = []
    cursor = None
    page_count = 0
    
    while page_count < max_pages:
        result = minha_receita_search(uf, cnae, municipio, cursor)
        
        if not result:
            break
        
        companies = result.get("data", [])
        all_companies.extend(companies)
        page_count += 1
        
        cursor = result.get("cursor")
        if not cursor or not companies:
            break
        
        time.sleep(DELAY_BETWEEN_REQUESTS)
    
    return all_companies

def enrich_company_data(company: Dict, group_info: Dict) -> Dict:
    """Enriquece dados da empresa com campos padrão do dashboard."""
    cnpj = company.get("cnpj", "").replace(".", "").replace("/", "").replace("-", "")
    cnpj_fmt = f"{cnpj[:2]}.{cnpj[2:5]}.{cnpj[5:8]}/{cnpj[8:12]}-{cnpj[12:]}" if len(cnpj) == 14 else company.get("cnpj", "")
    
    # Formatar telefones
    phones = []
    for i in [1, 2]:
        ddd = company.get(f"ddd_telefone_{i}")
        if ddd and str(ddd).strip():
            phones.append(f"({ddd}) {company.get(f'telefone_{i}', '')}")
    fax_ddd = company.get("ddd_fax")
    if fax_ddd and str(fax_ddd).strip():
        phones.append(f"({fax_ddd}) {company.get('fax', '')} (Fax)")
    
    # Sócios
    socios = company.get("qsa", [])
    socios_names = [s.get("nome_socio", "") for s in socios if s.get("nome_socio")]
    
    # Endereço completo
    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"])
    endereco_completo = ", ".join(endereco_parts)
    if company.get("bairro"):
        endereco_completo += f", {company['bairro']}"
    if company.get("municipio") and company.get("uf"):
        endereco_completo += f", {company['municipio']}/{company['uf']}"
    if company.get("cep"):
        cep = str(company["cep"]).zfill(8)
        endereco_completo += f" - CEP {cep[:5]}-{cep[5:]}"
    
    # Capital social
    capital = company.get("capital_social", 0)
    try:
        capital_val = float(str(capital).replace(",", "."))
    except:
        capital_val = 0
    
    # 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"))
    
    # 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", ""))
    
    # CNAE principal
    cnae_codigo = company.get("cnae_fiscal", "")
    cnae_desc = company.get("cnae_fiscal_descricao", "")
    
    # Natureza jurídica
    natureza = company.get("natureza_juridica", "")
    
    # Simples
    simples = company.get("opcao_pelo_simples", "")
    simples_str = "Sim" if simples == "true" or simples == "S" else "Não"
    
    # Email (Minha Receita não retorna email)
    email = ""
    
    # Data abertura
    data_abertura = company.get("data_inicio_atividade", "")
    
    return {
        "cnpj": cnpj_fmt,
        "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_codigo,
        "cnae_desc": cnae_desc,
        "situacao": situacao,
        "porte": porte,
        "capital_social": capital_val,
        "data_abertura": data_abertura,
        "tipo_prospeccao": group_info["category"],
        "b2c_categoria": group_info["label"] if group_info["category"] == "B2C" else "N/A",
        "best_fit_service": map_cnae_to_service(cnae_codigo),
        "fit_score": calculate_fit_score(cnae_codigo, group_info["category"]),
        "lelyon_fit": calculate_lelyon_fit(cnae_codigo),
        "email": email,
        "telefone": phones[0] if phones else "",
        "whatsapp": "",
        "website": "",
        "website_status": "pending",
        "contato_nome": socios_names[0] if socios_names else "",
        "contato_cargo": socios[0].get("qualificacao_socio", "") if socios else "",
        "linkedin": "",
        "instagram": "",
        "facebook": "",
        "fonte_enriquecimento": "minha_receita",
        "data_enriquecimento": datetime.now().isoformat(),
        "observacoes": f"Fonte: Minha Receita API | Grupo: {group_info['label']} | UF: SP",
        # Campos específicos B2C
        "tipo_estabelecimento": "",
        "tipo_codigo": "",
        "gestao": "",
        "gestao_codigo": "",
        "endereco_completo": endereco_completo,
        "latitude": "",
        "longitude": "",
        "especialidades": "",
        "especialidades_codigos": "",
        "leitos_total": 0,
        "leitos_sus": 0,
        "leitos_uti": 0,
        "data_atualizacao_cnes": "",
        # Minha Receita specific
        "natureza_juridica": natureza,
        "simples_nacional": simples_str,
        "data_situacao": company.get("data_situacao_cadastral", ""),
        "motivo_situacao": company.get("descricao_motivo_situacao_cadastral", ""),
        "cnaes_secundarios": ", ".join([str(c.get("codigo", "")) for c in company.get("cnaes_secundarios", []) if c.get("codigo")]),
        "qsa": socios_names,
    }

def map_cnae_to_service(cnae: str) -> str:
    """Mapeia CNAE para serviço LELYON."""
    cnae_service_map = {
        # TI / Desenvolvimento
        "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",
        # E-commerce
        "4791302": "Loja WordPress/WooCommerce",
        "4791303": "Loja WordPress/WooCommerce",
        "4789099": "Loja WordPress/WooCommerce",
        # CRM / Gestão
        "8211300": "CRM & Estoque QR-Code",
        "8299799": "CRM & Estoque QR-Code",
        "7020400": "CRM & Estoque QR-Code",
        # Marketing
        "7311400": "Marketing Digital/Ads",
        "7319002": "Marketing Digital/Ads",
        "7319003": "Marketing Digital/Ads",
        "7319004": "Marketing Digital/Ads",
        "7319099": "Marketing Digital/Ads",
        "7320300": "Marketing Digital/Ads",
        # Segurança
        "6209100": "Segurança + SEO",
    }
    return cnae_service_map.get(cnae, "N/A")

def calculate_fit_score(cnae: str, category: str) -> int:
    """Calcula score de fit baseado no CNAE e categoria."""
    if category == "B2B":
        high_fit = ["6201501", "6201502", "6202300", "6203100", "6204000", 
                    "6209100", "6311900", "4791302", "7020400", "7311400"]
        medium_fit = ["6201500", "6202300", "6203100", "6311900", "6312200",
                      "8211300", "8299799", "7311400", "7319002", "7319003"]
        if cnae in high_fit:
            return 90
        elif cnae in medium_fit:
            return 70
        else:
            return 40
    else:  # B2C
        high_fit = ["4791302", "5611201", "5611203", "9602501", "9602502",
                    "4781400", "4771701", "4772500", "4711302"]
        medium_fit = ["4712100", "4721102", "4741300", "4751201", "4752100",
                      "4753100", "4754701", "4782201", "4783100", "5620101"]
        if cnae in high_fit:
            return 85
        elif cnae in medium_fit:
            return 60
        else:
            return 35

def calculate_lelyon_fit(cnae: str) -> Dict:
    """Calcula fit detalhado para cada serviço 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
    }
    
    # Mapeamento CNAE -> scores
    cnae_fits = {
        "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},
        # B2C
        "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 cnae_fits.get(cnae, fits)

# ============ MAIN ============
def main():
    print("=" * 70)
    print("COLETA DE DADOS REAIS - LELYON Prospecting")
    print("Fonte: Minha Receita API (minhareceita.org)")
    print("=" * 70)
    print(f"Cidades alvo: {len(CITIES_IBGE)} (SP + Grande ABC)")
    print(f"Grupos CNAE: {len(CNAE_GROUPS)}")
    print()
    
    # Carregar progresso
    progress = load_progress("collection_progress.json")
    processed_keys = set(progress.get("processed", []))
    all_companies = progress.get("companies", {})
    
    print(f"Progresso anterior: {len(processed_keys)} buscas, {len(all_companies)} empresas")
    print()
    
    total_new = 0
    
    try:
        for group in CNAE_GROUPS:
            category = group["category"]
            group_label = group["label"]
            cnaes = group["cnaes"]
            
            print(f"\n{'='*60}")
            print(f"Grupo: {group_label} ({category})")
            print(f"CNAEs: {len(cnaes)}")
            print(f"{'='*60}")
            
            for cnae in cnaes:
                for cidade_code, cidade_nome in CITIES_IBGE.items():
                    key = f"{cnae}_{cidade_code}"
                    
                    if key in processed_keys:
                        continue
                    
                    print(f"\n🔍 [{cnae}] {cidade_nome}...")
                    
                    companies = search_all_pages("SP", cnae, cidade_code)
                    
                    if companies:
                        print(f"   ✅ {len(companies)} empresas encontradas")
                        for company in companies:
                            enriched = enrich_company_data(company, group)
                            cnpj_key = enriched["cnpj"].replace(".", "").replace("/", "").replace("-", "")
                            all_companies[cnpj_key] = enriched
                        total_new += len(companies)
                    else:
                        print(f"   ⭕ Nenhuma empresa")
                    
                    processed_keys.add(key)
                    
                    # Salvar progresso a cada 10 buscas
                    if len(processed_keys) % 10 == 0:
                        save_progress({
                            "processed": list(processed_keys),
                            "companies": all_companies,
                            "last_update": datetime.now().isoformat()
                        }, "collection_progress.json")
                    
                    time.sleep(DELAY_BETWEEN_REQUESTS)
        
        # Separar B2B e B2C
        b2b_companies = [c for c in all_companies.values() if c["tipo_prospeccao"] == "B2B"]
        b2c_companies = [c for c in all_companies.values() if c["tipo_prospeccao"] == "B2C"]
        
        print(f"\n{'='*60}")
        print(f"COLETA CONCLUÍDA!")
        print(f"Total empresas: {len(all_companies)}")
        print(f"  B2B: {len(b2b_companies)}")
        print(f"  B2C: {len(b2c_companies)}")
        print(f"  Novas nesta execução: {total_new}")
        
        # Salvar resultados finais
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        
        # JSON combinado
        combined = {"b2b": b2b_companies, "b2c": b2c_companies}
        json_file = DATA_DIR / f"lelyon_prospects_{timestamp}.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_prospects_{timestamp}.csv"
        if all_companies:
            fieldnames = list(all_companies[next(iter(all_companies))].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.values():
                    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 com abas
        excel_file = DATA_DIR / f"lelyon_prospects_{timestamp}.xlsx"
        with pd.ExcelWriter(excel_file, engine="openpyxl") as writer:
            df_all = pd.DataFrame(list(all_companies.values()))
            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)
            
            # Resumo por grupo
            group_stats = []
            for group in CNAE_GROUPS:
                cat = group["category"]
                count = len([c for c in all_companies.values() if c["tipo_prospeccao"] == cat and c.get("observacoes", "").find(group["label"]) >= 0])
                group_stats.append({"Grupo": group["label"], "Categoria": cat, "Empresas": count})
            
            pd.DataFrame(group_stats).to_excel(writer, sheet_name="Resumo_Grupos", index=False)
        
        # Atualizar symlinks latest
        for src, dst_name in [
            (json_file, "lelyon_prospects_latest.json"),
            (csv_file, "lelyon_prospects_latest.csv"),
            (excel_file, "lelyon_prospects_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 atualizados para *_latest.*")
        
        # Limpar progresso
        (DATA_DIR / "collection_progress.json").unlink(missing_ok=True)
        
    except KeyboardInterrupt:
        print("\n\n⏸️ Interrompido pelo usuário. Progresso salvo.")
        save_progress({
            "processed": list(processed_keys),
            "companies": all_companies,
            "last_update": datetime.now().isoformat()
        }, "collection_progress.json")

if __name__ == "__main__":
    main()