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

# Define configuration variables
api_url = 'https://eshop.livets-spejl.dk/wp-json/wc/v3/products'
username = 'ck_06ad70e1a4aa4a1c39bb95d07e247bc29cc357f6'
password = 'cs_ff563757b8d392f34d4df7aa9351987acb52ab03'
#image_base_url = 'https://eshop.livets-spejl.dk/wp-content/uploads/'
image_base_url  = 'https://livets-spejl.dk/image/'
excel_file = 'ls-exports.xlsx'

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

# Map the required columns to the WooCommerce API fields
column_mapping = {
    'name(da-dk)': 'name',
    'product_id': 'sku',
    'description(da-dk)': 'description',
    'description(da-dk)': 'short_description',
    'price': 'regular_price',
    'quantity': 'stock_quantity',
    'image_name': 'images',
    'weight': 'weight',
    'length': 'length',
    'width': 'width',
    'height': 'height'
}

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

# Validate if all required columns exist
for col in column_mapping.keys():
    if col not in df.columns:
        print(f"Kolonnen '{col}' findes ikke i Excel-filen.")
        exit()

# 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 = {api_field: remove_html_tags(str(row[excel_col])) for excel_col, api_field in column_mapping.items()}
    
    # Fix the format for the images field and add the base URL to the image URL
    if 'images' in data:
        #image_filename = os.path.basename(data['images'])
        image_filename = data['images']
        data['images'] = [{'src': image_base_url + image_filename}]


    # Fix the format for the dimensions
    if 'length' in data:
        data['dimensions'] = {
            'length': data['length'],
            'width':  data['width'],
            'height': data['height']
        }

    # Add standard values for the WooCommerce product
    data['type'] = 'simple'
    data['tax_status'] = 'none'
    data['status'] = 'publish'
    data['catalog_visibility'] = 'visible'
    data['manage_stock'] = True
    data['backorders'] = 'notify'
    data['stock_status'] = 'instock'

    # Check if the product already exists
    response = requests.get(api_url, params={'sku': data['sku']}, auth=HTTPBasicAuth(username, password))
    existing_products = response.json()
    
    if existing_products:
        # Update the existing product
        product_id = existing_products[0]['id']
        update_url = f"{api_url}/{product_id}"
        update_response = requests.put(update_url, json=data, auth=HTTPBasicAuth(username, password))
        print(f"Updated product {product_id}: {update_response.status_code}")
    else:
        # Make the POST request with Basic Authentication
        response = requests.post(api_url, json=data, auth=HTTPBasicAuth(username, password))
        print(f"Created product: {response.status_code}")
    
    # Print the response from the server
    print(response.text)