create or replace view pg_schema(table_name) as
SELECT tables.table_name
FROM information_schema.tables
WHERE tables.table_schema::name = 'public'::name
  AND tables.table_type::text = 'BASE TABLE'::text;

alter table pg_schema
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on pg_schema to anon;

grant delete, insert, references, select, trigger, truncate, update on pg_schema to authenticated;

grant delete, insert, references, select, trigger, truncate, update on pg_schema to service_role;

create or replace view v_reconciled_order_id_v2
            (id, created_at, bank, iban_recipient, iban_sender, type, product, started_date, completed_date,
             description, amount, fee, currency, balance, user_id, bank_account_id, reconciliation_id, verified,
             data_author, money_out, money_in, opening_balance, closing_balance, user_id_recipient, user_id_sender,
             document_provider, transaction_id, hash, transaction_type, company_id, status, category, contact, memo,
             apple_pay_card_number, feature, sepa_debtor_id, apple_pay_tx_id, referenced_website, sepa_consent_id,
             referenced_date, is_loan, loan_type_id, transaction_provider_id, mollie_payment_id, metadata, locale,
             method, mode, country_code, paid_at, resource, name_recipient, merchant_id, iban_beneficiary,
             name_beneficiary, name_consumer, iban_consumer, bic_consumer, bank_consumer, time, iban_id_consumer,
             receipt_id, invoice_id, bic_beneficiary, processed_description, invoice_nr, remittance_information,
             invoice_aggregate_id, fiscal_year, quarter, credit_note, amount_discount, debit_note, amount_adjustment,
             reason_adjustment, amount_tax, rate_tax, fx_conversion_date, transfer_reference, normalized, debited,
             credited, iban, iban_counterparty, bank_counterparty, bic, bic_counterparty, updated_at,
             account_holder_name, account_holder_name_counterparty, creditor_identifier, direct_debit_aggregate_id,
             scheduled_on, signature_date, bank_reason, bank_reason_code, country_code_counterparty,
             has_given_authorization_for_auto_charging, order_id, doc_id, doc_created_at)
as
SELECT t.id,
       t.created_at,
       t.bank,
       t.iban_recipient,
       t.iban_sender,
       t.type,
       t.product,
       t.started_date,
       t.completed_date,
       t.description,
       t.amount,
       t.fee,
       t.currency,
       t.balance,
       t.user_id,
       t.bank_account_id,
       t.reconciliation_id,
       t.verified,
       t.data_author,
       t.money_out,
       t.money_in,
       t.opening_balance,
       t.closing_balance,
       t.user_id_recipient,
       t.user_id_sender,
       t.document_provider,
       t.transaction_id,
       t.hash,
       t.transaction_type,
       t.company_id,
       t.status,
       t.category,
       t.contact,
       t.memo,
       t.apple_pay_card_number,
       t.feature,
       t.sepa_debtor_id,
       t.apple_pay_tx_id,
       t.referenced_website,
       t.sepa_consent_id,
       t.referenced_date,
       t.is_loan,
       t.loan_type_id,
       t.transaction_provider_id,
       t.mollie_payment_id,
       t.metadata,
       t.locale,
       t.method,
       t.mode,
       t.country_code,
       t.paid_at,
       t.resource,
       t.name_recipient,
       t.merchant_id,
       t.iban_beneficiary,
       t.name_beneficiary,
       t.name_consumer,
       t.iban_consumer,
       t.bic_consumer,
       t.bank_consumer,
       t."time",
       t.iban_id_consumer,
       t.receipt_id,
       t.invoice_id,
       t.bic_beneficiary,
       t.processed_description,
       t.invoice_nr,
       t.remittance_information,
       t.invoice_aggregate_id,
       t.fiscal_year,
       t.quarter,
       t.credit_note,
       t.amount_discount,
       t.debit_note,
       t.amount_adjustment,
       t.reason_adjustment,
       t.amount_tax,
       t.rate_tax,
       t.fx_conversion_date,
       t.transfer_reference,
       t.normalized,
       t.debited,
       t.credited,
       t.iban,
       t.iban_counterparty,
       t.bank_counterparty,
       t.bic,
       t.bic_counterparty,
       t.updated_at,
       t.account_holder_name,
       t.account_holder_name_counterparty,
       t.creditor_identifier,
       t.direct_debit_aggregate_id,
       t.scheduled_on,
       t.signature_date,
       t.bank_reason,
       t.bank_reason_code,
       t.country_code_counterparty,
       t.has_given_authorization_for_auto_charging,
       t.order_id,
       d.id         AS doc_id,
       d.created_at AS doc_created_at
FROM transactions t
         JOIN documents_v2 d
              ON d.order_id = t.order_id AND d.total = t.debited AND COALESCE(d.currency, t.currency) = t.currency;

alter table v_reconciled_order_id_v2
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_order_id_v2 to anon;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_order_id_v2 to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_order_id_v2 to service_role;

create or replace view v_reconciled_totals
            (id, created_at, bank, iban_recipient, iban_sender, type, product, started_date, completed_date,
             description, amount, fee, currency, balance, user_id, bank_account_id, reconciliation_id, verified,
             data_author, money_out, money_in, opening_balance, closing_balance, user_id_recipient, user_id_sender,
             document_provider, transaction_id, hash, transaction_type, company_id, status, category, contact, memo,
             apple_pay_card_number, feature, sepa_debtor_id, apple_pay_tx_id, referenced_website, sepa_consent_id,
             referenced_date, is_loan, loan_type_id, transaction_provider_id, mollie_payment_id, metadata, locale,
             method, mode, country_code, paid_at, resource, name_recipient, merchant_id, iban_beneficiary,
             name_beneficiary, name_consumer, iban_consumer, bic_consumer, bank_consumer, time, iban_id_consumer,
             receipt_id, invoice_id, bic_beneficiary, processed_description, invoice_nr, remittance_information,
             invoice_aggregate_id, fiscal_year, quarter, credit_note, amount_discount, debit_note, amount_adjustment,
             reason_adjustment, amount_tax, rate_tax, fx_conversion_date, transfer_reference, normalized, debited,
             credited, iban, iban_counterparty, bank_counterparty, bic, bic_counterparty, updated_at,
             account_holder_name, account_holder_name_counterparty, creditor_identifier, direct_debit_aggregate_id,
             scheduled_on, signature_date, bank_reason, bank_reason_code, country_code_counterparty,
             has_given_authorization_for_auto_charging, order_id, doc_id, doc_created_at)
as
SELECT t.id,
       t.created_at,
       t.bank,
       t.iban_recipient,
       t.iban_sender,
       t.type,
       t.product,
       t.started_date,
       t.completed_date,
       t.description,
       t.amount,
       t.fee,
       t.currency,
       t.balance,
       t.user_id,
       t.bank_account_id,
       t.reconciliation_id,
       t.verified,
       t.data_author,
       t.money_out,
       t.money_in,
       t.opening_balance,
       t.closing_balance,
       t.user_id_recipient,
       t.user_id_sender,
       t.document_provider,
       t.transaction_id,
       t.hash,
       t.transaction_type,
       t.company_id,
       t.status,
       t.category,
       t.contact,
       t.memo,
       t.apple_pay_card_number,
       t.feature,
       t.sepa_debtor_id,
       t.apple_pay_tx_id,
       t.referenced_website,
       t.sepa_consent_id,
       t.referenced_date,
       t.is_loan,
       t.loan_type_id,
       t.transaction_provider_id,
       t.mollie_payment_id,
       t.metadata,
       t.locale,
       t.method,
       t.mode,
       t.country_code,
       t.paid_at,
       t.resource,
       t.name_recipient,
       t.merchant_id,
       t.iban_beneficiary,
       t.name_beneficiary,
       t.name_consumer,
       t.iban_consumer,
       t.bic_consumer,
       t.bank_consumer,
       t."time",
       t.iban_id_consumer,
       t.receipt_id,
       t.invoice_id,
       t.bic_beneficiary,
       t.processed_description,
       t.invoice_nr,
       t.remittance_information,
       t.invoice_aggregate_id,
       t.fiscal_year,
       t.quarter,
       t.credit_note,
       t.amount_discount,
       t.debit_note,
       t.amount_adjustment,
       t.reason_adjustment,
       t.amount_tax,
       t.rate_tax,
       t.fx_conversion_date,
       t.transfer_reference,
       t.normalized,
       t.debited,
       t.credited,
       t.iban,
       t.iban_counterparty,
       t.bank_counterparty,
       t.bic,
       t.bic_counterparty,
       t.updated_at,
       t.account_holder_name,
       t.account_holder_name_counterparty,
       t.creditor_identifier,
       t.direct_debit_aggregate_id,
       t.scheduled_on,
       t.signature_date,
       t.bank_reason,
       t.bank_reason_code,
       t.country_code_counterparty,
       t.has_given_authorization_for_auto_charging,
       t.order_id,
       d.document_id AS doc_id,
       d.created_at  AS doc_created_at
FROM transactions t
         JOIN documents_v2 d ON d.total = t.debited AND COALESCE(d.currency, t.currency) = t.currency
WHERE t.company_id = d.company_id::text;

alter table v_reconciled_totals
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_totals to anon;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_totals to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_totals to service_role;

create or replace view v_reconciled_invoice_id
            (id, created_at, bank, iban_recipient, iban_sender, type, product, started_date, completed_date,
             description, amount, fee, currency, balance, user_id, bank_account_id, reconciliation_id, verified,
             data_author, money_out, money_in, opening_balance, closing_balance, user_id_recipient, user_id_sender,
             document_provider, transaction_id, hash, transaction_type, company_id, status, category, contact, memo,
             apple_pay_card_number, feature, sepa_debtor_id, apple_pay_tx_id, referenced_website, sepa_consent_id,
             referenced_date, is_loan, loan_type_id, transaction_provider_id, mollie_payment_id, metadata, locale,
             method, mode, country_code, paid_at, resource, name_recipient, merchant_id, iban_beneficiary,
             name_beneficiary, name_consumer, iban_consumer, bic_consumer, bank_consumer, time, iban_id_consumer,
             receipt_id, invoice_id, bic_beneficiary, processed_description, invoice_nr, remittance_information,
             invoice_aggregate_id, fiscal_year, quarter, credit_note, amount_discount, debit_note, amount_adjustment,
             reason_adjustment, amount_tax, rate_tax, fx_conversion_date, transfer_reference, normalized, debited,
             credited, iban, iban_counterparty, bank_counterparty, bic, bic_counterparty, updated_at,
             account_holder_name, account_holder_name_counterparty, creditor_identifier, direct_debit_aggregate_id,
             scheduled_on, signature_date, bank_reason, bank_reason_code, country_code_counterparty,
             has_given_authorization_for_auto_charging, order_id, doc_id, doc_created_at)
as
SELECT t.id,
       t.created_at,
       t.bank,
       t.iban_recipient,
       t.iban_sender,
       t.type,
       t.product,
       t.started_date,
       t.completed_date,
       t.description,
       t.amount,
       t.fee,
       t.currency,
       t.balance,
       t.user_id,
       t.bank_account_id,
       t.reconciliation_id,
       t.verified,
       t.data_author,
       t.money_out,
       t.money_in,
       t.opening_balance,
       t.closing_balance,
       t.user_id_recipient,
       t.user_id_sender,
       t.document_provider,
       t.transaction_id,
       t.hash,
       t.transaction_type,
       t.company_id,
       t.status,
       t.category,
       t.contact,
       t.memo,
       t.apple_pay_card_number,
       t.feature,
       t.sepa_debtor_id,
       t.apple_pay_tx_id,
       t.referenced_website,
       t.sepa_consent_id,
       t.referenced_date,
       t.is_loan,
       t.loan_type_id,
       t.transaction_provider_id,
       t.mollie_payment_id,
       t.metadata,
       t.locale,
       t.method,
       t.mode,
       t.country_code,
       t.paid_at,
       t.resource,
       t.name_recipient,
       t.merchant_id,
       t.iban_beneficiary,
       t.name_beneficiary,
       t.name_consumer,
       t.iban_consumer,
       t.bic_consumer,
       t.bank_consumer,
       t."time",
       t.iban_id_consumer,
       t.receipt_id,
       t.invoice_id,
       t.bic_beneficiary,
       t.processed_description,
       t.invoice_nr,
       t.remittance_information,
       t.invoice_aggregate_id,
       t.fiscal_year,
       t.quarter,
       t.credit_note,
       t.amount_discount,
       t.debit_note,
       t.amount_adjustment,
       t.reason_adjustment,
       t.amount_tax,
       t.rate_tax,
       t.fx_conversion_date,
       t.transfer_reference,
       t.normalized,
       t.debited,
       t.credited,
       t.iban,
       t.iban_counterparty,
       t.bank_counterparty,
       t.bic,
       t.bic_counterparty,
       t.updated_at,
       t.account_holder_name,
       t.account_holder_name_counterparty,
       t.creditor_identifier,
       t.direct_debit_aggregate_id,
       t.scheduled_on,
       t.signature_date,
       t.bank_reason,
       t.bank_reason_code,
       t.country_code_counterparty,
       t.has_given_authorization_for_auto_charging,
       t.order_id,
       d.document_id AS doc_id,
       d.created_at  AS doc_created_at
FROM transactions t
         JOIN documents_v2 d ON d.invoice_number = t.invoice_nr AND d.total = t.debited AND
                                COALESCE(d.currency, t.currency) = t.currency
WHERE t.invoice_nr <> NULL::text;

alter table v_reconciled_invoice_id
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_invoice_id to anon;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_invoice_id to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_invoice_id to service_role;

create or replace view v_reconciled_order_id
            (id, created_at, bank, iban_recipient, iban_sender, type, product, started_date, completed_date,
             description, amount, fee, currency, balance, user_id, bank_account_id, reconciliation_id, verified,
             data_author, money_out, money_in, opening_balance, closing_balance, user_id_recipient, user_id_sender,
             document_provider, transaction_id, hash, transaction_type, company_id, status, category, contact, memo,
             apple_pay_card_number, feature, sepa_debtor_id, apple_pay_tx_id, referenced_website, sepa_consent_id,
             referenced_date, is_loan, loan_type_id, transaction_provider_id, mollie_payment_id, metadata, locale,
             method, mode, country_code, paid_at, resource, name_recipient, merchant_id, iban_beneficiary,
             name_beneficiary, name_consumer, iban_consumer, bic_consumer, bank_consumer, time, iban_id_consumer,
             receipt_id, invoice_id, bic_beneficiary, processed_description, invoice_nr, remittance_information,
             invoice_aggregate_id, fiscal_year, quarter, credit_note, amount_discount, debit_note, amount_adjustment,
             reason_adjustment, amount_tax, rate_tax, fx_conversion_date, transfer_reference, normalized, debited,
             credited, iban, iban_counterparty, bank_counterparty, bic, bic_counterparty, updated_at,
             account_holder_name, account_holder_name_counterparty, creditor_identifier, direct_debit_aggregate_id,
             scheduled_on, signature_date, bank_reason, bank_reason_code, country_code_counterparty,
             has_given_authorization_for_auto_charging, order_id, test_mode, doc_id, doc_created_at)
as
SELECT t.id,
       t.created_at,
       t.bank,
       t.iban_recipient,
       t.iban_sender,
       t.type,
       t.product,
       t.started_date,
       t.completed_date,
       t.description,
       t.amount,
       t.fee,
       t.currency,
       t.balance,
       t.user_id,
       t.bank_account_id,
       t.reconciliation_id,
       t.verified,
       t.data_author,
       t.money_out,
       t.money_in,
       t.opening_balance,
       t.closing_balance,
       t.user_id_recipient,
       t.user_id_sender,
       t.document_provider,
       t.transaction_id,
       t.hash,
       t.transaction_type,
       t.company_id,
       t.status,
       t.category,
       t.contact,
       t.memo,
       t.apple_pay_card_number,
       t.feature,
       t.sepa_debtor_id,
       t.apple_pay_tx_id,
       t.referenced_website,
       t.sepa_consent_id,
       t.referenced_date,
       t.is_loan,
       t.loan_type_id,
       t.transaction_provider_id,
       t.mollie_payment_id,
       t.metadata,
       t.locale,
       t.method,
       t.mode,
       t.country_code,
       t.paid_at,
       t.resource,
       t.name_recipient,
       t.merchant_id,
       t.iban_beneficiary,
       t.name_beneficiary,
       t.name_consumer,
       t.iban_consumer,
       t.bic_consumer,
       t.bank_consumer,
       t."time",
       t.iban_id_consumer,
       t.receipt_id,
       t.invoice_id,
       t.bic_beneficiary,
       t.processed_description,
       t.invoice_nr,
       t.remittance_information,
       t.invoice_aggregate_id,
       t.fiscal_year,
       t.quarter,
       t.credit_note,
       t.amount_discount,
       t.debit_note,
       t.amount_adjustment,
       t.reason_adjustment,
       t.amount_tax,
       t.rate_tax,
       t.fx_conversion_date,
       t.transfer_reference,
       t.normalized,
       t.debited,
       t.credited,
       t.iban,
       t.iban_counterparty,
       t.bank_counterparty,
       t.bic,
       t.bic_counterparty,
       t.updated_at,
       t.account_holder_name,
       t.account_holder_name_counterparty,
       t.creditor_identifier,
       t.direct_debit_aggregate_id,
       t.scheduled_on,
       t.signature_date,
       t.bank_reason,
       t.bank_reason_code,
       t.country_code_counterparty,
       t.has_given_authorization_for_auto_charging,
       t.order_id,
       t.test_mode,
       d.document_id AS doc_id,
       d.created_at  AS doc_created_at
FROM transactions t
         JOIN documents_v2 d
              ON d.order_id = t.order_id AND d.total = t.debited AND COALESCE(d.currency, t.currency) = t.currency AND
                 d.company_id::text = t.company_id
WHERE t.order_id <> ''::text;

alter table v_reconciled_order_id
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_order_id to anon;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_order_id to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_reconciled_order_id to service_role;

create or replace view v_unreconciled_per_company
            (company_id, unreconciled_transactions, reconciled_transactions, unreconciled_documents,
             reconciled_documents, total_unreconciled, total_reconciled, distribution, unreconciled_percent, created_at)
as
WITH transactions_summary AS (SELECT transactions.company_id,
                                     count(*) FILTER (WHERE transactions.reconciled = false) AS unreconciled_transactions,
                                     count(*) FILTER (WHERE transactions.reconciled = true)  AS reconciled_transactions
                              FROM transactions
                              GROUP BY transactions.company_id),
     documents_summary AS (SELECT documents_v2.company_id::text                           AS company_id,
                                  count(*) FILTER (WHERE documents_v2.reconciled = false) AS unreconciled_documents,
                                  count(*) FILTER (WHERE documents_v2.reconciled = true)  AS reconciled_documents
                           FROM documents_v2
                           GROUP BY documents_v2.company_id)
SELECT COALESCE(t.company_id, d.company_id)                                                             AS company_id,
       COALESCE(t.unreconciled_transactions, 0::bigint)                                                 AS unreconciled_transactions,
       COALESCE(t.reconciled_transactions, 0::bigint)                                                   AS reconciled_transactions,
       COALESCE(d.unreconciled_documents, 0::bigint)                                                    AS unreconciled_documents,
       COALESCE(d.reconciled_documents, 0::bigint)                                                      AS reconciled_documents,
       COALESCE(t.unreconciled_transactions, 0::bigint) +
       COALESCE(d.unreconciled_documents, 0::bigint)                                                    AS total_unreconciled,
       COALESCE(t.reconciled_transactions, 0::bigint) +
       COALESCE(d.reconciled_documents, 0::bigint)                                                      AS total_reconciled,
       ((COALESCE(t.unreconciled_transactions, 0::bigint) + COALESCE(d.unreconciled_documents, 0::bigint)) ||
        ' / '::text) ||
       (COALESCE(t.unreconciled_transactions, 0::bigint) + COALESCE(d.unreconciled_documents, 0::bigint) +
        COALESCE(t.reconciled_transactions, 0::bigint) + COALESCE(d.reconciled_documents, 0::bigint))   AS distribution,
       round(100.0 * (COALESCE(t.unreconciled_transactions, 0::bigint) +
                      COALESCE(d.unreconciled_documents, 0::bigint))::numeric / NULLIF(
                     COALESCE(t.unreconciled_transactions, 0::bigint) + COALESCE(d.unreconciled_documents, 0::bigint) +
                     COALESCE(t.reconciled_transactions, 0::bigint) + COALESCE(d.reconciled_documents, 0::bigint),
                     0)::numeric,
             2)                                                                                         AS unreconciled_percent,
       now()                                                                                            AS created_at
FROM transactions_summary t
         FULL JOIN documents_summary d ON t.company_id = d.company_id;

alter table v_unreconciled_per_company
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_unreconciled_per_company to anon;

grant delete, insert, references, select, trigger, truncate, update on v_unreconciled_per_company to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_unreconciled_per_company to service_role;

create or replace view v_match_stats_by_company
            (company_id, total_reconciliations, avg_confidence, date_matches, date_pct, invoice_matches, invoice_pct,
             order_matches, order_pct, merchant_matches, merchant_pct, amount_matches, amount_pct, currency_matches,
             currency_pct, customer_matches, customer_pct)
as
SELECT r.company_id,
       count(*)                                                                                   AS total_reconciliations,
       round(avg(rms.confidence), 2)                                                              AS avg_confidence,
       sum(rms.date_match::integer)                                                               AS date_matches,
       round(100.0 * sum(rms.date_match::integer)::numeric / NULLIF(count(*), 0)::numeric, 2)     AS date_pct,
       sum(rms.invoice_match::integer)                                                            AS invoice_matches,
       round(100.0 * sum(rms.invoice_match::integer)::numeric / NULLIF(count(*), 0)::numeric, 2)  AS invoice_pct,
       sum(rms.order_match::integer)                                                              AS order_matches,
       round(100.0 * sum(rms.order_match::integer)::numeric / NULLIF(count(*), 0)::numeric, 2)    AS order_pct,
       sum(rms.merchant_match::integer)                                                           AS merchant_matches,
       round(100.0 * sum(rms.merchant_match::integer)::numeric / NULLIF(count(*), 0)::numeric, 2) AS merchant_pct,
       sum(rms.amount_match::integer)                                                             AS amount_matches,
       round(100.0 * sum(rms.amount_match::integer)::numeric / NULLIF(count(*), 0)::numeric, 2)   AS amount_pct,
       sum(rms.currency_match::integer)                                                           AS currency_matches,
       round(100.0 * sum(rms.currency_match::integer)::numeric / NULLIF(count(*), 0)::numeric, 2) AS currency_pct,
       sum(rms.customer_match::integer)                                                           AS customer_matches,
       round(100.0 * sum(rms.customer_match::integer)::numeric / NULLIF(count(*), 0)::numeric, 2) AS customer_pct
FROM reconciliations r
         JOIN reconciliations_match_scores rms ON rms.reconciliation_id = r.reconciliation_id
GROUP BY r.company_id;

alter table v_match_stats_by_company
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_match_stats_by_company to anon;

grant delete, insert, references, select, trigger, truncate, update on v_match_stats_by_company to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_match_stats_by_company to service_role;

create or replace view v_match_stats_by_company_key(company_id, match_key, total_rows, matches, match_pct, avg_confidence) as
WITH base AS (SELECT r.company_id,
                     rms.confidence,
                     k.key,
                     k.matched::integer AS matched
              FROM reconciliations r
                       JOIN reconciliations_match_scores rms ON rms.reconciliation_id = r.reconciliation_id
                       CROSS JOIN LATERAL ( VALUES ('date'::text, rms.date_match),
                                                   ('invoice'::text, rms.invoice_match),
                                                   ('order'::text, rms.order_match),
                                                   ('merchant'::text, rms.merchant_match),
                                                   ('amount'::text, rms.amount_match),
                                                   ('currency'::text, rms.currency_match),
                                                   ('customer'::text, rms.customer_match)) k(key, matched))
SELECT base.company_id,
       base.key                                                                    AS match_key,
       count(*)                                                                    AS total_rows,
       sum(base.matched)                                                           AS matches,
       round(100.0 * sum(base.matched)::numeric / NULLIF(count(*), 0)::numeric, 2) AS match_pct,
       round(avg(base.confidence), 2)                                              AS avg_confidence
FROM base
GROUP BY base.company_id, base.key;

alter table v_match_stats_by_company_key
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_match_stats_by_company_key to anon;

grant delete, insert, references, select, trigger, truncate, update on v_match_stats_by_company_key to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_match_stats_by_company_key to service_role;

create or replace view v_avg_confidence(company_id, avg_confidence, message) as
WITH company_avg AS (SELECT reconciliations_match_scores.company_id,
                            avg(reconciliations_match_scores.confidence) AS avg_confidence
                     FROM reconciliations_match_scores
                     GROUP BY reconciliations_match_scores.company_id)
SELECT company_avg.company_id,
       COALESCE(company_avg.avg_confidence, 0::numeric) AS avg_confidence,
       CASE
           WHEN company_avg.avg_confidence IS NULL THEN 'no matches found, defaulting to 0'::text
           ELSE 'success'::text
           END                                          AS message
FROM company_avg;

alter table v_avg_confidence
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_avg_confidence to anon;

grant delete, insert, references, select, trigger, truncate, update on v_avg_confidence to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_avg_confidence to service_role;

create or replace view confidence_distribution(company_id, cluster_id, count_in_cluster) as
WITH bins AS (SELECT generate_series(1, 20) AS cluster_id),
     scored AS (SELECT reconciliations_match_scores.company_id,
                       ceil(reconciliations_match_scores.confidence / 5.0)::integer AS cluster_id
                FROM reconciliations_match_scores)
SELECT c.company_id,
       b.cluster_id,
       count(s.cluster_id) AS count_in_cluster
FROM bins b
         CROSS JOIN (SELECT DISTINCT reconciliations_match_scores.company_id
                     FROM reconciliations_match_scores) c
         LEFT JOIN scored s ON s.cluster_id = b.cluster_id AND s.company_id = c.company_id
GROUP BY c.company_id, b.cluster_id
ORDER BY c.company_id, b.cluster_id;

alter table confidence_distribution
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on confidence_distribution to anon;

grant delete, insert, references, select, trigger, truncate, update on confidence_distribution to authenticated;

grant delete, insert, references, select, trigger, truncate, update on confidence_distribution to service_role;

create or replace view v_banking_accounts
            (id, provider, bank_account_id, company_id, created_at, updated_at, display_name, currency, status,
             balance_available, balance_current, balance_pending, primary_beneficiary, primary_iban,
             financial_institution_id, financial_institution_name, financial_institution_avatar, last_synced_at,
             auth_expires_at, reference, reference_type, external_refs)
as
WITH ponto AS (SELECT 'ponto'::text                                         AS provider,
                      spa.ponto_account_id                                  AS bank_account_id,
                      spa.company_id,
                      spa.created_at,
                      GREATEST(COALESCE(spa.available_balance_changed_at, spa.created_at),
                               COALESCE(spa.current_balance_changed_at, spa.created_at),
                               COALESCE(spa.authorization_expiration_expected_at, spa.created_at),
                               COALESCE(spa.authorized_at, spa.created_at)) AS updated_at,
                      COALESCE(spa.description, spa.holder_name)            AS display_name,
                      spa.currency,
                      CASE
                          WHEN spa.deprecated THEN 'deprecated'::text
                          ELSE 'active'::text
                          END                                               AS status,
                      spa.available_balance                                 AS balance_available,
                      spa.current_balance                                   AS balance_current,
                      NULL::numeric                                         AS balance_pending,
                      spa.holder_name                                       AS primary_beneficiary,
                      CASE
                          WHEN upper(COALESCE(spa.reference_type, ''::text)) = 'IBAN'::text THEN spa.reference
                          ELSE NULL::text
                          END                                               AS primary_iban,
                      spa.financial_institution_id,
                      spa.financial_institution_name,
                      spa.financial_institution_avatar,
                      NULLIF((spa.raw -> 'meta'::text) ->> 'synchronizedAt'::text,
                             ''::text)::timestamp with time zone            AS last_synced_at,
                      spa.authorization_expiration_expected_at              AS auth_expires_at,
                      spa.reference,
                      spa.reference_type,
                      jsonb_build_object('internal_reference', spa.internal_reference, 'product', spa.product,
                                         'subtype', spa.subtype, 'ponto_account_id', spa.ponto_account_id) ||
                      COALESCE(spa.raw, '{}'::jsonb)                        AS external_refs
               FROM stargate_ponto_accounts spa
               WHERE NOT spa.awaiting_revocation),
     mollie AS (SELECT 'mollie'::text                            AS provider,
                       ma.mollie_account_id                      AS bank_account_id,
                       ma.company_id,
                       ma.created_at,
                       COALESCE(b.created_at, ma.created_at)     AS updated_at,
                       COALESCE(ma.name, ma.primary_beneficiary) AS display_name,
                       b.currency,
                       CASE
                           WHEN ma.revoked THEN 'revoked'::text
                           WHEN NOT ma.active THEN 'inactive'::text
                           ELSE COALESCE(ma.status, 'active'::text)
                           END                                   AS status,
                       b.amount_available                        AS balance_available,
                       b.amount_available                        AS balance_current,
                       b.amount_pending                          AS balance_pending,
                       ma.primary_beneficiary,
                       ma.primary_iban,
                       NULL::uuid                                AS financial_institution_id,
                       NULL::text                                AS financial_institution_name,
                       NULL::text                                AS financial_institution_avatar,
                       b.created_at                              AS last_synced_at,
                       NULL::timestamp with time zone            AS auth_expires_at,
                       ma.primary_iban                           AS reference,
                       'IBAN'::text                              AS reference_type,
                       jsonb_build_object('profile_id', ma.profile_id, 'permissions', ma.permission_scopes,
                                          'amount_pending', b.amount_pending, 'mollie_account_id',
                                          ma.mollie_account_id)  AS external_refs
                FROM mollie_accounts ma
                         LEFT JOIN LATERAL ( SELECT bl.banking_account_id,
                                                    bl.amount_available,
                                                    bl.amount_pending,
                                                    bl.currency,
                                                    bl.created_at
                                             FROM balances bl
                                             WHERE bl.iban = ma.primary_iban
                                             ORDER BY bl.created_at DESC
                                             LIMIT 1) b ON true),
     unioned AS (SELECT ponto.provider,
                        ponto.bank_account_id,
                        ponto.company_id,
                        ponto.created_at,
                        ponto.updated_at,
                        ponto.display_name,
                        ponto.currency,
                        ponto.status,
                        ponto.balance_available,
                        ponto.balance_current,
                        ponto.balance_pending,
                        ponto.primary_beneficiary,
                        ponto.primary_iban,
                        ponto.financial_institution_id,
                        ponto.financial_institution_name,
                        ponto.financial_institution_avatar,
                        ponto.last_synced_at,
                        ponto.auth_expires_at,
                        ponto.reference,
                        ponto.reference_type,
                        ponto.external_refs
                 FROM ponto
                 UNION ALL
                 SELECT mollie.provider,
                        mollie.bank_account_id,
                        mollie.company_id,
                        mollie.created_at,
                        mollie.updated_at,
                        mollie.display_name,
                        mollie.currency,
                        mollie.status,
                        mollie.balance_available,
                        mollie.balance_current,
                        mollie.balance_pending,
                        mollie.primary_beneficiary,
                        mollie.primary_iban,
                        mollie.financial_institution_id,
                        mollie.financial_institution_name,
                        mollie.financial_institution_avatar,
                        mollie.last_synced_at,
                        mollie.auth_expires_at,
                        mollie.reference,
                        mollie.reference_type,
                        mollie.external_refs
                 FROM mollie)
SELECT row_number()
       OVER (ORDER BY unioned.provider, (COALESCE(unioned.display_name, ''::text)), unioned.created_at, (COALESCE(unioned.bank_account_id::text, ''::text))) AS id,
       unioned.provider,
       unioned.bank_account_id,
       unioned.company_id,
       unioned.created_at,
       EXTRACT(epoch FROM unioned.updated_at)::bigint                                                                                                        AS updated_at,
       unioned.display_name,
       unioned.currency,
       unioned.status,
       unioned.balance_available,
       unioned.balance_current,
       unioned.balance_pending,
       COALESCE(unioned.primary_beneficiary, unioned.display_name)                                                                                           AS primary_beneficiary,
       unioned.primary_iban,
       unioned.financial_institution_id,
       unioned.financial_institution_name,
       unioned.financial_institution_avatar,
       EXTRACT(epoch FROM unioned.last_synced_at)::bigint                                                                                                    AS last_synced_at,
       EXTRACT(epoch FROM unioned.auth_expires_at)::bigint                                                                                                   AS auth_expires_at,
       unioned.reference,
       unioned.reference_type,
       unioned.external_refs
FROM unioned;

alter table v_banking_accounts
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_banking_accounts to anon;

grant delete, insert, references, select, trigger, truncate, update on v_banking_accounts to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_banking_accounts to service_role;

create or replace view v_latest_stargate_ponto_token
            (id, created_at, time, state, access_token, refresh_token, scope, token_type, expires_in, raw,
             stargate_ponto_token_id, account_id, sub)
as
SELECT DISTINCT ON (stargate_ponto_tokens.account_id) stargate_ponto_tokens.id,
                                                      stargate_ponto_tokens.created_at,
                                                      stargate_ponto_tokens."time",
                                                      stargate_ponto_tokens.state,
                                                      stargate_ponto_tokens.access_token,
                                                      stargate_ponto_tokens.refresh_token,
                                                      stargate_ponto_tokens.scope,
                                                      stargate_ponto_tokens.token_type,
                                                      stargate_ponto_tokens.expires_in,
                                                      stargate_ponto_tokens.raw,
                                                      stargate_ponto_tokens.stargate_ponto_token_id,
                                                      stargate_ponto_tokens.account_id,
                                                      stargate_ponto_tokens.sub
FROM stargate_ponto_tokens
ORDER BY stargate_ponto_tokens.account_id, stargate_ponto_tokens."time" DESC;

alter table v_latest_stargate_ponto_token
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_latest_stargate_ponto_token to anon;

grant delete, insert, references, select, trigger, truncate, update on v_latest_stargate_ponto_token to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_latest_stargate_ponto_token to service_role;

create or replace view v_user_company_access
            (user_id, email, full_name, access_group_pk, access_group_created_at, access_group_id, role, status,
             company_id, company_name, country_code, company_number, avatar)
as
SELECT DISTINCT ON (u.user_id, c.company_id) u.user_id,
                                             u.email,
                                             u.full_name,
                                             ag.id              AS access_group_pk,
                                             ag.created_at      AS access_group_created_at,
                                             ag.access_group_id,
                                             ag.role,
                                             ag.status,
                                             c.company_id,
                                             c.name             AS company_name,
                                             c.country_code,
                                             c.company_number,
                                             c.company_logo_url AS avatar
FROM access_groups ag
         JOIN users u ON ag.user_id = u.user_id
         JOIN companies c ON ag.company_id = c.company_id
ORDER BY u.user_id, c.company_id, ag.created_at DESC;

alter table v_user_company_access
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_user_company_access to anon;

grant delete, insert, references, select, trigger, truncate, update on v_user_company_access to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_user_company_access to service_role;

create or replace view v_booking_staged
            (bookable_hash, transaction_id, company_id, amount, currency, description, transaction_status, created_at,
             reconciliation_id, document_id, booking_status)
as
WITH tx AS (SELECT t.transaction_id,
                   t.company_id,
                   t.amount,
                   t.currency,
                   t.description,
                   t.status AS transaction_status,
                   t.created_at,
                   r.reconciliation_id,
                   d.document_id,
                   CASE
                       WHEN t.status IS DISTINCT FROM 'paid'::text AND t.amount < 0::numeric THEN 'to_be_paid'::text
                       WHEN (t.status = ANY (ARRAY ['pending'::text, 'open'::text])) AND t.amount > 0::numeric
                           THEN 'to_be_received'::text
                       WHEN r.reconciliation_id IS NULL AND d.document_id IS NULL THEN 'without_document'::text
                       WHEN r.reconciliation_id IS NOT NULL AND NOT (EXISTS (SELECT 1
                                                                             FROM bookings b
                                                                             WHERE b.transaction_id = t.transaction_id))
                           THEN 'draft_booking'::text
                       WHEN (EXISTS (SELECT 1
                                     FROM bookings b
                                     WHERE b.transaction_id = t.transaction_id)) THEN 'booked'::text
                       ELSE 'open_chat'::text
                       END  AS booking_status
            FROM transactions t
                     LEFT JOIN reconciliations r ON r.transaction_id = t.transaction_id
                     LEFT JOIN documents_v2 d ON d.document_id = r.document_id)
SELECT md5(COALESCE(tx.transaction_id::text, ''::text) || COALESCE(tx.document_id::text, ''::text)) AS bookable_hash,
       tx.transaction_id,
       tx.company_id,
       tx.amount,
       tx.currency,
       tx.description,
       tx.transaction_status,
       tx.created_at,
       tx.reconciliation_id,
       tx.document_id,
       tx.booking_status
FROM tx;

alter table v_booking_staged
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_booking_staged to anon;

grant delete, insert, references, select, trigger, truncate, update on v_booking_staged to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_booking_staged to service_role;

create or replace view v_latest_mollie_oauth2_authorization
            (id, created_at, refresh_token, scope, token_type, expires_in, mollie_oath2_authorization_id, access_token,
             time, expires_at, active, updated_at, expired, company_id, user_id, state)
as
SELECT DISTINCT ON (mollie_oath2_authorizations.company_id) mollie_oath2_authorizations.id,
                                                            mollie_oath2_authorizations.created_at,
                                                            mollie_oath2_authorizations.refresh_token,
                                                            mollie_oath2_authorizations.scope,
                                                            mollie_oath2_authorizations.token_type,
                                                            mollie_oath2_authorizations.expires_in,
                                                            mollie_oath2_authorizations.mollie_oath2_authorization_id,
                                                            mollie_oath2_authorizations.access_token,
                                                            mollie_oath2_authorizations."time",
                                                            mollie_oath2_authorizations.expires_at,
                                                            mollie_oath2_authorizations.active,
                                                            mollie_oath2_authorizations.updated_at,
                                                            mollie_oath2_authorizations.expired,
                                                            mollie_oath2_authorizations.company_id,
                                                            mollie_oath2_authorizations.user_id,
                                                            mollie_oath2_authorizations.state
FROM mollie_oath2_authorizations
ORDER BY mollie_oath2_authorizations.company_id, mollie_oath2_authorizations."time" DESC;

alter table v_latest_mollie_oauth2_authorization
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_latest_mollie_oauth2_authorization to anon;

grant delete, insert, references, select, trigger, truncate, update on v_latest_mollie_oauth2_authorization to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_latest_mollie_oauth2_authorization to service_role;

create or replace view balance_sheets
            (id, created_at, company_id, accounting_period, open_accounting_period, currency, account_type, account,
             gl_code, net_change, amount_inflow, amount_outflow, starting_balance, ending_balance, balance_sheet_id)
as
SELECT row_number() OVER ()                                              AS id,
       now()                                                             AS created_at,
       je.company_id,
       to_char(je.entry_date::timestamp with time zone, 'YYYY-MM'::text) AS accounting_period,
       NULL::text                                                        AS open_accounting_period,
       'EUR'::text                                                       AS currency,
       CASE
           WHEN gat.code::text ~ '^1'::text THEN 'asset'::text
           WHEN gat.code::text ~ '^2'::text THEN 'liability'::text
           WHEN gat.code::text ~ '^3'::text THEN 'equity'::text
           WHEN gat.code::text ~ '^4'::text THEN 'revenue'::text
           WHEN gat.code::text ~ '^5'::text THEN 'expense'::text
           ELSE 'other'::text
           END                                                           AS account_type,
       COALESCE(cga.alias_name, gat.name)                                AS account,
       gat.code                                                          AS gl_code,
       COALESCE(sum(jl.debit - jl.credit), 0::numeric)                   AS net_change,
       COALESCE(sum(jl.debit), 0::numeric)                               AS amount_inflow,
       COALESCE(sum(jl.credit), 0::numeric)                              AS amount_outflow,
       COALESCE(sum(jl.debit - jl.credit), 0::numeric)                   AS starting_balance,
       COALESCE(sum(jl.debit - jl.credit), 0::numeric)                   AS ending_balance,
       gen_random_uuid()                                                 AS balance_sheet_id
FROM journal_entries je
         JOIN journal_lines jl ON jl.journal_entry_id = je.journal_entry_id
         JOIN company_gl_accounts cga ON cga.company_gl_account_id = jl.company_gl_account_id
         LEFT JOIN gl_account_templates gat ON gat.template_id = cga.template_id
GROUP BY je.company_id, (to_char(je.entry_date::timestamp with time zone, 'YYYY-MM'::text)),
         (
             CASE
                 WHEN gat.code::text ~ '^1'::text THEN 'asset'::text
                 WHEN gat.code::text ~ '^2'::text THEN 'liability'::text
                 WHEN gat.code::text ~ '^3'::text THEN 'equity'::text
                 WHEN gat.code::text ~ '^4'::text THEN 'revenue'::text
                 WHEN gat.code::text ~ '^5'::text THEN 'expense'::text
                 ELSE 'other'::text
                 END), (COALESCE(cga.alias_name, gat.name)), gat.code;

alter table balance_sheets
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on balance_sheets to anon;

grant delete, insert, references, select, trigger, truncate, update on balance_sheets to authenticated;

grant delete, insert, references, select, trigger, truncate, update on balance_sheets to service_role;

create or replace view vat_quarterly_declaration
            (aggregate_id, company_id, tax_period, created_at, "1a_turnover", "1a_vat", "1b_turnover", "1b_vat",
             "1c_turnover", "1c_vat", "1d_turnover", "1d_vat", "1e_turnover", "2a_turnover", "2a_vat", "3a_turnover",
             "3b_turnover", "3c_turnover", "4a_turnover", "4a_vat", "4b_turnover", "4b_vat", "5a_vat", "5b_vat",
             "5c_vat", "5d_vat", "5g_vat", total_deliveries_rate_high, total_deliveries_rate_low,
             total_deliveries_rate_other_except_zero, total_excluding_vat, total_vat, total_including_vat,
             filing_status)
as
WITH inv AS (SELECT date_trunc('quarter'::text, to_timestamp(i.issue_date::double precision))::date AS tax_period,
                    i.invoice_id,
                    i.total,
                    i.tax_amount,
                    i.tax_rate,
                    i.tax_exempt,
                    i.reverse_charged,
                    i.recipient_country,
                    i.author_country,
                    i.recipient_company_id                                                          AS company_id,
                    to_timestamp(i.issue_date::double precision)                                    AS created_at
             FROM invoices i
             WHERE i.status = 'complete'::text),
     tx AS (SELECT date_trunc('quarter'::text, t.created_at)::date AS tax_period,
                   t.transaction_id,
                   t.amount,
                   t.country_code,
                   t.country_code_counterparty,
                   t.status,
                   t.rate_tax,
                   t.company_id,
                   r.reconciliation_id,
                   d.document_id,
                   d.company_id                                    AS doc_company_id,
                   t.created_at
            FROM transactions t
                     LEFT JOIN reconciliations r ON r.transaction_id = t.transaction_id
                     LEFT JOIN documents_v2 d ON d.document_id = r.document_id
            WHERE t.status = 'paid'::text),
     bk AS (SELECT date_trunc('quarter'::text, b.created_at)::date AS tax_period,
                   b.transaction_id,
                   b.amount,
                   b.currency,
                   b.description,
                   b.booking_status,
                   b.company_id,
                   b.created_at
            FROM v_booking_staged b
            WHERE b.booking_status = 'without_document'::text),
     base AS (SELECT COALESCE(inv.tax_period, tx.tax_period, bk.tax_period) AS tax_period,
                     inv.total,
                     inv.tax_amount,
                     inv.tax_rate,
                     inv.tax_exempt,
                     inv.reverse_charged,
                     inv.recipient_country,
                     inv.author_country,
                     tx.amount                                              AS tx_amount,
                     tx.country_code,
                     tx.country_code_counterparty,
                     tx.rate_tax,
                     bk.amount                                              AS bk_amount,
                     bk.booking_status,
                     COALESCE(inv.company_id, tx.doc_company_id, NULLIF(tx.company_id, ''::text)::uuid,
                              NULLIF(bk.company_id, ''::text)::uuid)        AS company_id,
                     COALESCE(inv.created_at, tx.created_at, bk.created_at) AS created_at
              FROM inv
                       FULL JOIN tx ON tx.transaction_id::text = inv.invoice_id::text
                       FULL JOIN bk ON bk.transaction_id::text = tx.transaction_id::text),
     filtered AS (SELECT b.tax_period,
                         b.total,
                         b.tax_amount,
                         b.tax_rate,
                         b.tax_exempt,
                         b.reverse_charged,
                         b.recipient_country,
                         b.author_country,
                         b.tx_amount,
                         b.country_code,
                         b.country_code_counterparty,
                         b.rate_tax,
                         b.bk_amount,
                         b.booking_status,
                         b.company_id,
                         b.created_at
                  FROM base b
                           JOIN companies c ON c.company_id = b.company_id
                  WHERE c.country = 'NL'::text),
     agg AS (SELECT gen_random_uuid()                                                                     AS aggregate_id,
                    f.company_id,
                    f.tax_period,
                    min(f.created_at)                                                                     AS created_at,
                    sum(
                            CASE
                                WHEN f.reverse_charged = true AND (f.recipient_country = ANY
                                                                   (ARRAY ['AT'::text, 'BE'::text, 'BG'::text, 'HR'::text, 'CY'::text, 'CZ'::text, 'DK'::text, 'EE'::text, 'FI'::text, 'FR'::text, 'DE'::text, 'GR'::text, 'HU'::text, 'IE'::text, 'IT'::text, 'LV'::text, 'LT'::text, 'LU'::text, 'MT'::text, 'PL'::text, 'PT'::text, 'RO'::text, 'SK'::text, 'SI'::text, 'ES'::text, 'SE'::text])) AND
                                     f.recipient_country <> 'NL'::text THEN f.total
                                ELSE 0::numeric
                                END)                                                                      AS "1a_turnover",
                    sum(
                            CASE
                                WHEN f.reverse_charged = true AND (f.recipient_country = ANY
                                                                   (ARRAY ['AT'::text, 'BE'::text, 'BG'::text, 'HR'::text, 'CY'::text, 'CZ'::text, 'DK'::text, 'EE'::text, 'FI'::text, 'FR'::text, 'DE'::text, 'GR'::text, 'HU'::text, 'IE'::text, 'IT'::text, 'LV'::text, 'LT'::text, 'LU'::text, 'MT'::text, 'PL'::text, 'PT'::text, 'RO'::text, 'SK'::text, 'SI'::text, 'ES'::text, 'SE'::text])) AND
                                     f.recipient_country <> 'NL'::text THEN f.tax_amount
                                ELSE 0::numeric
                                END)                                                                      AS "1a_vat",
                    0::numeric                                                                            AS "1b_turnover",
                    0::numeric                                                                            AS "1b_vat",
                    0::numeric                                                                            AS "1c_turnover",
                    0::numeric                                                                            AS "1c_vat",
                    0::numeric                                                                            AS "1d_turnover",
                    0::numeric                                                                            AS "1d_vat",
                    0::numeric                                                                            AS "1e_turnover",
                    0::numeric                                                                            AS "2a_turnover",
                    0::numeric                                                                            AS "2a_vat",
                    sum(
                            CASE
                                WHEN f.recipient_country IS NOT NULL AND (f.recipient_country <> ALL
                                                                          (ARRAY ['AT'::text, 'BE'::text, 'BG'::text, 'HR'::text, 'CY'::text, 'CZ'::text, 'DK'::text, 'EE'::text, 'FI'::text, 'FR'::text, 'DE'::text, 'GR'::text, 'HU'::text, 'IE'::text, 'IT'::text, 'LV'::text, 'LT'::text, 'LU'::text, 'MT'::text, 'PL'::text, 'PT'::text, 'RO'::text, 'SK'::text, 'SI'::text, 'ES'::text, 'SE'::text, 'NL'::text]))
                                    THEN f.total
                                ELSE 0::numeric
                                END)                                                                      AS "3a_turnover",
                    sum(
                            CASE
                                WHEN (f.recipient_country = ANY
                                      (ARRAY ['AT'::text, 'BE'::text, 'BG'::text, 'HR'::text, 'CY'::text, 'CZ'::text, 'DK'::text, 'EE'::text, 'FI'::text, 'FR'::text, 'DE'::text, 'GR'::text, 'HU'::text, 'IE'::text, 'IT'::text, 'LV'::text, 'LT'::text, 'LU'::text, 'MT'::text, 'PL'::text, 'PT'::text, 'RO'::text, 'SK'::text, 'SI'::text, 'ES'::text, 'SE'::text])) AND
                                     f.recipient_country <> 'NL'::text AND f.reverse_charged = false THEN f.total
                                ELSE 0::numeric
                                END)                                                                      AS "3b_turnover",
                    0::numeric                                                                            AS "3c_turnover",
                    sum(
                            CASE
                                WHEN f.booking_status = 'without_document'::text THEN abs(f.bk_amount)
                                ELSE 0::numeric
                                END)                                                                      AS "4a_turnover",
                    sum(
                            CASE
                                WHEN f.booking_status = 'without_document'::text THEN
                                    abs(f.bk_amount) * COALESCE(f.rate_tax, 21::numeric) / 100::numeric
                                ELSE 0::numeric
                                END)                                                                      AS "4a_vat",
                    0::numeric                                                                            AS "4b_turnover",
                    0::numeric                                                                            AS "4b_vat",
                    sum(
                            CASE
                                WHEN f.tax_rate > 0::numeric AND f.tax_exempt = false THEN f.tax_amount
                                ELSE 0::numeric
                                END)                                                                      AS "5a_vat",
                    0::numeric                                                                            AS "5b_vat",
                    0::numeric                                                                            AS "5c_vat",
                    0::numeric                                                                            AS "5d_vat",
                    sum(COALESCE(f.tax_amount, 0::numeric)) + sum(
                            CASE
                                WHEN f.booking_status = 'without_document'::text THEN
                                    abs(f.bk_amount) * COALESCE(f.rate_tax, 21::numeric) / 100::numeric
                                ELSE 0::numeric
                                END)                                                                      AS "5g_vat",
                    sum(
                            CASE
                                WHEN f.tax_rate = 21::numeric AND f.tax_exempt = false THEN f.total
                                ELSE 0::numeric
                                END)                                                                      AS total_deliveries_rate_high,
                    sum(
                            CASE
                                WHEN f.tax_rate = 9::numeric AND f.tax_exempt = false THEN f.total
                                ELSE 0::numeric
                                END)                                                                      AS total_deliveries_rate_low,
                    sum(
                            CASE
                                WHEN (f.tax_rate <> ALL (ARRAY [21, 9]::numeric[])) AND f.tax_exempt = false
                                    THEN f.total
                                ELSE 0::numeric
                                END)                                                                      AS total_deliveries_rate_other_except_zero,
                    sum(COALESCE(f.total, f.tx_amount, f.bk_amount) -
                        COALESCE(f.tax_amount, 0::numeric))                                               AS total_excluding_vat,
                    sum(COALESCE(f.tax_amount, 0::numeric) +
                        CASE
                            WHEN f.booking_status = 'without_document'::text THEN
                                abs(f.bk_amount) * COALESCE(f.rate_tax, 21::numeric) / 100::numeric
                            ELSE 0::numeric
                            END)                                                                          AS total_vat,
                    sum(COALESCE(f.total, f.tx_amount, f.bk_amount))                                      AS total_including_vat
             FROM filtered f
             GROUP BY f.tax_period, f.company_id)
SELECT agg.aggregate_id,
       agg.company_id,
       agg.tax_period,
       agg.created_at,
       agg."1a_turnover",
       agg."1a_vat",
       agg."1b_turnover",
       agg."1b_vat",
       agg."1c_turnover",
       agg."1c_vat",
       agg."1d_turnover",
       agg."1d_vat",
       agg."1e_turnover",
       agg."2a_turnover",
       agg."2a_vat",
       agg."3a_turnover",
       agg."3b_turnover",
       agg."3c_turnover",
       agg."4a_turnover",
       agg."4a_vat",
       agg."4b_turnover",
       agg."4b_vat",
       agg."5a_vat",
       agg."5b_vat",
       agg."5c_vat",
       agg."5d_vat",
       agg."5g_vat",
       agg.total_deliveries_rate_high,
       agg.total_deliveries_rate_low,
       agg.total_deliveries_rate_other_except_zero,
       agg.total_excluding_vat,
       agg.total_vat,
       agg.total_including_vat,
       CASE
           WHEN (EXTRACT(month FROM CURRENT_DATE) = ANY (ARRAY [1, 4, 7, 10]::numeric[])) AND
                EXTRACT(quarter FROM CURRENT_DATE) = EXTRACT(quarter FROM agg.tax_period) THEN 'pending'::text
           WHEN agg.tax_period < date_trunc('quarter'::text, CURRENT_DATE::timestamp with time zone) THEN 'closed'::text
           ELSE 'future'::text
           END AS filing_status
FROM agg;

alter table vat_quarterly_declaration
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on vat_quarterly_declaration to anon;

grant delete, insert, references, select, trigger, truncate, update on vat_quarterly_declaration to authenticated;

grant delete, insert, references, select, trigger, truncate, update on vat_quarterly_declaration to service_role;

create or replace view v_reconciliations
            (transaction_id, document_id, company_id, confidence, time, engine_version, fiscal_year, quarter, month,
             summary, note) as
WITH cfg AS (SELECT reconciliations_config.amount_tolerance AS config_amount_tolerance,
                    reconciliations_config.date_window_days AS config_date_window_days,
                    CASE
                        WHEN reconciliations_config.min_confidence IS NULL THEN 0::numeric
                        WHEN reconciliations_config.min_confidence <= 1::numeric
                            THEN reconciliations_config.min_confidence * 100.0
                        ELSE reconciliations_config.min_confidence
                        END                                 AS config_min_confidence
             FROM reconciliations_config
             WHERE reconciliations_config.reconciliation_config_id = '3dae610e-2326-46b8-ba18-85f54961e4ca'::uuid
             LIMIT 1),
     const AS (SELECT CASE
                          WHEN cfg.config_amount_tolerance >= 1::numeric THEN cfg.config_amount_tolerance / 100.0
                          ELSE cfg.config_amount_tolerance
                          END                                                                          AS amount_tolerance_fraction,
                      ceil(COALESCE(cfg.config_date_window_days, 1::numeric) * 86400::numeric)::bigint AS date_window_seconds,
                      LEAST(ceil(COALESCE(cfg.config_date_window_days, 1::numeric) * 86400::numeric)::bigint,
                            (14 * 86400)::bigint)                                                      AS iban_exact_window_seconds,
                      30                                                                               AS date_full_weight_grace_days,
                      0.10                                                                             AS edge_weight_small,
                      sqrt(2::numeric * ln(1.0 / 0.10))                                                AS gauss_k,
                      5                                                                                AS cap_precise,
                      10                                                                               AS cap_broad,
                      3                                                                                AS cap_window_only,
                      5                                                                                AS cap_iban_exact,
                      20000                                                                            AS max_transactions_to_scan,
                      true                                                                             AS directional_matching,
                      cfg.config_min_confidence                                                        AS min_confidence
               FROM cfg),
     weights_json AS (SELECT COALESCE(jsonb_object_agg(s.k_norm, s.v_norm),
                                      jsonb_build_object('amount_total', 1, 'invoice_number', 1, 'order_number', 1,
                                                         'date', 1, 'currency', 1, 'merchant_id', 1, 'customer_name',
                                                         1)) AS wj
                      FROM (SELECT lower(TRIM(BOTH FROM w.weight_key)) AS k_norm,
                                   w.weight_value                      AS v_norm
                            FROM reconciliations_weight_registry w
                            WHERE w.active
                              AND w.merchant_id IS NULL
                              AND w.company_id IS NULL
                              AND (lower(TRIM(BOTH FROM w.weight_key)) = ANY
                                   (ARRAY ['amount_total'::text, 'invoice_number'::text, 'order_number'::text, 'date'::text, 'currency'::text, 'merchant_id'::text, 'customer_name'::text]))) s),
     document_scope AS MATERIALIZED (SELECT d.document_id,
                                            d.company_id,
                                            d.total,
                                            d.currency,
                                            NULLIF(d.invoice_number, ''::text)                                  AS invoice_number_raw,
                                            NULLIF(d.order_id, ''::text)                                        AS order_id_raw,
                                            NULLIF(norm_code(d.invoice_number), ''::text)                       AS invoice_number_norm,
                                            NULLIF(norm_code(d.order_id), ''::text)                             AS order_id_norm,
                                            d.document_date_unix::bigint                                        AS document_date_unix,
                                            d.merchant_id,
                                            d.customer_name,
                                            NULLIF(
                                                    norm_core_ns(COALESCE(d.merchant_name, d.merchant_info::jsonb ->> 'name'::text)),
                                                    ''::text)                                                   AS doc_merchant_ns,
                                            NULLIF(norm_text_ns(concat_ws(' '::text, d.customer_name,
                                                                          d.customer_info::jsonb ->> 'name'::text,
                                                                          d.customer_info::jsonb ->>
                                                                          'contact_person'::text,
                                                                          d.customer_info::jsonb ->>
                                                                          'company_name'::text,
                                                                          d.customer_info::jsonb ->> 'email'::text)),
                                                   ''::text)                                                    AS doc_customer_ns,
                                            NULLIF(norm_iban(replace(
                                                    COALESCE(d.merchant_info::jsonb ->> 'iban'::text, ''::text),
                                                    'O'::text, '0'::text)),
                                                   ''::text)                                                    AS doc_iban_ns,
                                            NULLIF(norm_iban_core(replace(
                                                    COALESCE(d.merchant_info::jsonb ->> 'iban'::text, ''::text),
                                                    'O'::text, '0'::text)),
                                                   ''::text)                                                    AS doc_iban_core_ns,
                                            COALESCE(d.customer_name, d.customer_info::jsonb ->> 'name'::text,
                                                     d.customer_info::jsonb ->> 'contact_person'::text,
                                                     d.customer_info::jsonb ->> 'company_name'::text,
                                                     d.customer_info::jsonb ->>
                                                     'email'::text)                                             AS doc_customer_display,
                                            COALESCE(NULLIF(d.dedup_serialized, ''::text),
                                                     d.document_id::text)                                       AS doc_dedup_key
                                     FROM documents_v2 d
                                     WHERE d.reconciled IS NOT TRUE),
     transactions_scope AS MATERIALIZED (SELECT t.transaction_id,
                                                t.company_id,
                                                t.amount,
                                                t.debited,
                                                t.credited,
                                                t.currency,
                                                NULLIF(norm_code(COALESCE(NULLIF(t.invoice_nr, ''::text),
                                                                          (SELECT match.match[1] AS match
                                                                           FROM regexp_matches(
                                                                                        upper(concat_ws(' '::text,
                                                                                                        t.remittance_information,
                                                                                                        t.description,
                                                                                                        t.transfer_reference,
                                                                                                        t.processed_description)),
                                                                                        '(?:\y(?:INV(?:OICE)?|FACTUUR(?:NR|NUMMER)?)\y\s*[-:#_/]*)?((?:\d{6}[-/]\d{2,20}|\d{4}[-/]\d{2,20}|[A-Z]{1,10}[-/]\d{2,20}|[A-Z0-9]{6,}))'::text,
                                                                                        'g'::text) match(match)
                                                                           LIMIT 1))), ''::text)                AS invoice_nr_norm,
                                                NULLIF(norm_code(COALESCE(NULLIF(t.order_id, ''::text),
                                                                          (SELECT match.match[1] AS match
                                                                           FROM regexp_matches(
                                                                                        upper(concat_ws(' '::text,
                                                                                                        t.remittance_information,
                                                                                                        t.description,
                                                                                                        t.transfer_reference,
                                                                                                        t.processed_description)),
                                                                                        '(?:\y(?:ORD(?:ER)?|PO|ORDER)\y\s*[-:#_/]*)?((?:\d{2,20}|[A-Z]{1,10}[-/]\d{2,20}))'::text,
                                                                                        'g'::text) match(match)
                                                                           LIMIT 1))),
                                                       ''::text)                                                AS order_id_norm,
                                                t.completed_date::bigint                                        AS completed_date,
                                                t.merchant_id,
                                                t.name_consumer,
                                                NULLIF(t.credited, 0::numeric) IS NOT NULL                      AS tx_is_in,
                                                NULLIF(t.debited, 0::numeric) IS NOT NULL                       AS tx_is_out,
                                                NULLIF(norm_core_ns(COALESCE(t.name_beneficiary,
                                                                             t.account_holder_name_counterparty,
                                                                             t.bank_counterparty)),
                                                       ''::text)                                                AS tx_merchant_ns,
                                                NULLIF(norm_text_ns(COALESCE(t.name_consumer, t.account_holder_name,
                                                                             t.account_holder_name_counterparty)),
                                                       ''::text)                                                AS tx_customer_ns,
                                                NULLIF(norm_iban(replace(COALESCE(t.iban_beneficiary, t.iban, ''::text),
                                                                         'O'::text, '0'::text)),
                                                       ''::text)                                                AS tx_iban_beneficiary_ns,
                                                NULLIF(norm_iban(replace(COALESCE(t.iban_counterparty, ''::text),
                                                                         'O'::text, '0'::text)),
                                                       ''::text)                                                AS tx_iban_counterparty_ns,
                                                NULLIF(norm_iban_core(replace(
                                                        COALESCE(t.iban_beneficiary, t.iban, ''::text), 'O'::text,
                                                        '0'::text)),
                                                       ''::text)                                                AS tx_iban_beneficiary_core_ns,
                                                NULLIF(norm_iban_core(replace(COALESCE(t.iban_counterparty, ''::text),
                                                                              'O'::text, '0'::text)),
                                                       ''::text)                                                AS tx_iban_counterparty_core_ns,
                                                COALESCE(t.name_consumer, t.account_holder_name,
                                                         t.account_holder_name_counterparty)                    AS tx_customer_display,
                                                CASE
                                                    WHEN t.debited IS NOT NULL AND t.debited > 0::numeric THEN t.debited
                                                    WHEN t.credited IS NOT NULL AND t.credited > 0::numeric
                                                        THEN t.credited
                                                    WHEN t.amount IS NOT NULL THEN abs(t.amount)
                                                    ELSE NULL::numeric
                                                    END                                                         AS effective_amount
                                         FROM transactions t
                                         WHERE t.reconciled IS NOT TRUE
                                           AND (t.invoice_nr IS NOT NULL OR t.order_id IS NOT NULL OR
                                                t.currency IS NOT NULL AND t.merchant_id IS NOT NULL OR
                                                t.currency IS NOT NULL AND
                                                (t.name_consumer IS NOT NULL OR t.account_holder_name IS NOT NULL OR
                                                 t.account_holder_name_counterparty IS NOT NULL) OR
                                                t.currency IS NOT NULL AND
                                                (t.amount IS NOT NULL OR t.debited IS NOT NULL OR t.credited IS NOT NULL))
                                         ORDER BY t.completed_date DESC, t.transaction_id
                                         LIMIT ((SELECT const.max_transactions_to_scan
                                                 FROM const))),
     s1_invoice AS (SELECT t.transaction_id,
                           d.document_id,
                           'invoice_number'::text AS seed
                    FROM transactions_scope t
                             JOIN document_scope d
                                  ON d.company_id::text = t.company_id AND d.invoice_number_norm IS NOT NULL AND
                                     t.invoice_nr_norm IS NOT NULL AND d.invoice_number_norm = t.invoice_nr_norm AND
                                     d.currency = t.currency),
     s2_order AS (SELECT s.transaction_id,
                         s.document_id,
                         s.seed
                  FROM (SELECT t.transaction_id,
                               d.document_id,
                               'order_number'::text AS seed
                        FROM transactions_scope t
                                 JOIN document_scope d
                                      ON d.company_id::text = t.company_id AND d.order_id_norm IS NOT NULL AND
                                         t.order_id_norm IS NOT NULL AND d.order_id_norm = t.order_id_norm AND
                                         d.currency = t.currency) s
                           LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                      FROM s1_invoice) u USING (transaction_id)
                  WHERE u.transaction_id IS NULL),
     s3_iban_exact_amount_out AS (SELECT t.transaction_id,
                                         d_out.document_id,
                                         'iban_exact_amount_out'::text AS seed
                                  FROM transactions_scope t
                                           LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                      FROM s1_invoice
                                                      UNION ALL
                                                      SELECT DISTINCT s2_order.transaction_id
                                                      FROM s2_order) done USING (transaction_id)
                                           JOIN LATERAL ( SELECT d.document_id
                                                          FROM document_scope d,
                                                               const c
                                                          WHERE d.company_id::text = t.company_id
                                                            AND (NOT c.directional_matching OR t.tx_is_out)
                                                            AND d.currency = t.currency
                                                            AND t.debited IS NOT NULL
                                                            AND t.debited > 0::numeric
                                                            AND d.total = t.debited
                                                            AND d.document_date_unix IS NOT NULL
                                                            AND t.completed_date IS NOT NULL
                                                            AND abs(d.document_date_unix - t.completed_date) <=
                                                                c.iban_exact_window_seconds
                                                            AND (d.doc_iban_ns IS NOT NULL AND
                                                                 t.tx_iban_counterparty_ns IS NOT NULL AND
                                                                 d.doc_iban_ns = t.tx_iban_counterparty_ns OR
                                                                 d.doc_iban_core_ns IS NOT NULL AND
                                                                 t.tx_iban_counterparty_core_ns IS NOT NULL AND
                                                                 d.doc_iban_core_ns = t.tx_iban_counterparty_core_ns)
                                                          ORDER BY (abs(d.document_date_unix - t.completed_date)),
                                                                   d.document_id
                                                          LIMIT ((SELECT const.cap_iban_exact
                                                                  FROM const))) d_out ON true
                                  WHERE done.transaction_id IS NULL),
     s3_iban_exact_amount_in AS (SELECT t.transaction_id,
                                        d_in.document_id,
                                        'iban_exact_amount_in'::text AS seed
                                 FROM transactions_scope t
                                          LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                     FROM s1_invoice
                                                     UNION ALL
                                                     SELECT DISTINCT s2_order.transaction_id
                                                     FROM s2_order) done USING (transaction_id)
                                          JOIN LATERAL ( SELECT d.document_id
                                                         FROM document_scope d,
                                                              const c
                                                         WHERE d.company_id::text = t.company_id
                                                           AND (NOT c.directional_matching OR t.tx_is_in)
                                                           AND d.currency = t.currency
                                                           AND t.credited IS NOT NULL
                                                           AND t.credited > 0::numeric
                                                           AND d.total = t.credited
                                                           AND d.document_date_unix IS NOT NULL
                                                           AND t.completed_date IS NOT NULL
                                                           AND abs(d.document_date_unix - t.completed_date) <=
                                                               c.iban_exact_window_seconds
                                                           AND (d.doc_iban_ns IS NOT NULL AND
                                                                t.tx_iban_beneficiary_ns IS NOT NULL AND
                                                                d.doc_iban_ns = t.tx_iban_beneficiary_ns OR
                                                                d.doc_iban_core_ns IS NOT NULL AND
                                                                t.tx_iban_beneficiary_core_ns IS NOT NULL AND
                                                                d.doc_iban_core_ns = t.tx_iban_beneficiary_core_ns)
                                                         ORDER BY (abs(d.document_date_unix - t.completed_date)),
                                                                  d.document_id
                                                         LIMIT ((SELECT const.cap_iban_exact
                                                                 FROM const))) d_in ON true
                                 WHERE done.transaction_id IS NULL),
     s3_iban_exact_amount AS (SELECT s3_iban_exact_amount_out.transaction_id,
                                     s3_iban_exact_amount_out.document_id,
                                     s3_iban_exact_amount_out.seed
                              FROM s3_iban_exact_amount_out
                              UNION ALL
                              SELECT s3_iban_exact_amount_in.transaction_id,
                                     s3_iban_exact_amount_in.document_id,
                                     s3_iban_exact_amount_in.seed
                              FROM s3_iban_exact_amount_in),
     s4_merchant_id_exact_amount AS (SELECT s.transaction_id,
                                            s.document_id,
                                            s.seed
                                     FROM (SELECT t.transaction_id,
                                                  d.document_id,
                                                  'merchant_id_exact_amount'::text AS seed
                                           FROM transactions_scope t
                                                    JOIN document_scope d ON d.company_id::text = t.company_id AND
                                                                             d.currency = t.currency AND
                                                                             d.merchant_id = t.merchant_id AND
                                                                             t.debited IS NOT NULL AND
                                                                             t.debited > 0::numeric AND
                                                                             d.total = t.debited AND
                                                                             d.document_date_unix IS NOT NULL AND
                                                                             t.completed_date IS NOT NULL AND
                                                                             abs(d.document_date_unix - t.completed_date) <=
                                                                             ((SELECT const.date_window_seconds
                                                                               FROM const))
                                                    CROSS JOIN const c
                                           WHERE NOT c.directional_matching
                                              OR t.tx_is_out) s
                                              LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                         FROM s1_invoice
                                                         UNION ALL
                                                         SELECT DISTINCT s2_order.transaction_id
                                                         FROM s2_order
                                                         UNION ALL
                                                         SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                         FROM s3_iban_exact_amount) done USING (transaction_id)
                                     WHERE done.transaction_id IS NULL),
     s5_merchant_ns_exact_amount AS (SELECT s.transaction_id,
                                            s.document_id,
                                            s.seed
                                     FROM (SELECT t.transaction_id,
                                                  d.document_id,
                                                  'merchant_ns_exact_amount'::text AS seed
                                           FROM transactions_scope t
                                                    JOIN document_scope d ON d.company_id::text = t.company_id AND
                                                                             d.currency = t.currency AND
                                                                             d.doc_merchant_ns IS NOT NULL AND
                                                                             t.tx_merchant_ns IS NOT NULL AND
                                                                             (d.doc_merchant_ns = t.tx_merchant_ns OR
                                                                              POSITION((d.doc_merchant_ns) IN (t.tx_merchant_ns)) >
                                                                              0 OR
                                                                              POSITION((t.tx_merchant_ns) IN (d.doc_merchant_ns)) >
                                                                              0) AND t.debited IS NOT NULL AND
                                                                             t.debited > 0::numeric AND
                                                                             d.total = t.debited AND
                                                                             d.document_date_unix IS NOT NULL AND
                                                                             t.completed_date IS NOT NULL AND
                                                                             abs(d.document_date_unix - t.completed_date) <=
                                                                             ((SELECT const.date_window_seconds
                                                                               FROM const))
                                                    CROSS JOIN const c
                                           WHERE NOT c.directional_matching
                                              OR t.tx_is_out) s
                                              LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                         FROM s1_invoice
                                                         UNION ALL
                                                         SELECT DISTINCT s2_order.transaction_id
                                                         FROM s2_order
                                                         UNION ALL
                                                         SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                         FROM s3_iban_exact_amount
                                                         UNION ALL
                                                         SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                         FROM s4_merchant_id_exact_amount) done USING (transaction_id)
                                     WHERE done.transaction_id IS NULL),
     s6_customer_ns_exact_amount AS (SELECT s.transaction_id,
                                            s.document_id,
                                            s.seed
                                     FROM (SELECT t.transaction_id,
                                                  d.document_id,
                                                  'customer_ns_exact_amount'::text AS seed
                                           FROM transactions_scope t
                                                    JOIN document_scope d ON d.company_id::text = t.company_id AND
                                                                             d.currency = t.currency AND
                                                                             d.doc_customer_ns IS NOT NULL AND
                                                                             t.tx_customer_ns IS NOT NULL AND
                                                                             (d.doc_customer_ns = t.tx_customer_ns OR
                                                                              POSITION((d.doc_customer_ns) IN (t.tx_customer_ns)) >
                                                                              0 OR
                                                                              POSITION((t.tx_customer_ns) IN (d.doc_customer_ns)) >
                                                                              0) AND t.credited IS NOT NULL AND
                                                                             t.credited > 0::numeric AND
                                                                             d.total = t.credited AND
                                                                             d.document_date_unix IS NOT NULL AND
                                                                             t.completed_date IS NOT NULL AND
                                                                             abs(d.document_date_unix - t.completed_date) <=
                                                                             ((SELECT const.date_window_seconds
                                                                               FROM const))
                                                    CROSS JOIN const c
                                           WHERE NOT c.directional_matching
                                              OR t.tx_is_in) s
                                              LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                         FROM s1_invoice
                                                         UNION ALL
                                                         SELECT DISTINCT s2_order.transaction_id
                                                         FROM s2_order
                                                         UNION ALL
                                                         SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                         FROM s3_iban_exact_amount
                                                         UNION ALL
                                                         SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                         FROM s4_merchant_id_exact_amount
                                                         UNION ALL
                                                         SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                                         FROM s5_merchant_ns_exact_amount) done USING (transaction_id)
                                     WHERE done.transaction_id IS NULL),
     s7_iban_plus_signal AS (SELECT t.transaction_id,
                                    d_sub.document_id,
                                    'iban_plus_signal'::text AS seed
                             FROM transactions_scope t
                                      LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                 FROM s1_invoice
                                                 UNION ALL
                                                 SELECT DISTINCT s2_order.transaction_id
                                                 FROM s2_order
                                                 UNION ALL
                                                 SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                 FROM s3_iban_exact_amount
                                                 UNION ALL
                                                 SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                 FROM s4_merchant_id_exact_amount
                                                 UNION ALL
                                                 SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                                 FROM s5_merchant_ns_exact_amount
                                                 UNION ALL
                                                 SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                                 FROM s6_customer_ns_exact_amount) done USING (transaction_id)
                                      JOIN LATERAL ( SELECT d.document_id
                                                     FROM document_scope d,
                                                          const c
                                                     WHERE d.company_id::text = t.company_id
                                                       AND d.currency = t.currency
                                                       AND d.document_date_unix IS NOT NULL
                                                       AND t.completed_date IS NOT NULL
                                                       AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                       AND ((NOT c.directional_matching OR t.tx_is_out) AND
                                                            (d.doc_iban_ns IS NOT NULL AND
                                                             t.tx_iban_counterparty_ns IS NOT NULL AND
                                                             d.doc_iban_ns = t.tx_iban_counterparty_ns OR
                                                             d.doc_iban_core_ns IS NOT NULL AND
                                                             t.tx_iban_counterparty_core_ns IS NOT NULL AND
                                                             d.doc_iban_core_ns = t.tx_iban_counterparty_core_ns) OR
                                                            (NOT c.directional_matching OR t.tx_is_in) AND
                                                            (d.doc_iban_ns IS NOT NULL AND
                                                             t.tx_iban_beneficiary_ns IS NOT NULL AND
                                                             d.doc_iban_ns = t.tx_iban_beneficiary_ns OR
                                                             d.doc_iban_core_ns IS NOT NULL AND
                                                             t.tx_iban_beneficiary_core_ns IS NOT NULL AND
                                                             d.doc_iban_core_ns = t.tx_iban_beneficiary_core_ns))
                                                       AND (t.invoice_nr_norm IS NOT NULL AND
                                                            d.invoice_number_norm IS NOT NULL OR
                                                            t.order_id_norm IS NOT NULL AND
                                                            d.order_id_norm IS NOT NULL OR
                                                            t.tx_merchant_ns IS NOT NULL AND
                                                            d.doc_merchant_ns IS NOT NULL OR
                                                            t.tx_customer_ns IS NOT NULL AND
                                                            d.doc_customer_ns IS NOT NULL)
                                                     ORDER BY (abs(d.document_date_unix - t.completed_date)),
                                                              d.document_id
                                                     LIMIT ((SELECT const.cap_precise
                                                             FROM const))) d_sub ON true
                             WHERE done.transaction_id IS NULL),
     s8_merchant_id_tol AS (SELECT t.transaction_id,
                                   d_sub.document_id,
                                   'merchant_id_tol'::text AS seed
                            FROM transactions_scope t
                                     LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                FROM s1_invoice
                                                UNION ALL
                                                SELECT DISTINCT s2_order.transaction_id
                                                FROM s2_order
                                                UNION ALL
                                                SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                FROM s3_iban_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                FROM s4_merchant_id_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                                FROM s5_merchant_ns_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                                FROM s6_customer_ns_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s7_iban_plus_signal.transaction_id
                                                FROM s7_iban_plus_signal) done USING (transaction_id)
                                     JOIN LATERAL ( SELECT d.document_id
                                                    FROM document_scope d,
                                                         const c
                                                    WHERE d.company_id::text = t.company_id
                                                      AND (NOT c.directional_matching OR t.tx_is_out)
                                                      AND d.currency = t.currency
                                                      AND d.merchant_id = t.merchant_id
                                                      AND c.amount_tolerance_fraction > 0::numeric
                                                      AND t.debited IS NOT NULL
                                                      AND t.debited > 0::numeric
                                                      AND d.total >= (t.debited / (1::numeric + c.amount_tolerance_fraction))
                                                      AND d.total <= (t.debited / (1::numeric - c.amount_tolerance_fraction))
                                                      AND d.document_date_unix IS NOT NULL
                                                      AND t.completed_date IS NOT NULL
                                                      AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                    ORDER BY (abs(d.total - t.debited)),
                                                             (abs(d.document_date_unix - t.completed_date))
                                                    LIMIT ((SELECT const.cap_broad
                                                            FROM const))) d_sub ON true
                            WHERE done.transaction_id IS NULL),
     s9_merchant_ns_tol AS (SELECT t.transaction_id,
                                   d_sub.document_id,
                                   'merchant_ns_tol'::text AS seed
                            FROM transactions_scope t
                                     LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                FROM s1_invoice
                                                UNION ALL
                                                SELECT DISTINCT s2_order.transaction_id
                                                FROM s2_order
                                                UNION ALL
                                                SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                FROM s3_iban_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                FROM s4_merchant_id_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                                FROM s5_merchant_ns_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                                FROM s6_customer_ns_exact_amount
                                                UNION ALL
                                                SELECT DISTINCT s7_iban_plus_signal.transaction_id
                                                FROM s7_iban_plus_signal
                                                UNION ALL
                                                SELECT DISTINCT s8_merchant_id_tol.transaction_id
                                                FROM s8_merchant_id_tol) done USING (transaction_id)
                                     JOIN LATERAL ( SELECT d.document_id
                                                    FROM document_scope d,
                                                         const c
                                                    WHERE d.company_id::text = t.company_id
                                                      AND (NOT c.directional_matching OR t.tx_is_out)
                                                      AND d.currency = t.currency
                                                      AND d.doc_merchant_ns IS NOT NULL
                                                      AND t.tx_merchant_ns IS NOT NULL
                                                      AND (d.doc_merchant_ns = t.tx_merchant_ns OR
                                                           POSITION((d.doc_merchant_ns) IN (t.tx_merchant_ns)) > 0 OR
                                                           POSITION((t.tx_merchant_ns) IN (d.doc_merchant_ns)) > 0)
                                                      AND c.amount_tolerance_fraction > 0::numeric
                                                      AND t.debited IS NOT NULL
                                                      AND t.debited > 0::numeric
                                                      AND d.total >= (t.debited / (1::numeric + c.amount_tolerance_fraction))
                                                      AND d.total <= (t.debited / (1::numeric - c.amount_tolerance_fraction))
                                                      AND d.document_date_unix IS NOT NULL
                                                      AND t.completed_date IS NOT NULL
                                                      AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                    ORDER BY (abs(d.total - t.debited)),
                                                             (abs(d.document_date_unix - t.completed_date))
                                                    LIMIT ((SELECT const.cap_broad
                                                            FROM const))) d_sub ON true
                            WHERE done.transaction_id IS NULL),
     s10_customer_ns_tol AS (SELECT t.transaction_id,
                                    d_sub.document_id,
                                    'customer_ns_tol'::text AS seed
                             FROM transactions_scope t
                                      LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                 FROM s1_invoice
                                                 UNION ALL
                                                 SELECT DISTINCT s2_order.transaction_id
                                                 FROM s2_order
                                                 UNION ALL
                                                 SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                 FROM s3_iban_exact_amount
                                                 UNION ALL
                                                 SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                 FROM s4_merchant_id_exact_amount
                                                 UNION ALL
                                                 SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                                 FROM s5_merchant_ns_exact_amount
                                                 UNION ALL
                                                 SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                                 FROM s6_customer_ns_exact_amount
                                                 UNION ALL
                                                 SELECT DISTINCT s7_iban_plus_signal.transaction_id
                                                 FROM s7_iban_plus_signal
                                                 UNION ALL
                                                 SELECT DISTINCT s8_merchant_id_tol.transaction_id
                                                 FROM s8_merchant_id_tol
                                                 UNION ALL
                                                 SELECT DISTINCT s9_merchant_ns_tol.transaction_id
                                                 FROM s9_merchant_ns_tol) done USING (transaction_id)
                                      JOIN LATERAL ( SELECT d.document_id
                                                     FROM document_scope d,
                                                          const c
                                                     WHERE d.company_id::text = t.company_id
                                                       AND (NOT c.directional_matching OR t.tx_is_in)
                                                       AND d.currency = t.currency
                                                       AND d.doc_customer_ns IS NOT NULL
                                                       AND t.tx_customer_ns IS NOT NULL
                                                       AND (d.doc_customer_ns = t.tx_customer_ns OR
                                                            POSITION((d.doc_customer_ns) IN (t.tx_customer_ns)) > 0 OR
                                                            POSITION((t.tx_customer_ns) IN (d.doc_customer_ns)) > 0)
                                                       AND c.amount_tolerance_fraction > 0::numeric
                                                       AND t.credited IS NOT NULL
                                                       AND t.credited > 0::numeric
                                                       AND d.total >= (t.credited / (1::numeric + c.amount_tolerance_fraction))
                                                       AND d.total <= (t.credited / (1::numeric - c.amount_tolerance_fraction))
                                                       AND d.document_date_unix IS NOT NULL
                                                       AND t.completed_date IS NOT NULL
                                                       AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                     ORDER BY (abs(d.total - t.credited)),
                                                              (abs(d.document_date_unix - t.completed_date))
                                                     LIMIT ((SELECT const.cap_broad
                                                             FROM const))) d_sub ON true
                             WHERE done.transaction_id IS NULL),
     s11_iban_tol AS (SELECT t.transaction_id,
                             d_sub.document_id,
                             'iban_tol'::text AS seed
                      FROM transactions_scope t
                               LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                          FROM s1_invoice
                                          UNION ALL
                                          SELECT DISTINCT s2_order.transaction_id
                                          FROM s2_order
                                          UNION ALL
                                          SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                          FROM s3_iban_exact_amount
                                          UNION ALL
                                          SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                          FROM s4_merchant_id_exact_amount
                                          UNION ALL
                                          SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                          FROM s5_merchant_ns_exact_amount
                                          UNION ALL
                                          SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                          FROM s6_customer_ns_exact_amount
                                          UNION ALL
                                          SELECT DISTINCT s7_iban_plus_signal.transaction_id
                                          FROM s7_iban_plus_signal
                                          UNION ALL
                                          SELECT DISTINCT s8_merchant_id_tol.transaction_id
                                          FROM s8_merchant_id_tol
                                          UNION ALL
                                          SELECT DISTINCT s9_merchant_ns_tol.transaction_id
                                          FROM s9_merchant_ns_tol
                                          UNION ALL
                                          SELECT DISTINCT s10_customer_ns_tol.transaction_id
                                          FROM s10_customer_ns_tol) done USING (transaction_id)
                               JOIN LATERAL ( SELECT d.document_id
                                              FROM document_scope d,
                                                   const c
                                              WHERE d.company_id::text = t.company_id
                                                AND d.currency = t.currency
                                                AND d.document_date_unix IS NOT NULL
                                                AND t.completed_date IS NOT NULL
                                                AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                AND c.amount_tolerance_fraction > 0::numeric
                                                AND ((NOT c.directional_matching OR t.tx_is_out) AND
                                                     (d.doc_iban_ns IS NOT NULL AND
                                                      t.tx_iban_counterparty_ns IS NOT NULL AND
                                                      d.doc_iban_ns = t.tx_iban_counterparty_ns OR
                                                      d.doc_iban_core_ns IS NOT NULL AND
                                                      t.tx_iban_counterparty_core_ns IS NOT NULL AND
                                                      d.doc_iban_core_ns = t.tx_iban_counterparty_core_ns) AND
                                                     t.debited IS NOT NULL AND t.debited > 0::numeric AND d.total >=
                                                                                                          (t.debited / (1::numeric + c.amount_tolerance_fraction)) AND
                                                     d.total <=
                                                     (t.debited / (1::numeric - c.amount_tolerance_fraction)) OR
                                                     (NOT c.directional_matching OR t.tx_is_in) AND
                                                     (d.doc_iban_ns IS NOT NULL AND
                                                      t.tx_iban_beneficiary_ns IS NOT NULL AND
                                                      d.doc_iban_ns = t.tx_iban_beneficiary_ns OR
                                                      d.doc_iban_core_ns IS NOT NULL AND
                                                      t.tx_iban_beneficiary_core_ns IS NOT NULL AND
                                                      d.doc_iban_core_ns = t.tx_iban_beneficiary_core_ns) AND
                                                     t.credited IS NOT NULL AND t.credited > 0::numeric AND d.total >=
                                                                                                            (t.credited / (1::numeric + c.amount_tolerance_fraction)) AND
                                                     d.total <=
                                                     (t.credited / (1::numeric - c.amount_tolerance_fraction)))
                                              ORDER BY (COALESCE(abs(d.total - t.debited), abs(d.total - t.credited))),
                                                       (abs(d.document_date_unix - t.completed_date))
                                              LIMIT ((SELECT const.cap_broad
                                                      FROM const))) d_sub ON true
                      WHERE done.transaction_id IS NULL),
     s12_window_amount_exact AS (SELECT t.transaction_id,
                                        d_sub.document_id,
                                        'window_amount_exact'::text AS seed
                                 FROM transactions_scope t
                                          LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                     FROM s1_invoice
                                                     UNION ALL
                                                     SELECT DISTINCT s2_order.transaction_id
                                                     FROM s2_order
                                                     UNION ALL
                                                     SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                     FROM s3_iban_exact_amount
                                                     UNION ALL
                                                     SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                     FROM s4_merchant_id_exact_amount
                                                     UNION ALL
                                                     SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                                     FROM s5_merchant_ns_exact_amount
                                                     UNION ALL
                                                     SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                                     FROM s6_customer_ns_exact_amount
                                                     UNION ALL
                                                     SELECT DISTINCT s7_iban_plus_signal.transaction_id
                                                     FROM s7_iban_plus_signal
                                                     UNION ALL
                                                     SELECT DISTINCT s8_merchant_id_tol.transaction_id
                                                     FROM s8_merchant_id_tol
                                                     UNION ALL
                                                     SELECT DISTINCT s9_merchant_ns_tol.transaction_id
                                                     FROM s9_merchant_ns_tol
                                                     UNION ALL
                                                     SELECT DISTINCT s10_customer_ns_tol.transaction_id
                                                     FROM s10_customer_ns_tol
                                                     UNION ALL
                                                     SELECT DISTINCT s11_iban_tol.transaction_id
                                                     FROM s11_iban_tol) done USING (transaction_id)
                                          JOIN LATERAL ( SELECT d.document_id
                                                         FROM document_scope d,
                                                              const c
                                                         WHERE d.company_id::text = t.company_id
                                                           AND d.currency = t.currency
                                                           AND d.document_date_unix IS NOT NULL
                                                           AND t.completed_date IS NOT NULL
                                                           AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                           AND d.total = COALESCE(NULLIF(t.debited, 0::numeric),
                                                                                  NULLIF(t.credited, 0::numeric),
                                                                                  abs(t.amount))
                                                         ORDER BY (abs(d.document_date_unix - t.completed_date))
                                                         LIMIT ((SELECT const.cap_broad
                                                                 FROM const))) d_sub ON true
                                 WHERE done.transaction_id IS NULL),
     s13_window_amount_tol AS (SELECT t.transaction_id,
                                      d_sub.document_id,
                                      'window_amount_tol'::text AS seed
                               FROM transactions_scope t
                                        LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                                   FROM s1_invoice
                                                   UNION ALL
                                                   SELECT DISTINCT s2_order.transaction_id
                                                   FROM s2_order
                                                   UNION ALL
                                                   SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                                   FROM s3_iban_exact_amount
                                                   UNION ALL
                                                   SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                                   FROM s4_merchant_id_exact_amount
                                                   UNION ALL
                                                   SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                                   FROM s5_merchant_ns_exact_amount
                                                   UNION ALL
                                                   SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                                   FROM s6_customer_ns_exact_amount
                                                   UNION ALL
                                                   SELECT DISTINCT s7_iban_plus_signal.transaction_id
                                                   FROM s7_iban_plus_signal
                                                   UNION ALL
                                                   SELECT DISTINCT s8_merchant_id_tol.transaction_id
                                                   FROM s8_merchant_id_tol
                                                   UNION ALL
                                                   SELECT DISTINCT s9_merchant_ns_tol.transaction_id
                                                   FROM s9_merchant_ns_tol
                                                   UNION ALL
                                                   SELECT DISTINCT s10_customer_ns_tol.transaction_id
                                                   FROM s10_customer_ns_tol
                                                   UNION ALL
                                                   SELECT DISTINCT s11_iban_tol.transaction_id
                                                   FROM s11_iban_tol
                                                   UNION ALL
                                                   SELECT DISTINCT s12_window_amount_exact.transaction_id
                                                   FROM s12_window_amount_exact) done USING (transaction_id)
                                        JOIN LATERAL ( SELECT d.document_id
                                                       FROM document_scope d,
                                                            const c
                                                       WHERE d.company_id::text = t.company_id
                                                         AND d.currency = t.currency
                                                         AND d.document_date_unix IS NOT NULL
                                                         AND t.completed_date IS NOT NULL
                                                         AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                         AND c.amount_tolerance_fraction > 0::numeric
                                                         AND d.total >= (COALESCE(NULLIF(t.debited, 0::numeric),
                                                                                  NULLIF(t.credited, 0::numeric),
                                                                                  abs(t.amount)) /
                                                                         (1::numeric + c.amount_tolerance_fraction))
                                                         AND d.total <= (COALESCE(NULLIF(t.debited, 0::numeric),
                                                                                  NULLIF(t.credited, 0::numeric),
                                                                                  abs(t.amount)) /
                                                                         (1::numeric - c.amount_tolerance_fraction))
                                                       ORDER BY (abs(d.total - COALESCE(NULLIF(t.debited, 0::numeric),
                                                                                        NULLIF(t.credited, 0::numeric),
                                                                                        abs(t.amount)))),
                                                                (abs(d.document_date_unix - t.completed_date))
                                                       LIMIT ((SELECT const.cap_broad
                                                               FROM const))) d_sub ON true
                               WHERE done.transaction_id IS NULL),
     s14_window_only AS (SELECT t.transaction_id,
                                d_sub.document_id,
                                'window_only'::text AS seed
                         FROM transactions_scope t
                                  LEFT JOIN (SELECT DISTINCT s1_invoice.transaction_id
                                             FROM s1_invoice
                                             UNION ALL
                                             SELECT DISTINCT s2_order.transaction_id
                                             FROM s2_order
                                             UNION ALL
                                             SELECT DISTINCT s3_iban_exact_amount.transaction_id
                                             FROM s3_iban_exact_amount
                                             UNION ALL
                                             SELECT DISTINCT s4_merchant_id_exact_amount.transaction_id
                                             FROM s4_merchant_id_exact_amount
                                             UNION ALL
                                             SELECT DISTINCT s5_merchant_ns_exact_amount.transaction_id
                                             FROM s5_merchant_ns_exact_amount
                                             UNION ALL
                                             SELECT DISTINCT s6_customer_ns_exact_amount.transaction_id
                                             FROM s6_customer_ns_exact_amount
                                             UNION ALL
                                             SELECT DISTINCT s7_iban_plus_signal.transaction_id
                                             FROM s7_iban_plus_signal
                                             UNION ALL
                                             SELECT DISTINCT s8_merchant_id_tol.transaction_id
                                             FROM s8_merchant_id_tol
                                             UNION ALL
                                             SELECT DISTINCT s9_merchant_ns_tol.transaction_id
                                             FROM s9_merchant_ns_tol
                                             UNION ALL
                                             SELECT DISTINCT s10_customer_ns_tol.transaction_id
                                             FROM s10_customer_ns_tol
                                             UNION ALL
                                             SELECT DISTINCT s11_iban_tol.transaction_id
                                             FROM s11_iban_tol
                                             UNION ALL
                                             SELECT DISTINCT s12_window_amount_exact.transaction_id
                                             FROM s12_window_amount_exact
                                             UNION ALL
                                             SELECT DISTINCT s13_window_amount_tol.transaction_id
                                             FROM s13_window_amount_tol) done USING (transaction_id)
                                  JOIN LATERAL ( SELECT d.document_id
                                                 FROM document_scope d,
                                                      const c
                                                 WHERE d.company_id::text = t.company_id
                                                   AND d.currency = t.currency
                                                   AND d.document_date_unix IS NOT NULL
                                                   AND t.completed_date IS NOT NULL
                                                   AND abs(d.document_date_unix - t.completed_date) <= c.date_window_seconds
                                                 ORDER BY (abs(d.document_date_unix - t.completed_date))
                                                 LIMIT ((SELECT const.cap_window_only
                                                         FROM const))) d_sub ON true
                         WHERE done.transaction_id IS NULL),
     seeds AS (SELECT s1_invoice.transaction_id,
                      s1_invoice.document_id,
                      s1_invoice.seed
               FROM s1_invoice
               UNION ALL
               SELECT s2_order.transaction_id,
                      s2_order.document_id,
                      s2_order.seed
               FROM s2_order
               UNION ALL
               SELECT s3_iban_exact_amount.transaction_id,
                      s3_iban_exact_amount.document_id,
                      s3_iban_exact_amount.seed
               FROM s3_iban_exact_amount
               UNION ALL
               SELECT s4_merchant_id_exact_amount.transaction_id,
                      s4_merchant_id_exact_amount.document_id,
                      s4_merchant_id_exact_amount.seed
               FROM s4_merchant_id_exact_amount
               UNION ALL
               SELECT s5_merchant_ns_exact_amount.transaction_id,
                      s5_merchant_ns_exact_amount.document_id,
                      s5_merchant_ns_exact_amount.seed
               FROM s5_merchant_ns_exact_amount
               UNION ALL
               SELECT s6_customer_ns_exact_amount.transaction_id,
                      s6_customer_ns_exact_amount.document_id,
                      s6_customer_ns_exact_amount.seed
               FROM s6_customer_ns_exact_amount
               UNION ALL
               SELECT s7_iban_plus_signal.transaction_id,
                      s7_iban_plus_signal.document_id,
                      s7_iban_plus_signal.seed
               FROM s7_iban_plus_signal
               UNION ALL
               SELECT s8_merchant_id_tol.transaction_id,
                      s8_merchant_id_tol.document_id,
                      s8_merchant_id_tol.seed
               FROM s8_merchant_id_tol
               UNION ALL
               SELECT s9_merchant_ns_tol.transaction_id,
                      s9_merchant_ns_tol.document_id,
                      s9_merchant_ns_tol.seed
               FROM s9_merchant_ns_tol
               UNION ALL
               SELECT s10_customer_ns_tol.transaction_id,
                      s10_customer_ns_tol.document_id,
                      s10_customer_ns_tol.seed
               FROM s10_customer_ns_tol
               UNION ALL
               SELECT s11_iban_tol.transaction_id,
                      s11_iban_tol.document_id,
                      s11_iban_tol.seed
               FROM s11_iban_tol
               UNION ALL
               SELECT s12_window_amount_exact.transaction_id,
                      s12_window_amount_exact.document_id,
                      s12_window_amount_exact.seed
               FROM s12_window_amount_exact
               UNION ALL
               SELECT s13_window_amount_tol.transaction_id,
                      s13_window_amount_tol.document_id,
                      s13_window_amount_tol.seed
               FROM s13_window_amount_tol
               UNION ALL
               SELECT s14_window_only.transaction_id,
                      s14_window_only.document_id,
                      s14_window_only.seed
               FROM s14_window_only),
     candidate_pairs AS (SELECT DISTINCT seeds.transaction_id,
                                         seeds.document_id
                         FROM seeds),
     attributes AS (SELECT p.transaction_id,
                           p.document_id,
                           t.company_id::uuid                                               AS transaction_company_id,
                           d.company_id                                                     AS document_company_id,
                           t.amount                                                         AS transaction_amount,
                           t.debited                                                        AS transaction_debited,
                           t.credited                                                       AS transaction_credited,
                           d.total                                                          AS document_total,
                           t.currency                                                       AS transaction_currency,
                           d.currency                                                       AS document_currency,
                           t.invoice_nr_norm                                                AS tx_invoice_norm,
                           d.invoice_number_norm                                            AS doc_invoice_norm,
                           t.order_id_norm                                                  AS tx_order_norm,
                           d.order_id_norm                                                  AS doc_order_norm,
                           d.invoice_number_raw                                             AS doc_invoice_raw,
                           d.order_id_raw                                                   AS doc_order_raw,
                           t.completed_date                                                 AS transaction_date_unix,
                           d.document_date_unix,
                           t.merchant_id                                                    AS transaction_merchant_id,
                           d.merchant_id                                                    AS document_merchant_id,
                           t.tx_customer_display                                            AS transaction_customer_name,
                           d.doc_customer_display                                           AS document_customer_name,
                           NULLIF(t.credited, 0::numeric) IS NOT NULL                       AS tx_is_in,
                           NULLIF(t.debited, 0::numeric) IS NOT NULL                        AS tx_is_out,
                           t.tx_merchant_ns,
                           d.doc_merchant_ns,
                           t.tx_customer_ns,
                           d.doc_customer_ns,
                           t.tx_iban_beneficiary_ns,
                           t.tx_iban_counterparty_ns,
                           d.doc_iban_ns,
                           t.tx_iban_beneficiary_core_ns,
                           t.tx_iban_counterparty_core_ns,
                           d.doc_iban_core_ns,
                           COALESCE(NULLIF(d.doc_dedup_key, ''::text), d.document_id::text) AS doc_dedup_key,
                           t.effective_amount                                               AS transaction_effective_amount
                    FROM candidate_pairs p
                             JOIN transactions_scope t ON t.transaction_id = p.transaction_id
                             JOIN document_scope d ON d.document_id = p.document_id
                    WHERE d.company_id::text = t.company_id),
     evaluated AS (SELECT a.transaction_id,
                          a.document_id,
                          a.transaction_effective_amount,
                          COALESCE(a.tx_invoice_norm IS NOT NULL AND a.doc_invoice_norm IS NOT NULL AND
                                   a.tx_invoice_norm = a.doc_invoice_norm, false)       AS invoice_number_match,
                          COALESCE(a.tx_order_norm IS NOT NULL AND a.doc_order_norm IS NOT NULL AND
                                   a.tx_order_norm = a.doc_order_norm, false)           AS order_number_match,
                          COALESCE(a.transaction_currency = a.document_currency, false) AS currency_match,
                          CASE
                              WHEN (SELECT const.directional_matching
                                    FROM const) THEN a.tx_is_out AND (a.transaction_merchant_id IS NOT NULL AND
                                                                      a.document_merchant_id IS NOT NULL AND
                                                                      a.transaction_merchant_id =
                                                                      a.document_merchant_id OR
                                                                      a.tx_merchant_ns IS NOT NULL AND
                                                                      a.doc_merchant_ns IS NOT NULL AND
                                                                      (a.tx_merchant_ns = a.doc_merchant_ns OR
                                                                       POSITION((a.doc_merchant_ns) IN (a.tx_merchant_ns)) >
                                                                       0 OR
                                                                       POSITION((a.tx_merchant_ns) IN (a.doc_merchant_ns)) >
                                                                       0) OR a.tx_iban_counterparty_ns IS NOT NULL AND
                                                                             a.doc_iban_ns IS NOT NULL AND
                                                                             a.tx_iban_counterparty_ns =
                                                                             a.doc_iban_ns OR
                                                                      a.tx_iban_counterparty_core_ns IS NOT NULL AND
                                                                      a.doc_iban_core_ns IS NOT NULL AND
                                                                      a.tx_iban_counterparty_core_ns =
                                                                      a.doc_iban_core_ns)
                              ELSE a.transaction_merchant_id IS NOT NULL AND a.document_merchant_id IS NOT NULL AND
                                   a.transaction_merchant_id = a.document_merchant_id OR
                                   a.tx_merchant_ns IS NOT NULL AND a.doc_merchant_ns IS NOT NULL AND
                                   (a.tx_merchant_ns = a.doc_merchant_ns OR
                                    POSITION((a.doc_merchant_ns) IN (a.tx_merchant_ns)) > 0 OR
                                    POSITION((a.tx_merchant_ns) IN (a.doc_merchant_ns)) > 0) OR
                                   a.tx_iban_counterparty_ns IS NOT NULL AND a.doc_iban_ns IS NOT NULL AND
                                   a.tx_iban_counterparty_ns = a.doc_iban_ns OR
                                   a.tx_iban_counterparty_core_ns IS NOT NULL AND a.doc_iban_core_ns IS NOT NULL AND
                                   a.tx_iban_counterparty_core_ns = a.doc_iban_core_ns
                              END                                                       AS merchant_match,
                          CASE
                              WHEN (SELECT const.directional_matching
                                    FROM const) THEN a.tx_is_in AND
                                                     (a.tx_customer_ns IS NOT NULL AND a.doc_customer_ns IS NOT NULL AND
                                                      (a.tx_customer_ns = a.doc_customer_ns OR
                                                       POSITION((a.doc_customer_ns) IN (a.tx_customer_ns)) > 0 OR
                                                       POSITION((a.tx_customer_ns) IN (a.doc_customer_ns)) > 0) OR
                                                      a.tx_iban_beneficiary_ns IS NOT NULL AND
                                                      a.doc_iban_ns IS NOT NULL AND
                                                      a.tx_iban_beneficiary_ns = a.doc_iban_ns OR
                                                      a.tx_iban_beneficiary_core_ns IS NOT NULL AND
                                                      a.doc_iban_core_ns IS NOT NULL AND
                                                      a.tx_iban_beneficiary_core_ns = a.doc_iban_core_ns)
                              ELSE a.tx_customer_ns IS NOT NULL AND a.doc_customer_ns IS NOT NULL AND
                                   (a.tx_customer_ns = a.doc_customer_ns OR
                                    POSITION((a.doc_customer_ns) IN (a.tx_customer_ns)) > 0 OR
                                    POSITION((a.tx_customer_ns) IN (a.doc_customer_ns)) > 0) OR
                                   a.tx_iban_beneficiary_ns IS NOT NULL AND a.doc_iban_ns IS NOT NULL AND
                                   a.tx_iban_beneficiary_ns = a.doc_iban_ns OR
                                   a.tx_iban_beneficiary_core_ns IS NOT NULL AND a.doc_iban_core_ns IS NOT NULL AND
                                   a.tx_iban_beneficiary_core_ns = a.doc_iban_core_ns
                              END                                                       AS customer_match,
                          COALESCE(abs(a.transaction_date_unix - a.document_date_unix) <=
                                   ((SELECT const.date_window_seconds
                                     FROM const)), false)                               AS date_within_cap,
                          a.document_total,
                          a.document_company_id,
                          a.transaction_date_unix,
                          a.document_date_unix,
                          a.doc_dedup_key,
                          a.transaction_debited IS NOT NULL AND a.transaction_debited > 0::numeric AND
                          a.document_total = a.transaction_debited                      AS amount_exact_from_debited,
                          a.transaction_credited IS NOT NULL AND a.transaction_credited > 0::numeric AND
                          a.document_total = a.transaction_credited                     AS amount_exact_from_credited,
                          a.transaction_debited IS NOT NULL AND a.transaction_debited > 0::numeric AND
                          abs(a.document_total - a.transaction_debited) <=
                          (a.document_total * ((SELECT const.amount_tolerance_fraction
                                                FROM const)))                           AS amount_tolerance_from_debited,
                          a.transaction_credited IS NOT NULL AND a.transaction_credited > 0::numeric AND
                          abs(a.document_total - a.transaction_credited) <=
                          (a.document_total * ((SELECT const.amount_tolerance_fraction
                                                FROM const)))                           AS amount_tolerance_from_credited,
                          a.transaction_effective_amount IS NOT NULL AND
                          a.document_total = a.transaction_effective_amount             AS amount_exact_from_effective,
                          a.transaction_effective_amount IS NOT NULL AND
                          abs(a.document_total - a.transaction_effective_amount) <=
                          (a.document_total * ((SELECT const.amount_tolerance_fraction
                                                FROM const)))                           AS amount_tolerance_from_effective,
                          a.tx_is_in,
                          a.tx_is_out,
                          a.tx_iban_beneficiary_ns,
                          a.tx_iban_counterparty_ns,
                          a.doc_iban_ns,
                          a.tx_iban_beneficiary_core_ns,
                          a.tx_iban_counterparty_core_ns,
                          a.doc_iban_core_ns,
                          a.tx_merchant_ns,
                          a.doc_merchant_ns,
                          a.tx_customer_ns,
                          a.doc_customer_ns,
                          a.transaction_merchant_id,
                          a.document_merchant_id
                   FROM attributes a),
     with_amount_tolerance AS (SELECT e_1.transaction_id,
                                      e_1.document_id,
                                      e_1.transaction_effective_amount,
                                      e_1.invoice_number_match,
                                      e_1.order_number_match,
                                      e_1.currency_match,
                                      e_1.merchant_match,
                                      e_1.customer_match,
                                      e_1.date_within_cap,
                                      e_1.document_total,
                                      e_1.document_company_id,
                                      e_1.transaction_date_unix,
                                      e_1.document_date_unix,
                                      e_1.doc_dedup_key,
                                      e_1.amount_exact_from_debited,
                                      e_1.amount_exact_from_credited,
                                      e_1.amount_tolerance_from_debited,
                                      e_1.amount_tolerance_from_credited,
                                      e_1.amount_exact_from_effective,
                                      e_1.amount_tolerance_from_effective,
                                      e_1.tx_is_in,
                                      e_1.tx_is_out,
                                      e_1.tx_iban_beneficiary_ns,
                                      e_1.tx_iban_counterparty_ns,
                                      e_1.doc_iban_ns,
                                      e_1.tx_iban_beneficiary_core_ns,
                                      e_1.tx_iban_counterparty_core_ns,
                                      e_1.doc_iban_core_ns,
                                      e_1.tx_merchant_ns,
                                      e_1.doc_merchant_ns,
                                      e_1.tx_customer_ns,
                                      e_1.doc_customer_ns,
                                      e_1.transaction_merchant_id,
                                      e_1.document_merchant_id,
                                      CASE
                                          WHEN (SELECT const.directional_matching
                                                FROM const) AND e_1.tx_is_out THEN e_1.amount_exact_from_debited
                                          WHEN (SELECT const.directional_matching
                                                FROM const) AND e_1.tx_is_in THEN e_1.amount_exact_from_credited
                                          ELSE COALESCE(e_1.amount_exact_from_debited, false) OR
                                               COALESCE(e_1.amount_exact_from_credited, false) OR
                                               COALESCE(e_1.amount_exact_from_effective, false)
                                          END                                              AS amount_exact_match,
                                      CASE
                                          WHEN ((SELECT const.amount_tolerance_fraction
                                                 FROM const)) > 0::numeric THEN
                                              CASE
                                                  WHEN (SELECT const.directional_matching
                                                        FROM const) AND e_1.tx_is_out
                                                      THEN e_1.amount_tolerance_from_debited
                                                  WHEN (SELECT const.directional_matching
                                                        FROM const) AND e_1.tx_is_in
                                                      THEN e_1.amount_tolerance_from_credited
                                                  ELSE COALESCE(e_1.amount_tolerance_from_debited, false) OR
                                                       COALESCE(e_1.amount_tolerance_from_credited, false) OR
                                                       COALESCE(e_1.amount_tolerance_from_effective, false)
                                                  END
                                          ELSE false
                                          END                                              AS amount_tolerance_match,
                                      COALESCE(e_1.amount_exact_from_debited, false) OR
                                      COALESCE(e_1.amount_exact_from_credited, false) OR
                                      COALESCE(e_1.amount_tolerance_from_debited, false) OR
                                      COALESCE(e_1.amount_tolerance_from_credited, false) OR
                                      COALESCE(e_1.amount_exact_from_effective, false) OR
                                      COALESCE(e_1.amount_tolerance_from_effective, false) AS amount_direction_ok
                               FROM evaluated e_1),
     resolved_weights
         AS (SELECT COALESCE((weights_json.wj ->> 'amount_total'::text)::numeric, 0::numeric)   AS amount_weight,
                    COALESCE((weights_json.wj ->> 'invoice_number'::text)::numeric, 0::numeric) AS invoice_weight,
                    COALESCE((weights_json.wj ->> 'order_number'::text)::numeric, 0::numeric)   AS order_weight,
                    COALESCE((weights_json.wj ->> 'date'::text)::numeric, 0::numeric)           AS date_weight,
                    COALESCE((weights_json.wj ->> 'currency'::text)::numeric, 0::numeric)       AS currency_weight,
                    COALESCE((weights_json.wj ->> 'merchant_id'::text)::numeric, 0::numeric)    AS merchant_weight,
                    COALESCE((weights_json.wj ->> 'customer_name'::text)::numeric, 0::numeric)  AS customer_weight
             FROM weights_json),
     scored_components AS (SELECT m.transaction_id,
                                  m.document_id,
                                  m.transaction_effective_amount,
                                  m.invoice_number_match,
                                  m.order_number_match,
                                  m.currency_match,
                                  m.merchant_match,
                                  m.customer_match,
                                  m.date_within_cap,
                                  m.document_total,
                                  m.document_company_id,
                                  m.transaction_date_unix,
                                  m.document_date_unix,
                                  m.doc_dedup_key,
                                  m.amount_exact_from_debited,
                                  m.amount_exact_from_credited,
                                  m.amount_tolerance_from_debited,
                                  m.amount_tolerance_from_credited,
                                  m.amount_exact_from_effective,
                                  m.amount_tolerance_from_effective,
                                  m.tx_is_in,
                                  m.tx_is_out,
                                  m.tx_iban_beneficiary_ns,
                                  m.tx_iban_counterparty_ns,
                                  m.doc_iban_ns,
                                  m.tx_iban_beneficiary_core_ns,
                                  m.tx_iban_counterparty_core_ns,
                                  m.doc_iban_core_ns,
                                  m.tx_merchant_ns,
                                  m.doc_merchant_ns,
                                  m.tx_customer_ns,
                                  m.doc_customer_ns,
                                  m.transaction_merchant_id,
                                  m.document_merchant_id,
                                  m.amount_exact_match,
                                  m.amount_tolerance_match,
                                  m.amount_direction_ok,
                                  COALESCE(
                                          CASE
                                              WHEN m.amount_exact_match THEN w.amount_weight
                                              WHEN m.amount_tolerance_match THEN w.amount_weight * exp('-0.5'::numeric *
                                                                                                       power(
                                                                                                               abs(m.transaction_effective_amount - m.document_total) /
                                                                                                               NULLIF(
                                                                                                                       m.document_total *
                                                                                                                       ((SELECT const.amount_tolerance_fraction
                                                                                                                         FROM const)) /
                                                                                                                       ((SELECT const.gauss_k
                                                                                                                         FROM const)),
                                                                                                                       0::numeric),
                                                                                                               2::numeric))
                                              ELSE 0::numeric
                                              END, 0::numeric)                                             AS amount_score,
                                  m.amount_exact_match OR m.amount_tolerance_match                         AS amount_match,
                                  COALESCE(m.invoice_number_match::integer, 0)::numeric *
                                  w.invoice_weight                                                         AS invoice_score,
                                  COALESCE(m.order_number_match::integer, 0)::numeric *
                                  w.order_weight                                                           AS order_score,
                                  COALESCE(m.currency_match::integer, 0)::numeric * w.currency_weight      AS currency_score,
                                  COALESCE(m.merchant_match::integer, 0)::numeric * w.merchant_weight      AS merchant_score,
                                  COALESCE(m.customer_match::integer, 0)::numeric * w.customer_weight      AS customer_score,
                                  w.amount_weight,
                                  w.invoice_weight,
                                  w.order_weight,
                                  w.date_weight,
                                  w.currency_weight,
                                  w.merchant_weight,
                                  w.customer_weight,
                                  CASE
                                      WHEN m.transaction_date_unix IS NOT NULL AND m.document_date_unix IS NOT NULL THEN
                                          w.date_weight * exp('-0.5'::numeric * power(GREATEST(0::numeric,
                                                                                               abs(m.transaction_date_unix - m.document_date_unix)::numeric /
                                                                                               86400.0 -
                                                                                               ((SELECT const.date_full_weight_grace_days
                                                                                                 FROM const))::numeric) /
                                                                                      NULLIF(GREATEST(1.0,
                                                                                                      (((SELECT cfg.config_date_window_days
                                                                                                         FROM cfg)) -
                                                                                                       ((SELECT const.date_full_weight_grace_days
                                                                                                         FROM const))::numeric) /
                                                                                                      ((SELECT const.gauss_k
                                                                                                        FROM const))),
                                                                                             0::numeric), 2::numeric))
                                      ELSE 0::numeric
                                      END                                                                  AS date_score,
                                  m.date_within_cap                                                        AS date_match
                           FROM with_amount_tolerance m
                                    CROSS JOIN resolved_weights w),
     scored_with_applicability AS (SELECT s.transaction_id,
                                          s.document_id,
                                          s.transaction_effective_amount,
                                          s.invoice_number_match,
                                          s.order_number_match,
                                          s.currency_match,
                                          s.merchant_match,
                                          s.customer_match,
                                          s.date_within_cap,
                                          s.document_total,
                                          s.document_company_id,
                                          s.transaction_date_unix,
                                          s.document_date_unix,
                                          s.doc_dedup_key,
                                          s.amount_exact_from_debited,
                                          s.amount_exact_from_credited,
                                          s.amount_tolerance_from_debited,
                                          s.amount_tolerance_from_credited,
                                          s.amount_exact_from_effective,
                                          s.amount_tolerance_from_effective,
                                          s.tx_is_in,
                                          s.tx_is_out,
                                          s.tx_iban_beneficiary_ns,
                                          s.tx_iban_counterparty_ns,
                                          s.doc_iban_ns,
                                          s.tx_iban_beneficiary_core_ns,
                                          s.tx_iban_counterparty_core_ns,
                                          s.doc_iban_core_ns,
                                          s.tx_merchant_ns,
                                          s.doc_merchant_ns,
                                          s.tx_customer_ns,
                                          s.doc_customer_ns,
                                          s.transaction_merchant_id,
                                          s.document_merchant_id,
                                          s.amount_exact_match,
                                          s.amount_tolerance_match,
                                          s.amount_direction_ok,
                                          s.amount_score,
                                          s.amount_match,
                                          s.invoice_score,
                                          s.order_score,
                                          s.currency_score,
                                          s.merchant_score,
                                          s.customer_score,
                                          s.amount_weight,
                                          s.invoice_weight,
                                          s.order_weight,
                                          s.date_weight,
                                          s.currency_weight,
                                          s.merchant_weight,
                                          s.customer_weight,
                                          s.date_score,
                                          s.date_match,
                                          CASE
                                              WHEN (SELECT const.directional_matching
                                                    FROM const) THEN s.tx_is_out AND (s.doc_merchant_ns IS NOT NULL AND
                                                                                      s.tx_merchant_ns IS NOT NULL OR
                                                                                      s.doc_iban_ns IS NOT NULL AND
                                                                                      s.tx_iban_counterparty_ns IS NOT NULL AND
                                                                                      s.doc_iban_ns =
                                                                                      s.tx_iban_counterparty_ns OR
                                                                                      s.doc_iban_core_ns IS NOT NULL AND
                                                                                      s.tx_iban_counterparty_core_ns IS NOT NULL AND
                                                                                      s.doc_iban_core_ns =
                                                                                      s.tx_iban_counterparty_core_ns OR
                                                                                      s.document_merchant_id IS NOT NULL AND
                                                                                      s.transaction_merchant_id IS NOT NULL)
                                              ELSE s.doc_merchant_ns IS NOT NULL AND s.tx_merchant_ns IS NOT NULL OR
                                                   s.doc_iban_ns IS NOT NULL AND
                                                   s.tx_iban_counterparty_ns IS NOT NULL AND
                                                   s.doc_iban_ns = s.tx_iban_counterparty_ns OR
                                                   s.doc_iban_core_ns IS NOT NULL AND
                                                   s.tx_iban_counterparty_core_ns IS NOT NULL AND
                                                   s.doc_iban_core_ns = s.tx_iban_counterparty_core_ns OR
                                                   s.document_merchant_id IS NOT NULL AND
                                                   s.transaction_merchant_id IS NOT NULL
                                              END AS merchant_applicable,
                                          CASE
                                              WHEN (SELECT const.directional_matching
                                                    FROM const) THEN s.tx_is_in AND (s.doc_customer_ns IS NOT NULL AND
                                                                                     s.tx_customer_ns IS NOT NULL OR
                                                                                     s.doc_iban_ns IS NOT NULL AND
                                                                                     s.tx_iban_beneficiary_ns IS NOT NULL AND
                                                                                     s.doc_iban_ns =
                                                                                     s.tx_iban_beneficiary_ns OR
                                                                                     s.doc_iban_core_ns IS NOT NULL AND
                                                                                     s.tx_iban_beneficiary_core_ns IS NOT NULL AND
                                                                                     s.doc_iban_core_ns =
                                                                                     s.tx_iban_beneficiary_core_ns)
                                              ELSE s.doc_customer_ns IS NOT NULL AND s.tx_customer_ns IS NOT NULL OR
                                                   s.doc_iban_ns IS NOT NULL AND
                                                   s.tx_iban_beneficiary_ns IS NOT NULL AND
                                                   s.doc_iban_ns = s.tx_iban_beneficiary_ns OR
                                                   s.doc_iban_core_ns IS NOT NULL AND
                                                   s.tx_iban_beneficiary_core_ns IS NOT NULL AND
                                                   s.doc_iban_core_ns = s.tx_iban_beneficiary_core_ns
                                              END AS customer_applicable
                                   FROM scored_components s),
     scored_with_confidence AS (SELECT s.transaction_id,
                                       s.document_id,
                                       s.transaction_effective_amount,
                                       s.invoice_number_match,
                                       s.order_number_match,
                                       s.currency_match,
                                       s.merchant_match,
                                       s.customer_match,
                                       s.date_within_cap,
                                       s.document_total,
                                       s.document_company_id,
                                       s.transaction_date_unix,
                                       s.document_date_unix,
                                       s.doc_dedup_key,
                                       s.amount_exact_from_debited,
                                       s.amount_exact_from_credited,
                                       s.amount_tolerance_from_debited,
                                       s.amount_tolerance_from_credited,
                                       s.amount_exact_from_effective,
                                       s.amount_tolerance_from_effective,
                                       s.tx_is_in,
                                       s.tx_is_out,
                                       s.tx_iban_beneficiary_ns,
                                       s.tx_iban_counterparty_ns,
                                       s.doc_iban_ns,
                                       s.tx_iban_beneficiary_core_ns,
                                       s.tx_iban_counterparty_core_ns,
                                       s.doc_iban_core_ns,
                                       s.tx_merchant_ns,
                                       s.doc_merchant_ns,
                                       s.tx_customer_ns,
                                       s.doc_customer_ns,
                                       s.transaction_merchant_id,
                                       s.document_merchant_id,
                                       s.amount_exact_match,
                                       s.amount_tolerance_match,
                                       s.amount_direction_ok,
                                       s.amount_score,
                                       s.amount_match,
                                       s.invoice_score,
                                       s.order_score,
                                       s.currency_score,
                                       s.merchant_score,
                                       s.customer_score,
                                       s.amount_weight,
                                       s.invoice_weight,
                                       s.order_weight,
                                       s.date_weight,
                                       s.currency_weight,
                                       s.merchant_weight,
                                       s.customer_weight,
                                       s.date_score,
                                       s.date_match,
                                       s.merchant_applicable,
                                       s.customer_applicable,
                                       s.amount_weight + s.invoice_weight + s.order_weight +
                                       CASE
                                           WHEN s.transaction_date_unix IS NOT NULL AND s.document_date_unix IS NOT NULL
                                               THEN s.date_weight
                                           ELSE 0::numeric
                                           END + s.currency_weight +
                                       CASE
                                           WHEN s.merchant_applicable THEN s.merchant_weight
                                           ELSE 0::numeric
                                           END +
                                       CASE
                                           WHEN s.customer_applicable THEN s.customer_weight
                                           ELSE 0::numeric
                                           END                                AS dynamic_total_weight,
                                       s.amount_score + s.invoice_score + s.order_score + s.date_score +
                                       s.currency_score +
                                       CASE
                                           WHEN s.merchant_applicable THEN s.merchant_score
                                           ELSE 0::numeric
                                           END +
                                       CASE
                                           WHEN s.customer_applicable THEN s.customer_score
                                           ELSE 0::numeric
                                           END                                AS total_numerator,
                                       COALESCE(s.amount_match::integer, 0) +
                                       COALESCE(s.invoice_number_match::integer, 0) +
                                       COALESCE(s.order_number_match::integer, 0) + COALESCE(s.date_match::integer, 0) +
                                       COALESCE(s.currency_match::integer, 0) + COALESCE(s.merchant_match::integer, 0) +
                                       COALESCE(s.customer_match::integer, 0) AS signals_matched_count,
                                       CASE
                                           WHEN (s.amount_weight + s.invoice_weight + s.order_weight +
                                                 CASE
                                                     WHEN s.transaction_date_unix IS NOT NULL AND s.document_date_unix IS NOT NULL
                                                         THEN s.date_weight
                                                     ELSE 0::numeric
                                                     END + s.currency_weight +
                                                 CASE
                                                     WHEN s.merchant_applicable THEN s.merchant_weight
                                                     ELSE 0::numeric
                                                     END +
                                                 CASE
                                                     WHEN s.customer_applicable THEN s.customer_weight
                                                     ELSE 0::numeric
                                                     END) > 0::numeric THEN round(
                                                   (s.amount_score + s.invoice_score + s.order_score + s.date_score +
                                                    s.currency_score +
                                                    CASE
                                                        WHEN s.merchant_applicable THEN s.merchant_score
                                                        ELSE 0::numeric
                                                        END +
                                                    CASE
                                                        WHEN s.customer_applicable THEN s.customer_score
                                                        ELSE 0::numeric
                                                        END) / (s.amount_weight + s.invoice_weight + s.order_weight +
                                                                CASE
                                                                    WHEN s.transaction_date_unix IS NOT NULL AND s.document_date_unix IS NOT NULL
                                                                        THEN s.date_weight
                                                                    ELSE 0::numeric
                                                                    END + s.currency_weight +
                                                                CASE
                                                                    WHEN s.merchant_applicable THEN s.merchant_weight
                                                                    ELSE 0::numeric
                                                                    END +
                                                                CASE
                                                                    WHEN s.customer_applicable THEN s.customer_weight
                                                                    ELSE 0::numeric
                                                                    END) * 100.0, 4)
                                           ELSE 0::numeric
                                           END                                AS confidence_percent
                                FROM scored_with_applicability s),
     ranked_per_transaction AS (SELECT b.transaction_id,
                                       b.document_id,
                                       b.transaction_effective_amount,
                                       b.invoice_number_match,
                                       b.order_number_match,
                                       b.currency_match,
                                       b.merchant_match,
                                       b.customer_match,
                                       b.date_within_cap,
                                       b.document_total,
                                       b.document_company_id,
                                       b.transaction_date_unix,
                                       b.document_date_unix,
                                       b.doc_dedup_key,
                                       b.amount_exact_from_debited,
                                       b.amount_exact_from_credited,
                                       b.amount_tolerance_from_debited,
                                       b.amount_tolerance_from_credited,
                                       b.amount_exact_from_effective,
                                       b.amount_tolerance_from_effective,
                                       b.tx_is_in,
                                       b.tx_is_out,
                                       b.tx_iban_beneficiary_ns,
                                       b.tx_iban_counterparty_ns,
                                       b.doc_iban_ns,
                                       b.tx_iban_beneficiary_core_ns,
                                       b.tx_iban_counterparty_core_ns,
                                       b.doc_iban_core_ns,
                                       b.tx_merchant_ns,
                                       b.doc_merchant_ns,
                                       b.tx_customer_ns,
                                       b.doc_customer_ns,
                                       b.transaction_merchant_id,
                                       b.document_merchant_id,
                                       b.amount_exact_match,
                                       b.amount_tolerance_match,
                                       b.amount_direction_ok,
                                       b.amount_score,
                                       b.amount_match,
                                       b.invoice_score,
                                       b.order_score,
                                       b.currency_score,
                                       b.merchant_score,
                                       b.customer_score,
                                       b.amount_weight,
                                       b.invoice_weight,
                                       b.order_weight,
                                       b.date_weight,
                                       b.currency_weight,
                                       b.merchant_weight,
                                       b.customer_weight,
                                       b.date_score,
                                       b.date_match,
                                       b.merchant_applicable,
                                       b.customer_applicable,
                                       b.dynamic_total_weight,
                                       b.total_numerator,
                                       b.signals_matched_count,
                                       b.confidence_percent,
                                       row_number()
                                       OVER (PARTITION BY b.transaction_id ORDER BY b.confidence_percent DESC, b.total_numerator DESC, b.signals_matched_count DESC, b.document_id) AS rownum_per_transaction
                                FROM scored_with_confidence b,
                                     const
                                WHERE b.signals_matched_count >= 1
                                  AND b.confidence_percent >= const.min_confidence),
     top_per_transaction AS (SELECT ranked_per_transaction.transaction_id,
                                    ranked_per_transaction.document_id,
                                    ranked_per_transaction.transaction_effective_amount,
                                    ranked_per_transaction.invoice_number_match,
                                    ranked_per_transaction.order_number_match,
                                    ranked_per_transaction.currency_match,
                                    ranked_per_transaction.merchant_match,
                                    ranked_per_transaction.customer_match,
                                    ranked_per_transaction.date_within_cap,
                                    ranked_per_transaction.document_total,
                                    ranked_per_transaction.document_company_id,
                                    ranked_per_transaction.transaction_date_unix,
                                    ranked_per_transaction.document_date_unix,
                                    ranked_per_transaction.doc_dedup_key,
                                    ranked_per_transaction.amount_exact_from_debited,
                                    ranked_per_transaction.amount_exact_from_credited,
                                    ranked_per_transaction.amount_tolerance_from_debited,
                                    ranked_per_transaction.amount_tolerance_from_credited,
                                    ranked_per_transaction.amount_exact_from_effective,
                                    ranked_per_transaction.amount_tolerance_from_effective,
                                    ranked_per_transaction.tx_is_in,
                                    ranked_per_transaction.tx_is_out,
                                    ranked_per_transaction.tx_iban_beneficiary_ns,
                                    ranked_per_transaction.tx_iban_counterparty_ns,
                                    ranked_per_transaction.doc_iban_ns,
                                    ranked_per_transaction.tx_iban_beneficiary_core_ns,
                                    ranked_per_transaction.tx_iban_counterparty_core_ns,
                                    ranked_per_transaction.doc_iban_core_ns,
                                    ranked_per_transaction.tx_merchant_ns,
                                    ranked_per_transaction.doc_merchant_ns,
                                    ranked_per_transaction.tx_customer_ns,
                                    ranked_per_transaction.doc_customer_ns,
                                    ranked_per_transaction.transaction_merchant_id,
                                    ranked_per_transaction.document_merchant_id,
                                    ranked_per_transaction.amount_exact_match,
                                    ranked_per_transaction.amount_tolerance_match,
                                    ranked_per_transaction.amount_direction_ok,
                                    ranked_per_transaction.amount_score,
                                    ranked_per_transaction.amount_match,
                                    ranked_per_transaction.invoice_score,
                                    ranked_per_transaction.order_score,
                                    ranked_per_transaction.currency_score,
                                    ranked_per_transaction.merchant_score,
                                    ranked_per_transaction.customer_score,
                                    ranked_per_transaction.amount_weight,
                                    ranked_per_transaction.invoice_weight,
                                    ranked_per_transaction.order_weight,
                                    ranked_per_transaction.date_weight,
                                    ranked_per_transaction.currency_weight,
                                    ranked_per_transaction.merchant_weight,
                                    ranked_per_transaction.customer_weight,
                                    ranked_per_transaction.date_score,
                                    ranked_per_transaction.date_match,
                                    ranked_per_transaction.merchant_applicable,
                                    ranked_per_transaction.customer_applicable,
                                    ranked_per_transaction.dynamic_total_weight,
                                    ranked_per_transaction.total_numerator,
                                    ranked_per_transaction.signals_matched_count,
                                    ranked_per_transaction.confidence_percent,
                                    ranked_per_transaction.rownum_per_transaction
                             FROM ranked_per_transaction
                             WHERE ranked_per_transaction.rownum_per_transaction = 1),
     ranked_per_document AS (SELECT t.transaction_id,
                                    t.document_id,
                                    t.transaction_effective_amount,
                                    t.invoice_number_match,
                                    t.order_number_match,
                                    t.currency_match,
                                    t.merchant_match,
                                    t.customer_match,
                                    t.date_within_cap,
                                    t.document_total,
                                    t.document_company_id,
                                    t.transaction_date_unix,
                                    t.document_date_unix,
                                    t.doc_dedup_key,
                                    t.amount_exact_from_debited,
                                    t.amount_exact_from_credited,
                                    t.amount_tolerance_from_debited,
                                    t.amount_tolerance_from_credited,
                                    t.amount_exact_from_effective,
                                    t.amount_tolerance_from_effective,
                                    t.tx_is_in,
                                    t.tx_is_out,
                                    t.tx_iban_beneficiary_ns,
                                    t.tx_iban_counterparty_ns,
                                    t.doc_iban_ns,
                                    t.tx_iban_beneficiary_core_ns,
                                    t.tx_iban_counterparty_core_ns,
                                    t.doc_iban_core_ns,
                                    t.tx_merchant_ns,
                                    t.doc_merchant_ns,
                                    t.tx_customer_ns,
                                    t.doc_customer_ns,
                                    t.transaction_merchant_id,
                                    t.document_merchant_id,
                                    t.amount_exact_match,
                                    t.amount_tolerance_match,
                                    t.amount_direction_ok,
                                    t.amount_score,
                                    t.amount_match,
                                    t.invoice_score,
                                    t.order_score,
                                    t.currency_score,
                                    t.merchant_score,
                                    t.customer_score,
                                    t.amount_weight,
                                    t.invoice_weight,
                                    t.order_weight,
                                    t.date_weight,
                                    t.currency_weight,
                                    t.merchant_weight,
                                    t.customer_weight,
                                    t.date_score,
                                    t.date_match,
                                    t.merchant_applicable,
                                    t.customer_applicable,
                                    t.dynamic_total_weight,
                                    t.total_numerator,
                                    t.signals_matched_count,
                                    t.confidence_percent,
                                    t.rownum_per_transaction,
                                    row_number()
                                    OVER (PARTITION BY t.doc_dedup_key ORDER BY t.confidence_percent DESC, t.total_numerator DESC, t.signals_matched_count DESC, (abs(t.transaction_date_unix - t.document_date_unix)), t.document_id) AS rownum_per_document
                             FROM top_per_transaction t),
     final_candidates AS (SELECT ranked_per_document.transaction_id,
                                 ranked_per_document.document_id,
                                 ranked_per_document.transaction_effective_amount,
                                 ranked_per_document.invoice_number_match,
                                 ranked_per_document.order_number_match,
                                 ranked_per_document.currency_match,
                                 ranked_per_document.merchant_match,
                                 ranked_per_document.customer_match,
                                 ranked_per_document.date_within_cap,
                                 ranked_per_document.document_total,
                                 ranked_per_document.document_company_id,
                                 ranked_per_document.transaction_date_unix,
                                 ranked_per_document.document_date_unix,
                                 ranked_per_document.doc_dedup_key,
                                 ranked_per_document.amount_exact_from_debited,
                                 ranked_per_document.amount_exact_from_credited,
                                 ranked_per_document.amount_tolerance_from_debited,
                                 ranked_per_document.amount_tolerance_from_credited,
                                 ranked_per_document.amount_exact_from_effective,
                                 ranked_per_document.amount_tolerance_from_effective,
                                 ranked_per_document.tx_is_in,
                                 ranked_per_document.tx_is_out,
                                 ranked_per_document.tx_iban_beneficiary_ns,
                                 ranked_per_document.tx_iban_counterparty_ns,
                                 ranked_per_document.doc_iban_ns,
                                 ranked_per_document.tx_iban_beneficiary_core_ns,
                                 ranked_per_document.tx_iban_counterparty_core_ns,
                                 ranked_per_document.doc_iban_core_ns,
                                 ranked_per_document.tx_merchant_ns,
                                 ranked_per_document.doc_merchant_ns,
                                 ranked_per_document.tx_customer_ns,
                                 ranked_per_document.doc_customer_ns,
                                 ranked_per_document.transaction_merchant_id,
                                 ranked_per_document.document_merchant_id,
                                 ranked_per_document.amount_exact_match,
                                 ranked_per_document.amount_tolerance_match,
                                 ranked_per_document.amount_direction_ok,
                                 ranked_per_document.amount_score,
                                 ranked_per_document.amount_match,
                                 ranked_per_document.invoice_score,
                                 ranked_per_document.order_score,
                                 ranked_per_document.currency_score,
                                 ranked_per_document.merchant_score,
                                 ranked_per_document.customer_score,
                                 ranked_per_document.amount_weight,
                                 ranked_per_document.invoice_weight,
                                 ranked_per_document.order_weight,
                                 ranked_per_document.date_weight,
                                 ranked_per_document.currency_weight,
                                 ranked_per_document.merchant_weight,
                                 ranked_per_document.customer_weight,
                                 ranked_per_document.date_score,
                                 ranked_per_document.date_match,
                                 ranked_per_document.merchant_applicable,
                                 ranked_per_document.customer_applicable,
                                 ranked_per_document.dynamic_total_weight,
                                 ranked_per_document.total_numerator,
                                 ranked_per_document.signals_matched_count,
                                 ranked_per_document.confidence_percent,
                                 ranked_per_document.rownum_per_transaction,
                                 ranked_per_document.rownum_per_document
                          FROM ranked_per_document
                          WHERE ranked_per_document.rownum_per_document = 1),
     ts_base AS (SELECT f.transaction_id,
                        f.document_id,
                        f.document_company_id,
                        f.confidence_percent,
                        COALESCE(f.transaction_date_unix, f.document_date_unix,
                                 EXTRACT(epoch FROM now())::bigint) AS base_unix
                 FROM final_candidates f),
     enrich AS (SELECT d.transaction_id,
                       d.document_id,
                       d.document_company_id,
                       d.confidence_percent,
                       d.invoice_number_match AS invoice_match,
                       d.order_number_match   AS order_match,
                       d.amount_match,
                       d.date_match,
                       d.currency_match,
                       d.merchant_match,
                       d.customer_match,
                       a.transaction_effective_amount,
                       a.document_total,
                       a.transaction_date_unix,
                       a.document_date_unix,
                       d.amount_score,
                       d.amount_weight,
                       d.date_score,
                       d.date_weight,
                       t.base_unix,
                       a.tx_invoice_norm,
                       a.doc_invoice_norm,
                       a.tx_order_norm,
                       a.doc_order_norm,
                       a.transaction_currency,
                       a.document_currency,
                       a.transaction_merchant_id,
                       a.document_merchant_id,
                       a.transaction_customer_name,
                       a.document_customer_name,
                       a.doc_invoice_raw,
                       a.doc_order_raw,
                       a.tx_iban_beneficiary_ns,
                       a.tx_iban_counterparty_ns,
                       a.doc_iban_ns,
                       a.tx_iban_beneficiary_core_ns,
                       a.tx_iban_counterparty_core_ns,
                       a.doc_iban_core_ns,
                       a.tx_is_in,
                       a.tx_is_out
                FROM ts_base t
                         JOIN final_candidates d
                              ON d.transaction_id = t.transaction_id AND d.document_id = t.document_id
                         JOIN attributes a ON a.transaction_id = d.transaction_id AND a.document_id = d.document_id),
     notes AS (SELECT e_1.transaction_id,
                      e_1.document_id,
                      e_1.document_company_id,
                      e_1.confidence_percent,
                      e_1.invoice_match,
                      e_1.order_match,
                      e_1.amount_match,
                      e_1.date_match,
                      e_1.currency_match,
                      e_1.merchant_match,
                      e_1.customer_match,
                      e_1.transaction_effective_amount,
                      e_1.document_total,
                      e_1.transaction_date_unix,
                      e_1.document_date_unix,
                      e_1.amount_score,
                      e_1.amount_weight,
                      e_1.date_score,
                      e_1.date_weight,
                      e_1.base_unix,
                      e_1.tx_invoice_norm,
                      e_1.doc_invoice_norm,
                      e_1.tx_order_norm,
                      e_1.doc_order_norm,
                      e_1.transaction_currency,
                      e_1.document_currency,
                      e_1.transaction_merchant_id,
                      e_1.document_merchant_id,
                      e_1.transaction_customer_name,
                      e_1.document_customer_name,
                      e_1.doc_invoice_raw,
                      e_1.doc_order_raw,
                      e_1.tx_iban_beneficiary_ns,
                      e_1.tx_iban_counterparty_ns,
                      e_1.doc_iban_ns,
                      e_1.tx_iban_beneficiary_core_ns,
                      e_1.tx_iban_counterparty_core_ns,
                      e_1.doc_iban_core_ns,
                      e_1.tx_is_in,
                      e_1.tx_is_out,
                      CASE
                          WHEN e_1.document_total IS NOT NULL AND e_1.document_total <> 0::numeric THEN round(
                                  abs(e_1.transaction_effective_amount - e_1.document_total) /
                                  NULLIF(e_1.document_total, 0::numeric) * 100.0, 2)
                          ELSE NULL::numeric
                          END AS amt_diff_pct,
                      CASE
                          WHEN e_1.transaction_date_unix IS NOT NULL AND e_1.document_date_unix IS NOT NULL
                              THEN abs(e_1.transaction_date_unix - e_1.document_date_unix)
                          ELSE NULL::bigint
                          END AS sec_diff,
                      CASE
                          WHEN e_1.transaction_date_unix IS NOT NULL AND e_1.document_date_unix IS NOT NULL THEN
                              abs(e_1.transaction_date_unix - e_1.document_date_unix)::numeric / 86400.0
                          ELSE NULL::numeric
                          END AS day_gap,
                      CASE
                          WHEN e_1.transaction_date_unix IS NOT NULL AND e_1.document_date_unix IS NOT NULL THEN
                              abs(e_1.transaction_date_unix - e_1.document_date_unix)::numeric / 3600.0
                          ELSE NULL::numeric
                          END AS hour_gap,
                      CASE
                          WHEN e_1.transaction_date_unix IS NOT NULL AND e_1.document_date_unix IS NOT NULL THEN
                              abs(e_1.transaction_date_unix - e_1.document_date_unix)::numeric / 60.0
                          ELSE NULL::numeric
                          END AS minute_gap,
                      CASE
                          WHEN e_1.amount_weight > 0::numeric
                              THEN round(e_1.amount_score / e_1.amount_weight * 100.0, 2)
                          ELSE NULL::numeric
                          END AS amount_ratio_pct,
                      CASE
                          WHEN e_1.date_weight > 0::numeric THEN round(e_1.date_score / e_1.date_weight * 100.0, 0)
                          ELSE NULL::numeric
                          END AS date_ratio_pct
               FROM enrich e_1),
     notes_text AS (SELECT n.transaction_id,
                           n.document_id,
                           n.document_company_id,
                           n.confidence_percent,
                           n.invoice_match,
                           n.order_match,
                           n.amount_match,
                           n.date_match,
                           n.currency_match,
                           n.merchant_match,
                           n.customer_match,
                           n.transaction_effective_amount,
                           n.document_total,
                           n.transaction_date_unix,
                           n.document_date_unix,
                           n.amount_score,
                           n.amount_weight,
                           n.date_score,
                           n.date_weight,
                           n.base_unix,
                           n.tx_invoice_norm,
                           n.doc_invoice_norm,
                           n.tx_order_norm,
                           n.doc_order_norm,
                           n.transaction_currency,
                           n.document_currency,
                           n.transaction_merchant_id,
                           n.document_merchant_id,
                           n.transaction_customer_name,
                           n.document_customer_name,
                           n.doc_invoice_raw,
                           n.doc_order_raw,
                           n.tx_iban_beneficiary_ns,
                           n.tx_iban_counterparty_ns,
                           n.doc_iban_ns,
                           n.tx_iban_beneficiary_core_ns,
                           n.tx_iban_counterparty_core_ns,
                           n.doc_iban_core_ns,
                           n.tx_is_in,
                           n.tx_is_out,
                           n.amt_diff_pct,
                           n.sec_diff,
                           n.day_gap,
                           n.hour_gap,
                           n.minute_gap,
                           n.amount_ratio_pct,
                           n.date_ratio_pct,
                           CASE
                               WHEN n.date_weight > 0::numeric AND n.date_ratio_pct <= 99::numeric THEN TRIM(BOTH ' '::text
                                                                                                             FROM
                                                                                                             concat_ws(
                                                                                                                     ' '::text,
                                                                                                                     CASE
                                                                                                                         WHEN
                                                                                                                             n.amount_weight >
                                                                                                                             0::numeric AND
                                                                                                                             n.amount_score <
                                                                                                                             n.amount_weight AND
                                                                                                                             n.amt_diff_pct IS NOT NULL
                                                                                                                             THEN concat(
                                                                                                                                 'Amount scored ',
                                                                                                                                 COALESCE(n.amount_ratio_pct::text, '0'::text),
                                                                                                                                 '% because payment vs document differed by ',
                                                                                                                                 n.amt_diff_pct::text,
                                                                                                                                 '%.')
                                                                                                                         ELSE NULL::text
                                                                                                                         END,
                                                                                                                     CASE
                                                                                                                         WHEN n.day_gap IS NOT NULL
                                                                                                                             THEN concat(
                                                                                                                                 'Date scored ',
                                                                                                                                 COALESCE(n.date_ratio_pct::text, '0'::text),
                                                                                                                                 '% because dates were ',
                                                                                                                                 round(n.day_gap, 2)::text,
                                                                                                                                 ' days apart.')
                                                                                                                         ELSE NULL::text
                                                                                                                         END))
                               ELSE NULL::text
                               END AS note_text
                    FROM notes n)
SELECT e.transaction_id,
       e.document_id,
       e.document_company_id                                                                                 AS company_id,
       COALESCE(e.confidence_percent, 0::numeric)                                                            AS confidence,
       EXTRACT(epoch FROM now())::bigint                                                                     AS "time",
       'v1_9_P'::text                                                                                        AS engine_version,
       EXTRACT(year FROM
               (to_timestamp(e.base_unix::double precision) AT TIME ZONE 'UTC'::text))::integer              AS fiscal_year,
       EXTRACT(quarter FROM (to_timestamp(e.base_unix::double precision) AT TIME ZONE 'UTC'::text))::integer AS quarter,
       to_char((to_timestamp(e.base_unix::double precision) AT TIME ZONE 'UTC'::text), 'FMMonth'::text)      AS month,
       json_build_object('signals', (SELECT json_agg(s.s) AS json_agg
                                     FROM unnest(ARRAY [
                                         CASE
                                             WHEN e.invoice_match THEN 'invoice'::text
                                             ELSE NULL::text
                                             END,
                                         CASE
                                             WHEN e.amount_match THEN 'amount'::text
                                             ELSE NULL::text
                                             END,
                                         CASE
                                             WHEN e.date_match THEN 'date'::text
                                             ELSE NULL::text
                                             END,
                                         CASE
                                             WHEN e.currency_match THEN 'currency'::text
                                             ELSE NULL::text
                                             END,
                                         CASE
                                             WHEN e.merchant_match THEN 'merchant'::text
                                             ELSE NULL::text
                                             END,
                                         CASE
                                             WHEN e.customer_match THEN 'customer'::text
                                             ELSE NULL::text
                                             END,
                                         CASE
                                             WHEN e.order_match THEN 'order'::text
                                             ELSE NULL::text
                                             END]) s(s)
                                     WHERE s.s IS NOT NULL), 'tx_amount', e.transaction_effective_amount, 'doc_total',
                         e.document_total, 'tx_invoice', e.tx_invoice_norm, 'doc_invoice',
                         COALESCE(e.doc_invoice_raw, e.doc_invoice_norm), 'tx_order', e.tx_order_norm, 'doc_order',
                         COALESCE(e.doc_order_raw, e.doc_order_norm), 'tx_currency', e.transaction_currency,
                         'doc_currency', e.document_currency, 'tx_merchant', e.transaction_merchant_id, 'doc_merchant',
                         e.document_merchant_id, 'tx_customer', e.transaction_customer_name, 'doc_customer',
                         e.document_customer_name, 'tx_unix', e.transaction_date_unix, 'doc_unix', e.document_date_unix,
                         'tx_iban_beneficiary', e.tx_iban_beneficiary_ns, 'tx_iban_counterparty',
                         e.tx_iban_counterparty_ns, 'doc_iban', e.doc_iban_ns, 'tx_iban_beneficiary_core',
                         e.tx_iban_beneficiary_core_ns, 'tx_iban_counterparty_core', e.tx_iban_counterparty_core_ns,
                         'doc_iban_core', e.doc_iban_core_ns, 'tx_is_in', e.tx_is_in, 'tx_is_out', e.tx_is_out,
                         'direction',
                         CASE
                             WHEN e.tx_is_in THEN 'incoming'::text
                             WHEN e.tx_is_out THEN 'outgoing'::text
                             ELSE 'unknown'::text
                             END, 'days_between_tx_and_doc',
                         CASE
                             WHEN e.transaction_date_unix IS NOT NULL AND e.document_date_unix IS NOT NULL
                                 THEN (abs(e.transaction_date_unix - e.document_date_unix) / 86400)::integer
                             ELSE NULL::integer
                             END)::text                                                                      AS summary,
       NULLIF(nt.note_text, ''::text)                                                                        AS note
FROM notes_text nt
         JOIN enrich e USING (transaction_id, document_id);

alter table v_reconciliations
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_reconciliations to anon;

grant delete, insert, references, select, trigger, truncate, update on v_reconciliations to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_reconciliations to service_role;

create or replace view v_customer_invoice_summary
            (id, customer_id, company_id, name, created_at, invoices_sent, invoices_overdue, to_be_received, revenue,
             slug) as
SELECT c.id,
       c.customer_id,
       c.company_id,
       c.name,
       c.created_at,
       COALESCE(count(i.invoice_id), 0::bigint)                                                     AS invoices_sent,
       COALESCE(count(*)
                FILTER (WHERE i.status = 'overdue'::text OR i.due_date < EXTRACT(epoch FROM now()) AND i.paid = false),
                0::bigint)                                                                          AS invoices_overdue,
       COALESCE(sum(i.amount_remaining) FILTER (WHERE i.paid = false AND c.company_id::text = i.author_company_id),
                0::numeric)                                                                         AS to_be_received,
       COALESCE(sum(i.revenue) FILTER (WHERE c.company_id::text = i.author_company_id), 0::numeric) AS revenue,
       max(i.slug)                                                                                  AS slug
FROM customers c
         LEFT JOIN invoices i ON c.customer_id = i.customer
GROUP BY c.id, c.customer_id, c.company_id, c.name, c.created_at;

alter table v_customer_invoice_summary
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_customer_invoice_summary to anon;

grant delete, insert, references, select, trigger, truncate, update on v_customer_invoice_summary to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_customer_invoice_summary to service_role;

create or replace view v_sf_formations_company_status_counts
            (id, rid, created_at, time, sf_formations_case_id, title, description, status, entity_type, author_user_id,
             assignee, company_id, metadata, count_status_new, count_status_pending, count_status_completing,
             count_status_await, count_status_cancelled)
as
WITH latest AS (SELECT DISTINCT ON (sf_formations_cases.company_id) sf_formations_cases.id,
                                                                    sf_formations_cases.rid,
                                                                    sf_formations_cases.created_at,
                                                                    sf_formations_cases."time",
                                                                    sf_formations_cases.sf_formations_case_id,
                                                                    sf_formations_cases.title,
                                                                    sf_formations_cases.description,
                                                                    sf_formations_cases.status,
                                                                    sf_formations_cases.entity_type,
                                                                    sf_formations_cases.author_user_id,
                                                                    sf_formations_cases.assignee,
                                                                    sf_formations_cases.company_id,
                                                                    sf_formations_cases.metadata
                FROM sf_formations_cases
                ORDER BY sf_formations_cases.company_id, sf_formations_cases.created_at DESC),
     counts AS (SELECT sf_formations_cases.company_id,
                       count(*) FILTER (WHERE sf_formations_cases.status = 'new'::text)        AS count_status_new,
                       count(*) FILTER (WHERE sf_formations_cases.status = 'pending'::text)    AS count_status_pending,
                       count(*)
                       FILTER (WHERE sf_formations_cases.status = 'completing'::text)          AS count_status_completing,
                       count(*) FILTER (WHERE sf_formations_cases.status = 'await'::text)      AS count_status_await,
                       count(*) FILTER (WHERE sf_formations_cases.status = 'cancelled'::text)  AS count_status_cancelled
                FROM sf_formations_cases
                GROUP BY sf_formations_cases.company_id)
SELECT l.id,
       l.rid,
       l.created_at,
       l."time",
       l.sf_formations_case_id,
       l.title,
       l.description,
       l.status,
       l.entity_type,
       l.author_user_id,
       l.assignee,
       l.company_id,
       l.metadata,
       c.count_status_new,
       c.count_status_pending,
       c.count_status_completing,
       c.count_status_await,
       c.count_status_cancelled
FROM latest l
         JOIN counts c USING (company_id);

alter table v_sf_formations_company_status_counts
    owner to postgres;

grant delete, insert, references, select, trigger, truncate, update on v_sf_formations_company_status_counts to anon;

grant delete, insert, references, select, trigger, truncate, update on v_sf_formations_company_status_counts to authenticated;

grant delete, insert, references, select, trigger, truncate, update on v_sf_formations_company_status_counts to service_role;

