-- https://www.phpmyadmin.net/
--
-- Erstellungszeit: 18. Mrz 2025 um 05:25
-- Server-Version: 8.0.39-30
-- PHP-Version: 7.2.34

START TRANSACTION;



--
-- Datenbank: `db_376260_24`
--

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

--
-- Tabellenstruktur für Tabelle `broadcasts`
--

CREATE TABLE `broadcasts` (
  `id` int NOT NULL,
  `moderator_id` int DEFAULT NULL,
  `broadcast_date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `image_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `wunschbox_enabled` tinyint(1) DEFAULT '0',
  `wunschbox` tinyint(1) NOT NULL DEFAULT '0',
  `description_color` varchar(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '#ffffff'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten für Tabelle `broadcasts`
--

INSERT INTO `broadcasts` (`id`, `moderator_id`, `broadcast_date`, `start_time`, `end_time`, `title`, `description`, `image_path`, `wunschbox_enabled`, `wunschbox`, `description_color`) VALUES
(24, 9, '2025-03-10', '17:00:00', '18:00:00', 'ich versuchs', 'hoffe es klappt', '', 0, 1, '#ff8000');

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

--
-- Tabellenstruktur für Tabelle `user_wish_count`
--

CREATE TABLE `user_wish_count` (
  `id` int NOT NULL,
  `visitor_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `wish_count` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten für Tabelle `user_wish_count`
--

INSERT INTO `user_wish_count` (`id`, `visitor_name`, `wish_count`) VALUES
(30, 'Mukki', 3);

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

--
-- Tabellenstruktur für Tabelle `wishes`
--

CREATE TABLE `wishes` (
  `id` int NOT NULL,
  `visitor_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `music_wish` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten für Tabelle `wishes`
--

INSERT INTO `wishes` (`id`, `visitor_name`, `music_wish`, `created_at`) VALUES
(92, 'Mukki', 'hab kein', '2025-03-10 16:12:52'),
(93, 'Mukki', 'Song A', '2025-03-17 15:59:12'),
(94, 'Mukki', 'Song B', '2025-03-17 16:00:04');

--
-- Trigger `wishes`
--
DELIMITER $$
CREATE TRIGGER `after_wish_insert` AFTER INSERT ON `wishes` FOR EACH ROW BEGIN
    -- Überprüfen, ob der Benutzer bereits in der Tabelle user_wish_count existiert
    IF EXISTS (SELECT 1 FROM user_wish_count WHERE visitor_name = NEW.visitor_name) THEN
        -- Wenn ja, erhöhe den wish_count um 1
        UPDATE user_wish_count
        SET wish_count = wish_count + 1
        WHERE visitor_name = NEW.visitor_name;
    ELSE
        -- Wenn nein, füge einen neuen Eintrag hinzu
        INSERT INTO user_wish_count (visitor_name, wish_count)
        VALUES (NEW.visitor_name, 1);
    END IF;
END
$$
DELIMITER ;

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

--
-- Tabellenstruktur für Tabelle `wishes_archive`
--

CREATE TABLE `wishes_archive` (
  `id` int NOT NULL,
  `visitor_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `artist` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten für Tabelle `wishes_archive`
--

INSERT INTO `wishes_archive` (`id`, `visitor_name`, `artist`, `title`, `created_at`) VALUES
(6, 'Mukki', 'hab kein', 'hab kein', '2025-03-10 16:12:52');

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `broadcasts`
--
ALTER TABLE `broadcasts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `moderator_id` (`moderator_id`);

--
-- Indizes für die Tabelle `user_wish_count`
--
ALTER TABLE `user_wish_count`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `visitor_name` (`visitor_name`);

--
-- Indizes für die Tabelle `wishes`
--
ALTER TABLE `wishes`
  ADD PRIMARY KEY (`id`);

--
-- Indizes für die Tabelle `wishes_archive`
--
ALTER TABLE `wishes_archive`
  ADD PRIMARY KEY (`id`);

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

--
-- AUTO_INCREMENT für Tabelle `broadcasts`
--
ALTER TABLE `broadcasts`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT für Tabelle `user_wish_count`
--
ALTER TABLE `user_wish_count`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;

--
-- AUTO_INCREMENT für Tabelle `wishes`
--
ALTER TABLE `wishes`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=95;

--
-- AUTO_INCREMENT für Tabelle `wishes_archive`
--
ALTER TABLE `wishes_archive`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `broadcasts`
--
ALTER TABLE `broadcasts`
  ADD CONSTRAINT `broadcasts_ibfk_1` FOREIGN KEY (`moderator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

