CREATE DATABASE LibrarySystemGrupp12
USE LibrarySystemGrupp12


CREATE TABLE Category (
    categoryID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
    categoryName NVARCHAR(MAX) NOT NULL,
    categoryAbbreviation NVARCHAR(5) NOT NULL,
    maxLoanAllowance INTEGER NOT NULL
)

CREATE TABLE Employee (
    employeeID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
    fName NVARCHAR(MAX) NOT NULL,
    lName NVARCHAR(MAX) NOT NULL,
    email NVARCHAR(MAX) NOT NULL,
    phoneNumber VARCHAR(10) NOT NULL,
    employeeType VARCHAR(MAX) NOT NULL
)

CREATE TABLE LibraryUser (
    userID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
    fName NVARCHAR(MAX) NOT NULL,
    lName NVARCHAR(MAX) NOT NULL,
    email NVARCHAR(MAX) NOT NULL,
    userAddress NVARCHAR(MAX) NOT NULL,
    phoneNumber VARCHAR(10) NOT NULL,
    userType VARCHAR(MAX) NOT NULL, --Student, teacher or researcher
    maxItems INTEGER NOT NULL,
    userCity NVARCHAR(MAX),
    zipCode VARCHAR(5)
)


CREATE TABLE Loan (
    loanID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
    userID INTEGER FOREIGN KEY REFERENCES LibraryUser(userID) NOT NULL,
    employeeID INTEGER FOREIGN KEY REFERENCES Employee(employeeID) NOT NULL,
    dueDate DATE NOT NULL,
    returnedDate DATE NULL,
    returned INT NOT NULL --1 or 0 for true/false
)


CREATE TABLE Item ( --Details
    itemID INTEGER PRIMARY KEY IDENTITY(1,1) NOT NULL,
    categoryID INTEGER FOREIGN KEY REFERENCES Category(categoryID) NOT NULL,
    title NVARCHAR(MAX) NOT NULL,
    itemDescription NVARCHAR(MAX) NOT NULL,
    itemType NVARCHAR(MAX) NOT NULL, --book || dvd
    releaseDate DATE NOT NULL,
    author NVARCHAR(MAX) NOT NULL, --author for books, = director as well
    publisher NVARCHAR(MAX) NULL,
    keywords NVARCHAR(MAX) NOT NULL,
    isbnNumber VARCHAR(13) NOT NULL,
    avaliable INT NOT NULL --true/false (0,1)
    section NVARCHAR(MAX) NOT NULL,
    barcode VARCHAR(30) NOT NULL
)