brokenquery.edgeql

# NOTE: this query is designed to be run adhoc to populate the type ProfessionalFeeApplicants
WITH
  feeapps := (
    SELECT DocketDocument {
      case_name := .bankruptcy_case.case_name,
      bankruptcy_case,
      case_title := .bankruptcy_case.title,
      docket_document_id := .id,
      docket_number,
      uploaded_href,
      title,
      applicant :=      re_match('.*(for|of) ([A-Z].*?),?\\s*(Inc|Services|Creditors|Group|Chartered|Partners|Consulting|P.L.L.C.|L.L.C.|L.L.P.|L.P.|P.C.|S.C.|PLLC|LLC|LLP|LP|PC|SC)', .title)[1] ?? '**unknown**',
      applicant_type := re_match(r'(Counsel|Co-counsel|Attorney|Financial Advisor|Advisor|Banker|Trustee|Officer|Ombudsman|Administrator|Consultant|Accountant|Other Professional)', .title)[0] ?? '**unknown**',
      applicant_party := 'Official Committee' IF .title ILIKE '%Official Committee%'
        ELSE 'Debtor' IF .title ILIKE '%Debtor%'
        ELSE 'Future Claimaint' IF .title ILIKE '%Future Claimaint%'
        ELSE '**unknown**',
      filed_by,
      extracted_amounts := (
        FOR extracted_text IN (.pages.extracted_text) UNION (
          FOR m IN (re_match_all('\\$([0-9,]+[.]?[0-9]+)', extracted_text)[0]) UNION (
            SELECT <decimal>str_replace(m, ',', '')
          )
        )
      ),
      is_final := .title ILIKE '%Final%',
    }
    FILTER 1=1
      AND (
        .title ILIKE '%Application%Fee%'
        OR .title ILIKE '%Application%Compensation%'
        OR .title ILIKE '%Fee%Application%'
        OR .title ILIKE '%Compensation%Application%'
      )
      # QUESTION: is this exhaustive as first word of the title?
      AND re_test('^(Fee|Compensation|Application|Interim|First|Second|Third|Fourth|Fifth|Final|Supplemental)', .title)
      # QUESTION: is this enough to filter out documents in response?
      AND len(.related_docket_numbers) = 0
      AND EXISTS .extracted_amounts
      # TODO: fix me
      AND .applicant != 'Expenses'
      AND .applicant != 'Compensation for'
      AND .applicant != 'Unsecured'
      AND NOT .applicant ILIKE '%for the period%'
      AND .applicant != '**unknown**'
    LIMIT <int64>$limit
  ),
  groups := (
    # QUESTION: is it a good heuristic here?
    GROUP feeapps { amount := max(.extracted_amounts) }
    BY (.bankruptcy_case, .case_name, .case_title, .applicant)
  )
FOR r IN (SELECT groups) UNION (
  INSERT ProfessionalFeeApplicant {
    bankruptcy_case := r.key.bankruptcy_case,
    name := r.key.applicant,
    applicant_types := array_agg((DISTINCT r.elements.applicant_type)),
    applicant_parties := array_agg((DISTINCT r.elements.applicant_party)),
    has_final := any(r.elements.is_final),
    amounts := array_agg(r.elements.amount),
    docket_document_ids := array_agg(r.elements.docket_document_id),
  }
)