#!/usr/bin/env python3

# usage: ./exif-to-sql.py *.jpg *.heic 
#
# requirements: * modules cryptography pillow pillow_heic pymysql  (pip install ... or uv add ...)
#               * MySQL/MariaDB server (update connection data below!)
#               * MySQL user 'exifuser' who can access
#               * database 'exifdb' with
#               * table 'photos':
#
#                 CREATE TABLE photos(
#                   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
#                   name VARCHAR(255) NOT NULL,
#                   size INT,
#                   orientation INT,
#                   datetimeoriginal DATETIME,
#                   latitude DOUBLE,
#                   longitude DOUBLE,
#                   altitude DOUBLE,
#                   ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()
#                 );

import inspect, os, platform, sys
from glob import glob
from PIL import Image
from PIL.ExifTags import TAGS
from pillow_heif import register_heif_opener
import pymysql.cursors

# activate HEIF extionsion for pillow
register_heif_opener()

# convert EXIF GPS (degrees, minutes, seconds) tuple to decimal degrees.
def gps_to_decimal(coords, ref: str) -> float:
    d, m, s = (float(x) for x in coords)
    decimal = d + m / 60 + s / 3600
    if ref in ('S', 'W'):
        decimal = -decimal
    return decimal

# read file from path, return EXIF attributes as dictionary
def get_exif_fields(path: str) -> dict:
    result = {
        'name':              os.path.basename(path),
        'size':              os.path.getsize(path),
        'orientation':       None,
        'datetimeoriginal':  None,
        'latitude':          None,
        'longitude':         None,
        'altitude':          None,
    }

    with Image.open(path) as img:
        exif = img.getexif()

    if not exif:
        return result

    # Orientation is in the main IFD
    result['orientation'] = exif.get(0x0112)

    # DateTimeOriginal is in the EXIF sub-IFD
    exif_ifd = exif.get_ifd(0x8769)
    dt = exif_ifd.get(0x9003)
    if dt is not None:
        # '2026:12:13 10:30:00' -> '2026-12-13 10:30:00'
        result['datetimeoriginal'] = str(dt.replace(':', '-', 2))

    # GPS is in its own sub-IFD
    # GPS (EXIF sub-IFD)
    gps_ifd = exif.get_ifd(0x8825)
    if gps_ifd:
        lat  = gps_ifd.get(0x0002)  # GPSLatitude
        latR = gps_ifd.get(0x0001)  # GPSLatitudeRef
        lon  = gps_ifd.get(0x0004)  # GPSLongitude
        lonR = gps_ifd.get(0x0003)  # GPSLongitudeRef
        alt  = gps_ifd.get(0x0006)  # GPSAltitude
        altR = gps_ifd.get(0x0005)  # GPSAltitudeRef
     
        if lat and latR:
            result['latitude']  = gps_to_decimal(lat, latR)
        if lon and lonR:
            result['longitude'] = gps_to_decimal(lon, lonR)
        if alt is not None:
            result['altitude'] = \
                -float(alt) if altR == b'\x01' else float(alt)

    return result

# --- main code starts here ---

# connect to MySQL/MariaDB server
conn = pymysql.connect(host='localhost',
                       user='exifuser',
                       password='topSecret',
                       db='exifdb',
                       port=3306,
                       charset='utf8mb4',
                       cursorclass=pymysql.cursors.DictCursor)

# globbing, necessary for only for Windows, doesn't hurt for other OSs
filenames = []
for arg in sys.argv[1:]:
    filenames.extend(glob(arg))
    
# SQL command to insert record; cleandoc removes indentation
sql = inspect.cleandoc("""
  INSERT INTO photos (name, size, orientation, datetimeoriginal,
                      latitude, longitude, altitude)
  VALUES (%s, %s, %s, %s, %s, %s, %s);""")

# process files, insert data into database
for file in filenames:
    try:
        fields = get_exif_fields(file)
        values = []
        for key in ('name', 'size', 'orientation', 'datetimeoriginal',
                    'latitude', 'longitude', 'altitude'):
            values.append(fields[key])

        # INSERT ausführen
        with conn.cursor() as cur:
            cur.execute(sql, tuple(values))
    except Exception as e:
        print("-- skipped %s" % file)
        print("error:", e)

# commit all inserts, close database connection
conn.commit()
conn.close()
