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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE TABLE `ws_icing_app_settings` (
  `id` varchar(100) NOT NULL,
  `value` longtext DEFAULT NULL CHECK (json_valid(`value`)),
  `description` mediumtext DEFAULT NULL,
  `modified` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_files` (
  `id` char(36) NOT NULL,
  `uri` varchar(255) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT 0,
  `name` varchar(255) NOT NULL,
  `assoc_id` char(36) DEFAULT NULL,
  `assoc_table` varchar(100) DEFAULT NULL,
  `display_order` int(11) NOT NULL DEFAULT 0,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_permissions` (
  `id` varchar(100) NOT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_roles` (
  `id` char(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_roles_permissions` (
  `id` char(36) NOT NULL,
  `role_id` char(36) DEFAULT NULL,
  `permission_id` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_tracked_emails` (
  `id` char(36) NOT NULL,
  `message_id` varchar(100) NOT NULL,
  `provider` varchar(100) NOT NULL,
  `events` longtext DEFAULT NULL CHECK (json_valid(`events`)),
  `assoc_table` varchar(100) DEFAULT NULL,
  `assoc_id` char(36) NOT NULL,
  `created` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_users` (
  `id` char(36) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `user_group_id` char(36) DEFAULT NULL,
  `secret` varchar(255) DEFAULT NULL,
  `password_reset_token` varchar(255) DEFAULT NULL,
  `dev` tinyint(1) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_user_groups` (
  `id` char(36) NOT NULL,
  `name` varchar(100) NOT NULL,
  `homepage` varchar(255) DEFAULT NULL,
  `default_user_group` tinyint(1) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `ws_icing_user_groups_roles` (
  `id` char(36) NOT NULL,
  `user_group_id` char(36) NOT NULL,
  `role_id` char(36) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `ws_icing_app_settings`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ws_icing_files`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ws_icing_permissions`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ws_icing_roles`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ws_icing_roles_permissions`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `ws_icing_tracked_emails`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ws_icing_emails_unique_message_id` (`message_id`,`provider`);

ALTER TABLE `ws_icing_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `_users_email_uindex` (`email`);

ALTER TABLE `ws_icing_user_groups`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ws_icing_user_groups_name_uindex` (`name`),
  ADD UNIQUE KEY `unique_default_user_group` (`default_user_group`);

ALTER TABLE `ws_icing_user_groups_roles`
  ADD PRIMARY KEY (`id`);

INSERT INTO `ws_icing_user_groups` (`id`, `name`, `homepage`, `default_user_group`, `created`, `modified`) VALUES
    ('admin', 'Admin', '/admin/users', 1, '2025-11-20 23:25:19', '2025-11-20 23:25:19');

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
