import mysql.connector
import requests
from bs4 import BeautifulSoup
import re

def scrape_website(url):
    try:
        response = requests.get(url)
        if response.status_code == 200:
            soup = BeautifulSoup(response.text, 'html.parser')

            # Scrape the page title
            title = soup.find('title').text

            # Attempt to scrape a phone number
            phone_regex = r'\+?\d[\d -]{8,}\d'
            phone_matches = re.findall(phone_regex, soup.text)
            phone_number = phone_matches[0] if phone_matches else "Not found"

            # Attempt to scrape social media links
            social_media_domains = ['facebook.com', 'twitter.com', 'instagram.com', 'linkedin.com', 'youtube.com']
            social_media_links = []
            for link in soup.find_all('a', href=True):
                href = link['href']
                if any(domain in href for domain in social_media_domains):
                    social_media_links.append(href)

            # Attempt to scrape Hours of Operation
            hours_of_operation = "Not found"
            potential_hours = soup.find_all(text=re.compile(r'Hours|Open', re.IGNORECASE))
            for text in potential_hours:
                parent_text = text.parent.text if text.parent else ""
                if any(day in parent_text for day in ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']):
                    hours_of_operation = parent_text
                    break

            return title, phone_number, social_media_links, hours_of_operation
        else:
            return "Failed to fetch the webpage", "N/A", [], "N/A", "N/A"
    except Exception as e:
        return f"An error occurred: {e}", "N/A", [], "N/A", "N/A"

def connect_and_query():
    conn = None
    try:
        # Connect to your database
        conn = mysql.connector.connect(
            host='localhost',
            user='OG07ZTQ8VS_ulocalu',
            password='14c1_Kc6d',
            database='admin_dev_JL3J2UYOQA_ulocalu_E4UHKC'
        )

        cursor = conn.cursor()

        # SQL query to select records
        query = "SELECT url FROM site_submission WHERE scraped != 1"

        cursor.execute(query)

        # Fetch all records where 'scraped' is not equal to 1
        records = cursor.fetchall()

        # Open a file to write
        with open('output.txt', 'w') as f:
            for row in records:
                url = row[0]
                title, phone_number, social_media_links, hours_of_operation
                f.write(f"URL: {url}, Title: {title}, Phone: {phone_number}, Hours: {hours_of_operation}, Social Media: {social_media_links}\n")

        print("Records have been written to output.txt")

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

if __name__ == "__main__":
    connect_and_query()