CREATE TABLE IF NOT EXISTS live_match_states (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  api_fixture_id BIGINT UNSIGNED NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  status_short VARCHAR(20) NULL,
  score_home INT NULL,
  score_away INT NULL,
  event_signature_hash CHAR(64) NULL,
  payload JSON NULL,
  last_checked_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_live_match_states_fixture (api_fixture_id),
  KEY idx_live_match_states_status (status_short)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_notification_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  device_token_id BIGINT UNSIGNED NULL,
  dedupe_key VARCHAR(190) NOT NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  payload JSON NULL,
  sent_at DATETIME NULL,
  error TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_push_dedupe_user_device (user_id, device_token_id, dedupe_key),
  KEY idx_push_user_created (user_id, created_at),
  CONSTRAINT fk_push_log_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_push_log_device FOREIGN KEY (device_token_id) REFERENCES device_tokens(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;