#!/usr/bin/env python
# coding: utf-8

# In[12]:


from datetime import datetime
import pandas as pd
from tms_data_interface import SQLQueryInterface

query = """
WITH 
-- Capture all orders and trades within the spoofing time window
trade_window AS (
    SELECT
        t.trade_id,
        t.trader_id,
        t.date_time AS trade_time,
        t.trade_side,
        t.trade_volume,
        o.trader_id AS order_trader_id,
        o.date_time AS order_time,
        o.order_volume,
        o.order_status,
        o.order_price,
        o.side AS order_side
    FROM 
        {trade_10m_v3} t
    LEFT JOIN 
        order_10m o ON o.date_time BETWEEN t.date_time - INTERVAL '{spoofing_time_window_s}' SECOND 
                                          AND t.date_time
    WHERE 
        o.side = '{spoofing_side}'
),

-- Calculate net order volume for the specific trader
net_order_volume_cte AS (
    SELECT 
        trader_id,
        trade_id,
        trade_time,
        SUM(CASE 
            WHEN order_status = 'new' THEN order_volume 
            WHEN order_status = 'cancelled' THEN -order_volume 
            WHEN order_status = 'fulfilled' THEN -order_volume 
            ELSE 0 
        END) AS net_order_volume,
        COUNT(*) AS num_orders
    FROM trade_window
    WHERE order_trader_id = trader_id  -- Filter by the trader who executed the trade
    GROUP BY trader_id, trade_id, trade_time
),

-- Calculate total net order volume for all traders (i.e., for spoofing side orders)
net_order_volume_all_cte AS (
    SELECT 
        trade_id,
        SUM(CASE 
            WHEN order_status = 'new' THEN order_volume 
            WHEN order_status = 'cancelled' THEN -order_volume 
            WHEN order_status = 'fulfilled' THEN -order_volume 
            ELSE 0 
        END) AS net_order_volume_all
    FROM trade_window
    GROUP BY trade_id
),

-- Calculate total trade volume on the opposite side (e.g., sell if spoofing is on buy)
opposite_trade_volume_cte AS (
    SELECT 
        t.trader_id,
        t.trade_id,
        SUM(t.trade_volume) AS total_trade_volume
    FROM {trade_10m_v3} t
    WHERE 
        t.date_time BETWEEN t.date_time - INTERVAL '{trade_time_window_s}' SECOND
                        AND t.date_time
        AND t.trade_side = CASE WHEN '{spoofing_side}' = 'buy' THEN 'sell' ELSE 'buy' END
    GROUP BY t.trader_id, t.trade_id
)

-- Final result with calculated spoofing indicators
SELECT
    n.trade_id,
    n.trader_id,
    n.trade_time,
    n.num_orders,
    n.net_order_volume,
    CASE 
        WHEN o.total_trade_volume > 0 THEN n.net_order_volume / o.total_trade_volume
        ELSE NULL
    END AS order_trade_ratio,
    CASE 
        WHEN a.net_order_volume_all > 0 THEN n.net_order_volume / a.net_order_volume_all
        ELSE NULL
    END AS volume_percentage
FROM 
    net_order_volume_cte n
LEFT JOIN 
    opposite_trade_volume_cte o ON n.trade_id = o.trade_id
LEFT JOIN 
    net_order_volume_all_cte a ON n.trade_id = a.trade_id
WHERE 
    n.net_order_volume > 0  -- Only consider positive net order volumes (potential spoofing);
    limit 100
"""

class Scenario:
    seq = SQLQueryInterface(schema="trade_schema")

    def logic(self, **params):
        spoofing_time_window = params.get('spoofing_time_window', 300000)  # default to 300,000 ms (5 minutes)
        spoofing_side = params.get('spoofing_side', 'buy')
        use_volume_for_order_trade_ratio = params.get('use_volume_for_order_trade_ratio', True)
        trade_time_window = params.get('trade_time_window', 300000)
        ignore_trade_after_spoofing = params.get('ignore_trade_after_spoofing', True)
        ignore_price_improvement = params.get('ignore_price_improvement', True)

        # Convert time windows from milliseconds to seconds
        spoofing_time_window_s = int(spoofing_time_window / 1000)
        trade_time_window_s = int(trade_time_window / 1000)

        query_start_time = datetime.now()
        print("Query start time:", query_start_time)

        # Execute the query with the parameters passed from `params`
        row_list = self.seq.execute_raw(query.format(
            trade_10m_v3="trade_10m_v3",  # Replace with actual table name
            spoofing_time_window_s=spoofing_time_window_s,
            trade_time_window_s=trade_time_window_s,
            spoofing_side=spoofing_side
        ))

        # Define columns for the resulting DataFrame
        cols = [
            'trade_id', 'focal_id', 'trade_time', 'num_orders', 
            'net_order_volume', 'order_trade_ratio', 'volume_percentage'
        ]

        # Create a DataFrame from the query result
        final_scenario_df = pd.DataFrame(row_list, columns=cols)


        # Adding additional columns
        final_scenario_df['segment'] = 'Default'
        final_scenario_df['sar_flag'] = 'N'
        final_scenario_df['risk'] = 'Low Risk'

        return final_scenario_df


# In[ ]:


scenario = Scenario()
scenario.logic(spoofing_time_window = 300000,
               spoofing_side = 'buy',
               use_volume_for_order_trade_ratio = True,
               trade_time_window = 300000,
               ignore_trade_after_spoofing = True,
               ignore_price_improvement = True)


# In[ ]: