import sys
import os

SCRIPTS_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.dirname(SCRIPTS_DIR)

if ROOT_DIR not in sys.path:
    sys.path.insert(0, ROOT_DIR)

from pinscrape.database import get_all_db_files, get_db_connection


def cleaning_title():
    db_files = get_all_db_files()
    if not db_files:
        print("No database files found.")
        return

    total_cleaned = 0
    for db_path in db_files:
        conn = get_db_connection(str(db_path))
        rows = conn.execute("SELECT id, ai_title FROM posts WHERE ai_title IS NOT NULL AND ai_title != ''").fetchall()

        cleaned = 0
        for row in rows:
            original = row["ai_title"]
            title = original.replace('"', '').replace('\u201c', '').replace('\u201d', '').strip()
            if title != original:
                conn.execute(
                    "UPDATE posts SET ai_title = ?, updated_at = datetime('now') WHERE id = ?",
                    (title, row["id"])
                )
                cleaned += 1

        conn.commit()
        conn.close()
        if cleaned > 0:
            print(f"Cleaned {cleaned} title(s) in {db_path.name}")
            total_cleaned += cleaned
        else:
            print(f"No titles to clean in {db_path.name}")

    print(f"\nTotal titles cleaned: {total_cleaned}")


if __name__ == "__main__":
    cleaning_title()
    print("Done.")