Payment Reconciliation Design

CREATE TABLE recon (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(2) NOT NULL,                    -- Discriminator: 'PF', 'PG'

    -- Transaction details
    date DATETIME,
    customer VARCHAR(50),
    currency VARCHAR(3),
    gross DECIMAL(19,2),
    fee DECIMAL(19,2),
    fee_tax DECIMAL(19,2),
    nett DECIMAL(19,2),

    -- Relationships
    order_id BIGINT,                             -- FK to sales_order
    order_system_id BIGINT,                      -- FK to registration_system

    CONSTRAINT fk_recon_order_system
        FOREIGN KEY (order_system_id) REFERENCES registration_system(id)
);

CREATE TABLE recon_payfast (
    id BIGINT PRIMARY KEY,                       -- FK to recon.id

    -- PayFast-specific fields
    payfast_payment_id BIGINT NOT NULL,          -- Unique PayFast transaction ID
    merchant_payment_id BIGINT,                  -- Merchant reference (for order matching)
    sign VARCHAR(6) NOT NULL,                    -- CREDIT or DEBIT
    party VARCHAR(50),
    name VARCHAR(50),
    description VARCHAR(100),
    funding_type VARCHAR(25),
    balance DECIMAL(19,2),
    account INT DEFAULT 2,
    custom1 VARCHAR(100),
    custom2 VARCHAR(100),
    custom3 VARCHAR(100),

    CONSTRAINT fk_recon_payfast_recon
        FOREIGN KEY (id) REFERENCES recon(id) ON DELETE CASCADE,
    CONSTRAINT uk_payfast_payment_id
        UNIQUE (payfast_payment_id)
);

CREATE TABLE recon_pay_gate (
    id BIGINT PRIMARY KEY,                       -- FK to recon.id

    -- PayGate-specific fields
    transaction_id BIGINT,                       -- Unique PayGate transaction ID
    batch BIGINT,
    number VARCHAR(20),
    record_type VARCHAR(20),
    transaction_date DATETIME,
    auth_code VARCHAR(6),
    result_code BIGINT,
    result_description VARCHAR(50),
    transaction_source VARCHAR(25),
    original_currency VARCHAR(3),
    original_amount DECIMAL(19,2),
    comment VARCHAR(100),
    product_description VARCHAR(100),

    CONSTRAINT fk_recon_paygate_recon
        FOREIGN KEY (id) REFERENCES recon(id) ON DELETE CASCADE,
    CONSTRAINT uk_paygate_transaction_id
        UNIQUE (transaction_id)
);

@Service
public class PayfastReconImportService extends BaseReconImportService {

    public ReconImportResult importCsv(InputStream csvStream, Organisation org) {
        // 1. Parse CSV with SuperCSV
        // 2. For each row:
        //    - Check if payfastPaymentId already exists (skip if duplicate)
        //    - Create ReconPayfast entity
        //    - Set customer from party field
        //    - Match to Order via merchantPaymentId
        //    - Persist entity
        // 3. Return statistics (created, skipped, errors)
    }
}

@Service
public class PaygateReconImportService extends BaseReconImportService {

    private static final BigDecimal FIXED_FEE = new BigDecimal("2.00");
    private static final BigDecimal PERCENT_FEE = new BigDecimal("0.035");
    private static final BigDecimal VAT_RATE = new BigDecimal("0.15");

    public ReconImportResult importCsv(InputStream csvStream, Organisation org) {
        // 1. Parse CSV with SuperCSV
        // 2. For each row:
        //    - Check if transactionId already exists (skip if duplicate)
        //    - Create ReconPaygate entity
        //    - Calculate fees if resultCode == 990018
        //    - Match to Order via reference field
        //    - Persist entity
        // 3. Return statistics
    }

    private void calculateFees(ReconPaygate recon) {
        if (recon.getResultCode() == 990018L) {
            // Only calculate for successful transactions
            BigDecimal gross = recon.getGross();

            // Fee = R2.00 + 3.5% of gross
            BigDecimal fee = FIXED_FEE.add(gross.multiply(PERCENT_FEE));

            // VAT on fee = 15%
            BigDecimal feeTax = fee.multiply(VAT_RATE);

            // Net = gross - fee - feeTax
            BigDecimal nett = gross.subtract(fee).subtract(feeTax);

            recon.setFee(fee);
            recon.setFeeTax(feeTax);
            recon.setNett(nett);
        }
    }
}

fee = R2.00 + (gross × 3.5%)
feeTax = fee × 15%
nett = gross - fee - feeTax

protected Order findOrder(String reference) {
    if (reference == null || reference.isEmpty()) {
        return null;
    }

    // Handle reference formats:
    // - "12345" (simple ID)
    // - "12345-12345" (ID with suffix)
    String orderId = reference.contains("-")
        ? reference.substring(0, reference.indexOf("-"))
        : reference;

    try {
        Long id = Long.parseLong(orderId);
        return orderRepository.findById(id).orElse(null);
    } catch (NumberFormatException e) {
        return null;
    }
}

-- Migrate discriminator values
UPDATE recon SET type = 'PF' WHERE type = 'Payfast';
UPDATE recon SET type = 'PG' WHERE type = 'Paygate';

-- Reduce column size
ALTER TABLE recon MODIFY type VARCHAR(2) NOT NULL;

-- Drop FK before rename
ALTER TABLE payfast_recon DROP FOREIGN KEY FK1md70kejy5u0ct02ue70lx5pb;

-- Rename table
RENAME TABLE payfast_recon TO recon_payfast;

-- Recreate FK
ALTER TABLE recon_payfast
    ADD CONSTRAINT fk_recon_payfast_recon
    FOREIGN KEY (id) REFERENCES recon(id) ON DELETE CASCADE;

-- Add FK to recon_pay_gate (currently missing)
ALTER TABLE recon_pay_gate
    ADD CONSTRAINT fk_recon_paygate_recon
    FOREIGN KEY (id) REFERENCES recon(id) ON DELETE CASCADE;