#!/usr/bin/env python3
"""Export today's FT360 device positions as GeoJSON for the dashboard map overlay."""
import os, subprocess, json, re
from datetime import datetime
from pathlib import Path

APP2='152.53.39.202'
SSH_KEY='/root/.ssh/itpp-infra'
H2='/tmp/h2.jar'
LOCAL_DIR='/tmp/ft360_geojson'
DB=os.path.join(LOCAL_DIR,'database.mv.db')
OUTPUT=Path('/var/www/ops/data/ft360-route.json')
DEVICE_ID=1

os.makedirs(LOCAL_DIR,exist_ok=True)
subprocess.run(['scp','-o','StrictHostKeyChecking=no','-o','ConnectTimeout=15','-i',SSH_KEY,
    f'root@{APP2}:/root/docker/traccar/traccar-data/database.mv.db',DB],check=True,timeout=40)

def query(sql):
    jdbc=f'jdbc:h2:{os.path.join(LOCAL_DIR,"database")};IFEXISTS=TRUE'
    p=subprocess.run(['java','-cp',H2,'org.h2.tools.Shell','-url',jdbc,'-user','sa','-password','','-sql',sql],
        text=True,capture_output=True,timeout=40)
    out=p.stdout if p.returncode==0 else ''
    rows=[]
    for line in out.splitlines():
        s=line.strip()
        if not s or s.startswith('(') or s.startswith('---'): continue
        cells=[c.strip() for c in s.split('|')]
        if cells and cells[0].upper() in {'DEVICETIME','LATITUDE','LONGITUDE','SPEED','COUNT(*)'}:
            continue
        if all(not c for c in cells): continue
        rows.append(cells)
    return rows

positions = query(f"""
    SELECT devicetime, latitude, longitude, speed
    FROM tc_positions
    WHERE deviceid={DEVICE_ID} AND devicetime >= CURRENT_DATE
      AND latitude IS NOT NULL AND longitude IS NOT NULL
    ORDER BY devicetime ASC
""")

features = []
for row in positions:
    if len(row) < 4: continue
    try:
        dt=row[0][:19]
        lat=float(row[1])
        lon=float(row[2])
        spd=float(row[3]) if row[3] else 0.0
    except (ValueError,IndexError):
        continue
    features.append({
        "type":"Feature",
        "geometry":{"type":"Point","coordinates":[lon,lat]},
        "properties":{
            "time":dt,
            "speed_kn":round(spd,1),
            "speed_mph":round(spd*1.15078,1)
        }
    })

# Build line segments between consecutive points (grouped into trips where gap < 30 min)
lines = []
if len(features) >= 2:
    segments = []
    current_seg = [[features[0]["geometry"]["coordinates"][0],features[0]["geometry"]["coordinates"][1]]]
    from datetime import datetime as dt
    for i in range(1,len(features)):
        try:
            t1=dt.strptime(features[i-1]["properties"]["time"],"%Y-%m-%d %H:%M:%S")
            t2=dt.strptime(features[i]["properties"]["time"],"%Y-%m-%d %H:%M:%S")
            gap=(t2-t1).total_seconds()
        except:
            gap=0
        if gap > 1800 or gap < 0:  # new trip if > 30 min gap
            if len(current_seg) >= 2:
                segments.append(current_seg)
            current_seg=[[features[i]["geometry"]["coordinates"][0],features[i]["geometry"]["coordinates"][1]]]
        else:
            current_seg.append([features[i]["geometry"]["coordinates"][0],features[i]["geometry"]["coordinates"][1]])
    if len(current_seg) >= 2:
        segments.append(current_seg)

    for j,seg in enumerate(segments):
        lines.append({
            "type":"Feature",
            "geometry":{"type":"LineString","coordinates":seg},
            "properties":{"trip":j+1}
        })

geojson={
    "type":"FeatureCollection",
    "generated_at":datetime.now().isoformat(),
    "features":features+lines,
    "metadata":{
        "device_id":DEVICE_ID,
        "total_points":len(features),
        "total_trips":len(lines),
        "date":datetime.now().strftime("%Y-%m-%d")
    }
}

OUTPUT.parent.mkdir(parents=True,exist_ok=True)
tmp=OUTPUT.with_suffix(".tmp")
tmp.write_text(json.dumps(geojson))
tmp.replace(OUTPUT)
print(json.dumps({"ok":True,"points":len(features),"trips":len(lines),"output":str(OUTPUT)}))