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


db_config = {
    "host": "10.8.0.1",
    "user": "integration",
    "password": "?Q8/{lVK2N08Y<b>k",
    "database": "Salsify"
}

connection = mysql.connector.connect(**db_config)
cursor = connection.cursor()

query = """
SELECT Salsify.MainData.SKU, Salsify.MainData.`Ferguson Item #`
FROM Salsify.MainData
WHERE Salsify.MainData.Status IN ("Active", "Liquidation") AND Salsify.MainData.`Ferguson Item #` IS NOT NULL LIMIT 30
"""
cursor.execute(query)
sku_item_list = cursor.fetchall()  
# sku_item_list = [
#     ("VG02001CH", "8737851")
# ]
base_url = "https://www.ferguson.com/product/"

def generate_url(sku, item_number):
    return f"https://www.ferguson.com/product/V{sku}/{item_number}.html?isSearchRedirect=true&searchTerm={item_number}"

def fetch_specifications(url):
    try:
        response = requests.get(url, headers={"User-Agent": "Mozilla/5.0"})
        if response.status_code == 200:
            soup = BeautifulSoup(response.text, "html.parser")
            spec_table = soup.find('div', class_='col-12 product-specification-table')
            if not spec_table:
                return None
            specs = {}
            spec_rows = spec_table.find_all('dl', class_='c-product-details__specs')
            for row in spec_rows:
                key = row.find('dt', class_='font-weight-bold').text.strip()
                value = row.find('dd', class_='d-inline-block').text.strip()
                specs[key] = value
            return specs
        else:
            return None
    except Exception as e:
        # print(f"Error fetching URL {url}: {e}")
        return None
        
results = []

for sku, item_number in sku_item_list:
    url = generate_url(sku, item_number)
    specifications = fetch_specifications(url)
    result = {
        "SKU": sku, 
        "Specifications": specifications if specifications else None
    }
    results.append(result)


print(json.dumps(results));
# output_file = "output.json"
# with open(output_file, "w") as json_file:
#     json.dump(results, json_file, indent=4)
    
# print(f"JSON data has been saved to {output_file}")

# print(output_file)

cursor.close()
connection.close()