import requests
import json
import time
import pandas as pd
from datetime import datetime
import random
import mysql.connector
import os
import atexit

LIST_OF_VGU = 'vgu.csv'
ERROR_NOT_LOADED_URLS = 'Not Available'
RESULT_FILE_NAME = 'url_wf_'
VPN_ENABLED = os.environ.get("WG_ENABLED") == "1"
WG_CONFIG_DIR = os.environ.get("WG_CONFIG_DIR", "/etc/wireguard")
WG_COOLDOWN_SEC = float(os.environ.get("WG_COOLDOWN_SEC", "2.0"))
VPN_ROTATOR = None

try:
    from wg_vpn import WireGuardRotator
except Exception:
    WireGuardRotator = None

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



# Підключення до бази даних
try:
    connection = mysql.connector.connect(**db_config)
    # print("Підключення до бази даних успішне.")
except mysql.connector.Error as e:
    # print(f"Помилка підключення: {e}")
    exit()

query = """
SELECT SL.`Wayfair SKU` FROM Salsify.MainData SL WHERE ((SL.Status)='Active' OR (SL.Status)='Liquidation')AND SL.`Wayfair SKU` IS NOT NULL;
"""

vgu_to_search = []

try:
    # Виконання запиту
    cursor = connection.cursor(dictionary=True)
    cursor.execute(query)
    results = cursor.fetchall()

    # Завантаження даних у DataFrame
    df_input = pd.DataFrame(results)
    df_input['VGU'] = df_input['Wayfair SKU']
    vgu_to_search = list(map(lambda record: record['Wayfair SKU'], results))

finally:
    # Закриття з'єднання
    if connection.is_connected():
        cursor.close()
        connection.close()

def get_pdf_info_json(vgu: str):
    URL = "https://www.wayfair.com/graphql?hash=24e17b1e9c2cffd32c2cab2189a5e917%2316dd774cea9d3bd2c887f99be034a1de"
    HEADER = {
        "use-web-hash": "true",    
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36 Edg/130.0.0.0",  
    }
    payload = {
        "variables": {
            "sku": str(vgu),
            "selectedOptionIds": ["75220470"],
            "withLegacySpecificationsData": False
        }
    }
    try:
        response = requests.post(url=URL, json=payload, headers=HEADER)
    except Exception as e:
        if VPN_ENABLED and VPN_ROTATOR is not None:
            VPN_ROTATOR.rotate(f"request error: {e}")
        raise
    if response.status_code == 200:
        data = json.loads(response.text)
        result = {
            "PDF Link": [doc["url"] for doc in data["data"]["product"]["documents"]],
            "PDF Text": [doc["name"] for doc in data["data"]["product"]["documents"]],
            "sku": data["data"]["product"]["manufacturerPartNumber"]["partNumbers"][0],
            "vgu": vgu
        }
        # print(result)
        return result
    else:
        if VPN_ENABLED and VPN_ROTATOR is not None:
            VPN_ROTATOR.rotate(f"HTTP {response.status_code} for {vgu}")
        # print(response.status_code)
        # print(response.headers)
        result = {
            "PDF Link": [],
            "sku": ERROR_NOT_LOADED_URLS,
            "vgu": vgu
        }
        return result



# print(get_pdf_info_json('VGU1000'))
amount_of_rows = len(vgu_to_search)
processedData = [] 

if VPN_ENABLED and WireGuardRotator is not None:
    VPN_ROTATOR = WireGuardRotator(WG_CONFIG_DIR, cooldown_sec=WG_COOLDOWN_SEC)
    VPN_ROTATOR.ensure_up()
    atexit.register(VPN_ROTATOR.shutdown)

for index, row in enumerate(vgu_to_search):
    # print(f'Working on {row} {index+1}/{amount_of_rows}')
    time.sleep(random.randint(2, 5))
    data = get_pdf_info_json(row)
    returnObject = {}
    if len(data['PDF Link']) < 1:
            returnObject['PDF Link'] = 'No PDF attached'
    else :
        returnObject['VGU'] = data['vgu']
        returnObject['SKU'] = data['sku']
        for i in range(len(data['PDF Link'])):
            returnObject[f'PDF Link{i+1}'] = data['PDF Link'][i]
            returnObject[f'PDF Text{i+1}'] = data['PDF Text'][i]
        processedData.append(returnObject)
    
output_path = os.environ.get("OUTPUT_PATH")
if output_path:
    with open(output_path, "w", encoding="utf-8") as f:
        f.write(json.dumps(processedData))
else:
    print(json.dumps(processedData))

# current_datetime = datetime.now().strftime("%Y%m%d_%H%M%S")
# master_df.to_csv(f'{RESULT_FILE_NAME}{current_datetime}.csv', index=False)