import sys
import os
import json
import logging
from sqlalchemy import text # Import text for raw SQL

# Add backend to path
sys.path.append(os.path.join(os.path.dirname(__file__), '..'))

from src.services.postgresql_manager import PostgreSQLManager
from src.models.segment import Segment
from src.utils.config import config

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def main():
    logger.info("Starting Segment geometry backfill...")
    db_manager = PostgreSQLManager(config.DATABASE_URL)
    
    with db_manager.get_db_session() as session:
        segments = session.query(Segment).filter(Segment.geom == None).all()
        logger.info(f"Found {len(segments)} segments needing geometry backfill.")
        
        count = 0
        for seg in segments:
            try:
                points_data = json.loads(seg.points) if isinstance(seg.points, str) else seg.points
                if not points_data or len(points_data) < 2:
                    logger.warning(f"Segment {seg.id} has insufficient points.")
                    continue
                
                # Points format: [[lon, lat], ...] or [[lon, lat, ele], ...]
                # WKT: LINESTRING(lon lat, lon lat, ...)
                coords = []
                for p in points_data:
                    if len(p) >= 2:
                        coords.append(f"{p[0]} {p[1]}")
                
                if coords:
                    wkt = f"SRID=4326;LINESTRING({', '.join(coords)})"
                    # We can set string to geom column if using geoalchemy2, it handles WKT
                    seg.geom = wkt
                    count += 1
            except Exception as e:
                logger.error(f"Error processing segment {seg.id}: {e}")
        
        session.commit()
        logger.info(f"Backfilled {count} segments.")

if __name__ == "__main__":
    main()