CREATE TABLE address ( addr_id int not null, addr_street1 varchar(40), addr_street2 varchar(40), addr_city varchar(30), addr_state varchar(20), addr_zip varchar(10), addr_co_id int, PRIMARY KEY(addr_id));
CREATE TABLE bill ( addr_id int not null, addr_street1 varchar(40), addr_street2 varchar(40), addr_city varchar(30), addr_state varchar(20), addr_zip varchar(10), addr_co_id int, PRIMARY KEY(addr_id));
CREATE TABLE ship ( addr_id int not null, addr_street1 varchar(40), addr_street2 varchar(40), addr_city varchar(30), addr_state varchar(20), addr_zip varchar(10), addr_co_id int, PRIMARY KEY(addr_id));
CREATE TABLE author ( a_id int not null, a_fname varchar(20), a_lname varchar(20), a_mname varchar(20), a_dob date, a_bio, PRIMARY KEY(a_id));
CREATE TABLE cc_xacts ( cx_o_id int not null, cx_type varchar(10), cx_num varchar(20), cx_name varchar(30), cx_expire date, cx_auth_id char(15), cx_xact_amt double, cx_xact_date date, cx_co_id int, PRIMARY KEY(cx_o_id));
CREATE TABLE country ( co_id int not null, co_name varchar(50), co_exchange double, co_currency varchar(18), PRIMARY KEY(co_id));
CREATE TABLE bill_co ( co_id int not null, co_name varchar(50), co_exchange double, co_currency varchar(18), PRIMARY KEY(co_id));
CREATE TABLE ship_co ( co_id int not null, co_name varchar(50), co_exchange double, co_currency varchar(18), PRIMARY KEY(co_id));
CREATE TABLE customer ( c_id int not null, c_uname varchar(20), c_passwd varchar(20), c_fname varchar(17), c_lname varchar(17), c_addr_id int, c_phone varchar(18), c_email varchar(50), c_since date, c_last_login date, c_login timestamp, c_expiration timestamp, c_discount real, c_balance double, c_ytd_pmt double, c_birthdate date, c_data, PRIMARY KEY(c_id));
CREATE TABLE item ( i_id int not null, i_title varchar(60), i_a_id int, i_pub_date date, i_publisher varchar(60), i_subject varchar(60), i_desc, i_related1 int, i_related2 int, i_related3 int, i_related4 int, i_related5 int, i_thumbnail varchar(40), i_image varchar(40), i_srp double, i_cost double, i_avail date, i_stock int, i_isbn char(13), i_page int, i_backing varchar(15), i_dimensions varchar(25), PRIMARY KEY(i_id));
CREATE TABLE order_line ( ol_id int not null, ol_o_id int not null, ol_i_id int, ol_qty int, ol_discount double, ol_comments varchar(110), PRIMARY KEY(ol_id, ol_o_id));
CREATE TABLE orders ( o_id int not null, o_c_id int, o_date date, o_sub_total double, o_tax double, o_total double, o_ship_type varchar(10), o_ship_date date, o_bill_addr_id int, o_ship_addr_id int, o_status varchar(15), PRIMARY KEY(o_id));
CREATE TABLE shopping_cart ( sc_id int not null, sc_time timestamp, PRIMARY KEY(sc_id));
CREATE TABLE shopping_cart_line ( scl_sc_id int not null, scl_qty int, scl_i_id int not null, PRIMARY KEY(scl_sc_id, scl_i_id));

# modified getBestSellers
# getBestSellers: SELECT i_id, i_title, a_fname, a_lname, SUM(ol_qty) AS total_qty FROM item, author, order_line WHERE item.i_id = order_line.ol_i_id AND item.i_a_id = author.a_id AND order_line.ol_o_id > 2588667 AND item.i_subject = ? GROUP BY i_id, i_title, a_fname, a_lname ORDER BY total_qty DESC limit 50;

getBestSellers: SELECT i_id, i_title, a_fname, a_lname FROM item, author, order_line WHERE item.i_id = order_line.ol_i_id AND item.i_a_id = author.a_id AND order_line.ol_o_id > 2588667 AND item.i_subject = ?;

# getMostRecentOrder.lines
getMostRecentOrderLines: SELECT * FROM order_line, item WHERE ol_o_id = ? AND ol_i_id = i_id;

# getName
getName: SELECT c_fname,c_lname FROM customer WHERE c_id = ?;

# getBook
getBook: SELECT * FROM item,author WHERE item.i_a_id = author.a_id AND i_id = ?;

# getCustomer
getCustomer: SELECT * FROM customer, address, country WHERE customer.c_addr_id = address.addr_id AND address.addr_co_id = country.co_id AND customer.c_uname = ?;

# doSubjectSearch
doSubjectSearch: SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_subject = ? ORDER BY item.i_title limit 50;

# doTitleSearch
#doTitleSearch: SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND substring(soundex(item.i_title),0,4)=substring(soundex(?),0,4) ORDER BY item.i_title limit 50;
#doTitleSearch: SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND substring(soundex(item.i_title),1,4)=substring(soundex(?),1,4) ORDER BY item.i_title limit 50;
#doTitleSearch: SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_title LIKE ? ORDER BY item.i_title limit 50;

# doAuthorSearch
#doAuthorSearch: SELECT * FROM author, item WHERE substring(soundex(author.a_lname),0,4)=substring(soundex(?),0,4) AND item.i_a_id = author.a_id ORDER BY item.i_title limit 50;
#doAuthorSearch: SELECT * FROM author, item WHERE substring(soundex(author.a_lname),1,4)=substring(soundex(?),1,4) AND item.i_a_id = author.a_id ORDER BY item.i_title limit 50;
#doAuthorSearch: SELECT * FROM author, item WHERE author.a_lname LIKE ? AND item.i_a_id = author.a_id ORDER BY item.i_title limit 50;

# getNewProducts
getNewProducts: SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE item.i_a_id = author.a_id AND item.i_subject = ? ORDER BY item.i_pub_date DESC,item.i_title limit 50;

# getBestSellers
#getBestSellers: SELECT i_id, i_title, a_fname, a_lname FROM item, author, order_line WHERE item.i_id = order_line.ol_i_id AND item.i_a_id = author.a_id AND order_line.ol_o_id > (SELECT MAX(o_id)-3333 FROM orders) AND item.i_subject = ? GROUP BY i_id, i_title, a_fname, a_lname ORDER BY SUM(ol_qty) DESC limit 50;

# getRelated
#getRelated: SELECT J.i_id,J.i_thumbnail from item I, item J where (I.i_related1 = J.i_id or I.i_related2 = J.i_id or I.i_related3 = J.i_id or I.i_related4 = J.i_id or I.i_related5 = J.i_id) and I.i_id = ?;

# adminUpdate
#UPDATE item SET i_cost = ?, i_image = ?, i_thumbnail = ?, i_pub_date = CURRENT_DATE WHERE i_id = ?;
# adminUpdate.related
#adminUpdateRelated: SELECT ol_i_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND NOT (order_line.ol_i_id = ?) AND orders.o_c_id IN (SELECT o_c_id FROM orders, order_line WHERE orders.o_id = order_line.ol_o_id AND orders.o_id > (SELECT MAX(o_id)-10000 FROM orders) AND order_line.ol_i_id = ?) GROUP BY ol_i_id ORDER BY SUM(ol_qty) DESC limit 5;
#UPDATE item SET i_related1 = ?, i_related2 = ?, i_related3 = ?, i_related4 = ?, i_related5 = ? WHERE i_id = ?;

# getUserName
getUserName: SELECT c_uname FROM customer WHERE c_id = ?;

# getPassword
getPassword: SELECT c_passwd FROM customer WHERE c_uname = ?;

# getRelated1
getRelated1: SELECT i_related1 FROM item where i_id = ?;

# getMostRecentOrder.id
getMostRecentOrderId: SELECT o_id FROM customer, orders WHERE customer.c_id = orders.o_c_id AND c_uname = ? ORDER BY o_date, orders.o_id DESC limit 1;

# getMostRecentOrder.order
getMostRecentOrderOrder: SELECT orders.*, customer.*, cc_xacts.cx_type, ship.addr_street1 AS ship_addr_street1, ship.addr_street2 AS ship_addr_street2, ship.addr_state AS ship_addr_state, ship.addr_zip AS ship_addr_zip, ship_co.co_name AS ship_co_name, bill.addr_street1 AS bill_addr_street1, bill.addr_street2 AS bill_addr_street2, bill.addr_state AS bill_addr_state, bill.addr_zip AS bill_addr_zip, bill_co.co_name AS bill_co_name FROM customer, orders, cc_xacts, address AS ship, country AS ship_co, bill, bill_co WHERE orders.o_id = ? AND cx_o_id = orders.o_id AND customer.c_id = orders.o_c_id AND orders.o_bill_addr_id = bill.addr_id AND bill.addr_co_id = bill_co.co_id AND orders.o_ship_addr_id = ship.addr_id AND ship.addr_co_id = ship_co.co_id;

# createEmptyCart
createEmptyCart: SELECT COUNT(*) FROM shopping_cart;
# createEmptyCart.insert
#INSERT into shopping_cart (sc_id, sc_time) VALUES ((SELECT COUNT(*) FROM shopping_cart), CURRENT_TIMESTAMP);
# createEmptyCart.insert.v2
#INSERT into shopping_cart (sc_id, sc_time) VALUES (?, CURRENT_TIMESTAMP);

# addItem
addItem: SELECT scl_qty FROM shopping_cart_line WHERE scl_sc_id = ? AND scl_i_id = ?;
# addItem.update
#UPDATE shopping_cart_line SET scl_qty = ? WHERE scl_sc_id = ? AND scl_i_id = ?;
# addItem.put
#INSERT into shopping_cart_line (scl_sc_id, scl_qty, scl_i_id) VALUES (?,?,?);

# refreshCArt.remove
#DELETE FROM shopping_cart_line WHERE scl_sc_id = ? AND scl_i_id = ?;
# refreshCart.update
#UPDATE shopping_cart_line SET scl_qty = ? WHERE scl_sc_id = ? AND scl_i_id = ?;

# addRandomItemToCartIfNecessary
addRandomItemToCartIfNecessary: SELECT COUNT(*) from shopping_cart_line where scl_sc_id = ?;

# resetCartTime
#UPDATE shopping_cart SET sc_time = CURRENT_TIMESTAMP WHERE sc_id = ?;

# getCart
getCart: SELECT * FROM shopping_cart_line, item WHERE scl_i_id = item.i_id AND scl_sc_id = ?;

# refreshSession
#UPDATE customer SET c_login = NOW(), c_expiration = (CURRENT_TIMESTAMP + INTERVAL 2 HOUR) WHERE c_id = ?;

# createNewCustomer
#INSERT into customer (c_id, c_uname, c_passwd, c_fname, c_lname, c_addr_id, c_phone, c_email, c_since, c_last_login, c_login, c_expiration, c_discount, c_balance, c_ytd_pmt, c_birthdate, c_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
# createNewCustomer.maxId
createNewCustomerMaxId: SELECT max(c_id) FROM customer;

# getCDiscount
getCDiscount: SELECT c_discount FROM customer WHERE customer.c_id = ?;

# getCAddrId
getCAddrId: SELECT c_addr_id FROM customer WHERE customer.c_id = ?;

# getCAddr
getCAddr: SELECT c_addr_id FROM customer WHERE customer.c_id = ?;

# enterCCXact
#INSERT into cc_xacts (cx_o_id, cx_type, cx_num, cx_name, cx_expire, cx_xact_amt, cx_xact_date, cx_co_id) VALUES (?, ?, ?, ?, ?, ?, CURRENT_DATE, (SELECT co_id FROM address, country WHERE addr_id = ? AND addr_co_id = co_id));

# clearCart
#DELETE FROM shopping_cart_line WHERE scl_sc_id = ?;

# enterAddress.id
enterAddressId: SELECT co_id FROM country WHERE co_name = ?;
# enterAddress.match
#enterAddressMatch: SELECT addr_id FROM address WHERE addr_street1 = ? AND addr_street2 = ? AND addr_city = ? AND addr_state = ? AND addr_zip = ? AND addr_co_id = ?;
# enterAddress.insert
#INSERT into address (addr_id, addr_street1, addr_street2, addr_city, addr_state, addr_zip, addr_co_id) VALUES (?, ?, ?, ?, ?, ?, ?);
# enterAddress.maxId
enterAddressMaxId: SELECT max(addr_id) FROM address;

# enterOrder.insert
#INSERT into orders (o_id, o_c_id, o_date, o_sub_total, o_tax, o_total, o_ship_type, o_ship_date, o_bill_addr_id, o_ship_addr_id, o_status) VALUES (?, ?, CURRENT_DATE, ?, 8.25, ?, ?, CURRENT_DATE + INTERVAL ? DAY, ?, ?, 'Pending');
# enterOrder.maxId
enterOrderMaxId: SELECT count(o_id) FROM orders;

# addOrderLine
#INSERT into order_line (ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments) VALUES (?, ?, ?, ?, ?, ?);

# getStock
getStock: SELECT i_stock FROM item WHERE i_id = ?;

# setStock
#UPDATE item SET i_stock = ? WHERE i_id = ?;

# verifyDBConsistency.custId
verifyDBConsistencyCustId: SELECT c_id FROM customer;
# verifyDBConsistency.itemId
verifyDBConsistencyItemId: SELECT i_id FROM item;
# verifyDBConsistency.addrId
verifyDBConsistencyAddrId: SELECT addr_id FROM address;