-- Core trades ledger
CREATE TABLE trades (
  id TEXT PRIMARY KEY,
  request_id TEXT NOT NULL,
  exchange TEXT NOT NULL,           -- 'bybit', 'binance', 'mexc'
  symbol TEXT NOT NULL,
  action TEXT NOT NULL,             -- 'LONG', 'SHORT', 'CLOSE'
  side TEXT NOT NULL,               -- 'BUY' or 'SELL'
  quantity REAL NOT NULL,
  price REAL NOT NULL,
  leverage INTEGER DEFAULT 1,
  fee REAL NOT NULL DEFAULT 0,
  order_id TEXT NOT NULL,
  status TEXT NOT NULL,             -- 'Filled', 'Rejected', 'Failed', 'Partial'
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Active positions tracking
CREATE TABLE positions (
  id TEXT PRIMARY KEY,
  symbol TEXT NOT NULL UNIQUE,
  side TEXT NOT NULL,
  quantity REAL NOT NULL,
  entry_price REAL NOT NULL,
  mark_price REAL DEFAULT 0,
  leverage INTEGER DEFAULT 1,
  unrealized_pnl REAL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Account balance snapshots
CREATE TABLE balances (
  id TEXT PRIMARY KEY,
  total_balance REAL NOT NULL,
  available_balance REAL NOT NULL,
  unrealized_pnl REAL DEFAULT 0,
  snapshot_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Incoming signal audit trail
CREATE TABLE trade_signals (
  id TEXT PRIMARY KEY,
  raw_payload TEXT NOT NULL,
  parsed_symbol TEXT,
  parsed_action TEXT,
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System diagnostic logs
CREATE TABLE system_logs (
  id TEXT PRIMARY KEY,
  level TEXT NOT NULL,              -- 'info', 'warn', 'error'
  source TEXT NOT NULL,             -- Worker name
  message TEXT NOT NULL,
  metadata TEXT,                    -- JSON blob of additional context
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

hoox db apply

# 2. Deploy schema and seed tables directly to live production Cloudflare D1
hoox db apply --remote

workers/d1-worker/src/drizzle/
├── 0001_initial_schema.sql
├── 0002_add_leverage_column.sql
├── 0003_balances_snapshot_table.sql
└── meta/
    └── snapshot.json

# List all applied and pending database migrations in production
hoox db migrate status --remote

# Apply all pending schema migrations sequentially to production D1
hoox db migrate --remote

# Rollback the most recent migration (use with caution)
hoox db migrate rollback --remote

# A. List all active tables in your production database
hoox db list --remote

# B. Count the total number of executed trades
hoox db query "SELECT COUNT(*) FROM trades" --remote

# C. Inspect the 5 most recent trade fills with formatting
hoox db query "SELECT created_at, symbol, action, price, quantity FROM trades ORDER BY created_at DESC LIMIT 5" --remote

# D. Check currently open positions on Bybit
hoox db query "SELECT symbol, side, size, entry_price FROM positions WHERE size > 0" --remote

# E. Calculate daily P&L
hoox db query "SELECT DATE(created_at) as day, SUM(CASE WHEN side='BUY' THEN -price*quantity ELSE price*quantity END) as daily_pnl FROM trades WHERE status='Filled' GROUP BY day ORDER BY day DESC LIMIT 7" --remote

# F. Export trades as CSV
hoox db query "SELECT * FROM trades ORDER BY created_at DESC" --remote --format csv > trades_export.csv

-- Win rate calculation
SELECT
  COUNT(CASE WHEN realized_pnl > 0 THEN 1 END) * 100.0 / COUNT(*) AS win_rate_pct
FROM trades WHERE status = 'Filled';

-- Average trade size by exchange
SELECT exchange, AVG(quantity * price) as avg_trade_size
FROM trades WHERE status = 'Filled'
GROUP BY exchange;

-- Hourly trading distribution (for optimal timing)
SELECT strftime('%H', created_at) as hour, COUNT(*) as trade_count
FROM trades WHERE status = 'Filled'
GROUP BY hour ORDER BY hour;

# Export the entire D1 database as a clean SQL script
hoox db export --remote

# Export to a specific file path
hoox db export --remote --output backups/my-backup.sql

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS trades (...);
INSERT INTO trades VALUES(...);
COMMIT;

# 1. Create a fresh database first
hoox infra d1 create hoox-db-backup

# 2. Apply the backup SQL
bunx wrangler d1 execute hoox-db-backup --file=backups/db-backup-2026-05-19.sql

# Wipe all tables in the local development database
hoox db reset --confirm

# Wipe all tables in the live production D1 database (USE WITH EXTREME CAUTION)
hoox db reset --remote --confirm

# Truncate all data tables while keeping schema intact
hoox db query "DELETE FROM trades; DELETE FROM positions; DELETE FROM balances; DELETE FROM system_logs;" --remote