SQLite Testing Script

--First, creating the tables
  
  CREATE TABLE IF NOT EXISTS Customers (
	UserId TEXT PRIMARY KEY,
	Location TEXT);
  
  CREATE TABLE IF NOT EXISTS Orders (
	BillingId TEXT PRIMARY KEY,
	ProdDescription TEXT NOT NULL,
	UserId TEXT,
	ProductId TEXT NOT NULL);
  
  CREATE TABLE IF NOT EXISTS Products (
	ProductId TEXT PRIMARY KEY,
	Category TEXT NOT NULL,
	SubCategory TEXT NOT NULL,
	Brand TEXT NOT NULL,
	ProdDescription TEXT NOT NULL);
  
  CREATE TABLE IF NOT EXISTS Reviews (
	UniqId TEXT PRIMARY KEY,
	RTitle TEXT,
	RMonth TEXT,
	RRating TEXT,
	RContent TEXT,
	HelpfulR TEXT,
	UserId TEXT NOT NULL,
  BillingId TEXT NOT NULL,
  ProductId TEXT NOT NULL);
  
  --Next, we create temporary tables for the purpose of cleaning up the data set before inserting it into the database
DELETE FROM amazon
WHERE
  (UniqId IS NULL OR UniqId  = '');
  
 CREATE TABLE temp_products AS
SELECT  DISTINCT
   RANK() OVER (
      ORDER BY ProdDescription desc
   ) AS 'ProductId', 
   Category, 
   SubCategory,
   Brand,
   ProdDescription
FROM amazon;

DELETE FROM temp_products
WHERE
  (Category IS NULL OR Category  = '')
  OR (Brand IS NULL OR Brand = '')
  OR (SubCategory IS NULL OR SubCategory = '')
  OR (ProdDescription IS NULL OR ProdDescription = '');
  
CREATE TABLE IF NOT EXISTS temp_reviews (
	UniqId TEXT PRIMARY KEY,
	RTitle TEXT,
	RMonth TEXT,
	RRating TEXT,
	RContent TEXT,
	HelpfulR TEXT,
	UserId TEXT NOT NULL,
  BillingId TEXT NOT NULL,
  ProductId TEXT NOT NULL);
  
INSERT INTO temp_reviews(UniqId, RTitle, RMonth, RRating, RContent, HelpfulR, UserId, BillingId, ProductId)
SELECT UniqId, RTitle, RMonth, RRating, RContent, HelpfulR, UserId, BillingID, ProductID
FROM amazon;

DELETE FROM temp_reviews
WHERE
  (UniqId IS NULL OR UniqId  = '');
  
CREATE TABLE temp_orders AS
SELECT  
   RANK() OVER (
      ORDER BY ProdDescription desc
   ) AS 'ProductId', 
      row_number() OVER (
      ORDER BY BillingId desc
   ) AS 'BillingId',  
   UserId,
   ProdDescription
FROM amazon;

DELETE FROM temp_orders
WHERE
  (UserId IS NULL OR UserId  = '')
  OR (ProductId IS NULL OR ProductId = '')
  OR (ProdDescription IS NULL OR ProdDescription = '');
  
  --We added in triggers to mimick a real-life database
  --Triggers were added in before inserting data to make sure the data we were inserting followed the database rules we have set in place
  
  
  --Trigger 1 - RRating can only take values between 1 and 5
  CREATE TRIGGER bef_update_RRating BEFORE UPDATE ON Reviews
BEGIN
	SELECT CASE
	WHEN ((SELECT Reviews.RRating FROM Reviews WHERE NEW.RRating < 1 OR NEW.RRating > 5) IS NOT NULL)
	THEN RAISE(FAIL, 'ERROR: Invalid rating score.')
END;
END;


--Trigger 2 - Ensuring each order can only have one review
CREATE TRIGGER bef_insert_review BEFORE INSERT ON Reviews
BEGIN
	SELECT CASE
	WHEN ((SELECT Reviews.BillingID FROM Reviews WHERE Reviews.BillingID = NEW.BillingID) IS NOT NULL)
	THEN RAISE(FAIL, 'ERROR: This order already has a review.')
END;
END;

--Trigger 3 - When inserting new foreign key value, the new keys must match a primary key
CREATE TRIGGER bef_insert_orders_UserId BEFORE INSERT ON Orders
BEGIN
	SELECT CASE
	WHEN ((SELECT COUNT(UserId) FROM Customers WHERE Customers.UserId = NEW.UserId) = 0)
	THEN RAISE(FAIL, 'ERROR: Foreign key violation: This UserId does not have a matching record in the database.')
END;
END;

CREATE TRIGGER bef_insert_orders_ProductId BEFORE INSERT ON Orders
BEGIN
	SELECT CASE
	WHEN ((SELECT COUNT(ProductId) FROM Products WHERE Products.ProductId = NEW.ProductId) = 0)
	THEN RAISE(FAIL, 'ERROR: Foreign key violation: This ProductId does not have a matching record in the database.')
END;
END;

CREATE TRIGGER bef_insert_reviews_UserId BEFORE INSERT ON Reviews
BEGIN
	SELECT CASE
	WHEN ((SELECT COUNT(UserId) FROM Customers WHERE Customers.UserId = NEW.UserId) = 0)
	THEN RAISE(FAIL, 'ERROR: Foreign key violation: This UserId does not have a matching record in the database.')
END;
END;

CREATE TRIGGER bef_insert_reviews_BillingId BEFORE INSERT ON Reviews
BEGIN
	SELECT CASE
	WHEN ((SELECT COUNT(BillingId) FROM Orders WHERE Orders.BillingId = NEW.BillingId) = 0)
	THEN RAISE(FAIL, 'ERROR: Foreign key violation: This BillingId does not have a matching record in the database.')
END;
END;

CREATE TRIGGER bef_insert_reviews_ProductId BEFORE INSERT ON Reviews
BEGIN
	SELECT CASE
	WHEN ((SELECT COUNT(ProductId) FROM Products WHERE Products.ProductId = NEW.ProductId) = 0)
	THEN RAISE(FAIL, 'ERROR: Foreign key violation: This ProductId does not have a matching record in the database.')
END;
END;

--Trigger 4 - Records are not allowed to be deleted
CREATE TRIGGER bef_delete_1 BEFORE DELETE ON Customers
BEGIN
	SELECT RAISE(ABORT, 'ERROR: Records cannot be deleted.');
END;

CREATE TRIGGER bef_delete_2 BEFORE DELETE ON Orders
BEGIN
	SELECT RAISE(ABORT, 'ERROR: Records cannot be deleted.');
END;

CREATE TRIGGER bef_delete_3 BEFORE DELETE ON Reviews
BEGIN
	SELECT RAISE(ABORT, 'ERROR: Records cannot be deleted.');
END;

CREATE TRIGGER bef_delete_4 BEFORE DELETE ON Products
BEGIN
	SELECT RAISE(ABORT, 'ERROR: Records cannot be deleted.');
END;

--Now, we insert the values into the database
INSERT INTO Customers (UserId, Location)
SELECT UserId, Location
FROM amazon;

DELETE FROM Customers
WHERE
  (UserId IS NULL OR UserId  = '');
  
INSERT INTO Products(ProductId, Category, SubCategory, Brand, ProdDescription)
SELECT    ProductId, Category, SubCategory, Brand, ProdDescription
FROM temp_products;

INSERT INTO Orders(BillingId, ProdDescription, UserId, ProductId)
SELECT    BillingId, ProdDescription, UserId, ProductId
FROM temp_orders;

INSERT INTO Reviews(UniqId, RTitle, RMonth, RRating,RContent,HelpfulR,UserId,BillingId,ProductId)
SELECT temp_reviews.UniqId, temp_reviews.RTitle, temp_reviews.RMonth, temp_reviews.RRating, temp_reviews.RContent, temp_reviews.HelpfulR, temp_reviews.UserId, Orders.BillingId, Orders.ProductId 
FROM temp_reviews
JOIN Orders ON temp_reviews.UserId = Orders.UserId;

DROP TABLE temp_orders;
DROP TABLE temp_products;
DROP TABLE temp_reviews;
DROP TABLE amazon;

--Now, we double-check that all our restrictions are working

--Testing trigger 1(Lower bound)
UPDATE Reviews
   SET RRating = -5
 WHERE UniqID = "1627ee2055d36db00459430b5ec3790d";
 
 --Testing trigger 1 (Upper bound)
 UPDATE Reviews
   SET RRating = 10
 WHERE UniqID = "1627ee2055d36db00459430b5ec3790d";
 
 --Testing trigger 2 - Inserting another review for an order with an existing review should raise an error
 INSERT INTO Reviews
VALUES ('9999ee2055d36db00459430b5ec3790dnew', 'None', 'July', 5.0, 'Like it', 0, 'AECRD4GDEG7RBMTQDOXCEN6NT74Q', '141', '1');

--Testing trigger 3 - Inserting a UserId which does not exist as a PK elsewhere in the Orders table
INSERT INTO Orders
VALUES ('9000000000000','Desc','TestID','1');

--Testing trigger 3 - Inserting a ProductId which does not exist as a PK elsewhere in the Orders table
INSERT INTO Orders
VALUES ('9000000000000','Desc','AFUNJ4TSQOUYJ7CYJ24MYKCH3QDQ','TestID');

--Testing trigger 3 - Inserting a UserId which does not exist as a PK elsewhere in the Reviews table
INSERT INTO Reviews
VALUES ('New test','Title','June',4.0,'OK','5','TestID','141','1');

--Testing trigger 3 - Inserting a BillingId which does not exist as a PK elsewhere in the Reviews table
INSERT INTO Reviews
VALUES ('New test','Title','June',4.0,'OK','5','AECRD4GDEG7RBMTQDOXCEN6NT74Q','TestID','1');

--Testing trigger 3 - Inserting a ProductId which does not exist as a PK elsewhere in the Reviews table
INSERT INTO Reviews
VALUES ('New test','Title','June',4.0,'OK','5','AECRD4GDEG7RBMTQDOXCEN6NT74Q','142','TestID');

--Testing trigger 4
DELETE FROM Customers;

--Query
SELECT RMonth, round(avg(RRating),1)  As 'Average Rating'
FROM Reviews
GROUP BY RMonth
ORDER BY case when RMonth = 'January' then 1
              when RMonth = 'February' then 2
              when RMonth = 'March' then 3
              when RMonth = 'April' then 4
              when RMonth = 'May' then 5
              when RMonth = 'June' then 6
              when RMonth = 'July' then 7
              when RMonth = 'August' then 8
              when RMonth = 'September' then 9
              when RMonth = 'October' then 10
              when RMonth = 'November' then 11
              else 12
         end;