text: /* 
  
  [DESCRIPTION]
  Initialise the tables for Bookshop Management Solution via DDL for Data-to-Value bootcamp.
  In case the solution is not deployed via cds deploy command. 
  
  - SAP_CAPIRE_BOOKSHOP_BOOKS: Books master data.
  - SAP_CAPIRE_BOOKSHOP_GENRES: Book Genre
  - SAP_CAPIRE_BOOKSHOP_AUTHORS: Authors master data
  - SAP_CAPIRE_BOOKSHOP_ORDERITEMS: Book sales order line
  
  */
  
  --0.Drop the tables for bookshop if exist
  DROP TABLE SAP_CAPIRE_BOOKSHOP_ORDERITEMS;
  DROP TABLE "SAP_CAPIRE_BOOKSHOP_BOOKS";
  DROP TABLE "SAP_CAPIRE_BOOKSHOP_GENRES";
  DROP TABLE "SAP_CAPIRE_BOOKSHOP_AUTHORS";
  
  --1.Create Book Genres table
  CREATE COLUMN TABLE "SAP_CAPIRE_BOOKSHOP_GENRES"(
  	"NAME" NVARCHAR(255),
  	"ID" INTEGER NOT NULL,
  	PRIMARY KEY (
  		"ID"
  	)
  );
  --"DESCR" NVARCHAR(1000),
  --"PARENT_ID" INTEGER,
  
  --2.Create Authors Table
  CREATE COLUMN TABLE "SAP_CAPIRE_BOOKSHOP_AUTHORS"(
  	"ID" INTEGER NOT NULL,
  	"NAME" NVARCHAR(200),
  	"AVG_RATING" DECIMAL(9, 2),
  	PRIMARY KEY (
  		"ID"
  	)
  );
  
  --Create Table for Books
  CREATE COLUMN TABLE "SAP_CAPIRE_BOOKSHOP_BOOKS"(
  	"ID" INTEGER NOT NULL,
  	"TITLE" NVARCHAR(255),
  	"DESCR" NVARCHAR(2000),
  	"AUTHOR_ID" INTEGER,
  	"GENRE_ID" INTEGER,
  	"STOCK" INTEGER,
  	"PRICE" DECIMAL(9, 2),
  	"CURRENCY_CODE" NVARCHAR(3),
  	"ISBN13" NVARCHAR(13),
  	"AVG_RATING" DECIMAL(9, 2),
  	"RATING_COUNT" INTEGER,
  	"PUBLISHER" NVARCHAR(100),
  	PRIMARY KEY (
  		"ID"
  	)
  )
  WITH ASSOCIATIONS( JOIN "SAP_CAPIRE_BOOKSHOP_AUTHORS" AS "AUTHOR" ON  author . ID  =  author_ID  ,  JOIN "SAP_CAPIRE_BOOKSHOP_GENRES" AS "GENRE" ON  genre . ID  =  genre_ID  );
  
  
  --4.Create table for book order item
  CREATE COLUMN TABLE "SAP_CAPIRE_BOOKSHOP_ORDERITEMS"(
  	order_ID INTEGER NOT NULL,
  	order_date DATE,
  	book_ID INTEGER,
  	Title NVARCHAR(255),
  	PRIMARY KEY (
  		order_ID,
  		book_ID
  	)
  )
  WITH ASSOCIATIONS( MANY  TO  ONE  JOIN "SAP_CAPIRE_BOOKSHOP_BOOKS" AS  book  ON  book . ID  =  book_ID  );
  