bookster.schema

DROP TABLE IF EXISTS facilityBookings,favourites,bookables,users,companies;

CREATE TABLE users(
  id int NOT NULL AUTO_INCREMENT,
	email varchar(50) UNIQUE,
	firstName varchar(20) NOT NULL ,
	familyName varchar(20) NOT NULL ,
	password varchar (255) NOT NULL ,
	birth varchar (10) NOT NULL ,
    address varchar(70),
	PRIMARY KEY (id)
);

CREATE TABLE companies(
    id int NOT NULL AUTO_INCREMENT,
    companyAlias varchar(15) UNIQUE ,
    name varchar(30) NOT NULL,
    city varchar(30),
    info varchar (255) NOT NULL,
    type varchar(15) NOT NULL,
    PRIMARY KEY (id)
    );

CREATE TABLE bookables(
    id int NOT NULL AUTO_INCREMENT,
    bookableAlias varchar(15) UNIQUE,
    name varchar(30) NOT NULL,
    company int NOT NULL,
    info varchar(255) NOT NULL,
    type varchar(15) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (company) REFERENCES companies(id)
    );

CREATE TABLE favourites(
    user int NOT NULL,
    bookable int NOT NULL,
    PRIMARY KEY (user, bookable),
    FOREIGN KEY (user) REFERENCES users(id),
    FOREIGN KEY (bookable) REFERENCES bookables(id)
    );

CREATE TABLE facilityBookings(
    bookedBy int,
    bookable int NOT NULL,
    title VARCHAR(30) NOT NULL,
    allDay BOOLEAN NOT NULL,
    start BIGINT NOT NULL,
    end BIGINT,
    descr VARCHAR(255),
    PRIMARY KEY (bookable, start),
    FOREIGN KEY (bookable) REFERENCES bookables(id)
    );


INSERT INTO companies (companyAlias, name, city, info, type)VALUES(
    'byggvestaSthlm',
    'Byggvesta',
    'Stockholm',
    'Rent apartments and book laundromat',
    'company'
    );

INSERT INTO companies (companyAlias, name, city, info, type)VALUES(
    'byggvestaLink',
    'Byggvesta',
    'Linköping',
    'Rent apartments and book laundromat',
    'company'
    );

INSERT INTO companies (companyAlias, name, city, info, type) VALUES(
    'dsektionenLiu',
    'Datateknologsektionen',
    'Linköping',
    'Rent our car etc.',
    'company'
    );
INSERT INTO companies (companyAlias, name, city, info, type) VALUES(
    'msektionenLiu',
    'Maskinteknologsektionen',
    'Linköping',
    'Rent our car etc.',
    'company'
    );

INSERT INTO bookables (bookableAlias, name, company, info, type) VALUES(
    'laundromat1',
    'Laundromat',
    1,
    'Very nice laundromat. Please remember to clean the filters after use.',
    'bookable'
    );
INSERT INTO bookables (bookableAlias, name, company, info, type)VALUES(
    'soccerField1',
    'Soccer Field',
    2,
    'Very nice soccer field.',
    'bookable'
    );

INSERT INTO bookables (bookableAlias, name, company, info, type)VALUES(
    'soccerField2',
    'Soccer Field',
    1,
    'Very nice soccer field.',
    'bookable'
    );

INSERT INTO bookables(bookableAlias, name, company, info, type) VALUES(
    'soccerField3',
    'Soccer Field',
    1,
    'Very nice soccer field.',
    'bookable'
    );

INSERT INTO bookables (bookableAlias, name, company, info, type)VALUES(
    'soccerField4',
    'Soccer Field',
    1,
    'Very nice soccer field.',
    'bookable'
    );

INSERT INTO bookables (bookableAlias, name, company, info, type)VALUES(
    'soccerField5',
    'Soccer Field',
    1,
    'Very nice soccer field.',
    'bookable'
    );

INSERT INTO users (email, firstName, familyName, password, birth, address) VALUES(
    'matildasoderholm@gmail.com',
    'Matilda',
    'Soderholm',
    'password',
    '1993-05-21',
    'Studievägen 5'
    );

INSERT INTO facilityBookings VALUES(
    1,
    1,
    'Laundry',
    FALSE,
    1494852244000,
    1494853244000,
    'Laundry for residents at Byggvesta Stockholm'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     1,
    'Laundry',
     FALSE ,
    1495983600000,
    1495994400000,
    'Laundry for residents at Byggvesta Stockholm'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     1,
    'Laundry',
     FALSE ,
    1496070000000,
    1496080800000,
    'Laundry for residents at Byggvesta Stockholm'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     1,
    'Laundry',
     FALSE ,
    1496156400000,
    1496167200000,
    'Laundry for residents at Byggvesta Stockholm'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     1,
    'Laundry',
     FALSE ,
    1496242800000,
    1496253600000,
    'Laundry for residents at Byggvesta Stockholm'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     1,
    'Laundry',
     FALSE ,
    1496145600000,
    1496156400000,
    'Laundry for residents at Byggvesta Stockholm'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     2,
    'Soccer Field - All day',
     TRUE ,
    1496275200000,
    1496275200000,
    'All day event for Linköpings Kommun'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     2,
    'Soccer Field',
     FALSE ,
    1495983600000,
    1495994400000,
    'Only bookable for soccer practice'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     2,
    'Soccer Field',
     FALSE ,
    1496070000000,
    1496080800000,
    'Only bookable for soccer practice'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     2,
    'Soccer Field',
     FALSE ,
    1496156400000,
    1496167200000,
    'Only bookable for soccer practice'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     2,
    'Soccer Field',
     FALSE ,
    1496242800000,
    1496253600000,
    'Only bookable for soccer practice'
    );

INSERT INTO facilityBookings VALUES(
     NULL,
     2,
    'Soccer Field',
     FALSE ,
    1496145600000,
    1496156400000,
    'Only bookable for soccer practice'
    );