ai-tax-agent/reasoning/schedule_queries.cypher

// FILE: reasoning/schedule_queries.cypher

// SA102: Employment Income Calculation
// Box 1: Pay from employment
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (ii:IncomeItem {type: 'employment'})
WHERE (tp)-[:HAS_INCOME]->(ii)
  AND ii.valid_from >= ty.start_date 
  AND ii.valid_from <= ty.end_date
  AND ii.retracted_at IS NULL
WITH ii, ty
MATCH (ii)-[:DERIVED_FROM]->(e:Evidence)-[:SUPPORTED_BY]->(d:Document)
RETURN 
  sum(ii.gross) as box_1_total,
  collect(DISTINCT {
    amount: ii.gross,
    source: d.doc_id,
    page: e.page,
    bbox: e.bbox,
    text_hash: e.text_hash,
    confidence: e.ocr_confidence
  }) as evidence_trail,
  count(ii) as income_items_count;

// Box 2: UK tax deducted
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (ii:IncomeItem {type: 'employment'})
WHERE (tp)-[:HAS_INCOME]->(ii)
  AND ii.valid_from >= ty.start_date 
  AND ii.valid_from <= ty.end_date
  AND ii.retracted_at IS NULL
  AND ii.tax_withheld IS NOT NULL
WITH ii, ty
MATCH (ii)-[:DERIVED_FROM]->(e:Evidence)-[:SUPPORTED_BY]->(d:Document)
RETURN 
  sum(ii.tax_withheld) as box_2_total,
  collect(DISTINCT {
    amount: ii.tax_withheld,
    source: d.doc_id,
    page: e.page,
    bbox: e.bbox,
    text_hash: e.text_hash
  }) as evidence_trail;

// SA103: Self-Employment Income Calculation
// Box 12: Turnover
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (ba:BusinessActivity)-[:OWNED_BY]->(tp)
MATCH (ii:IncomeItem {type: 'self_employment'})
WHERE (ba)-[:GENERATES]->(ii)
  AND ii.valid_from >= ty.start_date 
  AND ii.valid_from <= ty.end_date
  AND ii.retracted_at IS NULL
WITH ii, ba, ty
MATCH (ii)-[:DERIVED_FROM]->(e:Evidence)-[:SUPPORTED_BY]->(d:Document)
RETURN 
  ba.business_name as business_name,
  sum(ii.gross) as box_12_turnover,
  collect(DISTINCT {
    amount: ii.gross,
    description: ii.description,
    source: d.doc_id,
    page: e.page,
    bbox: e.bbox,
    text_hash: e.text_hash
  }) as evidence_trail;

// Box 31: Total allowable business expenses
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (ba:BusinessActivity)-[:OWNED_BY]->(tp)
MATCH (ei:ExpenseItem {type: 'business', allowable: true})
WHERE (ba)-[:INCURS]->(ei)
  AND ei.valid_from >= ty.start_date 
  AND ei.valid_from <= ty.end_date
  AND ei.retracted_at IS NULL
WITH ei, ba, ty
MATCH (ei)-[:DERIVED_FROM]->(e:Evidence)-[:SUPPORTED_BY]->(d:Document)
RETURN 
  ba.business_name as business_name,
  sum(ei.amount) as box_31_expenses,
  collect(DISTINCT {
    amount: ei.amount,
    category: ei.category,
    description: ei.description,
    source: d.doc_id,
    page: e.page,
    bbox: e.bbox,
    text_hash: e.text_hash
  }) as evidence_trail;

// SA105: Property Income Calculation
// Box 20: Total rents and other income from property
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (pa:PropertyAsset)-[:OWNED_BY]->(tp)
MATCH (ii:IncomeItem {type: 'property'})
WHERE (pa)-[:GENERATES]->(ii)
  AND ii.valid_from >= ty.start_date 
  AND ii.valid_from <= ty.end_date
  AND ii.retracted_at IS NULL
WITH ii, pa, ty
MATCH (ii)-[:DERIVED_FROM]->(e:Evidence)-[:SUPPORTED_BY]->(d:Document)
RETURN 
  pa.address as property_address,
  pa.usage as property_usage,
  sum(ii.gross) as box_20_rental_income,
  collect(DISTINCT {
    amount: ii.gross,
    period_start: ii.period_start,
    period_end: ii.period_end,
    source: d.doc_id,
    page: e.page,
    bbox: e.bbox,
    text_hash: e.text_hash
  }) as evidence_trail;

// Box 29: Total allowable property expenses
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (pa:PropertyAsset)-[:OWNED_BY]->(tp)
MATCH (ei:ExpenseItem {type: 'property', allowable: true})
WHERE (pa)-[:INCURS]->(ei)
  AND ei.valid_from >= ty.start_date 
  AND ei.valid_from <= ty.end_date
  AND ei.retracted_at IS NULL
WITH ei, pa, ty
MATCH (ei)-[:DERIVED_FROM]->(e:Evidence)-[:SUPPORTED_BY]->(d:Document)
RETURN 
  pa.address as property_address,
  sum(ei.amount) as box_29_expenses,
  collect(DISTINCT {
    amount: ei.amount,
    category: ei.category,
    description: ei.description,
    source: d.doc_id,
    page: e.page,
    bbox: e.bbox,
    text_hash: e.text_hash
  }) as evidence_trail;

// SA110: Tax Calculation Summary
// Personal allowance calculation with tapering
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (r:Rule {rule_id: 'personal_allowance_' + $tax_year})
WITH tp, ty, r,
  CASE 
    WHEN $total_income <= r.taper_threshold 
    THEN r.full_allowance
    WHEN $total_income >= r.taper_threshold + (2 * r.full_allowance)
    THEN 0
    ELSE r.full_allowance - (($total_income - r.taper_threshold) / 2)
  END as personal_allowance
RETURN personal_allowance;

// Income tax calculation with bands
MATCH (ty:TaxYear {label: $tax_year})
MATCH (r:Rule {rule_id: 'income_tax_bands_' + $tax_year})
WITH $taxable_income as income, r
RETURN 
  CASE 
    WHEN income <= r.basic_rate_threshold
    THEN income * r.basic_rate
    WHEN income <= r.higher_rate_threshold  
    THEN (r.basic_rate_threshold * r.basic_rate) + 
         ((income - r.basic_rate_threshold) * r.higher_rate)
    ELSE (r.basic_rate_threshold * r.basic_rate) + 
         ((r.higher_rate_threshold - r.basic_rate_threshold) * r.higher_rate) +
         ((income - r.higher_rate_threshold) * r.additional_rate)
  END as income_tax_due;

// NIC Class 4 calculation for self-employed
MATCH (ty:TaxYear {label: $tax_year})
MATCH (r:Rule {rule_id: 'nic_class4_' + $tax_year})
WITH $self_employment_profit as profit, r
RETURN 
  CASE 
    WHEN profit <= r.lower_threshold
    THEN 0
    WHEN profit <= r.upper_threshold
    THEN (profit - r.lower_threshold) * r.main_rate
    ELSE ((r.upper_threshold - r.lower_threshold) * r.main_rate) +
         ((profit - r.upper_threshold) * r.additional_rate)
  END as nic_class4_due;

// Property interest restriction (20% credit)
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (pa:PropertyAsset)-[:OWNED_BY]->(tp)
MATCH (ei:ExpenseItem {category: 'mortgage_interest'})
WHERE (pa)-[:INCURS]->(ei)
  AND ei.valid_from >= ty.start_date 
  AND ei.valid_from <= ty.end_date
  AND ei.retracted_at IS NULL
WITH sum(ei.amount) as total_interest
RETURN 
  total_interest as mortgage_interest_paid,
  total_interest * 0.20 as interest_relief_credit,
  total_interest * 0.80 as disallowed_interest;

// Furnished Holiday Lettings (FHL) qualification test
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
MATCH (pa:PropertyAsset {usage: 'furnished_holiday_letting'})-[:OWNED_BY]->(tp)
OPTIONAL MATCH (pa)-[:HAS_METRIC]->(m:PropertyMetric)
WHERE m.metric_type IN ['availability_days', 'letting_days', 'longer_term_days']
  AND m.tax_year = $tax_year
WITH pa, 
  collect(CASE WHEN m.metric_type = 'availability_days' THEN m.value END)[0] as availability,
  collect(CASE WHEN m.metric_type = 'letting_days' THEN m.value END)[0] as letting,
  collect(CASE WHEN m.metric_type = 'longer_term_days' THEN m.value END)[0] as longer_term
RETURN 
  pa.address as property_address,
  availability >= 210 as availability_test_passed,
  letting >= 105 as letting_test_passed,
  (longer_term IS NULL OR longer_term <= 155) as longer_term_test_passed,
  (availability >= 210 AND letting >= 105 AND (longer_term IS NULL OR longer_term <= 155)) as fhl_qualified;

// Validation query: Income/expense reconciliation
MATCH (tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (ty:TaxYear {label: $tax_year})
OPTIONAL MATCH (ii:IncomeItem)-[:BELONGS_TO]->(tp)
WHERE ii.valid_from >= ty.start_date AND ii.valid_from <= ty.end_date
OPTIONAL MATCH (ei:ExpenseItem)-[:BELONGS_TO]->(tp)  
WHERE ei.valid_from >= ty.start_date AND ei.valid_from <= ty.end_date
OPTIONAL MATCH (p:Payment)-[:BELONGS_TO]->(tp)
WHERE p.valid_from >= ty.start_date AND p.valid_from <= ty.end_date
RETURN 
  sum(ii.gross) as total_income,
  sum(ei.amount) as total_expenses,
  sum(CASE WHEN p.direction = 'outgoing' THEN p.amount ELSE 0 END) as total_payments_out,
  sum(CASE WHEN p.direction = 'incoming' THEN p.amount ELSE 0 END) as total_payments_in,
  abs(sum(ii.gross) - sum(CASE WHEN p.direction = 'incoming' THEN p.amount ELSE 0 END)) as income_reconciliation_delta,
  abs(sum(ei.amount) - sum(CASE WHEN p.direction = 'outgoing' THEN p.amount ELSE 0 END)) as expense_reconciliation_delta;

// Time-travel query: Get facts as of specific date
CALL temporal.asOf($as_of_date) YIELD node
WHERE node:IncomeItem OR node:ExpenseItem
WITH node
MATCH (node)-[:BELONGS_TO]->(tp:TaxpayerProfile {taxpayer_id: $taxpayer_id})
MATCH (node)-[:DERIVED_FROM]->(e:Evidence)-[:SUPPORTED_BY]->(d:Document)
RETURN 
  labels(node)[0] as node_type,
  node.type as item_type,
  CASE WHEN node:IncomeItem THEN node.gross ELSE node.amount END as amount,
  node.valid_from as valid_from,
  node.valid_to as valid_to,
  node.asserted_at as asserted_at,
  d.doc_id as source_document,
  e.page as source_page,
  e.text_hash as evidence_hash
ORDER BY node.valid_from, node.asserted_at;