CREATE DATABASE gnomeshop;

GRANT ALL ON gnomeshop.* TO gnomeshop@"%" IDENTIFIED BY "gnomeshop";
GRANT ALL ON gnomeshop.* TO gnomeshop@"localhost" IDENTIFIED BY "gnomeshop";

USE gnomeshop;

CREATE TABLE USER 
(
  USER_ID INT NOT NULL AUTO_INCREMENT,
  USER_NAME VARCHAR(50) NOT NULL,
  PW VARCHAR(50) NOT NULL,
  EMAIL_ADDRESS VARCHAR(255) NULL,
  IS_ADMIN VARCHAR(1) NULL,
  IS_BANNED VARCHAR(1) NULL,
  PRIMARY KEY (USER_ID)
);

INSERT INTO USER (USER_NAME, PW, IS_ADMIN, IS_BANNED)
VALUES ('olle','olle', 'Y','N');

INSERT INTO USER (USER_NAME, PW, IS_ADMIN, IS_BANNED)
VALUES ('ingmar','ingmar', 'N','N');


CREATE TABLE ITEM  
(
  ITEM_ID INT NOT NULL AUTO_INCREMENT,
  ITEM_NAME VARCHAR(255) NOT NULL,
  DESCRIPTION VARCHAR(255),
  PRICE INT,
  STOCK INT,
  PRIMARY KEY (ITEM_ID)
);

INSERT INTO ITEM (ITEM_ID, ITEM_NAME, PRICE, STOCK, DESCRIPTION)
VALUES (1, 'Olles Chinese Gnome', 123 , 1100, 'A good gnome from China.' );

INSERT INTO ITEM (ITEM_ID, ITEM_NAME, PRICE, STOCK, DESCRIPTION)
VALUES (2, 'Santa Gnome', 12 , 10, 'Big santa gnome');

INSERT INTO ITEM (ITEM_ID, ITEM_NAME, PRICE, STOCK, DESCRIPTION)
VALUES (3, 'Orcish gnome', 5 , 20, 'This gnome is special.');