ALTER TABLE `kwglobal_users` CHANGE `user_type` `user_type` ENUM('admin','employee','free','private','business','hagel','reporter') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'private';
CREATE TABLE `weather_reports` (`user_id` int(11) NOT NULL,`report_datetime` datetime NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `kwglobal_users` CHANGE `last_changed` `last_changed` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `weather_reports` ADD `postdata` TEXT NOT NULL AFTER `report_datetime`;
ALTER TABLE `weather_reports` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);


-- https://www.phpmyadmin.net/
--
-- Erstellungszeit: 07. Apr 2021 um 12:06
-- Server-Version: 10.3.27-MariaDB-0+deb10u1
-- PHP-Version: 7.3.19-1~deb10u1

START TRANSACTION;

--
-- Datenbank: `portal_userapi`
--

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

--
-- Tabellenstruktur für Tabelle `kwglobal_settings_vh`
--

CREATE TABLE `kwglobal_settings_vh` (
  `settings_key` varchar(64) NOT NULL,
  `settings_data` text NOT NULL,
  `last_access` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_changed` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `kwglobal_settings_vh`
--
ALTER TABLE `kwglobal_settings_vh`
  ADD PRIMARY KEY (`settings_key`) USING BTREE;
COMMIT;



-- https://www.phpmyadmin.net/
--
-- Erstellungszeit: 26. Okt 2021 um 09:59
-- Server-Version: 10.3.29-MariaDB-0+deb10u1
-- PHP-Version: 7.3.29-1~deb10u1

START TRANSACTION;

--
-- Datenbank: `portal_userapi`
--

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

--
-- Tabellenstruktur für Tabelle `kwglobal_activationkeys_xb`
--

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

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

--
-- Tabellenstruktur für Tabelle `kwglobal_config_sessions_xb`
--

CREATE TABLE `kwglobal_config_sessions_xb` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_type` enum('admin','employee','free','private','business','hagel') NOT NULL,
  `sessions` int(10) UNSIGNED NOT NULL DEFAULT 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Tabellenstruktur für Tabelle `kwglobal_pwchangekeys_xb`
--

CREATE TABLE `kwglobal_pwchangekeys_xb` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `pwchange_key` varchar(32) NOT NULL,
  `creation_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Tabellenstruktur für Tabelle `kwglobal_settings_xb`
--

CREATE TABLE `kwglobal_settings_xb` (
  `settings_type` enum('user_id','unique_key') NOT NULL DEFAULT 'user_id',
  `settings_key` varchar(48) NOT NULL,
  `settings_data` text NOT NULL,
  `last_changed` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

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

--
-- Tabellenstruktur für Tabelle `kwglobal_subscriptions_xb`
--

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

--
-- Daten für Tabelle `kwglobal_subscriptions_xb`
--

INSERT INTO `kwglobal_subscriptions_xb` (`user_id`, `rights_package`, `stripe_subscription`, `stripe_plan`, `creation_date`, `last_verified`) VALUES
(1, 'autobahn_soedel', '', '', '2021-10-19 09:45:55', '2021-10-19 09:45:55');

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

--
-- Tabellenstruktur für Tabelle `kwglobal_tokens_xb`
--

CREATE TABLE `kwglobal_tokens_xb` (
  `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,
  `last_seen` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `kwglobal_tokens_xb`
--

INSERT INTO `kwglobal_tokens_xb` (`token`, `authkey`, `user_id`, `user_token_data`, `creation_date`, `last_seen`) VALUES
('T3Qa3UbwupwDFZ9kZECpGJXoexGbNCUv', 'eg9qzKm7W-iYZuGnDqQzHMYdqEHHJYkX', 1, 'a:6:{s:7:\"browser\";s:6:\"Chrome\";s:2:\"ip\";s:12:\"84.139.78.58\";s:6:\"portal\";s:11:\"xb.rued.net\";s:8:\"platform\";s:7:\"Windows\";s:4:\"lang\";s:2:\"xb\";s:7:\"country\";s:2:\"XX\";}', '2021-10-26 07:49:13', '2021-10-26 05:49:13');

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

--
-- Tabellenstruktur für Tabelle `kwglobal_users_xb`
--

CREATE TABLE `kwglobal_users_xb` (
  `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','feuerwehr','winterdienst') NOT NULL DEFAULT 'business',
  `user_has_plan` enum('yes','no') NOT NULL DEFAULT 'yes',
  `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) NOT NULL,
  `creation_date` datetime NOT NULL,
  `last_changed` datetime NOT NULL,
  `last_verified` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `kwglobal_users_xb`
--

INSERT INTO `kwglobal_users_xb` (`user_id`, `user_status`, `user_type`, `user_has_plan`, `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$fsYu34wuykT4Bb5jiGB0vOo12NXGbSmTvJ.8PzAbIaTq7g2AGqNYa', 'a:1:{s:18:\"no_invoice_address\";b:1;}', '', '2018-04-27 08:08:43', '2019-03-19 19:45:08', '2019-04-14 20:41:16'),
(2, 'active', 'employee', 'yes', 'xb', 'xb', 'joerg@kachelmann.com', '$2y$13$0gH/rVMO76vF1VMapiO9cOWErEdyzUD1OBC50Hp9tbjOSdZO/s2w6', NULL, '', '2018-04-27 08:08:57', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(3, 'active', 'admin', 'no', 'xb', 'xb', 'fabian@kachelmann.com', '$2y$13$6dIicOa7kRWXJO9ZZ6LH7eXEMSjjsUY/TFE8nFAQW3AHoh0hj6fqq', NULL, '', '2018-04-27 08:09:05', '0000-00-00 00:00:00', '2019-02-12 09:37:22'),
(4, 'active', 'admin', 'no', 'xb', 'xb', 'janek@kachelmann.com', '$2y$13$Kfykqi9eO0ELthxph6sGOOyNv76N0vjPBCgwfY90U0EMijIj2F/VC', NULL, '', '2018-04-27 08:09:15', '0000-00-00 00:00:00', '2019-02-07 14:40:07'),
(5, 'active', 'admin', 'yes', 'xb', 'xb', 'mark@kachelmann.com', '$2y$13$znVVRfIMIktYp9.qs8f3Du/2fbcC03k7OS0lf2TDeOXhZ1T.S99Bq', NULL, '', '2018-04-27 08:09:24', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(6, 'active', 'employee', 'yes', 'xb', 'xb', 'peter@kachelmann.com', '$2y$13$dtQIJgO/q02ERBqsKnKoT.zifJJB5IsSwAVay0LnfzDmgDKKnZMaS', NULL, '', '2018-04-27 08:09:30', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(7, 'active', 'admin', 'yes', 'xb', 'xb', 'markusb@kachelmann.com', '$2y$13$fsYu34wuykT4Bb5jiGB0vOo12NXGbSmTvJ.8PzAbIaTq7g2AGqNYa', NULL, '', '2018-04-27 08:09:34', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(8, 'active', 'admin', 'yes', 'xb', 'xb', 'miriam@kachelmann.com', '$2y$13$JwMCatfEwfpGjQ/.Ff.TzOofSV.bOnDKpRoq9Xufo/3d8rIDQIWKi', NULL, '', '2018-04-27 08:09:38', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(9, 'active', 'employee', 'yes', 'xb', 'xb', 'stefan@kachelmann.com', '$2y$13$4o8sUk./LaJy8JNo4MYzw.Vn2frJil2alsemqhfLfYg8M20tv9CeS', NULL, '', '2018-04-27 08:09:48', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(10, 'active', 'employee', 'yes', 'xb', 'xb', 'marcel@kachelmann.com', '$2y$13$7eau9yucTNANxJRgQ2ZqSuFsWZCi4Z8HJPwbAM9W8JPxIc9WUz0j.', NULL, '', '2018-04-27 08:13:04', '0000-00-00 00:00:00', '0000-00-00 00:00:00');

--
-- Indizes der exportierten Tabellen
--

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

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

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

--
-- Indizes für die Tabelle `kwglobal_settings_xb`
--
ALTER TABLE `kwglobal_settings_xb`
  ADD PRIMARY KEY (`settings_type`,`settings_key`);

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

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

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

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

--
-- Tabellenstruktur für Tabelle `kwglobal_invoices`
--

CREATE TABLE `kwglobal_invoices_archive` (
                                             `invoice_id_extern` varchar(64) NOT NULL,
                                             `invoice_id_intern` varchar(32) NOT NULL,
                                             `invoice_date` date NOT NULL,
                                             `invoice_amount` decimal(6,2) NOT NULL,
                                             `invoice_currency` varchar(16) NOT NULL,
                                             `invoice_tax_amount` decimal(6,2) NOT NULL DEFAULT '0.00',
                                             `invoice_tax_percentage` decimal(5,2) UNSIGNED DEFAULT '0.00',
                                             `invoice_data_html` text,
                                             `invoice_data_pdf` longblob,
                                             `user_id` int(10) UNSIGNED NOT NULL,
                                             `portal` enum('de','us','xx') NOT NULL DEFAULT 'de',
                                             `stripe_invoice_id` varchar(64) NOT NULL,
                                             `creation_date` datetime NOT NULL,
                                             `last_access` datetime NOT NULL,
                                             `download_counter` smallint(6) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `kwglobal_invoices_archive`
--
ALTER TABLE `kwglobal_invoices_archive`
    ADD PRIMARY KEY (`invoice_id_extern`),
    ADD KEY `user_id` (`user_id`);
COMMIT;