import re
import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='signgwph_university',
    password='signgwph_university',
    database='signgwph_university'
)

cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT id, title, description FROM subjects WHERE description LIKE '%[video%'")
rows = cursor.fetchall()
print(f"Found {len(rows)} subjects with video shortcodes.")

updated_count = 0
for r in rows:
    desc = r['description']
    if not desc:
        continue
        
    def repl(match):
        mp4_url = match.group(1)
        return f'<div class="video-container my-3" style="text-align:center;"><video controls width="100%" style="max-width:800px; border-radius:8px; box-shadow: 0 4px 12px rgba(0,0,0,0.15);" src="{mp4_url}"></video></div>'
        
    new_desc = re.sub(r'\[video[^\]]*mp4=["\']([^"\']+)["\'][^\]]*\](?:\[\/video\])?', repl, desc)
    new_desc = re.sub(r'\[video[^\]]*src=["\']([^"\']+)["\'][^\]]*\](?:\[\/video\])?', repl, new_desc)
    
    if new_desc != desc:
        cursor2 = conn.cursor()
        cursor2.execute("UPDATE subjects SET description = %s WHERE id = %s", (new_desc, r['id']))
        cursor2.close()
        updated_count += 1
        
conn.commit()
print(f"Successfully converted video shortcodes in {updated_count} lessons/courses!")
cursor.close()
conn.close()