Queries

SELECT CASE WHEN $3 < LENGTH(CAST("public"."onboarding_merchant_events"."metadata" AS TEXT)) THEN $4 ELSE "public"."onboarding_merchant_events"."metadata" END AS "metadata" FROM "public"."onboarding_merchant_events" INNER JOIN ((SELECT "public"."onboarding_merchant_events"."id" FROM "public"."onboarding_merchant_events" ORDER BY "public"."onboarding_merchant_events"."id" ASC LIMIT $1) UNION ALL (SELECT "public"."onboarding_merchant_events"."id" FROM "public"."onboarding_merchant_events" ORDER BY "public"."onboarding_merchant_events"."id" DESC LIMIT $2)) AS "result" ON ("result"."id" = "public"."onboarding_merchant_events"."id")

-- Metabase:: userID: 1 queryType: native queryHash: 0bddb0d867fd99792662e3b98c871ca225cce987c5cdac7c206c8a8a562c32c2
with selected_date as (
    select cast(cast( now() 
        as date) as timestamp) as ANALYSIS_DATE,
        TO_CHAR(cast( now() 
        as date), $2) as ANALYSIS_DATE_VW
),
exec_date as (
    select ANALYSIS_DATE, ANALYSIS_DATE_VW, ANALYSIS_DATE start_date, cast(ANALYSIS_DATE + INTERVAL $3 as timestamp) end_date
        --cast(ANALYSIS_DATE - INTERVAL '2 days' as timestamp) start_date, cast((ANALYSIS_DATE - INTERVAL '1 day') + INTERVAL '23:59:59' as timestamp) end_date
    from selected_date
),
merchants_with_transactions as (
    select distinct ANALYSIS_DATE, ANALYSIS_DATE_VW, start_date, end_date,
        tr.merchant_id,
        tr.business_unit_id,
        merchants.name AS merchant_name,
        merchants.status AS merchant_status,
        merchants.status_reason AS merchant_status_reason,
        merchants.tax_number,
        bu.name AS bu_name,
        bu.branch,
        bu.division,
        bu.mcc,
        bu.status AS bu_status,
        bu.status_reason AS bu_status_reason,
        bu.tax_number AS bu_tax_number
    from exec_date ed
    inner join transactions tr on tr.created_at between ed.start_date and ed.end_date
    INNER JOIN merchants ON merchants.id = tr.merchant_id
    LEFT JOIN merchant_business_units bu ON bu.merchant_id = merchants.id 
                                        AND bu.id = tr.business_unit_id 
),
analysis_data as (
    SELECT 
        start_date, end_date,
        flt_merchants.ANALYSIS_DATE_VW,
        flt_merchants.start_date,
        flt_merchants.end_date,
        flt_merchants.merchant_id,
        flt_merchants.business_unit_id,
        flt_merchants.merchant_name,
        flt_merchants.merchant_status,
        flt_merchants.merchant_status_reason,
        flt_merchants.tax_number,
        flt_merchants.bu_name,
        flt_merchants.branch,
        flt_merchants.division,
        flt_merchants.mcc,
        flt_merchants.bu_status,
        flt_merchants.bu_status_reason,
        flt_merchants.bu_tax_number,
        -- Média e contagem das transações
        COUNT(tr.amount) AS count_transaction_day,
        sum(tr.amount) AS sum_amount_day,
        MIN(tr.amount) AS min_amount_day,
        MAX(tr.amount) AS max_amount_day,
        AVG(tr.amount) AS avg_amount_day,
        -- Desvio Padrão Transacional do dia
        STDDEV_POP(tr.amount) AS stddev_transaction_day,
        
            -- Contagem e média para os últimos 30, 60, 90 dias
        -- 30 dias
        (SELECT COUNT(*) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $4) AND end_date
        ) AS count_30_days,
        
        (SELECT AVG(tr30.amount) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $5) AND end_date
        ) AS avg_30_days,
        
        (SELECT STDDEV_POP(tr30.amount) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $6) AND end_date
        ) AS stddev_30_days,
        
        -- 60 dias
        (SELECT COUNT(*) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $7) AND end_date
        ) AS count_60_days,
        
        (SELECT AVG(tr60.amount) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $8) AND end_date
        ) AS avg_60_days,
        
        (SELECT STDDEV_POP(tr60.amount) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $9) AND end_date
        ) AS stddev_60_days,
        
        -- 90 dias
        (SELECT COUNT(*) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $10) AND end_date
        ) AS count_90_days,
        
        (SELECT AVG(tr90.amount) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $11) AND end_date
        ) AS avg_90_days,
        
        (SELECT STDDEV_POP(tr90.amount) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $12) AND end_date
        ) AS stddev_90_days
        
    FROM merchants_with_transactions flt_merchants
    inner join transactions tr on tr.created_at between flt_merchants.start_date and flt_merchants.end_date
    GROUP BY 
        start_date, end_date,
        flt_merchants.ANALYSIS_DATE_VW,
        flt_merchants.ANALYSIS_DATE,
        flt_merchants.start_date,
        flt_merchants.end_date,
        flt_merchants.merchant_id,
        flt_merchants.business_unit_id,
        flt_merchants.merchant_name,
        flt_merchants.merchant_status,
        flt_merchants.merchant_status_reason,
        flt_merchants.tax_number,
        flt_merchants.bu_name,
        flt_merchants.branch,
        flt_merchants.division,
        flt_merchants.mcc,
        flt_merchants.bu_status,
        flt_merchants.bu_status_reason,
        flt_merchants.bu_tax_number
    ORDER BY 1 DESC
),
score as (
    select *,
        -- Normalizando o Z-Score para o intervalo de 0 a 1 usando a fórmula de sigmoid
        ($13 / ($14 + EXP(-(avg_amount_day - avg_amount_day) / case when stddev_transaction_day <= $15 then $16 else stddev_transaction_day end))) AS weight_current_day,
        (CASE 
            WHEN avg_30_days IS NOT NULL AND stddev_30_days > $17 THEN 
                $18 / ($19 + EXP(-(avg_amount_day - avg_30_days) / stddev_30_days))
            ELSE $20 
        END) AS weight_30_days,
        
        (CASE 
            WHEN avg_60_days IS NOT NULL AND stddev_60_days > $21 THEN 
                $22 / ($23 + EXP(-(avg_amount_day - avg_60_days) / stddev_60_days))
            ELSE $24 
        END) AS weight_60_days,
        
        (CASE 
            WHEN avg_90_days IS NOT NULL AND stddev_90_days > $25 THEN 
                $26 / ($27 + EXP(-(avg_amount_day - avg_90_days) / stddev_90_days))
            ELSE $28 
        END) AS weight_90_days
    from analysis_data
),
monitoring as (
select
    *,
    -- Flag de Anomalia: com base no peso normalizado
    (CASE 
        WHEN weight_30_days >= $29 OR weight_60_days >= $30 OR weight_90_days >= $31 THEN $32 -- 'Alert'
        WHEN weight_30_days >= $33 OR weight_60_days >= $34 OR weight_90_days >= $35 THEN $36 -- 'Monitoring'
        ELSE weight_current_day -- 'Normal'
    END) AS risk_score
from score
)
select * ,
    -- Flag de Anomalia: com base no peso normalizado
    (CASE 
        WHEN risk_score >= $37 THEN $38
        WHEN risk_score >= $39 THEN $40
        ELSE $41
    END) AS anomaly_flag
from monitoring
where $42=$43
and risk_score >= (case $1-- FALSE
					when $44 then $45
					else $46 end )

SET DateStyle='ISO'

SET application_name = 'PostgreSQL JDBC Driver'

SET extra_float_digits = 3

SELECT COALESCE(SUM(pg_stat_get_live_tuples(c.oid)), $1) AS n_live_tup, COALESCE(SUM(pg_stat_get_dead_tuples(c.oid)), $2) AS n_dead_tup, current_timestamp FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = ANY (ARRAY[$3::"char", $4::"char", $5::"char", $6::"char"]) AND n.nspname <> ALL (ARRAY[$7::name, $8::name, $9::name])

SET application_name=''

SELECT
    COALESCE(sum(psat.n_tup_upd), $1) AS total_update_count,
    COALESCE(sum(psat.n_tup_hot_upd), $2) AS hot_update_count,
    (SELECT psd.stats_reset AS stats_reset_time FROM pg_catalog.pg_stat_database psd WHERE psd.datname = current_database()) AS stats_reset_time FROM
    pg_catalog.pg_stat_all_tables psat

select coalesce((select json_agg(j.data) from (
  select json_build_object($1::text, (json_build_object($2::text, sum($3))), $4::text, json_build_array((__local_0__."status")::text)) as data
  from "public"."transactions" as __local_0__
  where $5 = $6
  group by __local_0__."status"
  
) j), $7::json) as "@groupedAggregates"

SELECT COALESCE(SUM(pg_stat_get_live_tuples(c.oid)), $1) AS n_live_tup, COALESCE(SUM(pg_stat_get_dead_tuples(c.oid)), $2) AS n_dead_tup, current_timestamp FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = ANY (ARRAY[$3::"char", $4::"char", $5::"char", $6::"char"]) AND n.nspname = $7::name

alter table maindb.public.onboarding_tokens
    drop constraint onboarding_tokens_merchant_type_check

SELECT ome.* FROM public.onboarding_merchant_events AS ome

SELECT
    sum(psat.autovacuum_count) AS total_autovacuum_count,
    sum(psat.autoanalyze_count) AS total_autoanalyze_count,
    (SELECT psd.stats_reset FROM pg_stat_database psd WHERE psd.datname = current_database()) AS stats_reset_time
FROM
    pg_catalog.pg_stat_all_tables psat

SELECT "c"."column_name" AS "name", CASE WHEN "c"."udt_schema" IN ($3, $4) THEN FORMAT($5, "c"."udt_name") ELSE FORMAT($6, "c"."udt_schema", "c"."udt_name") END AS "database-type", "c"."ordinal_position" - $7 AS "database-position", "c"."table_schema" AS "table-schema", "c"."table_name" AS "table-name", "pk"."column_name" IS NOT NULL AS "pk?", COL_DESCRIPTION(CAST(CAST(FORMAT($8, CAST("c"."table_schema" AS TEXT), CAST("c"."table_name" AS TEXT)) AS REGCLASS) AS OID), "c"."ordinal_position") AS "field-comment", (("column_default" IS NULL) OR (LOWER("column_default") = $9)) AND ("is_nullable" = $10) AND NOT ((("column_default" IS NOT NULL) AND ("column_default" LIKE $11)) OR ("is_identity" <> $12)) AS "database-required", (("column_default" IS NOT NULL) AND ("column_default" LIKE $13)) OR ("is_identity" <> $14) AS "database-is-auto-increment" FROM "information_schema"."columns" AS "c" LEFT JOIN (SELECT "tc"."table_schema", "tc"."table_name", "kc"."column_name" FROM "information_schema"."table_constraints" AS "tc" INNER JOIN "information_schema"."key_column_usage" AS "kc" ON ("tc"."constraint_name" = "kc"."constraint_name") AND ("tc"."table_schema" = "kc"."table_schema") AND ("tc"."table_name" = "kc"."table_name") WHERE "tc"."constraint_type" = $15) AS "pk" ON ("c"."table_schema" = "pk"."table_schema") AND ("c"."table_name" = "pk"."table_name") AND ("c"."column_name" = "pk"."column_name") WHERE c.table_schema !~ $16 AND ("c"."table_schema" IN ($1)) UNION ALL SELECT "pa"."attname" AS "name", CASE WHEN "ptn"."nspname" IN ($17, $18) THEN FORMAT($19, "pt"."typname") ELSE FORMAT($20, "ptn"."nspname", "pt"."typname") END AS "database-type", "pa"."attnum" - $21 AS "database-position", "pn"."nspname" AS "table-schema", "pc"."relname" AS "table-name", $22 AS "pk?", $23 AS "field-comment", $24 AS "database-required", $25 AS "database-is-auto-increment" FROM "pg_catalog"."pg_class" AS "pc" INNER JOIN "pg_catalog"."pg_namespace" AS "pn" ON "pn"."oid" = "pc"."relnamespace" INNER JOIN "pg_catalog"."pg_attribute" AS "pa" ON "pa"."attrelid" = "pc"."oid" INNER JOIN "pg_catalog"."pg_type" AS "pt" ON "pt"."oid" = "pa"."atttypid" INNER JOIN "pg_catalog"."pg_namespace" AS "ptn" ON "ptn"."oid" = "pt"."typnamespace" WHERE ("pc"."relkind" = $26) AND ("pa"."attnum" >= $27) AND ("pn"."nspname" IN ($2)) ORDER BY "table-schema" ASC, "table-name" ASC, "database-position" ASC

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json((with __local_1__ as (select to_json((json_build_object($3::text, json_build_array(((__local_2__."id")::numeric)::text), $4::text, ((__local_2__."amount"))::text, $5::text, (__local_2__."authorization_code"), $6::text, (__local_2__."blocked_payer_reason"), $7::text, (__local_2__."brand"), $8::text, ((__local_2__."business_unit_id"))::text, $9::text, (__local_2__."comments"), $10::text, (__local_2__."due_date"), $11::text, (__local_2__."external_id"), $12::text, (__local_2__."installment_interest_type"), $13::text, ((__local_2__."id"))::text, $14::text, (__local_2__."installment_number"), $15::text, (__local_2__."installment_total"), $16::text, (__local_2__."internal_id"), $17::text, ((__local_2__."merchant_id"))::text, $18::text, (__local_2__."movement_date"), $19::text, (__local_2__."nsu"), $20::text, (__local_2__."order_code"), $21::text, (__local_2__."payer_tax_number"), $22::text, ((__local_2__."payment_partner_id"))::text, $23::text, (__local_2__."refunded_at"), $24::text, (__local_2__."refund_status"), $25::text, (__local_2__."status"), $26::text, (__local_2__."status_reason"), $27::text, (__local_2__."tags"), $28::text, (__local_2__."transaction_info"), $29::text, ((__local_2__."transaction_parent_id"))::text, $30::text, (__local_2__."created_at"), $31::text, (__local_2__."deleted_at"), $32::text, (__local_2__."updated_at"), $33::text, (__local_2__."merchant_payment_identifier"), $34::text, (__local_2__."transaction_token"), $35::text, (__local_2__."currency_code"), $36::text, (select json_build_object($37::text, json_build_array(((__local_3__."id")::numeric)::text), $38::text, (__local_3__."payment_method_name")) as object
from "public"."payment_methods" as __local_3__

where (__local_2__."payment_method_id" = __local_3__."id") and ($39) and ($40)


), $41::text, (__local_2__."capture_method"), $42::text, (select json_build_object($43::text, json_build_array(((__local_4__."id")::numeric)::text), $44::text, (__local_4__."merchant_token"), $45::text, (__local_4__."name")) as object
from "public"."merchants" as __local_4__

where (__local_2__."merchant_id" = __local_4__."id") and ($46) and ($47)


), $48::text, (select json_build_object($49::text, json_build_array(((__local_5__."id")::numeric)::text), $50::text, (__local_5__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_5__

where (__local_2__."business_unit_id" = __local_5__."id") and ($51) and ($52)


), $53::text, (__local_2__."invoice_token"), $54::text, (__local_2__."metadata")))) as "@nodes" from (select __local_2__.*
from "public"."transactions" as __local_2__

where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $55::"pg_catalog"."int8" limit $56)))) and ($57) and ($58)
order by __local_2__."created_at" DESC,__local_2__."id" ASC
limit $59
) __local_2__), __local_6__ as (select json_agg(to_json(__local_1__)) as data from __local_1__) select json_build_object($60::text, coalesce((select __local_6__.data from __local_6__), $61::json), $62::text, (
  select json_build_object($63::text, count($64))
  from "public"."transactions" as __local_2__
  where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $65::"pg_catalog"."int8" limit $66))))
)) )) as "@allTransactions"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $2) and ($67) and ($68)

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(((__local_0__."id"))::text) as "id", to_json((__local_0__."description")) as "description", to_json((__local_0__."capture_method")) as "captureMethod", to_json(((__local_0__."business_unit_id"))::text) as "businessUnitId", to_json(((__local_0__."amount"))::text) as "amount", to_json((__local_0__."due_date")) as "dueDate", to_json((__local_0__."expiration_date")) as "expirationDate", to_json((__local_0__."invoice_info")) as "invoiceInfo", to_json((__local_0__."installment_total")) as "installmentTotal", to_json((__local_0__."payer_tax_number")) as "payerTaxNumber", to_json((__local_0__."payer_name")) as "payerName", to_json((__local_0__."merchant_payment_id")) as "merchantPaymentId", to_json((select json_build_object($2::text, json_build_array(((__local_1__."id")::numeric)::text), $3::text, (__local_1__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_1__

where (__local_0__."business_unit_id" = __local_1__."id") and ($4) and ($5)


)) as "@merchantBusinessUnitByBusinessUnitId", to_json((select json_build_object($6::text, json_build_array(((__local_2__."id")::numeric)::text), $7::text, (__local_2__."name"), $8::text, (__local_2__."merchant_token"), $9::text, (with __local_3__ as (select to_json((json_build_object($10::text, json_build_array(((__local_4__."id")::numeric)::text), $11::text, (__local_4__."status"), $12::text, (__local_4__."transaction_token"), $13::text, (__local_4__."updated_at"), $14::text, (select json_build_object($15::text, json_build_array(((__local_5__."id")::numeric)::text), $16::text, (__local_5__."payment_method_name")) as object
from "public"."payment_methods" as __local_5__

where (__local_4__."payment_method_id" = __local_5__."id") and ($17) and ($18)


)))) as "@nodes" from (select __local_4__.*
from "public"."transactions" as __local_4__

where (__local_4__."merchant_id" = __local_2__."id") and ($19) and ($20)
order by __local_4__."id" ASC

) __local_4__), __local_6__ as (select json_agg(to_json(__local_3__)) as data from __local_3__) select json_build_object($21::text, coalesce((select __local_6__.data from __local_6__), $22::json)) )) as object
from "public"."merchants" as __local_2__

where (__local_0__."merchant_id" = __local_2__."id") and ($23) and ($24)


)) as "@merchantByMerchantId"
from "public"."invoices" as __local_0__

where (__local_0__."invoice_token" = $1) and ($25) and ($26)

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($8::text, coalesce((select json_agg(j.data) from (
  select json_build_object($9::text, (json_build_object($10::text, sum($11), $12::text, (json_build_object($13::text, coalesce(sum(__local_1__."amount"), $14))))), $15::text, json_build_array((__local_1__."status")::text, (date_trunc($16, __local_1__."created_at"))::text)) as data
  from "public"."transactions" as __local_1__
  where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."status" IN ($2,$3,$4))) and ((__local_1__."created_at" <= $5) and (__local_1__."created_at" >= $6)))
  group by __local_1__."status", date_trunc($17, __local_1__."created_at")
  
) j), $18::json)) )) as "@paymentCompare", to_json(( select json_build_object($19::text, coalesce((select json_agg(j.data) from (
  select json_build_object($20::text, (json_build_object($21::text, sum($22))), $23::text, json_build_array((__local_2__."status")::text)) as data
  from "public"."transactions" as __local_2__
  where (__local_2__."merchant_id" = __local_0__."id")
  group by __local_2__."status"
  
) j), $24::json)) )) as "@StatusTransactionsKeys"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $7) and ($25) and ($26)

select 
    merchants.merchant_token,
    mbu.business_unit_token,
	mbu."name",  
	mbu.tax_number, 
	tw.id, 
    tw.wallet_token,
    tw.currency_code,
    tw.bank_number,
    tw.bank_account_number,
    tw.bank_account_digit,
    tw.bank_branch,
    tw.bank_name,
    tw.currency_token,
	buk.partner_id,
    buk.id pix_key_id,
	buk.key_name,
	buk.key_type,
    buk.key_value
from business_unit_partner_keys buk
inner join merchant_business_units mbu on buk.business_unit_token = mbu.business_unit_token
inner join merchants on merchants.id = mbu.merchant_id
inner join transaction_wallets tw on tw.business_unit_token = mbu.business_unit_token
								 and tw.currency_code = $1
								 and tw.wallet_type = $2
								 and tw.wallet_subtype = $3
								 and tw.partner_token = $4
where buk.key_name = $5 
  and buk.key_type = $6 
  and buk.key_value = $7
  and buk.partner_id = $8

SELECT * FROM (SELECT current_database(), n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull  OR (t.typtype = $1 AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,t.typtypmod,row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, nullif(a.attidentity, $2) as attidentity,nullif(a.attgenerated, $3) as attgenerated,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname=$4)  LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname=$5)  WHERE c.relkind in ($6,$7,$8,$9,$10) and a.attnum > $11 AND NOT a.attisdropped  AND n.nspname LIKE $12 AND c.relname LIKE $13) c WHERE $14  ORDER BY nspname,c.relname,attnum

SELECT        result.TABLE_CAT AS "TABLE_CAT",        result.TABLE_SCHEM AS "TABLE_SCHEM",        result.TABLE_NAME AS "TABLE_NAME",        result.COLUMN_NAME AS "COLUMN_NAME",        result.KEY_SEQ AS "KEY_SEQ",        result.PK_NAME AS "PK_NAME"FROM      (SELECT current_database() AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,   (information_schema._pg_expandarray(i.indkey)).n AS KEY_SEQ, ci.relname AS PK_NAME,   information_schema._pg_expandarray(i.indkey) AS KEYS, a.attnum AS A_ATTNUM, i.indnkeyatts as KEY_COUNT FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid)   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)   JOIN pg_catalog.pg_index i ON ( a.attrelid = i.indrelid)   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE $1  AND n.nspname = $2 AND ct.relname = $3 AND i.indisprimary  ) result where  result.A_ATTNUM = (result.KEYS).x AND result.KEY_SEQ <= KEY_COUNT  ORDER BY result.table_name, result.pk_name, result.key_seq

SELECT "c"."column_name" AS "name", CASE WHEN "c"."udt_schema" IN ($4, $5) THEN FORMAT($6, "c"."udt_name") ELSE FORMAT($7, "c"."udt_schema", "c"."udt_name") END AS "database-type", "c"."ordinal_position" - $8 AS "database-position", "c"."table_schema" AS "table-schema", "c"."table_name" AS "table-name", "pk"."column_name" IS NOT NULL AS "pk?", COL_DESCRIPTION(CAST(CAST(FORMAT($9, CAST("c"."table_schema" AS TEXT), CAST("c"."table_name" AS TEXT)) AS REGCLASS) AS OID), "c"."ordinal_position") AS "field-comment", (("column_default" IS NULL) OR (LOWER("column_default") = $10)) AND ("is_nullable" = $11) AND NOT ((("column_default" IS NOT NULL) AND ("column_default" LIKE $12)) OR ("is_identity" <> $13)) AS "database-required", "column_default" AS "database-default", (("column_default" IS NOT NULL) AND ("column_default" LIKE $14)) OR ("is_identity" <> $15) AS "database-is-auto-increment", "is_generated" = $1 AS "database-is-generated", "is_nullable" = $16 AS "database-is-nullable" FROM "information_schema"."columns" AS "c" LEFT JOIN (SELECT "tc"."table_schema", "tc"."table_name", "kc"."column_name" FROM "information_schema"."table_constraints" AS "tc" INNER JOIN "information_schema"."key_column_usage" AS "kc" ON ("tc"."constraint_name" = "kc"."constraint_name") AND ("tc"."table_schema" = "kc"."table_schema") AND ("tc"."table_name" = "kc"."table_name") WHERE "tc"."constraint_type" = $17) AS "pk" ON ("c"."table_schema" = "pk"."table_schema") AND ("c"."table_name" = "pk"."table_name") AND ("c"."column_name" = "pk"."column_name") WHERE c.table_schema !~ $18 AND ("c"."table_schema" IN ($2)) UNION ALL SELECT "pa"."attname" AS "name", CASE WHEN "ptn"."nspname" IN ($19, $20) THEN FORMAT($21, "pt"."typname") ELSE FORMAT($22, "ptn"."nspname", "pt"."typname") END AS "database-type", "pa"."attnum" - $23 AS "database-position", "pn"."nspname" AS "table-schema", "pc"."relname" AS "table-name", $24 AS "pk?", $25 AS "field-comment", $26 AS "database-required", $27 AS "database-default", $28 AS "database-is-auto-increment", $29 AS "database-is-generated", $30 AS "database-is-nullable" FROM "pg_catalog"."pg_class" AS "pc" INNER JOIN "pg_catalog"."pg_namespace" AS "pn" ON "pn"."oid" = "pc"."relnamespace" INNER JOIN "pg_catalog"."pg_attribute" AS "pa" ON "pa"."attrelid" = "pc"."oid" INNER JOIN "pg_catalog"."pg_type" AS "pt" ON "pt"."oid" = "pa"."atttypid" INNER JOIN "pg_catalog"."pg_namespace" AS "ptn" ON "ptn"."oid" = "pt"."typnamespace" WHERE ("pc"."relkind" = $31) AND ("pa"."attnum" >= $32) AND ("pn"."nspname" IN ($3)) ORDER BY "table-schema" ASC, "table-name" ASC, "database-position" ASC

select te1_0.id,te1_0.amount,te1_0.authorization_code,te1_0.blocked_payer_reason,te1_0.brand,te1_0.business_unit_id,te1_0.canceled_reason,te1_0.capture_method,te1_0.card_brand,te1_0.card_expiration_month,te1_0.card_expiration_year,te1_0.card_pan,te1_0.comments,te1_0.created_at,te1_0.currency_code,te1_0.deleted_at,te1_0.due_date,te1_0.external_id,te1_0.fee_total_amount,te1_0.has_payer,te1_0.installment_interest_type,te1_0.installment_number,te1_0.installment_total,te1_0.internal_id,te1_0.invoice_token,te1_0.merchant_id,te1_0.merchant_payment_id,te1_0.merchant_payment_identifier,te1_0.metadata,te1_0.movement_date,te1_0.nsu,te1_0.paid_amount,te1_0.partner_tracking_id,te1_0.partner_transaction_id,te1_0.payer_tax_number,te1_0.payment_method_id,te1_0.payment_partner_id,te1_0.refund_status,te1_0.refunded_amount,te1_0.refunded_at,te1_0.session_key,te1_0.status,te1_0.status_reason,te1_0.tags,te1_0.transaction_info,te1_0.transaction_token,te1_0.transaction_type,te1_0.updated_at from transactions te1_0 where te1_0.partner_tracking_id=$1

CREATE INDEX CONCURRENTLY ON transactions (partner_tracking_id)

Rows: 22438
Row progression: 22438, 1

Row estimates
- partner_tracking_id (=): 1

Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($7::text, (
  select json_build_object($8::text, (json_build_object($9::text, sum($10), $11::text, (json_build_object($12::text, coalesce(sum(__local_1__."amount"), $13))), $14::text, (json_build_object($15::text, avg(__local_1__."amount"))), $16::text, (json_build_object($17::text, count(distinct __local_1__."id"))))))
  from "public"."transactions" as __local_1__
  where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."status" NOT IN ($2,$3,$4,$5))))
)) )) as "@totalRevenue"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $6) and ($18) and ($19)

with __local_0__ as (select to_json((json_build_object($2::text, json_build_array(((__local_1__."id")::numeric)::text), $3::text, ((__local_1__."id"))::text, $4::text, ((__local_1__."amount"))::text, $5::text, (__local_1__."status"), $6::text, (__local_1__."transaction_token"), $7::text, ((__local_1__."merchant_id"))::text, $8::text, ((__local_1__."business_unit_id"))::text, $9::text, (__local_1__."created_at"), $10::text, (__local_1__."updated_at"), $11::text, (__local_1__."payer_tax_number"), $12::text, (__local_1__."metadata"), $13::text, (__local_1__."due_date"), $14::text, (__local_1__."brand"), $15::text, (__local_1__."nsu"), $16::text, (__local_1__."authorization_code"), $17::text, (__local_1__."capture_method"), $18::text, (__local_1__."currency_code"), $19::text, (__local_1__."invoice_token"), $20::text, (__local_1__."installment_total"), $21::text, (select json_build_object($22::text, json_build_array(((__local_2__."id")::numeric)::text), $23::text, (__local_2__."payment_method_name")) as object
from "public"."payment_methods" as __local_2__

where (__local_1__."payment_method_id" = __local_2__."id") and ($24) and ($25)


), $26::text, (select json_build_object($27::text, json_build_array(((__local_3__."id")::numeric)::text), $28::text, (__local_3__."merchant_token")) as object
from "public"."merchants" as __local_3__

where (__local_1__."merchant_id" = __local_3__."id") and ($29) and ($30)


), $31::text, (select json_build_object($32::text, json_build_array(((__local_4__."id")::numeric)::text), $33::text, (__local_4__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_4__

where (__local_1__."business_unit_id" = __local_4__."id") and ($34) and ($35)


), $36::text, (__local_1__."merchant_payment_identifier")))) as "@nodes" from (select __local_1__.*
from "public"."transactions" as __local_1__

where (__local_1__."invoice_token" = $1) and ($37) and ($38)
order by __local_1__."id" ASC

) __local_1__), __local_5__ as (select json_agg(to_json(__local_0__)) as data from __local_0__) select coalesce((select __local_5__.data from __local_5__), $39::json) as "data"

SELECT extname, current_timestamp FROM pg_catalog.pg_extension UNION SELECT plugin, current_timestamp FROM pg_catalog.pg_replication_slots WHERE slot_type = $1 AND database = current_database()

with __local_0__ as (select to_json((json_build_object($2::text, json_build_array(((__local_1__."id")::numeric)::text), $3::text, ((__local_1__."id"))::text, $4::text, ((__local_1__."amount"))::text, $5::text, (__local_1__."status"), $6::text, (__local_1__."transaction_token"), $7::text, ((__local_1__."merchant_id"))::text, $8::text, ((__local_1__."business_unit_id"))::text, $9::text, (__local_1__."created_at"), $10::text, (__local_1__."updated_at"), $11::text, (__local_1__."payer_tax_number"), $12::text, (__local_1__."metadata"), $13::text, (__local_1__."due_date"), $14::text, (__local_1__."brand"), $15::text, (__local_1__."nsu"), $16::text, (__local_1__."authorization_code"), $17::text, (__local_1__."capture_method"), $18::text, (__local_1__."currency_code"), $19::text, (__local_1__."invoice_token"), $20::text, (__local_1__."installment_total"), $21::text, (select json_build_object($22::text, json_build_array(((__local_2__."id")::numeric)::text), $23::text, (__local_2__."payment_method_name")) as object
from "public"."payment_methods" as __local_2__

where (__local_1__."payment_method_id" = __local_2__."id") and ($24) and ($25)


), $26::text, (select json_build_object($27::text, json_build_array(((__local_3__."id")::numeric)::text), $28::text, (__local_3__."merchant_token")) as object
from "public"."merchants" as __local_3__

where (__local_1__."merchant_id" = __local_3__."id") and ($29) and ($30)


), $31::text, (select json_build_object($32::text, json_build_array(((__local_4__."id")::numeric)::text), $33::text, (__local_4__."business_unit_token"), $34::text, (select json_build_object($35::text, json_build_array(((__local_5__."id")::numeric)::text), $36::text, (__local_5__."name")) as object
from "public"."merchants" as __local_5__

where (__local_4__."merchant_id" = __local_5__."id") and ($37) and ($38)


)) as object
from "public"."merchant_business_units" as __local_4__

where (__local_1__."business_unit_id" = __local_4__."id") and ($39) and ($40)


), $41::text, (__local_1__."merchant_payment_identifier")))) as "@nodes" from (select __local_1__.*
from "public"."transactions" as __local_1__

where (__local_1__."invoice_token" = $1) and ($42) and ($43)
order by __local_1__."id" ASC

) __local_1__), __local_6__ as (select json_agg(to_json(__local_0__)) as data from __local_0__) select coalesce((select __local_6__.data from __local_6__), $44::json) as "data"

SELECT $1

insert into transaction_events (amount,created_at,event_name,integration_id,event_metadata,session_key,status,status_reason,transaction_id) values ($1,$2,$3,$4,$5,$6,$7,$8,$9) returning id

insert into transactions (amount,authorization_code,blocked_payer_reason,brand,business_unit_id,canceled_reason,capture_method,comments,created_at,deleted_at,due_date,external_id,has_payer,installment_interest_type,installment_number,installment_total,internal_id,invoice_token,merchant_id,merchant_payment_id,merchant_payment_identifier,metadata,movement_date,nsu,paid_amount,partner_tracking_id,partner_transaction_id,payer_tax_number,payment_method_id,payment_partner_id,refund_status,refunded_amount,refunded_at,session_key,status,status_reason,tags,transaction_info,transaction_token,transaction_type,updated_at) values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41) returning id

SET statement_timeout TO 3000

SET extra_float_digits = 2

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json((with __local_1__ as (select to_json((json_build_object($5::text, json_build_array(((__local_2__."id")::numeric)::text), $6::text, (__local_2__."metadata"), $7::text, (__local_2__."order_code")))) as "@list" from (select __local_2__.*
from "public"."transactions" as __local_2__

where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."created_at" <= $2) and (__local_2__."created_at" >= $3))) and ($8) and ($9)
order by __local_2__."created_at" DESC,__local_2__."id" ASC

) __local_2__), __local_3__ as (select json_agg(to_json(__local_1__)) as data from __local_1__) select json_build_object($10::text, coalesce((select __local_3__.data from __local_3__), $11::json)) )) as "@payers"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $4) and ($12) and ($13)

update transactions set amount=$1,authorization_code=$2,blocked_payer_reason=$3,brand=$4,business_unit_id=$5,canceled_reason=$6,capture_method=$7,comments=$8,created_at=$9,deleted_at=$10,due_date=$11,external_id=$12,has_payer=$13,installment_interest_type=$14,installment_number=$15,installment_total=$16,internal_id=$17,invoice_token=$18,merchant_id=$19,merchant_payment_id=$20,merchant_payment_identifier=$21,metadata=$22,movement_date=$23,nsu=$24,paid_amount=$25,partner_tracking_id=$26,partner_transaction_id=$27,payer_tax_number=$28,payment_method_id=$29,payment_partner_id=$30,refund_status=$31,refunded_amount=$32,refunded_at=$33,session_key=$34,status=$35,status_reason=$36,tags=$37,transaction_info=$38,transaction_token=$39,transaction_type=$40,updated_at=$41 where id=$42

Covered by index on (id)
Rows: 22438
Row progression: 22438, 1

Row estimates
- id (=): 1

Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE

SET TimeZone='UTC'

-- Metabase:: userID: 1 queryType: native queryHash: 25009ce1f2f3e41c03fe8b5a4979e2a976a0bfe1e3dbcc1d7450871e4b3e7a46
with selected_date as (
    select cast(cast( now() 
        as date) as timestamp) as ANALYSIS_DATE,
        TO_CHAR(cast( now() 
        as date), $1) as ANALYSIS_DATE_VW
),
exec_date as (
    select ANALYSIS_DATE, ANALYSIS_DATE_VW, ANALYSIS_DATE start_date, cast(ANALYSIS_DATE + INTERVAL $2 as timestamp) end_date
        --cast(ANALYSIS_DATE - INTERVAL '2 days' as timestamp) start_date, cast((ANALYSIS_DATE - INTERVAL '1 day') + INTERVAL '23:59:59' as timestamp) end_date
    from selected_date
),
merchants_with_transactions as (
    select distinct ANALYSIS_DATE, ANALYSIS_DATE_VW, start_date, end_date,
        tr.merchant_id,
        tr.business_unit_id,
        merchants.name AS merchant_name,
        merchants.status AS merchant_status,
        merchants.status_reason AS merchant_status_reason,
        merchants.tax_number,
        bu.name AS bu_name,
        bu.branch,
        bu.division,
        bu.mcc,
        bu.status AS bu_status,
        bu.status_reason AS bu_status_reason,
        bu.tax_number AS bu_tax_number
    from exec_date ed
    inner join transactions tr on tr.created_at between ed.start_date and ed.end_date
    INNER JOIN merchants ON merchants.id = tr.merchant_id
    LEFT JOIN merchant_business_units bu ON bu.merchant_id = merchants.id 
                                        AND bu.id = tr.business_unit_id 
),
analysis_data as (
    SELECT 
        start_date, end_date,
        flt_merchants.ANALYSIS_DATE_VW,
        flt_merchants.start_date,
        flt_merchants.end_date,
        flt_merchants.merchant_id,
        flt_merchants.business_unit_id,
        flt_merchants.merchant_name,
        flt_merchants.merchant_status,
        flt_merchants.merchant_status_reason,
        flt_merchants.tax_number,
        flt_merchants.bu_name,
        flt_merchants.branch,
        flt_merchants.division,
        flt_merchants.mcc,
        flt_merchants.bu_status,
        flt_merchants.bu_status_reason,
        flt_merchants.bu_tax_number,
        -- Média e contagem das transações
        COUNT(tr.amount) AS count_transaction_day,
        sum(tr.amount) AS sum_amount_day,
        MIN(tr.amount) AS min_amount_day,
        MAX(tr.amount) AS max_amount_day,
        AVG(tr.amount) AS avg_amount_day,
        -- Desvio Padrão Transacional do dia
        STDDEV_POP(tr.amount) AS stddev_transaction_day,
        
            -- Contagem e média para os últimos 30, 60, 90 dias
        -- 30 dias
        (SELECT COUNT(*) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $3) AND end_date
        ) AS count_30_days,
        
        (SELECT AVG(tr30.amount) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $4) AND end_date
        ) AS avg_30_days,
        
        (SELECT STDDEV_POP(tr30.amount) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $5) AND end_date
        ) AS stddev_30_days,
        
        -- 60 dias
        (SELECT COUNT(*) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $6) AND end_date
        ) AS count_60_days,
        
        (SELECT AVG(tr60.amount) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $7) AND end_date
        ) AS avg_60_days,
        
        (SELECT STDDEV_POP(tr60.amount) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $8) AND end_date
        ) AS stddev_60_days,
        
        -- 90 dias
        (SELECT COUNT(*) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $9) AND end_date
        ) AS count_90_days,
        
        (SELECT AVG(tr90.amount) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $10) AND end_date
        ) AS avg_90_days,
        
        (SELECT STDDEV_POP(tr90.amount) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $11) AND end_date
        ) AS stddev_90_days
        
    FROM merchants_with_transactions flt_merchants
    inner join transactions tr on tr.created_at between flt_merchants.start_date and flt_merchants.end_date
    GROUP BY 
        start_date, end_date,
        flt_merchants.ANALYSIS_DATE_VW,
        flt_merchants.ANALYSIS_DATE,
        flt_merchants.start_date,
        flt_merchants.end_date,
        flt_merchants.merchant_id,
        flt_merchants.business_unit_id,
        flt_merchants.merchant_name,
        flt_merchants.merchant_status,
        flt_merchants.merchant_status_reason,
        flt_merchants.tax_number,
        flt_merchants.bu_name,
        flt_merchants.branch,
        flt_merchants.division,
        flt_merchants.mcc,
        flt_merchants.bu_status,
        flt_merchants.bu_status_reason,
        flt_merchants.bu_tax_number
    ORDER BY 1 DESC
),
score as (
    select *,
        -- Normalizando o Z-Score para o intervalo de 0 a 1 usando a fórmula de sigmoid
        ($12 / ($13 + EXP(-(avg_amount_day - avg_amount_day) / case when stddev_transaction_day <= $14 then $15 else stddev_transaction_day end))) AS weight_current_day,
        (CASE 
            WHEN avg_30_days IS NOT NULL AND stddev_30_days > $16 THEN 
                $17 / ($18 + EXP(-(avg_amount_day - avg_30_days) / stddev_30_days))
            ELSE $19 
        END) AS weight_30_days,
        
        (CASE 
            WHEN avg_60_days IS NOT NULL AND stddev_60_days > $20 THEN 
                $21 / ($22 + EXP(-(avg_amount_day - avg_60_days) / stddev_60_days))
            ELSE $23 
        END) AS weight_60_days,
        
        (CASE 
            WHEN avg_90_days IS NOT NULL AND stddev_90_days > $24 THEN 
                $25 / ($26 + EXP(-(avg_amount_day - avg_90_days) / stddev_90_days))
            ELSE $27 
        END) AS weight_90_days
    from analysis_data
),
monitoring as (
select
    *,
    -- Flag de Anomalia: com base no peso normalizado
    (CASE 
        WHEN weight_30_days >= $28 OR weight_60_days >= $29 OR weight_90_days >= $30 THEN $31 -- 'Alert'
        WHEN weight_30_days >= $32 OR weight_60_days >= $33 OR weight_90_days >= $34 THEN $35 -- 'Monitoring'
        ELSE weight_current_day -- 'Normal'
    END) AS risk_score
from score
)
select * ,
    -- Flag de Anomalia: com base no peso normalizado
    (CASE 
        WHEN risk_score >= $36 THEN $37
        WHEN risk_score >= $38 THEN $39
        ELSE $40
    END) AS anomaly_flag
from monitoring

SET application_name='PostgreSQL JDBC Driver'

select te1_0.id,te1_0.amount,te1_0.authorization_code,te1_0.blocked_payer_reason,te1_0.brand,bu1_0.id,a1_0.id,a1_0.address_line,a1_0.address_line_2,a1_0.city,a1_0.city_code,a1_0.complement,a1_0.country,a1_0.created_at,a1_0.deleted_at,a1_0.federative_unit,a1_0.neighborhood,a1_0.reference,a1_0.state,a1_0.updated_at,a1_0.zip_code,bu1_0.branch,bu1_0.business_unit_token,bu1_0.created_at,bu1_0.deleted_at,bu1_0.division,bu1_0.mcc,bu1_0.merchant_id,m1_0.id,a2_0.id,a2_0.address_line,a2_0.address_line_2,a2_0.city,a2_0.city_code,a2_0.complement,a2_0.country,a2_0.created_at,a2_0.deleted_at,a2_0.federative_unit,a2_0.neighborhood,a2_0.reference,a2_0.state,a2_0.updated_at,a2_0.zip_code,m1_0.created_at,m1_0.deleted_at,m1_0.merchant_token,m1_0.name,m1_0.status,m1_0.status_reason,m1_0.tax_number,m1_0.updated_at,bu1_0.name,bu1_0.private_key,bu1_0.short_description,bu1_0.status,bu1_0.status_reason,bu1_0.tax_number,bu1_0.updated_at,te1_0.canceled_reason,te1_0.capture_method,te1_0.comments,te1_0.created_at,te1_0.deleted_at,te1_0.due_date,te1_0.external_id,te1_0.has_payer,te1_0.installment_interest_type,te1_0.installment_number,te1_0.installment_total,te1_0.internal_id,te1_0.invoice_token,m2_0.id,a3_0.id,a3_0.address_line,a3_0.address_line_2,a3_0.city,a3_0.city_code,a3_0.complement,a3_0.country,a3_0.created_at,a3_0.deleted_at,a3_0.federative_unit,a3_0.neighborhood,a3_0.reference,a3_0.state,a3_0.updated_at,a3_0.zip_code,m2_0.created_at,m2_0.deleted_at,m2_0.merchant_token,m2_0.name,m2_0.status,m2_0.status_reason,m2_0.tax_number,m2_0.updated_at,bu2_0.merchant_id,bu2_0.id,a4_0.id,a4_0.address_line,a4_0.address_line_2,a4_0.city,a4_0.city_code,a4_0.complement,a4_0.country,a4_0.created_at,a4_0.deleted_at,a4_0.federative_unit,a4_0.neighborhood,a4_0.reference,a4_0.state,a4_0.updated_at,a4_0.zip_code,bu2_0.branch,bu2_0.business_unit_token,bu2_0.created_at,bu2_0.deleted_at,bu2_0.division,bu2_0.mcc,bu2_0.name,bu2_0.private_key,bu2_0.short_description,bu2_0.status,bu2_0.status_reason,bu2_0.tax_number,bu2_0.updated_at,te1_0.merchant_payment_id,te1_0.merchant_payment_identifier,te1_0.metadata,te1_0.movement_date,te1_0.nsu,te1_0.paid_amount,te1_0.partner_tracking_id,te1_0.partner_transaction_id,te1_0.payer_tax_number,pm1_0.id,pm1_0.created_at,pm1_0.deleted_at,pm1_0.payment_method_name,pm1_0.payment_method_token,pm1_0.status,pm1_0.status_reason,pm1_0.updated_at,pp1_0.id,pp1_0.created_at,pp1_0.deleted_at,pp1_0.partner_name,pp1_0.partner_token,pp1_0.status,pp1_0.status_reason,pp1_0.updated_at,te1_0.refund_status,te1_0.refunded_amount,te1_0.refunded_at,te1_0.session_key,te1_0.status,te1_0.status_reason,te1_0.tags,te1_0.transaction_info,te1_0.transaction_token,te1_0.transaction_type,te1_0.updated_at from transactions te1_0 left join merchant_business_units bu1_0 on bu1_0.id=te1_0.business_unit_id left join addresses a1_0 on a1_0.id=bu1_0.address_id left join merchants m1_0 on m1_0.id=bu1_0.merchant_id left join addresses a2_0 on a2_0.id=m1_0.address_id left join merchants m2_0 on m2_0.id=te1_0.merchant_id left join addresses a3_0 on a3_0.id=m2_0.address_id left join merchant_business_units bu2_0 on m2_0.id=bu2_0.merchant_id left join addresses a4_0 on a4_0.id=bu2_0.address_id left join payment_methods pm1_0 on pm1_0.id=te1_0.payment_method_id left join payment_partners pp1_0 on pp1_0.id=te1_0.payment_partner_id where te1_0.id=$1

SELECT nspname, typname FROM pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace WHERE t.oid IN (SELECT DISTINCT enumtypid FROM pg_enum e)

with onboarding as (
	select * 
	from onboarding_merchants
	where id = $1
)
select *
from (
	SELECT 
		onboarding.id onboarding_id, 
		onboarding.onboarding_token onboarding_token, 
		onboarding.merchant_token merchant_token, 
		onboarding.name onboarding_name, 
		onboarding.mail onboarding_mail, 
		onboarding.tax_number onboarding_tax_number, 
		onboarding.tax_type onboarding_tax_type, 
		onboarding.nick_name onboarding_nick_name, 
		onboarding.phone_number onboarding_phone_number, 
		onboarding.company_type onboarding_company_type, 
		onboarding.merchant_type onboarding_merchant_type, 
		onboarding.metadata onboarding_metabase, 
		onboarding.address_id onboarding_address_id, 
		onboarding.status onboarding_status, 
		onboarding.status_reason onboarding_status_reason, 
		onboarding.step onboarding_step, 
		onboarding.version onboarding_version, 
		onboarding.lead_id onboarding_lead_id, 
		onboarding.hubspot_origin onboarding_hubspot_origin, 
		onboarding.created_at onboarding_created_at, 
		onboarding.updated_at onboarding_updated_at, 
		onboarding.deleted_at onboarding_deleted_at,
		evt.*, ROW_NUMBER() OVER (PARTITION BY evt.step ORDER BY evt.created_at DESC) AS row_num
    FROM onboarding 
    inner join public.onboarding_merchant_events evt on onboarding.id = evt.onboarding_id and 
    													evt.step in (
	    													$2,
															$3,
															$4,
															$5,
															$6,
															$7,
															$8,
															$9)
    													--evt.step not in ('COMPLETED', 'LEGAL_PERSON_DOCUMENTS_BACK', 'LEGAL_PERSON_DOCUMENTS_FRONT', 'COMPANY_DOCUMENTS_PROOF_ADDRESS','COMPANY_DOCUMENTS_ANNUAL_REVENUE','COMPANY_DOCUMENTS_SOCIAL_CONTRACT')
    order by evt.created_at desc
) last_list 
where last_list.row_num = $10

with onboarding as (
	select * 
	from onboarding_merchants
	where id = $1
)
select *
from (
	SELECT 
		onboarding.id onboarding_id, 
		onboarding.onboarding_token onboarding_token, 
		onboarding.merchant_token merchant_token, 
		onboarding.name onboarding_name, 
		onboarding.mail onboarding_mail, 
		onboarding.tax_number onboarding_tax_number, 
		onboarding.tax_type onboarding_tax_type, 
		onboarding.nick_name onboarding_nick_name, 
		onboarding.phone_number onboarding_phone_number, 
		onboarding.company_type onboarding_company_type, 
		onboarding.merchant_type onboarding_merchant_type, 
		onboarding.metadata onboarding_metabase, 
		onboarding.address_id onboarding_address_id, 
		onboarding.status onboarding_status, 
		onboarding.status_reason onboarding_status_reason, 
		onboarding.step onboarding_step, 
		onboarding.version onboarding_version, 
		onboarding.lead_id onboarding_lead_id, 
		onboarding.hubspot_origin onboarding_hubspot_origin, 
		onboarding.created_at onboarding_created_at, 
		onboarding.updated_at onboarding_updated_at, 
		onboarding.deleted_at onboarding_deleted_at,
		evt.*, ROW_NUMBER() OVER (PARTITION BY evt.step ORDER BY evt.created_at DESC) AS row_num
    FROM onboarding 
    inner join public.onboarding_merchant_events evt on onboarding.id = evt.onboarding_id --and 
    													--evt.step not in ('COMPLETED', 'LEGAL_PERSON_DOCUMENTS_BACK', 'LEGAL_PERSON_DOCUMENTS_FRONT', 'COMPANY_DOCUMENTS_PROOF_ADDRESS','COMPANY_DOCUMENTS_ANNUAL_REVENUE','COMPANY_DOCUMENTS_SOCIAL_CONTRACT')
    order by evt.created_at desc
) last_list 
where last_list.row_num = $2

SET TimeZone='GMT'

SELECT * FROM (SELECT current_database() AS current_database, n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull  OR (t.typtype = $3 AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,t.typtypmod,row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, nullif(a.attidentity, $4) as attidentity,nullif(a.attgenerated, $5) as attgenerated,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname=$6)  LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname=$7)  WHERE c.relkind in ($8,$9,$10,$11,$12) and a.attnum > $13 AND NOT a.attisdropped  AND n.nspname LIKE $1 AND c.relname LIKE $2) c WHERE $14  ORDER BY nspname,c.relname,attnum

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($3::text, (
  select json_build_object($4::text, (json_build_object($5::text, sum($6), $7::text, (json_build_object($8::text, coalesce(sum(__local_1__."amount"), $9))), $10::text, (json_build_object($11::text, avg(__local_1__."amount"))), $12::text, (json_build_object($13::text, count(distinct __local_1__."id"))))))
  from "public"."transactions" as __local_1__
  where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1)
)) )) as "@totalRevenue"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $2) and ($14) and ($15)

SELECT * FROM (SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype = $1 AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,t.typtypmod,row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, nullif(a.attidentity, $2) as attidentity,nullif(a.attgenerated, $3) as attgenerated,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname=$4)  LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname=$5)  WHERE c.relkind in ($6,$7,$8,$9,$10) and a.attnum > $11 AND NOT a.attisdropped  AND n.nspname LIKE $12 AND c.relname LIKE $13) c WHERE $14  ORDER BY nspname,c.relname,attnum

SELECT        result.TABLE_CAT AS "TABLE_CAT",        result.TABLE_SCHEM AS "TABLE_SCHEM",        result.TABLE_NAME AS "TABLE_NAME",        result.COLUMN_NAME AS "COLUMN_NAME",        result.KEY_SEQ AS "KEY_SEQ",        result.PK_NAME AS "PK_NAME"FROM      (SELECT current_database() AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,   (information_schema._pg_expandarray(i.indkey)).n AS KEY_SEQ, ci.relname AS PK_NAME,   information_schema._pg_expandarray(i.indkey) AS KEYS, a.attnum AS A_ATTNUM, i.indnkeyatts as KEY_COUNT FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid)   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)   JOIN pg_catalog.pg_index i ON ( a.attrelid = i.indrelid)   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE $3  AND n.nspname = $1 AND ct.relname = $2 AND i.indisprimary  ) result where  result.A_ATTNUM = (result.KEYS).x AND result.KEY_SEQ <= KEY_COUNT  ORDER BY result.table_name, result.pk_name, result.key_seq

SELECT        result.TABLE_CAT,        result.TABLE_SCHEM,        result.TABLE_NAME,        result.COLUMN_NAME,        result.KEY_SEQ,        result.PK_NAME FROM      (SELECT $1 AS TABLE_CAT, n.nspname AS TABLE_SCHEM,   ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,   (information_schema._pg_expandarray(i.indkey)).n AS KEY_SEQ, ci.relname AS PK_NAME,   information_schema._pg_expandarray(i.indkey) AS KEYS, a.attnum AS A_ATTNUM FROM pg_catalog.pg_class ct   JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid)   JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid)   JOIN pg_catalog.pg_index i ON ( a.attrelid = i.indrelid)   JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE $2  AND n.nspname = $3 AND ct.relname = $4 AND i.indisprimary  ) result where  result.A_ATTNUM = (result.KEYS).x  ORDER BY result.table_name, result.pk_name, result.key_seq

SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/

with table_privileges as (
 select
   $1 as role,
   t.schemaname as schema,
   t.objectname as table,
   pg_catalog.has_any_column_privilege(current_user, $2 || replace(t.schemaname, $3, $4) || $5 || $6 || $7 || replace(t.objectname, $8, $9) || $10,  $11) as update,
   pg_catalog.has_any_column_privilege(current_user, $12 || replace(t.schemaname, $13, $14) || $15 || $16 || $17 || replace(t.objectname, $18, $19) || $20,  $21) as select,
   pg_catalog.has_any_column_privilege(current_user, $22 || replace(t.schemaname, $23, $24) || $25 || $26 || $27 || replace(t.objectname, $28, $29) || $30,  $31) as insert,
   pg_catalog.has_table_privilege(     current_user, $32 || replace(t.schemaname, $33, $34) || $35 || $36 || $37 || replace(t.objectname, $38, $39) || $40,  $41) as delete
 from (
   select schemaname, tablename as objectname from pg_catalog.pg_tables
   union
   select schemaname, viewname as objectname from pg_catalog.pg_views
   union
   select schemaname, matviewname as objectname from pg_catalog.pg_matviews
 ) t
 where t.schemaname !~ $42
   and t.schemaname <> $43
   and pg_catalog.has_schema_privilege(current_user, t.schemaname, $44)
)
select t.*
from table_privileges t

SELECT CASE WHEN $3 < LENGTH(CAST("public"."business_unit_partner_keys"."metadata" AS TEXT)) THEN $4 ELSE "public"."business_unit_partner_keys"."metadata" END AS "metadata" FROM "public"."business_unit_partner_keys" INNER JOIN ((SELECT "public"."business_unit_partner_keys"."id" FROM "public"."business_unit_partner_keys" ORDER BY "public"."business_unit_partner_keys"."id" ASC LIMIT $1) UNION ALL (SELECT "public"."business_unit_partner_keys"."id" FROM "public"."business_unit_partner_keys" ORDER BY "public"."business_unit_partner_keys"."id" DESC LIMIT $2)) AS "result" ON ("result"."id" = "public"."business_unit_partner_keys"."id")

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json((with __local_1__ as (select to_json((json_build_object($3::text, json_build_array(((__local_2__."id")::numeric)::text), $4::text, ((__local_2__."amount"))::text, $5::text, (__local_2__."authorization_code"), $6::text, (__local_2__."blocked_payer_reason"), $7::text, (__local_2__."brand"), $8::text, ((__local_2__."business_unit_id"))::text, $9::text, (__local_2__."comments"), $10::text, (__local_2__."due_date"), $11::text, (__local_2__."external_id"), $12::text, (__local_2__."installment_interest_type"), $13::text, ((__local_2__."id"))::text, $14::text, (__local_2__."installment_number"), $15::text, (__local_2__."installment_total"), $16::text, (__local_2__."internal_id"), $17::text, ((__local_2__."merchant_id"))::text, $18::text, (__local_2__."movement_date"), $19::text, (__local_2__."nsu"), $20::text, (__local_2__."order_code"), $21::text, (__local_2__."payer_tax_number"), $22::text, ((__local_2__."payment_partner_id"))::text, $23::text, (__local_2__."refunded_at"), $24::text, (__local_2__."refund_status"), $25::text, (__local_2__."status"), $26::text, (__local_2__."status_reason"), $27::text, (__local_2__."tags"), $28::text, (__local_2__."transaction_info"), $29::text, ((__local_2__."transaction_parent_id"))::text, $30::text, (__local_2__."created_at"), $31::text, (__local_2__."deleted_at"), $32::text, (__local_2__."updated_at"), $33::text, (__local_2__."merchant_payment_identifier"), $34::text, (__local_2__."transaction_token"), $35::text, (__local_2__."currency_code"), $36::text, (select json_build_object($37::text, json_build_array(((__local_3__."id")::numeric)::text), $38::text, (__local_3__."payment_method_name")) as object
from "public"."payment_methods" as __local_3__

where (__local_2__."payment_method_id" = __local_3__."id") and ($39) and ($40)


), $41::text, (__local_2__."capture_method"), $42::text, (select json_build_object($43::text, json_build_array(((__local_4__."id")::numeric)::text), $44::text, (__local_4__."merchant_token"), $45::text, (__local_4__."name")) as object
from "public"."merchants" as __local_4__

where (__local_2__."merchant_id" = __local_4__."id") and ($46) and ($47)


), $48::text, (select json_build_object($49::text, json_build_array(((__local_5__."id")::numeric)::text), $50::text, (__local_5__."business_unit_token")) as object
from "public"."merchant_business_units" as __local_5__

where (__local_2__."business_unit_id" = __local_5__."id") and ($51) and ($52)


), $53::text, (__local_2__."invoice_token"), $54::text, (__local_2__."metadata")))) as "@nodes" from (select __local_2__.*
from "public"."transactions" as __local_2__

where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $55::"pg_catalog"."int8" limit $56)))) and ($57) and ($58)
order by __local_2__."created_at" DESC,__local_2__."id" ASC
limit $59
offset $60) __local_2__), __local_6__ as (select json_agg(to_json(__local_1__)) as data from __local_1__) select json_build_object($61::text, coalesce((select __local_6__.data from __local_6__), $62::json), $63::text, (
  select json_build_object($64::text, count($65))
  from "public"."transactions" as __local_2__
  where (__local_2__."merchant_id" = __local_0__."id") and (__local_2__."business_unit_id" = $1) and (((__local_2__."id" NOT IN (select $66::"pg_catalog"."int8" limit $67))))
)) )) as "@allTransactions"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $2) and ($68) and ($69)

-- @see https://www.postgresql.org/docs/9.5/static/catalogs.html
-- @see https://github.com/graphile/graphile-engine/blob/master/packages/graphile-build-pg/src/plugins/introspectionQuery.js
--
-- ## Parameters
-- - `$1`: An array of strings that represent the namespaces we are introspecting.
-- - `$2`: set true to include functions/tables/etc that come from extensions
with
  recursive accessible_roles as (
    select oid _oid, pg_roles.*
    from pg_roles
    where rolname = current_user
    
    union all
      select pg_roles.oid _oid, pg_roles.*
      from pg_roles, accessible_roles, pg_auth_members
      where pg_auth_members.roleid = pg_roles.oid
        and pg_auth_members.member = accessible_roles._oid 
  
  ),
  -- @see https://www.postgresql.org/docs/9.5/static/catalog-pg-namespace.html
  namespace as (
    select
      $3 as "kind",
      nsp.oid as "id",
      nsp.nspname as "name",
      dsc.description as "description"
    from
      pg_catalog.pg_namespace as nsp
      left join pg_catalog.pg_description as dsc on dsc.objoid = nsp.oid and dsc.classoid = $4::regclass
    where
      nsp.nspname = any ($1)
    order by
      nsp.nspname
  ),
  -- Select all of the remote procedures we can use in this schema. This comes
  -- first so that we can select types and classes using the information we get
  -- from it.
  --
  -- @see https://www.postgresql.org/docs/9.6/static/catalog-pg-proc.html
  procedure as (
    select
      $5 as "kind",
      pro.oid as "id",
      pro.proname as "name",
      dsc.description as "description",
      pro.pronamespace as "namespaceId",
      nsp.nspname as "namespaceName",
      pro.proisstrict as "isStrict",
      pro.proretset as "returnsSet",
      case
        when pro.provolatile = $6 then $7
        when pro.provolatile = $8 then $9
        else $10
      end as "isStable",
      pro.prorettype as "returnTypeId",
      coalesce(pro.proallargtypes, pro.proargtypes) as "argTypeIds",
      coalesce(pro.proargmodes, array[]::text[]) as "argModes",
      coalesce(pro.proargnames, array[]::text[]) as "argNames",
      pro.pronargs as "inputArgsCount",
      pro.pronargdefaults as "argDefaultsNum",
      pro.procost as "cost",
      exists(select $11 from accessible_roles where has_function_privilege(accessible_roles.oid, pro.oid, $12)) as "aclExecutable",
      (select lanname from pg_catalog.pg_language where pg_language.oid = pro.prolang) as "language"
    from
      pg_catalog.pg_proc as pro
      left join pg_catalog.pg_description as dsc on dsc.objoid = pro.oid and dsc.classoid = $13::regclass
      left join pg_catalog.pg_namespace as nsp on nsp.oid = pro.pronamespace
    where
      pro.pronamespace in (select "id" from namespace) and
      -- Currently we don’t support functions with variadic arguments. In the
      -- future we may, but for now let’s just ignore functions with variadic
      -- arguments.
      -- TODO: Variadic arguments.
      pro.provariadic = $14 and
      -- Filter our aggregate functions and window functions.
      pro.prokind = $15 and
            -- We want to make sure the argument modes for all of our arguments are
      -- `IN`, `OUT`, `INOUT`, or `TABLE` (not `VARIADIC`).
      (pro.proargmodes is null or pro.proargmodes operator(pg_catalog.<@) array[$16,$17,$18,$19]::"char"[]) and
      -- Do not select procedures that return `RECORD` (oid 2249) unless they
      -- have `OUT`, `INOUT`, or `TABLE` arguments to define the return type.
      (pro.prorettype operator(pg_catalog.<>) $20 or pro.proargmodes && array[$21,$22,$23]::"char"[]) and
      -- Do not select procedures that have `RECORD` arguments.
      (pro.proallargtypes is null or not (pro.proallargtypes operator(pg_catalog.@>) array[$24::oid])) and
      -- Do not select procedures that create range types. These are utility
      -- functions that really don’t need to be exposed in an API.
      pro.proname not in (
        select typ.typname
        from pg_catalog.pg_type as typ
        where typ.typtype = $25
        and typ.typnamespace = pro.pronamespace
      ) and
      -- Do not expose trigger functions (type trigger has oid 2279)
      pro.prorettype operator(pg_catalog.<>) $26 and
      -- We don't want functions that will clash with GraphQL (treat them as private)
      pro.proname not like $27 and
      -- We also don’t want procedures that have been defined in our namespace
      -- twice. This leads to duplicate fields in the API which throws an
      -- error. In the future we may support this case. For now though, it is
      -- too complex.
      (
        select count(pro2.*)
        from pg_catalog.pg_proc as pro2
        where pro2.pronamespace = pro.pronamespace
        and pro2.proname = pro.proname
      ) = $28 and
      ($2 is true or not exists(
        select $29
        from pg_catalog.pg_depend
        where pg_depend.refclassid = $30::pg_catalog.regclass
        and pg_depend.deptype = $31
        and pg_depend.classid = $32::pg_catalog.regclass
        and pg_depend.objid = pro.oid
      ))
    order by
      pro.pronamespace, pro.proname
  ),
  -- @see https://www.postgresql.org/docs/9.5/static/catalog-pg-class.html
  class as (
    select
      $33 as "kind",
      rel.oid as "id",
      rel.relname as "name",
      rel.relkind as "classKind",
      dsc.description as "description",
      rel.relnamespace as "namespaceId",
      nsp.nspname as "namespaceName",
      rel.reltype as "typeId",
      -- Here we determine whether or not we can use this class in a
      -- `SELECT`’s `FROM` clause. In order to determine this we look at them
      -- `relkind` column, if it is `i` (index) or `c` (composite), we cannot
      -- select this class. Otherwise we can.
      rel.relkind not in ($34, $35) as "isSelectable",
      -- Here we are determining whether we can insert/update/delete a class.
      -- This is helpful as it lets us detect non-updatable views and then
      -- exclude them from being inserted/updated/deleted into. For more info
      -- on how `pg_catalog.pg_relation_is_updatable` works:
      --
      -- - https://www.postgresql.org/message-id/CAEZATCV2_qN9P3zbvADwME_TkYf2gR_X2cLQR4R+pqkwxGxqJg@mail.gmail.com
      -- - https://github.com/postgres/postgres/blob/2410a2543e77983dab1f63f48b2adcd23dba994e/src/backend/utils/adt/misc.c#L684
      -- - https://github.com/postgres/postgres/blob/3aff33aa687e47d52f453892498b30ac98a296af/src/backend/rewrite/rewriteHandler.c#L2351
      (pg_catalog.pg_relation_is_updatable(rel.oid, $36)::bit(8) operator(pg_catalog.&) $37) = $38 as "isDeletable",
      (pg_catalog.pg_relation_is_updatable(rel.oid, $39)::bit(8) operator(pg_catalog.&) $40) = $41 as "isInsertable",
      (pg_catalog.pg_relation_is_updatable(rel.oid, $42)::bit(8) operator(pg_catalog.&) $43) = $44 as "isUpdatable",
      exists(select $45 from accessible_roles where has_table_privilege(accessible_roles.oid, rel.oid, $46)) as "aclSelectable",
      exists(select $47 from accessible_roles where has_table_privilege(accessible_roles.oid, rel.oid, $48)) as "aclInsertable",
      exists(select $49 from accessible_roles where has_table_privilege(accessible_roles.oid, rel.oid, $50)) as "aclUpdatable",
      exists(select $51 from accessible_roles where has_table_privilege(accessible_roles.oid, rel.oid, $52)) as "aclDeletable"
    from
      pg_catalog.pg_class as rel
      left join pg_catalog.pg_description as dsc on dsc.objoid = rel.oid and dsc.objsubid = $53 and dsc.classoid = $54::regclass
      left join pg_catalog.pg_namespace as nsp on nsp.oid = rel.relnamespace
    where
      rel.relpersistence in ($55) and
      -- We don't want classes that will clash with GraphQL (treat them as private)
      rel.relname not like $56 and
      rel.relkind in ($57, $58, $59, $60, $61) and
      ($2 is true or not exists(
        select $62
        from pg_catalog.pg_depend
        where pg_depend.refclassid = $63::pg_catalog.regclass
        and pg_depend.deptype = $64
        and pg_depend.classid = $65::pg_catalog.regclass
        and pg_depend.objid = rel.oid
      ))
    order by
      rel.relnamespace, rel.relname
  ),
  -- @see https://www.postgresql.org/docs/9.5/static/catalog-pg-attribute.html
  attribute as (
    select
      $66 as "kind",
      att.attrelid as "classId",
      att.attnum as "num",
      att.attname as "name",
      dsc.description as "description",
      att.atttypid as "typeId",
      nullif(att.atttypmod, $67) as "typeModifier",
      att.attnotnull as "isNotNull",
      att.atthasdef as "hasDefault",
      att.attidentity as "identity",
      exists(select $68 from accessible_roles where has_column_privilege(accessible_roles.oid, att.attrelid, att.attname, $69)) as "aclSelectable",
      exists(select $70 from accessible_roles where has_column_privilege(accessible_roles.oid, att.attrelid, att.attname, $71)) as "aclInsertable",
      exists(select $72 from accessible_roles where has_column_privilege(accessible_roles.oid, att.attrelid, att.attname, $73)) as "aclUpdatable",
      -- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c62dd80cdf149e2792b13c13777a539f5abb0370
      att.attacl is not null and exists(select $74 from aclexplode(att.attacl) aclitem where aclitem.privilege_type = $75 and grantee in (select oid from accessible_roles)) as "columnLevelSelectGrant"
    from
      pg_catalog.pg_attribute as att
      left join pg_catalog.pg_description as dsc on dsc.objoid = att.attrelid and dsc.objsubid = att.attnum and dsc.classoid = $76::regclass
    where
      att.attrelid in (select "id" from class) and
      att.attnum > $77 and
      -- We don't want attributes that will clash with GraphQL (treat them as private)
      att.attname not like $78 and
      not att.attisdropped
    order by
      att.attrelid, att.attnum
  ),
  -- @see https://www.postgresql.org/docs/9.5/static/catalog-pg-type.html
  type as (
    -- Use another `WITH` statement here, because our `WHERE` clause will need
    -- to

SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, $1), t.typtypmod) as base_type_name, d.description
FROM pg_catalog.pg_type t
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem 
LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid
LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid
WHERE t.typname IS NOT NULL
AND (c.relkind IS NULL OR c.relkind = $2) AND (et.typcategory IS NULL OR et.typcategory <> $3)

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($5::text, coalesce((select json_agg(j.data) from (
  select json_build_object($6::text, (json_build_object($7::text, sum($8), $9::text, (json_build_object($10::text, coalesce(sum(__local_1__."amount"), $11))))), $12::text, json_build_array((__local_1__."status")::text, (date_trunc($13, __local_1__."created_at"))::text)) as data
  from "public"."transactions" as __local_1__
  where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."created_at" <= $2) and (__local_1__."created_at" >= $3)))
  group by __local_1__."status", date_trunc($14, __local_1__."created_at")
  
) j), $15::json)) )) as "@transactionsByStatus"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $4) and ($16) and ($17)

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($9::text, (
  select json_build_object($10::text, (json_build_object($11::text, sum($12), $13::text, (json_build_object($14::text, coalesce(sum(__local_1__."amount"), $15))), $16::text, (json_build_object($17::text, avg(__local_1__."amount"))), $18::text, (json_build_object($19::text, count(distinct __local_1__."id"))))))
  from "public"."transactions" as __local_1__
  where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."status" NOT IN ($2,$3,$4,$5))) and ((__local_1__."created_at" <= $6) and (__local_1__."created_at" >= $7)))
)) )) as "@totalRevenue"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $8) and ($20) and ($21)

select bu1_0.merchant_id,bu1_0.id,a1_0.id,a1_0.address_line,a1_0.address_line_2,a1_0.city,a1_0.city_code,a1_0.complement,a1_0.country,a1_0.created_at,a1_0.deleted_at,a1_0.federative_unit,a1_0.neighborhood,a1_0.reference,a1_0.state,a1_0.updated_at,a1_0.zip_code,bu1_0.branch,bu1_0.business_unit_token,bu1_0.created_at,bu1_0.deleted_at,bu1_0.division,bu1_0.mcc,bu1_0.name,bu1_0.private_key,bu1_0.short_description,bu1_0.status,bu1_0.status_reason,bu1_0.tax_number,bu1_0.updated_at from merchant_business_units bu1_0 left join addresses a1_0 on a1_0.id=bu1_0.address_id where bu1_0.merchant_id=$1

SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/

with onboarding as (
	select * 
	from onboarding_merchants
	where onboarding_token = $1
)
select *
from (
	SELECT 
		evt.*, ROW_NUMBER() OVER (PARTITION BY evt.step ORDER BY evt.created_at DESC) AS row_num
    FROM onboarding 
    inner join public.onboarding_merchant_events evt on onboarding.id = evt.onboarding_id 
    order by evt.created_at desc
) last_list 
where last_list.row_num = $2

select to_json(json_build_array(((__local_0__."id")::numeric)::text)) as "__identifiers", to_json(( select json_build_object($5::text, (
  select json_build_object($6::text, (json_build_object($7::text, sum($8), $9::text, (json_build_object($10::text, coalesce(sum(__local_1__."amount"), $11))), $12::text, (json_build_object($13::text, avg(__local_1__."amount"))), $14::text, (json_build_object($15::text, count(distinct __local_1__."id"))))))
  from "public"."transactions" as __local_1__
  where (__local_1__."merchant_id" = __local_0__."id") and (__local_1__."business_unit_id" = $1) and (((__local_1__."created_at" <= $2) and (__local_1__."created_at" >= $3)))
)) )) as "@totalRevenue"
from "public"."merchants" as __local_0__

where (__local_0__."merchant_token" = $4) and ($16) and ($17)

-- Metabase
SELECT "public"."transactions"."status" AS "status" FROM "public"."transactions" GROUP BY "public"."transactions"."status" ORDER BY "public"."transactions"."status" ASC LIMIT $1

CREATE INDEX CONCURRENTLY ON transactions (status)

Rows: 22438
Row progression: 22438, 2493

Row estimates
- status (sort): 2493

Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE

SELECT "n"."nspname" AS "schema", "c"."relname" AS "name", CASE "c"."relkind" WHEN $6 THEN $7 WHEN $8 THEN $9 WHEN $10 THEN $11 WHEN $12 THEN $13 WHEN $14 THEN $15 ELSE $16 END AS "type", "d"."description" AS "description", "stat"."n_live_tup" AS "estimated_row_count" FROM "pg_catalog"."pg_class" AS "c" INNER JOIN "pg_catalog"."pg_namespace" AS "n" ON "c"."relnamespace" = "n"."oid" LEFT JOIN "pg_catalog"."pg_description" AS "d" ON ("c"."oid" = "d"."objoid") AND ("d"."objsubid" = $1) AND ("d"."classoid" = $17::regclass) LEFT JOIN "pg_stat_user_tables" AS "stat" ON ("n"."nspname" = "stat"."schemaname") AND ("c"."relname" = "stat"."relname") WHERE ("c"."relnamespace" = "n"."oid") AND ("n"."nspname" !~ $2) AND ("n"."nspname" <> $3) AND c.relkind in ($18, $19, $20, $21, $22) AND ("n"."nspname" IN ($4, $5)) ORDER BY "type" ASC, "schema" ASC, "name" ASC

select me1_0.id,a1_0.id,a1_0.address_line,a1_0.address_line_2,a1_0.city,a1_0.city_code,a1_0.complement,a1_0.country,a1_0.created_at,a1_0.deleted_at,a1_0.federative_unit,a1_0.neighborhood,a1_0.reference,a1_0.state,a1_0.updated_at,a1_0.zip_code,me1_0.created_at,me1_0.deleted_at,me1_0.merchant_token,me1_0.name,me1_0.status,me1_0.status_reason,me1_0.tax_number,me1_0.updated_at,bu1_0.merchant_id,bu1_0.id,a2_0.id,a2_0.address_line,a2_0.address_line_2,a2_0.city,a2_0.city_code,a2_0.complement,a2_0.country,a2_0.created_at,a2_0.deleted_at,a2_0.federative_unit,a2_0.neighborhood,a2_0.reference,a2_0.state,a2_0.updated_at,a2_0.zip_code,bu1_0.branch,bu1_0.business_unit_token,bu1_0.created_at,bu1_0.deleted_at,bu1_0.division,bu1_0.mcc,bu1_0.name,bu1_0.private_key,bu1_0.short_description,bu1_0.status,bu1_0.status_reason,bu1_0.tax_number,bu1_0.updated_at from merchants me1_0 left join addresses a1_0 on a1_0.id=me1_0.address_id left join merchant_business_units bu1_0 on me1_0.id=bu1_0.merchant_id left join addresses a2_0 on a2_0.id=bu1_0.address_id where me1_0.id=$1

SELECT "fk_ns"."nspname" AS "fk-table-schema", "fk_table"."relname" AS "fk-table-name", "fk_column"."attname" AS "fk-column-name", "pk_ns"."nspname" AS "pk-table-schema", "pk_table"."relname" AS "pk-table-name", "pk_column"."attname" AS "pk-column-name" FROM "pg_constraint" AS "c" INNER JOIN "pg_class" AS "fk_table" ON "c"."conrelid" = "fk_table"."oid" INNER JOIN "pg_namespace" AS "fk_ns" ON "c"."connamespace" = "fk_ns"."oid" INNER JOIN "pg_attribute" AS "fk_column" ON "c"."conrelid" = "fk_column"."attrelid" INNER JOIN "pg_class" AS "pk_table" ON "c"."confrelid" = "pk_table"."oid" INNER JOIN "pg_namespace" AS "pk_ns" ON "pk_table"."relnamespace" = "pk_ns"."oid" INNER JOIN "pg_attribute" AS "pk_column" ON "c"."confrelid" = "pk_column"."attrelid" WHERE fk_ns.nspname !~ $2 AND ("c"."contype" = $3::char) AND ("fk_column"."attnum" = ANY(c.conkey)) AND ("pk_column"."attnum" = ANY(c.confkey)) AND ("fk_ns"."nspname" IN ($1)) ORDER BY "fk-table-schema" ASC, "fk-table-name" ASC

-- Metabase:: userID: 13 queryType: MBQL queryHash: a921ad42ea6db70e2a3334d8d040d7f3d618ae3140477afaddb13e9e2cc1fb64
SELECT "source"."merchant_name" AS "merchant_name", "source"."business_unit_name" AS "business_unit_name", "source"."transaction_token" AS "transaction_token", "source"."payment_method" AS "payment_method", "source"."card_type" AS "card_type", "source"."amount" AS "amount", "source"."paid_amount" AS "paid_amount", "source"."status" AS "status", "source"."status_reason" AS "status_reason", "source"."created_at" AS "created_at", "source"."card_brand" AS "card_brand", "source"."card_pan" AS "card_pan" FROM (SELECT 
  m.name AS merchant_name,
  mbu.name AS business_unit_name,
  t.transaction_token,
  pm.payment_method_name AS payment_method,
  CASE
    WHEN LOWER(pm.payment_method_name) LIKE $1 THEN $2
    WHEN LOWER(pm.payment_method_name) LIKE $3 THEN $4
    ELSE $5
  END AS card_type,
  t.amount,
  t.paid_amount,
  t.status,
  t.status_reason,
  t.created_at,
  t.card_brand,
  t.card_pan
FROM 
  transactions t
JOIN 
  payment_methods pm 
    ON t.payment_method_id = pm.id
JOIN
  merchants m
    ON t.merchant_id = m.id
JOIN
  merchant_business_units mbu
    ON t.business_unit_id = mbu.id
WHERE 
  t.merchant_id = $6) AS "source" LIMIT $7

insert into transactions (amount,authorization_code,blocked_payer_reason,brand,business_unit_id,canceled_reason,capture_method,card_brand,card_expiration_month,card_expiration_year,card_pan,comments,created_at,currency_code,deleted_at,due_date,external_id,fee_total_amount,has_payer,installment_interest_type,installment_number,installment_total,internal_id,invoice_token,merchant_id,merchant_payment_id,merchant_payment_identifier,metadata,movement_date,nsu,paid_amount,partner_tracking_id,partner_transaction_id,payer_tax_number,payment_method_id,payment_partner_id,refund_status,refunded_amount,refunded_at,session_key,status,status_reason,tags,transaction_info,transaction_token,transaction_type,updated_at) values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47) returning id

-- Metabase
SELECT "public"."transactions"."internal_id" AS "internal_id" FROM "public"."transactions" GROUP BY "public"."transactions"."internal_id" ORDER BY "public"."transactions"."internal_id" ASC LIMIT $1

CREATE INDEX CONCURRENTLY ON transactions (internal_id)

Rows: 22438
Row progression: 22438, 0

Row estimates
- internal_id (sort): 0

Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40)

-- Metabase:: userID: 1 queryType: native queryHash: e9377ec08c8634cd498247eec8b4b9f50059c1452d18da0009eda8f120a484b8
with selected_date as (
    select cast(cast( now() 
        as date) as timestamp) as ANALYSIS_DATE,
        TO_CHAR(cast( now() 
        as date), $1) as ANALYSIS_DATE_VW
),
exec_date as (
    select ANALYSIS_DATE, ANALYSIS_DATE_VW, ANALYSIS_DATE start_date, cast(ANALYSIS_DATE + INTERVAL $2 as timestamp) end_date
        --cast(ANALYSIS_DATE - INTERVAL '2 days' as timestamp) start_date, cast((ANALYSIS_DATE - INTERVAL '1 day') + INTERVAL '23:59:59' as timestamp) end_date
    from selected_date
),
merchants_with_transactions as (
    select distinct ANALYSIS_DATE, ANALYSIS_DATE_VW, start_date, end_date,
        tr.merchant_id,
        tr.business_unit_id,
        merchants.name AS merchant_name,
        merchants.status AS merchant_status,
        merchants.status_reason AS merchant_status_reason,
        merchants.tax_number,
        bu.name AS bu_name,
        bu.branch,
        bu.division,
        bu.mcc,
        bu.status AS bu_status,
        bu.status_reason AS bu_status_reason,
        bu.tax_number AS bu_tax_number
    from exec_date ed
    inner join transactions tr on tr.created_at between ed.start_date and ed.end_date
    INNER JOIN merchants ON merchants.id = tr.merchant_id
    LEFT JOIN merchant_business_units bu ON bu.merchant_id = merchants.id 
                                        AND bu.id = tr.business_unit_id 
),
analysis_data as (
    SELECT 
        start_date, end_date,
        flt_merchants.ANALYSIS_DATE_VW,
        flt_merchants.start_date,
        flt_merchants.end_date,
        flt_merchants.merchant_id,
        flt_merchants.business_unit_id,
        flt_merchants.merchant_name,
        flt_merchants.merchant_status,
        flt_merchants.merchant_status_reason,
        flt_merchants.tax_number,
        flt_merchants.bu_name,
        flt_merchants.branch,
        flt_merchants.division,
        flt_merchants.mcc,
        flt_merchants.bu_status,
        flt_merchants.bu_status_reason,
        flt_merchants.bu_tax_number,
        -- Média e contagem das transações
        COUNT(tr.amount) AS count_transaction_day,
        sum(tr.amount) AS sum_amount_day,
        MIN(tr.amount) AS min_amount_day,
        MAX(tr.amount) AS max_amount_day,
        AVG(tr.amount) AS avg_amount_day,
        -- Desvio Padrão Transacional do dia
        STDDEV_POP(tr.amount) AS stddev_transaction_day,
        
            -- Contagem e média para os últimos 30, 60, 90 dias
        -- 30 dias
        (SELECT COUNT(*) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $3) AND end_date
        ) AS count_30_days,
        
        (SELECT AVG(tr30.amount) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $4) AND end_date
        ) AS avg_30_days,
        
        (SELECT STDDEV_POP(tr30.amount) 
         FROM transactions tr30
         WHERE tr30.merchant_id = flt_merchants.merchant_id
           AND tr30.business_unit_id = flt_merchants.business_unit_id
           AND tr30.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $5) AND end_date
        ) AS stddev_30_days,
        
        -- 60 dias
        (SELECT COUNT(*) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $6) AND end_date
        ) AS count_60_days,
        
        (SELECT AVG(tr60.amount) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $7) AND end_date
        ) AS avg_60_days,
        
        (SELECT STDDEV_POP(tr60.amount) 
         FROM transactions tr60
         WHERE tr60.merchant_id = flt_merchants.merchant_id
           AND tr60.business_unit_id = flt_merchants.business_unit_id
           AND tr60.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $8) AND end_date
        ) AS stddev_60_days,
        
        -- 90 dias
        (SELECT COUNT(*) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $9) AND end_date
        ) AS count_90_days,
        
        (SELECT AVG(tr90.amount) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $10) AND end_date
        ) AS avg_90_days,
        
        (SELECT STDDEV_POP(tr90.amount) 
         FROM transactions tr90
         WHERE tr90.merchant_id = flt_merchants.merchant_id
           AND tr90.business_unit_id = flt_merchants.business_unit_id
           AND tr90.created_at BETWEEN (ANALYSIS_DATE - INTERVAL $11) AND end_date
        ) AS stddev_90_days
        
    FROM merchants_with_transactions flt_merchants
    inner join transactions tr on tr.created_at between flt_merchants.start_date and flt_merchants.end_date
    GROUP BY 
        start_date, end_date,
        flt_merchants.ANALYSIS_DATE_VW,
        flt_merchants.ANALYSIS_DATE,
        flt_merchants.start_date,
        flt_merchants.end_date,
        flt_merchants.merchant_id,
        flt_merchants.business_unit_id,
        flt_merchants.merchant_name,
        flt_merchants.merchant_status,
        flt_merchants.merchant_status_reason,
        flt_merchants.tax_number,
        flt_merchants.bu_name,
        flt_merchants.branch,
        flt_merchants.division,
        flt_merchants.mcc,
        flt_merchants.bu_status,
        flt_merchants.bu_status_reason,
        flt_merchants.bu_tax_number
    ORDER BY 1 DESC
),
score as (
    select *,
        -- Normalizando o Z-Score para o intervalo de 0 a 1 usando a fórmula de sigmoid
        ($12 / ($13 + EXP(-(avg_amount_day - avg_amount_day) / case when stddev_transaction_day <= $14 then $15 else stddev_transaction_day end))) AS weight_current_day,
        (CASE 
            WHEN avg_30_days IS NOT NULL AND stddev_30_days > $16 THEN 
                $17 / ($18 + EXP(-(avg_amount_day - avg_30_days) / stddev_30_days))
            ELSE $19 
        END) AS weight_30_days,
        
        (CASE 
            WHEN avg_60_days IS NOT NULL AND stddev_60_days > $20 THEN 
                $21 / ($22 + EXP(-(avg_amount_day - avg_60_days) / stddev_60_days))
            ELSE $23 
        END) AS weight_60_days,
        
        (CASE 
            WHEN avg_90_days IS NOT NULL AND stddev_90_days > $24 THEN 
                $25 / ($26 + EXP(-(avg_amount_day - avg_90_days) / stddev_90_days))
            ELSE $27 
        END) AS weight_90_days
    from analysis_data
),
monitoring as (
select
    *,
    -- Flag de Anomalia: com base no peso normalizado
    (CASE 
        WHEN weight_30_days >= $28 OR weight_60_days >= $29 OR weight_90_days >= $30 THEN $31 -- 'Alert'
        WHEN weight_30_days >= $32 OR weight_60_days >= $33 OR weight_90_days >= $34 THEN $35 -- 'Monitoring'
        ELSE weight_current_day -- 'Normal'
    END) AS risk_score
from score
)
select * ,
    -- Flag de Anomalia: com base no peso normalizado
    (CASE 
        WHEN risk_score >= $36 THEN $37
        WHEN risk_score >= $38 THEN $39
        ELSE $40
    END) AS anomaly_flag
from monitoring
where $41=$42
and risk_score >= (case  $43
					when $44 then $45
					else $46 end )

SELECT * FROM "transactions" WHERE transaction_token = $1 AND "transactions"."deleted_at" IS NULL ORDER BY "transactions"."id" LIMIT $2

Covered by index on (transaction_token)
Rows: 22438
Row progression: 22438, 1

Row estimates
- transaction_token (=): 1
- deleted_at (null): 22438
- id (sort): 1

Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE

select string_agg(word, $1) from pg_catalog.pg_get_keywords() where word <> ALL ($2::text[])

select mbue1_0.id,a1_0.id,a1_0.address_line,a1_0.address_line_2,a1_0.city,a1_0.city_code,a1_0.complement,a1_0.country,a1_0.created_at,a1_0.deleted_at,a1_0.federative_unit,a1_0.neighborhood,a1_0.reference,a1_0.state,a1_0.updated_at,a1_0.zip_code,mbue1_0.branch,mbue1_0.business_unit_token,mbue1_0.created_at,mbue1_0.deleted_at,mbue1_0.division,mbue1_0.mcc,mbue1_0.merchant_id,m1_0.id,a2_0.id,a2_0.address_line,a2_0.address_line_2,a2_0.city,a2_0.city_code,a2_0.complement,a2_0.country,a2_0.created_at,a2_0.deleted_at,a2_0.federative_unit,a2_0.neighborhood,a2_0.reference,a2_0.state,a2_0.updated_at,a2_0.zip_code,m1_0.created_at,m1_0.deleted_at,m1_0.merchant_token,m1_0.name,m1_0.status,m1_0.status_reason,m1_0.tax_number,m1_0.updated_at,mbue1_0.name,mbue1_0.private_key,mbue1_0.short_description,mbue1_0.status,mbue1_0.status_reason,mbue1_0.tax_number,mbue1_0.updated_at from merchant_business_units mbue1_0 left join addresses a1_0 on a1_0.id=mbue1_0.address_id join merchants m1_0 on m1_0.id=mbue1_0.merchant_id left join addresses a2_0 on a2_0.id=m1_0.address_id where mbue1_0.id=$1

select  * from public.onboarding_merchant_events where onboarding_id = $1

SELECT c.relname,a.*,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, $2) as def_value,dsc.description,dep.objid
FROM pg_catalog.pg_attribute a
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum)
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)
LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = $3 and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid
WHERE NOT a.attisdropped AND c.relkind not in ($4,$5,$6) AND c.oid=$1
ORDER BY a.attnum

-- Metabase:: userID: 1 queryType: native queryHash: 39d0dc6803b375518c0bc78b292e6c7c8f79ea2feda682f16f215efc50aaadc6
select distinct to_char(coalesce(tr.updated_at, tr.created_at), $1) date, sum(amount) amount, AVG(amount) avg_amount, count($2) qty, status
from transactions tr 
where coalesce(tr.updated_at, tr.created_at) >= (current_date - INTERVAL $3)
group by to_char(coalesce(tr.updated_at, tr.created_at), 'YYYY-MM-DD'), tr.status
order by 1 desc, status

select ae1_0.id,ae1_0.address_line,ae1_0.address_line_2,ae1_0.city,ae1_0.city_code,ae1_0.complement,ae1_0.country,ae1_0.created_at,ae1_0.deleted_at,ae1_0.federative_unit,ae1_0.neighborhood,ae1_0.reference,ae1_0.state,ae1_0.updated_at,ae1_0.zip_code from addresses ae1_0 where ae1_0.id=$1

SELECT * FROM "merchants" WHERE merchant_token = $1 AND "merchants"."deleted_at" IS NULL ORDER BY "merchants"."id" LIMIT $2

SELECT c.oid,c.*,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key 
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=$2 AND d.classoid=$3::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ($4,$5,$6)

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Metabase:: userID: 1 queryType: MBQL queryHash: e14d38bbb76e45eb43a89e6f1c5783d400d428e98f79c00fc8e6a8fc358ea339
SELECT DATE_TRUNC($1, "public"."transactions"."created_at") AS "created_at", SUM("public"."transactions"."amount") AS "sum" FROM "public"."transactions" WHERE ("public"."transactions"."created_at" >= DATE_TRUNC($2, (NOW() + INTERVAL $3))) AND ("public"."transactions"."created_at" < DATE_TRUNC($4, (NOW() + INTERVAL $5))) AND (("public"."transactions"."merchant_id" <> $6) OR ("public"."transactions"."merchant_id" IS NULL)) GROUP BY DATE_TRUNC('quarter', "public"."transactions"."created_at") ORDER BY DATE_TRUNC('quarter', "public"."transactions"."created_at") ASC

SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = $1 AND t.typnotnull), a.attidentity != $2 OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE $3 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT $4 AS oid , $5 AS attnum UNION ALL SELECT $6, $7 UNION ALL SELECT $8, $9 UNION ALL SELECT $10, $11) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) where c.oid in ($12)

-- Metabase:: userID: 1 queryType: MBQL queryHash: 7cd34e5073eabc629d8c4b2b1817d22ef26a01a8310b677f57679d5b58b99ae3
SELECT DATE_TRUNC($1, "public"."transactions"."created_at") AS "created_at", SUM("public"."transactions"."amount") AS "sum", COUNT(*) AS "count" FROM "public"."transactions" WHERE ("public"."transactions"."created_at" >= DATE_TRUNC($2, (NOW() + INTERVAL $3))) AND ("public"."transactions"."created_at" < DATE_TRUNC($4, (NOW() + INTERVAL $5))) AND (("public"."transactions"."merchant_id" <> $6) OR ("public"."transactions"."merchant_id" IS NULL)) GROUP BY DATE_TRUNC('month', "public"."transactions"."created_at") ORDER BY DATE_TRUNC('month', "public"."transactions"."created_at") ASC

-- Metabase:: userID: 1 queryType: MBQL queryHash: c21b0bd1511c5d252ac2ae7e241622f833420c465af9d7df84600afb34d9cba3
SELECT DATE_TRUNC($1, "public"."transactions"."created_at") AS "created_at", "payment_methods__via__payment_method_id"."payment_method_name" AS "payment_methods__via__payment_method_id__payment_method_name", SUM("public"."transactions"."amount") AS "sum" FROM "public"."transactions" LEFT JOIN "public"."payment_methods" AS "payment_methods__via__payment_method_id" ON "public"."transactions"."payment_method_id" = "payment_methods__via__payment_method_id"."id" WHERE ("public"."transactions"."created_at" >= DATE_TRUNC($2, (NOW() + INTERVAL $3))) AND ("public"."transactions"."created_at" < DATE_TRUNC($4, (NOW() + INTERVAL $5))) AND (("public"."transactions"."merchant_id" <> $6) OR ("public"."transactions"."merchant_id" IS NULL)) GROUP BY DATE_TRUNC('month', "public"."transactions"."created_at"), "payment_methods__via__payment_method_id"."payment_method_name" ORDER BY DATE_TRUNC('month', "public"."transactions"."created_at") ASC, "payment_methods__via__payment_method_id"."payment_method_name" ASC

SELECT CASE WHEN $3 < LENGTH(CAST("public"."business_unit_payer_events"."metadata" AS TEXT)) THEN $4 ELSE "public"."business_unit_payer_events"."metadata" END AS "metadata" FROM "public"."business_unit_payer_events" INNER JOIN ((SELECT "public"."business_unit_payer_events"."id" FROM "public"."business_unit_payer_events" ORDER BY "public"."business_unit_payer_events"."id" ASC LIMIT $1) UNION ALL (SELECT "public"."business_unit_payer_events"."id" FROM "public"."business_unit_payer_events" ORDER BY "public"."business_unit_payer_events"."id" DESC LIMIT $2)) AS "result" ON ("result"."id" = "public"."business_unit_payer_events"."id")

SELECT "tmp"."table-schema", "tmp"."table-name", TRIM(BOTH $2 FROM PG_CATALOG.PG_GET_INDEXDEF("tmp"."ci_oid", "tmp"."pos", $3)) AS "field-name" FROM (SELECT "n"."nspname" AS "table-schema", "ct"."relname" AS "table-name", "ci"."oid" AS "ci_oid", (INFORMATION_SCHEMA._PG_EXPANDARRAY("i"."indkey"))."n" AS "pos" FROM "pg_catalog"."pg_class" AS "ct" INNER JOIN "pg_catalog"."pg_namespace" AS "n" ON "ct"."relnamespace" = "n"."oid" INNER JOIN "pg_catalog"."pg_index" AS "i" ON "ct"."oid" = "i"."indrelid" INNER JOIN "pg_catalog"."pg_class" AS "ci" ON "ci"."oid" = "i"."indexrelid" WHERE (PG_CATALOG.PG_GET_EXPR("i"."indpred", "i"."indrelid") IS NULL) AND n.nspname !~ $4) AS "tmp" WHERE "tmp"."pos" = $1

-- Metabase:: userID: 1 queryType: MBQL queryHash: 773355c3fa7725b84a6b794c3c3d3e842f671d8fc1e1928b3e23882a81d4d113
SELECT "payment_methods__via__payment_method_id"."payment_method_name" AS "payment_methods__via__payment_method_id__payment_method_name", DATE_TRUNC($1, "public"."transactions"."created_at") AS "created_at", SUM("public"."transactions"."amount") AS "sum" FROM "public"."transactions" LEFT JOIN "public"."payment_methods" AS "payment_methods__via__payment_method_id" ON "public"."transactions"."payment_method_id" = "payment_methods__via__payment_method_id"."id" WHERE ("public"."transactions"."created_at" >= DATE_TRUNC($2, (NOW() + INTERVAL $3))) AND ("public"."transactions"."created_at" < DATE_TRUNC($4, (NOW() + INTERVAL $5))) AND (("public"."transactions"."merchant_id" <> $6) OR ("public"."transactions"."merchant_id" IS NULL)) GROUP BY "payment_methods__via__payment_method_id"."payment_method_name", DATE_TRUNC('quarter', "public"."transactions"."created_at") ORDER BY "payment_methods__via__payment_method_id"."payment_method_name" ASC, DATE_TRUNC('quarter', "public"."transactions"."created_at") ASC

-- Metabase:: userID: 1 queryType: MBQL queryHash: 288936d50d907dc2efee96d72fe42dac857a86c84f58d35cf106fc76171c6ebf
SELECT DATE_TRUNC($1, "public"."transactions"."created_at") AS "created_at", COUNT(*) AS "count" FROM "public"."transactions" WHERE ("public"."transactions"."created_at" >= DATE_TRUNC($2, (NOW() + INTERVAL $3))) AND ("public"."transactions"."created_at" < DATE_TRUNC($4, (NOW() + INTERVAL $5))) AND (("public"."transactions"."merchant_id" <> $6) OR ("public"."transactions"."merchant_id" IS NULL)) GROUP BY DATE_TRUNC('quarter', "public"."transactions"."created_at") ORDER BY DATE_TRUNC('quarter', "public"."transactions"."created_at") ASC

-- Metabase:: userID: 1 queryType: native queryHash: 2d780206f50c015a736f457a20809234f476593d478e54ff63312d6e8840e384
select distinct to_char(tr.created_at, $1) date, sum(amount) amount, AVG(amount) avg_amount, count($2) qty, status
from transactions tr 
where tr.created_at >= (current_date - INTERVAL $3)
group by to_char(tr.created_at, 'YYYY-MM-DD'), tr.status
order by 1 desc, status

CREATE INDEX CONCURRENTLY ON transactions (created_at)

Rows: 22438
Row progression: 22438, 2244

Row estimates
- created_at (>=): 2244

Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE

SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = $1 AND t.typnotnull), a.attidentity != $2 OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE $3 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT $4 AS oid , $5 AS attnum UNION ALL SELECT $6, $7 UNION ALL SELECT $8, $9 UNION ALL SELECT $10, $11 UNION ALL SELECT $12, $13 UNION ALL SELECT $14, $15 UNION ALL SELECT $16, $17 UNION ALL SELECT $18, $19 UNION ALL SELECT $20, $21 UNION ALL SELECT $22, $23 UNION ALL SELECT $24, $25 UNION ALL SELECT $26, $27 UNION ALL SELECT $28, $29) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) where c.oid in ($30,$31,$32)

-- Metabase:: userID: 1 queryType: MBQL queryHash: fe04ceba37a8e68edacc182816b576721622645f247bb9978ab48d06825337c0
SELECT DATE_TRUNC($1, "public"."transactions"."created_at") AS "created_at", SUM("public"."transactions"."fee_total_amount") AS "sum" FROM "public"."transactions" WHERE ("public"."transactions"."created_at" >= DATE_TRUNC($2, (NOW() + INTERVAL $3))) AND ("public"."transactions"."created_at" < DATE_TRUNC($4, (NOW() + INTERVAL $5))) AND (("public"."transactions"."merchant_id" <> $6) OR ("public"."transactions"."merchant_id" IS NULL)) GROUP BY DATE_TRUNC('quarter', "public"."transactions"."created_at") ORDER BY DATE_TRUNC('quarter', "public"."transactions"."created_at") ASC

SET TimeZone='America/Sao_Paulo'

select mbue1_0.id,mbue1_0.address_id,mbue1_0.branch,mbue1_0.business_unit_token,mbue1_0.created_at,mbue1_0.deleted_at,mbue1_0.division,mbue1_0.mcc,mbue1_0.merchant_id,mbue1_0.name,mbue1_0.private_key,mbue1_0.short_description,mbue1_0.status,mbue1_0.status_reason,mbue1_0.tax_number,mbue1_0.updated_at from merchant_business_units mbue1_0 where mbue1_0.business_unit_token=$1

select te1_0.id,te1_0.amount,te1_0.authorization_code,te1_0.blocked_payer_reason,te1_0.brand,te1_0.business_unit_id,te1_0.canceled_reason,te1_0.capture_method,te1_0.comments,te1_0.created_at,te1_0.deleted_at,te1_0.due_date,te1_0.external_id,te1_0.has_payer,te1_0.installment_interest_type,te1_0.installment_number,te1_0.installment_total,te1_0.internal_id,te1_0.invoice_token,te1_0.merchant_id,te1_0.merchant_payment_id,te1_0.merchant_payment_identifier,te1_0.metadata,te1_0.movement_date,te1_0.nsu,te1_0.paid_amount,te1_0.partner_tracking_id,te1_0.partner_transaction_id,te1_0.payer_tax_number,te1_0.payment_method_id,te1_0.payment_partner_id,te1_0.refund_status,te1_0.refunded_amount,te1_0.refunded_at,te1_0.session_key,te1_0.status,te1_0.status_reason,te1_0.tags,te1_0.transaction_info,te1_0.transaction_token,te1_0.transaction_type,te1_0.updated_at from transactions te1_0 where te1_0.transaction_token=$1

Covered by index on (transaction_token)
Rows: 22438
Row progression: 22438, 1

Row estimates
- transaction_token (=): 1

Existing indexes
- id PRIMARY
- merchant_id, business_unit_id, created_at
- plan_billing_token
- transaction_token UNIQUE