CREATE TABLE pay_transaction (
  pay_transaction_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id BIGINT(20) UNSIGNED DEFAULT NULL,
  account_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  system_id BIGINT(20) UNSIGNED NOT NULL,
  action_id VARCHAR(196) NOT NULL DEFAULT '0',
  action ENUM('1',2',3') DEFAULT NULL,
  external_user_id VARCHAR(40) DEFAULT NULL,
  session_id VARCHAR(50) DEFAULT NULL,
  data JSON NOT NULL,
  raw MEDIUMTEXT DEFAULT NULL,
  date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created TIMESTAMP(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4),
  amount DECIMAL(20, 2) GENERATED ALWAYS AS (json_extract(`data`,_utf8'$.amount')) STORED,
  bonus DECIMAL(20, 2) GENERATED ALWAYS AS (json_extract(`data`,_utf8'$.bonus.amount')) STORED,
  original_action_id VARCHAR(255) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,_utf8'$.original_action_id'))) VIRTUAL,
  day DATE GENERATED ALWAYS AS (cast(`date` as date)) STORED,
  PRIMARY KEY (pay_transaction_id, date),
  INDEX IDX_transaction_day(account_id, pay_transaction , day, action, system_id,  bonus, amount, parent_id)
)

  SELECT  sum(pay_transaction .amount),sum(pay_transaction .bonus), sum(pay_transaction .bonus)   FROM pay_transaction   WHERE `pay_transaction `. day BETWEEN "2021-12-20" AND "2021-12-27" AND pay_transaction .action = '1' GROUP BY bet_transaction.account_id

SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) 
FROM information_schema.tables WHERE engine='InnoDB';

SELECT
  SUM(pay_transaction.amount)
FROM `pay_transaction_softswiss` AS `bet_transaction`
WHERE `pay_transaction`.date BETWEEN "2021-12-20" AND "2021-12-27"
AND pay_transaction.action = 'complete'
GROUP BY pay_transaction.account_id

ALTER TABLE pay_transaction
ADD INDEX IDX_pay_test (date, action, amount, account_id);