-- Datenbank anlegen: wedding_db
CREATE DATABASE IF NOT EXISTS `wedding_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `wedding_db`;

CREATE TABLE `photos` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `filename` VARCHAR(255) NOT NULL,
  `photographer_name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `upload_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uploader_ip` VARCHAR(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ratings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `photo_id` INT NOT NULL,
  `rating` INT NOT NULL CHECK (`rating` BETWEEN 1 AND 5),
  `rating_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rater_ip` VARCHAR(45) NOT NULL,
  `session_id` VARCHAR(255) NOT NULL,
  FOREIGN KEY (`photo_id`) REFERENCES `photos`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;