@ BlockNostr

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROCEDURE [dbo].[WALLETCONE] @TokenId NVARCHAR(255),
@InitiatingAddress NVARCHAR(255)
AS BEGIN SET NOCOUNT ON;

DECLARE 
    @TokenName NVARCHAR(255),
    @TableName NVARCHAR(255),
    @JsonPath  NVARCHAR(300),
    @Sql       NVARCHAR(MAX),
    @StartTime DATETIME = GETDATE();

PRINT 'Procedure started at: ' + CONVERT(NVARCHAR(30), @StartTime, 120);

-- Validate inputs
IF @TokenId IS NULL OR @TokenId = '' 
   OR @InitiatingAddress IS NULL OR @InitiatingAddress = ''
BEGIN
    PRINT 'Error: TokenId and InitiatingAddress cannot be NULL or empty';
    RETURN;
END

-- Resolve token name
SELECT @TokenName = UPPER(REPLACE(Name,' ','_'))
  FROM dbo.DOH_INFO_TOKENS
 WHERE TokenID = @TokenId;

IF @TokenName IS NULL
BEGIN
    PRINT 'Error: Invalid TokenId: ' + @TokenId;
    RETURN;
END

-- Build table name & JSON path
SET @TableName = 'DOH_INFO_TRANSACTIONS_' + @TokenName;
SET @JsonPath  = '$."' + REPLACE(@TokenId,'"','""') + '"';

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @TableName)
BEGIN
    PRINT 'Error: Table not found: ' + @TableName;
    RETURN;
END

----------------------------------------------------------------
-- 1) Pool Transactions Detail
----------------------------------------------------------------
SET @Sql = N'
SELECT
  @TokenId AS token_id,
  t.[hash],
  t.initiating_address,
  COALESCE(p.Name,''Unknown'') AS pool_name,
  t.transaction_type,
  TRY_CAST(COALESCE(JSON_VALUE(t.gotTokens,@JsonPath),''0'') AS FLOAT) AS got_tokens,
  TRY_CAST(COALESCE(JSON_VALUE(t.forTokens,@JsonPath),''0'') AS FLOAT) AS for_tokens,
  TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10)) AS ratio,
  t.[timestamp],
  CASE
    WHEN t.transaction_type LIKE ''%BUY%''  
      THEN -(TRY_CAST(JSON_VALUE(t.gotTokens,@JsonPath) AS FLOAT)
             *TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10)))
    WHEN t.transaction_type LIKE ''%SELL%'' 
      THEN TRY_CAST(JSON_VALUE(t.forTokens,@JsonPath) AS FLOAT)
           *TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10))
    ELSE NULL
  END AS gain_loss
FROM dbo.' + QUOTENAME(@TableName) + ' t
LEFT JOIN dbo.DOH_INFO_POOLS p ON t.pool_id = p.PoolID
WHERE
  (JSON_VALUE(t.gotTokens,@JsonPath) IS NOT NULL OR JSON_VALUE(t.forTokens,@JsonPath) IS NOT NULL)
  AND t.initiating_address = @InitiatingAddress
  AND t.pool_id IS NOT NULL;';
EXEC sp_executesql
  @Sql,
  N'@TokenId NVARCHAR(255),@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @TokenId,@InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 2) Pool Transactions Summary
----------------------------------------------------------------
SET @Sql = N'
SELECT
  COALESCE(p.Name,''Unknown'') AS pool_name,
  t.pool_id,
  COUNT(*) AS transaction_count,
  SUM(CASE
        WHEN t.transaction_type LIKE ''%BUY%''  
          THEN -(TRY_CAST(JSON_VALUE(t.gotTokens,@JsonPath) AS FLOAT)
                 *TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10)))
        WHEN t.transaction_type LIKE ''%SELL%'' 
          THEN TRY_CAST(JSON_VALUE(t.forTokens,@JsonPath) AS FLOAT)
               *TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10))
        ELSE 0
      END) AS total_gain_loss,
  AVG(CASE WHEN t.transaction_type LIKE ''%BUY%''  
           THEN TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10)) ELSE NULL END) AS avg_buy_price,
  AVG(CASE WHEN t.transaction_type LIKE ''%SELL%'' 
           THEN TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10)) ELSE NULL END) AS avg_sell_price,
  SUM(CASE
        WHEN t.transaction_type LIKE ''%BUY%''  
          THEN TRY_CAST(JSON_VALUE(t.gotTokens,@JsonPath) AS FLOAT)
        WHEN t.transaction_type LIKE ''%SELL%'' 
          THEN TRY_CAST(JSON_VALUE(t.forTokens,@JsonPath) AS FLOAT)
        ELSE 0
      END) AS total_tokens_transacted
FROM dbo.' + QUOTENAME(@TableName) + ' t
LEFT JOIN dbo.DOH_INFO_POOLS p ON t.pool_id = p.PoolID
WHERE
  (JSON_VALUE(t.gotTokens,@JsonPath) IS NOT NULL OR JSON_VALUE(t.forTokens,@JsonPath) IS NOT NULL)
  AND t.initiating_address = @InitiatingAddress
  AND t.pool_id IS NOT NULL
GROUP BY COALESCE(p.Name,''Unknown''), t.pool_id
ORDER BY total_gain_loss DESC, COALESCE(p.Name,''Unknown'') ASC;';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 3) Non-Pool Transactions Detail (all direct txns)
----------------------------------------------------------------
SET @Sql = N'
SELECT
  @TokenId AS token_id,
  t.[hash],
  t.initiating_address,
  t.receiving_address,
  t.transaction_type,
  TRY_CAST(COALESCE(JSON_VALUE(t.sent_tokens,@JsonPath),''0'') AS FLOAT) AS sent_tokens,
  TRY_CAST(COALESCE(JSON_VALUE(t.received_tokens,@JsonPath),''0'') AS FLOAT) AS received_tokens,
  TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10)) AS ratio,
  t.[timestamp],
  t.status
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  (JSON_VALUE(t.sent_tokens,@JsonPath) IS NOT NULL OR JSON_VALUE(t.received_tokens,@JsonPath) IS NOT NULL)
  AND t.initiating_address = @InitiatingAddress
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'');';
EXEC sp_executesql
  @Sql,
  N'@TokenId NVARCHAR(255),@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @TokenId,@InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 4) Non-Pool Sent Summary (you → others)
----------------------------------------------------------------
SET @Sql = N'
SELECT
  t.initiating_address AS sender_address,
  t.receiving_address  AS receiver_address,
  COUNT(*)             AS send_count,
  SUM(TRY_CAST(JSON_VALUE(t.sent_tokens,@JsonPath) AS FLOAT)) AS total_sent,
  MIN(t.[timestamp])   AS first_sent,
  MAX(t.[timestamp])   AS last_sent
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  JSON_VALUE(t.sent_tokens,@JsonPath) IS NOT NULL
  AND t.initiating_address = @InitiatingAddress
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
GROUP BY t.initiating_address, t.receiving_address
ORDER BY send_count DESC, total_sent DESC;';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 5) Non-Pool Received Summary (others → you, no self-sends)
----------------------------------------------------------------
SET @Sql = N'
SELECT
  t.initiating_address AS sender_address,
  @InitiatingAddress   AS receiver_address,
  COUNT(*)             AS send_count,
  SUM(TRY_CAST(JSON_VALUE(t.received_tokens,@JsonPath) AS FLOAT)) AS total_sent,
  MIN(t.[timestamp])   AS first_sent,
  MAX(t.[timestamp])   AS last_sent
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  JSON_VALUE(t.received_tokens,@JsonPath) IS NOT NULL
  AND t.receiving_address = @InitiatingAddress
  AND t.initiating_address <> @InitiatingAddress
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
GROUP BY t.initiating_address
ORDER BY send_count DESC, total_sent DESC;';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 6) Non-Pool Received Detail (every incoming txn)
----------------------------------------------------------------
SET @Sql = N'
SELECT
  @TokenId AS token_id,
  t.[hash],
  t.initiating_address AS sender_address,
  t.receiving_address  AS receiver_address,
  t.transaction_type,
  TRY_CAST(COALESCE(JSON_VALUE(t.sent_tokens,@JsonPath),''0'') AS FLOAT) AS sent_tokens,
  TRY_CAST(COALESCE(JSON_VALUE(t.received_tokens,@JsonPath),''0'') AS FLOAT) AS received_tokens,
  TRY_CAST(CAST(t.ratio AS FLOAT) AS DECIMAL(38,10)) AS ratio,
  t.[timestamp],
  t.status
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  JSON_VALUE(t.received_tokens,@JsonPath) IS NOT NULL
  AND t.receiving_address = @InitiatingAddress
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
ORDER BY t.[timestamp] DESC;';
EXEC sp_executesql
  @Sql,
  N'@TokenId NVARCHAR(255),@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @TokenId,@InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 7) Net Token Position Summary
----------------------------------------------------------------
SET @Sql = N'
SELECT
  @InitiatingAddress                             AS wallet,
  SUM(TRY_CAST(JSON_VALUE(t.received_tokens,@JsonPath) AS FLOAT))
  - SUM(TRY_CAST(JSON_VALUE(t.sent_tokens,@JsonPath) AS FLOAT))     AS net_token_position,
  (SUM(TRY_CAST(JSON_VALUE(t.received_tokens,@JsonPath) AS FLOAT))
  - SUM(TRY_CAST(JSON_VALUE(t.sent_tokens,@JsonPath) AS FLOAT)))
  * AVG(CAST(t.ratio AS FLOAT))                   AS estimated_net_value
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  (JSON_VALUE(t.sent_tokens,@JsonPath) IS NOT NULL
   OR JSON_VALUE(t.received_tokens,@JsonPath) IS NOT NULL)
  AND (t.initiating_address = @InitiatingAddress OR t.receiving_address = @InitiatingAddress)
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'');
';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 8) Unique Counterparty Count
----------------------------------------------------------------
SET @Sql = N'
SELECT
  COUNT(DISTINCT
    CASE
      WHEN t.initiating_address = @InitiatingAddress THEN t.receiving_address
      ELSE t.initiating_address
    END
  ) AS unique_counterparties
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  (t.initiating_address = @InitiatingAddress OR t.receiving_address = @InitiatingAddress)
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'');
';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255)',
  @InitiatingAddress;

----------------------------------------------------------------
-- 9) Token Activity Timeline (monthly)
----------------------------------------------------------------
SET @Sql = N'
SELECT
  FORMAT(t.[timestamp],''yyyy-MM'') AS month,
  COUNT(*)                         AS transaction_count,
  SUM(
    COALESCE(TRY_CAST(JSON_VALUE(t.sent_tokens,@JsonPath) AS FLOAT),0) +
    COALESCE(TRY_CAST(JSON_VALUE(t.received_tokens,@JsonPath) AS FLOAT),0)
  )                                AS total_volume
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  (t.initiating_address = @InitiatingAddress OR t.receiving_address = @InitiatingAddress)
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
GROUP BY FORMAT(t.[timestamp],''yyyy-MM'')
ORDER BY month;';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 10) Top Volume Counterparties
----------------------------------------------------------------
SET @Sql = N'
SELECT
  CASE 
    WHEN t.initiating_address = @InitiatingAddress THEN t.receiving_address 
    ELSE t.initiating_address 
  END AS counterparty,
  COUNT(*) AS tx_count,
  SUM(
    COALESCE(TRY_CAST(JSON_VALUE(t.sent_tokens,@JsonPath) AS FLOAT),0) +
    COALESCE(TRY_CAST(JSON_VALUE(t.received_tokens,@JsonPath) AS FLOAT),0)
  ) AS total_volume
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  (t.initiating_address = @InitiatingAddress OR t.receiving_address = @InitiatingAddress)
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
GROUP BY 
  CASE 
    WHEN t.initiating_address = @InitiatingAddress THEN t.receiving_address 
    ELSE t.initiating_address 
  END
ORDER BY total_volume DESC;';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 11) Largest Single Transactions
----------------------------------------------------------------
-- 11a) Largest Received
SET @Sql = N'
SELECT TOP 1 *
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE 
  t.receiving_address = @InitiatingAddress
  AND JSON_VALUE(t.received_tokens,@JsonPath) IS NOT NULL
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
ORDER BY TRY_CAST(JSON_VALUE(t.received_tokens,@JsonPath) AS FLOAT) DESC;';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

-- 11b) Largest Sent
SET @Sql = N'
SELECT TOP 1 *
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE 
  t.initiating_address = @InitiatingAddress
  AND JSON_VALUE(t.sent_tokens,@JsonPath) IS NOT NULL
  AND (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
ORDER BY TRY_CAST(JSON_VALUE(t.sent_tokens,@JsonPath) AS FLOAT) DESC;';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

----------------------------------------------------------------
-- 12) SNIPER: Transactions before first pool txn
----------------------------------------------------------------
SET @Sql = N'
SELECT
  COUNT(*) AS SNIPER
FROM dbo.' + QUOTENAME(@TableName) + ' t
WHERE
  (t.pool_id IS NULL OR t.pool_id = '''' OR LOWER(t.status) = ''no pool'')
  AND (t.initiating_address = @InitiatingAddress OR t.receiving_address = @InitiatingAddress)
  AND t.[timestamp] < (
    SELECT MIN(tp.[timestamp])
    FROM dbo.' + QUOTENAME(@TableName) + ' tp
    WHERE tp.initiating_address = @InitiatingAddress
      AND tp.pool_id IS NOT NULL
  );';
EXEC sp_executesql
  @Sql,
  N'@InitiatingAddress NVARCHAR(255),@JsonPath NVARCHAR(300)',
  @InitiatingAddress,@JsonPath;

PRINT 'Procedure completed at: ' 
  + CONVERT(NVARCHAR(30), GETDATE(), 120)
  + ', duration (ms): ' 
  + CAST(DATEDIFF(MILLISECOND, @StartTime, GETDATE()) AS NVARCHAR(10));

END GO