------------------------------------------------------------------------------------------
-- PROCEDURE: get_stores_lookup
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_stores_lookup
(
	p_name      IN VARCHAR2,
	a_cursor   OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			store_name,
			store_id
		from stores
		where store_name like p_name||'%'
		order by store_name;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_titles_lookup
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_titles_lookup
(
	p_name      IN VARCHAR2,
	p_isbn      IN VARCHAR2,
	a_cursor   OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			title,
			isbn
		from titles
		where title like p_name||'%'
		  and isbn  like p_isbn||'%'
		order by title;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: insert_order
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE insert_order
(
	p_store_name      IN VARCHAR2,
	p_order_date      IN DATE,
	p_order_id       OUT INTEGER
)
AS
BEGIN
	SELECT orders_seq.NEXTVAL INTO p_order_id FROM DUAL;

	INSERT INTO orders(
		order_id, 
		store_name, 
		order_date)
	VALUES (
		p_order_id, 
		p_store_name,
		p_order_date);
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: insert_order_detail
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE insert_order_detail
(
	p_order_id        IN INTEGER,
	p_isbn            IN CHAR,
	p_quantity        IN INTEGER,
	p_discount        IN NUMBER
)
AS
	v_title_id int;
BEGIN
	SELECT title_id INTO v_title_id FROM titles WHERE isbn = p_isbn;

	INSERT INTO orderdetails(
		order_id, 
		title_id, 
		quantity,
		discount)
	VALUES (
		p_order_id, 
		v_title_id,
		p_quantity,
		p_discount);
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_order
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE delete_order
(
	p_order_id        IN INTEGER
)
AS
BEGIN
	delete from orderdetails where order_id = p_order_id;
	delete from orders       where order_id = p_order_id;
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_order_detail
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE delete_order_detail
(
	p_order_id        IN INTEGER
)
AS
BEGIN
	delete from orderdetails where order_id = p_order_id;
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: validate_order_detail
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE validate_order_detail
(
	p_title           IN VARCHAR2,
	p_isbn            IN CHAR,
	p_quantity        IN INTEGER,
	p_discount        IN NUMBER,
	p_warning_msg    OUT VARCHAR2
)
AS
	v_cnt int;
BEGIN
	p_warning_msg := ''; -- empty string is NO WARNING

	-- Check ISBN
	SELECT count(*) INTO v_cnt FROM titles WHERE isbn = p_isbn;
	IF (v_cnt = 0)
	THEN
		p_warning_msg := 'ISBN can''t be found in DB';
	END IF;

	-- Check TITLE
	SELECT count(*) INTO v_cnt FROM titles WHERE title = p_title;
	IF (v_cnt = 0)
	THEN
		p_warning_msg := 'Title can''t be found in DB';
	END IF;

	-- Check DISCOUNT
	IF (p_discount > 100.0)
	THEN
		p_warning_msg := 'Discount can''t be above 100.0';
	END IF;
END;
/ 
