view: orch_app_qa_royalty_accounting_account_payee_kyc_notification {
  # sql_table_name: ORCHARD_APP_REPORTING_V2.QA_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_PAYEE_KYC_NOTIFICATION ;;
  derived_table: {
    sql:
      with vendor_contact as (
        select distinct vendor_id, contact_email, row_number() over (partition by vendor_id order by vc.id desc, vc.contact_id desc) as rn from
        ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VEND_CONTACT vc
        left JOIN orchard_app_reporting_v2.ART_RELATIONS_PROD_ART_RELATIONS.CONTACT c on vc.contact_id= c.contact_id where master='Y'
        QUALIFY rn=1
        ),
        kyc_notif as (
        select
          *,
          row_number() over (
              partition by account_payee_id
              order by
                  account_payee_kyc_notification_id desc
          ) rn
          from
          ORCHARD_APP_REPORTING_V2.QA_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.ACCOUNT_PAYEE_KYC_NOTIFICATION
          QUALIFY rn = 1
          )
      SELECT
          ap.account_id,
          st.display_name AS client_service_tier,
          ap.account_payee_id,
          -- Eligibility status
          CASE
              WHEN bdst.action_status IS NULL AND pest.action_status = 'init' THEN 'Not Provided Banking Details'
              WHEN bdst.action_status = 'init' AND pest.action_status = 'init' THEN 'Banking Details Under Review (KYC In Progress)'
              WHEN bdst.action_status = 'running' AND pest.action_status = 'init' THEN 'KYC Screening Detects Invalid/Incomplete Information'
              WHEN bdst.action_status = 'approved' AND pest.action_status = 'running' THEN 'Banking Details Approved by Payoneer (Waiting Finance Review)'
              WHEN bdst.action_status = 'rejected' AND pest.action_status = 'init' THEN 'Banking Details Rejected by Payoneer'
              WHEN bdst.action_status = 'approved' AND pest.action_status = 'approved' THEN 'Banking Details Approved by Finance (Payment Eligible)'
              WHEN bdst.action_status = 'approved' AND pest.action_status = 'rejected' THEN 'Banking Details Approved by Finance (Payment Rejected)'
              ELSE 'Not Provided Banking Details'
          END AS eligibility_status,

          -- Reason for ineligibility
          CASE
              WHEN bdst.action_status = 'rejected' AND pest.action_status = 'init' THEN bdst.message  -- Reason for rejection from Payoneer
              WHEN bdst.action_status = 'approved' AND pest.action_status = 'rejected' THEN pest.message  -- Reason for rejection from Finance
              ELSE NULL
          END AS reason_for_ineligibility,
          CASE
              WHEN bdst.action_status IS NULL
              AND pest.action_status = 'init' THEN NULL
              WHEN bdst.action_status = 'init'
              AND pest.action_status = 'init' THEN MAX(Coalesce(pest.last_modified, bdst.last_modified))
              WHEN bdst.action_status = 'running'
              AND pest.action_status = 'init' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
              WHEN bdst.action_status = 'approved'
              AND pest.action_status = 'running' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
              WHEN bdst.action_status = 'rejected'
              AND pest.action_status = 'running' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
              WHEN bdst.action_status = 'approved'
              AND pest.action_status = 'approved' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
              WHEN bdst.action_status = 'approved'
              AND pest.action_status = 'rejected' THEN MAX(Coalesce(bdst.last_modified, pest.last_modified))
              ELSE NULL
          END AS eligibility_status_last_modified,
          CASE WHEN test.action_status IN ('init', 'running') or test.action_status is NULL THEN 'Client has not provided tax details yet'
              WHEN test.action_status = 'complete' THEN 'Tax details approved by Finance Team'
              WHEN test.action_status = 'rejected' THEN 'Tax details rejected  by Finance Team'
              ELSE NULL
          END AS tax_eligibility_status_funnel,

          -- Additional columns
          bdst.action_status AS banking_details_review,
          bdst.message AS banking_details_message,
          pest.action_status AS payment_eligibility_status,
          pest.message AS payment_eligibility_message,
          test.action_status AS tax_eligibility_status,
          test.message AS tax_eligibility_message,
          test.last_modified AS tax_eligibility_last_modified,
          kyc_notif.file_upload_link,
          kyc_notif.created_at,
          kyc_notif.created_by,
          kyc_notif.entity_reference_id,
          kyc_notif.entity_reference_type_id,
          kyc_notif.last_modified as kyc_notif_last_modified,
          kyc_notif.last_modified_by,
          kyc_notif.possible_sub_requirement_types,
          kyc_notif.sub_requirement_status_id,
          kyc_notif.sub_requirement_id,
          kyc_notif.requirement_id,
          kyc_notif.requirement_type_id,
          vc.contact_email,
          -- Sum of positive contract balances on the account
          SUM(COALESCE(lac.current_balance, 0)) AS sum_of_positive_contract_balances

      FROM
          ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.account_payee ap

      -- Join service tier information
      LEFT JOIN FACTS.qa.VENDOR_IN_SERVICE_TIER_SERVICE_TIER vist
          ON ap.account_id = vist.vendor_id
      LEFT JOIN FACTS.QA.SERVICE_TIER st
          ON st.uuid=vist.service_tier_uuid

      -- Join the payment type to check payment service
      INNER JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.reference_payment_type rpt
          ON ap.reference_payment_type_id = rpt.reference_payment_type_id

      -- Join abacus_state table for banking details review
      LEFT JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.abacus_state bdst
          ON bdst.parent_table_id = ap.account_payee_id
          AND bdst.parent_table_name = 'account_payee'
          AND bdst.action_name = 'banking_details_review'

      -- Join abacus_state table for payment eligibility status
      LEFT JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.abacus_state pest
          ON pest.parent_table_id = ap.account_payee_id
          AND pest.parent_table_name = 'account_payee'
          AND pest.action_name = 'payment_eligibility'

      -- Join abacus_state table for tax eligibility status
      LEFT JOIN ORCHARD_APP_REPORTING_V2.qa_ROYALTY_ACCOUNTING_ROYALTY_ACCOUNTING.abacus_state test
          ON test.parent_table_id = ap.account_payee_id
          AND test.parent_table_name = 'account_payee'
          AND test.action_name = 'tax_eligibility'

      -- Join to ledger account balances
      LEFT JOIN orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.ledger_account_contract_current_balance bal
          ON bal.account_id = ap.account_id

      LEFT JOIN orchard_app_reporting_v2.QA_royalty_accounting_royalty_accounting.ledger_account_contract lac
          ON bal.ledger_account_contract_id = lac.ledger_account_contract_id
          AND lac.current_balance > 0

      LEFT JOIN kyc_notif
          ON ap.account_payee_id = kyc_notif.account_payee_id

      LEFT JOIN vendor_contact vc on vc.vendor_id=ap.account_id

      -- Filter by payment service
      WHERE rpt.payment_service in ('payoneer_whitelabel', 'paychex', 'payoneer_wire')

      -- Group by the necessary columns (explicitly list non-aggregated columns)
      GROUP BY
          ap.account_id,
          st.display_name,
          bdst.action_status,
          bdst.message,
          pest.action_status,
          pest.message,
          test.action_status,
          test.message,
          test.last_modified,
          kyc_notif.file_upload_link,
          kyc_notif.created_at,
          kyc_notif.created_by,
          kyc_notif.entity_reference_id,
          kyc_notif.entity_reference_type_id,
          kyc_notif.file_upload_link,
          kyc_notif.last_modified,
          kyc_notif.last_modified_by,
          kyc_notif.possible_sub_requirement_types,
          kyc_notif.sub_requirement_status_id,
          kyc_notif.sub_requirement_id,
          kyc_notif.requirement_id,
          ap.account_payee_id,
          kyc_notif.requirement_type_id,
          vc.contact_email;;
  }
  dimension: account_id {
    type: number
    sql: ${TABLE}.account_id ;;
  }

  dimension: account_payee_id {
    type: number
    sql: ${TABLE}.account_payee_id ;;
  }

  dimension: sub_requirement_status_id {
    type: number
    sql: ${TABLE}.sub_requirement_status_id ;;
  }

  dimension: entity_reference_type_id {
    type: number
    sql: ${TABLE}.entity_reference_type_id ;;
  }

  dimension: requirement_id {
    type: string
    sql: ${TABLE}.requirement_id ;;
  }

  dimension: file_upload_link {
    type: string
    sql: ${TABLE}.file_upload_link ;;
  }

  dimension: sub_requirement_id {
    type: string
    sql: ${TABLE}.sub_requirement_id ;;
  }

  dimension: created_at {
    type: string
    sql: ${TABLE}.created_at ;;
  }

  dimension: created_by {
    type: string
    sql: ${TABLE}.created_by ;;
  }

  dimension: kyc_notif_last_modified {
    type: string
    sql: ${TABLE}.kyc_notif_last_modified ;;
  }

  dimension: eligibility_status_last_modified {
    type: string
    sql: ${TABLE}.eligibility_status_last_modified ;;
  }

  dimension: tax_eligibility_last_modified {
    type: string
    sql: ${TABLE}.tax_eligibility_last_modified ;;
  }

  dimension: last_modified_by {
    type: string
    sql: ${TABLE}.last_modified_by ;;
  }

  dimension: banking_details_review {
    type: string
    sql: ${TABLE}.banking_details_review ;;
  }

  dimension: banking_details_message {
    type: string
    sql: ${TABLE}.banking_details_message ;;
  }

  dimension: requirement_type_id {
    type: string
    sql: ${TABLE}.requirement_type_id ;;
  }

  dimension: possible_sub_requirement_types {
    type: string
    sql: ${TABLE}.possible_sub_requirement_types ;;
  }

  dimension: entity_reference_id {
    type: string
    sql: ${TABLE}.entity_reference_id ;;
  }

  dimension: reason_for_ineligibility {
    type: string
    sql: ${TABLE}.reason_for_ineligibility ;;
  }

  dimension: eligibility_status {
    type: string
    sql: ${TABLE}.eligibility_status ;;
  }

  dimension: client_service_tier {
    type: string
    sql: ${TABLE}.client_service_tier ;;
  }

  dimension: payment_eligibility_message {
    type: string
    sql: ${TABLE}.payment_eligibility_message ;;
  }

  dimension: payment_eligibility_status {
    type: string
    sql: ${TABLE}.payment_eligibility_status ;;
  }

  dimension: tax_eligibility_status {
    type: string
    sql: ${TABLE}.tax_eligibility_status ;;
  }

  dimension: tax_eligibility_status_funnel {
    type: string
    sql: ${TABLE}.tax_eligibility_status_funnel ;;
  }

  dimension: tax_eligibility_message {
    type: string
    sql: ${TABLE}.tax_eligibility_message ;;
  }

  dimension: contact_email {
    type: string
    sql: ${TABLE}.contact_email ;;
  }

  measure: sum_of_positive_contract_balances {
    type: sum
    sql: ${TABLE}.sum_of_positive_contract_balances ;;
  }
}