#!/usr/bin/env python

import os
import sqlite3

shotwell = sqlite3.connect(
    os.getenv("HOME", ".") + "/.local/share/shotwell/data/photo.db"
)
shotwell.row_factory = sqlite3.Row

for c in shotwell.execute("SELECT id, rating, filename FROM PhotoTable p"):
    report = "thumb%0.16x\t" % c["id"]
    report += "%d\t%d\t" % (c["id"], c["rating"])
    tags = [
        t["name"]
        for t in [
            tag
            for tag in shotwell.execute(
                "SELECT name FROM TagTable WHERE photo_id_list LIKE ?",
                ("%%thumb%0.16x%%" % c["id"],),
            )
        ]
    ]
    report += ",".join(tags)
    report += "\t%s" % c["filename"]
    if len(tags) > 0:
        # print(report)
        # create exiv2 cmp file
        cmd_file = c["filename"] + ".cmd"
        f = open(cmd_file, "a")
        for t in tags:
            print("set Xmp.dc.subject XmpBag %s" % (t), file=f)
            print("set Xmp.digiKam.TagsList %s" % (t), file=f)
        f.close()
        # create xmp sidecar
        command = '''exiv2 -eX -m "%s" "%s"''' % (cmd_file, c["filename"])
        print(command)
        good_sidecar = c["filename"] + ".xmp"
        bad_sidecar = c["filename"][:-4] + ".xmp"
        ret = os.system(command.encode("utf-8"))
        if ret != 0:
            print("warning: exiv command return %d, ran: %s" % (ret, command))
        else:
            os.rename(bad_sidecar, good_sidecar)
        os.remove(cmd_file)