#!/usr/bin/env python3
from dotenv import load_dotenv
from openai import OpenAI
import html, os, phpserialize, pymysql, re

# Usage:    uv run hello-ai.py
# Requires: openai dotenv pymysql phpserialize
#           local WordPress installation
#           OPENAI_API_KEY, DB_NAME, DB_USER and DB_PW in environment or .env file
# Caution:  do a backup first!

# use openAI API to get description of one image
def describeImage(client, imgUrl, title):
    system_prompt = """
        Du bist ein Agent, der Screenshots und vereinzelt Fotos auf
        einer technischen Website (IT-Blog) beschreibt.

        Du bekommst ein Bild und sollst dann in deutscher Sprache
        beschreiben, was auf dem Bild zu sehen ist. Der erste Satz
        soll einen kurzen Überblick über den Inhalt geben. Weitere
        zwei bis vier Sätze beschreiben die Details. Der Text
        sollte maximal 50 bis 70 Worte lang sein.
      """
    user_prompt = """
        Beschreibe das Bild. Die Bildunterschrift 
        (soweit vorhanden) lautet: 
      """ + title
    response = client.responses.create(
        model = "gpt-5.4-mini",
        reasoning={"effort": "low"},
        max_output_tokens=1000,
        input=[
            {
                "role": "system",
                "content": system_prompt
            },
            {
                "role": "user",
                "content": [
                    {
                        "type": "input_image",
                        "image_url": imgUrl
                    },
                    {
                        "type": "input_text",
                        "text": user_prompt
                    }
                ]
            }
        ]
        )
    if response.status == "completed":
        return response.output_text
    else:
        return ""

# loop over recent images in WordPress database without alt text,
# use openAI to get description and save it in database
def processImages(client, n):
    selectImages = """
        SELECT p.ID,
               p.guid AS 'url',
               p.post_excerpt AS 'title'
        FROM   wp_posts p
        LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id 
                                AND pm.meta_key = '_wp_attachment_image_alt'
        WHERE p.post_type = 'attachment'
            AND p.post_mime_type LIKE 'image/%'
            AND pm.meta_value IS NULL OR pm.meta_value = ''
        ORDER BY p.post_date DESC
        LIMIT """ + str(n) + ";"
    cursor.execute(selectImages)
    result = cursor.fetchall()
    for row in result:
        altText = describeImage(client, row['url'], row['title'])
        print(row['ID'], row['url'], altText)
        if altText.strip() != "":
            saveAltText(row['ID'], altText)
        print()

# change alt text for one image in wp database
def saveAltText(id, altText):
    insertAltText = """
       INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
       VALUES (%s, '_wp_attachment_image_alt', %s);"""
    # print(insertAltText, id, altText)
    cursor.execute(insertAltText, (id, altText))
    myConn.commit()

# load image metadata from WordPress database into dictionary
# for efficient later use
def buildImageDictionary(n):
    # build dictionary with image URL as key and alt text as value
    # alt text is in wp_postmeta (key = _wp_attachment_image_alt)
    # image variants are in wp_postmeta (key = _wp_attachment_metadata) in a PHP serialized string
    selectAltTexts = """
        SELECT p.id,
               p.guid AS url,
               pm_alt.meta_value as alttext,
               pm_meta.meta_value as metadata
        FROM   wp_posts p
        JOIN   wp_postmeta pm_alt ON p.ID = pm_alt.post_id AND pm_alt.meta_key = '_wp_attachment_image_alt'
        JOIN   wp_postmeta pm_meta ON p.ID = pm_meta.post_id AND pm_meta.meta_key = '_wp_attachment_metadata'
        WHERE  p.post_type = 'attachment'
          AND  p.post_mime_type LIKE 'image/%'
          AND  pm_alt.meta_value <> ''  -- Ensures alt text is not empty
          AND  pm_meta.meta_value <> ''  -- Ensures metadata is not empty
        ORDER BY p.post_date DESC
        LIMIT """ + str(n) + ";"
    cursor.execute(selectAltTexts)
    result = cursor.fetchall()
    dict = {}
    for row in result:
        alttext = row['alttext']
        imageUrl = row['url']
        imageMeta = row['metadata']
        # all image URLs are collected here; insert original url here, all variants later
        allUrls = [imageUrl]  
        # https://host/path/name.jpg -> https://host/path
        urlPath = os.path.dirname(imageUrl)
        # phpserialize expects bytes, not a string
        data = phpserialize.loads(imageMeta.encode('utf-8'), decode_strings=True)
        # special case: name-scaled.png/jpg (only when original image > 2560 pixel)
        firstOne = urlPath+ '/' + data['file']
        if firstOne != imageUrl:    
            allUrls += [firstOne]
         # loop through alternative image file names (name-300x300.jpg etc.)
        sizes_info = data['sizes']
        for _, info in sizes_info.items():
            allUrls += [ urlPath+ '/' + info['file']]
        for url in allUrls:
            dict[url] = alttext
    return dict

# get recent posts/pages, loop through img tags, replace
# old/missing alt attributes with new text
def processPagesAndPosts(n):
    selectPP = """
        SELECT p.id, p.post_title, p.post_content, p.post_content_filtered
        FROM wp_posts p
        WHERE p.post_content LIKE '%alt=""%'
          AND p.post_type IN('page', 'post')
          AND p.post_status='publish'
        ORDER BY id DESC LIMIT """ + str(n) + ";"
    cursor.execute(selectPP)
    result = cursor.fetchall()
    for pp in result:  # loop over posts/pages
        print(pp['id'], pp['post_title'])
        contentHtml = pp['post_content']
        contentMarkdown = pp['post_content_filtered']
        imgRegex = r'<img\s+([^>]*?)\s*/?>'
        # print("post id: " + str(pp['id']))
        newContentHtml = re.sub(imgRegex, setAltAttribute, contentHtml) 
        newContentMarkdown = re.sub(imgRegex, setAltAttribute, contentMarkdown)

        if contentHtml != newContentHtml or contentMarkdown != newContentMarkdown:
            print("changed, orig/new len:", len(contentHtml), len(newContentHtml))
            print("changed, orig/new len:", len(contentMarkdown), len(newContentMarkdown))
            updatePP(pp['id'], newContentHtml, newContentMarkdown)
    
# update post/page in WP database
def updatePP(id, newContent, newContentFiltered):
    updateContent = """
       UPDATE wp_posts 
       SET post_content=%s, post_content_filtered=%s
       WHERE id = %s"""
    cursor.execute(updateContent, (newContent, newContentFiltered, id))
    myConn.commit()
    # print(updateContent % (newContent, newContentFiltered, id))


# replace/insert alt tag (to be used in re.sub())
def setAltAttribute(match):
    origImgTag = match.group(0)
    insideTag = match.group(1)
    # print("*", origImgTag) # debugging
    # get src attribute
    srcMatch = re.search(r'\bsrc=["\'](.*?)["\']', insideTag)    
    if not srcMatch:         # no src attribute? -> skip
        return origImgTag

    src = srcMatch.group(1)  # 'https://hostname/uploads/name.jpg
    if not src in imgDict:   # src url is not in dictionary: skip
        return origImgTag

    # get alt attribute
    altMatch = re.search(r'\balt=["\'](.*?)["\']', insideTag)
    # new alt text from dictionary, escape " < >
    newAltText = html.escape(imgDict.get(src), quote=True)
    # print(newAltText)
    if altMatch:
        # Alt attribute exists, replace its value
        newImgTag = re.sub(r'\balt=["\'].*?["\']', lambda m: f'alt="{newAltText}"', origImgTag)
        # print("alt changed/updated")
    else:
        # No alt attribute, add it
        newImgTag = origImgTag[:-2] + f' alt="{newAltText}"' + origImgTag[-2:]
    
    return newImgTag

# main code starts here

# load environment variables from .env file
load_dotenv()
# file has to look like this
# OPENAI_API_KEY=your-key
# DB_NAME=wp-db-name
# DB_USER=mysql-user
# DB_PW=mysql-pw

# connect to OpenAI API; uses os.getenv('OPENAI_API_KEY') per default
client = OpenAI()

# connect to database
myConn = pymysql.connect(host='localhost',
                         user=os.getenv('DB_USER'),
                         password=os.getenv('DB_PW'),
                         database=os.getenv('DB_NAME'),
                         cursorclass=pymysql.cursors.DictCursor)

try:
    with myConn.cursor() as cursor:
       # process images (set alt data in wp_postmeta) 
       processImages(client, 50) 
       # process pages, change alt tag in HTML + Markdown for images which have alt data in wp_postmeta
       imgDict = buildImageDictionary(2000)
       processPagesAndPosts(2000)
finally:
    # Close the connection
    myConn.close()
