write .xmp with DateTimeOriginal for files which miss creation date based on Library.apdb extraction and file creation date

create_date_xmps.ugly.sh

#!/usr/bin/env bash

## move away CR2 and AAE files
cd /data/
SRC="./Fotos-Mediathek.photoslibrary/Masters/"
DEST="./additional-files/Masters/"
sudo mkdir -p "$DEST"

sudo rsync -av \
  --remove-source-files \
  --prune-empty-dirs \
  --include='*/' \
  --include='*.AAE' --include='*.aae' \
  --include='*.CR2' --include='*.cr2' \
  --exclude='*' \
  "$SRC" "$DEST"


## export exif
exiftool -r -j -n \
  -FilePath -FileModifyDate \
  -DateTimeOriginal -CreateDate -ModifyDate \
  -XMP:CreateDate -XMP:ModifyDate -XMP:DateCreated -XMP:MetadataDate \
  -OffsetTimeOriginal -TimeZoneOffset \
  /data/Fotos-Mediathek.photoslibrary/Masters > filelist.json

# manual review
# jq 'sort_by(.FileModifyDate // "9999:99:99 99:99:99")' filelist.json > filelist-sorted.json


## export photosdb
DB="/data/Fotos-Mediathek.photoslibrary/database/Library.apdb"
OUT="photosdb.json"
LIB_PREFIX="/data/Fotos-Mediathek.photoslibrary/Masters/"

sqlite3 -readonly "$DB" <<SQL > "$OUT"
.mode json

-- RKMaster = files (has relative path like 2014/01/31/20140131-.../IMG_7981.mp4)
-- RKVersion = per-photo version, has imageDate (the "taken" date)
WITH ver AS (
  SELECT
    v.masterId,
    v.imageDate,
    ROW_NUMBER() OVER (PARTITION BY v.masterId ORDER BY v.imageDate) AS rn
  FROM RKVersion v
  WHERE v.isInTrash = 0
)
SELECT
  '$LIB_PREFIX' || m.imagePath AS path,
  -- convert from Apple/Cocoa epoch (2001-01-01) to Unix
  CASE
    WHEN ver.imageDate IS NOT NULL
    THEN datetime(ver.imageDate + 978307200, 'unixepoch')
    ELSE NULL
  END AS imageDate
FROM RKMaster m
LEFT JOIN ver ON ver.masterId = m.modelId AND ver.rn = 1
WHERE m.isInTrash = 0
ORDER BY path;
SQL

echo "wrote $OUT"

## test
jq 'length' filelist.json 
# 28272
jq 'length' photosdb.json 
# 28674

# missings
jq -s '
  .[0] as $exif
| (.[1] | map(.path) | INDEX(.)) as $dbidx
| [
    $exif[]
    | select( ($dbidx[.SourceFile] // null) == null )
  ]
' filelist.json photosdb.json > missing.json

jq 'length' missing.json 
# 178


## join
jq -s '
  .[0] as $exif
| .[1] as $db
| [
    $exif[]
    | . as $e
    | $e + (
        first( $db[] | select(.path == $e.SourceFile) )
        // {}
      )
  ]
' filelist.json photosdb.json > joined.json

jq 'length' joined.json


# to-xmp.json
jq '
  [
    .[]
    | select(
        (.DateTimeOriginal | tostring | startswith("0000:00:00"))
        or (.DateTimeOriginal == null)
        or (.DateTimeOriginal == "")
      )
    | . as $e
    | (
        if ($e.imageDate != null and $e.imageDate != "") then
          # imageDate like "2016-02-04 07:48:58"
          ($e.imageDate
            | capture("^(?<y>[0-9]{4})-(?<m>[0-9]{2})-(?<d>[0-9]{2}) (?<time>[0-9]{2}:[0-9]{2}:[0-9]{2})")
            | "\(.y):\(.m):\(.d) \(.time)"
          )
        else
          # fallback: FileModifyDate, keep timezone if present
          $e.FileModifyDate
        end
      ) as $dt
    | {
        SourceFile: $e.SourceFile,
        "XMP:DateTimeOriginal": $dt,
        "XMP:CreateDate": $dt,
        "XMP:ModifyDate": $dt
      }
  ]
' joined.json > to-xmp.json

jq 'length' to-xmp.json
#9714

## much more complicated to-xmp with timezone handling
jq '
  # turn a DB date into an object
  def parse_db_date:
    capture("^(?<y>\\d{4})-(?<m>\\d{2})-(?<d>\\d{2}) (?<H>\\d{2}):(?<M>\\d{2}):(?<S>\\d{2})")
    | {y:(.y|tonumber), m:(.m|tonumber), d:(.d|tonumber),
       H:(.H|tonumber), M:(.M|tonumber), S:(.S|tonumber)};

  # turn a file date with tz into an object
  # e.g. 2012:05:17 01:52:13+02:00
  def parse_file_date_tz:
    capture("^(?<y>\\d{4}):(?<m>\\d{2}):(?<d>\\d{2}) (?<H>\\d{2}):(?<M>\\d{2}):(?<S>\\d{2})(?<tz>[+-]\\d{2}:?\\d{2})?")
    | {
        y:(.y|tonumber), m:(.m|tonumber), d:(.d|tonumber),
        H:(.H|tonumber), M:(.M|tonumber), S:(.S|tonumber),
        tz:(.tz // "+00:00")
      };

  # seconds since midnight
  def secs(h;m;s): h*3600 + m*60 + s;

  # parse "+02:00" or "+0200" → seconds
  def tz_to_secs($tz):
    ($tz
     | capture("^(?<sign>[+-])(?<h>\\d{2}):?(?<m>\\d{2})$")
     | {sign: .sign, h:(.h|tonumber), m:(.m|tonumber)}
    ) as $x
    | (($x.h*3600 + $x.m*60) * (if $x.sign == "-" then -1 else 1 end));

  # compare db datetime with file datetime-in-UTC
  # return true if same second and date is same or file-date = db-date+1day
  def same_instant_db_file($db; $file):
    # db totals
    (secs($db.H; $db.M; $db.S)) as $dbs
    |
    # file local seconds
    (secs($file.H; $file.M; $file.S)) as $fs_local
    |
    # offset in seconds
    (tz_to_secs($file.tz)) as $off
    |
    # file in UTC
    ($fs_local - $off) as $fs_utc_raw
    |
    # normalize to 0..86399 and get day shift
    (if $fs_utc_raw < 0
     then {sec: ($fs_utc_raw + 86400), day_shift: -1}
     elif $fs_utc_raw >= 86400
     then {sec: ($fs_utc_raw - 86400), day_shift: 1}
     else {sec: $fs_utc_raw, day_shift: 0}
     end) as $utc
    |
    # times must match
    ($utc.sec == $dbs)
    and
    (
      # same day
      ($utc.day_shift == 0 and $file.y == $db.y and $file.m == $db.m and $file.d == $db.d)
      or
      # file one day ahead (the screenshot case)
      ($utc.day_shift == -1 and (
        # naive 1-day-back compare (good enough for this library)
        ($file.y == $db.y and $file.m == $db.m and ($file.d - 1) == $db.d)
      ))
    );

  [
    .[]
    | select(
        (.DateTimeOriginal | tostring | startswith("0000:00:00"))
        or (.DateTimeOriginal == null)
        or (.DateTimeOriginal == "")
      )
    | . as $e
    | (
        if ($e.imageDate != null and $e.imageDate != "") and ($e.FileModifyDate != null and $e.FileModifyDate != "") then
          # we have both -> decide
          ( ($e.imageDate | parse_db_date) ) as $db
          | ( ($e.FileModifyDate | parse_file_date_tz) ) as $fd
          | if same_instant_db_file($db; $fd) then
              # they describe the same instant -> keep the one WITH TZ
              $e.FileModifyDate
            else
              # else keep Photos date, but colonized
              ($e.imageDate
                | capture("^(?<y>\\d{4})-(?<m>\\d{2})-(?<d>\\d{2}) (?<t>\\d{2}:\\d{2}:\\d{2})")
                | "\(.y):\(.m):\(.d) \(.t)"
              )
            end
        elif ($e.imageDate != null and $e.imageDate != "") then
          # only DB
          ($e.imageDate
            | capture("^(?<y>\\d{4})-(?<m>\\d{2})-(?<d>\\d{2}) (?<t>\\d{2}:\\d{2}:\\d{2})")
            | "\(.y):\(.m):\(.d) \(.t)"
          )
        else
          # only file
          $e.FileModifyDate
        end
      ) as $dt
    | {
        SourceFile: $e.SourceFile,
        "XMP:DateTimeOriginal": $dt,
        "XMP:CreateDate": $dt,
        "XMP:ModifyDate": $dt
      }
  ]
' joined.json > to-xmp.json


jq 'length' to-xmp.json
#9714

jq '.[0:2]' to-xmp.json > to-xmp-test.json

## all trials to make exiftool -j=to-xmp-test.json work failed!!

# write xmp sidecar files

# jq -c '.[]' to-xmp.json | while IFS= read -r row; do
#   file=$(jq -r '.SourceFile' <<<"$row")
#   dto=$(jq -r '."XMP:DateTimeOriginal"' <<<"$row")
#   exiftool \
#     -overwrite_original \
#     -o '%d%f.%e.xmp' \
#     -XMP:DateTimeOriginal="$dto" \
#     -XMP:CreateDate="$dto" \
#     -XMP:ModifyDate="$dto" \
#     "$file"
# done

# parallel version
jq -r '.[] | [ .SourceFile, ."XMP:DateTimeOriginal" ] | @tsv' to-xmp.json \
| parallel -j 4 --colsep '\t' \
    exiftool -overwrite_original -o %d%f.%e.xmp \
      -XMP:DateTimeOriginal={2} -XMP:CreateDate={2} -XMP:ModifyDate={2} {1}


echo "done"