sstent
d1cfd0fd8e
- Add Fitbit authentication flow (save credentials, OAuth callback handling) - Implement Garmin MFA support with successful session/cookie handling - Optimize segment discovery with new sampling and activity query services - Refactor database session management in discovery API for better testability - Enhance activity data parsing for charts and analysis - Update tests to use testcontainers and proper dependency injection - Clean up repository by ignoring and removing tracked transient files (.pyc, .db)
97 lines
3.9 KiB
Python
97 lines
3.9 KiB
Python
import os
|
|
import json
|
|
import logging
|
|
from sqlalchemy import create_engine, text
|
|
from sqlalchemy.orm import sessionmaker
|
|
|
|
# Configure logging
|
|
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
|
|
logger = logging.getLogger(__name__)
|
|
|
|
# Constants
|
|
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:postgres@db:5432/fitbit_garmin_sync")
|
|
# If running outside docker, use localhost:5433
|
|
if "db:5432" in DATABASE_URL and os.system("ping -c 1 db > /dev/null 2>&1") != 0:
|
|
DATABASE_URL = DATABASE_URL.replace("db:5432", "localhost:5433")
|
|
|
|
def repair_geodata():
|
|
engine = create_engine(DATABASE_URL)
|
|
Session = sessionmaker(bind=engine)
|
|
session = Session()
|
|
|
|
try:
|
|
# 1. Repair Segments (Populate NULL geom)
|
|
logger.info("Starting segment geom repair...")
|
|
result = session.execute(text("SELECT id, name, points FROM segments WHERE geom IS NULL"))
|
|
segments_to_fix = result.fetchall()
|
|
|
|
logger.info(f"Found {len(segments_to_fix)} segments with NULL geom.")
|
|
for seg_id, name, points_json in segments_to_fix:
|
|
try:
|
|
points = json.loads(points_json) if isinstance(points_json, str) else points_json
|
|
if not points:
|
|
logger.warning(f"Segment {seg_id} ({name}) has no points. Skipping.")
|
|
continue
|
|
|
|
wkt_coords = [f"{p[0]} {p[1]}" for p in points if len(p) >= 2]
|
|
if not wkt_coords:
|
|
logger.warning(f"Segment {seg_id} ({name}) has invalid points. Skipping.")
|
|
continue
|
|
|
|
geom_wkt = f"SRID=4326;LINESTRING({', '.join(wkt_coords)})"
|
|
session.execute(
|
|
text("UPDATE segments SET geom = ST_GeomFromText(:wkt, 4326) WHERE id = :id"),
|
|
{"wkt": geom_wkt, "id": seg_id}
|
|
)
|
|
logger.info(f"Fixed Segment {seg_id}: {name}")
|
|
except Exception as e:
|
|
logger.error(f"Error fixing Segment {seg_id}: {e}")
|
|
|
|
# 2. Repair Activities (Populate NULL start_lat/lng from streams)
|
|
logger.info("\nStarting activity coordinate repair...")
|
|
# Join with activity_streams to find valid coordinates
|
|
result = session.execute(text("""
|
|
SELECT a.id, a.activity_name, s.latitude, s.longitude
|
|
FROM activities a
|
|
JOIN activity_streams s ON a.id = s.activity_id
|
|
WHERE (a.start_lat IS NULL OR a.start_lng IS NULL)
|
|
"""))
|
|
activities_to_fix = result.fetchall()
|
|
|
|
logger.info(f"Found {len(activities_to_fix)} activities without coordinates but with streams.")
|
|
|
|
fixed_count = 0
|
|
for act_id, name, lats, lons in activities_to_fix:
|
|
try:
|
|
# Find first non-null coord
|
|
start_lat = None
|
|
start_lng = None
|
|
for lat, lon in zip(lats or [], lons or []):
|
|
if lat is not None and lon is not None:
|
|
start_lat = lat
|
|
start_lng = lon
|
|
break
|
|
|
|
if start_lat is not None:
|
|
session.execute(
|
|
text("UPDATE activities SET start_lat = :lat, start_lng = :lng WHERE id = :id"),
|
|
{"lat": start_lat, "lng": start_lng, "id": act_id}
|
|
)
|
|
fixed_count += 1
|
|
except Exception as e:
|
|
logger.error(f"Error fixing Activity {act_id}: {e}")
|
|
|
|
logger.info(f"Successfully fixed {fixed_count} activities.")
|
|
|
|
session.commit()
|
|
logger.info("\nGeodata repair complete.")
|
|
|
|
except Exception as e:
|
|
session.rollback()
|
|
logger.error(f"Critical error during repair: {e}")
|
|
finally:
|
|
session.close()
|
|
|
|
if __name__ == "__main__":
|
|
repair_geodata()
|