-- =====================================================================
-- SQL Init & Setup Script für MariaDB (wtc.dev.waiswuaschd.de)
-- Kochbuch & Essens-Zufallsgenerator (What To Cook?)
-- =====================================================================
-- Ausführung auf dem DB-Server:
-- mariadb --ssl=0 -u root -p < init_mariadb.sql
-- =====================================================================

-- 1. DATENBANK ERSTELLEN
CREATE DATABASE IF NOT EXISTS `v2_db_wtc` 
    DEFAULT CHARACTER SET utf8mb4 
    DEFAULT COLLATE utf8mb4_unicode_ci;

-- 2. BENUTZER ERSTELLEN (Passwort nach Bedarf anpassen)
CREATE USER IF NOT EXISTS 'v2_usr_wtc'@'%' IDENTIFIED BY 'Wtc_Kochbuch_Pass_2026!';
GRANT ALL PRIVILEGES ON `v2_db_wtc`.* TO 'v2_usr_wtc'@'%';
FLUSH PRIVILEGES;

USE `v2_db_wtc`;

-- 3. TABELLE: DISHES (Zufallsgenerator)
CREATE TABLE IF NOT EXISTS `dishes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL UNIQUE,
    `category` VARCHAR(100) NOT NULL,
    `effort` VARCHAR(20) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_dish_category` (`category`),
    INDEX `idx_dish_effort` (`effort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. TABELLE: RECIPES (Digitales Kochbuch)
CREATE TABLE IF NOT EXISTS `recipes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `category` VARCHAR(100) NOT NULL,
    `chapter_number` INT NOT NULL,
    `effort` VARCHAR(20) NOT NULL DEFAULT 'mittel',
    `prep_time` VARCHAR(50) DEFAULT '',
    `cook_time` VARCHAR(50) DEFAULT '',
    `portions` INT DEFAULT 4,
    `ingredients` LONGTEXT NOT NULL,
    `instructions` LONGTEXT NOT NULL,
    `notes` TEXT,
    `source` VARCHAR(255) DEFAULT '',
    `image_url` VARCHAR(500) DEFAULT '',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_recipe_chapter` (`chapter_number`),
    INDEX `idx_recipe_category` (`category`),
    INDEX `idx_recipe_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;