import pymysql
from bs4 import BeautifulSoup
import json

DB_CONFIG = {
    "host": "localhost",
    "database": "chamados_local",
    "user": "dev", 
    "password": "dev",    
    "port": 3306
}

def migrate():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    try:
        cursor.execute("SELECT id, payload FROM tickets WHERE payload IS NOT NULL")
        rows = cursor.fetchall()

        updated = 0

        for row in rows:
            ticket_id = row[0]
            payload_raw = row[1]

            try:
                payload = json.loads(payload_raw)
            except:
                continue  # pula JSON inválido

            # verifica ambas versões da chave
            has_observacoes = "observacoes" in payload or "observações" in payload

            if not has_observacoes:
                continue

            value = payload.get("observacoes") or payload.get("observações")

            # só cria msg se não existir
            if "msg" not in payload:
                payload["msg"] = value or "Sem observações"

            # remove as chaves antigas
            payload.pop("observacoes", None)
            payload.pop("observações", None)

            cursor.execute(
                "UPDATE tickets SET payload = %s WHERE id = %s",
                (json.dumps(payload, ensure_ascii=False), ticket_id)
            )

            updated += 1

            if updated % 100 == 0:
                print(f"🔄 {updated} registros atualizados...")

        conn.commit()
        print(f"✅ Migração concluída! Total atualizado: {updated}")

    except Exception as e:
        conn.rollback()
        print("❌ Erro:", e)

    finally:
        cursor.close()
        conn.close()

def parse_html_to_json(html):
    soup = BeautifulSoup(html, "html.parser")
    result = {}

    for p in soup.find_all("p"):
        strong = p.find("strong")

        if not strong:
            continue

        strong_text = strong.text.strip()

        # Caso 1: "Nome: "
        if ":" in strong_text:
            key = strong_text.replace(":", "").strip().lower()
            value = p.get_text().replace(strong_text, "").strip()

        # Caso 2: "Nome" + <strong>valor</strong>
        else:
            key = p.contents[0].strip().lower()
            value = strong.text.strip()

        result[key] = value

    return result

def main():
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    cursor.execute("SELECT id, content FROM tickets WHERE payload IS NULL")

    total = 0

    for id, content in cursor.fetchall():
        try:
            parsed = parse_html_to_json(content)

            cursor.execute(
                "UPDATE tickets SET payload = %s WHERE id = %s",
                (json.dumps(parsed, ensure_ascii=False), id)
            )

            total += 1

            if total % 100 == 0:
                print(f"{total} processados...")
                conn.commit()

        except Exception as e:
            print(f"Erro no ID {id}: {e}")

    conn.commit()
    cursor.close()
    conn.close()

    print(f"Finalizado: {total} registros processados")


if __name__ == "__main__":
    main()
    migrate()