System Changes - [MFG - Integrate Bank Reco]

Upload Bank PDF + Reco Excel
        ↓
Service reads both files
        ↓
Send data + prompts to AI
        ↓
AI returns JSON matches
        ↓
C# updates Excel data
        ↓
ClosedXML generates Excel files
        ↓
Return Excel files to user

You are a financial bank reconciliation engine.

Your task is to reconcile bank statement transactions with voucher records from a reconciliation model.

Follow all rules strictly. Do not guess or assume matches.

--------------------------------------------------
GENERAL RULES
--------------------------------------------------
1. Never modify any existing Reco Date value in the reconciliation model.
2. If Reco Date is already filled, skip that voucher record completely.
3. Only fill Reco Date for vouchers where it is blank.
4. Do not create new columns in the output.
5. Use the existing Reco Date column present in the reconciliation model.

--------------------------------------------------
DATA STANDARDIZATION
--------------------------------------------------
Before matching, normalize the following:

Cheque Numbers:
- Remove spaces
- Remove non-numeric characters
- Extract digits only
- Pad with zeros to 6 digits (Example: 999 → 000999)

Text Fields:
- Trim spaces
- Ignore case
- Ignore punctuation

--------------------------------------------------
TRANSACTION TYPE MAPPING
--------------------------------------------------
Bank Statement:
- Debit Amount > 0 → Payment
- Credit Amount > 0 → Receipt

Voucher Records:
- VoucherType = Payment → Payment
- VoucherType = Receipt → Receipt

STRICT RULE:
- Payment must match Payment
- Receipt must match Receipt
- Do not match opposite transaction types

--------------------------------------------------
MATCHING ORDER (STRICT PRIORITY)
--------------------------------------------------
Apply rules in this exact order:

1. Cheque Number Match
2. Transaction ID Match (only if column exists)
3. Group Matching
4. Bank Specific Rules
5. Name + Amount Match

--------------------------------------------------
MATCH USAGE RULE
--------------------------------------------------
- Once a bank transaction is matched, mark it as used
- Do not reuse the same bank transaction again

--------------------------------------------------
OUTPUT FORMAT (STRICT JSON ONLY)
--------------------------------------------------
Return ONLY valid JSON. No explanations.

{
  "matches": [
    {
      "voucherId": "",
      "voucherNumber": "",
      "bankTransactionIndex": "",
      "recoDate": "",
      "matchBasis": ""
    }
  ],
  "hasMore": false,
  "nextOffset": 0
}

--------------------------------------------------
OUTPUT RULES
--------------------------------------------------
- recoDate format must be: dd-MMM-yy
- matchBasis must describe the rule used
- Do not include any extra fields
- Do not include any explanation text

--------------------------------------------------
OUTPUT SIZE CONTROL (VERY IMPORTANT)
--------------------------------------------------
- Return between 50 and 100 matches if available
- Do NOT stop early unless no more matches exist
- Ensure JSON is always complete and valid
- Never truncate JSON output

--------------------------------------------------
PAGINATION RULE
--------------------------------------------------
- If more matches remain:
    - set "hasMore": true
    - set "nextOffset" to the next starting index
- If no more matches:
    - set "hasMore": false
    - set "nextOffset": 0

--------------------------------------------------
FINAL INSTRUCTION
--------------------------------------------------
Ensure the JSON is COMPLETE, CLOSED, and VALID.
Never cut off mid-response.

You are a document validation engine.

Your task is to determine whether the uploaded document is a valid bank statement.

A valid bank statement typically contains:

- Bank name
- Account number
- Transaction table
- Columns such as Date, Description, Debit, Credit, Balance

Validation Rules

1. If the document clearly appears to be a bank statement, return:

VALID

2. If the document is not a bank statement, return:

INVALID

3. Return only one word:

VALID
or
INVALID

Do not include any explanation or additional text.

Apply the following reconciliation rules for all banks.

RULE 1 – CHEQUE NUMBER MATCH

If cheque number exists in both bank statement and voucher records,
match using:

Cheque Number + Voucher Type + Amount

RULE 2 – GROUP MATCHING

Allow reconciliation in the following cases:

Multiple bank transactions → one voucher

One bank transaction → multiple vouchers

When performing grouped matching:
- Sum the absolute values
- Ensure totals match exactly.

RULE 3 – NAME MATCHING

If cheque number is not available,
match using:

Party Name + Voucher Type + Amount

The party name may appear partially in the bank description.

RULE 4 – EXISTING RECONCILIATION

If Reco Date in the voucher record already contains a value:
- Do not modify it
- Skip that record from reconciliation.

RULE 5 – DATA PRESERVATION

Do not modify:

Voucher Number  
Voucher Type  
Amount  
Description  

Only determine the correct Reco Date and match basis.

Apply the following bank specific rules.

1. Ignore bank statement records where:

Description contains "TDISSUE"

AND

Amount is a multiple of 25000.

These transactions should not be reconciled.

2. TransactionId matching

The reconciliation model for this bank contains a TransactionId column.

If TransactionId exists in both voucher records and bank description,
match using:

TransactionId + Voucher Type + Amount.

This rule has higher priority than name matching.

3. Transfer Entries

If bank description contains:

CSHDEP

Treat it as transfer of fund.

Match using:

Amount + Voucher Type.

4. Group Reconciliation

Multiple bank statement entries may match one voucher.

One bank entry may match multiple vouchers.

Ensure total amount matches exactly.

5. Cheque Number Padding

Some cheque numbers appear in bank statement as short numbers.

Example:
999

Convert them to 6 digit format before matching.

Example:
999 → 000999

Apply the following bank specific rules.

1. Ignore bank statement transactions where description contains:

INT

These represent interest entries and should not be reconciled.

2. TDS Entries

If bank description contains "GST",
it may correspond to TDS vouchers.

Match using:

Amount + Voucher Type.

3. Standard Matching

If no identifier exists,
match using:

Party Name + Voucher Type + Amount.

No TransactionId column exists for this bank.

public async Task<string> BankReconciliationAI(string dbname,
string bankStatementFile, string recoModelFile, string bankCode)
{
    // STEP 1 — Load Prompts
    string systemPrompt = await GetPrompt("RecoSystemPrompt");
    string commonBankPrompt = await GetPrompt("RecoCommonBankPrompt");
    string bankPrompt = await GetPrompt(bankCode);
    string validationPrompt = await GetPrompt("RecoValidationPrompt");

    // STEP 2 — Read Files
    var bankBytes = await File.ReadAllBytesAsync(bankStatementFile);
    var recoBytes = await File.ReadAllBytesAsync(recoModelFile);

    var bankName = Path.GetFileNameWithoutExtension(bankStatementFile);
    var recoName = Path.GetFileNameWithoutExtension(recoModelFile);

    // STEP 3 — Validate Bank Statement
    var validationRequest = new ConverseRequest
    {
        ModelId = _modelId,
        System = new List<SystemContentBlock>
        {
            new SystemContentBlock
            {
                Text = "You are a financial document validator."
            }
        },
        Messages = new List<Message>
        {
            new Message
            {
                Role = ConversationRole.User,
                Content = new List<ContentBlock>
                {
                    new ContentBlock { Text = validationPrompt },
                    new ContentBlock
                    {
                        Document = new DocumentBlock
                        {
                            Format = DocumentFormat.Pdf,
                            Name = bankName,
                            Source = new DocumentSource
                            {
                                Bytes = new MemoryStream(bankBytes)
                            }
                        }
                    }
                }
            }
        }
    };

    var validationResponse = await _bedrockClient.ConverseAsync(validationRequest);
    var validationResult = validationResponse?.Output?.Message?.Content?[0]?.Text?.Trim().ToUpper();

    if (validationResult != "VALID")
    {
        throw new Exception("Invalid bank statement document.");
    }

    try
    {
        // STEP 4 — Prepare User Prompt
        string userPrompt = $@"
        Apply reconciliation rules.

        COMMON BANK RULES
        {commonBankPrompt}

        BANK SPECIFIC RULES
        {bankPrompt}

        Return result in JSON format only.
        ";

        // STEP 5 — AI Request
        var request = new ConverseRequest
        {
            ModelId = _modelId,
            System = new List<SystemContentBlock>
            {
                new SystemContentBlock { Text = systemPrompt }
            },
            Messages = new List<Message>
            {
                new Message
                {
                    Role = ConversationRole.User,
                    Content = new List<ContentBlock>
                    {
                        new ContentBlock { Text = userPrompt },

                        new ContentBlock
                        {
                            Document = new DocumentBlock
                            {
                                Format = DocumentFormat.Pdf,
                                Name = bankName,
                                Source = new DocumentSource
                                {
                                    Bytes = new MemoryStream(bankBytes)
                                }
                            }
                        },

                        new ContentBlock
                        {
                            Document = new DocumentBlock
                            {
                                Format = DocumentFormat.Xlsx,
                                Name = recoName,
                                Source = new DocumentSource
                                {
                                    Bytes = new MemoryStream(recoBytes)
                                }
                            }
                        }
                    }
                }
            },
            InferenceConfig = new InferenceConfiguration
            {
                Temperature = 0.0f,
                TopP = 0.2f
            }
        };

        // STEP 6 — Call AI
        var response = await _bedrockClient.ConverseAsync(request);
        var jsonResult = response?.Output?.Message?.Content?[0]?.Text ?? "";

        if (string.IsNullOrEmpty(jsonResult))
        {
            throw new Exception("AI returned empty response.");
        }

        // STEP 7 — Deserialize AI JSON
        var recoResult = JsonSerializer.Deserialize<RecoResult>(jsonResult);

        // STEP 8 — Generate Excel File
        string excelFile = await CreateBankRecoExcel(recoResult, recoModelFile, bankStatementFile, dbname);

        return (excelFile);
    }
    catch (AmazonBedrockRuntimeException ex)
    {
        throw new Exception($"Bedrock invocation failed: {ex.Message}", ex);
    }
}