CREATE OR REPLACE FUNCTION public.update_stats()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare
    recon reconciliations;
    device_id uuid;
    conductor_id uuid;
    company_id uuid;
    driver_id uuid;
    route_id uuid;

    amount int;
    formatted_month text;
    tx_year text;
    tx_month text;
    tx_day text;
begin
    tx_year := '20' || substr(new.datetime_transmission, 1, 2);
    tx_month := substr(new.datetime_transmission, 3, 2);
    tx_day := substr(new.datetime_transmission, 5, 2);

    device_id := (select id from devices where terminal_id = new.terminal_id);

    amount = cast(trim(both 'C' from new.transaction_amount) as int);

    if new.type_id = '0521' then

        company_id := (select id from companies where merchant_id = new.merchant_id);
        conductor_id := cast(new.additional_data::json->>'conductor' as uuid);
        route_id := cast(new.additional_data::json->>'route' as uuid);
        driver_id := cast(new.additional_data::json->>'driver' as uuid);

        insert into transactions_stats
        (
            entity,
            entity_id,
            day,
            month,
            year,
            total,
            total_cash,
            total_cashless,
            count,
            count_cash,
            count_cashless,
            ticket_amount
        )
        values
        (
            'device',
            device_id,
            tx_day,
            tx_month,
            tx_year,
            amount,
            amount,
            0,
            amount / 2000,
            amount / 2000,
            0,
            2000
        );

        insert into transactions_stats
        (
            entity,
            entity_id,
            day,
            month,
            year,
            total,
            total_cash,
            total_cashless,
            count,
            count_cash,
            count_cashless,
            ticket_amount
        )
        values
        (
            'company',
            company_id,
            tx_day,
            tx_month,
            tx_year,
            amount,
            amount,
            0,
            amount / 2000,
            amount / 2000,
            0,
            2000
        );

        insert into transactions_stats
        (
            entity,
            entity_id,
            day,
            month,
            year,
            total,
            total_cash,
            total_cashless,
            count,
            count_cash,
            count_cashless,
            ticket_amount
        )
        values
        (
            'conductor',
            conductor_id,
            tx_day,
            tx_month,
            tx_year,
            amount,
            amount,
            0,
            amount / 2000,
            amount / 2000,
            0,
            2000
        );

        insert into transactions_stats
        (
            entity,
            entity_id,
            day,
            month,
            year,
            total,
            total_cash,
            total_cashless,
            count,
            count_cash,
            count_cashless,
            ticket_amount
        )
        values
        (
            'driver',
            driver_id,
            tx_day,
            tx_month,
            tx_year,
            amount,
            amount,
            0,
            amount / 2000,
            amount / 2000,
            0,
            2000
        );

        insert into transactions_stats
        (
            entity,
            entity_id,
            day,
            month,
            year,
            total,
            total_cash,
            total_cashless,
            count,
            count_cash,
            count_cashless,
            ticket_amount
        )
        values
        (
            'route',
            route_id,
            tx_day,
            tx_month,
            tx_year,
            amount,
            amount,
            0,
            amount / 2000,
            amount / 2000,
            0,
            2000
        );
    end if;
    if new.type_id = '0530' then

        if char_length(tx_day) = 1 then
            tx_day := '0' || tx_day;
        end if;

        if char_length(tx_month) = 1 then
            tx_month := '0' || tx_month;
        end if;

        if char_length(tx_year) = 2 then
            tx_year := '20' || tx_year;
        end if;

        conductor_id := (
            select cast(additional_data::json->>'conductor' as uuid) as conductor_id
            from reconciliations
            where type_id = '0521'
            and substring(cast(created_at::timestamp::date as text) from 1 for 4) = tx_year
            and substring(cast(created_at::timestamp::date as text) from 6 for 2) = tx_month
            and substring(cast(created_at::timestamp::date as text) from 9 for 2) = tx_day
            limit 1
        );

        if exists (select 1
            from transactions_stats
            where entity_id = conductor_id
            and entity = 'conductor'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0
        ) then
            update transactions_stats
            set
                total_cashless = amount,
                count_cashless = (amount / 2000),
                count = (amount / 2000) + count,
                total = amount + total,
                is_closed = 1
            where entity_id = conductor_id
            and entity = 'conductor'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0;
        end if;

        driver_id := (
            select cast(additional_data::json->>'driver' as uuid) as driver_id
            from reconciliations
            where type_id = '0521'
            and substring(cast(created_at::timestamp::date as text) from 1 for 4) = tx_year
            and substring(cast(created_at::timestamp::date as text) from 6 for 2) = tx_month
            and substring(cast(created_at::timestamp::date as text) from 9 for 2) = tx_day
            limit 1
        );

        if exists (select 1
            from transactions_stats
            where entity_id = driver_id
            and entity = 'driver'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0
        ) then
            update transactions_stats
            set
                total_cashless = amount,
                count_cashless = (amount / 2000),
                count = (amount / 2000) + count,
                total = amount + total,
                is_closed = 1
            where entity_id = driver_id
            and entity = 'driver'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0;
        end if;

        route_id := (
            select cast(additional_data::json->>'route' as uuid) as route_id
            from reconciliations
            where type_id = '0521'
            and substring(cast(created_at::timestamp::date as text) from 1 for 4) = tx_year
            and substring(cast(created_at::timestamp::date as text) from 6 for 2) = tx_month
            and substring(cast(created_at::timestamp::date as text) from 9 for 2) = tx_day
            limit 1
        );

        if exists (select 1
            from transactions_stats
            where entity_id = route_id
            and entity = 'route'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0
        ) then
            update transactions_stats
            set
                total_cashless = amount,
                count_cashless = (amount / 2000),
                count = (amount / 2000) + count,
                total = amount + total,
                is_closed = 1
            where entity_id = route_id
            and entity = 'route'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0;
        end if;


        company_id := (
            select id
            from companies
            where merchant_id = (select merchant_id from reconciliations where terminal_id = new.terminal_id
            and substring(cast(created_at::timestamp::date as text) from 1 for 4) = tx_year
            and substring(cast(created_at::timestamp::date as text) from 6 for 2) = tx_month
            and substring(cast(created_at::timestamp::date as text) from 9 for 2) = tx_day
            limit 1)
        );

        if exists (select 1
            from transactions_stats
            where entity_id = company_id
            and entity = 'company'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0
        ) then
            update transactions_stats
            set
                total_cashless = amount,
                count_cashless = (amount / 2000),
                count = (amount / 2000) + count,
                total = amount + total,
                is_closed = 1
            where entity_id = company_id
            and entity = 'company'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0;
        end if;

        device_id := (
            select id
            from devices
            where terminal_id = (select merchant_id from reconciliations where terminal_id = new.terminal_id
            and substring(cast(created_at::timestamp::date as text) from 1 for 4) = tx_year
            and substring(cast(created_at::timestamp::date as text) from 6 for 2) = tx_month
            and substring(cast(created_at::timestamp::date as text) from 9 for 2) = tx_day
            limit 1)
        );

        if exists (select 1
            from transactions_stats
            where entity_id = device_id
            and entity = 'device'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0
        ) then
            update transactions_stats
            set
                total_cashless = amount,
                count_cashless = (amount / 2000),
                count = (amount / 2000) + count,
                total = amount + total,
                is_closed = 1
            where entity_id = device_id
            and entity = 'device'
            and day = tx_day
            and month = tx_month
            and year = tx_year
            and is_closed = 0;
        end if;

    end if;

    return new;
end;
$function$
;