#!/usr/bin/env python3
"""Compute daily stats from the Traccar H2 DB for the FT360 dashboard export."""
import os, subprocess, json, math
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_daily_stats'
DB=os.path.join(LOCAL_DIR,'database.mv.db')

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 {'ID','DEVICEID','LATITUDE','NAME','COUNT(*)'}: continue
        if all(not c for c in cells): continue
        rows.append(cells)
    return rows

DEVICE_ID=1

# Today's positions ordered by devicetime
today_rows = query(f"""
    SELECT devicetime, latitude, longitude, speed, attributes
    FROM tc_positions
    WHERE deviceid={DEVICE_ID}
      AND devicetime >= CURRENT_DATE
    ORDER BY devicetime ASC
""")

# Extract totalDistance deltas
first_td = None
last_td = None
max_speed = 0.0
trip_segments = 0
positions_with_speed = 0
prev_time = None

for row in today_rows:
    if len(row) < 4: continue
    try:
        dt = datetime.strptime(row[0][:19], '%Y-%m-%d %H:%M:%S')
        speed = float(row[3]) if row[3] else 0.0
        km_speed = speed * 1.852  # knots to km/h
        mph_speed = km_speed * 0.621371
        attrs = row[4] if len(row) > 4 else ''
    except (ValueError, IndexError):
        continue

    if mph_speed > 0.5:
        positions_with_speed += 1
        if mph_speed > max_speed:
            max_speed = mph_speed
        if prev_time and mph_speed > 1.0:
            gap = (dt - prev_time).total_seconds()
            if gap > 0 and gap < 3600:
                trip_segments += 1

    # Extract totalDistance from attributes JSON
    td = None
    if attrs:
        try:
            import re
            m = re.search(r'"totalDistance":([0-9.]+)', attrs)
            if m:
                td = float(m.group(1))
        except: pass

    if td is not None:
        if first_td is None: first_td = td
        last_td = td

    prev_time = dt

total_miles = 0.0
if first_td is not None and last_td is not None:
    total_miles = (last_td - first_td) * 0.000621371  # meters to miles
    total_miles = round(total_miles, 2)

result = {
    "date": datetime.now().strftime('%Y-%m-%d'),
    "device_id": DEVICE_ID,
    "total_positions_today": len(today_rows),
    "miles_traveled": total_miles,
    "max_speed_mph": round(max_speed, 1),
    "moving_segments": trip_segments,
    "positions_with_movement": positions_with_speed,
    "notes": "Miles from Traccar totalDistance. Steps and active time not available from OsmAnd protocol.",
}
print(json.dumps(result, indent=2))