-- 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 `users`
--

CREATE TABLE `users` (
  `id` int NOT NULL,
  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `role` enum('admin','moderator') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `image_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Daten für Tabelle `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `role`, `image_path`, `description`) VALUES
(1, 'Mutchatcho', 'Lefty2025', 'admin', 'https://radiotrallacity.de/rahmen/chris.gif', 'Sendeplan\r\nMutchatcho\r\nPW: Lefty2025\r\n--------------------------------\r\nAdmin bereich\r\nhttps://radioadmin.laut.fm/client/21844/\r\nBenutzer : mutchatcho65@gmail.com\r\nPW : SminiSmonty2710+\r\n----------------------------------\r\nRadiochat\r\nTrallajaner   Pw:sminismonty\r\n---------------------------------\r\nElement \r\nmutchatcho:matrix.org  \r\nPw schnegge0815\r\n----------------------------\r\nProfil\r\nMutchatcho  Pw: Schnegge0815\r\n-----------------------------\r\nThemensendung\r\nSchnegge  Pw:Heiligenhafen2025'),
(2, 'Schnegge', 'sminismonty', 'admin', 'https://radiotrallacity.de/rahmen/iloblackmod.gif', 'Sendeplan\r\nSchnegge\r\nPW: sminismonty\r\n----------------\r\nShoutbox\r\nSchnegge   Pw: Schnegge2701+\r\nAdmin shoutbox\r\nName : Citytalk\r\nUsername : Mutchatcho\r\nPW: Mutchatcho1608+\r\n--------------------------\r\nProfil\r\nSchnegge  Pw: Wuppertal2710\r\n---------------------------\r\nElement\r\nschnegge077.matrix.org\r\nPw:wuppertal2710\r\n--------------------------\r\nProfile anlegen\r\nhttps://radiotrallacity.de/profil-register.php\r\n\r\nhttps://radiotrallacity.de/profil-user_profile.php?user_id=22 (in der html ändern)\r\n---------------------\r\nprofil sven /Sven Pw:Brackwede1975'),
(3, 'TrallaKitty', 'Musik', 'moderator', 'https://radiotrallacity.de/rahmen/kitty.gif', 'Sendeplan\r\nTrallaKitty\r\nPW: Musik\r\n------------------------------'),
(4, 'Dartbaer', 'pfeil', 'moderator', 'https://radiotrallacity.de/rahmen/dart.gif', 'Dartbär\r\nPW: dart\r\nPw :pfeil'),
(5, 'Samira2222', 'Holland', 'moderator', 'https://radiotrallacity.de/rahmen/2222.gif', 'Samira2222\r\nPW: Holland'),
(8, 'Die Aybis', 'Berlin', 'moderator', 'https://radiotrallacity.de/rahmen/aybis.gif', 'Die Aybis\r\nPW: Berlin'),
(9, 'Trucker', 'MAN18502', 'moderator', 'https://radiotrallacity.de/rahmen/peter.gif', 'Sendeplan\r\nTrucker  Pw MAN18502\r\n---------------------\r\nShoutbox\r\ntrucker710   Pw scaniafan\r\n-------------------------\r\nProfil\r\nTrucker   Pw:Scania'),
(10, 'Campino', 'Affenhaus', 'moderator', 'https://radiotrallacity.de/rahmen/campi.gif', 'Sendeplan\r\nCampino   Pw: Affenhaus \r\n------------------------------\r\nProfil\r\nCampino  Pw: Vogelhaus\r\n----------------------------\r\nShoutbox\r\nCampino1   Pw:Affenhaus\r\n'),
(11, 'Blacky', 'Vogelhaus', 'moderator', 'https://radiotrallacity.de/rahmen/blacky.gif', 'Sendeplan\r\nBlacky  Pw: Vogelhaus\r\n----------------------\r\nProfil\r\nBlacky   Pw:Affenhaus\r\n'),
(12, 'Flash', 'Baum', 'moderator', 'https://radiotrallacity.de/rahmen/dennis.gif', 'Sendeplan\r\nFlash  Pw: Baum\r\n----------------------\r\nProfil\r\nFlash  Pw: Baum'),
(13, 'Aybi', 'Berlin1', 'moderator', 'https://radiotrallacity.de/rahmen/car.gif', 'Profil\r\nAybi   Pw: Berlin1\r\n-------------------------'),
(14, 'Aybiman', 'Berlin2', 'moderator', 'https://radiotrallacity.de/rahmen/ronny.gif', 'Profil\r\nAybiman  Pw: Berlin2\r\n---------------------'),
(16, 'DuoKaarst', 'Erftsrtasse', 'moderator', 'https://radiotrallacity.de/rahmen/kaarst.png', 'DuoKaarst\r\npw: Erftstrasse'),
(17, 'Exotic', 'Erfurt', 'moderator', 'https://radiotrallacity.de/rahmen/exo.gif', 'Exotic\r\nPw: Erfurt'),
(18, 'Das Chaotische Duo', 'Rattereni', 'moderator', 'https://radiotrallacity.de/rahmen/cduo.gif', 'Das Chaotische Duo\r\nPw: Rattereni'),
(19, 'MrFox', 'Fuchs', 'moderator', 'https://radiotrallacity.de/rahmen/MrFox.gif', 'Fuchs'),
(20, 'MrsFox', 'Fuchsbau', 'moderator', 'https://radiotrallacity.de/rahmen/MrsFox.gif', 'MrsFox\r\nPw: Fuchsbau'),
(21, 'GeburtstagsJumper', 'Happyday', 'moderator', 'https://radiotrallacity.de/rahmen/gebjump.png', 'GeburtstagsJumper\r\nPw: Happyday'),
(22, 'StreamJumper', 'Huepf', 'moderator', 'https://radiotrallacity.de/rahmen/jump.gif', 'GeburtstagsJumper\r\nPw:Huepf'),
(23, 'CityDuo', 'Ficken', 'moderator', 'https://radiotrallacity.de/rahmen/2doof.gif', 'CityDuo\r\nPW: Ficken'),
(24, 'Wutti', 'Duisburg', 'moderator', 'https://radiotrallacity.de/rahmen/wutti.png', 'Wutti\r\nPw; Duisburg'),
(25, 'Florian', 'SCPaderborn', 'moderator', 'https://radiotrallacity.de/rahmen/florian.gif', 'Florian\r\nPw: SCPaderborn'),
(26, 'Mr.Wellington', 'Jette', 'moderator', 'https://radiotrallacity.de/rahmen/udo.gif', 'Mr.Wellington\r\nPw: Jette'),
(27, 'Mrs.Wellington', 'Jemma', 'moderator', 'https://radiotrallacity.de/rahmen/mareen.gif', 'Mrs.Wellington\r\nPw: Jemma');

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

--
-- 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 `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- 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 `users`
--
ALTER TABLE `users`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

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

