-- Run before INSERT
SELECT
	"MANSOUR.BEHABADIFORO".MEMBERS.USERID AS "User Id",
	"MANSOUR.BEHABADIFORO".ITEMS.ITEMNUMBER AS "Item Number",
	"MANSOUR.BEHABADIFORO".ITEMS.TITLE AS "Title",
	"MANSOUR.BEHABADIFORO".ITEMS.SUBTITLE AS "Subtitle",
	"MANSOUR.BEHABADIFORO".ITEMS.DESCRIPTION AS "Description",
	CASE WHEN "MANSOUR.BEHABADIFORO".ITEMS.CONDITION = 0 THEN 'New' ELSE 'Used' END "Condition",
	CASE
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS IS NULL OR "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 0 THEN 'Added'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 1 THEN 'Listed'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 2 THEN 'Sold'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 3 THEN 'Unsold'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 4 THEN 'Paid For'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 5 THEN 'Posted'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 6 THEN 'Cancelled'
	END "Item Status",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.QUANTITY AS "Quantity",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.PRICE AS "Price",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.DURATION AS "Duration",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.TOTALLISTINGFEES AS "Total Listing Fees",
	CASE WHEN "MANSOUR.BEHABADIFORO".LISTEDITEMS.ISFEATURED = 1 THEN 'Yes' ELSE 'No' END "Is Featured",
	CASE WHEN "MANSOUR.BEHABADIFORO".LISTEDITEMS.ISHIGHLIGHTED = 1 THEN 'Yes' ELSE 'No' END "Is Highlighted",
	CASE WHEN "MANSOUR.BEHABADIFORO".LISTEDITEMS.ISACTIVE = 1 THEN 'Yes' ELSE 'No' END "Is Active",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.DATETIMELISTED
FROM ("MANSOUR.BEHABADIFORO".MEMBERS INNER JOIN "MANSOUR.BEHABADIFORO".ITEMS ON "MANSOUR.BEHABADIFORO".MEMBERS.MEMBERID = "MANSOUR.BEHABADIFORO".ITEMS.MEMBERID) LEFT OUTER JOIN "MANSOUR.BEHABADIFORO".LISTEDITEMS ON "MANSOUR.BEHABADIFORO".ITEMS.ITEMNUMBER = "MANSOUR.BEHABADIFORO".LISTEDITEMS.ITEMNUMBER
WHERE "MANSOUR.BEHABADIFORO".MEMBERS.MEMBERID = 10;

-- INSERT

INSERT INTO "MANSOUR.BEHABADIFORO".ITEMS (ITEMNUMBER, MEMBERID, TITLE, SUBTITLE, DESCRIPTION, CONDITION, ITEMSTATUS)
	VALUES ( (SELECT MAX(ITEMNUMBER) + 1 FROM "MANSOUR.BEHABADIFORO".ITEMS), 10, 'BRAND NEW Seagate 250GB HD', '', 'Good stuff', 0, 1);
INSERT INTO "MANSOUR.BEHABADIFORO".LISTEDITEMS (ITEMNUMBER, QUANTITY, PRICE, DURATION, TOTALLISTINGFEES, ISFEATURED, ISHIGHLIGHTED, ISACTIVE, DATETIMELISTED)
	VALUES ( (SELECT MAX(ITEMNUMBER) FROM "MANSOUR.BEHABADIFORO".ITEMS), 1, 150, 10, 4.50, 0, 0, 1, CURRENT DATE);

-- Run after INSERT
SELECT
	"MANSOUR.BEHABADIFORO".MEMBERS.USERID AS "User Id",
	"MANSOUR.BEHABADIFORO".ITEMS.ITEMNUMBER AS "Item Number",
	"MANSOUR.BEHABADIFORO".ITEMS.TITLE AS "Title",
	"MANSOUR.BEHABADIFORO".ITEMS.SUBTITLE AS "Subtitle",
	"MANSOUR.BEHABADIFORO".ITEMS.DESCRIPTION AS "Description",
	CASE WHEN "MANSOUR.BEHABADIFORO".ITEMS.CONDITION = 0 THEN 'New' ELSE 'Used' END "Condition",
	CASE
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS IS NULL OR "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 0 THEN 'Added'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 1 THEN 'Listed'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 2 THEN 'Sold'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 3 THEN 'Unsold'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 4 THEN 'Paid For'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 5 THEN 'Posted'
		WHEN "MANSOUR.BEHABADIFORO".ITEMS.ITEMSTATUS = 6 THEN 'Cancelled'
		ELSE 'Unknown'
	END "Item Status",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.QUANTITY AS "Quantity",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.PRICE AS "Price",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.DURATION AS "Duration",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.TOTALLISTINGFEES AS "Total Listing Fees",
	CASE WHEN "MANSOUR.BEHABADIFORO".LISTEDITEMS.ISFEATURED = 1 THEN 'Yes' ELSE 'No' END "Is Featured",
	CASE WHEN "MANSOUR.BEHABADIFORO".LISTEDITEMS.ISHIGHLIGHTED = 1 THEN 'Yes' ELSE 'No' END "Is Highlighted",
	CASE WHEN "MANSOUR.BEHABADIFORO".LISTEDITEMS.ISACTIVE = 1 THEN 'Yes' ELSE 'No' END "Is Active",
	"MANSOUR.BEHABADIFORO".LISTEDITEMS.DATETIMELISTED
FROM ("MANSOUR.BEHABADIFORO".MEMBERS INNER JOIN "MANSOUR.BEHABADIFORO".ITEMS ON "MANSOUR.BEHABADIFORO".MEMBERS.MEMBERID = "MANSOUR.BEHABADIFORO".ITEMS.MEMBERID) LEFT OUTER JOIN "MANSOUR.BEHABADIFORO".LISTEDITEMS ON "MANSOUR.BEHABADIFORO".ITEMS.ITEMNUMBER = "MANSOUR.BEHABADIFORO".LISTEDITEMS.ITEMNUMBER
WHERE "MANSOUR.BEHABADIFORO".MEMBERS.MEMBERID = 10;