CREATE TABLE IF NOT EXISTS device_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  token_value VARCHAR(500) NOT NULL,
  platform VARCHAR(40) NOT NULL DEFAULT 'ios',
  device_name VARCHAR(190) NULL,
  notifications_enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_device_token_hash (token_hash),
  KEY idx_device_tokens_user (user_id),
  CONSTRAINT fk_device_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_pick_watchlist (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NULL,
  recommendation_key VARCHAR(190) NOT NULL,
  title VARCHAR(190) NOT NULL,
  market VARCHAR(120) NULL,
  odds DECIMAL(5,2) NULL,
  bookmaker VARCHAR(120) NULL,
  status ENUM('pending', 'validated', 'lost', 'void') NOT NULL DEFAULT 'pending',
  notified_at DATETIME NULL,
  payload JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_pick_daily (user_id, recommendation_key),
  KEY idx_user_pick_user_status (user_id, status),
  KEY idx_user_pick_fixture (api_fixture_id),
  CONSTRAINT fk_user_pick_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_pick_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;