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, event_page_url = "Not found", "Not found"
    try:
        response = requests.get(url, timeout=10)
        if response.status_code == 200:
            soup = BeautifulSoup(response.text, 'html.parser')

            # Find Contact Page URL
            for link in soup.find_all('a', href=True, text=re.compile(r'Contact|Contact Us', re.IGNORECASE)):
                contact_href = link.get('href')
                # Check if the href is a full URL or a relative path
                if contact_href.startswith('http'):
                    contact_page_url = contact_href
                else:
                    # Build a full URL if it's a relative path
                    from urllib.parse import urljoin
                    contact_page_url = urljoin(url, contact_href)
                break  # Assuming only one contact page link is needed

            # Find Event Page URL
            for link in soup.find_all('a', href=True, text=re.compile(r'Event|Events', re.IGNORECASE)):
                events_href = link.get('href')
                # Check if the href is a full URL or a relative path
                if events_href.startswith('http'):
                    events_page_url = events_href
                else:
                    # Build a full URL if it's a relative path
                    from urllib.parse import urljoin
                    events_page_url = urljoin(url, events_href)
                break  # Assuming only one contact page link is needed

            # Find About Us Page URL
            for link in soup.find_all('a', href=True, text=re.compile(r'About Us|About', re.IGNORECASE)):
                href = link.get('href')
                about_us_page_url = href if href.startswith('http') else urljoin(url, href)
                break

    except Exception as e:
        print(f"An error occurred while trying to scrape {url}: {e}")
    return contact_page_url, event_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 != 1"
        update_query = """
            UPDATE site_submission
            SET contact_url = %s, event_url = %s, scraped = 1
            WHERE id = %s
        """

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

        for row in records:
            id, url = row
            contact_page_url, events_page_url = scrape_website(url)
            
            cursor.execute(update_query, (contact_page_url, events_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()