Spreadsheet Struct Verified

#!/usr/bin/env python3
"""Deterministic workbook structure verification script."""

from openpyxl import load_workbook
import sys
from pathlib import Path

def verify_workbook(workbook_path, specification):
    """
    Verify workbook structure against specification.
    
    Args:
        workbook_path: Exact path to the saved workbook
        specification: Dict with required_sheets, required_columns, 
                      expected_row_counts, sample_criteria, etc.
    
    Returns:
        dict with verification_results, passed (bool), errors (list)
    """
    results = {
        'passed': True,
        'errors': [],
        'warnings': [],
        'details': {}
    }
    
    # Load workbook
    try:
        wb = load_workbook(workbook_path, data_only=True)
        results['details']['workbook_loaded'] = True
    except Exception as e:
        results['passed'] = False
        results['errors'].append(f"Failed to load workbook: {e}")
        return results
    
    # Verify required sheets
    actual_sheets = wb.sheetnames
    required_sheets = specification.get('required_sheets', [])
    
    for sheet_name in required_sheets:
        if sheet_name in actual_sheets:
            results['details'][f'sheet_{sheet_name}'] = 'present'
        else:
            results['passed'] = False
            results['errors'].append(f"Missing required sheet: {sheet_name}")
            results['details'][f'sheet_{sheet_name}'] = 'missing'
    
    # Verify each required sheet
    for sheet_name in required_sheets:
        if sheet_name not in actual_sheets:
            continue
            
        ws = wb[sheet_name]
        
        # Get headers from first row
        headers = []
        if ws.max_row >= 1:
            for col in range(1, ws.max_column + 1):
                cell_value = ws.cell(row=1, column=col).value
                headers.append(str(cell_value).strip() if cell_value else '')
        
        results['details'][f'{sheet_name}_headers'] = headers
        
        # Verify required columns for this sheet
        sheet_required_cols = specification.get('required_columns', {}).get(sheet_name, [])
        for col_name in sheet_required_cols:
            if col_name in headers:
                results['details'][f'{sheet_name}_col_{col_name}'] = 'present'
            else:
                results['passed'] = False
                results['errors'].append(
                    f"Missing required column '{col_name}' in sheet '{sheet_name}'"
                )
                results['details'][f'{sheet_name}_col_{col_name}'] = 'missing'
        
        # Count data rows (excluding header)
        data_row_count = max(0, ws.max_row - 1) if ws.max_row > 1 else 0
        results['details'][f'{sheet_name}_row_count'] = data_row_count
        
        # Verify expected row counts
        expected_count = specification.get('expected_row_counts', {}).get(sheet_name)
        if expected_count is not None:
            if data_row_count < expected_count:
                results['passed'] = False
                results['errors'].append(
                    f"Sheet '{sheet_name}' has {data_row_count} rows, expected at least {expected_count}"
                )
        
        # Verify populated columns (spot check)
        for col_name in sheet_required_cols:
            if col_name not in headers:
                continue
            col_idx = headers.index(col_name) + 1
            
            # Check first few data rows are populated
            populated_count = 0
            for row in range(2, min(ws.max_row + 1, 11)):
                cell_value = ws.cell(row=row, column=col_idx).value
                if cell_value is not None and str(cell_value).strip() != '':
                    populated_count += 1
            
            if populated_count == 0 and data_row_count > 0:
                results['warnings'].append(
                    f"Column '{col_name}' in sheet '{sheet_name}' appears empty"
                )
    
    # Verify sample counts if applicable
    sample_mark_col = specification.get('sample_mark_column')
    sample_mark_value = specification.get('sample_mark_value', 'Yes')
    expected_sample_count = specification.get('expected_sample_count')
    
    if sample_mark_col and expected_sample_count is not None:
        for sheet_name in required_sheets:
            if sheet_name not in actual_sheets:
                continue
            ws = wb[sheet_name]
            headers = []
            if ws.max_row >= 1:
                for col in range(1, ws.max_column + 1):
                    cell_value = ws.cell(row=1, column=col).value
                    headers.append(str(cell_value).strip() if cell_value else '')
            
            if sample_mark_col not in headers:
                continue
            
            col_idx = headers.index(sample_mark_col) + 1
            marked_count = 0
            for row in range(2, ws.max_row + 1):
                cell_value = ws.cell(row=row, column=col_idx).value
                if cell_value is not None and str(cell_value).strip() == sample_mark_value:
                    marked_count += 1
            
            results['details'][f'{sheet_name}_marked_count'] = marked_count
            
            if marked_count != expected_sample_count:
                results['passed'] = False
                results['errors'].append(
                    f"Expected {expected_sample_count} marked rows in '{sheet_name}', found {marked_count}"
                )
    
    # Verify sampling criteria if applicable
    sampling_criteria = specification.get('sampling_criteria', [])
    if sampling_criteria:
        # Check that each criterion is represented in marked rows
        # This requires checking the actual marked row content
        for criterion in sampling_criteria:
            criterion_found = False
            for sheet_name in required_sheets:
                if sheet_name not in actual_sheets:
                    continue
                ws = wb[sheet_name]
                # Simplified check - look for criterion value in marked rows
                # Full implementation would check specific columns
                for row in range(2, ws.max_row + 1):
                    for col in range(1, ws.max_column + 1):
                        cell_value = ws.cell(row=row, column=col).value
                        if cell_value is not None and str(cell_value).strip() == criterion:
                            criterion_found = True
                            break
                    if criterion_found:
                        break
                if criterion_found:
                    break
            
            results['details'][f'criterion_{criterion}'] = 'found' if criterion_found else 'missing'
            if not criterion_found:
                # Check if fallback is permitted
                if not specification.get('criterion_fallback_permitted', False):
                    results['passed'] = False
                    results['errors'].append(
                        f"Sampling criterion '{criterion}' not found in marked rows"
                    )
                else:
                    results['warnings'].append(
                        f"Sampling criterion '{criterion}' not found (fallback permitted)"
                    )
    
    return results


def main():
    if len(sys.argv) < 3:
        print("Usage: verify_workbook.py <workbook_path> <spec_json>")
        sys.exit(1)
    
    workbook_path = sys.argv[1]
    spec_json = sys.argv[2]
    
    import json
    specification = json.loads(spec_json)
    
    results = verify_workbook(workbook_path, specification)
    
    # Print structured results
    print("=" * 60)
    print("WORKBOOK VERIFICATION RESULTS")
    print("=" * 60)
    print(f"Workbook: {workbook_path}")
    print(f"Status: {'PASSED' if results['passed'] else 'FAILED'}")
    print()
    
    if results['errors']:
        print("ERRORS:")
        for err in results['errors']:
            print(f"  ✗ {err}")
        print()
    
    if results['warnings']:
        print("WARNINGS:")
        for warn in results['warnings']:
            print(f"  ⚠ {warn}")
        print()
    
    print("DETAILS:")
    for key, value in results['details'].items():
        print(f"  {key}: {value}")
    
    print("=" * 60)
    
    sys.exit(0 if results['passed'] else 1)


if __name__ == '__main__':
    main()