CREATE DATABASE IF NOT EXISTS contention1821;
USE contention1821;

CREATE TABLE stats ENGINE=innodb (SELECT FROM_DAYS(TO_DAYS('2023-01-01')+seq/1000000) as date,
	FLOOR(seq/1000)%1000 as domain_id, seq%1000 as creative_id, GREATEST(0,10-FLOOR(rand()*100)) as stat_a, 
	GREATEST(0,10-FLOOR(rand()*100)) as stat_b
	FROM seq_1_to_30000000);
	
ALTER TABLE `stats` 
	ADD UNIQUE INDEX(`date`, `domain_id`, `creative_id`);
	
CREATE TABLE `domain` (
  `id` int(11) NOT NULL,
  `_subnet` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO domain 
	SELECT seq, CONCAT(CHR(65+seq%10), CHR(65+seq%10))  FROM seq_1_to_50000;

CREATE TABLE `creative` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO creative 
  SELECT seq, seq%1200  FROM seq_1_to_50000;

CREATE TABLE creative_myisam LIKE creative;
ALTER TABLE creative_myisam ENGINE = MyISAM;
INSERT INTO creative_myisam SELECT * FROM creative;

CREATE TABLE creative_aria LIKE creative;
ALTER TABLE creative_aria ENGINE = aria;
INSERT INTO creative_aria SELECT * FROM creative;