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;

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

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

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