"""
Einmalige Migration: MOV/AVI/M4V → MP4, HEIC/HEIF → JPEG
Alle betroffenen Dateien auf Disk konvertieren + DB-URLs aktualisieren.

Ausführen im Container:
    python3 /app/migrate_media.py
"""

import json
import os
import sys

from database import db
from media_utils import convert_media

MEDIA_DIR    = os.getenv("MEDIA_DIR", "/data/media")
CONVERT_EXTS = {".mov", ".avi", ".m4v", ".heic", ".heif"}


def _new_ext(ext: str) -> str:
    return ".mp4" if ext in {".mov", ".avi", ".m4v"} else ".jpg"


def convert_file(path: str) -> str | None:
    """Convert file in-place, return new path or None on failure."""
    ext = os.path.splitext(path)[1].lower()
    if ext not in CONVERT_EXTS:
        return None
    print(f"  Konvertiere: {path}")
    with open(path, "rb") as f:
        data = f.read()
    converted, new_ext = convert_media(data, os.path.basename(path))
    if new_ext == ext:
        print(f"    ✗ Konvertierung fehlgeschlagen, übersprungen.")
        return None
    new_path = path[: -len(ext)] + new_ext
    with open(new_path, "wb") as f:
        f.write(converted)
    os.unlink(path)
    print(f"    ✓ → {new_path}")
    return new_path


def url_from_path(path: str) -> str:
    rel = os.path.relpath(path, MEDIA_DIR)
    return "/media/" + rel.replace(os.sep, "/")


def update_db(old_url: str, new_url: str) -> int:
    updates = 0
    with db() as conn:
        # diary_media
        r = conn.execute("UPDATE diary_media SET url=? WHERE url=?", (new_url, old_url))
        updates += r.rowcount

        # JSON-Arrays: forum_threads, forum_posts, routes
        for table, col in [("forum_threads", "foto_urls"),
                            ("forum_posts",   "foto_urls"),
                            ("routes",        "foto_urls")]:
            rows = conn.execute(f"SELECT id, {col} FROM {table} WHERE {col} LIKE ?",
                                (f"%{old_url}%",)).fetchall()
            for row in rows:
                urls = json.loads(row[col] or "[]")
                new_urls = [new_url if u == old_url else u for u in urls]
                conn.execute(f"UPDATE {table} SET {col}=? WHERE id=?",
                             (json.dumps(new_urls), row["id"]))
                updates += 1

        # Einzelne URL-Felder
        for table, col in [("lost_dogs", "foto_url"), ("poison", "foto_url")]:
            r = conn.execute(f"UPDATE {table} SET {col}=? WHERE {col}=?",
                             (new_url, old_url))
            updates += r.rowcount

    return updates


def main():
    total_files = 0
    total_db    = 0

    for root, _, files in os.walk(MEDIA_DIR):
        for fname in files:
            ext = os.path.splitext(fname)[1].lower()
            if ext not in CONVERT_EXTS:
                continue
            old_path = os.path.join(root, fname)
            old_url  = url_from_path(old_path)
            new_path = convert_file(old_path)
            if new_path:
                new_url  = url_from_path(new_path)
                n = update_db(old_url, new_url)
                print(f"    DB: {n} Zeile(n) aktualisiert ({old_url} → {new_url})")
                total_files += 1
                total_db    += n

    print(f"\n✓ Fertig: {total_files} Datei(en) konvertiert, {total_db} DB-Einträge aktualisiert.")


if __name__ == "__main__":
    main()