import mysql.connector
from mysql.connector import Error
import requests
from bs4 import BeautifulSoup
from urllib.parse import urljoin
import re

def scrape_website(url):
    contact_page_url, events_page_url, about_us_page_url = "Not found", "Not found", "Not found"
    try:
        response = requests.get(url, timeout=10)
        if response.status_code == 200:
            soup = BeautifulSoup(response.text, 'html.parser')

            # Attempt to scrape Contact, Event, and About Us page URLs
            for link in soup.find_all('a', href=True):
                href = link.get('href')
                text = link.text.lower()
                if 'contact' in text and contact_page_url == "Not found":
                    contact_page_url = href if href.startswith('http') else urljoin(url, href)
                elif 'event' in text and events_page_url == "Not found":
                    events_page_url = href if href.startswith('http') else urljoin(url, href)
                elif 'about' in text and about_us_page_url == "Not found":
                    about_us_page_url = href if href.startswith('http') else urljoin(url, href)

    except Exception as e:
        print(f"An error occurred while trying to scrape {url}: {e}")
    return contact_page_url, events_page_url, about_us_page_url

def connect_and_query():
    try:
        conn = mysql.connector.connect(
            host='localhost',
            user='OG07ZTQ8VS_ulocalu',
            password='14c1_Kc6d',
            database='admin_dev_JL3J2UYOQA_ulocalu_E4UHKC'
        )
        cursor = conn.cursor()

        select_query = "SELECT id, url FROM site_submission WHERE scraped_url != 1"
        update_query = """
            UPDATE site_submission
            SET contact_url = %s, event_url = %s, about_us_url = %s, scraped_url = 1
            WHERE id = %s
        """

        cursor.execute(select_query)
        records = cursor.fetchall()

        for row in records:
            id, url = row
            contact_page_url, events_page_url, about_us_page_url = scrape_website(url)

            # Update the database with the scraped URLs
            cursor.execute(update_query, (contact_page_url, events_page_url, about_us_page_url, id))

        conn.commit()
        print(f"{cursor.rowcount} rows have been updated.")

    except Error as e:
        print(f"Error connecting to MySQL Platform: {e}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
            print("MySQL connection is closed")

if __name__ == "__main__":
    connect_and_query()