-- drops
DROP TABLE customers;
DROP TABLE products;
DROP TABLE orders;

-----------------------------
-- create functions

CREATE OR REPLACE FUNCTION get_age_group(birth_date DATE)
RETURN VARCHAR2 IS
    age NUMBER;
BEGIN
    age := EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birth_date);
    IF age <= 30 THEN
        RETURN 'under 30';
    ELSIF age <= 40 THEN
        RETURN '30-40';
    ELSIF age <= 50 THEN
        RETURN '40-50';
    ELSIF age <= 60 THEN
        RETURN '50-60';
    ELSIF age <= 70 THEN
        RETURN '60-70';
    ELSE
        RETURN 'above 70';
    END IF;
END;
/

CREATE OR REPLACE FUNCTION get_income_level(init_income_level VARCHAR2)
RETURN VARCHAR2 IS
    income_str VARCHAR2(50);
    income NUMBER;
BEGIN

    income_str := REGEXP_REPLACE(init_income_level, '[A-Z]: ', '');
    income_str := REGEXP_REPLACE(income_str, '([0-9]+,[0-9]+ - |[A-z]+ | [A-z]+)', '');
    income_str := REGEXP_REPLACE(income_str, ',', '');
    income := TO_NUMBER(income_str);

    IF income < 110000 THEN
        RETURN 'low';
    ELSIF income < 190000 THEN
        RETURN 'medium';
    ELSE
        RETURN 'high';
    END IF;

END;
/

CREATE OR REPLACE FUNCTION fix_status(marital_status VARCHAR2)
RETURN VARCHAR2 IS
BEGIN

    IF      marital_status = 'Married'
            OR marital_status = 'Mabsent'
            OR marital_status = 'married'
            OR marital_status = 'Mar-AF'
        THEN
        RETURN 'married';
    ELSIF marital_status IS NULL THEN
        RETURN 'unknown';
    ELSE
        RETURN 'single';
    END IF;

END;
/

-----------------------------
-- create tables

CREATE TABLE customers AS
SELECT id customer_id, gender,
        get_age_group(birth_date) agegroup, fix_status(marital_status) marital_status,
        get_income_level(income_level) income_level
FROM xsales.customers;

CREATE TABLE products AS
SELECT p.identifier product_id, p.name productname,
        c.name categoryname, p.list_price
FROM xsales.products p JOIN xsales.categories c ON p.subcategory_reference = c.id;

CREATE TABLE orders AS
SELECT o.id order_id, oi.product_id, o.customer_id,
        o.order_finished - oi.order_date days_to_process,
        oi.amount price, oi.COST, o.channel
FROM xsales.orders o JOIN xsales.order_items oi ON o.id = oi.order_id;

COMMIT;

-----------------------------

/*

-- these tables are just for the tuning analysis


DROP TABLE orders_sorted;
CREATE TABLE orders_sorted AS
SELECT o.id order_id, oi.product_id, o.customer_id,
        o.order_finished - oi.order_date days_to_process,
        oi.amount price, oi.cost, o.channel
FROM xsales.orders o JOIN xsales.order_items oi ON o.id = oi.order_id
ORDER BY order_id;

DROP TABLE orders_uns;
CREATE TABLE orders_uns AS
SELECT o.id order_id, oi.product_id, o.customer_id,
        o.order_finished - oi.order_date days_to_process,
        oi.amount price, oi.cost, o.channel
FROM xsales.orders o JOIN xsales.order_items oi ON o.id = oi.order_id;

*/