CREATE SCHEMA IF NOT EXISTS `EOSTransactions` DEFAULT CHARACTER SET utf8;
USE `EOSTransactions` ;

CREATE TABLE IF NOT EXISTS `account` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(12),
  `created` DATETIME NOT NULL,
  `balance` BIGINT(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC)
);

CREATE TABLE IF NOT EXISTS `transaction` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `trx_id` CHAR(64) NOT NULL,
  `from_id` BIGINT(20) NOT NULL,
  `to_id` BIGINT(20) NOT NULL,
  `amount` BIGINT(20) NOT NULL,
  `created` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `created_idx` (`created` ASC),
  INDEX `to_id_fk_idx` (`to_id` ASC),
  INDEX `from_id_fk_idx` (`from_id` ASC),
  CONSTRAINT `from_id_fk`
    FOREIGN KEY (`from_id`)
    REFERENCES `account` (`id`),
  CONSTRAINT `to_id_fk`
    FOREIGN KEY (`to_id`)
    REFERENCES `account` (`id`)
);