create_database.sql

CREATE DATABASE IF NOT EXISTS dapi;

USE dapi;

CREATE TABLE IF NOT EXISTS users(
  id INTEGER NOT NULL AUTO_INCREMENT,
  firstName VARCHAR (255) NOT NULL,
  lastName VARCHAR (255) NOT NULL,
  password VARCHAR (255) NOT NULL,
  email VARCHAR (255) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS roles(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR (255) NOT NULL,
  level INTEGER NOT NULL,
  description TEXT NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS userRoles(
  userId INTEGER NOT NULL,
  roleId INTEGER NOT NULL,
  PRIMARY KEY (userId, roleId),
  FOREIGN KEY (userId) REFERENCES users(id),
  FOREIGN KEY (roleId) REFERENCES roles(id)
);

CREATE TABLE IF NOT EXISTS posts(
  id INTEGER NOT NULL AUTO_INCREMENT,
  userId INTEGER NOT NULL,
  title VARCHAR (255) NOT NULL,
  text VARCHAR (255) NOT NULL,
  imgPath VARCHAR (1000),
  link VARCHAR (1000),
  date DATETIME,
  PRIMARY KEY (id),
  FOREIGN KEY (userId) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS tags(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR (255) NOT NULL,
  priority INTEGER NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS postTags(
  postId INTEGER NOT NULL,
  tagId INTEGER NOT NULL ,
  PRIMARY KEY (postId, tagId),
  FOREIGN KEY (postId) REFERENCES posts(id),
  FOREIGN KEY (tagId) REFERENCES tags(id)
);

CREATE TABLE IF NOT EXISTS photoGalleries(
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR (255) NOT NULL,
  description TEXT,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS photos(
  id INTEGER NOT NULL AUTO_INCREMENT,
  galleryId INTEGER NOT NULL,
  title VARCHAR (255),
  description TEXT,
  date DATETIME,
  photoLink VARCHAR (1000),
  PRIMARY KEY (id),
  FOREIGN KEY (galleryId) REFERENCES photoGalleries(id)
);