CREATE DATABASE IF NOT EXISTS meme_spy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE meme_spy;

CREATE TABLE wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  address VARCHAR(64) NOT NULL,
  chain VARCHAR(20) NOT NULL DEFAULT 'solana',
  label VARCHAR(255) NULL,
  source VARCHAR(50) NULL,
  source_rank INT NULL,
  realized_pnl_usd DECIMAL(18,2) DEFAULT 0,
  win_rate DECIMAL(8,4) DEFAULT 0,
  trade_count INT DEFAULT 0,
  wallet_score DECIMAL(12,4) DEFAULT 0,
  tier TINYINT DEFAULT 3,
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_wallet (address, chain),
  INDEX idx_score (wallet_score),
  INDEX idx_tier (tier),
  INDEX idx_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE wallet_sources (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wallet_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(50) NOT NULL,
  source_rank INT NULL,
  pnl_usd DECIMAL(18,2) NULL,
  win_rate DECIMAL(8,4) NULL,
  trade_count INT NULL,
  captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
  INDEX idx_wallet_source (wallet_id, source),
  INDEX idx_captured (captured_at)
) ENGINE=InnoDB;

CREATE TABLE tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  chain VARCHAR(20) NOT NULL DEFAULT 'solana',
  address VARCHAR(64) NOT NULL,
  name VARCHAR(255) NULL,
  symbol VARCHAR(100) NULL,
  pair_address VARCHAR(64) NULL,
  market_cap_usd DECIMAL(20,2) NULL,
  liquidity_usd DECIMAL(20,2) NULL,
  volume_24h_usd DECIMAL(20,2) NULL,
  first_seen DATETIME NULL,
  last_seen DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_token (address, chain),
  INDEX idx_first_seen (first_seen),
  INDEX idx_market_cap (market_cap_usd)
) ENGINE=InnoDB;

CREATE TABLE wallet_trades (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wallet_id BIGINT UNSIGNED NOT NULL,
  token_id BIGINT UNSIGNED NOT NULL,
  tx_hash VARCHAR(100) NULL,
  side ENUM('BUY','SELL') NOT NULL,
  token_amount DECIMAL(30,10) NULL,
  sol_amount DECIMAL(30,10) NULL,
  usd_value DECIMAL(20,4) NULL,
  token_price_usd DECIMAL(30,15) NULL,
  market_cap_usd DECIMAL(20,2) NULL,
  trade_time DATETIME NOT NULL,
  source VARCHAR(50) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
  FOREIGN KEY (token_id) REFERENCES tokens(id) ON DELETE CASCADE,
  UNIQUE KEY uq_tx_wallet (wallet_id, tx_hash),
  INDEX idx_wallet_time (wallet_id, trade_time),
  INDEX idx_token_time (token_id, trade_time),
  INDEX idx_buy_time (side, trade_time)
) ENGINE=InnoDB;

CREATE TABLE wallet_clusters (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cluster_name VARCHAR(255) NULL,
  confidence DECIMAL(8,4) DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE wallet_cluster_members (
  cluster_id BIGINT UNSIGNED NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  confidence DECIMAL(8,4) DEFAULT 0,
  PRIMARY KEY (cluster_id, wallet_id),
  FOREIGN KEY (cluster_id) REFERENCES wallet_clusters(id) ON DELETE CASCADE,
  FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE consensus_signals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  token_id BIGINT UNSIGNED NOT NULL,
  wallet_count INT DEFAULT 0,
  independent_wallet_count INT DEFAULT 0,
  tier1_count INT DEFAULT 0,
  tier2_count INT DEFAULT 0,
  tier3_count INT DEFAULT 0,
  total_buy_usd DECIMAL(20,2) DEFAULT 0,
  first_wallet_buy DATETIME NULL,
  last_wallet_buy DATETIME NULL,
  consensus_score DECIMAL(12,4) DEFAULT 0,
  market_cap_at_signal DECIMAL(20,2) NULL,
  status ENUM('NEW','ACTIVE','COOLING','INVALIDATED') DEFAULT 'NEW',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (token_id) REFERENCES tokens(id) ON DELETE CASCADE,
  INDEX idx_score (consensus_score),
  INDEX idx_status (status),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE signal_wallets (
  signal_id BIGINT UNSIGNED NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  trade_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (signal_id, wallet_id),
  FOREIGN KEY (signal_id) REFERENCES consensus_signals(id) ON DELETE CASCADE,
  FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE,
  FOREIGN KEY (trade_id) REFERENCES wallet_trades(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE token_outcomes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  signal_id BIGINT UNSIGNED NOT NULL,
  token_id BIGINT UNSIGNED NOT NULL,
  price_at_signal DECIMAL(30,15) NULL,
  price_1h DECIMAL(30,15) NULL,
  price_6h DECIMAL(30,15) NULL,
  price_24h DECIMAL(30,15) NULL,
  peak_price DECIMAL(30,15) NULL,
  return_1h DECIMAL(12,4) NULL,
  return_6h DECIMAL(12,4) NULL,
  return_24h DECIMAL(12,4) NULL,
  peak_return DECIMAL(12,4) NULL,
  recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (signal_id) REFERENCES consensus_signals(id) ON DELETE CASCADE,
  FOREIGN KEY (token_id) REFERENCES tokens(id) ON DELETE CASCADE
) ENGINE=InnoDB;