import duckdb
import pandas as pd

def check_revenue():
    conn = duckdb.connect('analytics.duckdb')
    try:
        conn.execute("INSTALL httpfs; LOAD httpfs;")
        conn.execute("SET s3_region='sgp1';")
        conn.execute("SET s3_endpoint='sgp1.digitaloceanspaces.com';")
        conn.execute("SET s3_url_style='path';")
        
        # Check top riders by revenue
        print("--- TOP 10 RIDERS BY ORDERAMOUNT ---")
        sql = """
        SELECT 
            ridername, 
            SUM(orderamount) as total_order_value,
            COUNT(*) as total_deliveries,
            SUM(deliveryamt) as total_delivery_fees
        FROM read_parquet('s3://nearle/parquet/deliveries/*.parquet', union_by_name = true)
        WHERE ridername IS NOT NULL AND ridername != ''
        GROUP BY 1 
        ORDER BY 2 DESC 
        LIMIT 10
        """
        df = conn.execute(sql).df()
        print(df)
        
    except Exception as e:
        print(f"Error: {e}")
    finally:
        conn.close()

if __name__ == "__main__":
    check_revenue()