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()