{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Matriz de similaridade:\n",
      "[[0.48120117 0.33854263]\n",
      " [0.         0.22133323]]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[nltk_data] Downloading package stopwords to\n",
      "[nltk_data]     /home/anaconda/nltk_data...\n",
      "[nltk_data]   Package stopwords is already up-to-date!\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "incomplete input (3202424487.py, line 180)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Cell \u001b[0;32mIn[1], line 180\u001b[0;36m\u001b[0m\n\u001b[0;31m    processar_categorias(cursor, categorias_subcategorias,\u001b[0m\n\u001b[0m                                                          ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m incomplete input\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import nltk\n",
    "import re\n",
    "from nltk.corpus import stopwords\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "import numpy as np\n",
    "import mysql.connector\n",
    "import time\n",
    "from nltk.stem import RSLPStemmer\n",
    "\n",
    "# Baixar recursos necessários do NLTK\n",
    "nltk.download('stopwords')\n",
    "nltk.download('rslp')\n",
    "stop_words = set(stopwords.words('portuguese'))\n",
    "stemmer = RSLPStemmer()\n",
    "\n",
    "# Função para conectar ao banco de dados\n",
    "def conectar_banco():\n",
    "    try:\n",
    "        conexao = mysql.connector.connect(\n",
    "            host=\"seu_host\",\n",
    "            user=\"seu_usuario\",\n",
    "            password=\"sua_senha\",\n",
    "            database=\"seu_banco_de_dados\"\n",
    "        )\n",
    "        return conexao\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao conectar ao banco de dados: {e}\")\n",
    "        raise\n",
    "\n",
    "# Função para coletar links do site\n",
    "def coletar_links(site):\n",
    "    try:\n",
    "        resposta = requests.get(site, timeout=30)\n",
    "        soup = BeautifulSoup(resposta.content, 'html.parser')\n",
    "        links = []\n",
    "        for link in soup.find_all('a', href=True):\n",
    "            url = link['href']\n",
    "            if url.startswith('http'):\n",
    "                links.append(url)\n",
    "            elif url.startswith('/'):\n",
    "                # Tratar links relativos\n",
    "                base_url = '/'.join(site.split('/')[:3])\n",
    "                links.append(base_url + url)\n",
    "        return links\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao coletar links de {site}: {e}\")\n",
    "        return []\n",
    "\n",
    "# Função para extrair frases da página HTML\n",
    "def extrair_frases(site):\n",
    "    try:\n",
    "        resposta = requests.get(site, timeout=30)\n",
    "        soup = BeautifulSoup(resposta.content, 'html.parser')\n",
    "        # Remover scripts e estilos\n",
    "        for script in soup([\"script\", \"style\"]):\n",
    "            script.decompose()\n",
    "        texto = soup.get_text()\n",
    "        # Dividir em frases\n",
    "        frases = re.split(r'[.!?]+', texto)\n",
    "        return [frase.strip() for frase in frases if len(frase.strip()) > 10]\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao extrair frases de {site}: {e}\")\n",
    "        return []\n",
    "\n",
    "# Função para remover caracteres especiais e stopwords\n",
    "def limpar_frase(frase):\n",
    "    # Remover caracteres especiais\n",
    "    frase = re.sub(r'[^\\w\\s]', '', frase.lower())\n",
    "    # Remover stopwords\n",
    "    palavras = frase.split()\n",
    "    frase_limpa = ' '.join([palavra for palavra in palavras if palavra not in stop_words])\n",
    "    return frase_limpa\n",
    "\n",
    "# Função para limpar e lematizar frases\n",
    "def limpar_e_lematizar_frases(frases):\n",
    "    frases_limpas = []\n",
    "    for frase in frases:\n",
    "        # Limpar a frase\n",
    "        frase_limpa = limpar_frase(frase)\n",
    "        # Aplicar stemming\n",
    "        palavras = frase_limpa.split()\n",
    "        palavras_stemizadas = [stemmer.stem(palavra) for palavra in palavras]\n",
    "        frase_stemizada = ' '.join(palavras_stemizadas)\n",
    "        frases_limpas.append(frase_stemizada)\n",
    "    return frases_limpas\n",
    "\n",
    "# Função para contar a similaridade de frases usando TF-IDF e cosine similarity\n",
    "def contar_similaridade(frases_html, frases_categoria):\n",
    "    # Verificar se há frases para processar\n",
    "    if not frases_html or not frases_categoria:\n",
    "        return np.zeros((max(1, len(frases_html)), max(1, len(frases_categoria))))\n",
    "    \n",
    "    # Limpar as frases\n",
    "    frases_html_limpas = [f for f in frases_html if f.strip()]\n",
    "    frases_categoria_limpas = [f for f in frases_categoria if f.strip()]\n",
    "    \n",
    "    if not frases_html_limpas or not frases_categoria_limpas:\n",
    "        return np.zeros((max(1, len(frases_html)), max(1, len(frases_categoria))))\n",
    "    \n",
    "    # Combinar todas as frases para criar o vocabulário\n",
    "    todas_frases = frases_html_limpas + frases_categoria_limpas\n",
    "    \n",
    "    try:\n",
    "        # Criar vetores TF-IDF\n",
    "        vectorizer = TfidfVectorizer()\n",
    "        tfidf_matrix = vectorizer.fit_transform(todas_frases)\n",
    "        \n",
    "        # Separar os vetores das frases HTML e das categorias\n",
    "        tfidf_html = tfidf_matrix[:len(frases_html_limpas)]\n",
    "        tfidf_categoria = tfidf_matrix[len(frases_html_limpas):]\n",
    "        \n",
    "        # Calcular similaridade de cosseno\n",
    "        similaridade = cosine_similarity(tfidf_html, tfidf_categoria)\n",
    "        \n",
    "        return similaridade\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao calcular similaridade: {e}\")\n",
    "        return np.zeros((len(frases_html), len(frases_categoria)))\n",
    "\n",
    "def main():\n",
    "    try:\n",
    "        # Estabelecer conexão com o banco\n",
    "        conexao = conectar_banco()\n",
    "        print(\"Conexão com o banco estabelecida com sucesso!\")\n",
    "        cursor = conexao.cursor()\n",
    "\n",
    "        # Executar SELECT para obter os sites principais\n",
    "        cursor.execute('''\n",
    "            SELECT uf, municipio, site\n",
    "            FROM tb_municipio\n",
    "            WHERE processado = 'N'\n",
    "            AND uf IN ('RR','RS','SC','SE','SP','TO')\n",
    "            ORDER BY uf, municipio;\n",
    "        ''')\n",
    "        sites_principais = cursor.fetchall()\n",
    "        print(f\"Sites principais carregados: {len(sites_principais)} encontrados.\")\n",
    "\n",
    "        # Validar se existem sites a processar\n",
    "        if not sites_principais:\n",
    "            print(\"Nenhum site principal a processar. Encerrando...\")\n",
    "            return\n",
    "\n",
    "        # Executar SELECT para obter categorias e subcategorias\n",
    "        cursor.execute('''\n",
    "            SELECT a.cod_categoria, b.cod_sub_categoria,\n",
    "                   a.descricao, b.descricao, b.frase\n",
    "            FROM tb_categoria a\n",
    "            JOIN tb_sub_categoria b ON a.cod_categoria = b.cod_categoria\n",
    "            ORDER BY a.cod_categoria, b.cod_sub_categoria;\n",
    "        ''')\n",
    "        resultados = cursor.fetchall()\n",
    "        print(f\"Categorias e subcategorias carregadas: {len(resultados)}.\")\n",
    "\n",
    "        # Validar se existem categorias/subcategorias\n",
    "        if not resultados:\n",
    "            print(\"Nenhuma categoria ou subcategoria encontrada. Encerrando...\")\n",
    "            return\n",
    "\n",
    "        # Organizar dados no formato desejado\n",
    "        categorias_subcategorias = organizar_categorias(resultados)\n",
    "        print(\"Categorias organizadas com sucesso.\")\n",
    "\n",
    "        # Iterar sobre os sites principais\n",
    "        for uf, municipio, site_principal in sites_principais:\n",
    "            print(f\"Processando {uf} - {municipio} - {site_principal}\")\n",
    "            links = coletar_links(site_principal)\n",
    "            if not links:\n",
    "                print(f\"Nenhum link encontrado para {site_principal}.\")\n",
    "                continue\n",
    "\n",
    "            for link in links:\n",
    "                try:\n",
    "                    frases_html = extrair_frases(link)\n",
    "                    frases_html_limpas = limpar_e_lematizar_frases(frases_html)\n",
    "\n",
    "                    # Processar categorias e armazenar resultados\n",
    "                    processar_categorias(cursor, categorias_subcategorias,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "incomplete input (3202424487.py, line 180)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Cell \u001b[0;32mIn[2], line 180\u001b[0;36m\u001b[0m\n\u001b[0;31m    processar_categorias(cursor, categorias_subcategorias,\u001b[0m\n\u001b[0m                                                          ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m incomplete input\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import nltk\n",
    "import re\n",
    "from nltk.corpus import stopwords\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "from sklearn.metrics.pairwise import cosine_similarity\n",
    "import numpy as np\n",
    "import mysql.connector\n",
    "import time\n",
    "from nltk.stem import RSLPStemmer\n",
    "\n",
    "# Baixar recursos necessários do NLTK\n",
    "nltk.download('stopwords')\n",
    "nltk.download('rslp')\n",
    "stop_words = set(stopwords.words('portuguese'))\n",
    "stemmer = RSLPStemmer()\n",
    "\n",
    "# Função para conectar ao banco de dados\n",
    "def conectar_banco():\n",
    "    try:\n",
    "        conexao = mysql.connector.connect(\n",
    "            host=\"seu_host\",\n",
    "            user=\"seu_usuario\",\n",
    "            password=\"sua_senha\",\n",
    "            database=\"seu_banco_de_dados\"\n",
    "        )\n",
    "        return conexao\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao conectar ao banco de dados: {e}\")\n",
    "        raise\n",
    "\n",
    "# Função para coletar links do site\n",
    "def coletar_links(site):\n",
    "    try:\n",
    "        resposta = requests.get(site, timeout=30)\n",
    "        soup = BeautifulSoup(resposta.content, 'html.parser')\n",
    "        links = []\n",
    "        for link in soup.find_all('a', href=True):\n",
    "            url = link['href']\n",
    "            if url.startswith('http'):\n",
    "                links.append(url)\n",
    "            elif url.startswith('/'):\n",
    "                # Tratar links relativos\n",
    "                base_url = '/'.join(site.split('/')[:3])\n",
    "                links.append(base_url + url)\n",
    "        return links\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao coletar links de {site}: {e}\")\n",
    "        return []\n",
    "\n",
    "# Função para extrair frases da página HTML\n",
    "def extrair_frases(site):\n",
    "    try:\n",
    "        resposta = requests.get(site, timeout=30)\n",
    "        soup = BeautifulSoup(resposta.content, 'html.parser')\n",
    "        # Remover scripts e estilos\n",
    "        for script in soup([\"script\", \"style\"]):\n",
    "            script.decompose()\n",
    "        texto = soup.get_text()\n",
    "        # Dividir em frases\n",
    "        frases = re.split(r'[.!?]+', texto)\n",
    "        return [frase.strip() for frase in frases if len(frase.strip()) > 10]\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao extrair frases de {site}: {e}\")\n",
    "        return []\n",
    "\n",
    "# Função para remover caracteres especiais e stopwords\n",
    "def limpar_frase(frase):\n",
    "    # Remover caracteres especiais\n",
    "    frase = re.sub(r'[^\\w\\s]', '', frase.lower())\n",
    "    # Remover stopwords\n",
    "    palavras = frase.split()\n",
    "    frase_limpa = ' '.join([palavra for palavra in palavras if palavra not in stop_words])\n",
    "    return frase_limpa\n",
    "\n",
    "# Função para limpar e lematizar frases\n",
    "def limpar_e_lematizar_frases(frases):\n",
    "    frases_limpas = []\n",
    "    for frase in frases:\n",
    "        # Limpar a frase\n",
    "        frase_limpa = limpar_frase(frase)\n",
    "        # Aplicar stemming\n",
    "        palavras = frase_limpa.split()\n",
    "        palavras_stemizadas = [stemmer.stem(palavra) for palavra in palavras]\n",
    "        frase_stemizada = ' '.join(palavras_stemizadas)\n",
    "        frases_limpas.append(frase_stemizada)\n",
    "    return frases_limpas\n",
    "\n",
    "# Função para contar a similaridade de frases usando TF-IDF e cosine similarity\n",
    "def contar_similaridade(frases_html, frases_categoria):\n",
    "    # Verificar se há frases para processar\n",
    "    if not frases_html or not frases_categoria:\n",
    "        return np.zeros((max(1, len(frases_html)), max(1, len(frases_categoria))))\n",
    "    \n",
    "    # Limpar as frases\n",
    "    frases_html_limpas = [f for f in frases_html if f.strip()]\n",
    "    frases_categoria_limpas = [f for f in frases_categoria if f.strip()]\n",
    "    \n",
    "    if not frases_html_limpas or not frases_categoria_limpas:\n",
    "        return np.zeros((max(1, len(frases_html)), max(1, len(frases_categoria))))\n",
    "    \n",
    "    # Combinar todas as frases para criar o vocabulário\n",
    "    todas_frases = frases_html_limpas + frases_categoria_limpas\n",
    "    \n",
    "    try:\n",
    "        # Criar vetores TF-IDF\n",
    "        vectorizer = TfidfVectorizer()\n",
    "        tfidf_matrix = vectorizer.fit_transform(todas_frases)\n",
    "        \n",
    "        # Separar os vetores das frases HTML e das categorias\n",
    "        tfidf_html = tfidf_matrix[:len(frases_html_limpas)]\n",
    "        tfidf_categoria = tfidf_matrix[len(frases_html_limpas):]\n",
    "        \n",
    "        # Calcular similaridade de cosseno\n",
    "        similaridade = cosine_similarity(tfidf_html, tfidf_categoria)\n",
    "        \n",
    "        return similaridade\n",
    "    except Exception as e:\n",
    "        print(f\"Erro ao calcular similaridade: {e}\")\n",
    "        return np.zeros((len(frases_html), len(frases_categoria)))\n",
    "\n",
    "def main():\n",
    "    try:\n",
    "        # Estabelecer conexão com o banco\n",
    "        conexao = conectar_banco()\n",
    "        print(\"Conexão com o banco estabelecida com sucesso!\")\n",
    "        cursor = conexao.cursor()\n",
    "\n",
    "        # Executar SELECT para obter os sites principais\n",
    "        cursor.execute('''\n",
    "            SELECT uf, municipio, site\n",
    "            FROM tb_municipio\n",
    "            WHERE processado = 'N'\n",
    "            AND uf IN ('RR','RS','SC','SE','SP','TO')\n",
    "            ORDER BY uf, municipio;\n",
    "        ''')\n",
    "        sites_principais = cursor.fetchall()\n",
    "        print(f\"Sites principais carregados: {len(sites_principais)} encontrados.\")\n",
    "\n",
    "        # Validar se existem sites a processar\n",
    "        if not sites_principais:\n",
    "            print(\"Nenhum site principal a processar. Encerrando...\")\n",
    "            return\n",
    "\n",
    "        # Executar SELECT para obter categorias e subcategorias\n",
    "        cursor.execute('''\n",
    "            SELECT a.cod_categoria, b.cod_sub_categoria,\n",
    "                   a.descricao, b.descricao, b.frase\n",
    "            FROM tb_categoria a\n",
    "            JOIN tb_sub_categoria b ON a.cod_categoria = b.cod_categoria\n",
    "            ORDER BY a.cod_categoria, b.cod_sub_categoria;\n",
    "        ''')\n",
    "        resultados = cursor.fetchall()\n",
    "        print(f\"Categorias e subcategorias carregadas: {len(resultados)}.\")\n",
    "\n",
    "        # Validar se existem categorias/subcategorias\n",
    "        if not resultados:\n",
    "            print(\"Nenhuma categoria ou subcategoria encontrada. Encerrando...\")\n",
    "            return\n",
    "\n",
    "        # Organizar dados no formato desejado\n",
    "        categorias_subcategorias = organizar_categorias(resultados)\n",
    "        print(\"Categorias organizadas com sucesso.\")\n",
    "\n",
    "        # Iterar sobre os sites principais\n",
    "        for uf, municipio, site_principal in sites_principais:\n",
    "            print(f\"Processando {uf} - {municipio} - {site_principal}\")\n",
    "            links = coletar_links(site_principal)\n",
    "            if not links:\n",
    "                print(f\"Nenhum link encontrado para {site_principal}.\")\n",
    "                continue\n",
    "\n",
    "            for link in links:\n",
    "                try:\n",
    "                    frases_html = extrair_frases(link)\n",
    "                    frases_html_limpas = limpar_e_lematizar_frases(frases_html)\n",
    "\n",
    "                    # Processar categorias e armazenar resultados\n",
    "                    processar_categorias(cursor, categorias_subcategorias,"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:base] *",
   "language": "python",
   "name": "conda-base-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}