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

CREATE TABLE IF NOT EXISTS settings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT,
  guide TEXT,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS slots (
  id INT PRIMARY KEY AUTO_INCREMENT,
  slot_name VARCHAR(120) NOT NULL,
  status ENUM('enabled','disabled') NOT NULL DEFAULT 'enabled',
  slot_mode ENUM('paper','global') NOT NULL DEFAULT 'paper',
  priority INT NOT NULL DEFAULT 1,
  leverage INT NOT NULL DEFAULT 5,
  max_amount_inr DECIMAL(18,2) NOT NULL DEFAULT 5000.00,
  price_rise_pct DECIMAL(10,2) NOT NULL DEFAULT 30.00,
  pump_time_seconds INT NOT NULL DEFAULT 300,
  high_tracking_seconds INT NULL DEFAULT 300,
  price_fall_pct DECIMAL(10,2) NOT NULL DEFAULT 3.00,
  fixed_stop_loss_inr DECIMAL(18,2) NOT NULL DEFAULT 300.00,
  profit_rule ENUM('fixed','trailing') NOT NULL DEFAULT 'trailing',
  profit_pct DECIMAL(10,2) NOT NULL DEFAULT 2.00,
  trailing_start_pct DECIMAL(10,2) NOT NULL DEFAULT 2.00,
  trailing_distance_pct DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  max_trade_duration_seconds INT NULL DEFAULT 300,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_slots_status (status),
  INDEX idx_slots_priority (price_rise_pct, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS symbols (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id BIGINT NULL,
  symbol VARCHAR(80) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  contract_type VARCHAR(80) NULL,
  state VARCHAR(60) NULL,
  max_leverage INT NOT NULL DEFAULT 1,
  tick_size DECIMAL(24,12) NULL,
  lot_size DECIMAL(24,12) NULL,
  last_price DECIMAL(24,12) NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_symbols_leverage (max_leverage),
  INDEX idx_symbols_state (state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS price_ticks (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  symbol VARCHAR(80) NOT NULL,
  price DECIMAL(24,12) NOT NULL,
  tick_time DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ticks_symbol_time (symbol, tick_time),
  INDEX idx_ticks_time (tick_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pump_events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  slot_id INT NOT NULL,
  symbol VARCHAR(80) NOT NULL,
  pump_start_price DECIMAL(24,12) NOT NULL,
  pump_high_price DECIMAL(24,12) NOT NULL,
  pump_pct DECIMAL(10,2) NOT NULL,
  detected_at DATETIME NOT NULL,
  expires_at DATETIME NULL,
  status ENUM('tracking','expired','entered') NOT NULL DEFAULT 'tracking',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (slot_id) REFERENCES slots(id) ON DELETE CASCADE,
  INDEX idx_pump_symbol_status (symbol, status),
  INDEX idx_pump_slot_symbol (slot_id, symbol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS trades (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  pump_event_id BIGINT NULL,
  slot_id INT NOT NULL,
  slot_name VARCHAR(120) NOT NULL,
  symbol VARCHAR(80) NOT NULL,
  direction ENUM('SELL') NOT NULL DEFAULT 'SELL',
  mode ENUM('paper','live') NOT NULL DEFAULT 'paper',
  leverage INT NOT NULL,
  margin_amount_inr DECIMAL(18,2) NOT NULL,
  effective_exposure_inr DECIMAL(18,2) NOT NULL,
  pump_start_price DECIMAL(24,12) NOT NULL,
  pump_high_price DECIMAL(24,12) NOT NULL,
  pump_pct DECIMAL(10,2) NOT NULL,
  entry_price DECIMAL(24,12) NOT NULL,
  exit_price DECIMAL(24,12) NULL,
  stop_loss_amount_inr DECIMAL(18,2) NOT NULL,
  profit_rule ENUM('fixed','trailing') NOT NULL,
  profit_pct DECIMAL(10,2) NOT NULL,
  trailing_start_pct DECIMAL(10,2) NOT NULL,
  trailing_distance_pct DECIMAL(10,2) NOT NULL,
  max_trade_duration_seconds INT NULL,
  best_price DECIMAL(24,12) NULL,
  trailing_stop_price DECIMAL(24,12) NULL,
  entry_time DATETIME NOT NULL,
  exit_time DATETIME NULL,
  holding_duration_seconds INT NULL,
  exit_reason VARCHAR(120) NULL,
  gross_pl_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  net_pl_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  status ENUM('open','closed','blocked') NOT NULL DEFAULT 'open',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (slot_id) REFERENCES slots(id) ON DELETE CASCADE,
  FOREIGN KEY (pump_event_id) REFERENCES pump_events(id) ON DELETE SET NULL,
  INDEX idx_trades_status_symbol (status, symbol),
  INDEX idx_trades_entry_time (entry_time),
  INDEX idx_trades_slot (slot_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS trade_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  trade_id BIGINT NULL,
  pump_event_id BIGINT NULL,
  level VARCHAR(20) NOT NULL DEFAULT 'INFO',
  message TEXT NOT NULL,
  context TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (trade_id) REFERENCES trades(id) ON DELETE CASCADE,
  FOREIGN KEY (pump_event_id) REFERENCES pump_events(id) ON DELETE SET NULL,
  INDEX idx_trade_logs_trade (trade_id),
  INDEX idx_trade_logs_event (pump_event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  service VARCHAR(80) NOT NULL,
  endpoint VARCHAR(255) NOT NULL,
  status_code INT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  message TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_api_logs_service_time (service, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS daily_summary (
  id INT PRIMARY KEY AUTO_INCREMENT,
  summary_date DATE NOT NULL UNIQUE,
  total_trades INT NOT NULL DEFAULT 0,
  winning_trades INT NOT NULL DEFAULT 0,
  losing_trades INT NOT NULL DEFAULT 0,
  open_trades INT NOT NULL DEFAULT 0,
  gross_pl_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  net_pl_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  exposure_used_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS slot_performance (
  id INT PRIMARY KEY AUTO_INCREMENT,
  slot_id INT NOT NULL UNIQUE,
  total_trades INT NOT NULL DEFAULT 0,
  wins INT NOT NULL DEFAULT 0,
  losses INT NOT NULL DEFAULT 0,
  profit_loss_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  average_profit_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  average_loss_inr DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  best_coin VARCHAR(80) NULL,
  worst_coin VARCHAR(80) NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (slot_id) REFERENCES slots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (setting_key, setting_value, guide) VALUES
('trading_mode', 'paper', 'Paper Mode simulates trades using live price. Live Mode is disabled in Phase 1.'),
('max_open_slots_per_coin', '1', 'Controls how many different slots can run on the same coin at the same time.'),
('global_max_capital_exposure', '20000', 'Maximum total margin amount allowed across all open trades.'),
('daily_loss_limit', '500', 'If total daily paper loss reaches this value, no new paper trades are opened for the day.'),
('max_total_open_trades', '3', 'Maximum number of trades open across all coins.'),
('telegram_bot_token', '', 'Telegram bot token for alerts.'),
('telegram_chat_id', '', 'Telegram chat ID for alerts.'),
('bot_status', 'stopped', 'Current bot status shown in the dashboard.')
ON DUPLICATE KEY UPDATE guide = VALUES(guide);

INSERT INTO slots (
  slot_name, status, slot_mode, priority, leverage, max_amount_inr, price_rise_pct,
  pump_time_seconds, high_tracking_seconds, price_fall_pct, fixed_stop_loss_inr,
  profit_rule, profit_pct, trailing_start_pct, trailing_distance_pct, max_trade_duration_seconds
) VALUES (
  'Starter Slot', 'enabled', 'paper', 1, 5, 5000.00, 30.00,
  300, 300, 3.00, 300.00,
  'trailing', 2.00, 2.00, 1.00, 300
);