-- drop-ы
alter table rotax_team.price_modifiers
drop column tips_type;

alter table rotax_team.price_modifiers
drop column tips_value;

alter table rotax_team.price_modifiers
drop column discount_coef;

alter table rotax_team.price_modifiers
drop column coupon_value;

alter table rotax_team.price_modifiers
drop column coupon_applied;

alter table rotax_team.orders
drop column total_paid;

alter table rotax_team.waiting_statuses
rename column riding_time to transporting_time;

alter table rotax_team.waiting_statuses
rename column riding_status to transporting_status;

-- price_modifiers.tips_value
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        dj ->> '_id' as id,
        dj -> 'order' -> 'creditcard' -> 'tips' ->> 'type' as tips_type,
        (dj -> 'order' -> 'creditcard' -> 'tips' ->> 'value')::decimal as tips_value,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost
    FROM orders
)
SELECT
       id,
       case when tips_type='percent' then cost * tips_value / 100
            when tips_type='flat' then tips_value
            else null
       end as tips_value
FROM parsed_orders
where tips_value is not null;

-- price_modifiers.discount_value, price_modifiers.coupon_value
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        dj ->> '_id' as id,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        dj -> 'order' ->> 'taxi_status' as taxi_status,
        (dj -> 'order' -> 'discount' ->> 'price')::decimal as orig_price,
        (dj -> 'order' -> 'discount' ->> 'value')::decimal as discount_coef,
        (dj -> 'order' -> 'coupon' ->> 'value')::decimal as coupon_value,
        (dj -> 'order' -> 'coupon' ->> 'was_used')::boolean as coupon_used
    FROM orders
),
revenues as (
    select
           id,
           taxi_status,
           coalesce(orig_price, cost) as orig_price,
           coalesce(discount_coef, 0) as discount_coef,
           coalesce(coupon_value, 0) as coupon_value,
           coalesce(coupon_used, FALSE) as coupon_used
    from parsed_orders
)
SELECT
    id,
    orig_price * discount_coef as discount_value,
    case when coupon_used=True and coupon_value >= orig_price then orig_price
         when coupon_used=True and coupon_value < orig_price then coupon_value
         else 0
    end as coupon_value
FROM
    revenues
WHERE
     taxi_status = 'complete' and (discount_coef != 0 or coupon_used = True);


-- orders.original_price
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 100000
),
parsed_orders AS (
    SELECT
        dj ->> '_id' as id,
        (dj -> 'order' ->> 'cost')::DECIMAL AS cost,
        (dj -> 'order' -> 'discount' ->> 'price')::decimal as orig_price
    FROM orders
)
SELECT
    id,
    coalesce(orig_price, cost) as original_price
FROM
    parsed_orders;



-- waiting_statuses.waiting_status, waiting_statuses.waiting_time
WITH orders AS (
    SELECT
        doc::json AS dj
    FROM common.order_proc_rotax
    LIMIT 10000
),
parsed_orders AS (
    SELECT
        dj ->> '_id' as id,
        (dj ->> 'created')::TIMESTAMP AS created_dttm,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> 0 ->> 'c')::timestamp as cr_time,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -3 ->> 'c')::timestamp as waiting_time1,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -3 ->> 't') as waiting_status1,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -2 ->> 'c')::timestamp as waiting_time2,
        (dj -> 'order_info' -> 'statistics' -> 'status_updates' -> -2 ->> 't') as waiting_status2,
        dj -> 'order' ->> 'city' as city
    FROM orders
)
select
    id,
    case when waiting_status1 = 'waiting' then waiting_status1
         when waiting_status2 = 'waiting' then waiting_status2
         else null
    end as waiting_status,
    case when waiting_status1 = 'waiting' then waiting_time1
         when waiting_status2 = 'waiting' then waiting_time2
         else null
    end as waiting_time
from parsed_orders;