canteen-asset-tracker/scripts/apply_gps_sources.py

#!/usr/bin/env python3
"""
Apply GPS coordinates from multiple sources to canteen assets DB.

Sources (priority order):
  1. Booking GPS (MSFS technician check-in) — most reliable
  2. Photo EXIF GPS (OCR'd technician photos)
  3. exif-test-dev GPS (iPhone uploads)

Usage:
    python3 scripts/apply_gps_sources.py           # dry-run
    python3 scripts/apply_gps_sources.py --apply   # write to DB
    python3 scripts/apply_gps_sources.py --force   # overwrite existing GPS too
"""
import json
import sqlite3
import sys
from collections import defaultdict
from pathlib import Path

ASSET_DB = str(Path(__file__).resolve().parent.parent / "assets.db")
MSFS_DATA_DIR = "/home/oplabs/projects/ms-field-service-extraction/web/static/data"
MSFS_BACKUP_DB = "/home/oplabs/projects/ms-field-service-extraction/data-samples/msfs_backup/fieldservicecanteen.crm.dynamics.com_9fc6c50c-b097-f011-b4cb-7ced8d1b15a2_data.db"


def load_merged_assets() -> dict:
    """Load merged MSFS<->canteen asset mappings. Returns guid->machine_id."""
    with open(f"{MSFS_DATA_DIR}/merged-assets.json") as f:
        merged = json.load(f)
    guid_to_mid = {}
    for a in merged["assets"]:
        cmid = a.get("canteen_machine_id")
        guid = a.get("msfs_canteen_connect_guid", "")
        if cmid and guid:
            guid_to_mid[guid.upper()] = cmid
    return guid_to_mid


def get_booking_gps(guid_to_mid: dict) -> dict:
    """Extract booking GPS from MSFS backup, grouped by machine_id."""
    db = sqlite3.connect(MSFS_BACKUP_DB)

    # Work order -> customer asset
    wo_to_asset = {}
    for w in db.execute(
        'SELECT "msdyn_workorderid", "msdyn_customerasset!id" FROM "msdyn_workorder" WHERE "msdyn_customerasset!id" IS NOT NULL'
    ):
        wo_to_asset[w[0]] = w[1]

    # Customer asset -> GUID
    asset_info = {}
    for c in db.execute(
        'SELECT "msdyn_customerassetid", "hsl_canteenconnectguid" FROM "msdyn_customerasset"'
    ):
        asset_info[c[0]] = (c[1] or "").upper() if c[1] else ""

    # Collect booking GPS points
    booking_by_mid = defaultdict(list)
    for r in db.execute(
        """
        SELECT b."msdyn_latitude", b."msdyn_longitude", b."msdyn_workorder!id"
        FROM "bookableresourcebooking" b
        WHERE b."msdyn_latitude" IS NOT NULL AND b."msdyn_longitude" IS NOT NULL
        """
    ):
        lat, lon, wo_id = r
        if not wo_id or wo_id not in wo_to_asset:
            continue
        asset_id = wo_to_asset[wo_id]
        if not asset_id or asset_id not in asset_info:
            continue
        guid = asset_info[asset_id]
        if guid in guid_to_mid:
            mid = guid_to_mid[guid]
            booking_by_mid[mid].append((lat, lon))

    # Average per machine
    result = {}
    for mid, points in booking_by_mid.items():
        result[mid] = {
            "lat": sum(p[0] for p in points) / len(points),
            "lon": sum(p[1] for p in points) / len(points),
            "source": "booking",
            "count": len(points),
        }
    return result


def get_photo_gps() -> dict:
    """Extract GPS from OCR'd technician photos."""
    try:
        with open(f"{MSFS_DATA_DIR}/ocr_results.json") as f:
            ocr = json.load(f)
    except FileNotFoundError:
        return {}

    photo_by_mid = defaultdict(list)
    for k, v in ocr.items():
        if v.get("has_gps") and v.get("machine_id") and v.get("gps_lat") and v.get("gps_lon"):
            mid = v["machine_id"]
            # Normalize: strip leading zeros from last part
            parts = mid.split("-")
            last_part = parts[-1]
            if last_part.startswith("0") and len(last_part) > 5:
                last_part = last_part.lstrip("0")
            photo_by_mid[last_part].append((v["gps_lat"], v["gps_lon"]))

    result = {}
    for mid, points in photo_by_mid.items():
        result[mid] = {
            "lat": sum(p[0] for p in points) / len(points),
            "lon": sum(p[1] for p in points) / len(points),
            "source": "photo",
            "count": len(points),
        }
    return result


def get_exif_test_gps() -> dict:
    """Extract GPS from exif-test-dev iPhone uploads."""
    exif_dev_db = str(
        Path.home() / "projects" / "exif-test-dev" / "photos.db"
    )
    if not Path(exif_dev_db).exists():
        return {}

    db = sqlite3.connect(exif_dev_db)
    result = {}
    for r in db.execute(
        "SELECT machine_id, gps_lat, gps_lng FROM photos WHERE machine_id IS NOT NULL AND gps_lat IS NOT NULL"
    ):
        mid, lat, lng = r
        if mid not in result:
            result[mid] = {"points": [], "source": "exif_dev"}
        result[mid]["points"].append((lat, lng))

    averaged = {}
    for mid, data in result.items():
        pts = data["points"]
        averaged[mid] = {
            "lat": sum(p[0] for p in pts) / len(pts),
            "lon": sum(p[1] for p in pts) / len(pts),
            "source": "exif_dev",
            "count": len(pts),
        }
    return averaged


def get_seed_gps(conn: sqlite3.Connection) -> dict:
    """Extract GPS from Cantaloupe seed data if available."""
    try:
        cur = conn.execute(
            "SELECT machine_id, json_extract(data, '$.Latitude'), json_extract(data, '$.Longitude') "
            "FROM seed_data "
            "WHERE json_extract(data, '$.Latitude') IS NOT NULL "
            "AND json_extract(data, '$.Longitude') IS NOT NULL"
        )
        result = {}
        for row in cur.fetchall():
            mid, lat, lon = row
            if lat and lon:
                try:
                    result[mid] = {
                        "lat": float(lat),
                        "lon": float(lon),
                        "source": "seed",
                        "count": 1,
                    }
                except (ValueError, TypeError):
                    pass
        return result
    except sqlite3.OperationalError:
        return {}


def main():
    apply_changes = "--apply" in sys.argv
    force_update = "--force" in sys.argv
    mode = "DRY RUN (no writes)" if not apply_changes else "APPLYING changes"

    print(f"Assets DB: {ASSET_DB}")
    print(f"Mode: {mode}")
    if force_update:
        print("  (--force: will overwrite existing GPS coordinates too)")
    print()

    # 1. Load all GPS sources
    print("Loading GPS sources...")
    guid_to_mid = load_merged_assets()
    print(f"  GUID→machine_id mappings: {len(guid_to_mid)}")

    booking_gps = get_booking_gps(guid_to_mid)
    print(f"  Booking GPS: {len(booking_gps)} machine IDs")

    photo_gps = get_photo_gps()
    print(f"  Photo EXIF GPS: {len(photo_gps)} machine IDs")

    exif_gps = get_exif_test_gps()
    print(f"  EXIF test GPS: {len(exif_gps)} machine IDs")

    # Connect to assets DB
    conn = sqlite3.connect(ASSET_DB)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=OFF")

    seed_gps = get_seed_gps(conn)
    print(f"  Seed (Cantaloupe) GPS: {len(seed_gps)} machine IDs")

    # 2. Get current GPS state from assets DB
    cur = conn.execute("SELECT machine_id, latitude, longitude, name, company FROM assets")
    existing = {}
    for r in cur.fetchall():
        existing[r[0]] = {"lat": r[1], "lon": r[2], "name": r[3], "company": r[4]}

    existing_count = sum(1 for v in existing.values() if v["lat"] is not None)
    print(f"\nCurrent assets with GPS: {existing_count} / {len(existing)}")

    # 3. Prioritize and merge sources
    all_mids = set()
    all_mids.update(booking_gps.keys())
    all_mids.update(photo_gps.keys())
    all_mids.update(exif_gps.keys())
    all_mids.update(seed_gps.keys())

    updates = []  # (lat, lon, source, machine_id)
    skipped_existing = []
    no_match = []

    for mid in sorted(all_mids):
        # Determine best GPS: booking > photo > exif_dev > seed
        best = None
        for src_dict, src_name in [
            (booking_gps, "booking"),
            (photo_gps, "photo"),
            (exif_gps, "exif_dev"),
            (seed_gps, "seed"),
        ]:
            if mid in src_dict:
                best = src_dict[mid]
                break

        if not best:
            continue

        # Check if asset exists in DB
        if mid not in existing:
            no_match.append(mid)
            continue

        existing_gps = existing[mid]
        has_gps = existing_gps["lat"] is not None and existing_gps["lon"] is not None

        if has_gps and not force_update:
            # Code for checking distance removed — just skip
            skipped_existing.append((mid, existing_gps["lat"], existing_gps["lon"], best["lat"], best["lon"]))
            continue

        old_label = f" ({existing_gps['lat']:.6f},{existing_gps['lon']:.6f})" if has_gps else " (no GPS)"
        updates.append((best["lat"], best["lon"], best["source"], mid))
        print(
            f"  {mid:>6s} {old_label} → ({best['lat']:.6f}, {best['lon']:.6f}) [{best['source']:>7s}] "
            f"{existing_gps['name'][:40]}"
        )

    # 4. Apply
    if apply_changes and updates:
        conn.executemany(
            "UPDATE assets SET latitude=?, longitude=?, location_source=?, updated_at=datetime('now') WHERE machine_id=?",
            [(lat, lon, src, mid) for lat, lon, src, mid in updates],
        )
        conn.commit()
        print(f"\n  ✅ Applied {len(updates)} GPS updates")
    elif updates:
        print(f"\n  Would update {len(updates)} assets. Run with --apply to write.")
    else:
        print("\n  No updates needed.")

    # Summary
    print(f"\n{'=' * 60}")
    print(f"SUMMARY")
    print(f"{'=' * 60}")
    print(f"  Total GPS sources combined:    {len(all_mids)}")
    print(f"  Updates to apply:              {len(updates)}")
    print(f"  Skipped (already has GPS):     {len(skipped_existing)}")
    print(f"  No matching asset in DB:       {len(no_match)}")

    # Verify final count
    if apply_changes:
        cur = conn.execute("SELECT COUNT(*) FROM assets WHERE latitude IS NOT NULL")
        final_count = cur.fetchone()[0]
        print(f"\n  Final assets with GPS: {final_count} / {len(existing)}")

    # Print a few no-match examples
    if no_match:
        print(f"\n  Sample unmapped machine IDs: {sorted(no_match)[:5]}")

    conn.close()


if __name__ == "__main__":
    main()