generate_test_data.py

#!/usr/bin/env python3
"""
Generate test Excel file for Scope 3.4 & 3.9 validation testing.
Based on validation rules from scope-3.4&3.9.columns.ts and scope-3.4&3.9.ts
"""

import openpyxl
from openpyxl.styles import PatternFill, Font
import sys

# Column headers based on SCOPE_34_39_COLUMNS
HEADERS = [
    'Service Operator',           # A
    'Service Description',         # B (was S in validation)
    'Transport Mode',             # C
    'Mass',                       # D
    'Mass unit',                  # E
    'Distance',                   # F
    'Origin address',             # G (was H in validation)
    'Origin City',                # H (was G in validation)
    'Origin postal code',         # I
    'Origin country',             # J
    'Destination address',        # K (was L in validation)
    'Destination city',           # L (was K in validation)
    'Destination postal code',    # M
    'Destination Country',        # N
    'Euro spend',                 # O
    'Data owner',                 # P
    'Data quality',               # Q
    'Data source',                # R
    'Comment',                    # S
]

# Test data rows covering all validation scenarios
TEST_ROWS = [
    # Row 1: VALID - Complete data with addresses (Combo 1: C, D, E, H, J, L, N)
    {
        'Service Operator': 'DHL Express',
        'Service Description': 'International shipping service',
        'Transport Mode': 'Road',
        'Mass': '1500',
        'Mass unit': 'kg',
        'Distance': '',
        'Origin address': '123 Main Street',
        'Origin City': 'Berlin',
        'Origin postal code': '',
        'Origin country': 'Germany',
        'Destination address': '456 Oak Avenue',
        'Destination city': 'Paris',
        'Destination postal code': '',
        'Destination Country': 'France',
        'Euro spend': '',
        'Data owner': 'John Doe',
        'Data quality': 'Invoice',
        'Data source': 'SAP System',
        'Comment': 'Valid complete record with addresses',
    },

    # Row 2: VALID - Complete data with postal codes (Combo 2: C, D, E, I, J, M, N)
    {
        'Service Operator': 'FedEx',
        'Service Description': 'Domestic freight',
        'Transport Mode': 'Air',
        'Mass': '2500.5',
        'Mass unit': 't',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '10115',
        'Origin country': 'DE',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': 'SW1A 1AA',
        'Destination Country': 'United Kingdom',
        'Euro spend': '',
        'Data owner': 'Jane Smith',
        'Data quality': 'Metering',
        'Data source': 'TMS',
        'Comment': 'Valid with postal codes',
    },

    # Row 3: VALID - Distance-based (Combo 3: C, D, E, F)
    {
        'Service Operator': 'UPS',
        'Service Description': 'Express delivery',
        'Transport Mode': 'Sea',
        'Mass': '3000',
        'Mass unit': 'lb',
        'Distance': '500.75',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Bob Johnson',
        'Data quality': 'Calculation',
        'Data source': 'Excel',
        'Comment': 'Valid distance-based calculation',
    },

    # Row 4: VALID - Euro spend only (Combo 4: C, O)
    {
        'Service Operator': 'Maersk',
        'Service Description': 'Container shipping',
        'Transport Mode': 'Rail',
        'Mass': '',
        'Mass unit': '#Please select',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '15000.50',
        'Data owner': 'Alice Williams',
        'Data quality': 'Estimation',
        'Data source': 'Manual Entry',
        'Comment': 'Valid with euro spend only',
    },

    # Row 5: VALID - US country with postal codes
    {
        'Service Operator': 'USPS',
        'Service Description': 'US domestic mail',
        'Transport Mode': 'Road',
        'Mass': '100',
        'Mass unit': 'kg',
        'Distance': '',
        'Origin address': '',
        'Origin City': 'New York',
        'Origin postal code': '10001',
        'Origin country': 'USA',
        'Destination address': '',
        'Destination city': 'Los Angeles',
        'Destination postal code': '90001',
        'Destination Country': 'United States',
        'Euro spend': '',
        'Data owner': 'Mike Brown',
        'Data quality': 'Invoice',
        'Data source': 'API',
        'Comment': 'Valid US shipment with postal codes',
    },

    # Row 6: INVALID - City with numbers (Origin City validation)
    {
        'Service Operator': 'Test Carrier 1',
        'Service Description': 'Test service',
        'Transport Mode': 'Road',
        'Mass': '500',
        'Mass unit': 'kg',
        'Distance': '',
        'Origin address': '789 Test St',
        'Origin City': 'Berlin123',  # INVALID: contains numbers
        'Origin postal code': '',
        'Origin country': 'Germany',
        'Destination address': '321 Test Ave',
        'Destination city': 'Munich',
        'Destination postal code': '',
        'Destination Country': 'Germany',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Invoice',
        'Data source': 'Test',
        'Comment': 'INVALID: Origin city contains numbers',
    },

    # Row 7: INVALID - City with special characters (& + ( ) ,)
    {
        'Service Operator': 'Test Carrier 2',
        'Service Description': 'Test service',
        'Transport Mode': 'Air',
        'Mass': '750',
        'Mass unit': 't',
        'Distance': '',
        'Origin address': '111 Origin Rd',
        'Origin City': 'Frankfurt',
        'Origin postal code': '',
        'Origin country': 'Germany',
        'Destination address': '222 Dest Rd',
        'Destination city': 'Paris & Lyon',  # INVALID: contains &
        'Destination postal code': '',
        'Destination Country': 'France',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Metering',
        'Data source': 'Test',
        'Comment': 'INVALID: Destination city contains & character',
    },

    # Row 8: INVALID - Postal code too short
    {
        'Service Operator': 'Test Carrier 3',
        'Service Description': 'Test service',
        'Transport Mode': 'Sea',
        'Mass': '1000',
        'Mass unit': 'kg',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '12',  # INVALID: only 2 characters (needs 3-8)
        'Origin country': 'Germany',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '75001',
        'Destination Country': 'France',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Calculation',
        'Data source': 'Test',
        'Comment': 'INVALID: Origin postal code too short',
    },

    # Row 9: INVALID - Postal code too long
    {
        'Service Operator': 'Test Carrier 4',
        'Service Description': 'Test service',
        'Transport Mode': 'Rail',
        'Mass': '1200',
        'Mass unit': 't',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '10115',
        'Origin country': 'Germany',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '123456789',  # INVALID: 9 characters (max 8)
        'Destination Country': 'France',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Estimation',
        'Data source': 'Test',
        'Comment': 'INVALID: Destination postal code too long',
    },

    # Row 10: INVALID - Postal code with invalid characters
    {
        'Service Operator': 'Test Carrier 5',
        'Service Description': 'Test service',
        'Transport Mode': 'Road',
        'Mass': '800',
        'Mass unit': 'lb',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': 'AB@123',  # INVALID: contains @ (only letters, numbers, space, hyphen allowed)
        'Origin country': 'Germany',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '75001',
        'Destination Country': 'France',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Invoice',
        'Data source': 'Test',
        'Comment': 'INVALID: Origin postal code contains @ character',
    },

    # Row 11: INVALID - Mass is zero
    {
        'Service Operator': 'Test Carrier 6',
        'Service Description': 'Test service',
        'Transport Mode': 'Air',
        'Mass': '0',  # INVALID: must be > 0
        'Mass unit': 'kg',
        'Distance': '100',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Metering',
        'Data source': 'Test',
        'Comment': 'INVALID: Mass is zero (must be > 0)',
    },

    # Row 12: INVALID - Mass is negative
    {
        'Service Operator': 'Test Carrier 7',
        'Service Description': 'Test service',
        'Transport Mode': 'Sea',
        'Mass': '-500',  # INVALID: must be > 0
        'Mass unit': 't',
        'Distance': '200',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Calculation',
        'Data source': 'Test',
        'Comment': 'INVALID: Mass is negative',
    },

    # Row 13: INVALID - Distance is zero
    {
        'Service Operator': 'Test Carrier 8',
        'Service Description': 'Test service',
        'Transport Mode': 'Rail',
        'Mass': '1000',
        'Mass unit': 'kg',
        'Distance': '0',  # INVALID: must be > 0
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Estimation',
        'Data source': 'Test',
        'Comment': 'INVALID: Distance is zero',
    },

    # Row 14: INVALID - Distance is negative
    {
        'Service Operator': 'Test Carrier 9',
        'Service Description': 'Test service',
        'Transport Mode': 'Road',
        'Mass': '1500',
        'Mass unit': 't',
        'Distance': '-150.5',  # INVALID: must be > 0
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Invoice',
        'Data source': 'Test',
        'Comment': 'INVALID: Distance is negative',
    },

    # Row 15: INVALID - Euro spend is zero
    {
        'Service Operator': 'Test Carrier 10',
        'Service Description': 'Test service',
        'Transport Mode': 'Air',
        'Mass': '',
        'Mass unit': '#Please select',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '0',  # INVALID: must be > 0
        'Data owner': 'Test User',
        'Data quality': 'Metering',
        'Data source': 'Test',
        'Comment': 'INVALID: Euro spend is zero',
    },

    # Row 16: INVALID - Euro spend is negative
    {
        'Service Operator': 'Test Carrier 11',
        'Service Description': 'Test service',
        'Transport Mode': 'Sea',
        'Mass': '',
        'Mass unit': '#Please select',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '-5000',  # INVALID: must be > 0
        'Data owner': 'Test User',
        'Data quality': 'Calculation',
        'Data source': 'Test',
        'Comment': 'INVALID: Euro spend is negative',
    },

    # Row 17: INVALID - Invalid origin country
    {
        'Service Operator': 'Test Carrier 12',
        'Service Description': 'Test service',
        'Transport Mode': 'Rail',
        'Mass': '2000',
        'Mass unit': 'kg',
        'Distance': '',
        'Origin address': '123 Test St',
        'Origin City': 'Test City',
        'Origin postal code': '',
        'Origin country': 'Atlantis',  # INVALID: not a valid country
        'Destination address': '456 Test Ave',
        'Destination city': 'Paris',
        'Destination postal code': '',
        'Destination Country': 'France',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Estimation',
        'Data source': 'Test',
        'Comment': 'INVALID: Origin country is not valid',
    },

    # Row 18: INVALID - Invalid destination country
    {
        'Service Operator': 'Test Carrier 13',
        'Service Description': 'Test service',
        'Transport Mode': 'Road',
        'Mass': '1800',
        'Mass unit': 't',
        'Distance': '',
        'Origin address': '789 Test Rd',
        'Origin City': 'Berlin',
        'Origin postal code': '',
        'Origin country': 'Germany',
        'Destination address': '321 Test Blvd',
        'Destination city': 'Test City',
        'Destination postal code': '',
        'Destination Country': 'Narnia',  # INVALID: not a valid country
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Invoice',
        'Data source': 'Test',
        'Comment': 'INVALID: Destination country is not valid',
    },

    # Row 19: INVALID - Missing transport mode (always required)
    {
        'Service Operator': 'Test Carrier 14',
        'Service Description': 'Test service',
        'Transport Mode': '#Please select',  # INVALID: always required
        'Mass': '1000',
        'Mass unit': 'kg',
        'Distance': '100',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Metering',
        'Data source': 'Test',
        'Comment': 'INVALID: Transport mode is required',
    },

    # Row 20: INVALID - Mass unit filled but mass empty
    {
        'Service Operator': 'Test Carrier 15',
        'Service Description': 'Test service',
        'Transport Mode': 'Air',
        'Mass': '',  # INVALID: required when mass_unit is filled
        'Mass unit': 'kg',
        'Distance': '',
        'Origin address': '123 Test',
        'Origin City': 'Berlin',
        'Origin postal code': '',
        'Origin country': 'Germany',
        'Destination address': '456 Test',
        'Destination city': 'Paris',
        'Destination postal code': '',
        'Destination Country': 'France',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Calculation',
        'Data source': 'Test',
        'Comment': 'INVALID: Mass unit filled but mass empty',
    },

    # Row 21: INVALID - US country without postal codes
    {
        'Service Operator': 'Test Carrier 16',
        'Service Description': 'Test service',
        'Transport Mode': 'Sea',
        'Mass': '2500',
        'Mass unit': 't',
        'Distance': '',
        'Origin address': '123 US Street',
        'Origin City': 'New York',
        'Origin postal code': '',  # INVALID: required for US
        'Origin country': 'United States of America',
        'Destination address': '456 US Ave',
        'Destination city': 'Chicago',
        'Destination postal code': '',  # INVALID: required for US
        'Destination Country': 'USA',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Estimation',
        'Data source': 'Test',
        'Comment': 'INVALID: US country requires postal codes',
    },

    # Row 22: INVALID - Transport mode filled but no valid combination
    {
        'Service Operator': 'Test Carrier 17',
        'Service Description': 'Test service',
        'Transport Mode': 'Rail',  # Filled
        'Mass': '1000',  # Filled
        'Mass unit': 'kg',  # Filled
        'Distance': '',  # Empty - so D,E,F combo incomplete
        'Origin address': '',  # Empty - so D,E,H,J,L,N combo incomplete
        'Origin City': '',
        'Origin postal code': '',  # Empty - so D,E,I,J,M,N combo incomplete
        'Origin country': '',  # Empty - combos incomplete
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',  # Empty - O combo incomplete
        'Data owner': 'Test User',
        'Data quality': 'Invoice',
        'Data source': 'Test',
        'Comment': 'INVALID: Transport mode filled but no valid combination complete',
    },

    # Row 23: INVALID - Distance filled but mass and mass_unit missing
    {
        'Service Operator': 'Test Carrier 18',
        'Service Description': 'Test service',
        'Transport Mode': 'Road',
        'Mass': '',  # INVALID: required when distance is filled
        'Mass unit': '#Please select',  # INVALID: required when distance is filled
        'Distance': '250',  # Filled
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Metering',
        'Data source': 'Test',
        'Comment': 'INVALID: Distance filled but mass/mass_unit empty',
    },

    # Row 24: INVALID - Euro spend filled but transport mode missing
    {
        'Service Operator': 'Test Carrier 19',
        'Service Description': 'Test service',
        'Transport Mode': '#Please select',  # INVALID: required when euro_spend is filled
        'Mass': '',
        'Mass unit': '#Please select',
        'Distance': '',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '10000',  # Filled
        'Data owner': 'Test User',
        'Data quality': 'Calculation',
        'Data source': 'Test',
        'Comment': 'INVALID: Euro spend filled but transport mode missing',
    },

    # Row 25: INVALID - Missing required fields (mass and mass_unit when euro_spend empty)
    {
        'Service Operator': 'Test Carrier 20',
        'Service Description': 'Test service',
        'Transport Mode': 'Air',
        'Mass': '',  # INVALID: required when euro_spend is empty
        'Mass unit': '#Please select',  # INVALID: required when euro_spend is empty
        'Distance': '',
        'Origin address': '123 Test',
        'Origin City': 'Berlin',
        'Origin postal code': '',
        'Origin country': 'Germany',
        'Destination address': '456 Test',
        'Destination city': 'Paris',
        'Destination postal code': '',
        'Destination Country': 'France',
        'Euro spend': '',  # Empty
        'Data owner': 'Test User',
        'Data quality': 'Estimation',
        'Data source': 'Test',
        'Comment': 'INVALID: Mass and mass_unit required when euro_spend is empty',
    },

    # Row 26: WARNING - Missing service description (warning only)
    {
        'Service Operator': 'Test Carrier 21',
        'Service Description': '',  # WARNING: recommended to be filled
        'Transport Mode': 'Sea',
        'Mass': '3000',
        'Mass unit': 'kg',
        'Distance': '500',
        'Origin address': '',
        'Origin City': '',
        'Origin postal code': '',
        'Origin country': '',
        'Destination address': '',
        'Destination city': '',
        'Destination postal code': '',
        'Destination Country': '',
        'Euro spend': '',
        'Data owner': 'Test User',
        'Data quality': 'Invoice',
        'Data source': 'Test',
        'Comment': 'WARNING: Service description is recommended',
    },
]

def create_test_excel():
    """Create Excel file with test data."""
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Test Data"

    # Style for header row
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_font = Font(bold=True, color="FFFFFF")

    # Write headers
    for col_idx, header in enumerate(HEADERS, start=1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.fill = header_fill
        cell.font = header_font

    # Write test data rows
    for row_idx, row_data in enumerate(TEST_ROWS, start=2):
        for col_idx, header in enumerate(HEADERS, start=1):
            value = row_data.get(header, '')
            ws.cell(row=row_idx, column=col_idx, value=value)

    # Adjust column widths
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = min(max_length + 2, 50)
        ws.column_dimensions[column_letter].width = adjusted_width

    return wb

def main():
    """Main function to generate the test Excel file."""
    print("Generating test Excel file...")
    print(f"Total test rows: {len(TEST_ROWS)}")
    print(f"Total columns: {len(HEADERS)}")

    wb = create_test_excel()

    output_file = '/Users/dominikbetka/Documents/3.4&3.9-test.xlsx'
    wb.save(output_file)

    print(f"\n✓ Test file created successfully: {output_file}")
    print("\nTest cases included:")
    print("  - Rows 1-5: Valid data covering all 4 combination scenarios")
    print("  - Rows 6-7: Invalid city names (with numbers and special chars)")
    print("  - Rows 8-10: Invalid postal codes (length and characters)")
    print("  - Rows 11-12: Invalid mass (zero and negative)")
    print("  - Rows 13-14: Invalid distance (zero and negative)")
    print("  - Rows 15-16: Invalid euro spend (zero and negative)")
    print("  - Rows 17-18: Invalid country names")
    print("  - Row 19: Missing required transport mode")
    print("  - Row 20: Mass unit filled but mass empty")
    print("  - Row 21: US country without postal codes")
    print("  - Row 22: Transport mode with no valid combination")
    print("  - Row 23: Distance filled but mass/mass_unit missing")
    print("  - Row 24: Euro spend filled but transport mode missing")
    print("  - Row 25: Missing mass/mass_unit when euro_spend empty")
    print("  - Row 26: Missing service description (warning)")

if __name__ == '__main__':
    main()