-- 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;

--
-- 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`);
COMMIT;

