import pandas as pd
import requests
from requests.auth import HTTPBasicAuth
import re
import os

# Define the API endpoint and credentials
api_url = 'https://eshop.livets-spejl.dk/wp-json/wc/v3/products'
username = 'ck_06ad70e1a4aa4a1c39bb95d07e247bc29cc357f6'
password = 'cs_ff563757b8d392f34d4df7aa9351987acb52ab03'
excel_file = 'ls-import.xlsx'
image_base_url = 'https://eshop.livets-spejl.dk/wp-content/uploads/2024/11/'

# Function to remove HTML tags
def remove_html_tags(text):
    clean = re.compile('<.*?>')
    return re.sub(clean, '', text)

# Open the Excel file
df = pd.read_excel(excel_file)

# Iterate over each row in the DataFrame
for index, row in df.iterrows():
    # Prepare the data for the POST request using column names and row values
    data = {col: remove_html_tags(str(row[col])) for col in df.columns}
    
    # Fix the format for the images field and add the base URL to the image URL
    if 'image_name' in df.columns:
        image_filename = os.path.basename(data['image_name'])
        data['images'] = [{'src': image_base_url + image_filename}]
        del data['image_name']
    
    # Add the type field
    data['type'] = 'simple'
    
    print(data)
    # Make the POST request with Basic Authentication
    response = requests.post(api_url, json=data, auth=HTTPBasicAuth(username, password))
    # Print the response from the server
    print(response.text)