// This file will create all tables for the bblib schema
// DATA WILL BE LOST IF DB ALREADY EXISTS, RUN AT OWN RISK

import bbLibConfig from '../bbLibConfig.ts';
import dbClient from './dbClient.ts';

console.log('Attempting to create DB');
await dbClient.execute(`CREATE SCHEMA IF NOT EXISTS ${bbLibConfig.db.name};`);
await dbClient.execute(`USE ${bbLibConfig.db.name}`);
console.log('DB created');

console.log('Attempt to drop all tables');
await dbClient.execute(`DROP VIEW IF EXISTS db_size;`);
await dbClient.execute(`DROP PROCEDURE IF EXISTS INC_CNT;`);
await dbClient.execute(`DROP TABLE IF EXISTS command_cnt;`);
await dbClient.execute(`DROP TABLE IF EXISTS banned_guilds`);
await dbClient.execute(`DROP TABLE IF EXISTS banned_users`);
console.log('Tables dropped');

console.log('Attempting to create table command_cnt');
await dbClient.execute(`
	CREATE TABLE command_cnt (
		command char(20) NOT NULL,
		count bigint unsigned NOT NULL DEFAULT 0,
		PRIMARY KEY (command),
		UNIQUE KEY command_cnt_command_UNIQUE (command)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`);
console.log('Table created');

console.log('Attempt creating increment Stored Procedure');
await dbClient.execute(`
	CREATE PROCEDURE INC_CNT(
		IN cmd CHAR(20)
	)
	BEGIN
		declare oldCnt bigint unsigned;
		set oldCnt = (SELECT count FROM ${bbLibConfig.db.name}.command_cnt WHERE command = cmd);
		UPDATE ${bbLibConfig.db.name}.command_cnt SET count = oldCnt + 1 WHERE command = cmd;
	END
`);
console.log('Stored Procedure created');

console.log('Attempting to create table banned_guilds');
await dbClient.execute(`
	CREATE TABLE banned_guilds (
		id bigint unsigned NOT NULL,
		soft tinyint(1) NOT NULL,
		hard tinyint(1) NOT NULL,
		PRIMARY KEY (id),
		UNIQUE KEY banned_guilds_id_UNIQUE (id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`);
console.log('Table created');

console.log('Attempting to create table banned_users');
await dbClient.execute(`
	CREATE TABLE banned_users (
		id bigint unsigned NOT NULL,
		soft tinyint(1) NOT NULL,
		hard tinyint(1) NOT NULL,
		PRIMARY KEY (id),
		UNIQUE KEY banned_users_id_UNIQUE (id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`);
console.log('Table created');

// Database sizes view
console.log('Attempting to create view db_size');
await dbClient.execute(`
	CREATE VIEW db_size AS
		SELECT
			table_name AS "table",
			ROUND(((data_length + index_length) / 1024 / 1024), 3) AS "size",
			table_rows AS "rows"
		FROM information_schema.TABLES
		WHERE
			table_schema = "${bbLibConfig.db.name}"
			AND table_name <> "db_size";
`);
console.log('View Created');

await dbClient.close();
console.log('Done!');