SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `@DBNAME@` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `@DBNAME@`;

CREATE TABLE `@DBPREFIX@sys_config` (
     `config_option` varchar(30) NOT NULL,
     `value` text NOT NULL,
     `user_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `@DBPREFIX@sys_config`
    ADD PRIMARY KEY (`config_option`),
    ADD UNIQUE KEY `config_option` (`config_option`);

CREATE TABLE `@DBPREFIX@sys_log` (
  `log_id` bigint NOT NULL,
  `user_id` int DEFAULT NULL,
  `event` varchar(120) NOT NULL,
  `arguments` varchar(444) DEFAULT NULL,
  `ip_address` varchar(50) NOT NULL,
  `browser` varchar(555) NOT NULL,
  `version` varchar(44) NOT NULL,
  `platform` varchar(44) NOT NULL,
  `is_mobile` varchar(55) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `@DBPREFIX@sys_notifications` (
  `id` int NOT NULL,
  `event` varchar(55) NOT NULL,
  `value` varchar(55) NOT NULL,
  `enable` tinyint(1) NOT NULL DEFAULT '0',
  `user_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `@DBPREFIX@sys_permissions` (
  `perm_id` int UNSIGNED NOT NULL,
  `perm_desc` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `@DBPREFIX@sys_roles` (
  `role_id` int UNSIGNED NOT NULL,
  `role_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `@DBPREFIX@sys_role_perm` (
  `id` int NOT NULL,
  `role_id` int UNSIGNED NOT NULL,
  `perm_id` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `@DBPREFIX@sys_survey` (
  `id` int NOT NULL,
  `interface_satisfaction` int NOT NULL,
  `performance_satisfaction` int NOT NULL,
  `complexity_satisfaction` int NOT NULL,
  `comments` text,
  `user_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `@DBPREFIX@sys_user` (
  `password` text NOT NULL,
  `user_name` varchar(120) NOT NULL,
  `name` varchar(30) NOT NULL,
  `last_name` varchar(90) NOT NULL,
  `expire` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `activation` varchar(32) NOT NULL,
  `admin` tinyint NOT NULL DEFAULT '0',
  `wizard_done` tinyint NOT NULL DEFAULT '0',
  `locked` tinyint NOT NULL DEFAULT '0',
  `idle` int NOT NULL,
  `user_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `@DBPREFIX@sys_user_role` (
  `user_role_id` int NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `role_id` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `@DBPREFIX@sys_log`
  ADD PRIMARY KEY (`log_id`);

ALTER TABLE `@DBPREFIX@sys_permissions`
  ADD PRIMARY KEY (`perm_id`);

ALTER TABLE `@DBPREFIX@sys_roles`
  ADD PRIMARY KEY (`role_id`);

ALTER TABLE `@DBPREFIX@sys_role_perm`
  ADD PRIMARY KEY (`id`),
  ADD KEY `role_id` (`role_id`),
  ADD KEY `perm_id` (`perm_id`);

ALTER TABLE `@DBPREFIX@sys_user`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `email` (`user_name`),
  ADD KEY `email_2` (`user_name`);

ALTER TABLE `@DBPREFIX@sys_user_role`
  ADD PRIMARY KEY (`user_role_id`);

ALTER TABLE `@DBPREFIX@sys_log`
  MODIFY `log_id` bigint NOT NULL AUTO_INCREMENT;

ALTER TABLE `@DBPREFIX@sys_permissions`
  MODIFY `perm_id` int UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `@DBPREFIX@sys_roles`
  MODIFY `role_id` int UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `@DBPREFIX@sys_role_perm`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `@DBPREFIX@sys_user`
  MODIFY `user_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `@DBPREFIX@sys_user_role`
  MODIFY `user_role_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `@DBPREFIX@sys_role_perm`
  ADD CONSTRAINT `@DBPREFIX@sys_role_perm_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `@DBPREFIX@sys_roles` (`role_id`),
  ADD CONSTRAINT `@DBPREFIX@sys_role_perm_ibfk_2` FOREIGN KEY (`perm_id`) REFERENCES `@DBPREFIX@sys_permissions` (`perm_id`);
COMMIT;