
CREATE TABLE "Accounts" (
	account_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	date_account_opened DATETIME, 
	account_name VARCHAR(50), 
	other_account_details VARCHAR(255), 
	PRIMARY KEY (account_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	customer_first_name VARCHAR(50), 
	customer_middle_initial VARCHAR(1), 
	customer_last_name VARCHAR(50), 
	gender VARCHAR(1), 
	email_address VARCHAR(255), 
	login_name VARCHAR(80), 
	login_password VARCHAR(20), 
	phone_number VARCHAR(255), 
	town_city VARCHAR(50), 
	state_county_province VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (customer_id)
)


CREATE TABLE "Financial_Transactions" (
	transaction_id INTEGER NOT NULL, 
	account_id INTEGER NOT NULL, 
	invoice_number INTEGER, 
	transaction_type VARCHAR(15) NOT NULL, 
	transaction_date DATETIME, 
	transaction_amount DECIMAL(19, 4), 
	transaction_comment VARCHAR(255), 
	other_transaction_details VARCHAR(255), 
	FOREIGN KEY(account_id) REFERENCES "Accounts" (account_id), 
	FOREIGN KEY(invoice_number) REFERENCES "Invoices" (invoice_number)
)


CREATE TABLE "Invoice_Line_Items" (
	order_item_id INTEGER NOT NULL, 
	invoice_number INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	product_title VARCHAR(80), 
	product_quantity VARCHAR(50), 
	product_price DECIMAL(19, 4), 
	derived_product_cost DECIMAL(19, 4), 
	derived_vat_payable DECIMAL(19, 4), 
	derived_total_cost DECIMAL(19, 4), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id), 
	FOREIGN KEY(invoice_number) REFERENCES "Invoices" (invoice_number), 
	FOREIGN KEY(order_item_id) REFERENCES "Order_Items" (order_item_id)
)


CREATE TABLE "Invoices" (
	invoice_number INTEGER, 
	order_id INTEGER NOT NULL, 
	invoice_date DATETIME, 
	PRIMARY KEY (invoice_number), 
	FOREIGN KEY(order_id) REFERENCES "Orders" (order_id)
)


CREATE TABLE "Order_Items" (
	order_item_id INTEGER, 
	order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	product_quantity VARCHAR(50), 
	other_order_item_details VARCHAR(255), 
	PRIMARY KEY (order_item_id), 
	FOREIGN KEY(order_id) REFERENCES "Orders" (order_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id)
)


CREATE TABLE "Orders" (
	order_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	date_order_placed DATETIME NOT NULL, 
	order_details VARCHAR(255), 
	PRIMARY KEY (order_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Product_Categories" (
	production_type_code VARCHAR(15), 
	product_type_description VARCHAR(80), 
	vat_rating DECIMAL(19, 4), 
	PRIMARY KEY (production_type_code)
)


CREATE TABLE "Products" (
	product_id INTEGER, 
	parent_product_id INTEGER, 
	production_type_code VARCHAR(15) NOT NULL, 
	unit_price DECIMAL(19, 4), 
	product_name VARCHAR(80), 
	product_color VARCHAR(20), 
	product_size VARCHAR(20), 
	PRIMARY KEY (product_id), 
	FOREIGN KEY(production_type_code) REFERENCES "Product_Categories" (production_type_code)
)

CREATE TABLE "Accounts" (
	account_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	date_account_opened DATETIME, 
	account_name VARCHAR(50), 
	other_account_details VARCHAR(255), 
	PRIMARY KEY (account_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Accounts table:
account_id	customer_id	date_account_opened	account_name	other_account_details
1	8	2016-07-30 22:22:24	900	Regular
2	3	2017-05-29 16:45:17	520	VIP
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	customer_first_name VARCHAR(50), 
	customer_middle_initial VARCHAR(1), 
	customer_last_name VARCHAR(50), 
	gender VARCHAR(1), 
	email_address VARCHAR(255), 
	login_name VARCHAR(80), 
	login_password VARCHAR(20), 
	phone_number VARCHAR(255), 
	town_city VARCHAR(50), 
	state_county_province VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (customer_id)
)

/*
2 rows from Customers table:
customer_id	customer_first_name	customer_middle_initial	customer_last_name	gender	email_address	login_name	login_password	phone_number	town_city	state_county_province	country
1	Dee	A	Larkin	1	thora.torphy@example.org	xhartmann	77789d292604ea04406f	241.796.1219x37862	North Nellie	WestVirginia	USA
2	Brennon	H	Weimann	0	roosevelt.collier@example.org	shayne.lesch	ce97a3e4539347daab96	(943)219-4234x415	South Isabell	Oklahoma	USA
*/


CREATE TABLE "Financial_Transactions" (
	transaction_id INTEGER NOT NULL, 
	account_id INTEGER NOT NULL, 
	invoice_number INTEGER, 
	transaction_type VARCHAR(15) NOT NULL, 
	transaction_date DATETIME, 
	transaction_amount DECIMAL(19, 4), 
	transaction_comment VARCHAR(255), 
	other_transaction_details VARCHAR(255), 
	FOREIGN KEY(account_id) REFERENCES "Accounts" (account_id), 
	FOREIGN KEY(invoice_number) REFERENCES "Invoices" (invoice_number)
)

/*
2 rows from Financial_Transactions table:
transaction_id	account_id	invoice_number	transaction_type	transaction_date	transaction_amount	transaction_comment	other_transaction_details
1	13	12	Payment	2018-03-15 21:13:57	613.9600	None	None
2	9	1	Payment	2018-03-13 13:27:46	368.4600	None	None
*/


CREATE TABLE "Invoice_Line_Items" (
	order_item_id INTEGER NOT NULL, 
	invoice_number INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	product_title VARCHAR(80), 
	product_quantity VARCHAR(50), 
	product_price DECIMAL(19, 4), 
	derived_product_cost DECIMAL(19, 4), 
	derived_vat_payable DECIMAL(19, 4), 
	derived_total_cost DECIMAL(19, 4), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id), 
	FOREIGN KEY(invoice_number) REFERENCES "Invoices" (invoice_number), 
	FOREIGN KEY(order_item_id) REFERENCES "Order_Items" (order_item_id)
)

/*
2 rows from Invoice_Line_Items table:
order_item_id	invoice_number	product_id	product_title	product_quantity	product_price	derived_product_cost	derived_vat_payable	derived_total_cost
14	9	5	prod_name	4	742.3700	191.1100	None	69.8200
3	9	15	prod_name	1	814.8700	176.2900	None	59.5600
*/


CREATE TABLE "Invoices" (
	invoice_number INTEGER, 
	order_id INTEGER NOT NULL, 
	invoice_date DATETIME, 
	PRIMARY KEY (invoice_number), 
	FOREIGN KEY(order_id) REFERENCES "Orders" (order_id)
)

/*
2 rows from Invoices table:
invoice_number	order_id	invoice_date
1	9	2018-03-01 16:40:48
2	9	2018-03-20 00:21:41
*/


CREATE TABLE "Order_Items" (
	order_item_id INTEGER, 
	order_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	product_quantity VARCHAR(50), 
	other_order_item_details VARCHAR(255), 
	PRIMARY KEY (order_item_id), 
	FOREIGN KEY(order_id) REFERENCES "Orders" (order_id), 
	FOREIGN KEY(product_id) REFERENCES "Products" (product_id)
)

/*
2 rows from Order_Items table:
order_item_id	order_id	product_id	product_quantity	other_order_item_details
1	4	4	6	None
2	4	10	7	None
*/


CREATE TABLE "Orders" (
	order_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	date_order_placed DATETIME NOT NULL, 
	order_details VARCHAR(255), 
	PRIMARY KEY (order_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Orders table:
order_id	customer_id	date_order_placed	order_details
1	12	2012-06-27 20:49:56	None
2	12	2012-08-25 07:51:54	None
*/


CREATE TABLE "Product_Categories" (
	production_type_code VARCHAR(15), 
	product_type_description VARCHAR(80), 
	vat_rating DECIMAL(19, 4), 
	PRIMARY KEY (production_type_code)
)

/*
2 rows from Product_Categories table:
production_type_code	product_type_description	vat_rating
Food	Food	15.8400
DVDs	Dvd products	11.4000
*/


CREATE TABLE "Products" (
	product_id INTEGER, 
	parent_product_id INTEGER, 
	production_type_code VARCHAR(15) NOT NULL, 
	unit_price DECIMAL(19, 4), 
	product_name VARCHAR(80), 
	product_color VARCHAR(20), 
	product_size VARCHAR(20), 
	PRIMARY KEY (product_id), 
	FOREIGN KEY(production_type_code) REFERENCES "Product_Categories" (production_type_code)
)

/*
2 rows from Products table:
product_id	parent_product_id	production_type_code	unit_price	product_name	product_color	product_size
1	4	Food	617.9500	Coffee Bean	Red	Medium
2	4	Books	558.4900	Learning French	Yellow	Medium
*/
Count the number of invoices.
SELECT count(*) FROM Invoices
[{'count(*)': 15}]