# Product:	SQL commands for Web API (for SQLite Backend)
# Version: 	1.08
# Lines starting with '#' are comments.
# Backslash character at the end of line means that the command continues in the next line.

# Create tables
CREATE_TABLE_TBL_CATEGORY=CREATE TABLE IF NOT EXISTS `tbl_category` ( \
  `id` INTEGER PRIMARY KEY AUTOINCREMENT, \
  `category_name` varchar(200) NULL \
)

INSERT_DUMMY_TBL_CATEGORY=INSERT INTO `tbl_category` (`category_name`) VALUES \
('Hardwares'), \
('Toys')

CREATE_TABLE_TBL_PRODUCTS=CREATE TABLE IF NOT EXISTS `tbl_products` ( \
  `id` INTEGER PRIMARY KEY AUTOINCREMENT, \
  `category_id` INTEGER NOT NULL, \
  `product_code` varchar(12) NULL, \
  `product_name` varchar(200) NULL, \
  `product_price` decimal(10,2) DEFAULT '0.00', \
  FOREIGN KEY (`category_id`) REFERENCES `tbl_category` (`id`) \
)

INSERT_DUMMY_TBL_PRODUCTS=INSERT INTO `tbl_products` \
(`category_id`, `product_code`, `product_name`, `product_price`) VALUES \
(2,	'T001',	'Teddy Bear', 99.9), \
(1,	'H001',	'Hammer', 15.75), \
(2,	'T002',	'Optimus Prime', 1000.00)

# CATEGORY
GET_ALL_CATEGORIES=SELECT * FROM `tbl_category`
GET_CATEGORY_BY_ID=SELECT * FROM `tbl_category` WHERE `id` = ?
ADD_NEW_CATEGORY=INSERT INTO `tbl_category` (`category_name`) SELECT ?
EDIT_CATEGORY_BY_ID=UPDATE `tbl_category` SET `category_name` = ? WHERE `id` = ?
REMOVE_CATEGORY_BY_ID=DELETE FROM `tbl_category` WHERE `id` = ?
GET_ID_BY_CATEGORY_NAME=SELECT `id` FROM `tbl_category` WHERE `category_name` = ?

# PRODUCT
GET_ALL_PRODUCTS_BY_CATEGORY=SELECT * FROM `tbl_products` \
WHERE `category_id` = ?
GET_PRODUCT_BY_CATEGORY_AND_ID=SELECT * FROM `tbl_products` \
WHERE `category_id` = ? AND `id` = ?
ADD_NEW_PRODUCT_BY_CATEGORY=INSERT INTO `tbl_products` \
(`category_id`, `product_code`, `product_name`, `product_price`) SELECT ?, ?, ?, ?
EDIT_PRODUCT_BY_CATEGORY_AND_ID=UPDATE `tbl_products` \
SET `category_id` = ?, `product_code` = ?, `product_name` = ?, `product_price` = ? \
WHERE `category_id` = ? AND `id` = ?
REMOVE_PRODUCT_BY_CATEGORY_AND_ID=DELETE FROM `tbl_products` \
WHERE `category_id` = ? AND `id` = ?
GET_ID_BY_CATEGORY_ID_AND_PRODUCT_NAME=SELECT `id` FROM `tbl_products` \
WHERE `category_id` = ? AND `product_name` = ?

# SEARCH
SEARCH_PRODUCT_BY_CATEGORY_CODE_AND_NAME_ONEWORD_ORDERED=SELECT P.id AS aa, \
P.product_code AS bb, C.`category_name` AS cc, P.product_name AS dd, P.product_price AS ee \
FROM `tbl_products` P JOIN `tbl_category` C ON P.`category_id` = C.`id` \
WHERE C.`category_name` LIKE ? OR P.`product_code` LIKE ? OR P.`product_name` LIKE ?
SEARCH_PRODUCT_BY_CATEGORY_CODE_AND_NAME_TWOWORDS_ORDERED=SELECT P.id AS aa, \
P.product_code AS bb, C.`category_name` AS cc, P.product_name AS dd, P.product_price AS ee \
FROM `tbl_products` P JOIN `tbl_category` C ON P.`category_id` = C.`id` \
WHERE C.`category_name` LIKE ? OR P.`product_code` LIKE ? OR P.`product_name` LIKE ? \
OR C.`category_name` LIKE ? OR P.`product_code` LIKE ? OR P.`product_name` LIKE ?

GET_LAST_INSERT_ID=SELECT LAST_INSERT_ROWID()