CREATE TABLE bewerbungen (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bandname VARCHAR(150) NOT NULL,
  ort VARCHAR(100),
  land VARCHAR(100),
  musikstil VARCHAR(100),
  besetzung TEXT,
  biografie TEXT,
  rezensionen TEXT,
  setlaenge INT,
  instagram VARCHAR(255),
  soundcloud VARCHAR(255),
  youtube VARCHAR(255),
  status ENUM('neu', 'angesehen', 'gebucht', 'abgelehnt') DEFAULT 'neu',
  ansprechpartner_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ansprechpartner_id) REFERENCES ansprechpartner(id) ON DELETE SET NULL
);


CREATE TABLE band_image (
  id INT AUTO_INCREMENT PRIMARY KEY,
  band_id CHAR(36),
  image_path VARCHAR(255),
  FOREIGN KEY (band_id) REFERENCES band(id)
);

CREATE TABLE contact (
  email VARCHAR(255) PRIMARY KEY,
  band_id CHAR(36),
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  street VARCHAR(255),
  postal_city VARCHAR(255),
  country VARCHAR(100),
  phone VARCHAR(50),
  FOREIGN KEY (band_id) REFERENCES band(id)
);

CREATE TABLE login (
  email VARCHAR(255) PRIMARY KEY,
  password_hash VARCHAR(255),
  FOREIGN KEY (email) REFERENCES contact(email)
);

CREATE TABLE admin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  password_hash TEXT NOT NULL
);


CREATE TABLE login_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(45),
  username VARCHAR(255),
  success TINYINT(1),
  attempt_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE blocked_ips (
  ip VARCHAR(45) PRIMARY KEY,
  blocked_until DATETIME
);

CREATE TABLE ansprechpartner (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bewerbung_id INT,
  vorname VARCHAR(100),
  nachname VARCHAR(100),
  strasse VARCHAR(150),
  plz VARCHAR(10),
  ort VARCHAR(100),
  land VARCHAR(100),
  email VARCHAR(150) UNIQUE,
  telefon VARCHAR(50),
  FOREIGN KEY (bewerbung_id) REFERENCES bewerbungen(id) ON DELETE CASCADE
);
