extract_data.py

"""
VI Portal PDF Data Extractor
Extracts structured data from MarshBerry VI Portal reports.
Each PDF is a ZIP of page images + OCR text files.
"""

import zipfile
import json
import re
import os
import csv
from pathlib import Path
from datetime import datetime


def extract_text_pages(pdf_path):
    """Extract all text pages from a VI Portal PDF (ZIP of images + text)."""
    pages = {}
    with zipfile.ZipFile(pdf_path, 'r') as zf:
        # Read manifest for page count
        manifest = json.loads(zf.read('manifest.json'))
        num_pages = manifest['num_pages']
        
        for i in range(1, num_pages + 1):
            txt_name = f"{i}.txt"
            try:
                text = zf.read(txt_name).decode('utf-8', errors='replace')
                pages[i] = text.replace('\r\n', '\n').replace('\r', '\n')
            except KeyError:
                pages[i] = ""
    return pages


def find_page_by_header(pages, header_text):
    """Find a page whose first line contains a specific header."""
    for page_num, text in pages.items():
        first_line = text.strip().split('\n')[0] if text.strip() else ""
        if header_text.lower() in first_line.lower():
            return page_num, text
    # Fallback: search anywhere in first 3 lines
    for page_num, text in pages.items():
        lines = text.strip().split('\n')[:3]
        combined = ' '.join(lines).lower()
        if header_text.lower() in combined:
            return page_num, text
    return None, None


def extract_effective_date(pages):
    """Extract effective date from page 1."""
    text = pages.get(1, "")
    match = re.search(r'Effective Date:\s*(\w+ \d+,?\s*\d{4})', text)
    if match:
        date_str = match.group(1).replace(',', '')
        try:
            return datetime.strptime(date_str, "%B %d %Y")
        except:
            pass
    # Try alternate format
    match = re.search(r'Effective Date:\s*(\d{1,2}/\d{1,2}/\d{4})', text)
    if match:
        try:
            return datetime.strptime(match.group(1), "%m/%d/%Y")
        except:
            pass
    return None


def parse_perspectives(text):
    """Parse CEO Perspectives / Profit-Growth-Operational-Equity perspectives page."""
    data = {}
    lines = text.strip().split('\n')
    
    current_section = None
    for line in lines:
        line = line.strip()
        
        if 'PROFIT PERSPECTIVES' in line:
            current_section = 'profit'
        elif 'GROWTH PERSPECTIVES' in line:
            current_section = 'growth'
        elif 'OPERATIONAL PERSPECTIVES' in line:
            current_section = 'operational'
        elif 'EQUITY PERSPECTIVES' in line:
            current_section = 'equity'
        
        # Parse metric lines: "Metric Name value1 value2 value3 value4 value5 percentile"
        # e.g. "Reward Ratio 0.51 0.49 0.46 0.55 0.63 71"
        patterns = [
            (r'Reward Ratio\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+(\d+)', 'reward_ratio'),
            (r'Servicing Costs per \$ of Comm\*?\s+\$([\d.]+)\s+\$([\d.]+)\s+\$([\d.]+)\s+\$([\d.]+)\s+\$([\d.]+)\s+(\d+)', 'servicing_cost_per_comm'),
            (r'Employee Marginal Profitability\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+(\d+)', 'employee_marginal_profitability'),
            (r'Contingent & Override Consistency Ratio\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+(\d+)', 'contingent_consistency_ratio'),
            (r'Sales Velocity\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+(\d+)', 'sales_velocity'),
            (r'Total Comm & Fees Growth Rate\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+(\d+)', 'total_cf_growth_rate'),
            (r'New Business Dollars per Production Person\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+(\d+)', 'new_biz_per_prod_person'),
            (r'Net Unvalidated Producer Payroll \(NUPP\)\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+(\d+)', 'nupp'),
            (r'Revenue Per Employee\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+(\d+)', 'revenue_per_employee'),
            (r'Total Comm & Fees Per Production Person\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+(\d+)', 'cf_per_prod_person'),
            (r'Total Comm & Fees Per Service Person\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+\$([\d,]+)\s+(\d+)', 'cf_per_service_person'),
            (r'Support Staff as % of Total Employees\*?\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+(\d+)', 'support_staff_pct'),
            (r'Debt Service Coverage Ratio\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+(\d+)', 'debt_service_coverage'),
            (r'Trust Ratio\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+(\d+)', 'trust_ratio'),
            (r'Defensive Interval.*?\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)', 'defensive_interval'),
            (r'Weighted Average Shareholder Age\*?\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+(\d+)', 'weighted_avg_shareholder_age'),
        ]
        
        for pattern, key in patterns:
            m = re.search(pattern, line)
            if m:
                groups = m.groups()
                data[key] = {
                    'last_year': clean_number(groups[0]),
                    'this_year': clean_number(groups[1]),
                    'average': clean_number(groups[2]),
                    'best_25': clean_number(groups[3]),
                    'peak': clean_number(groups[4]),
                    'percentile': int(groups[5]),
                }
    return data


def parse_key_return_metrics(text):
    """Parse Key Return Metrics page."""
    data = {}
    lines = text.strip().split('\n')
    
    patterns = [
        (r'Reward Ratio\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+(\d+)', 'reward_ratio'),
        (r'Rule of (?:40|20)\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+(\d+)', 'rule_of_40'),
        (r'Owner Return Rate\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+(\d+)', 'owner_return_rate'),
        (r'Net Revenue Growth Rate\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+([\d.]+)%\s+(\d+)', 'net_revenue_growth_rate'),
    ]
    
    for line in lines:
        line = line.strip()
        for pattern, key in patterns:
            m = re.search(pattern, line)
            if m:
                groups = m.groups()
                data[key] = {
                    'last_year': clean_number(groups[0]),
                    'this_year': clean_number(groups[1]),
                    'average': clean_number(groups[2]),
                    'best_25': clean_number(groups[3]),
                    'peak': clean_number(groups[4]),
                    'percentile': int(groups[5]),
                }
    
    # Also extract EBITDA and compensation ratios from the chart text
    ebitda_pattern = r'(\d+\.?\d*)%\s+(\d+\.?\d*)%\s+(\d+\.?\d*)%\s+(\d+\.?\d*)%\s*\nOperational EBITDA'
    m = re.search(ebitda_pattern, text)
    if m:
        data['operational_ebitda_margin'] = {
            'last_year': float(m.group(1)),
            'this_year': float(m.group(2)),
            'average': float(m.group(3)),
            'best_25': float(m.group(4)),
        }
    
    comp_pattern = r'(\d+\.?\d*)%\s+(\d+\.?\d*)%\s+(\d+\.?\d*)%\s+(\d+\.?\d*)%\s*\nCompensation as % of Net Revenue'
    m = re.search(comp_pattern, text)
    if m:
        data['compensation_pct_net_rev'] = {
            'last_year': float(m.group(1)),
            'this_year': float(m.group(2)),
            'average': float(m.group(3)),
            'best_25': float(m.group(4)),
        }
    
    return data


def parse_growth_metrics(text):
    """Parse Growth of Insurance Income page."""
    data = {}
    lines = text.strip().split('\n')
    
    current_section = None
    for line in lines:
        line = line.strip()
        
        if 'Total Growth Rate' in line:
            current_section = 'total_growth'
        elif 'Sales Velocity' in line and 'Total' not in line:
            current_section = 'sales_velocity'
        elif 'Leakage/Lift' in line:
            current_section = 'leakage_lift'
        elif 'Organic Growth' in line:
            current_section = 'organic_growth'
        
        if current_section:
            # Match lines like: "Total Commissions and Fees (TCF) 12.4% 12.1% 11.4% 21.1% 30.2% 60"
            patterns = [
                (r'Total Commissions and Fees \(TCF\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(\d+)', 'tcf'),
                (r'Property & Casualty.*\(P&C\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(\d+)', 'pc'),
                (r'Commercial Lines.*\(CL\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(\d+)', 'cl'),
                (r'Personal Lines.*\(PL\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(\d+)', 'pl'),
                (r'Life & Health.*\(L&H\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(\d+)', 'lh'),
                (r'Group Commission.*\(GRP\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(\d+)', 'grp'),
                # For Total Commissions under Sales Velocity
                (r'Total Commissions\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(\d+)', 'total'),
            ]
            
            # For leakage/lift lines with NRPP 
            leakage_patterns = [
                (r'Total Commissions\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%', 'total'),
                (r'Property & Casualty.*\(P&C\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%', 'pc'),
                (r'Commercial Lines.*\(CL\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%', 'cl'),
                (r'Personal Lines.*\(PL\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%', 'pl'),
                (r'Life & Health.*\(L&H\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%', 'lh'),
                (r'Group Commission.*\(GRP\)\s+(-?[\d.]+)%\s+(-?[\d.]+)%\s+(-?[\d.]+)%', 'grp'),
            ]
            
            if current_section == 'leakage_lift':
                for pattern, key in leakage_patterns:
                    m = re.search(pattern, line)
                    if m:
                        metric_key = f"{current_section}_{key}"
                        data[metric_key] = {
                            'last_year': float(m.group(1)),
                            'this_year': float(m.group(2)),
                            'average': float(m.group(3)),
                        }
            else:
                for pattern, key in patterns:
                    m = re.search(pattern, line)
                    if m:
                        groups = m.groups()
                        metric_key = f"{current_section}_{key}"
                        data[metric_key] = {
                            'last_year': float(groups[0]),
                            'this_year': float(groups[1]),
                            'average': float(groups[2]),
                            'best_25': float(groups[3]),
                            'peak': float(groups[4]),
                            'percentile': int(groups[5]),
                        }
    return data


def parse_income_statement(text):
    """Parse the Income Statement / Revenues page."""
    data = {}
    
    # Revenue line items
    revenue_patterns = [
        (r'P&C Commissions\s+\$?\s*([\d,]+)', 'pc_commissions'),
        (r'Life & Health Commissions\s+(\d[\d,]*)', 'lh_commissions'),
        (r'Fee Income\s+(\d[\d,]*)', 'fee_income'),
        (r'Total Commissions And Fees\s+(\d[\d,]*)', 'total_commissions_fees'),
        (r'Contingents\s+(\d[\d,]*)', 'contingents'),
        (r'Net Revenues\s+\$?\s*([\d,]+)', 'net_revenues'),
        (r'Total Compensation\s+\$?\s*([\d,]+)', 'total_compensation'),
        (r'Pre-Tax Profit\s+\$?\s*([\d,]+)', 'pretax_profit'),
        (r'EBITDA\s+\$?\s*([\d,]+)', 'ebitda'),
        (r'Operating EBITDA\s+\$?\s*([\d,]+)', 'operating_ebitda'),
        (r'Total Expenses\s+\$?\s*([\d,]+)', 'total_expenses'),
        (r'Gross Revenues\s+(\d[\d,]*)', 'gross_revenues'),
    ]
    
    lines = text.strip().split('\n')
    for line in lines:
        line = line.strip()
        for pattern, key in revenue_patterns:
            m = re.search(pattern, line)
            if m:
                # The line has multiple values: last_year pct this_year pct ...
                # Extract all dollar amounts from the line
                amounts = re.findall(r'\$?\s*([\d,]+(?:\.\d+)?)\s+[\d.]+\s*%', line)
                if len(amounts) >= 2:
                    data[key] = {
                        'last_year': clean_number(amounts[0]),
                        'this_year': clean_number(amounts[1]),
                    }
                elif amounts:
                    data[key] = {'value': clean_number(amounts[0])}
    
    # More robust parsing: grab all dollar values on lines with known labels
    for line in lines:
        line = line.strip()
        # Match: Label $amount pct% $amount pct%
        m = re.match(r'([\w&\s\(\)]+?)\s+\$?\s*([\d,]+)\s+([\d.]+)\s*%?\s+\$?\s*([\d,]+)\s+([\d.]+)\s*%', line)
        if m:
            label = m.group(1).strip().lower().replace(' ', '_').replace('&', 'and')
            label = re.sub(r'[^a-z0-9_]', '', label)
            if label and label not in data:
                data[label] = {
                    'last_year': clean_number(m.group(2)),
                    'last_year_pct': float(m.group(3)),
                    'this_year': clean_number(m.group(4)),
                    'this_year_pct': float(m.group(5)),
                }
    
    return data


def clean_number(s):
    """Clean a number string, removing commas and dollar signs."""
    if isinstance(s, (int, float)):
        return float(s)
    s = str(s).replace(',', '').replace('$', '').strip()
    try:
        return float(s)
    except:
        return 0.0


def extract_all_data(pdf_dir):
    """Extract data from all VI Portal PDFs in a directory."""
    all_data = []
    
    pdf_files = sorted(Path(pdf_dir).glob("VI_Portal*.pdf"))
    print(f"Found {len(pdf_files)} PDF files")
    
    for pdf_path in pdf_files:
        print(f"\nProcessing: {pdf_path.name}")
        
        try:
            pages = extract_text_pages(str(pdf_path))
        except Exception as e:
            print(f"  ERROR reading {pdf_path.name}: {e}")
            continue
        
        effective_date = extract_effective_date(pages)
        if effective_date:
            date_str = effective_date.strftime("%m/%d/%Y")
            quarter_label = effective_date.strftime("%b %Y")
            print(f"  Effective Date: {date_str}")
        else:
            print(f"  WARNING: Could not extract effective date")
            date_str = pdf_path.stem
            quarter_label = pdf_path.stem
        
        report = {
            'file': pdf_path.name,
            'effective_date': date_str,
            'quarter_label': quarter_label,
            'perspectives': {},
            'key_return_metrics': {},
            'growth_metrics': {},
            'income_statement': {},
        }
        
        # Parse Perspectives (page 3 in all PDFs)
        _, persp_text = find_page_by_header(pages, "PROFIT PERSPECTIVES")
        if persp_text:
            report['perspectives'] = parse_perspectives(persp_text)
            print(f"  Perspectives: {len(report['perspectives'])} metrics")
        
        # Parse Key Return Metrics
        _, krm_text = find_page_by_header(pages, "KEY RETURN METRICS")
        if krm_text:
            report['key_return_metrics'] = parse_key_return_metrics(krm_text)
            print(f"  Key Return Metrics: {len(report['key_return_metrics'])} metrics")
        
        # Parse Growth of Insurance Income
        _, growth_text = find_page_by_header(pages, "GROWTH OF INSURANCE INCOME")
        if growth_text:
            report['growth_metrics'] = parse_growth_metrics(growth_text)
            print(f"  Growth Metrics: {len(report['growth_metrics'])} metrics")
        
        # Parse Income Statement
        for header in ["Revenues", "Revenue"]:
            _, inc_text = find_page_by_header(pages, header)
            if inc_text and len(inc_text) > 500:
                report['income_statement'] = parse_income_statement(inc_text)
                print(f"  Income Statement: {len(report['income_statement'])} items")
                break
        
        all_data.append(report)
    
    return all_data


def build_time_series(all_data):
    """Build flat time-series data for dashboard consumption."""
    
    # KPI Time Series
    kpi_rows = []
    for report in all_data:
        date = report['effective_date']
        label = report['quarter_label']
        
        # Key Return Metrics
        krm = report.get('key_return_metrics', {})
        perspectives = report.get('perspectives', {})
        growth = report.get('growth_metrics', {})
        income = report.get('income_statement', {})
        
        row = {'date': date, 'quarter': label}
        
        # Key Return Metrics
        for metric in ['reward_ratio', 'rule_of_40', 'owner_return_rate', 'net_revenue_growth_rate']:
            d = krm.get(metric, perspectives.get(metric, {}))
            if d:
                row[f'{metric}_agency'] = d.get('this_year', d.get('value', ''))
                row[f'{metric}_last_year'] = d.get('last_year', '')
                row[f'{metric}_average'] = d.get('average', '')
                row[f'{metric}_best25'] = d.get('best_25', '')
                row[f'{metric}_percentile'] = d.get('percentile', '')
        
        # Additional perspectives metrics
        for metric in ['servicing_cost_per_comm', 'employee_marginal_profitability',
                       'sales_velocity', 'total_cf_growth_rate', 'revenue_per_employee',
                       'cf_per_prod_person', 'cf_per_service_person', 'support_staff_pct',
                       'new_biz_per_prod_person', 'nupp', 'debt_service_coverage',
                       'trust_ratio', 'defensive_interval', 'weighted_avg_shareholder_age',
                       'contingent_consistency_ratio']:
            d = perspectives.get(metric, {})
            if d:
                row[f'{metric}_agency'] = d.get('this_year', '')
                row[f'{metric}_last_year'] = d.get('last_year', '')
                row[f'{metric}_average'] = d.get('average', '')
                row[f'{metric}_best25'] = d.get('best_25', '')
                row[f'{metric}_percentile'] = d.get('percentile', '')
        
        # EBITDA / Compensation from Key Return Metrics charts
        for metric in ['operational_ebitda_margin', 'compensation_pct_net_rev']:
            d = krm.get(metric, {})
            if d:
                row[f'{metric}_agency'] = d.get('this_year', '')
                row[f'{metric}_last_year'] = d.get('last_year', '')
                row[f'{metric}_average'] = d.get('average', '')
                row[f'{metric}_best25'] = d.get('best_25', '')
        
        # Growth metrics
        for section in ['total_growth', 'organic_growth', 'sales_velocity']:
            for lob in ['tcf', 'total', 'pc', 'cl', 'pl', 'lh', 'grp']:
                key = f"{section}_{lob}"
                d = growth.get(key, {})
                if d:
                    row[f'{key}_agency'] = d.get('this_year', '')
                    row[f'{key}_last_year'] = d.get('last_year', '')
                    row[f'{key}_average'] = d.get('average', '')
                    row[f'{key}_best25'] = d.get('best_25', '')
                    if 'percentile' in d:
                        row[f'{key}_percentile'] = d['percentile']
        
        # Leakage/Lift
        for lob in ['total', 'pc', 'cl', 'pl', 'lh', 'grp']:
            key = f"leakage_lift_{lob}"
            d = growth.get(key, {})
            if d:
                row[f'{key}_agency'] = d.get('this_year', '')
                row[f'{key}_last_year'] = d.get('last_year', '')
                row[f'{key}_average'] = d.get('average', '')
        
        # Income statement highlights
        for key in ['net_revenues', 'total_commissions_fees', 'pc_commissions',
                     'lh_commissions', 'contingents', 'total_compensation',
                     'pretax_profit', 'ebitda', 'operating_ebitda', 'total_expenses',
                     'gross_revenues', 'fee_income']:
            d = income.get(key, {})
            if d:
                row[f'income_{key}_this_year'] = d.get('this_year', d.get('value', ''))
                row[f'income_{key}_last_year'] = d.get('last_year', '')
        
        kpi_rows.append(row)
    
    return kpi_rows


def save_data(kpi_rows, output_dir):
    """Save extracted data to JSON and CSV files."""
    os.makedirs(output_dir, exist_ok=True)
    
    # Save JSON
    json_path = os.path.join(output_dir, 'vi_portal_data.json')
    with open(json_path, 'w') as f:
        json.dump(kpi_rows, f, indent=2, default=str)
    print(f"\nSaved JSON: {json_path}")
    
    # Save CSV
    if kpi_rows:
        csv_path = os.path.join(output_dir, 'vi_portal_kpis.csv')
        all_keys = set()
        for row in kpi_rows:
            all_keys.update(row.keys())
        all_keys = sorted(all_keys)
        
        with open(csv_path, 'w', newline='') as f:
            writer = csv.DictWriter(f, fieldnames=all_keys)
            writer.writeheader()
            writer.writerows(kpi_rows)
        print(f"Saved CSV: {csv_path}")
    
    return json_path


if __name__ == '__main__':
    import sys
    
    pdf_dir = sys.argv[1] if len(sys.argv) > 1 else '/home/claude'
    output_dir = sys.argv[2] if len(sys.argv) > 2 else '/home/claude/vi_dashboard/data'
    
    print("=" * 60)
    print("VI Portal Data Extraction")
    print("=" * 60)
    
    all_data = extract_all_data(pdf_dir)
    
    # Save raw extracted data
    raw_path = os.path.join(output_dir, 'raw_extracted.json')
    os.makedirs(output_dir, exist_ok=True)
    with open(raw_path, 'w') as f:
        json.dump(all_data, f, indent=2, default=str)
    
    # Build time series
    kpi_rows = build_time_series(all_data)
    save_data(kpi_rows, output_dir)
    
    print(f"\n{'=' * 60}")
    print(f"Extracted {len(all_data)} reports, {len(kpi_rows)} quarterly data points")
    print(f"{'=' * 60}")