-- phpMyAdmin SQL Dump
-- version 5.0.4deb2+deb11u1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Erstellungszeit: 02. Apr 2026 um 16:16
-- Server-Version: 8.0.34
-- PHP-Version: 7.4.33

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

--
-- Datenbank: `skidepot`
--

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

--
-- Tabellenstruktur für Tabelle `lockers`
--

CREATE TABLE `lockers` (
  `locker_id` int UNSIGNED NOT NULL,
  `locker_number` int NOT NULL,
  `is_online_bookable` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Daten für Tabelle `lockers`
--

INSERT INTO `lockers` (`locker_id`, `locker_number`, `is_online_bookable`, `created_at`, `updated_at`) VALUES
(1, 273, 1, '2026-02-27 09:27:08', '2026-02-27 09:27:08'),
(2, 274, 1, '2026-02-27 09:27:08', '2026-02-27 09:27:08'),
(3, 271, 1, '2026-02-27 13:39:04', '2026-02-27 13:39:04');

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

--
-- Tabellenstruktur für Tabelle `sonnenbahn_booking`
--

CREATE TABLE `sonnenbahn_booking` (
  `booking_id` bigint UNSIGNED NOT NULL,
  `customer_id` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dummy_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `locker_id` int UNSIGNED NOT NULL COMMENT 'Verweis auf lockers',
  `locker_number` int DEFAULT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `guest_mail` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `guest_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `guest_phone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'optional',
  `access_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Code für Metra (RFID, PIN, ...)',
  `status` enum('neu','reserviert','bezahlt','aktiv','abgelaufen','storniert','sync_ok','sync_fehler') COLLATE utf8mb4_unicode_ci DEFAULT 'neu',
  `sync_status` enum('pending','success','failed','not_needed') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `sync_message` text COLLATE utf8mb4_unicode_ci,
  `total_price` decimal(8,2) DEFAULT NULL,
  `payment_method` enum('online','hotel','cash','other') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `reserved_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Daten für Tabelle `sonnenbahn_booking`
--

INSERT INTO `sonnenbahn_booking` (`booking_id`, `customer_id`, `dummy_id`, `locker_id`, `locker_number`, `start_datetime`, `end_datetime`, `guest_mail`, `guest_name`, `guest_phone`, `access_code`, `status`, `sync_status`, `sync_message`, `total_price`, `payment_method`, `created_at`, `reserved_at`, `updated_at`) VALUES
(71, 'cust_69a1b7de472426.06974517', '0009001100000000004D270220261627269F', 1, 273, '2026-02-27 08:00:00', '2026-03-01 20:00:00', 'test@dsdfds.de', 'test2', '4332423432', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-02-27 15:27:26', '2026-02-27 15:27:26', '2026-02-27 15:27:26'),
(72, 'cust_69a1b80314db53.54710202', '0009001100000000004D2702202616280305', 3, 271, '2026-02-28 08:00:00', '2026-02-28 20:00:00', 'sadsa@dsadsa.de', 'test', '34243242', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-02-27 15:28:03', '2026-02-27 15:28:03', '2026-02-27 15:28:03'),
(73, 'cust_69a1b80314db53.54710202', '0009001100000000004D2702202616280371', 2, 274, '2026-02-28 08:00:00', '2026-02-28 20:00:00', 'sadsa@dsadsa.de', 'test', '34243242', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-02-27 15:28:03', '2026-02-27 15:28:03', '2026-02-27 15:28:03'),
(74, 'cust_69a539da0684b2.09134403', '0009001100000000004D0203202608185032', 3, 271, '2026-03-03 08:00:00', '2026-03-14 20:00:00', 'adasd@ddfds.de', 'ABC', '43243432', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-03-02 07:18:50', '2026-03-02 07:18:50', '2026-03-02 07:18:50'),
(75, 'cust_69a539da0684b2.09134403', '0009001100000000004D02032026081850C4', 1, 273, '2026-03-03 08:00:00', '2026-03-14 20:00:00', 'adasd@ddfds.de', 'ABC', '43243432', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-03-02 07:18:50', '2026-03-02 07:18:50', '2026-03-02 07:18:50'),
(76, 'cust_69a539da0684b2.09134403', '0009001100000000004D02032026081850D2', 2, 274, '2026-03-03 08:00:00', '2026-03-14 20:00:00', 'adasd@ddfds.de', 'ABC', '43243432', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-03-02 07:18:50', '2026-03-02 07:18:50', '2026-03-02 07:18:50'),
(77, 'cust_69ce778b5e13e1.91314783', '0009001100000000004D020420261604591F', 3, 271, '2026-04-03 08:00:00', '2026-04-04 20:00:00', 'test@test.de', 'philipp', '3233421321', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-04-02 14:04:59', '2026-04-02 14:04:59', '2026-04-02 14:04:59'),
(78, 'cust_69ce778b5e13e1.91314783', '0009001100000000004D02042026160459C9', 1, 273, '2026-04-03 08:00:00', '2026-04-04 20:00:00', 'test@test.de', 'philipp', '3233421321', NULL, 'bezahlt', 'pending', NULL, NULL, NULL, '2026-04-02 14:04:59', '2026-04-02 14:04:59', '2026-04-02 14:04:59');

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `lockers`
--
ALTER TABLE `lockers`
  ADD PRIMARY KEY (`locker_id`),
  ADD UNIQUE KEY `uk_locker_number` (`locker_number`);

--
-- Indizes für die Tabelle `sonnenbahn_booking`
--
ALTER TABLE `sonnenbahn_booking`
  ADD PRIMARY KEY (`booking_id`),
  ADD KEY `idx_locker_time` (`locker_id`,`start_datetime`,`end_datetime`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_reserved_timeout` (`status`,`reserved_at`),
  ADD KEY `idx_customer_id` (`customer_id`);

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

--
-- AUTO_INCREMENT für Tabelle `lockers`
--
ALTER TABLE `lockers`
  MODIFY `locker_id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT für Tabelle `sonnenbahn_booking`
--
ALTER TABLE `sonnenbahn_booking`
  MODIFY `booking_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=79;

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `sonnenbahn_booking`
--
ALTER TABLE `sonnenbahn_booking`
  ADD CONSTRAINT `sonnenbahn_booking_ibfk_1` FOREIGN KEY (`locker_id`) REFERENCES `lockers` (`locker_id`) ON DELETE RESTRICT;
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 */;
