-- https://www.phpmyadmin.net/
--
-- Erstellungszeit: 29. Apr 2022 um 10:52
-- Server-Version: 10.3.31-MariaDB-0+deb10u1
-- PHP-Version: 7.3.31-1~deb10u1

START TRANSACTION;



--
-- Datenbank: `portal_userapi_v2`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_config_groups`
--

CREATE TABLE `xb_kwglobal_config_groups` (
  `rights_package` varchar(32) NOT NULL,
  `group_rights` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Daten für Tabelle `xb_kwglobal_config_groups`
--

INSERT INTO `xb_kwglobal_config_groups` (`rights_package`, `group_rights`) VALUES
('authorities', 'settings_set_single'),
('authorities_admin', 'handle_users'),
('authorities_admin', 'settings_set_all'),
('authorities_owner', 'account_owner'),
('authorities_owner', 'handle_users'),
('authorities_owner', 'settings_set_all'),
('authorities_owner', 'settings_set_single'),
('winterservice', 'settings_set_single'),
('winterservice_admin', 'handle_users'),
('winterservice_admin', 'settings_set_all'),
('winterservice_owner', 'account_owner'),
('winterservice_owner', 'handle_users'),
('winterservice_owner', 'settings_set_all'),
('winterservice_owner', 'settings_set_single');

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_config_sessions`
--

CREATE TABLE `xb_kwglobal_config_sessions` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_type` enum('admin','employee','business','stripe','invoice','sales') NOT NULL,
  `sessions` int(10) UNSIGNED NOT NULL DEFAULT 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `xb_kwglobal_config_sessions`
--

INSERT INTO `xb_kwglobal_config_sessions` (`user_id`, `user_type`, `sessions`) VALUES
(0, 'admin', 10),
(0, 'employee', 10),
(0, 'business', 1),
(0, 'stripe', 3),
(0, 'invoice', 3),
(0, 'sales', 3);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_config_subaccounts`
--

CREATE TABLE `xb_kwglobal_config_subaccounts` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `rights_package` varchar(32) NOT NULL,
  `sub_rights_package` varchar(32) NOT NULL,
  `subaccounts` int(10) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `xb_kwglobal_config_subaccounts`
--

INSERT INTO `xb_kwglobal_config_subaccounts` (`user_id`, `rights_package`, `sub_rights_package`, `subaccounts`) VALUES
(0, 'admin', '', 0),
(0, 'authorities_admin', 'authorities', 0),
(0, 'authorities_owner', '', 25),
(0, 'authorities_owner', 'authorities', 0),
(0, 'authorities_owner', 'authorities_admin', 2),
(0, 'authorities_owner', 'firedepartment', 0),
(0, 'authorities_owner', 'firedepartment_admin', 2),
(0, 'authorities_owner', 'winterservice', 0),
(0, 'authorities_owner', 'winterservice_admin', 2),
(0, 'business', '', 0),
(0, 'employee', '', 0),
(0, 'invoice', '', 0),
(0, 'sales', '', 25),
(0, 'stripe', '', 0),
(0, 'winterservice_admin', '', 5),
(0, 'winterservice_admin', 'winterservice', 0),
(0, 'winterservice_owner', '', 10),
(0, 'winterservice_owner', 'winterservice', 0),
(0, 'winterservice_owner', 'winterservice_admin', 2);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_keys_activate`
--

CREATE TABLE `xb_kwglobal_keys_activate` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `one_time_key` varchar(32) NOT NULL,
  `stripe_customer` varchar(48) DEFAULT NULL,
  `stripe_plans` text DEFAULT NULL,
  `is_locked` enum('yes','no') DEFAULT 'no',
  `creation_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `xb_kwglobal_keys_activate`
--


-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_keys_email`
--

CREATE TABLE `xb_kwglobal_keys_email` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `one_time_key` varchar(32) NOT NULL,
  `email_new` varchar(255) NOT NULL,
  `is_locked` enum('yes','no') DEFAULT 'no',
  `creation_date` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_keys_password`
--

CREATE TABLE `xb_kwglobal_keys_password` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `one_time_key` varchar(32) NOT NULL,
  `is_locked` enum('yes','no') DEFAULT 'no',
  `creation_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `xb_kwglobal_keys_password`
--


-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_settings`
--

CREATE TABLE `xb_kwglobal_settings` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `settings_type` enum('single','all') NOT NULL DEFAULT 'single',
  `settings_key` varchar(32) NOT NULL,
  `settings_data` text NOT NULL,
  `last_changed` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Daten für Tabelle `xb_kwglobal_settings`
--


-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_subscriptions`
--

CREATE TABLE `xb_kwglobal_subscriptions` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `rights_package` varchar(32) NOT NULL DEFAULT 'guest',
  `parent_user_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `parent_rights_package` varchar(32) DEFAULT NULL,
  `stripe_subscription` varchar(48) DEFAULT NULL,
  `stripe_plan` varchar(48) DEFAULT NULL,
  `plan` varchar(48) DEFAULT NULL,
  `creation_date` datetime NOT NULL DEFAULT current_timestamp(),
  `last_verified` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `xb_kwglobal_subscriptions`
--


-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_tokens`
--

CREATE TABLE `xb_kwglobal_tokens` (
  `token` varchar(64) NOT NULL,
  `authkey` varchar(64) NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_token_data` text NOT NULL,
  `creation_date` datetime NOT NULL DEFAULT current_timestamp(),
  `last_seen` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `xb_kwglobal_tokens`
--


-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `xb_kwglobal_users`
--

CREATE TABLE `xb_kwglobal_users` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_status` enum('active','inactive','banned','canceled','deleted') NOT NULL DEFAULT 'inactive',
  `user_type` enum('admin','employee','business','stripe','invoice','sales') NOT NULL DEFAULT 'business',
  `is_locked` enum('yes','no') NOT NULL DEFAULT 'no',
  `user_main_portal` enum('xb') NOT NULL DEFAULT 'xb',
  `user_main_tld` varchar(2) NOT NULL DEFAULT 'xb',
  `user_email` varchar(255) NOT NULL,
  `user_password` varchar(255) NOT NULL,
  `user_data` text DEFAULT NULL,
  `stripe_customer` varchar(48) DEFAULT NULL,
  `creation_date` datetime NOT NULL DEFAULT current_timestamp(),
  `last_changed` datetime NOT NULL DEFAULT current_timestamp(),
  `last_verified` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `xb_kwglobal_users`
--

INSERT INTO `xb_kwglobal_users` (`user_id`, `user_status`, `user_type`, `is_locked`, `user_main_portal`, `user_main_tld`, `user_email`, `user_password`, `user_data`, `stripe_customer`, `creation_date`, `last_changed`, `last_verified`) VALUES
(1, 'active', 'admin', 'no', 'xb', 'xb', 'daniel@kachelmann.com', '$2y$13$dtQIJgO/q02ERBqsKnKoT.zifJJB5IsSwAVay0LnfzDmgDKKnZMaS', 'a:2:{s:4:\"test\";s:22:\"asdkjahlsdjkhalksdkasd\";s:18:\"no_invoice_address\";s:4:\"true\";}', 'cus_KJIlanYiWqyjRJ', '2018-04-27 08:08:43', '2022-03-16 14:49:19', '2022-01-20 14:44:39'),
(2, 'active', 'stripe', 'no', 'xb', 'xb', 'joerg@kachelmann.com', '$2y$13$0gH/rVMO76vF1VMapiO9cOWErEdyzUD1OBC50Hp9tbjOSdZO/s2w6', NULL, '', '2018-04-27 08:08:57', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
(3, 'active', 'stripe', 'no', 'xb', 'xb', 'fabian@kachelmann.com', '$2y$13$dtQIJgO/q02ERBqsKnKoT.zifJJB5IsSwAVay0LnfzDmgDKKnZMaS', NULL, '', '2018-04-27 08:09:05', '2022-03-16 14:52:30', '2022-02-01 16:49:13'),
(4, 'active', 'admin', 'no', 'xb', 'xb', 'janek@kachelmann.com', '$2y$13$Kfykqi9eO0ELthxph6sGOOyNv76N0vjPBCgwfY90U0EMijIj2F/VC', NULL, '', '2018-04-27 08:09:15', '2022-01-01 00:00:00', '2019-02-07 14:40:07'),
(5, 'active', 'admin', 'no', 'xb', 'xb', 'mark@kachelmann.com', '$2y$13$znVVRfIMIktYp9.qs8f3Du/2fbcC03k7OS0lf2TDeOXhZ1T.S99Bq', NULL, '', '2018-04-27 08:09:24', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
(7, 'active', 'admin', 'no', 'xb', 'xb', 'markusb@kachelmann.com', '$2y$13$fsYu34wuykT4Bb5jiGB0vOo12NXGbSmTvJ.8PzAbIaTq7g2AGqNYa', NULL, '', '2018-04-27 08:09:34', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
(8, 'active', 'admin', 'no', 'xb', 'xb', 'miriam@kachelmann.com', '$2y$13$JwMCatfEwfpGjQ/.Ff.TzOofSV.bOnDKpRoq9Xufo/3d8rIDQIWKi', NULL, '', '2018-04-27 08:09:38', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
(9, 'active', 'employee', 'no', 'xb', 'xb', 'stefan@kachelmann.com', '$2y$13$4o8sUk./LaJy8JNo4MYzw.Vn2frJil2alsemqhfLfYg8M20tv9CeS', NULL, 'cus_blablabla', '2018-04-27 08:09:48', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
(11, 'active', 'stripe', 'no', 'xb', 'xb', 'marcel@kachelmann.com', '$2y$13$7eau9yucTNANxJRgQ2ZqSuFsWZCi4Z8HJPwbAM9W8JPxIc9WUz0j.', NULL, '', '2018-04-27 08:13:04', '2022-01-01 00:00:00', '2022-02-02 12:38:38'),
(21, 'active', 'stripe', 'no', 'xb', 'xb', 'peter@kachelmann.com', '$2y$13$dtQIJgO/q02ERBqsKnKoT.zifJJB5IsSwAVay0LnfzDmgDKKnZMaS', NULL, 'cus_KJIlanYiWqyjRJ', '2018-04-27 08:09:30', '2022-01-01 00:00:00', '2022-01-01 00:00:00');

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `xb_kwglobal_config_groups`
--
ALTER TABLE `xb_kwglobal_config_groups`
  ADD PRIMARY KEY (`rights_package`,`group_rights`) USING BTREE;

--
-- Indizes für die Tabelle `xb_kwglobal_config_sessions`
--
ALTER TABLE `xb_kwglobal_config_sessions`
  ADD PRIMARY KEY (`user_id`,`user_type`);

--
-- Indizes für die Tabelle `xb_kwglobal_config_subaccounts`
--
ALTER TABLE `xb_kwglobal_config_subaccounts`
  ADD PRIMARY KEY (`user_id`,`rights_package`,`sub_rights_package`) USING BTREE;

--
-- Indizes für die Tabelle `xb_kwglobal_keys_activate`
--
ALTER TABLE `xb_kwglobal_keys_activate`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `activation_key` (`one_time_key`);

--
-- Indizes für die Tabelle `xb_kwglobal_keys_email`
--
ALTER TABLE `xb_kwglobal_keys_email`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `activation_key` (`one_time_key`);

--
-- Indizes für die Tabelle `xb_kwglobal_keys_password`
--
ALTER TABLE `xb_kwglobal_keys_password`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `activation_key` (`one_time_key`);

--
-- Indizes für die Tabelle `xb_kwglobal_settings`
--
ALTER TABLE `xb_kwglobal_settings`
  ADD PRIMARY KEY (`user_id`,`settings_key`,`settings_type`) USING BTREE,
  ADD KEY `UserIdType` (`user_id`,`settings_type`);

--
-- Indizes für die Tabelle `xb_kwglobal_subscriptions`
--
ALTER TABLE `xb_kwglobal_subscriptions`
  ADD PRIMARY KEY (`user_id`,`rights_package`),
  ADD KEY `parents` (`parent_user_id`,`parent_rights_package`);

--
-- Indizes für die Tabelle `xb_kwglobal_tokens`
--
ALTER TABLE `xb_kwglobal_tokens`
  ADD PRIMARY KEY (`token`),
  ADD UNIQUE KEY `authkey` (`authkey`),
  ADD KEY `user_id` (`user_id`,`token`);

--
-- Indizes für die Tabelle `xb_kwglobal_users`
--
ALTER TABLE `xb_kwglobal_users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`);

--
-- AUTO_INCREMENT für exportierte Tabellen
--

--
-- AUTO_INCREMENT für Tabelle `xb_kwglobal_users`
--
ALTER TABLE `xb_kwglobal_users`
  MODIFY `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1000;
COMMIT;

