import requests
import pdfplumber
import mysql.connector
import openai

# Your OpenAI API key
openai.api_key = "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"

# Connect to MariaDB database
conn = mysql.connector.connect(
    host="localhost",          # Change to your MariaDB host (e.g., localhost or IP)
    user="mps",       # Your MariaDB username
    password="1705Jg@35!",  # Your MariaDB password
    database="techhub"    # The database containing the manuals table
)

cursor = conn.cursor()

# Get manual links
cursor.execute("SELECT id, file_path FROM manuals")
manuals = cursor.fetchall()

# Function to download and extract text from a PDF
def extract_pdf_content(pdf_url):
    response = requests.get(pdf_url)
    with open("temp_manual.pdf", "wb") as f:
        f.write(response.content)

    # Extract content using pdfplumber (you can use PyPDF2 if preferred)
    with pdfplumber.open("temp_manual.pdf") as pdf:
        text = ""
        for page in pdf.pages:
            text += page.extract_text()  # Extract text from each page
    return text

# Convert text to embeddings (if needed for later use)
def get_embedding(text):
    response = openai.embeddings.create(
        input=text,
        model="text-embedding-ada-002"
    )
    return response['data'][0]['embedding']

# Loop over manuals and process each one
for manual_id, file_path in manuals:
    print(f"Processing manual ID {manual_id}...")
    
    # Extract text from the PDF
    content = extract_pdf_content('https://mp-techhub.zapto.org/storage/' + file_path)
    
    # Optionally: You can generate embeddings for later use
    embedding = get_embedding(content)
    
    # Save the content into the database
    cursor.execute("UPDATE manuals SET content = %s WHERE id = %s", (content, manual_id))
    conn.commit()

    print(f"Manual {manual_id} processed and saved.")

conn.close()
print("✅ All manuals processed and content saved!")