import os
import sys
from sqlalchemy import create_engine, text
from datetime import datetime
import json

# Database connection
DATABASE_URL = "postgresql://habitforge:habitforge@localhost:5432/habitforge"

def check_sync_status():
    try:
        engine = create_engine(DATABASE_URL)
        with engine.connect() as connection:
            print("\n=== SYNC STATUS REPORT ===")
            
            # 1. Check Sync Logs
            print("\n--- Latest Sync Session ---")
            query_logs = text("""
                SELECT id, status, sync_timestamp, records_pushed, error_message 
                FROM health_connect_sync_logs 
                ORDER BY sync_timestamp DESC 
                LIMIT 1
            """)
            result_logs = connection.execute(query_logs).fetchone()
            
            if result_logs:
                print(f"Sync ID: {result_logs[0]}")
                print(f"Status: {result_logs[1]}")
                print(f"Timestamp: {result_logs[2]}")
                print(f"Records Pushed: {result_logs[3]}")
                if result_logs[4]:
                    print(f"Error: {result_logs[4]}")
                
                sync_id = result_logs[0]
            else:
                print("No sync logs found.")
                return

            # 2. Check Raw Tables
            tables = [
                "hc_raw_steps",
                "hc_raw_heart_rate",
                "hc_raw_resting_hr",
                "hc_raw_sleep_sessions", 
                "hc_raw_distance",
                "hc_raw_calories",
                "hc_raw_weight",
                "hc_raw_spo2",
                "hc_raw_exercise_sessions",
                "hc_raw_nutrition",
                "hc_raw_hydration"
            ]
            
            print("\n--- Raw Data Counts (All Time) ---")
            for table in tables:
                try:
                    # Check if table exists first (handling case where some tables might not be created yet)
                    query_count = text(f"SELECT COUNT(*), MIN(start_time), MAX(start_time) FROM {table}")
                    result_count = connection.execute(query_count).fetchone()
                    count = result_count[0]
                    min_time = result_count[1]
                    max_time = result_count[2]
                    print(f"{table}: {count} records (Latest: {max_time})")
                except Exception as e:
                    connection.rollback()
                    print(f"{table}: Error querying (might not exist or empty) - {str(e).splitlines()[0]}")

            # 3. Specific check for the user's main concern: Steps from the latest sync
            print("\n--- Steps from Latest Sync ---")
            if sync_id:
                query_steps_sync = text(f"SELECT COUNT(*) FROM hc_raw_steps WHERE sync_id = :sync_id")
                steps_synced_count = connection.execute(query_steps_sync, {"sync_id": sync_id}).scalar()
                print(f"Steps added in last sync (ID {sync_id}): {steps_synced_count}")
            
    except Exception as e:
        print(f"CRITICAL ERROR: {e}")

if __name__ == "__main__":
    check_sync_status()