-- CS 328 - Homework 8
-- Drew Hengehold
-- Last Modified: 8 April 2025

start /home/djh133/328Database/design.sql;
start /home/djh133/328Database/populate.sql;
set serveroutput on
spool second-db-out.txt

prompt File made by Drew Hengehold;
/*
 Function: cust_exist
 Parameters: lastname
 Purpose: Expects a customers last name and outputs the customer ID if they exist, otherwise outputs -1;
*/
create or replace function cust_exist(lastname varchar2) return customer.cust_num%TYPE as
    cust_num customer.cust_num%TYPE;
begin
    select cust_num
    into cust_num
    from customer
    where cust_lname = lastname;

    return cust_num;
exception
    when no_data_found  then
        return -1;
end;
/
show errors

/*
 Function: item_exist
 Parameters: name of item, order_quantity
 Purpose: Expects a item name and order quantity and outputs the item ID if the item exists
        and the item quantity ordered is within the bounds of the stock order.
        Otherwise outputs -1.
*/
create or replace function item_exist(name_item varchar, order_quant number) return sale_item.item_id%TYPE as
    item_number sale_item.item_id%TYPE;
begin
    select item_id
    into item_number
    from sale_item
    where name_item = sale_item.item_name and stock_quant >= order_quant;

    return item_number;
exception
    when no_data_found then
        return -1;
end;
/
show errors

/*
 Function: empl_exists
 Parameters: employee_name
 Purpose: Expects an employee name and outputs the employee ID if that employee exists
        otherwise outputs -1.
*/
create or replace function empl_exists(employee_name varchar2) return employee.empl_id%TYPE as
    employee_id employee.empl_id%TYPE;
begin
    select empl_id
    into employee_id
    from employee
    where employee_name = employee.empl_lname;

    return employee_id;
exception
    when no_data_found then
        return -1;
end;
/
show errors


/*
 Procedure: item_sold
 Parameters: customer last name, item name, quantity, employee_lname
 Purpose: The procedure check if the customer and employee exist, and the item exists and has the quantity needed to sell, 
    it then creates a new sale and decriments the quanitity it sold. 
*/

create or replace procedure item_sold(customer_lname varchar2, nameofitem varchar2, quantity number, employee_lname varchar2) as
    customerNUM customer.cust_num%TYPE;
    item_id sale_item.item_id%TYPE;
    empl_id employee.empl_id%TYPE;

begin

--Check that customer exists, return customer id

    customerNUM := cust_exist(customer_lname);

--Check that item exists in sale item and has the proper quant, return the item ID

    item_id := item_exist(nameofitem, quantity);

--Check that employee exists, return employee id

    empl_id := empl_exists(employee_lname);

    dbms_output.put_line('Customer id is: ' || customerNUM);
    dbms_output.put_line('Item id is: ' || item_id);
    dbms_output.put_line('Employee id is: ' || empl_id);
--IF EVERYTHING EXISTS DO TRANSACTION
    if(customerNUM != -1 and empl_id != -1 and item_id != -1) then
        -- Create sale item with employee id, cust id, incriment sale_num, quantity is sale_amt

        INSERT INTO sale(sale_num, sale_amt, cust_num, empl_id)
        VALUES (
        sale_num.nextval,
        quantity,
        customerNUM,
        empl_id);

        --Subtract the quantity sold from sale_item
        UPDATE sale_item
        SET stock_quant = stock_quant - quantity
        where sale_item.item_name = nameofitem;

        dbms_output.put_line('Transaction Complete');
    else
        dbms_output.put_line('Transaction Failure');
    end if;
    

end;
/
show errors

/*
    Testing procedure: item_sold
*/

prompt ========Testing item_sold========
prompt
prompt +-+-+-+-+-+-+-+-+-+
prompt (Test 1 of item_sold) Should output: employee id = 000001, item id = 001, and customer id = 200001
prompt Additionally, the sale_item table should have 2 less black teas, and the sale table should have one more entry
prompt +-+-+-+-+-+-+-+-+-+

exec item_sold('coulsen','black', 2,'higgins')

select *
from sale;

select *
from sale_item;

prompt (Test 2 of item_sold) Should output: employee id = -1, item id = 001, and customer id = 200001
prompt The output should be transaction failed, and no updates should be made to the table so black tea should
prompt be 28, and the last sale number should be 11
prompt +-+-+-+-+-+-+-+-+-+

exec item_sold('coulsen','black', 2,'jack')

select *
from sale;

select *
from sale_item;
spool off;