CREATE DATABASE IF NOT EXISTS vpr DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE vpr;

--Jakob
DROP TABLE IF EXISTS campaign_notes;
DROP TABLE IF EXISTS campaign_characters;
DROP TABLE IF EXISTS campaigns;
DROP TABLE IF EXISTS characters;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS users;

-- User
CREATE TABLE users (
  user_id   INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(40) NOT NULL UNIQUE,
  pass_hash VARCHAR(255) NOT NULL
);

CREATE TABLE campaigns (
  campaign_id   INT AUTO_INCREMENT PRIMARY KEY,
  owner_user_id INT NOT NULL,
  title         VARCHAR(100) NOT NULL DEFAULT 'Meine Kampagne',
  description   TEXT NULL,
  UNIQUE KEY ux_owner_one_campaign (owner_user_id),
  CONSTRAINT fk_campaign_owner FOREIGN KEY (owner_user_id)
    REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE characters (
  character_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id      INT NOT NULL,
  campaign_id  INT NULL,
  name         VARCHAR(50) NOT NULL,
  CONSTRAINT fk_char_user FOREIGN KEY (user_id)
    REFERENCES users(user_id) ON DELETE CASCADE,
  CONSTRAINT fk_char_campaign FOREIGN KEY (campaign_id)
    REFERENCES campaigns(campaign_id) ON DELETE SET NULL,
  CONSTRAINT ux_user_char UNIQUE (user_id, name)
);

CREATE TABLE sessions (
  session_id INT AUTO_INCREMENT PRIMARY KEY,
  title      VARCHAR(100) NOT NULL
);

CREATE TABLE campaign_notes (
  note_id     INT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  user_id     INT NOT NULL,
  text        TEXT NOT NULL,
  CONSTRAINT fk_note_campaign FOREIGN KEY (campaign_id)
    REFERENCES campaigns(campaign_id) ON DELETE CASCADE,
  CONSTRAINT fk_note_user FOREIGN KEY (user_id)
    REFERENCES users(user_id) ON DELETE CASCADE
);