-- ---------------------------------------------------------------------------
-- Users & authentication
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(100)        NOT NULL,
  email          VARCHAR(190)        NOT NULL UNIQUE,
  password_hash  VARCHAR(255)        NOT NULL,
  referral_code  VARCHAR(20)         NOT NULL UNIQUE,
  referred_by    INT                 NULL,
  balance        DECIMAL(20,8)       NOT NULL DEFAULT 10000.00000000,
  futures_balance DECIMAL(20,8)      NOT NULL DEFAULT 0,
  earn_balance   DECIMAL(20,8)       NOT NULL DEFAULT 0,
  email_verified TINYINT(1)          NOT NULL DEFAULT 1,
  email_verify_token VARCHAR(64)     NULL,
  email_verify_expires DATETIME      NULL,
  totp_secret    VARCHAR(64)         NULL,
  totp_enabled   TINYINT(1)          NOT NULL DEFAULT 0,
  totp_last_slice BIGINT             NULL,
  kyc_status     ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
  kyc_name       VARCHAR(120)        NULL,
  status         ENUM('active','banned') NOT NULL DEFAULT 'active',
  created_at     TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_users_referral (referral_code),
  CONSTRAINT fk_users_referred_by FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_users_balances_nonneg
    CHECK (balance >= 0 AND futures_balance >= 0 AND earn_balance >= 0)
) ENGINE=InnoDB;

-- Session tokens are stored as SHA-256 hex digests (64 chars), never raw.
CREATE TABLE IF NOT EXISTS sessions (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT          NOT NULL,
  token       VARCHAR(64)  NOT NULL UNIQUE,
  expires_at  DATETIME     NOT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sessions_user (user_id),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Immutable money ledger. Every credit/debit to a wallet writes one row inside
-- the same transaction as the balance change. Rows are append-only: never
-- update or delete them (refund by writing an opposite entry).
-- wallet = 'spot' also covers spot holdings (asset = coin symbol).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS balance_ledger (
  id             BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id        INT            NOT NULL,
  wallet         ENUM('spot','futures','earn') NOT NULL,
  asset          VARCHAR(20)    NOT NULL DEFAULT 'USDT',
  delta          DECIMAL(24,8)  NOT NULL,
  balance_before DECIMAL(24,8)  NOT NULL,
  balance_after  DECIMAL(24,8)  NOT NULL,
  ref_type       VARCHAR(40)    NOT NULL,
  ref_id         VARCHAR(80)    NULL,
  note           VARCHAR(255)   NULL,
  created_at     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ledger_user (user_id, id),
  INDEX idx_ledger_ref (ref_type, ref_id),
  CONSTRAINT fk_ledger_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- DB-backed fixed-window rate limiting (shared across web workers so it cannot
-- be bypassed by opening more connections).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rate_limits (
  bucket       VARCHAR(190) NOT NULL PRIMARY KEY,
  hits         INT          NOT NULL DEFAULT 0,
  window_start DATETIME     NOT NULL,
  expires_at   DATETIME     NOT NULL,
  INDEX idx_rate_limits_expiry (expires_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Single-use, purpose-bound email one-time codes. Only the SHA-256 digest is
-- stored; a code can be consumed once and is capped by attempts.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_codes (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT           NULL,
  email       VARCHAR(190)  NOT NULL,
  purpose     VARCHAR(40)   NOT NULL,
  code_hash   CHAR(64)      NOT NULL,
  attempts    INT           NOT NULL DEFAULT 0,
  expires_at  DATETIME      NOT NULL,
  consumed_at DATETIME      NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email_codes_lookup (email, purpose, consumed_at),
  INDEX idx_email_codes_expiry (expires_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Balances / spot holdings
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS holdings (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  user_id   INT            NOT NULL,
  coin_id   VARCHAR(60)    NOT NULL,
  symbol    VARCHAR(20)    NOT NULL,
  amount    DECIMAL(24,8)  NOT NULL DEFAULT 0,
  UNIQUE KEY uq_user_coin (user_id, coin_id),
  CONSTRAINT fk_holdings_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT            NOT NULL,
  coin_id     VARCHAR(60)    NOT NULL,
  pair        VARCHAR(20)    NOT NULL,
  side        ENUM('buy','sell','long','short') NOT NULL,
  order_type  ENUM('market','limit','stop') NOT NULL DEFAULT 'market',
  product     ENUM('spot','futures') NOT NULL DEFAULT 'spot',
  price       DECIMAL(20,8)  NOT NULL,
  stop_price  DECIMAL(20,8)  NULL,
  take_profit DECIMAL(20,8)  NULL,
  stop_loss   DECIMAL(20,8)  NULL,
  amount      DECIMAL(24,8)  NOT NULL,
  margin      DECIMAL(20,8)  NULL,
  margin_mode VARCHAR(20)    NOT NULL DEFAULT 'Cross',
  leverage    INT            NOT NULL DEFAULT 1,
  size_usdt   DECIMAL(20,8)  NULL,
  fee         DECIMAL(20,8)  NOT NULL DEFAULT 0,
  status      ENUM('open','filled','cancelled') NOT NULL DEFAULT 'open',
  created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_orders_user (user_id),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Enforce the futures columns on databases created before futures trading
-- shipped (the order endpoint inserts product / margin / leverage / size_usdt
-- and uses side = long|short).
SET @has_order_product := (SELECT COUNT(*) FROM information_schema.COLUMNS
                           WHERE TABLE_SCHEMA = DATABASE()
                             AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'product');
SET @ddl := IF(@has_order_product = 0,
  'ALTER TABLE orders ADD COLUMN product ENUM(''spot'',''futures'') NOT NULL DEFAULT ''spot'' AFTER order_type',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_order_margin := (SELECT COUNT(*) FROM information_schema.COLUMNS
                          WHERE TABLE_SCHEMA = DATABASE()
                            AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'margin');
SET @ddl := IF(@has_order_margin = 0,
  'ALTER TABLE orders ADD COLUMN margin DECIMAL(20,8) NULL AFTER amount',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_order_leverage := (SELECT COUNT(*) FROM information_schema.COLUMNS
                            WHERE TABLE_SCHEMA = DATABASE()
                              AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'leverage');
SET @ddl := IF(@has_order_leverage = 0,
  'ALTER TABLE orders ADD COLUMN leverage INT NOT NULL DEFAULT 1 AFTER margin',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_order_margin_mode := (SELECT COUNT(*) FROM information_schema.COLUMNS
                               WHERE TABLE_SCHEMA = DATABASE()
                                 AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'margin_mode');
SET @ddl := IF(@has_order_margin_mode = 0,
  'ALTER TABLE orders ADD COLUMN margin_mode VARCHAR(20) NOT NULL DEFAULT ''Cross'' AFTER margin',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_order_size := (SELECT COUNT(*) FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = DATABASE()
                          AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'size_usdt');
SET @ddl := IF(@has_order_size = 0,
  'ALTER TABLE orders ADD COLUMN size_usdt DECIMAL(20,8) NULL AFTER leverage',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @order_side_type := (SELECT COLUMN_TYPE FROM information_schema.COLUMNS
                         WHERE TABLE_SCHEMA = DATABASE()
                           AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'side');
SET @ddl := IF(@order_side_type NOT LIKE '%long%',
  'ALTER TABLE orders MODIFY COLUMN side ENUM(''buy'',''sell'',''long'',''short'') NOT NULL',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_order_fee := (SELECT COUNT(*) FROM information_schema.COLUMNS
                       WHERE TABLE_SCHEMA = DATABASE()
                         AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'fee');
SET @ddl := IF(@has_order_fee = 0,
  'ALTER TABLE orders ADD COLUMN fee DECIMAL(20,8) NOT NULL DEFAULT 0 AFTER size_usdt',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Take-profit / stop-loss levels travel with the order into the position.
SET @has_order_tp := (SELECT COUNT(*) FROM information_schema.COLUMNS
                      WHERE TABLE_SCHEMA = DATABASE()
                        AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'take_profit');
SET @ddl := IF(@has_order_tp = 0,
  'ALTER TABLE orders ADD COLUMN take_profit DECIMAL(20,8) NULL AFTER stop_price',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_order_sl := (SELECT COUNT(*) FROM information_schema.COLUMNS
                      WHERE TABLE_SCHEMA = DATABASE()
                        AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'stop_loss');
SET @ddl := IF(@has_order_sl = 0,
  'ALTER TABLE orders ADD COLUMN stop_loss DECIMAL(20,8) NULL AFTER take_profit',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Orders created without an explicit status must stay open for settlement.
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'open';

-- ---------------------------------------------------------------------------
-- Futures
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS positions (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  user_id        INT            NOT NULL,
  coin_id        VARCHAR(60)    NOT NULL,
  symbol         VARCHAR(20)    NOT NULL,
  side           ENUM('long','short') NOT NULL,
  leverage       INT            NOT NULL DEFAULT 1,
  margin_mode    VARCHAR(20)    NOT NULL DEFAULT 'Cross',
  position_mode  VARCHAR(20)    NOT NULL DEFAULT 'One-way',
  entry_price    DECIMAL(20,8)  NOT NULL,
  margin         DECIMAL(20,8)  NOT NULL,
  size_usdt      DECIMAL(20,8)  NOT NULL,
  liq_price      DECIMAL(20,8)  NOT NULL,
  take_profit    DECIMAL(20,8)  NULL,
  stop_loss      DECIMAL(20,8)  NULL,
  close_price    DECIMAL(20,8)  NULL,
  pnl            DECIMAL(20,8)  NULL,
  fee            DECIMAL(20,8)  NOT NULL DEFAULT 0,
  status         ENUM('open','closed') NOT NULL DEFAULT 'open',
  close_reason   ENUM('manual','stop_loss','take_profit','liquidation','admin') NULL,
  created_at     TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at      DATETIME       NULL,
  INDEX idx_positions_user_status (user_id, status),
  INDEX idx_positions_status (status),
  CONSTRAINT fk_positions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

SET @has_position_close_reason := (SELECT COUNT(*) FROM information_schema.COLUMNS
                                   WHERE TABLE_SCHEMA = DATABASE()
                                     AND TABLE_NAME = 'positions' AND COLUMN_NAME = 'close_reason');
SET @ddl := IF(@has_position_close_reason = 0,
  'ALTER TABLE positions ADD COLUMN close_reason ENUM(''manual'',''stop_loss'',''take_profit'',''liquidation'',''admin'') NULL AFTER status',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Enforce the fee column on databases created before trading fees shipped.
SET @has_position_fee := (SELECT COUNT(*) FROM information_schema.COLUMNS
                          WHERE TABLE_SCHEMA = DATABASE()
                            AND TABLE_NAME = 'positions' AND COLUMN_NAME = 'fee');
SET @ddl := IF(@has_position_fee = 0,
  'ALTER TABLE positions ADD COLUMN fee DECIMAL(20,8) NOT NULL DEFAULT 0 AFTER pnl',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_position_tp := (SELECT COUNT(*) FROM information_schema.COLUMNS
                         WHERE TABLE_SCHEMA = DATABASE()
                           AND TABLE_NAME = 'positions' AND COLUMN_NAME = 'take_profit');
SET @ddl := IF(@has_position_tp = 0,
  'ALTER TABLE positions ADD COLUMN take_profit DECIMAL(20,8) NULL AFTER liq_price',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_position_sl := (SELECT COUNT(*) FROM information_schema.COLUMNS
                         WHERE TABLE_SCHEMA = DATABASE()
                           AND TABLE_NAME = 'positions' AND COLUMN_NAME = 'stop_loss');
SET @ddl := IF(@has_position_sl = 0,
  'ALTER TABLE positions ADD COLUMN stop_loss DECIMAL(20,8) NULL AFTER take_profit',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- Referrals
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS referrals (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  referrer_id INT            NOT NULL,
  referred_id INT            NOT NULL UNIQUE,
  reward      DECIMAL(20,8)  NOT NULL DEFAULT 0,
  rewarded_at DATETIME       NULL,
  created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ref_referrer FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ref_referred FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

SET @has_ref_rewarded := (SELECT COUNT(*) FROM information_schema.COLUMNS
                          WHERE TABLE_SCHEMA = DATABASE()
                            AND TABLE_NAME = 'referrals' AND COLUMN_NAME = 'rewarded_at');
SET @ddl := IF(@has_ref_rewarded = 0,
  'ALTER TABLE referrals ADD COLUMN rewarded_at DATETIME NULL AFTER reward',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- External API response cache (Bybit, ...)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_cache (
  cache_key  VARCHAR(191)  PRIMARY KEY,
  payload    MEDIUMTEXT    NOT NULL,
  expires_at DATETIME      NOT NULL,
  INDEX idx_cache_expiry (expires_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Admin panel
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(60)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('super','moderator') NOT NULL DEFAULT 'moderator',
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login_at DATETIME     NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_sessions (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  admin_id   INT         NOT NULL,
  token      VARCHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME    NOT NULL,
  created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_admin_sessions_admin (admin_id),
  CONSTRAINT fk_admin_sessions_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- App settings (managed from the admin panel)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
  setting_key   VARCHAR(64)  PRIMARY KEY,
  setting_value TEXT         NOT NULL,
  description   VARCHAR(255) NULL,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- External API keys (e.g. Bybit), managed from the admin panel
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_keys (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  provider    VARCHAR(60)  NOT NULL DEFAULT 'bybit',
  plan        ENUM('demo','pro') NOT NULL DEFAULT 'demo',
  key_value   TEXT         NOT NULL,
  status      ENUM('active','disabled') NOT NULL DEFAULT 'active',
  last_checked DATETIME    NULL,
  last_ok     TINYINT(1)   NULL,
  note        VARCHAR(255) NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Admin activity log
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_logs (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  admin_id    INT          NULL,
  action      VARCHAR(60)  NOT NULL,
  target_type VARCHAR(60)  NULL,
  target_id   VARCHAR(60)  NULL,
  details     TEXT         NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_admin_logs_admin (admin_id),
  CONSTRAINT fk_admin_logs_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Per-coin feature switches (managed from the admin panel). A missing row
-- means every feature is enabled, so the catalog works with zero setup.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS coin_flags (
  coin_id    VARCHAR(60) NOT NULL PRIMARY KEY,
  spot       TINYINT(1)  NOT NULL DEFAULT 1,
  futures    TINYINT(1)  NOT NULL DEFAULT 1,
  deposit    TINYINT(1)  NOT NULL DEFAULT 1,
  withdraw   TINYINT(1)  NOT NULL DEFAULT 1,
  updated_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- Admin-defined custom coins. mode = 'market' mirrors a real Bybit pair
-- (base_symbol, e.g. BTC -> BTCUSDT); mode = 'simulated' runs the price
-- through a random walk the engine keeps inside [sim_min_price, sim_max_price].
-- sim_price / sim_updated_at hold the live simulated state.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS custom_coins (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  coin_id       VARCHAR(60)   NOT NULL,
  symbol        VARCHAR(20)   NOT NULL,
  name          VARCHAR(80)   NOT NULL,
  mode          ENUM('market','simulated') NOT NULL DEFAULT 'simulated',
  base_symbol   VARCHAR(20)   NOT NULL DEFAULT 'BTC',
  base_price    DECIMAL(20,8) NOT NULL DEFAULT 0,
  sim_min_price DECIMAL(20,8) NOT NULL DEFAULT 0,
  sim_max_price DECIMAL(20,8) NOT NULL DEFAULT 0,
  sim_step_pct  DECIMAL(8,4)  NOT NULL DEFAULT 1.0000,
  sim_price     DECIMAL(20,8) NULL DEFAULT NULL,
  sim_updated_at TIMESTAMP    NULL DEFAULT NULL,
  enabled       TINYINT(1)    NOT NULL DEFAULT 1,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_custom_coins_id (coin_id),
  UNIQUE KEY uq_custom_coins_symbol (symbol)
) ENGINE=InnoDB;

-- Admin-managed withdrawal networks per symbol. The empty symbol means the
-- network is offered for every coin without its own entry.
CREATE TABLE IF NOT EXISTS withdraw_networks (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  symbol     VARCHAR(20)  NOT NULL DEFAULT '',
  network_id VARCHAR(20)  NOT NULL,
  name       VARCHAR(80)  NOT NULL,
  kind       ENUM('evm','tron') NOT NULL DEFAULT 'evm',
  sort_order INT          NOT NULL DEFAULT 0,
  min_amount DECIMAL(20,8) NOT NULL DEFAULT 0,
  fee        DECIMAL(20,8) NOT NULL DEFAULT 0,
  enabled    TINYINT(1)   NOT NULL DEFAULT 1,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_withdraw_networks (symbol, network_id)
) ENGINE=InnoDB;

-- Enforce the min/fee columns on databases created before per-network
-- withdrawal terms shipped.
SET @has_wn_min := (SELECT COUNT(*) FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE()
                      AND TABLE_NAME = 'withdraw_networks' AND COLUMN_NAME = 'min_amount');
SET @ddl := IF(@has_wn_min = 0,
  'ALTER TABLE withdraw_networks ADD COLUMN min_amount DECIMAL(20,8) NOT NULL DEFAULT 0 AFTER sort_order',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_wn_fee := (SELECT COUNT(*) FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE()
                      AND TABLE_NAME = 'withdraw_networks' AND COLUMN_NAME = 'fee');
SET @ddl := IF(@has_wn_fee = 0,
  'ALTER TABLE withdraw_networks ADD COLUMN fee DECIMAL(20,8) NOT NULL DEFAULT 0 AFTER min_amount',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Seed the conventional networks once; admins add or remove from the panel.
INSERT IGNORE INTO withdraw_networks (symbol, network_id, name, kind, sort_order) VALUES
  ('USDT', 'BEP-20', 'BNB Smart Chain (BEP-20)', 'evm', 1),
  ('USDT', 'ERC-20', 'Ethereum (ERC-20)',       'evm', 2),
  ('USDT', 'TRC-20', 'Tron (TRC-20)',           'tron', 3),
  ('USDC', 'BEP-20', 'BNB Smart Chain (BEP-20)', 'evm', 1),
  ('USDC', 'ERC-20', 'Ethereum (ERC-20)',       'evm', 2),
  ('BNB',  'BEP-20', 'BNB Smart Chain (BEP-20)', 'evm', 1),
  ('ETH',  'ERC-20', 'Ethereum (ERC-20)',       'evm', 1),
  ('TRX',  'TRC-20', 'Tron (TRC-20)',           'tron', 1),
  ('',     'BEP-20', 'BNB Smart Chain (BEP-20)', 'evm', 1),
  ('',     'ERC-20', 'Ethereum (ERC-20)',       'evm', 2);


-- Enforce the same column on databases created before the admin panel existed.
SET @has_status := (SELECT COUNT(*) FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE()
                      AND TABLE_NAME = 'users' AND COLUMN_NAME = 'status');
SET @ddl := IF(@has_status = 0,
  'ALTER TABLE users ADD COLUMN status ENUM(''active'',''banned'') NOT NULL DEFAULT ''active''',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- api_keys.plan is reused by the Bybit key manager: demo -> testnet host,
-- pro -> live host.
SET @has_plan := (SELECT COUNT(*) FROM information_schema.COLUMNS
                  WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'api_keys' AND COLUMN_NAME = 'plan');
SET @ddl2 := IF(@has_plan = 0,
  'ALTER TABLE api_keys ADD COLUMN plan ENUM(''demo'',''pro'') NOT NULL DEFAULT ''demo'' AFTER provider',
  'SELECT 1');
PREPARE stmt2 FROM @ddl2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;

-- ---------------------------------------------------------------------------
-- Seed data
-- ---------------------------------------------------------------------------
-- Default admin account: username "admin", password "admin123".
-- CHANGE THIS PASSWORD after your first login from the admin panel.
INSERT INTO admins (username, password_hash, role)
SELECT 'admin', '$2y$10$illUs/ykU4yLvVtMh/nKf.EH6vk2Cgkv4CYUEUh.ghn5faa8eMBGu', 'super'
WHERE NOT EXISTS (SELECT 1 FROM admins);

INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
  ('MAINTENANCE_MODE',    '0',   'When on, every public API call returns 503.'),
  ('TRADING_ENABLED',     '1',   'Turn off to reject spot orders and futures actions.'),
  ('REGISTRATION_ENABLED','1',   'Turn off to block new account registrations.'),
  ('START_BALANCE',       '10000', 'USDT granted to every new account.'),
  ('MARKET_CACHE_TTL',    '60',  'Seconds to cache the Bybit market list.'),
  ('CHART_CACHE_TTL',     '300', 'Seconds to cache Bybit chart + OHLC klines.'),
  ('PRICE_CACHE_TTL',     '1',   'Seconds to cache the live ticker (Bybit only).'),
  ('SESSION_DAYS',        '30',  'App session lifetime in days.'),
  ('MIN_PASSWORD_LENGTH', '6',   'Minimum app password length.'),
  ('SITE_NAME',           'VeloTrade', 'Brand name used by the app/admin panel.');

-- ---------------------------------------------------------------------------
-- HD deposit wallet (BEP-20 USDT / USDC / BNB)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wallet_seed (
  id                  TINYINT      NOT NULL DEFAULT 1 PRIMARY KEY,
  mnemonic_encrypted  TEXT         NOT NULL,
  seed_encrypted      TEXT         NOT NULL,
  admin_address       VARCHAR(64)  NOT NULL,
  gas_address         VARCHAR(64)  NOT NULL,
  created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_state (
  state_key   VARCHAR(96)  NOT NULL PRIMARY KEY,
  state_value TEXT         NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_txs (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT            NULL,
  address       VARCHAR(64)    NOT NULL,
  coin_id       VARCHAR(60)    NOT NULL,
  symbol        VARCHAR(20)    NOT NULL,
  amount        DECIMAL(36,18) NOT NULL,
  tx_hash       VARCHAR(80)    NOT NULL,
  log_index     INT            NOT NULL DEFAULT 0,
  ref_key       VARCHAR(160)   NOT NULL UNIQUE,
  from_address  VARCHAR(64)    NULL,
  block_number  BIGINT         NOT NULL DEFAULT 0,
  confirmations INT            NOT NULL DEFAULT 0,
  direction     ENUM('in','out') NOT NULL DEFAULT 'in',
  status        ENUM('pending','credited','swept','sweep_failed','failed') NOT NULL DEFAULT 'pending',
  sweep_tx      VARCHAR(80)    NULL,
  created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  credited_at   DATETIME       NULL,
  INDEX idx_wallet_txs_user (user_id),
  INDEX idx_wallet_txs_status (status),
  INDEX idx_wallet_txs_address (address)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wallet_balances (
  address      VARCHAR(64)    NOT NULL PRIMARY KEY,
  last_balance DECIMAL(36,18) NOT NULL DEFAULT 0,
  updated_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- One scan cursor per asset. A cursor only advances after every transfer in the
-- processed block range was credited successfully, so a single failing RPC or
-- database write can never skip deposits.
CREATE TABLE IF NOT EXISTS wallet_scan_cursors (
  asset_key  VARCHAR(80) NOT NULL PRIMARY KEY,
  last_block BIGINT      NOT NULL DEFAULT 0,
  updated_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Outgoing gas top-ups from the gas wallet. The deposit scanner subtracts these
-- native transfers instead of crediting them as user deposits.
CREATE TABLE IF NOT EXISTS wallet_gas_topups (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  address    VARCHAR(64)    NOT NULL,
  tx_hash    VARCHAR(80)    NOT NULL UNIQUE,
  amount     DECIMAL(36,18) NOT NULL,
  status     ENUM('pending','confirmed','absorbed','failed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_gas_topups_address (address)
) ENGINE=InnoDB;

-- Upgrade databases created before the absorbed state existed.
ALTER TABLE wallet_gas_topups
  MODIFY COLUMN status ENUM('pending','confirmed','absorbed','failed') NOT NULL DEFAULT 'pending';

-- users.deposit_address / deposit_index (guarded for pre-existing databases)
SET @has_deposit_address := (SELECT COUNT(*) FROM information_schema.COLUMNS
                             WHERE TABLE_SCHEMA = DATABASE()
                               AND TABLE_NAME = 'users' AND COLUMN_NAME = 'deposit_address');
SET @ddl := IF(@has_deposit_address = 0,
  'ALTER TABLE users ADD COLUMN deposit_address VARCHAR(64) NULL AFTER referred_by, ADD COLUMN deposit_index INT NULL AFTER deposit_address',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_deposit_unique := (SELECT COUNT(*) FROM information_schema.STATISTICS
                            WHERE TABLE_SCHEMA = DATABASE()
                              AND TABLE_NAME = 'users' AND INDEX_NAME = 'idx_users_deposit_address');
SET @ddl := IF(@has_deposit_unique = 0,
  'ALTER TABLE users ADD UNIQUE KEY idx_users_deposit_address (deposit_address)',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
  ('WALLET_ENABLED',         '1', 'Enable BEP-20 deposit addresses and the deposit watcher.'),
  ('WALLET_RPC_URL',         'https://bsc.publicnode.com', 'BSC JSON-RPC endpoint.'),
  ('WALLET_CHAIN_ID',        '56', 'EVM chain id (56 = BSC mainnet).'),
  ('WALLET_CONFIRMATIONS',   '5', 'Confirmations required before crediting a deposit.'),
  ('WALLET_START_INDEX',     '2', 'First BIP-44 child index used for user deposit addresses (0 = gas, 1 = admin).'),
  ('WALLET_SWEEP_ADDRESS',   '', 'Destination for swept funds. Empty = the HD admin address.'),
  ('WALLET_USDT_CONTRACT',   '0x55d398326f99059fF775485246999027B3197955', 'BEP-20 USDT contract.'),
  ('WALLET_USDC_CONTRACT',   '0x8AC76a51cc950d9822D68b83fE1Ad97B32Cd580d', 'BEP-20 USDC contract.'),
  ('WALLET_GAS_TOPUP_MIN',   '0.0005', 'Top up a child address when its BNB drops below this.'),
  ('WALLET_GAS_TOPUP_AMOUNT','0.002',  'BNB sent when topping up a child address for sweeping.'),
  ('WALLET_AUTO_CREATE_SEED','1', 'Generate a master seed automatically on first deposit address request.');

-- ---------------------------------------------------------------------------
-- Manual withdrawals: users submit a request, an admin pays (or rejects) it by
-- hand. The amount is held from the user's balance/holding on submission and
-- refunded when a request is rejected.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS withdrawals (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT            NOT NULL,
  coin_id     VARCHAR(60)    NOT NULL,
  symbol      VARCHAR(20)    NOT NULL,
  network     VARCHAR(40)    NOT NULL,
  address     VARCHAR(120)   NOT NULL,
  amount      DECIMAL(36,18) NOT NULL,
  fee         DECIMAL(36,18) NOT NULL DEFAULT 0,
  status      ENUM('pending','paid','rejected') NOT NULL DEFAULT 'pending',
  tx_hash     VARCHAR(120)   NULL,
  admin_note  VARCHAR(255)   NULL,
  reviewed_by INT            NULL,
  reviewed_at DATETIME       NULL,
  created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_withdrawals_user (user_id),
  INDEX idx_withdrawals_status (status),
  UNIQUE KEY uq_withdrawals_tx_hash (tx_hash),
  CONSTRAINT fk_withdrawals_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

SET @has_withdrawal_tx_unique := (SELECT COUNT(*) FROM information_schema.STATISTICS
                                  WHERE TABLE_SCHEMA = DATABASE()
                                    AND TABLE_NAME = 'withdrawals' AND INDEX_NAME = 'uq_withdrawals_tx_hash');
SET @ddl := IF(@has_withdrawal_tx_unique = 0,
  'ALTER TABLE withdrawals ADD UNIQUE KEY uq_withdrawals_tx_hash (tx_hash)',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
  ('WITHDRAW_ENABLED', '1', 'Allow users to submit manual withdrawal requests.'),
  ('WITHDRAW_MIN',     '0', 'Minimum coin amount per withdrawal request (0 = no minimum).');

-- ---------------------------------------------------------------------------
-- Account security: email verification, Google Authenticator (TOTP) 2FA and
-- KYC verification. New columns are added to `users`; the guarded blocks below
-- upgrade databases created before these features existed.
-- ---------------------------------------------------------------------------
SET @has_email_verified := (SELECT COUNT(*) FROM information_schema.COLUMNS
                            WHERE TABLE_SCHEMA = DATABASE()
                              AND TABLE_NAME = 'users' AND COLUMN_NAME = 'email_verified');
SET @ddl := IF(@has_email_verified = 0,
  'ALTER TABLE users
     ADD COLUMN email_verified TINYINT(1) NOT NULL DEFAULT 1 AFTER balance,
     ADD COLUMN email_verify_token VARCHAR(64) NULL AFTER email_verified,
     ADD COLUMN email_verify_expires DATETIME NULL AFTER email_verify_token',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_totp := (SELECT COUNT(*) FROM information_schema.COLUMNS
                  WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME = 'users' AND COLUMN_NAME = 'totp_enabled');
SET @ddl := IF(@has_totp = 0,
  'ALTER TABLE users
     ADD COLUMN totp_secret VARCHAR(64) NULL AFTER email_verify_expires,
     ADD COLUMN totp_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER totp_secret',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_kyc_status := (SELECT COUNT(*) FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = DATABASE()
                          AND TABLE_NAME = 'users' AND COLUMN_NAME = 'kyc_status');
SET @ddl := IF(@has_kyc_status = 0,
  'ALTER TABLE users
     ADD COLUMN kyc_status ENUM(''none'',''pending'',''approved'',''rejected'') NOT NULL DEFAULT ''none'' AFTER totp_enabled,
     ADD COLUMN kyc_name VARCHAR(120) NULL AFTER kyc_status',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS kyc_submissions (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT            NOT NULL,
  full_name   VARCHAR(120)   NOT NULL,
  dob         DATE           NULL,
  country     VARCHAR(80)    NOT NULL,
  id_type     VARCHAR(40)    NOT NULL,
  id_number   VARCHAR(80)    NOT NULL,
  address     VARCHAR(255)   NULL,
  selfie_path VARCHAR(255)   NULL,
  doc_path    VARCHAR(255)   NULL,
  doc_back_path VARCHAR(255) NULL,
  status      ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_note  VARCHAR(255)   NULL,
  reviewed_by INT            NULL,
  reviewed_at DATETIME       NULL,
  created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_kyc_user (user_id),
  INDEX idx_kyc_status (status),
  CONSTRAINT fk_kyc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
  ('SECURITY_ENABLED',           '1',        'Allow users to enable Google Authenticator (TOTP) 2FA.'),
  ('REQUIRE_EMAIL_VERIFICATION', '0',        'When on, unverified accounts cannot sign in.'),
  ('KYC_ENABLED',                '1',        'Allow users to submit KYC verification.'),
  ('APP_URL',                    '',         'Public base URL used in verification email links. Empty = auto-detect.'),
  ('MAIL_FROM_EMAIL',            '',         'From address for verification emails. Empty = no-reply@<host>.'),
  ('MAIL_FROM_NAME',             'VeloTrade','Display name for verification emails.'),
  ('KYC_MAX_UPLOAD_KB',          '800',      'Max size (KB) of each KYC image after compression.');

-- ---------------------------------------------------------------------------
-- Promotional banners (app home carousel), managed from the admin panel.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS banners (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(120)  NOT NULL,
  subtitle    VARCHAR(200)  NULL,
  image_url   VARCHAR(255)  NULL,
  action_url  VARCHAR(255)  NULL,
  color_start VARCHAR(9)    NOT NULL DEFAULT '#00C087',
  color_end   VARCHAR(9)    NOT NULL DEFAULT '#066E4E',
  sort_order  INT           NOT NULL DEFAULT 0,
  active      TINYINT(1)    NOT NULL DEFAULT 1,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_banners_active (active, sort_order)
) ENGINE=InnoDB;

INSERT INTO banners (title, subtitle, color_start, color_end, sort_order)
SELECT * FROM (
  SELECT 'Deposit & trade' AS title, 'Fund your wallet and start in seconds' AS subtitle, '#00C087' AS color_start, '#066E4E' AS color_end, 1 AS sort_order
  UNION ALL SELECT 'Futures up to 100x', 'Trade with leverage, long or short', '#6C5CE7', '#3B2F9E', 2
  UNION ALL SELECT 'Invite & earn', 'Share your referral code to earn rewards', '#F7A23B', '#C15A00', 3
) seeds
WHERE NOT EXISTS (SELECT 1 FROM banners);

-- ---------------------------------------------------------------------------
-- New-user reward tasks ("deposit + trade futures, get a USDT bonus").
-- Progress is derived live from wallet_txs (USDT deposits) and filled futures
-- order volume; claiming inserts a row in reward_claims for admin review.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reward_tasks (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  code          VARCHAR(40)   NOT NULL,
  title         VARCHAR(120)  NOT NULL,
  subtitle      VARCHAR(200)  NULL,
  deposit_req   DECIMAL(20,2) NOT NULL DEFAULT 0,
  trade_req     DECIMAL(20,2) NOT NULL DEFAULT 0,
  bonus         DECIMAL(20,2) NOT NULL DEFAULT 0,
  kyc_required  TINYINT(1)    NOT NULL DEFAULT 1,
  sort_order    INT           NOT NULL DEFAULT 0,
  active        TINYINT(1)    NOT NULL DEFAULT 1,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reward_tasks_code (code),
  INDEX idx_reward_tasks_active (active, sort_order)
) ENGINE=InnoDB;

INSERT IGNORE INTO reward_tasks (code, title, subtitle, deposit_req, trade_req, bonus, kyc_required, sort_order) VALUES
  ('starter_5',   'Starter task',   'Deposit 5 USDT and trade 500 USDT in futures',   5.00,    500.00,   5.00, 1, 1),
  ('starter_10',  'Pro task',       'Deposit 10 USDT and trade 1,000 USDT in futures', 10.00,   1000.00,  10.00, 1, 2),
  ('starter_20',  'Elite task',     'Deposit 20 USDT and trade 2,000 USDT in futures', 20.00,   2000.00,  10.00, 1, 3);

-- One claim per user per task; status pending -> approved -> paid (or rejected).
CREATE TABLE IF NOT EXISTS reward_claims (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT           NOT NULL,
  task_id       INT           NOT NULL,
  bonus         DECIMAL(20,2) NOT NULL DEFAULT 0,
  deposit_done  DECIMAL(20,2) NOT NULL DEFAULT 0,
  trade_done    DECIMAL(20,2) NOT NULL DEFAULT 0,
  status        ENUM('pending','approved','paid','rejected') NOT NULL DEFAULT 'pending',
  admin_note    VARCHAR(255)  NULL,
  reviewed_by   INT           NULL,
  reviewed_at   DATETIME      NULL,
  paid_at       DATETIME      NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reward_claims_user_task (user_id, task_id),
  INDEX idx_reward_claims_status (status),
  INDEX idx_reward_claims_task (task_id)
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
  ('REWARD_PAY_DAYS', '7', 'Days after task completion within which a reward is paid.');

-- ---------------------------------------------------------------------------
-- Sign-ups are verified *before* the account exists: the name/email/password
-- wait here with a 6-digit code and are only written to `users` once the code
-- is confirmed (api/verify_registration.php).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pending_registrations (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(100)  NOT NULL,
  email         VARCHAR(190)  NOT NULL,
  password_hash VARCHAR(255)  NOT NULL,
  referral      VARCHAR(20)   NULL,
  code          VARCHAR(6)    NOT NULL,
  expires_at    DATETIME      NOT NULL,
  attempts      INT           NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pending_reg_email (email)
) ENGINE=InnoDB;

-- New sign-ups no longer store a plaintext code here (email_codes owns the
-- hashed OTP); widen the column for the sentinel used by newer rows.
ALTER TABLE pending_registrations MODIFY COLUMN code VARCHAR(64) NOT NULL;

-- ---------------------------------------------------------------------------
-- Support tickets: users open a ticket from the app, admins reply from the
-- panel. The conversation lives in support_messages (first row = the user's
-- original message).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS support_tickets (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  user_id     INT            NOT NULL,
  subject     VARCHAR(160)   NOT NULL,
  category    VARCHAR(40)    NOT NULL DEFAULT 'general',
  status      ENUM('open','waiting','closed') NOT NULL DEFAULT 'open',
  last_reply_at DATETIME     NULL,
  created_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_support_tickets_user (user_id),
  INDEX idx_support_tickets_status (status, last_reply_at),
  CONSTRAINT fk_support_tickets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS support_messages (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id  INT            NOT NULL,
  user_id    INT            NULL,
  sender     ENUM('user','admin') NOT NULL,
  message    TEXT           NOT NULL,
  created_at TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_support_messages_ticket (ticket_id, id),
  CONSTRAINT fk_support_messages_ticket FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
  CONSTRAINT fk_support_messages_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
  ('SUPPORT_ENABLED', '1', 'Allow users to open support tickets from the app.');

-- ---------------------------------------------------------------------------
-- Earn: users move Spot USDT into a principal-bearing position. Accrual runs
-- daily from cron/earn-accrual.php and is credited on redeem (or whenever the
-- position is accrued). APR is snapshotted at subscribe time.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS earn_positions (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  user_id         INT            NOT NULL,
  amount          DECIMAL(24,8)  NOT NULL,
  apr_percent     DECIMAL(10,4)  NOT NULL,
  accrued         DECIMAL(24,8)  NOT NULL DEFAULT 0,
  status          ENUM('active','closed') NOT NULL DEFAULT 'active',
  opened_at       DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_accrual_at DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at       DATETIME       NULL,
  INDEX idx_earn_user_status (user_id, status),
  CONSTRAINT fk_earn_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT chk_earn_amounts_nonneg CHECK (amount >= 0 AND accrued >= 0)
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value, description) VALUES
  ('EARN_ENABLED',                '1',  'Allow users to subscribe and redeem Earn positions.'),
  ('EARN_APR_PERCENT',            '8',  'Default Earn APR percent per year for new subscriptions.'),
  ('EARN_MIN_AMOUNT',             '1',  'Minimum USDT amount per Earn subscription.'),
  ('REFERRAL_REWARD',             '5',  'USDT paid to the referrer on the referred user''s first credited deposit.'),
  ('REFERRAL_MIN_DEPOSIT',        '1',  'Minimum first-deposit size (USDT) that triggers the referral reward.'),
  ('FUTURES_MAINTENANCE_PERCENT', '0.5','Maintenance margin as a percent of position notional before liquidation.'),
  ('WALLET_START_BLOCK',          '0',  'First BSC block scanned for deposits (0 = earliest available).'),
  ('WALLET_SCAN_CHUNK',           '2000', 'Blocks fetched per deposit scan batch.'),
  ('RATE_LOGIN_MAX',              '10', 'Failed sign-in attempts allowed per window per IP/email.'),
  ('RATE_LOGIN_WINDOW',          '300', 'Sign-in rate-limit window in seconds.'),
  ('RATE_REGISTER_MAX',           '5',  'Registration and resend attempts allowed per window per IP.'),
  ('RATE_REGISTER_WINDOW',      '3600', 'Registration rate-limit window in seconds.'),
  ('RATE_OTP_MAX',                '5',  'OTP send/verify attempts allowed per window.'),
  ('RATE_OTP_WINDOW',           '300',  'OTP rate-limit window in seconds.'),
  ('RATE_TRADE_MAX',             '60',  'Order submissions allowed per window per user.'),
  ('RATE_TRADE_WINDOW',          '60',  'Order rate-limit window in seconds.'),
  ('RATE_WITHDRAW_MAX',          '10',  'Withdrawal submissions allowed per window per user.'),
  ('RATE_WITHDRAW_WINDOW',     '3600',  'Withdrawal rate-limit window in seconds.');

-- ---------------------------------------------------------------------------
-- Security hardening for databases created before these columns existed.
-- ---------------------------------------------------------------------------
SET @has_totp_slice := (SELECT COUNT(*) FROM information_schema.COLUMNS
                        WHERE TABLE_SCHEMA = DATABASE()
                          AND TABLE_NAME = 'users' AND COLUMN_NAME = 'totp_last_slice');
SET @ddl := IF(@has_totp_slice = 0,
  'ALTER TABLE users ADD COLUMN totp_last_slice BIGINT NULL AFTER totp_enabled',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_hold_nonneg := (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
                         WHERE TABLE_SCHEMA = DATABASE()
                           AND TABLE_NAME = 'holdings' AND CONSTRAINT_NAME = 'chk_holdings_amount_nonneg');
SET @ddl := IF(@has_hold_nonneg = 0,
  'ALTER TABLE holdings ADD CONSTRAINT chk_holdings_amount_nonneg CHECK (amount >= 0)',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Existing databases may hold a negative balance produced by the old
-- non-atomic debits. Clamp those before enforcing the invariant.
UPDATE users SET balance = 0 WHERE balance < 0;
UPDATE users SET futures_balance = 0 WHERE futures_balance < 0;
UPDATE users SET earn_balance = 0 WHERE earn_balance < 0;
UPDATE holdings SET amount = 0 WHERE amount < 0;

SET @has_users_nonneg := (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
                          WHERE TABLE_SCHEMA = DATABASE()
                            AND TABLE_NAME = 'users' AND CONSTRAINT_NAME = 'chk_users_balances_nonneg');
SET @ddl := IF(@has_users_nonneg = 0,
  'ALTER TABLE users ADD CONSTRAINT chk_users_balances_nonneg CHECK (balance >= 0 AND futures_balance >= 0 AND earn_balance >= 0)',
  'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;