DROP PROCEDURE get_authors
/
DROP PROCEDURE get_authors_count
/
DROP PROCEDURE get_author_picture
/
DROP PROCEDURE get_authors_lookup
/
DROP PROCEDURE get_author_by_id
/
DROP PROCEDURE insert_author
/
DROP PROCEDURE update_author
/
DROP PROCEDURE update_author_picture
/
DROP PROCEDURE delete_author
/

DROP PROCEDURE get_titles
/
DROP PROCEDURE get_titles_count
/
DROP PROCEDURE get_title_picture
/
DROP PROCEDURE insert_title
/
DROP PROCEDURE update_title
/
DROP PROCEDURE update_title_picture
/
DROP PROCEDURE delete_title
/
DROP PROCEDURE get_title_report
/
DROP PROCEDURE get_publishers
/
DROP PROCEDURE get_publishers_count
/
DROP PROCEDURE get_publishers_lookup
/
DROP PROCEDURE get_publisher_by_id
/
DROP PROCEDURE insert_publisher
/
DROP PROCEDURE update_publisher
/
DROP PROCEDURE delete_publisher
/

DROP PROCEDURE get_stores
/
DROP PROCEDURE get_stores_count
/
DROP PROCEDURE insert_store
/
DROP PROCEDURE update_store
/
DROP PROCEDURE delete_store
/

DROP PROCEDURE get_orders
/
DROP PROCEDURE get_orders_count
/
DROP PROCEDURE get_order_details
/

DROP PROCEDURE get_stores_lookup
/
DROP PROCEDURE get_titles_lookup
/
DROP PROCEDURE insert_order
/
DROP PROCEDURE insert_order_detail
/
DROP PROCEDURE delete_order
/
DROP PROCEDURE delete_order_details
/
DROP PROCEDURE validate_order_detail
/


DROP FUNCTION is_valid_isbn
/

DROP FUNCTION is_number
/

DROP FUNCTION is_valid_phone
/

DROP FUNCTION calculate_isbn_check_digit
/

DROP FUNCTION is_valid_postalcode
/

DROP FUNCTION author_name
/

DROP FUNCTION title_name
/

DROP FUNCTION publisher_name
/

DROP FUNCTION store_name
/

DROP FUNCTION calculate_sales_details
/

-- FUNCTIONS --

------------------------------------------------------------------------------------------
-- FUNCTION: is_valid_isbn
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION is_valid_isbn (isbn_in varchar) 
   RETURN char
IS

isbn varchar(30);
isbn_without_check_digit char(12);
check_digit char(1);
sum_total number;
correct_check_digit number;
ret char(1);

BEGIN
               isbn := replace (isbn_in, '-','');
               IF (length (isbn) != 13)  or 
                  (LENGTH(TRIM(TRANSLATE(isbn, ' 0123456789-', ' '))) > 0) or
                  (substr(isbn,1,3) != '978') THEN
                              ret := 'N';
               ELSE
                              isbn_without_check_digit := substr (isbn, 1, 12);
                              check_digit := substr (isbn, -1, 1);
                              sum_total := 0;
                              FOR i IN 1..12 LOOP
                                             IF (mod (i,2) = 0) THEN
                                                           sum_total := sum_total + to_number (substr (isbn_without_check_digit,i,1)) * 3;
                                            ELSE
                                                           sum_total := sum_total + to_number (substr (isbn_without_check_digit,i,1));
                                            END IF; 
                              END LOOP;
                              
                              correct_check_digit := 10 - mod (sum_total,10);

                              IF (correct_check_digit = to_number(check_digit)) THEN
                                            ret := 'Y';
                              ELSE
                                            ret := 'N';
                              END IF;

               END IF;
               
               RETURN ret;                     
END;
/


------------------------------------------------------------------------------------------
-- FUNCTION: is_number
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION is_number (number_in varchar) 
   RETURN char
IS

ret char(1);

BEGIN
	IF (LENGTH(TRIM(TRANSLATE(number_in, ' +-.0123456789', ' '))) > 0) THEN
		ret := 'N';
	ELSE
		ret := 'Y';
	END IF;               
	
	RETURN ret;                     
END;
/

------------------------------------------------------------------------------------------
-- FUNCTION: is_valid_phone
------------------------------------------------------------------------------------------


-- basic check for valid phone numbers:
--	* '+' can be first character (after removing blanks at the beginning
--  * phone can contain any digits and '-'

CREATE OR REPLACE FUNCTION is_valid_phone (phone_number varchar) 
   RETURN char
IS

phone_aux varchar (12);
ret char(1);

BEGIN
	phone_aux := TRIM (phone_number);
	IF (substr (phone_aux, 1, 1) = '+' ) THEN
		phone_aux := substr (phone_aux, 2, length (phone_aux) - 1);
	ELSE
		phone_aux := phone_number;
	END IF;
	
	IF (LENGTH(TRIM(TRANSLATE(phone_aux, ' -0123456789', ' '))) > 0) THEN
		ret := 'N';
	ELSE
		ret := 'Y';
	END IF;               
	
	RETURN ret;                     
END;
/


------------------------------------------------------------------------------------------
-- FUNCTION: calcualte_isbn_check_digit
------------------------------------------------------------------------------------------

-- imput paramter is 12 isbn digits wihtout any space or any other character
-- return:
--		check digit if isbn is ok
--		'I' if isbn is invalid

CREATE OR REPLACE FUNCTION calculate_isbn_check_digit (isbn_in varchar) 
   RETURN char 
IS

sum_total number;
ret char(1);

BEGIN
               IF (length (isbn_in) != 12)  or 
                  (LENGTH(TRIM(TRANSLATE(isbn_in, ' 0123456789', ' '))) > 0) or
                  (substr(isbn_in,1,3) != '978') THEN
                              ret := 'I';
               ELSE
                              sum_total := 0;
                              FOR i IN 1..12 LOOP
                                             IF (mod (i,2) = 0) THEN
                                                           sum_total := sum_total + to_number (substr (isbn_in,i,1)) * 3;
                                            ELSE
                                                           sum_total := sum_total + to_number (substr (isbn_in,i,1));
                                            END IF; 
                              END LOOP;
                              
                              ret := to_char (10 - mod (sum_total,10));

               END IF;
               
               RETURN ret;                     
END;
/

------------------------------------------------------------------------------------------
-- FUNCTION: is_valid_postalcode
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION is_valid_postalcode (postalcode varchar) 
   RETURN char
IS

ret char(1);

BEGIN
	IF INSTR(postalcode, ' ', 1, 1) > 0 THEN
		ret := 'N';
	ELSIF (LENGTH(TRIM(TRANSLATE(postalcode, ' 0123456789', ' '))) > 0) THEN
		ret := 'N';
	ELSE
		ret := 'Y';
	END IF;               
	
	RETURN ret;                     
END;
/

------------------------------------------------------------------------------------------
-- FUNCTION: title_name
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION title_name (p_title_id int ) 
   RETURN varchar
IS

ret varchar(200);

BEGIN
	select title into ret from titles where title_id = p_title_id;
	RETURN ret;                     
END;
/

------------------------------------------------------------------------------------------
-- FUNCTION: author_name
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION author_name (p_author_id int ) 
   RETURN varchar
IS

ret varchar(200);

BEGIN
	select au_fname || ' ' || au_lname into ret from authors where au_id = p_author_id;
	RETURN ret;                     
END;
/

------------------------------------------------------------------------------------------
-- FUNCTION: publisher_name
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION publisher_name (p_pub_id int ) 
   RETURN varchar
IS

ret varchar(200);

BEGIN
	select pub_name into ret from publishers where pub_id = p_pub_id;
	RETURN ret;                     
END;
/

------------------------------------------------------------------------------------------
-- FUNCTION: store_name
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION store_name (p_store_id int ) 
   RETURN varchar
IS

ret varchar(200);

BEGIN
	select store_name into ret from stores where store_id = p_store_id;
	RETURN ret;                     
END;
/


------------------------------------------------------------------------------------------
-- FUNCTION: isbn
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION isbn (p_title_id int ) 
   RETURN varchar
IS

ret varchar(200);

BEGIN
	select isbn into ret from titles where title_id = p_title_id;
	RETURN ret;                     
END;
/

------------------------------------------------------------------------------------------
-- FUNCTION: calculate_sales_details
------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION calculate_sales_details
(
	p_title_id  INTEGER
)
	return varchar
IS
	orderdetils_row orderdetails%rowtype;
	title_price number(8,2);
	total_sales int;
	total_amount number(12,5);
	n_titles int;
	title_name varchar (200);
	ret varchar (300);
	CURSOR orderdetails_cursor is
	SELECT 	order_id, 
		title_id,
		quantity, 
		discount
	FROM orderdetails where title_id = p_title_id;

BEGIN

	SELECT count(*) into n_titles FROM titles where title_id = p_title_id;
	IF n_titles = 0 THEN
		ret := 'Title does not exist';
	ELSE
		total_sales := 0;
		total_amount := 0;	  
		select price into title_price from titles where title_id = p_title_id; 

		OPEN orderdetails_cursor;
		LOOP
			  FETCH orderdetails_cursor into orderdetils_row;
			  EXIT WHEN orderdetails_cursor%notfound;
			  total_sales := total_sales + orderdetils_row.quantity;
			  total_amount := total_amount + (orderdetils_row.quantity*title_price)*(orderdetils_row.discount/100);
		END LOOP;
		select title into title_name from titles where title_id = p_title_id;
		ret := 'Title: ' || title_name || '; Total sales: ' || to_char (total_sales) || '; Total amount ($): ' || to_char (total_amount);
	END IF;
	return ret;
END;
/

-- PROCEDURES --

/* =======================================================================================
** =======================================================================================
** == AUTHORS 
** =======================================================================================
** =======================================================================================
*/

------------------------------------------------------------------------------------------
-- PROCEDURE: get_authors
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_authors
(
          p_au_fname        IN VARCHAR2         default null,
          p_au_lname        IN VARCHAR2         default null,
          p_has_picture     IN INT              default null,    -- boolean: NULL=BOTH, 0=NO-PICTURE, 1=HAS-PICTURE, 
          a_cursor         OUT SYS_REFCURSOR
)
AS
BEGIN
          IF (p_has_picture is null)
          THEN
                   OPEN a_cursor FOR 
                             select 
                                       au_fname as first_name, 
                                       au_lname as last_name, 
                                       (CASE WHEN picture IS NULL THEN 'No' ELSE 'Yes' END) as img,
                                       (select count(*) from titles t where a.au_id = t.au_id) as books,
                                       phone, 
                                       address, 
                                       city, 
                                       state, 
                                       country, 
                                       postalcode, 
                                       au_id
                             from authors a
                             where upper(au_fname) like '%'||upper(trim(p_au_fname))||'%'
                               and upper(au_lname) like '%'||upper(trim(p_au_lname))||'%'
                             order by au_id;
          ELSE
                   OPEN a_cursor FOR 
                             select 
                                       au_fname as first_name, 
                                       au_lname as last_name, 
                                       (CASE WHEN picture IS NULL THEN 'No' ELSE 'Yes' END) as img,
                                       (select count(*) from titles t where a.au_id = t.au_id) as books,
                                       phone, 
                                       address, 
                                       city, 
                                       state, 
                                       country, 
                                       postalcode, 
                                       au_id
                             from authors a
                             where upper(au_fname) like '%'||upper(trim(p_au_fname))||'%'
                               and upper(au_lname) like '%'||upper(trim(p_au_lname))||'%'
                               and ((CASE WHEN picture IS NULL THEN 0 ELSE 1 END) = p_has_picture OR p_has_picture IS NULL) -- NOTE: maybe this can be done in a better way, but I was thinking portability
                             order by au_id;
          END IF;
END;
/


------------------------------------------------------------------------------------------
-- PROCEDURE: get_authors_count
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_authors_count
(
	p_count    OUT INT
)
AS
BEGIN
	select count(*) into p_count
	from authors;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_author_picture
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_author_picture
(
	p_au_id          IN    INTEGER,       --- PK Col
	a_cursor         OUT   SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			picture
		from authors
		where au_id = p_au_id;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_authors_lookup
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_authors_lookup
(
	p_first_name      IN VARCHAR2,
	p_last_name       IN VARCHAR2,
	a_cursor         OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			au_fname as first_name,
			au_lname as last_name,
			au_id 
		from authors
		where upper(au_fname) like '%'||trim(upper(p_first_name))||'%'
--		   or upper(au_lname) like '%'||trim(upper(p_last_name))||'%'
		   and upper(au_lname) like '%'||trim(upper(p_last_name))||'%'
		order by au_fname, au_lname;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_author_by_id
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_author_by_id
(
	p_au_id        IN INT,
	a_cursor      OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			au_fname as first_name,
			au_lname as last_name,
			au_id 
		from authors
		where au_id = p_au_id;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: insert_author
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE insert_author
(
	p_au_fname    IN VARCHAR2,
	p_au_lname    IN VARCHAR2,
	p_phone       IN CHAR,
	p_address     IN VARCHAR2,
	p_city        IN VARCHAR2,
	p_state       IN CHAR,
	p_country     IN VARCHAR2,
	p_postalcode  IN CHAR,
	p_picture     IN BLOB
)
AS

	null_value exception;
	state_length_exceeded exception;
	fname_length_exceeded exception;
	lname_length_exceeded exception;
	phone_length_exceeded exception;
	address_length_exceeded exception;
	city_length_exceeded exception;
	country_length_exceeded exception;
	postalcode_length_exceeded exception;
	invalid_phone exception;
	invalid_postalcode exception;
	err_code varchar(20);
	err_msg varchar(200);

	BEGIN
		IF p_au_fname is null THEN
			raise null_value;
		ELSIF p_au_lname is null THEN
			raise null_value;
		ELSIF (length (rtrim(p_state)) > 2) THEN
			raise state_length_exceeded;
		ELSIF (length (rtrim(p_au_fname)) > 20) THEN
			raise fname_length_exceeded;
		ELSIF (length (rtrim(p_au_lname)) > 40) THEN
			raise lname_length_exceeded;
		ELSIF (length (rtrim(p_phone)) > 12) THEN
			raise phone_length_exceeded;
		ELSIF (length (rtrim(p_address)) > 40) THEN
			raise address_length_exceeded;
		ELSIF (length (rtrim(p_city)) > 20) THEN
			raise city_length_exceeded;
		ELSIF (length (rtrim(p_country)) > 12) THEN
			raise country_length_exceeded;
		ELSIF (length (rtrim(p_postalcode)) > 10) THEN
			raise postalcode_length_exceeded;
		ELSIF (is_valid_postalcode (p_postalcode) = 'N') THEN
			raise invalid_postalcode;
		ELSIF (is_valid_phone (p_phone) = 'N') THEN
			raise invalid_phone;
		ELSE
			INSERT INTO authors(
				au_id, 
				au_fname, 
				au_lname, 
				phone, 
				address, 
				city, 
				state, 
				country, 
				postalcode,
				picture)
			VALUES (
				authors_seq.NEXTVAL, 
				CASE WHEN LENGTH(TRIM(p_au_fname)) > 0 then p_au_fname
					ELSE NULL
				END,
				CASE WHEN LENGTH(TRIM(p_au_lname)) > 0 then p_au_lname
                    	ELSE NULL
                    END,
				p_phone,
				p_address,
				p_city,
				p_state,
				p_country,
				p_postalcode,
				p_picture);
		END IF;
		commit;
		
	EXCEPTION
		WHEN null_value THEN
			raise_application_error (-20003,'Author name and last name cannot be null');
		WHEN state_length_exceeded THEN
			raise_application_error (-20004,'Length for STATE field cannot be greather than 2');
		WHEN fname_length_exceeded THEN
			raise_application_error (-20004,'Length for first name field cannot be greather than 20');
		WHEN lname_length_exceeded THEN
			raise_application_error (-20004,'Length for last name field cannot be greather than 40');
		WHEN phone_length_exceeded THEN
			raise_application_error (-20004,'Length for phone field cannot be greather than 12');
		WHEN address_length_exceeded THEN
			raise_application_error (-20004,'Length for address field cannot be greather than 40');
		WHEN city_length_exceeded THEN
			raise_application_error (-20004,'Length for city field cannot be greather than 20');
		WHEN country_length_exceeded THEN
			raise_application_error (-20004,'Length for country field cannot be greather than 12');
		WHEN postalcode_length_exceeded THEN
			raise_application_error (-20004,'Length for postalcode field cannot be greather than 10');			
		WHEN invalid_postalcode THEN
			raise_application_error (-20005,'Invalid postal code');
		WHEN invalid_phone THEN
			raise_application_error (-20006,'Invalid phone number valid format is + optional at the beginning with digits and -');
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20007, 'Cannot insert author, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: update_author
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE update_author
(
	p_au_id       IN INTEGER,       --- PK Col

	p_au_fname    IN VARCHAR2,
	p_au_lname    IN VARCHAR2,
	p_phone       IN CHAR,
	p_address     IN VARCHAR2,
	p_city        IN VARCHAR2,
	p_state       IN CHAR,
	p_country     IN VARCHAR2,
	p_postalcode  IN CHAR
)
AS

	null_value exception;
	state_length_exceeded exception;
	fname_length_exceeded exception;
	lname_length_exceeded exception;
	phone_length_exceeded exception;
	address_length_exceeded exception;
	city_length_exceeded exception;
	country_length_exceeded exception;
	postalcode_length_exceeded exception;
	invalid_phone exception;
	invalid_postalcode exception;
	err_code varchar(20);
	err_msg varchar(200);
	
	BEGIN
	
		IF p_au_fname is null THEN
			raise null_value;
		ELSIF p_au_lname is null THEN
			raise null_value;
		ELSIF (length (rtrim(p_state)) > 2) THEN
			raise state_length_exceeded;
		ELSIF (length (rtrim(p_au_fname)) > 20) THEN
			raise fname_length_exceeded;
		ELSIF (length (rtrim(p_au_lname)) > 40) THEN
			raise lname_length_exceeded;
		ELSIF (length (rtrim(p_phone)) > 12) THEN
			raise phone_length_exceeded;
		ELSIF (length (rtrim(p_address)) > 40) THEN
			raise address_length_exceeded;
		ELSIF (length (rtrim(p_city)) > 20) THEN
			raise city_length_exceeded;
		ELSIF (length (rtrim(p_country)) > 12) THEN
			raise country_length_exceeded;
		ELSIF (length (rtrim(p_postalcode)) > 10) THEN
			raise postalcode_length_exceeded;
		ELSIF (is_valid_postalcode (p_postalcode) = 'N') THEN
			raise invalid_postalcode;
		ELSIF (is_valid_phone (p_phone) = 'N') THEN
			raise invalid_phone;
		ELSE
			UPDATE authors 
			SET 
				au_fname   = CASE WHEN LENGTH(TRIM(p_au_fname)) > 0 then p_au_fname
							 ELSE NULL
							 END,
				au_lname   = CASE WHEN LENGTH(TRIM(p_au_lname)) > 0 then p_au_lname
							 ELSE NULL
							 END,
				phone      = p_phone,
				address    = p_address,
				city       = p_city,
				state      = p_state,
				country    = p_country,
				postalcode = p_postalcode
			WHERE au_id = p_au_id;
		END IF;
		commit;
		
	EXCEPTION
	
		WHEN null_value THEN
			raise_application_error (-20003,'Author name and last name cannot be null');
		WHEN state_length_exceeded THEN
			raise_application_error (-20004,'Length for state field cannot be greather than 2');
		WHEN fname_length_exceeded THEN
			raise_application_error (-20004,'Length for first name field cannot be greather than 20');
		WHEN lname_length_exceeded THEN
			raise_application_error (-20004,'Length for last name field cannot be greather than 40');
		WHEN phone_length_exceeded THEN
			raise_application_error (-20004,'Length for phone field cannot be greather than 12');
		WHEN address_length_exceeded THEN
			raise_application_error (-20004,'Length for address field cannot be greather than 40');
		WHEN city_length_exceeded THEN
			raise_application_error (-20004,'Length for city field cannot be greather than 20');
		WHEN country_length_exceeded THEN
			raise_application_error (-20004,'Length for country field cannot be greather than 12');
		WHEN postalcode_length_exceeded THEN
			raise_application_error (-20004,'Length for postalcode field cannot be greather than 10');		
		WHEN invalid_postalcode THEN
			raise_application_error (-20005,'Invalid postal code');
		WHEN invalid_phone THEN
			raise_application_error (-20006,'Invalid phone number valid format is + optional at the beginning with digits and -');			
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20007, 'Cannot update author, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);

END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: update_author_picture
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE update_author_picture
(
	p_au_id       IN INTEGER,       --- PK Col

	p_picture     IN BLOB
)
AS
BEGIN
	UPDATE authors 
	SET 
		picture = p_picture
	WHERE au_id = p_au_id;
	commit;
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_author
------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE delete_author
(
        p_au_id  IN INTEGER
)
AS

	n_titles int;
	author_has_titles exception;
	err_code varchar (20);
	err_msg varchar(200);

	BEGIN
	
		   SELECT count(*) into n_titles FROM titles where au_id = p_au_id;
		   IF n_titles > 0 THEN
						  RAISE author_has_titles;
		   ELSE
						  DELETE FROM authors WHERE au_id = p_au_id;
		   END IF;
           
		   commit;
			  
	EXCEPTION
	
		   WHEN author_has_titles THEN
						  raise_application_error (-20001,'You cannot delete author, associated titles exist');
	
		   WHEN OTHERS THEN
			  err_code := to_char (SQLCODE);
			  err_msg := SQLERRM;
			  raise_application_error (-20002, 'Cannot delete author, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
						
END;
/




/* =======================================================================================
** =======================================================================================
** == TITLES 
** =======================================================================================
** =======================================================================================
*/

------------------------------------------------------------------------------------------
-- PROCEDURE: get_titles
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_titles
(
	au_id_list    IN  VARCHAR2           default null, -- CSV list of au_id (integers-as-strings): 1,2,3,4
	pub_id_list   IN  VARCHAR2           default null, -- CSV list of pub_id (integers-as-strings): 1,2,3,4
	p_title       IN  VARCHAR2           default null, 
	p_has_picture IN  INT                default null, -- boolean: NULL=BOTH, 0=NO-PICTURE, 1=HAS-PICTURE, 
	a_cursor      OUT SYS_REFCURSOR
)
AS
BEGIN
	-- Get titles by: a list of AUTHORS: id1, id2, id3
	IF ( au_id_list is not null )
	THEN
		OPEN a_cursor FOR 
		SELECT
			title,
			(CASE WHEN picture IS NULL THEN 'No' ELSE 'Yes' END) as img,
			type,
			isbn,
			(select a.au_fname||' '||a.au_lname from authors a    where a.au_id  = t.au_id)  as author_name,
			(select p.pub_name from publishers p where p.pub_id = t.pub_id) as publisher_name,
			pubdate,
			price,
			advance,
			total_sales,
			notes,
			contract,
			title_id,
			au_id,
			pub_id 
		from titles t
		where instr (','||au_id_list||',', ','||to_char(au_id)||',') > 0
		order by title_id;

	-- Get titles by: a list of PUBLISHERS: id1, id2, id3
	ELSIF ( pub_id_list is not null )
	THEN
		OPEN a_cursor FOR 
		SELECT  
			title,
			(CASE WHEN picture IS NULL THEN 'No' ELSE 'Yes' END) as img,
			type,
			isbn,
			(select a.au_fname||' '||a.au_lname from authors a    where a.au_id  = t.au_id)  as author_name,
			(select p.pub_name                    from publishers p where p.pub_id = t.pub_id) as publisher_name,
			pubdate,
			price,
			advance,
			total_sales,
			notes,
			contract,
			title_id,
			au_id,
			pub_id 
			from titles t
		where instr (','||pub_id_list||',', ','||to_char(pub_id)||',') > 0
		order by title_id;

	-- Get ALL titles
	ELSE
		OPEN a_cursor FOR 
		SELECT 
			title, 
			(CASE WHEN picture IS NULL THEN 'No' ELSE 'Yes' END) as img,
			type, 
			isbn, 
			(select a.au_fname||' '||a.au_lname from authors a    where a.au_id  = t.au_id)  as author_name, 
			(select p.pub_name                  from publishers p where p.pub_id = t.pub_id) as publisher_name, 
			pubdate, 
			price, 
			advance, 
			total_sales, 
			notes, 
			contract, 
			title_id, 
			au_id, 
			pub_id 
		from titles t
		where upper(title) like '%'||trim(upper(p_title))||'%'
		  and ((CASE WHEN picture IS NULL THEN 0 ELSE 1 END) = p_has_picture OR p_has_picture IS NULL) -- NOTE: maybe this can be done in a better way, but I was thinking partability
		order by title_id;
	END IF;
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: get_titles_count
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_titles_count
(
	p_count    OUT INT
)
AS
BEGIN
	select count(*) into p_count
	from titles;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_title_picture
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_title_picture
(
	p_title_id       IN    INTEGER,       --- PK Col
	a_cursor         OUT   SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			picture
		from titles
		where title_id = p_title_id;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: insert_title
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE insert_title
(
	p_title        IN VARCHAR2,
	p_type         IN CHAR,
	p_price        IN NUMBER ,
	p_advance      IN NUMBER ,
	p_total_sales  IN INTEGER,
	p_notes        IN VARCHAR2,
	p_pubdate      IN DATE,
	p_contract     IN SMALLINT,
	p_isbn         IN CHAR,
--	p_au_id        IN INTEGER,
--	p_pub_id       IN INTEGER,
	p_au_fname     IN VARCHAR2,
	p_au_lname     IN VARCHAR2,
	p_pub_name     IN VARCHAR2,
	p_picture      IN BLOB
)
AS

	count_publishers int;
	count_authors int;
	v_au_id int;
	v_pub_id int;
	publisher_does_not_exist exception;
	author_does_not_exist exception;
	null_value exception;
	title_length_exceeded exception;
	type_length_exceeded exception;
	notes_length_exceeded exception;
	isbn_length_exceeded exception;
	invalid_isbn exception;
	err_code varchar (20);
	err_msg varchar(200);
	
	BEGIN
		SELECT pub_id into v_pub_id FROM publishers where pub_name = p_pub_name;
		SELECT au_id  into v_au_id  FROM authors    where au_fname = p_au_fname and au_lname = p_au_lname;

		SELECT count(*) into count_publishers FROM publishers where pub_id = v_pub_id;
		SELECT count(*) into count_authors    FROM authors    where au_id  = v_au_id;
		IF count_authors = 0 THEN
	     	RAISE author_does_not_exist;
		ELSIF count_publishers = 0 THEN
	     	RAISE publisher_does_not_exist;
	    ELSIF p_title is null THEN
	     	raise null_value;
		ELSIF p_contract is null THEN
			raise null_value;
	    ELSIF (length (rtrim(p_title)) > 80) THEN
			raise title_length_exceeded;
	    ELSIF (length (rtrim(p_type)) > 12) THEN
			raise type_length_exceeded;
	    ELSIF (length (rtrim(p_notes)) > 200) THEN
			raise notes_length_exceeded;
	    ELSIF (length (rtrim(p_isbn)) > 17) THEN
	        raise isbn_length_exceeded;
		ELSIF is_valid_isbn (p_isbn) = 'N' THEN
	     	raise invalid_isbn;
		ELSE
			INSERT INTO titles(
				title_id, 
				title, 
				type, 
				pub_id, 
				price, 
				advance, 
				total_sales, 
				notes, 
				pubdate, 
				contract, 
				au_id, 
				isbn,
				picture)
			VALUES (
				titles_seq.NEXTVAL, 
				CASE WHEN LENGTH(TRIM(p_title)) > 0 then p_title
                     ELSE NULL
                END,
				p_type,
				v_pub_id,
				p_price,
				p_advance,
				p_total_sales,
				p_notes,
				p_pubdate,
				p_contract,
				v_au_id,
				p_isbn,
				p_picture);
		END IF;

		commit;
		
	EXCEPTION
	
		WHEN author_does_not_exist THEN
			raise_application_error (-20006,'Specified author does not exist, enter a valid author.');
		WHEN publisher_does_not_exist THEN
			raise_application_error (-20006,'Specified publisher does not exist, enter a valid publisher.');
		WHEN title_length_exceeded THEN
			raise_application_error (-20007,'Length for title field cannot be greather than 80');
		WHEN type_length_exceeded THEN
			raise_application_error (-20008,'Length for type field cannot be greather than 12');
		WHEN notes_length_exceeded THEN
			raise_application_error (-20009,'Length for notes field cannot be greather than 200');
		WHEN isbn_length_exceeded THEN
			raise_application_error (-20010,'Length for isbn field cannot be greather than 80');
		WHEN invalid_isbn THEN
			raise_application_error (-20011,'Invalid isbn, check the isbn code');			
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20015, 'Cannot insert title, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: update_title
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE update_title
(
	p_title_id     IN INTEGER,       --- PK Col

	p_title        IN VARCHAR2,
	p_type         IN CHAR,
	p_price        IN NUMBER ,
	p_advance      IN NUMBER ,
	p_total_sales  IN INTEGER,
	p_notes        IN VARCHAR2,
	p_pubdate      IN DATE,
	p_contract     IN SMALLINT,
	p_isbn         IN CHAR,
--	p_au_id        IN INTEGER,
--	p_pub_id       IN INTEGER
	p_au_fname     IN VARCHAR2,
	p_au_lname     IN VARCHAR2,
	p_pub_name     IN VARCHAR2
)
AS

	count_publishers int;
	count_authors int;
	v_au_id int;
	v_pub_id int;
	publisher_does_not_exist exception;
	author_does_not_exist exception;
	null_value exception;
	title_length_exceeded exception;
	type_length_exceeded exception;
	notes_length_exceeded exception;
	isbn_length_exceeded exception;
	invalid_isbn exception;
	err_code varchar (20);
	err_msg varchar(200);
	
	BEGIN
		SELECT pub_id into v_pub_id FROM publishers where pub_name = p_pub_name;
		SELECT au_id  into v_au_id  FROM authors    where au_fname = p_au_fname and au_lname = p_au_lname;

		SELECT count(*) into count_publishers FROM publishers where pub_id = v_pub_id;
		SELECT count(*) into count_authors    FROM authors    where au_id  = v_au_id;
		IF count_authors = 0 THEN
	     	RAISE author_does_not_exist;
		ELSIF count_publishers = 0 THEN
	     	RAISE publisher_does_not_exist;
	    ELSIF p_title is null THEN
	     	raise null_value;
		ELSIF p_contract is null THEN
			raise null_value;
	    ELSIF (length (rtrim(p_title)) > 80) THEN
			raise title_length_exceeded;
	    ELSIF (length (rtrim(p_type)) > 12) THEN
			raise type_length_exceeded;
	    ELSIF (length (rtrim(p_notes)) > 200) THEN
			raise notes_length_exceeded;
	    ELSIF (length (rtrim(p_isbn)) > 17) THEN
	          raise isbn_length_exceeded;
		ELSIF is_valid_isbn (p_isbn) = 'N' THEN
	     	raise invalid_isbn;
		ELSE
			UPDATE titles 
			SET 
				title       = CASE WHEN LENGTH(TRIM(p_title)) > 0 then p_title
							  ELSE NULL
							  END, 
				type        = p_type, 
				price       = p_price, 
				advance     = p_advance, 
				total_sales = p_total_sales, 
				notes       = p_notes, 
				pubdate     = p_pubdate, 
				contract    = p_contract, 
				isbn        = p_isbn,
				au_id       = v_au_id, 
				pub_id      = v_pub_id
			WHERE title_id = p_title_id;
		END IF;
		
		commit;
		
	EXCEPTION
	
		WHEN author_does_not_exist THEN
			raise_application_error (-20006,'Specified author does not exist, enter a valid author.');
		WHEN publisher_does_not_exist THEN
			raise_application_error (-20006,'Specified publisher does not exist, enter a valid publisher.');
		WHEN title_length_exceeded THEN
			raise_application_error (-20007,'Length for title field cannot be greather than 80');
		WHEN type_length_exceeded THEN
			raise_application_error (-20008,'Length for type field cannot be greather than 12');
		WHEN notes_length_exceeded THEN
			raise_application_error (-20009,'Length for notes field cannot be greather than 200');
		WHEN isbn_length_exceeded THEN
			raise_application_error (-20010,'Length for isbn field cannot be greather than 80');
		WHEN invalid_isbn THEN
			raise_application_error (-20011,'Invalid isbn, check the isbn code');	
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20015, 'Cannot update title, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);	
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: update_author_picture
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE update_title_picture
(
	p_title_id    IN INTEGER,       --- PK Col

	p_picture     IN BLOB
)
AS
BEGIN
	UPDATE titles 
	SET 
		picture = p_picture
	WHERE title_id = p_title_id;
	
	commit;
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_title
------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE delete_title
(
	p_title_id  IN INTEGER
)
AS

	n_orders int;
	title_has_orders exception;
	err_code varchar (20);
	err_msg varchar(200);

	BEGIN
	
		SELECT count(*) into n_orders FROM orderdetails where title_id = p_title_id;
		IF n_orders > 0 THEN
			RAISE title_has_orders;
		ELSE
			DELETE FROM titles
			WHERE title_id = p_title_id;
		END IF;
				
		commit;
				
	EXCEPTION
	
		WHEN title_has_orders THEN
			raise_application_error (-20019,'You cannot delete title, associated orders with that title exist');
		
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20020, 'Cannot delete title, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
	
				 
END;
/



CREATE OR REPLACE PROCEDURE get_title_report
(
          p_title_id        IN INT,
          p_report_output  OUT VARCHAR2
)
AS
          v_cnt int;
BEGIN
          p_report_output := '';

          select calculate_sales_details(p_title_id) into p_report_output from dual;
END;
/




/* =======================================================================================
** =======================================================================================
** == PUBLISHERS 
** =======================================================================================
** =======================================================================================
*/

------------------------------------------------------------------------------------------
-- PROCEDURE: get_publishers
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_publishers
(
	p_pub_name     IN VARCHAR2,
	a_cursor      OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			pub_name as publisher_name,
			city,
			state,
			pub_id
		from publishers
		where upper(pub_name) like '%'||trim(upper(p_pub_name))||'%'
		order by pub_id;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_publishers_count
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_publishers_count
(
	p_count    OUT INT
)
AS
BEGIN
	select count(*) into p_count
	from publishers;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_publishers_lookup
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_publishers_lookup
(
	p_pub_name      IN VARCHAR2,
	a_cursor       OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			pub_name, 
			pub_id 
		from publishers
		where upper(pub_name) like '%'||trim(upper(p_pub_name))||'%'
		order by pub_name;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_publisher_by_id
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_publisher_by_id
(
	p_pub_id        IN INT,
	a_cursor       OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			pub_name, 
			pub_id 
		from publishers
		where pub_id = p_pub_id;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: insert_publisher
------------------------------------------------------------------------------------------
--			## In this proc we are going to handle exceptions in 2 different ways for testing and checking how the conversion 
--			## to target DBMS is done. In oracle raise_application_error raises an exception and sends a message to client, 
--			## (this means an implicit rollback), but put_line does not raise an error and thus not implicit rollback is done.
--			## Here instead of raise/raise_application_error we are handling the exception with put_line + explicit rollback 
--			## in 2 conditions:
--			##
--			##	· When length (city) > 20 instead for "raise" <exception_name> to jump into EXCEPTION block we call put_line + 
--			## 	  explicit rollback.
--			##	· When length (state) > 2 we jump into EXCEPTION block with raise and there we call put_line + explicit rollback
--			## 
--			## NOTE1:	In oracle you have to activate dbms_output to be able to capture buffered ouput (DBMS_OUTPUT.ENABLE() or
--			## 			"set serveroutput on").
--			## NOTE2: 	In ASE, after convertion, you can get buffered messages in 2 ways
--			##				· configuring "How to convert DBMS_OUTPUT calls" to "print_cmd" or
--			##				· configuring "How to convert DBMS_OUTPUT calls" to "buffered" and getting messages with 
--			##				  xp_dbmtk_get_buffered_output (check Exodus User's guide for further details)
--			##  

CREATE OR REPLACE PROCEDURE insert_publisher
(
	p_pub_name  IN VARCHAR2,
	p_city      IN VARCHAR2,
	p_state     IN CHAR
)
AS

	null_value exception;
	pub_name_length_exceeded exception;
	city_length_exceeded exception;
	state_length_exceeded exception;
	err_code varchar(20);
	err_msg varchar(200);
	
	BEGIN

		IF p_pub_name is null THEN
			raise null_value;
		ELSIF (length (rtrim(p_pub_name)) > 40) THEN
				raise pub_name_length_exceeded;
		ELSIF (length (rtrim(p_city)) > 20) THEN
--			put_line + rollback instead of raise
--			raise city_length_exceeded;
				dbms_output.put_line ('Error: Length for city field cannot be greather than 20');
				rollback;
		ELSIF (length (rtrim(p_state)) > 2) THEN
				raise state_length_exceeded;
		ELSE
			INSERT INTO publishers 
			VALUES (
				publishers_seq.NEXTVAL, 
				CASE WHEN LENGTH(TRIM(p_pub_name)) > 0 then p_pub_name
                     ELSE NULL
                END, 
				p_city, 
				p_state
			);
		END IF;
		
		commit;
		
	EXCEPTION
	
		WHEN null_value THEN
			raise_application_error (-20003,'Publisher name cannot be null');
		WHEN pub_name_length_exceeded THEN
			raise_application_error (-20004,'Length for publisher name cannot be greather than 40');
		WHEN city_length_exceeded THEN
			raise_application_error (-20004,'Length for city field cannot be greather than 20');	
		WHEN state_length_exceeded THEN
--			## put_line + rollback instead of raise_application_error
--			## raise_application_error (-20004,'Length for state field cannot be greather than 2');
			dbms_output.put_line ('Error: Length for state field cannot be greather than 2');
			rollback;
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20005, 'Cannot insert publisher, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
END;
/
------------------------------------------------------------------------------------------
-- PROCEDURE: update_publisher
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE update_publisher
(
	p_pub_id       IN INTEGER,       --- PK Col

	p_pub_name     IN VARCHAR2,
	p_city         IN VARCHAR2,
	p_state        IN CHAR
)
AS

	null_value exception;
	pub_name_length_exceeded exception;
	city_length_exceeded exception;
	state_length_exceeded exception;
	err_code varchar(20);
	err_msg varchar(200);
	
	BEGIN

		IF p_pub_name is null THEN
			raise null_value;
		ELSIF (length (rtrim(p_pub_name)) > 40) THEN
				raise pub_name_length_exceeded;
		ELSIF (length (rtrim(p_city)) > 20) THEN
				raise city_length_exceeded;
		ELSIF (length (rtrim(p_state)) > 2) THEN
				raise state_length_exceeded;
		ELSE
			UPDATE publishers 
			SET 
				pub_name = CASE WHEN LENGTH(TRIM(p_pub_name)) > 0 then p_pub_name
						   ELSE NULL
						   END,
				city     = p_city,
				state    = p_state
			WHERE pub_id = p_pub_id;
		END IF;
		
		commit;
		
	EXCEPTION
	
		WHEN null_value THEN
			raise_application_error (-20003,'Publisher name cannot be null');
		WHEN pub_name_length_exceeded THEN
			raise_application_error (-20004,'Length for publisher name cannot be greather than 40');
		WHEN city_length_exceeded THEN
			raise_application_error (-20004,'Length for city field cannot be greather than 20');	
		WHEN state_length_exceeded THEN
			raise_application_error (-20004,'Length for state field cannot be greather than 2');
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20005, 'Cannot update publisher, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_publisher
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE delete_publisher
(
	p_pub_id  IN INTEGER
)
AS

	n_titles int;
	publisher_has_titles exception;
	err_code varchar (20);
	err_msg varchar(200);

	BEGIN
	
		   SELECT count(*) into n_titles FROM titles where pub_id = p_pub_id;
		   IF n_titles > 0 THEN
				RAISE publisher_has_titles;
		   ELSE
				DELETE FROM publishers
				WHERE pub_id = p_pub_id;
		   END IF;
               
		   commit;   
		   
	EXCEPTION

		   WHEN publisher_has_titles THEN
				raise_application_error (-20001,'You cannot delete publisher, associated titles exist');
		
		   WHEN OTHERS THEN
				err_code := to_char (SQLCODE);
				err_msg := SQLERRM;
				raise_application_error (-20002, 'Cannot delete publisher, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
			
	
END;
/


/* =======================================================================================
** =======================================================================================
** == STORES 
** =======================================================================================
** =======================================================================================
*/

------------------------------------------------------------------------------------------
-- PROCEDURE: get_stores
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_stores
(
	p_store_name    IN VARCHAR2,
	a_cursor       OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			store_name,
			address,
			city,
			state,
			country,
			postalcode,
			store_id
		from stores
		where upper(store_name) like '%'||trim(upper(p_store_name))||'%'
		order by store_name;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_stores_count
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_stores_count
(
	p_count    OUT INT
)
AS
BEGIN
	select count(*) into p_count
	from stores;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: insert_store
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE insert_store
(
	p_store_name   IN VARCHAR2,
	p_address      IN VARCHAR2,
	p_city         IN VARCHAR2,
	p_state        IN CHAR,
	p_country      IN VARCHAR2,
	p_postalcode   IN CHAR
)
AS

	null_value exception;
	store_name_length_exceeded exception;
	address_length_exceeded exception;
	city_length_exceeded exception;
	state_length_exceeded exception;
	country_length_exceeded exception;
	postalcode_length_exceeded exception;
	invalid_postalcode exception;
	err_code varchar(20);
	err_msg varchar(200);

	BEGIN
		IF (p_store_name is null) or 
		   (p_city is null) or
		   (p_state is null) THEN
			raise null_value;
		ELSIF (length (rtrim(p_store_name)) > 40) THEN
				raise store_name_length_exceeded;
		ELSIF (length (rtrim(p_address)) > 20) THEN
				raise address_length_exceeded;
		ELSIF (length (rtrim(p_city)) > 20) THEN
				raise city_length_exceeded;
		ELSIF (length (rtrim(p_state)) > 2) THEN
				raise state_length_exceeded;
		ELSIF (length (rtrim(p_country)) > 12) THEN
				raise country_length_exceeded;
		ELSIF (length (rtrim(p_postalcode)) > 10) THEN
				raise postalcode_length_exceeded;
		ELSIF (is_valid_postalcode (p_postalcode) = 'N') THEN
			raise invalid_postalcode;
		ELSE
			INSERT INTO stores(
				store_id,
				store_name,
				address,
				city,
				state,
				country,
				postalcode)
			VALUES (
				stores_seq.NEXTVAL, 
				CASE WHEN LENGTH(TRIM(p_store_name)) > 0 then p_store_name
					 ELSE NULL
				END,
				p_address,
				CASE WHEN LENGTH(TRIM(p_city)) > 0 then p_city
					 ELSE NULL
				END,
				CASE WHEN LENGTH(TRIM(p_state)) > 0 then p_state
					 ELSE NULL
				END,
				p_country,
				p_postalcode);
		END IF;
		
		commit;
		
	EXCEPTION
	
		WHEN null_value THEN
			raise_application_error (-20003,'Store name, city and state cannot be null');
		WHEN store_name_length_exceeded THEN
			raise_application_error (-20004,'Length for store name cannot be greather than 40');
		WHEN address_length_exceeded THEN
			raise_application_error (-20004,'Length for address cannot be greather than 42');	
		WHEN city_length_exceeded THEN
			raise_application_error (-20004,'Length for city field cannot be greather than 20');	
		WHEN state_length_exceeded THEN
			raise_application_error (-20004,'Length for state field cannot be greather than 2');
		WHEN country_length_exceeded THEN
			raise_application_error (-20004,'Length for country field cannot be greather than 12');
		WHEN postalcode_length_exceeded THEN
			raise_application_error (-20004,'Length for postalcode field cannot be greather than 10');		
		WHEN invalid_postalcode THEN
			raise_application_error (-20005,'Invalid postal code');
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20005, 'Cannot insert publisher, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: update_store
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE update_store
(
	p_store_id     IN INTEGER,   --- PK Col
	p_store_name   IN VARCHAR2,
	p_address      IN VARCHAR2,
	p_city         IN VARCHAR2,
	p_state        IN CHAR,
	p_country      IN VARCHAR2,
	p_postalcode   IN CHAR
)
AS

	null_value exception;
	store_name_length_exceeded exception;
	address_length_exceeded exception;
	city_length_exceeded exception;
	state_length_exceeded exception;
	country_length_exceeded exception;
	postalcode_length_exceeded exception;
	invalid_postalcode exception;
	err_code varchar(20);
	err_msg varchar(200);
	
	BEGIN
	
		IF (p_store_name is null) or 
		   (p_city is null) or
		   (p_state is null) THEN
			raise null_value;
		ELSIF (length (rtrim(p_store_name)) > 40) THEN
				raise store_name_length_exceeded;
		ELSIF (length (rtrim(p_address)) > 20) THEN
				raise address_length_exceeded;
		ELSIF (length (rtrim(p_city)) > 20) THEN
				raise city_length_exceeded;
		ELSIF (length (rtrim(p_state)) > 2) THEN
				raise state_length_exceeded;
		ELSIF (length (rtrim(p_country)) > 12) THEN
				raise country_length_exceeded;
		ELSIF (length (rtrim(p_postalcode)) > 10) THEN
				raise postalcode_length_exceeded;
		ELSIF (is_valid_postalcode (p_postalcode) = 'N') THEN
			raise invalid_postalcode;
		ELSE
			UPDATE stores 
			SET 
				store_name = CASE WHEN LENGTH(TRIM(p_store_name)) > 0 then p_store_name
								  ELSE NULL
							 END,
				address    = p_address,
				city       = CASE WHEN LENGTH(TRIM(p_city)) > 0 then p_city
								  ELSE NULL
							 END,
				state      = CASE WHEN LENGTH(TRIM(p_state)) > 0 then p_state
								  ELSE NULL
							 END,
				country    = p_country,
				postalcode = p_postalcode
			WHERE store_id = p_store_id;
		END IF;
		
		commit;
		
	EXCEPTION
	
		WHEN null_value THEN
			raise_application_error (-20003,'Store name, city and state cannot be null');
		WHEN store_name_length_exceeded THEN
			raise_application_error (-20004,'Length for store name cannot be greather than 40');
		WHEN address_length_exceeded THEN
			raise_application_error (-20004,'Length for address cannot be greather than 42');	
		WHEN city_length_exceeded THEN
			raise_application_error (-20004,'Length for city field cannot be greather than 20');	
		WHEN state_length_exceeded THEN
			raise_application_error (-20004,'Length for state field cannot be greather than 2');
		WHEN country_length_exceeded THEN
			raise_application_error (-20004,'Length for country field cannot be greather than 12');
		WHEN postalcode_length_exceeded THEN
			raise_application_error (-20004,'Length for postalcode field cannot be greather than 10');		
		WHEN invalid_postalcode THEN
			raise_application_error (-20005,'Invalid postal code');
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20005, 'Cannot insert publisher, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_store
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE delete_store
(
	p_store_id  IN INTEGER
)
AS
BEGIN
	DELETE FROM stores
	WHERE store_id = p_store_id;
	commit;
END;
/



/* =======================================================================================
** =======================================================================================
** == ORDERS 
** =======================================================================================
** =======================================================================================
*/

------------------------------------------------------------------------------------------
-- PROCEDURE: get_orders
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_orders
(
	p_store_name    IN VARCHAR2,
	a_cursor       OUT SYS_REFCURSOR
)
AS
BEGIN
	OPEN a_cursor FOR 
		select 
			order_id, 
			store_name, 
			order_date
		from orders
		where upper(store_name) like '%'||trim(upper(p_store_name))||'%'
		order by order_id;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_orders_count
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_orders_count
(
	p_count    OUT INT
)
AS
BEGIN
	select count(*) into p_count
	from orders;
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: get_order_details
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE get_order_details
(
	order_id_list IN  VARCHAR2           default null, -- CSV list of order_id (integers-as-strings): 1,2,3,4
	a_cursor      OUT SYS_REFCURSOR
)
AS
BEGIN
	-- Get titles by: a list of AUTHORS: id1, id2, id3
	IF ( order_id_list is not null )
	THEN
		-- Get titles by: a list of AUTHORS: id1, id2, id3
		OPEN a_cursor FOR 
			'SELECT'
			||'    od.order_id, '
			||'    od.quantity, '
			||'    od.discount, '
			||'    t.title, '
			||'    t.isbn, '
			||'    od.title_id '
			||' from orderdetails od '
			||' left outer join titles t on od.title_id = t.title_id '
			||' where od.order_id in ('||order_id_list||') '
			||' order by od.order_id, od.quantity desc ';
	ELSE
		OPEN a_cursor FOR 
			SELECT
			    od.order_id, 
			    od.quantity, 
			    od.discount, 
			    t.title, 
			    t.isbn, 
			    od.title_id 
			from orderdetails od 
			left outer join titles t on od.title_id = t.title_id 
			where 1 = 2;
	END IF;
END;
/ 






/* =======================================================================================
** =======================================================================================
** == ORDERDETAILS
** =======================================================================================
** =======================================================================================
*/

------------------------------------------------------------------------------------------
-- 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 upper(store_name) like '%'||trim (upper(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 upper(title) like '%'||trim (upper(p_name))||'%'
		  and isbn  like '%'||trim (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

	store_name_length_exceeded exception;
	null_value exception;
	store_does_not_exist exception;
	count_stores int;
	err_code varchar(20);
	err_msg varchar(200);
	
BEGIN

	SELECT count(*) into count_stores FROM stores where store_name = p_store_name;

	IF (p_store_name is null) THEN
		raise null_value;
	ELSIF (length (rtrim(p_store_name)) > 40) THEN
		raise store_name_length_exceeded;
	ELSIF count_stores = 0 THEN
		raise store_does_not_exist;
	ELSE
		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 IF;
	
	commit;
	
	EXCEPTION
		WHEN null_value THEN
			raise_application_error (-20003,'Store name cannot be null');
		WHEN store_name_length_exceeded THEN
			raise_application_error (-20004,'Length for store name cannot be greather than 40');
		WHEN store_does_not_exist THEN
			raise_application_error (-20007,'Specified store does not exist, enter a valid store.');
		WHEN OTHERS THEN
			err_code := to_char (SQLCODE);
			err_msg := SQLERRM;
			raise_application_error (-20005, 'Cannot create store, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
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;
	count_orders int;
	count_titles int;
	order_does_not_exist exception;
	title_does_not_exist exception;
	invalid_quantity exception;
	invalid_discount exception;
	err_code varchar (20);
	err_msg varchar(200);
	
BEGIN

	SELECT count(*) into count_orders FROM orders where order_id = p_order_id;
	SELECT count(*) into count_titles FROM titles where isbn = p_isbn;
	IF count_orders = 0 THEN
		RAISE order_does_not_exist;
	ELSIF count_titles = 0 THEN
		RAISE title_does_not_exist;
	ELSIF p_quantity < 1 THEN
		RAISE invalid_quantity;
	ELSIF p_discount < 0 or p_discount > 100 THEN
		RAISE invalid_discount;
	ELSE
		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 IF;
	
	commit;
	
	EXCEPTION
	
		WHEN order_does_not_exist THEN
			raise_application_error (-20006,'Specified order does not exist, enter a valid order.');
		WHEN title_does_not_exist THEN
			raise_application_error (-20007,'Specified title does not exist, check title name and isbn.');
		WHEN invalid_quantity THEN
			raise_application_error (-20011,'Invalid quantity, quantity should be a positive number');
		WHEN invalid_discount THEN
			raise_application_error (-20011,'Invalid discount, discount should be between 1 and 100');
		
END;
/ 

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_order
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE delete_order
(
	p_order_id        IN INTEGER
)
AS

	err_code varchar (20);
	err_msg varchar(200);
	
BEGIN
	delete from orderdetails where order_id = p_order_id;
	delete from orders       where order_id = p_order_id;
	
	EXCEPTION
		WHEN OTHERS THEN
			 err_code := to_char (SQLCODE);
			 err_msg := SQLERRM;
			 raise_application_error (-20002, 'Cannot delete author, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
END;
/

------------------------------------------------------------------------------------------
-- PROCEDURE: delete_order_details
------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE delete_order_details
(
	p_order_id        IN INTEGER
)
AS

	err_code varchar (20);
	err_msg varchar(200);
	
BEGIN

	delete from orderdetails where order_id = p_order_id;

	EXCEPTION
		WHEN OTHERS THEN
			 err_code := to_char (SQLCODE);
			 err_msg := SQLERRM;
			 raise_application_error (-20002, 'Cannot delete order detail, ERR_CODE: ' || err_code || ', ERR_MSG: ' || err_msg);
	
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;
/ 





select '-END-OF-SQL-STATEMENTS-' as TIME_TO_EXIT from dual
/
EXIT

/* =======================================================================================
** =======================================================================================
** == SOME TEST CODES
** =======================================================================================
** =======================================================================================
*/

--SHOW ERRORS
select OWNER, NAME, TYPE, SEQUENCE, LINE, POSITION, TEXT, ATTRIBUTE,MESSAGE_NUMBER
from ALL_ERRORS where OWNER = USER order by SEQUENCE
go plain
select * from USER_ERRORS
select * from DBA_ERRORS
select * from ALL_ERRORS
go plain


--- TEST THE PROCS (note: the bellow command: '\call' is a sqlw command to use JDBC CallableStatement)
\call get_titles(?, ?, ?) :(string='1,2,3', string=NULL,  ora_rs)
\call get_titles(?, ?, ?) :(string=NULL,    string='1,2', ora_rs)
\call get_titles(?, ?, ?) :(string=NULL,    string=NULL,  ora_rs)

\call get_authors(?)    :(ora_rs)
\call get_publishers(?) :(ora_rs)
\call get_stores(?)     :(ora_rs)
\call get_orders(?)     :(ora_rs)

\call get_author_picture(?,?)    :(int=100001, ora_rs)

select * from authors    --where 1=2;
select * from titles     --where 1=2;
select * from publishers --where 1=2;
select * from stores     --where 1=2;
select * from orders     --where 1=2;

select user from dual

begin
	if is_valid_isbn ('978849873668') then 
		dbms_output.put_line ('Yes');
	else 
		dbms_output.put_line ('No');
	end if;
end;

-- is_valid_isbn function

declare 
out_put varchar (50);
begin
               out_put := is_valid_isbn ('978-84-98-773-688');
               dbms_output.put_line (out_put);
end;

/* =======================================================================================
** == sqlplus
** =======================================================================================
** sqlplus 'system/jupiter@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=nlhtblob001.htb.sap.corp)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora11dev)))'
** sqlplus 'APPDEMO/appdemo@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=nlhtblob001.htb.sap.corp)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora11dev)))'
** sqlplus 'APPDEMO/appdemo@ORA11DEV' @/path/filename.sql
** sqlplus 'APPDEMO/appdemo@ORA11DEV' @/tmp/gorans/PUBS2_ORA_FINAL_V2.gorans.sql
*/
