SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `audiofiles`
-- --------------------------------------------------------

CREATE TABLE `audiofiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(1024) NOT NULL,
  `file_path` varchar(1024) NOT NULL,
  `edited_by` int(11) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `disziplinen`
-- --------------------------------------------------------

CREATE TABLE `disziplinen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(1024) DEFAULT NULL,
  `start_index` int(11) DEFAULT NULL,
  `color_kampfrichter` char(7) NOT NULL DEFAULT '#424242',
  `audiofile` tinyint(1) DEFAULT NULL,
  `audiofile_max_duration` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `einmal_links`
-- --------------------------------------------------------

CREATE TABLE `einmal_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(1000) DEFAULT NULL,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `type` enum('login','pwreset','create_profile','') NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

DELIMITER $$
CREATE TRIGGER `before_insert_login_tokens` 
BEFORE INSERT ON `einmal_links` 
FOR EACH ROW 
BEGIN 
    IF NEW.url IS NULL THEN 
        SET NEW.url = HEX(RANDOM_BYTES(32)); 
    END IF; 
END$$
DELIMITER ;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `gruppen`
-- --------------------------------------------------------

CREATE TABLE `gruppen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT 'KEIN NAME',
  `order_index` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `gruppen_zeiten`
-- --------------------------------------------------------

CREATE TABLE `gruppen_zeiten` (
  `zeitplan_id` int(11) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time DEFAULT NULL,
  `abt_id` int(11) NOT NULL,
  UNIQUE KEY `unique_index` (`zeitplan_id`,`abt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `intern_benutzende`
-- --------------------------------------------------------

CREATE TABLE `intern_benutzende` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(191) DEFAULT NULL,
  `name_person` varchar(255) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `password_cipher` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `edited_by` varchar(20) NOT NULL DEFAULT '0',
  `freigabe` varchar(500) NOT NULL DEFAULT 'keine',
  `login_active` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_page_unique` (`username`,`password_hash`) USING BTREE,
  KEY `indexFreigabe` (`freigabe`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin Seed Entry (Uses Argon2id placeholder for replacement via bash sed script)
INSERT INTO `intern_benutzende` (`username`, `password_hash`, `password_cipher`, `freigabe`, `login_active`)
VALUES ('admin', '__ARGON_HASH_PLACEHOLDER__', 'not_set', '{"types":["wk_leitung","trainer","kampfrichter"],"freigabenTrainer":["admin"],"freigabenKampfrichter":["A"]}', 1);

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `kategorien`
-- --------------------------------------------------------

CREATE TABLE `kategorien` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `programm` text NOT NULL,
  `order_index` int(11) DEFAULT NULL,
  `preis` decimal(10,2) DEFAULT 0.00,
  `aktiv` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `tabellen_konfiguration`
-- --------------------------------------------------------

CREATE TABLE `tabellen_konfiguration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_slug` varchar(128) NOT NULL,
  `json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`json`)),
  `all_noten_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`all_noten_json`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_slug` (`type_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `teilnehmende`
-- --------------------------------------------------------

CREATE TABLE `teilnehmende` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wk_id` int(11) DEFAULT NULL,
  `name` text NOT NULL,
  `vorname` text NOT NULL,
  `geburtsdatum` date NOT NULL,
  `programm` text NOT NULL,
  `verein` text NOT NULL,
  `bezahlt` int(11) NOT NULL DEFAULT 1,
  `bezahltoverride` int(11) NOT NULL DEFAULT 0,
  `betrag_bezahlt` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `wk_index` (`wk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `teilnehmende_audiofiles`
-- --------------------------------------------------------

CREATE TABLE `teilnehmende_audiofiles` (
  `person_id` int(7) NOT NULL,
  `geraet_id` int(7) NOT NULL,
  `audiofile_id` int(7) NOT NULL,
  UNIQUE KEY `unique_combo` (`person_id`,`geraet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `teilnehmende_gruppen`
-- --------------------------------------------------------

CREATE TABLE `teilnehmende_gruppen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `turnerin_id` int(11) NOT NULL,
  `abteilung_id` int(11) NOT NULL,
  `geraet_id` int(11) NOT NULL,
  `turnerin_index` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_turnerin` (`turnerin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `variables`
-- --------------------------------------------------------

CREATE TABLE `variables` (
  `name` varchar(255) NOT NULL DEFAULT 'NaN',
  `value` varchar(255) DEFAULT NULL,
  UNIQUE KEY `uniqueIndex` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `verbuchte_startgebueren`
-- --------------------------------------------------------

CREATE TABLE `verbuchte_startgebueren` (
  `order_id` int(11) NOT NULL,
  `order_type` varchar(100) DEFAULT NULL,
  `preis` float DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `item_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`item_ids`)),
  `used_verein_konten` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `order_status` int(11) NOT NULL DEFAULT 0,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `edited_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `verbuchte_startgebueren_log`
-- --------------------------------------------------------

CREATE TABLE `verbuchte_startgebueren_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `old_order_status` int(11) NOT NULL,
  `new_order_status` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `edited_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `vereine`
-- --------------------------------------------------------

CREATE TABLE `vereine` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `verein` text NOT NULL,
  `email` varchar(100) NOT NULL DEFAULT 'forms@testseite-fh.ch',
  `konto` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `indexturnerinnen` (`id`,`verein`(31))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `warenkorb_startgebueren`
-- --------------------------------------------------------

CREATE TABLE `warenkorb_startgebueren` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `item_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `basket_items_ibfk_1` (`user_id`),
  KEY `basket_items_ibfk_2` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `wertungen`
-- --------------------------------------------------------

CREATE TABLE `wertungen` (
  `person_id` int(11) NOT NULL,
  `note_bezeichnung_id` int(11) NOT NULL,
  `geraet_id` int(11) NOT NULL,
  `wk_id` int(11) NOT NULL,
  `run_number` tinyint(3) NOT NULL DEFAULT 1,
  `value` float DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT NULL,
  `public_value` float DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  UNIQUE KEY `uniquePersonNoteJahrGereat` (`person_id`,`note_bezeichnung_id`,`wk_id`,`geraet_id`,`run_number`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `wertungen_log`
-- --------------------------------------------------------

CREATE TABLE `wertungen_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) NOT NULL,
  `note_bezeichnung_id` int(11) NOT NULL,
  `geraet_id` int(11) NOT NULL,
  `wk_id` int(11) NOT NULL,
  `run_number` tinyint(3) NOT NULL DEFAULT 1,
  `old_value` float DEFAULT NULL,
  `new_value` float DEFAULT NULL,
  `edited_by` int(11) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `wertungstypen`
-- --------------------------------------------------------

CREATE TABLE `wertungstypen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `default_value` float DEFAULT NULL,
  `type` enum('input','berechnung') NOT NULL DEFAULT 'input',
  `berechnung` varchar(255) DEFAULT NULL,
  `berechnung_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`berechnung_json`)),
  `max_value` float DEFAULT NULL,
  `min_value` float DEFAULT NULL,
  `pro_geraet` tinyint(1) NOT NULL DEFAULT 1,
  `anzahl_laeufe_json` text DEFAULT NULL,
  `geraete_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '[]',
  `zeige_auf_rangliste` float DEFAULT NULL,
  `groesse_auf_rangliste` int(11) NOT NULL DEFAULT 0,
  `nullstellen` int(11) NOT NULL DEFAULT 2,
  `display_string` varchar(63) NOT NULL DEFAULT '${note}',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueName` (`name`) USING BTREE,
  KEY `indexMaxValue` (`max_value`),
  KEY `indexMinValue` (`min_value`),
  KEY `indexBerechnungJson` (`berechnung_json`(768)),
  KEY `indexDefaultValue` (`default_value`),
  KEY `indexType` (`type`),
  KEY `indexNullstellen` (`nullstellen`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `wettkaempfe`
-- --------------------------------------------------------

CREATE TABLE `wettkaempfe` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `slug` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- --------------------------------------------------------
-- Tabellenstruktur für Tabelle `zeitplan_types`
-- --------------------------------------------------------

CREATE TABLE `zeitplan_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(2048) NOT NULL,
  `has_endtime` tinyint(1) DEFAULT NULL,
  `display_index` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

COMMIT;