-- creating a simple orders table
create or replace table orders(
    order_id number,
    customer_id_fk number,
    item_id_fk number,
    retail_price number(10,2),
    purchase_price number(10,2),
    sold_quantity number(3),
    country_code varchar(2)
);

-- inserting handful records
insert into orders 
(order_id, customer_id_fk, item_id_fk,retail_price,purchase_price, sold_quantity,country_code)
values
(1,1,1,99.2,89.6,2,'US'),
(2,8,2,17.1,11,10,'IN'),
(3,5,1,827,900.99,5,'JP'),
(4,10,4,200,172,7,'DE');

-- lets check the records
select * from orders;

create or replace function 
calculate_profit(retail_price number, purchase_price number, sold_quantity number)
returns number(10,2)
comment = 'this is simple profit calculator'
as 
$$
 select ((retail_price - purchase_price) * sold_quantity))
$$;

select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity, 
    calculate_profit(retail_price,purchase_price, sold_quantity) as profit_udf 
from orders ;

select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity,
    ((retail_price - purchase_price) * sold_quantity) as profit
from orders ;

-- create bit more complex udf
create or replace function country_name(country_code string)
returns string
as 
$$
 select country_code || '-' ||case
        when country_code='US' then 'USA'
        when country_code='IN' then 'India'
        when country_code='JP' then 'Japan'
        when country_code='DE' then 'Germany'
        else 'Unknown'
    end
$$;

select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity,
    calculate_profit(retail_price,purchase_price, sold_quantity) as profit_udf ,
    country_name(country_code) as country_udf
 from orders ;
 
select 
    item_id_fk,
    retail_price,
    purchase_price, 
    sold_quantity,
    ((retail_price - purchase_price) * sold_quantity) as profit ,
    country_code || '-' ||case
        when country_code='US' then 'USA'
        when country_code='IN' then 'India'
        when country_code='JP' then 'Japan'
        when country_code='DE' then 'Germany'
        else 'Unknown'
        end as country
 from orders ;

-- now lets understand show and desc function features.
show functions;

-- filter by using object name
show functions like 'COUNTRY%';
show functions like'%PROFIT';

-- how to describe a function using desc function sql keywords
desc function country_name(string);

-- how will you filter builtin vs user defined
-- lets use result scan table function and use the result as table data
show functions;
select * from table(result_scan( LAST_QUERY_ID()));
select * 
    from table(result_scan(query_id => '01a4xgt6-0000-9e5d-0002-5532000b643e')) t 
    where "is_builtin" = 'N';

select * from table(result_scan('01a4xgt6-0000-9e5d-0002-5532000b643e')) t 
    where 
    "name" in ('CALCULATE_PROFIT','COUNTRY_NAME') ;


select 
        "language",
        "schema_name" , 
        "is_builtin", 
        count(1) 
    from table(result_scan('01a4d5bf-0000-9e5d-0002-5532000b643e')) t 
    group by "language","schema_name" , "is_builtin";