import config from '~config';

import dbClient from './client.ts';

console.log('Attempting to create DB');
await dbClient.execute(`CREATE SCHEMA IF NOT EXISTS ${config.db.name};`);
await dbClient.execute(`USE ${config.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 TABLE IF EXISTS plans;`);
await dbClient.execute(`DROP TABLE IF EXISTS users;`);
console.log('Tables dropped');

console.log('Attempting to create table users');
await dbClient.execute(`
  CREATE TABLE users (
    id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    name varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    hash varchar(60) NOT NULL,
    email varchar(255) NULL,
    deleteCode varchar(20) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY users_id_UNIQUE (id),
    UNIQUE KEY users_name_UNIQUE (name)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
`);
console.log('Table created');

console.log('Attempting to create table plans');
await dbClient.execute(`
  CREATE TABLE plans (
    id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    ownerId varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    name varchar(200) NOT NULL,
    folder varchar(200) NOT NULL,
    lastUpdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    data longtext NOT NULL,
    deleted tinyint(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    CONSTRAINT plans_ownerId_FK FOREIGN KEY (ownerId) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    UNIQUE KEY plans_id_UNIQUE (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
`);
console.log('Table created');

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 = "${config.db.name}"
      AND table_name <> "db_size";
`);
console.log('View Created');

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