Implementation Guide: Analyze project cost-to-complete vs. budget and flag margin erosion early

az login
az group create --name rg-construction-analytics --location eastus2
az sql server create --name sql-construction-analytics --resource-group rg-construction-analytics --location eastus2 --admin-user sqladmin --admin-password '<STRONG_PASSWORD_HERE>'
az sql db create --resource-group rg-construction-analytics --server sql-construction-analytics --name ConstructionCostDB --service-objective S2 --backup-storage-redundancy Local
az storage account create --name stconstructiondata --resource-group rg-construction-analytics --location eastus2 --sku Standard_LRS --kind StorageV2
az storage container create --name raw-imports --account-name stconstructiondata
az storage container create --name processed-data --account-name stconstructiondata
az functionapp create --resource-group rg-construction-analytics --consumption-plan-location eastus2 --runtime python --runtime-version 3.11 --functions-version 4 --name func-construction-etl --storage-account stconstructiondata
az cognitiveservices account create --name openai-construction --resource-group rg-construction-analytics --kind OpenAI --sku S0 --location eastus2
az cognitiveservices account deployment create --name openai-construction --resource-group rg-construction-analytics --deployment-name gpt4o-analysis --model-name gpt-5.4 --model-version 2024-08-06 --model-format OpenAI --sku-capacity 10 --sku-name Standard
az monitor app-insights component create --app insights-construction --location eastus2 --resource-group rg-construction-analytics

sqlcmd -S sql-construction-analytics.database.windows.net -d ConstructionCostDB -U sqladmin -P '<PASSWORD>'

CREATE SCHEMA construction;
GO

CREATE TABLE construction.dim_projects (
    project_id INT IDENTITY(1,1) PRIMARY KEY,
    source_system_id NVARCHAR(50) NOT NULL,
    source_system NVARCHAR(50) NOT NULL, -- 'procore', 'sage', 'quickbooks', etc.
    project_name NVARCHAR(255) NOT NULL,
    project_number NVARCHAR(50),
    client_name NVARCHAR(255),
    project_manager NVARCHAR(255),
    contract_amount DECIMAL(18,2),
    original_budget DECIMAL(18,2),
    revised_budget DECIMAL(18,2),
    start_date DATE,
    estimated_completion_date DATE,
    actual_completion_date DATE,
    project_status NVARCHAR(50) DEFAULT 'Active', -- Active, Complete, On Hold, Cancelled
    project_type NVARCHAR(100), -- Residential, Commercial, Industrial, Infrastructure
    bonded BIT DEFAULT 0,
    prevailing_wage BIT DEFAULT 0,
    created_at DATETIME2 DEFAULT GETUTCDATE(),
    updated_at DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE construction.dim_cost_codes (
    cost_code_id INT IDENTITY(1,1) PRIMARY KEY,
    cost_code NVARCHAR(50) NOT NULL,
    cost_code_description NVARCHAR(255),
    cost_type NVARCHAR(50), -- Labor, Material, Subcontractor, Equipment, Other
    csi_division NVARCHAR(10), -- CSI MasterFormat division if applicable
    parent_cost_code NVARCHAR(50),
    is_active BIT DEFAULT 1
);

CREATE TABLE construction.dim_vendors (
    vendor_id INT IDENTITY(1,1) PRIMARY KEY,
    source_system_id NVARCHAR(50),
    vendor_name NVARCHAR(255) NOT NULL,
    vendor_type NVARCHAR(50), -- Subcontractor, Supplier, Equipment Rental
    is_active BIT DEFAULT 1
);

CREATE TABLE construction.fact_budget (
    budget_id INT IDENTITY(1,1) PRIMARY KEY,
    project_id INT FOREIGN KEY REFERENCES construction.dim_projects(project_id),
    cost_code_id INT FOREIGN KEY REFERENCES construction.dim_cost_codes(cost_code_id),
    original_budget_amount DECIMAL(18,2) NOT NULL,
    approved_changes DECIMAL(18,2) DEFAULT 0,
    revised_budget_amount AS (original_budget_amount + approved_changes),
    budget_date DATE NOT NULL,
    snapshot_date DATE NOT NULL DEFAULT CAST(GETUTCDATE() AS DATE)
);

CREATE TABLE construction.fact_actual_costs (
    actual_cost_id INT IDENTITY(1,1) PRIMARY KEY,
    project_id INT FOREIGN KEY REFERENCES construction.dim_projects(project_id),
    cost_code_id INT FOREIGN KEY REFERENCES construction.dim_cost_codes(cost_code_id),
    vendor_id INT FOREIGN KEY REFERENCES construction.dim_vendors(vendor_id),
    transaction_date DATE NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    description NVARCHAR(500),
    source_document NVARCHAR(100), -- PO number, invoice number, timecard ID
    cost_type NVARCHAR(50), -- Labor, Material, Sub, Equipment, Other
    imported_at DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE construction.fact_commitments (
    commitment_id INT IDENTITY(1,1) PRIMARY KEY,
    project_id INT FOREIGN KEY REFERENCES construction.dim_projects(project_id),
    cost_code_id INT FOREIGN KEY REFERENCES construction.dim_cost_codes(cost_code_id),
    vendor_id INT FOREIGN KEY REFERENCES construction.dim_vendors(vendor_id),
    commitment_type NVARCHAR(50), -- Purchase Order, Subcontract, Rental Agreement
    original_amount DECIMAL(18,2) NOT NULL,
    approved_changes DECIMAL(18,2) DEFAULT 0,
    revised_amount AS (original_amount + approved_changes),
    invoiced_to_date DECIMAL(18,2) DEFAULT 0,
    remaining_to_invoice AS (original_amount + approved_changes - invoiced_to_date),
    commitment_date DATE,
    status NVARCHAR(50) DEFAULT 'Open'
);

CREATE TABLE construction.fact_change_orders (
    change_order_id INT IDENTITY(1,1) PRIMARY KEY,
    project_id INT FOREIGN KEY REFERENCES construction.dim_projects(project_id),
    change_order_number NVARCHAR(50),
    description NVARCHAR(500),
    revenue_amount DECIMAL(18,2) DEFAULT 0,
    cost_amount DECIMAL(18,2) DEFAULT 0,
    margin_impact AS (revenue_amount - cost_amount),
    status NVARCHAR(50), -- Pending, Approved, Rejected
    submitted_date DATE,
    approved_date DATE
);

CREATE TABLE construction.fact_cost_to_complete (
    ctc_id INT IDENTITY(1,1) PRIMARY KEY,
    project_id INT FOREIGN KEY REFERENCES construction.dim_projects(project_id),
    cost_code_id INT FOREIGN KEY REFERENCES construction.dim_cost_codes(cost_code_id),
    estimate_date DATE NOT NULL,
    cost_to_date DECIMAL(18,2) NOT NULL,
    committed_not_invoiced DECIMAL(18,2) DEFAULT 0,
    estimated_cost_to_complete DECIMAL(18,2) NOT NULL,
    estimate_at_completion AS (cost_to_date + committed_not_invoiced + estimated_cost_to_complete),
    percent_complete DECIMAL(5,2),
    entered_by NVARCHAR(255),
    notes NVARCHAR(1000),
    snapshot_date DATE NOT NULL DEFAULT CAST(GETUTCDATE() AS DATE)
);

CREATE TABLE construction.fact_margin_snapshots (
    snapshot_id INT IDENTITY(1,1) PRIMARY KEY,
    project_id INT FOREIGN KEY REFERENCES construction.dim_projects(project_id),
    snapshot_date DATE NOT NULL,
    contract_amount DECIMAL(18,2),
    revised_budget DECIMAL(18,2),
    cost_to_date DECIMAL(18,2),
    committed_costs DECIMAL(18,2),
    estimated_cost_to_complete DECIMAL(18,2),
    estimate_at_completion DECIMAL(18,2),
    projected_margin_amount AS (contract_amount - estimate_at_completion),
    projected_margin_pct AS (CASE WHEN contract_amount > 0 THEN (contract_amount - estimate_at_completion) / contract_amount * 100 ELSE 0 END),
    original_margin_pct DECIMAL(5,2),
    margin_erosion_pct AS (CASE WHEN contract_amount > 0 THEN ((contract_amount - estimate_at_completion) / contract_amount * 100) ELSE 0 END - ISNULL(original_margin_pct, 0)),
    earned_value DECIMAL(18,2),
    schedule_variance DECIMAL(18,2),
    cost_variance DECIMAL(18,2),
    ai_risk_score DECIMAL(5,2), -- 0-100 risk score from AI model
    ai_narrative NVARCHAR(MAX), -- AI-generated plain-English summary
    alert_triggered BIT DEFAULT 0
);

-- Create indexes for common query patterns
CREATE INDEX IX_fact_actual_costs_project ON construction.fact_actual_costs(project_id, transaction_date);
CREATE INDEX IX_fact_commitments_project ON construction.fact_commitments(project_id);
CREATE INDEX IX_fact_ctc_project_date ON construction.fact_cost_to_complete(project_id, estimate_date);
CREATE INDEX IX_fact_margin_snapshot ON construction.fact_margin_snapshots(project_id, snapshot_date);

az keyvault create --name kv-construction --resource-group rg-construction-analytics --location eastus2
az keyvault secret set --vault-name kv-construction --name AgaveApiKey --value '<AGAVE_API_KEY>'
az keyvault secret set --vault-name kv-construction --name AgaveClientSecret --value '<AGAVE_CLIENT_SECRET>'

pip install requests
python3 -c "
import requests
headers = {'API-Key': '<AGAVE_API_KEY>', 'Client-Secret': '<AGAVE_CLIENT_SECRET>'}
# List linked accounts
resp = requests.get('https://api.agaveapi.com/linked-accounts', headers=headers)
print(resp.json())
"

func init construction-etl --python
cd construction-etl

func new --name sync_projects --template 'Timer trigger'
func new --name sync_costs --template 'Timer trigger'
func new --name sync_commitments --template 'Timer trigger'
func new --name calculate_margins --template 'Timer trigger'

pip install pyodbc requests openai azure-identity azure-keyvault-secrets
pip freeze > requirements.txt

func azure functionapp publish func-construction-etl

# Verify OpenAI deployment is ready
az cognitiveservices account deployment list --name openai-construction --resource-group rg-construction-analytics -o table

# Store the OpenAI endpoint and key in Key Vault
az keyvault secret set --vault-name kv-construction --name OpenAIEndpoint --value 'https://openai-construction.openai.azure.com/'
az keyvault secret set --vault-name kv-construction --name OpenAIKey --value '<OPENAI_KEY>'

# Test the deployment with a sample construction cost analysis prompt
python3 -c "
from openai import AzureOpenAI
client = AzureOpenAI(
    azure_endpoint='https://openai-construction.openai.azure.com/',
    api_key='<OPENAI_KEY>',
    api_version='2024-06-01'
)
response = client.chat.completions.create(
    model='gpt4o-analysis',
    messages=[
        {'role': 'system', 'content': 'You are a construction cost analyst assistant.'},
        {'role': 'user', 'content': 'Project ABC has original budget 500000, cost to date 350000 at 60% complete, committed costs 100000, estimated cost to complete 120000. Analyze margin risk.'}
    ],
    temperature=0.3
)
print(response.choices[0].message.content)
"

# Power BI connection string for Azure SQL
# Server: sql-construction-analytics.database.windows.net
# Database: ConstructionCostDB
# Authentication: Azure Active Directory or SQL Authentication

az storage blob upload-batch --account-name stconstructiondata --destination raw-imports --source ./historical-exports/

python3 historical_import.py --source-dir ./historical-exports/ --target-db ConstructionCostDB

python3 backfill_margins.py --start-date 2023-01-01 --end-date 2024-12-31

python3 calibrate_thresholds.py --output threshold_recommendations.json

az sql server audit-policy update --resource-group rg-construction-analytics --name sql-construction-analytics --state Enabled --storage-account stconstructiondata

az sql db threat-policy update --resource-group rg-construction-analytics --server sql-construction-analytics --name ConstructionCostDB --state Enabled --email-addresses 'msp-security@yourmsp.com'

# allow only Azure services and MSP management IPs

az sql server firewall-rule create --resource-group rg-construction-analytics --server sql-construction-analytics --name AllowAzureServices --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0
az sql server firewall-rule create --resource-group rg-construction-analytics --server sql-construction-analytics --name MSP-Management --start-ip-address <MSP_STATIC_IP> --end-ip-address <MSP_STATIC_IP>

az keyvault update --name kv-construction --enable-soft-delete true --enable-purge-protection true

az ad group create --display-name 'Construction-Analytics-Admins' --mail-nickname analytics-admins
az ad group create --display-name 'Construction-Analytics-PMs' --mail-nickname analytics-pms
az ad group create --display-name 'Construction-Analytics-Executives' --mail-nickname analytics-execs

az functionapp config appsettings set --name func-construction-etl --resource-group rg-construction-analytics --settings AzureWebJobs.sync_projects.Disabled=false
az functionapp config appsettings set --name func-construction-etl --resource-group rg-construction-analytics --settings AzureWebJobs.sync_costs.Disabled=false
az functionapp config appsettings set --name func-construction-etl --resource-group rg-construction-analytics --settings AzureWebJobs.sync_commitments.Disabled=false
az functionapp config appsettings set --name func-construction-etl --resource-group rg-construction-analytics --settings AzureWebJobs.calculate_margins.Disabled=false

az functionapp function list --name func-construction-etl --resource-group rg-construction-analytics -o table

func azure functionapp logstream func-construction-etl

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "name": "timer",
      "type": "timerTrigger",
      "direction": "in",
      "schedule": "0 3 * * *"
    }
  ]
}

import azure.functions as func
import pyodbc
import json
import logging
from datetime import datetime, timedelta
from openai import AzureOpenAI
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

def main(timer: func.TimerRequest) -> None:
    logging.info('Margin calculation function triggered')
    
    # Load secrets from Key Vault
    credential = DefaultAzureCredential()
    kv_client = SecretClient(vault_url='https://kv-construction.vault.azure.net/', credential=credential)
    
    sql_conn_str = kv_client.get_secret('SqlConnectionString').value
    openai_endpoint = kv_client.get_secret('OpenAIEndpoint').value
    openai_key = kv_client.get_secret('OpenAIKey').value
    
    # Initialize OpenAI client
    ai_client = AzureOpenAI(
        azure_endpoint=openai_endpoint,
        api_key=openai_key,
        api_version='2024-06-01'
    )
    
    conn = pyodbc.connect(sql_conn_str)
    cursor = conn.cursor()
    
    # Get all active projects
    cursor.execute("""
        SELECT project_id, project_name, project_number, project_manager,
               contract_amount, original_budget, revised_budget, project_type,
               bonded, prevailing_wage, start_date, estimated_completion_date
        FROM construction.dim_projects
        WHERE project_status = 'Active'
    """)
    projects = cursor.fetchall()
    
    today = datetime.utcnow().date()
    
    for project in projects:
        project_id = project.project_id
        contract_amount = float(project.contract_amount or 0)
        original_budget = float(project.original_budget or 0)
        
        # Calculate cost to date
        cursor.execute("""
            SELECT ISNULL(SUM(amount), 0) as cost_to_date
            FROM construction.fact_actual_costs
            WHERE project_id = ?
        """, project_id)
        cost_to_date = float(cursor.fetchone().cost_to_date)
        
        # Calculate committed but not yet invoiced
        cursor.execute("""
            SELECT ISNULL(SUM(CASE WHEN remaining_to_invoice > 0 THEN remaining_to_invoice ELSE 0 END), 0) as committed_remaining
            FROM construction.fact_commitments
            WHERE project_id = ? AND status = 'Open'
        """, project_id)
        committed_remaining = float(cursor.fetchone().committed_remaining)
        
        # Get latest cost-to-complete estimate from PMs
        cursor.execute("""
            SELECT TOP 1 estimated_cost_to_complete, percent_complete, entered_by, notes
            FROM construction.fact_cost_to_complete
            WHERE project_id = ?
            ORDER BY estimate_date DESC
        """, project_id)
        ctc_row = cursor.fetchone()
        
        if ctc_row:
            est_cost_to_complete = float(ctc_row.estimated_cost_to_complete)
            percent_complete = float(ctc_row.percent_complete or 0)
        else:
            # Fallback: estimate CTC based on committed + budget remaining
            est_cost_to_complete = max(0, float(project.revised_budget or original_budget) - cost_to_date - committed_remaining)
            percent_complete = (cost_to_date / original_budget * 100) if original_budget > 0 else 0
        
        # Calculate Estimate at Completion (EAC)
        eac = cost_to_date + committed_remaining + est_cost_to_complete
        
        # Calculate margins
        projected_margin_amount = contract_amount - eac
        projected_margin_pct = (projected_margin_amount / contract_amount * 100) if contract_amount > 0 else 0
        original_margin_pct = ((contract_amount - original_budget) / contract_amount * 100) if contract_amount > 0 else 0
        margin_erosion_pct = projected_margin_pct - original_margin_pct
        
        # Earned Value calculations
        earned_value = contract_amount * (percent_complete / 100)
        cost_variance = earned_value - cost_to_date  # Positive = under budget
        
        # Calculate schedule variance (simplified)
        if project.start_date and project.estimated_completion_date:
            total_duration = (project.estimated_completion_date - project.start_date).days
            elapsed = (today - project.start_date).days
            planned_pct = min(100, (elapsed / total_duration * 100)) if total_duration > 0 else 0
            schedule_variance = percent_complete - planned_pct  # Positive = ahead of schedule
        else:
            schedule_variance = 0
        
        # Get previous margin snapshots for trend analysis
        cursor.execute("""
            SELECT TOP 4 snapshot_date, projected_margin_pct, estimate_at_completion
            FROM construction.fact_margin_snapshots
            WHERE project_id = ?
            ORDER BY snapshot_date DESC
        """, project_id)
        history = cursor.fetchall()
        
        # Calculate margin velocity (rate of erosion per week)
        margin_velocity = 0
        if len(history) >= 2:
            prev = history[0]
            days_diff = (today - prev.snapshot_date).days
            if days_diff > 0:
                margin_velocity = (projected_margin_pct - float(prev.projected_margin_pct)) / (days_diff / 7)
        
        # Determine alert level
        alert_triggered = False
        alert_level = 'NONE'
        
        # Load thresholds from config table
        cursor.execute("SELECT setting_value FROM construction.system_config WHERE setting_key = 'threshold_watch'")
        row = cursor.fetchone()
        threshold_watch = float(row.setting_value) if row else -2.0
        
        cursor.execute("SELECT setting_value FROM construction.system_config WHERE setting_key = 'threshold_warning'")
        row = cursor.fetchone()
        threshold_warning = float(row.setting_value) if row else -5.0
        
        cursor.execute("SELECT setting_value FROM construction.system_config WHERE setting_key = 'threshold_critical'")
        row = cursor.fetchone()
        threshold_critical = float(row.setting_value) if row else -10.0
        
        if margin_erosion_pct <= threshold_critical or projected_margin_amount < 0:
            alert_level = 'CRITICAL'
            alert_triggered = True
        elif margin_erosion_pct <= threshold_warning:
            alert_level = 'WARNING'
            alert_triggered = True
        elif margin_erosion_pct <= threshold_watch:
            alert_level = 'WATCH'
            alert_triggered = True
        
        # Generate AI narrative for flagged projects
        ai_narrative = ''
        ai_risk_score = abs(margin_erosion_pct) * 5  # Simple heuristic base score
        
        if alert_triggered or abs(margin_erosion_pct) > 1.0:
            # Get cost breakdown by type
            cursor.execute("""
                SELECT cost_type, SUM(amount) as total
                FROM construction.fact_actual_costs
                WHERE project_id = ?
                GROUP BY cost_type
                ORDER BY total DESC
            """, project_id)
            cost_breakdown = {row.cost_type: float(row.total) for row in cursor.fetchall()}
            
            # Get change order summary
            cursor.execute("""
                SELECT COUNT(*) as co_count,
                       SUM(CASE WHEN status='Approved' THEN revenue_amount ELSE 0 END) as approved_revenue,
                       SUM(CASE WHEN status='Approved' THEN cost_amount ELSE 0 END) as approved_cost,
                       SUM(CASE WHEN status='Pending' THEN revenue_amount ELSE 0 END) as pending_revenue
                FROM construction.fact_change_orders
                WHERE project_id = ?
            """, project_id)
            co = cursor.fetchone()
            
            history_text = ''
            for h in history:
                history_text += f"  {h.snapshot_date}: margin={h.projected_margin_pct:.1f}%, EAC=${h.estimate_at_completion:,.0f}\n"
            
            prompt = f"""Analyze this construction project's financial health and margin erosion risk:

PROJECT: {project.project_name} ({project.project_number})
Type: {project.project_type}
Project Manager: {project.project_manager}
Bonded: {'Yes' if project.bonded else 'No'}
Prevailing Wage: {'Yes' if project.prevailing_wage else 'No'}

FINANCIAL SUMMARY:
- Contract Amount: ${contract_amount:,.2f}
- Original Budget: ${original_budget:,.2f}
- Original Margin: {original_margin_pct:.1f}%
- Cost to Date: ${cost_to_date:,.2f}
- Committed (uninvoiced): ${committed_remaining:,.2f}
- Estimated Cost to Complete: ${est_cost_to_complete:,.2f}
- Estimate at Completion (EAC): ${eac:,.2f}
- Projected Margin: ${projected_margin_amount:,.2f} ({projected_margin_pct:.1f}%)
- Margin Erosion: {margin_erosion_pct:.1f} percentage points
- Margin Velocity: {margin_velocity:.2f} pct points/week
- Percent Complete: {percent_complete:.0f}%
- Cost Variance: ${cost_variance:,.2f}
- Schedule Variance: {schedule_variance:.1f} percentage points

COST BREAKDOWN:
{json.dumps(cost_breakdown, indent=2)}

CHANGE ORDERS:
- Total COs: {co.co_count}
- Approved Revenue: ${float(co.approved_revenue or 0):,.2f}
- Approved Cost: ${float(co.approved_cost or 0):,.2f}
- Pending Revenue (not yet approved): ${float(co.pending_revenue or 0):,.2f}

MARGIN HISTORY (most recent first):
{history_text}

Alert Level: {alert_level}

Provide:
1. A 2-3 sentence executive summary of this project's financial health
2. The primary drivers of margin erosion (be specific about cost categories)
3. Risk assessment (Low/Medium/High/Critical) with justification
4. 3 specific, actionable recommendations for the project manager
5. If there are pending change orders, comment on their potential impact

Keep the analysis factual and based solely on the numbers provided. Do not invent or assume any figures not given."""
            
            try:
                response = ai_client.chat.completions.create(
                    model='gpt4o-analysis',
                    messages=[
                        {'role': 'system', 'content': CONSTRUCTION_ANALYST_SYSTEM_PROMPT},
                        {'role': 'user', 'content': prompt}
                    ],
                    temperature=0.3,
                    max_tokens=1000
                )
                ai_narrative = response.choices[0].message.content
                
                # Extract risk score from narrative (look for risk assessment)
                if 'Critical' in ai_narrative:
                    ai_risk_score = min(100, ai_risk_score + 30)
                elif 'High' in ai_narrative:
                    ai_risk_score = min(100, ai_risk_score + 15)
            except Exception as e:
                logging.error(f'OpenAI call failed for project {project_id}: {e}')
                ai_narrative = f'AI analysis unavailable. Alert level: {alert_level}. Margin erosion: {margin_erosion_pct:.1f}%'
        
        # Insert margin snapshot
        cursor.execute("""
            INSERT INTO construction.fact_margin_snapshots
            (project_id, snapshot_date, contract_amount, revised_budget,
             cost_to_date, committed_costs, estimated_cost_to_complete,
             estimate_at_completion, original_margin_pct, earned_value,
             schedule_variance, cost_variance, ai_risk_score, ai_narrative, alert_triggered)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, project_id, today, contract_amount, float(project.revised_budget or original_budget),
             cost_to_date, committed_remaining, est_cost_to_complete,
             eac, original_margin_pct, earned_value,
             schedule_variance, cost_variance, ai_risk_score, ai_narrative,
             1 if alert_triggered else 0)
        
        if alert_triggered:
            logging.warning(f'ALERT [{alert_level}]: Project {project.project_name} - margin erosion {margin_erosion_pct:.1f}%')
    
    conn.commit()
    conn.close()
    logging.info(f'Margin calculation complete for {len(projects)} projects')


# System prompt constant
CONSTRUCTION_ANALYST_SYSTEM_PROMPT = """You are an expert construction financial analyst assistant working for a general contractor. Your role is to analyze project cost data and provide clear, actionable insights about margin erosion and financial risk.

RULES:
1. Only reference numbers explicitly provided in the data. Never invent, estimate, or hallucinate financial figures.
2. Use construction industry terminology: EAC (Estimate at Completion), CTC (Cost to Complete), WIP, cost-to-date, committed costs, earned value, cost variance, schedule variance.
3. Be direct and specific. Name the exact cost categories driving erosion.
4. Recommendations must be actionable by a project manager: e.g., 'Review subcontractor XYZ invoices against contract terms' not 'Consider reviewing costs.'
5. If the project is bonded, note implications for the surety relationship if margin continues to erode.
6. If the project is prevailing wage, consider whether labor cost overruns may be related to wage compliance.
7. Format your response with clear headers: EXECUTIVE SUMMARY, EROSION DRIVERS, RISK ASSESSMENT, RECOMMENDATIONS, CHANGE ORDER IMPACT (if applicable).
8. Keep total response under 500 words."""

# sync_costs/__init__.py
import azure.functions as func
import pyodbc
import requests
import json
import logging
from datetime import datetime, timedelta
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

def main(timer: func.TimerRequest) -> None:
    logging.info('Cost sync function triggered')
    
    credential = DefaultAzureCredential()
    kv_client = SecretClient(vault_url='https://kv-construction.vault.azure.net/', credential=credential)
    
    agave_api_key = kv_client.get_secret('AgaveApiKey').value
    agave_client_secret = kv_client.get_secret('AgaveClientSecret').value
    sql_conn_str = kv_client.get_secret('SqlConnectionString').value
    
    headers = {
        'API-Key': agave_api_key,
        'Client-Secret': agave_client_secret,
        'Content-Type': 'application/json'
    }
    
    conn = pyodbc.connect(sql_conn_str)
    cursor = conn.cursor()
    
    # Get last sync timestamp
    cursor.execute("""
        SELECT ISNULL(MAX(imported_at), '2020-01-01') as last_sync
        FROM construction.fact_actual_costs
    """)
    last_sync = cursor.fetchone().last_sync
    
    # Get linked accounts from Agave
    accounts_resp = requests.get('https://api.agaveapi.com/linked-accounts', headers=headers)
    accounts = accounts_resp.json().get('data', [])
    
    for account in accounts:
        account_token = account.get('account_token')
        account_headers = {**headers, 'Account-Token': account_token}
        
        # Sync projects first
        projects_resp = requests.get(
            'https://api.agaveapi.com/projects',
            headers=account_headers,
            params={'updated_after': last_sync.isoformat() if hasattr(last_sync, 'isoformat') else str(last_sync)}
        )
        
        if projects_resp.status_code == 200:
            for proj in projects_resp.json().get('data', []):
                # Upsert project into dim_projects
                cursor.execute("""
                    MERGE construction.dim_projects AS target
                    USING (SELECT ? as source_system_id) AS source
                    ON target.source_system_id = source.source_system_id AND target.source_system = ?
                    WHEN MATCHED THEN
                        UPDATE SET project_name = ?, contract_amount = ?,
                                   project_status = ?, updated_at = GETUTCDATE()
                    WHEN NOT MATCHED THEN
                        INSERT (source_system_id, source_system, project_name, project_number,
                                contract_amount, original_budget, project_status)
                        VALUES (?, ?, ?, ?, ?, ?, ?);
                """, proj.get('id'), account.get('source_system', 'unknown'),
                     proj.get('name'), proj.get('contract_value'),
                     proj.get('status', 'Active'),
                     proj.get('id'), account.get('source_system', 'unknown'),
                     proj.get('name'), proj.get('number'),
                     proj.get('contract_value'), proj.get('budget'),
                     proj.get('status', 'Active'))
        
        # Sync actual cost transactions
        # Agave normalizes these as 'invoices' or 'direct_costs' depending on source system
        for endpoint in ['invoices', 'direct-costs']:
            page = 1
            while True:
                costs_resp = requests.get(
                    f'https://api.agaveapi.com/{endpoint}',
                    headers=account_headers,
                    params={
                        'updated_after': last_sync.isoformat() if hasattr(last_sync, 'isoformat') else str(last_sync),
                        'page': page,
                        'per_page': 100
                    }
                )
                
                if costs_resp.status_code != 200:
                    logging.error(f'Failed to fetch {endpoint}: {costs_resp.status_code}')
                    break
                
                data = costs_resp.json().get('data', [])
                if not data:
                    break
                
                for cost in data:
                    # Map to our schema
                    project_source_id = cost.get('project_id')
                    
                    # Look up our internal project_id
                    cursor.execute("""
                        SELECT project_id FROM construction.dim_projects
                        WHERE source_system_id = ? AND source_system = ?
                    """, project_source_id, account.get('source_system', 'unknown'))
                    proj_row = cursor.fetchone()
                    
                    if not proj_row:
                        logging.warning(f'Project {project_source_id} not found, skipping cost')
                        continue
                    
                    internal_project_id = proj_row.project_id
                    
                    # Resolve or create cost code
                    cost_code_value = cost.get('cost_code', 'UNASSIGNED')
                    cursor.execute("""
                        SELECT cost_code_id FROM construction.dim_cost_codes
                        WHERE cost_code = ?
                    """, cost_code_value)
                    cc_row = cursor.fetchone()
                    
                    if not cc_row:
                        cursor.execute("""
                            INSERT INTO construction.dim_cost_codes (cost_code, cost_code_description, cost_type)
                            VALUES (?, ?, ?)
                        """, cost_code_value, cost.get('cost_code_description', ''),
                             cost.get('cost_type', 'Other'))
                        cursor.execute('SELECT @@IDENTITY')
                        cost_code_id = int(cursor.fetchone()[0])
                    else:
                        cost_code_id = cc_row.cost_code_id
                    
                    # Resolve or create vendor
                    vendor_name = cost.get('vendor_name', 'Unknown')
                    cursor.execute("""
                        SELECT vendor_id FROM construction.dim_vendors WHERE vendor_name = ?
                    """, vendor_name)
                    v_row = cursor.fetchone()
                    
                    if not v_row:
                        cursor.execute("""
                            INSERT INTO construction.dim_vendors (vendor_name, vendor_type) VALUES (?, ?)
                        """, vendor_name, cost.get('vendor_type', 'Supplier'))
                        cursor.execute('SELECT @@IDENTITY')
                        vendor_id = int(cursor.fetchone()[0])
                    else:
                        vendor_id = v_row.vendor_id
                    
                    # Insert cost transaction (check for duplicates)
                    source_doc = cost.get('number', cost.get('id', ''))
                    cursor.execute("""
                        SELECT actual_cost_id FROM construction.fact_actual_costs
                        WHERE project_id = ? AND source_document = ? AND amount = ?
                    """, internal_project_id, source_doc, cost.get('amount', 0))
                    
                    if not cursor.fetchone():
                        cursor.execute("""
                            INSERT INTO construction.fact_actual_costs
                            (project_id, cost_code_id, vendor_id, transaction_date,
                             amount, description, source_document, cost_type)
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        """, internal_project_id, cost_code_id, vendor_id,
                             cost.get('date', datetime.utcnow().date()),
                             float(cost.get('amount', 0)),
                             cost.get('description', ''),
                             source_doc,
                             cost.get('cost_type', 'Other'))
                
                page += 1
                if len(data) < 100:
                    break
    
    conn.commit()
    conn.close()
    logging.info('Cost sync complete')


# sync_projects/__init__.py - similar structure, focused on project master data
# sync_commitments/__init__.py - similar structure for POs and subcontracts
# See sync_costs above as the template pattern; adapt the Agave endpoints:
#   Projects: GET /projects
#   Commitments: GET /purchase-orders and GET /subcontracts
#   Change Orders: GET /change-orders

Total Contract Amount = SUM(dim_projects[contract_amount])

Original Budget = SUM(fact_budget[original_budget_amount])

Revised Budget = SUM(fact_budget[revised_budget_amount])

Cost to Date = SUM(fact_actual_costs[amount])

Committed Costs = 
    CALCULATE(
        SUM(fact_commitments[revised_amount]),
        fact_commitments[status] = "Open"
    )

Committed Uninvoiced = 
    CALCULATE(
        SUM(fact_commitments[remaining_to_invoice]),
        fact_commitments[status] = "Open",
        fact_commitments[remaining_to_invoice] > 0
    )

Estimated Cost to Complete = 
    CALCULATE(
        SUM(fact_cost_to_complete[estimated_cost_to_complete]),
        LASTDATE(fact_cost_to_complete[estimate_date])
    )

Estimate at Completion (EAC) = 
    [Cost to Date] + [Committed Uninvoiced] + [Estimated Cost to Complete]

Projected Margin $ = [Total Contract Amount] - [Estimate at Completion (EAC)]

Projected Margin % = 
    DIVIDE([Projected Margin $], [Total Contract Amount], 0) * 100

Original Margin % = 
    DIVIDE([Total Contract Amount] - [Original Budget], [Total Contract Amount], 0) * 100

Margin Erosion % = [Projected Margin %] - [Original Margin %]

Budget Variance = [Revised Budget] - [Estimate at Completion (EAC)]

Budget Variance % = DIVIDE([Budget Variance], [Revised Budget], 0) * 100

Percent Complete = 
    CALCULATE(
        MAX(fact_cost_to_complete[percent_complete]),
        LASTDATE(fact_cost_to_complete[estimate_date])
    )

Cost Performance Index (CPI) = 
    VAR EV = [Total Contract Amount] * [Percent Complete] / 100
    RETURN DIVIDE(EV, [Cost to Date], 1)

Margin Health Status = 
    SWITCH(
        TRUE(),
        [Margin Erosion %] <= -10 || [Projected Margin $] < 0, "CRITICAL",
        [Margin Erosion %] <= -5, "WARNING",
        [Margin Erosion %] <= -2, "WATCH",
        "HEALTHY"
    )

Margin Health Color = 
    SWITCH(
        [Margin Health Status],
        "CRITICAL", "#D32F2F",
        "WARNING", "#F57C00",
        "WATCH", "#FBC02D",
        "#388E3C"
    )

AI Risk Score = 
    CALCULATE(
        MAX(fact_margin_snapshots[ai_risk_score]),
        LASTDATE(fact_margin_snapshots[snapshot_date])
    )

Latest AI Narrative = 
    CALCULATE(
        MAX(fact_margin_snapshots[ai_narrative]),
        LASTDATE(fact_margin_snapshots[snapshot_date])
    )

Projects at Risk Count = 
    CALCULATE(
        COUNTROWS(dim_projects),
        FILTER(
            dim_projects,
            [Margin Health Status] IN {"CRITICAL", "WARNING"}
        )
    )

# Azure Function called by Power Automate to generate weekly executive
# summary email via Azure OpenAI

# weekly_summary_generator.py
# Called by Power Automate via HTTP-triggered Azure Function

import azure.functions as func
import pyodbc
import json
from openai import AzureOpenAI
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient
from datetime import datetime, timedelta

def main(req: func.HttpRequest) -> func.HttpResponse:
    credential = DefaultAzureCredential()
    kv_client = SecretClient(vault_url='https://kv-construction.vault.azure.net/', credential=credential)
    
    sql_conn_str = kv_client.get_secret('SqlConnectionString').value
    openai_endpoint = kv_client.get_secret('OpenAIEndpoint').value
    openai_key = kv_client.get_secret('OpenAIKey').value
    
    ai_client = AzureOpenAI(
        azure_endpoint=openai_endpoint,
        api_key=openai_key,
        api_version='2024-06-01'
    )
    
    conn = pyodbc.connect(sql_conn_str)
    cursor = conn.cursor()
    
    today = datetime.utcnow().date()
    week_ago = today - timedelta(days=7)
    
    # Get latest margin snapshots for all active projects
    cursor.execute("""
        SELECT p.project_name, p.project_number, p.project_manager, p.contract_amount,
               ms.estimate_at_completion, ms.projected_margin_pct, ms.ai_risk_score,
               ms.margin_erosion_pct, ms.cost_to_date, ms.alert_triggered,
               prev.projected_margin_pct as prev_margin_pct
        FROM construction.dim_projects p
        INNER JOIN construction.fact_margin_snapshots ms
            ON p.project_id = ms.project_id AND ms.snapshot_date = ?
        LEFT JOIN construction.fact_margin_snapshots prev
            ON p.project_id = prev.project_id AND prev.snapshot_date = ?
        WHERE p.project_status = 'Active'
        ORDER BY ms.ai_risk_score DESC
    """, today, week_ago)
    
    projects_data = []
    total_contract = 0
    total_eac = 0
    critical_count = 0
    warning_count = 0
    
    for row in cursor.fetchall():
        contract = float(row.contract_amount or 0)
        eac = float(row.estimate_at_completion or 0)
        margin_pct = float(row.projected_margin_pct or 0)
        erosion = float(row.margin_erosion_pct or 0)
        prev_margin = float(row.prev_margin_pct or margin_pct)
        weekly_change = margin_pct - prev_margin
        
        total_contract += contract
        total_eac += eac
        
        status = 'HEALTHY'
        if erosion <= -10 or (contract - eac) < 0:
            status = 'CRITICAL'
            critical_count += 1
        elif erosion <= -5:
            status = 'WARNING'
            warning_count += 1
        elif erosion <= -2:
            status = 'WATCH'
        
        projects_data.append({
            'name': row.project_name,
            'number': row.project_number,
            'pm': row.project_manager,
            'contract': contract,
            'eac': eac,
            'margin_pct': margin_pct,
            'erosion': erosion,
            'weekly_change': weekly_change,
            'risk_score': float(row.ai_risk_score or 0),
            'status': status
        })
    
    conn.close()
    
    portfolio_margin = ((total_contract - total_eac) / total_contract * 100) if total_contract > 0 else 0
    
    # Build the prompt
    projects_text = ''
    for p in projects_data:
        emoji = {'CRITICAL': '🔴', 'WARNING': '🟠', 'WATCH': '🟡', 'HEALTHY': '🟢'}[p['status']]
        direction = '↑' if p['weekly_change'] > 0 else '↓' if p['weekly_change'] < 0 else '→'
        projects_text += f"{emoji} {p['name']} ({p['number']}): Margin {p['margin_pct']:.1f}% {direction} ({p['weekly_change']:+.1f}% this week) | PM: {p['pm']} | Risk: {p['risk_score']:.0f}/100\n"
    
    prompt = f"""Generate a weekly construction portfolio executive summary for the week ending {today.strftime('%B %d, %Y')}.

PORTFOLIO OVERVIEW:
- Active Projects: {len(projects_data)}
- Total Contract Value: ${total_contract:,.0f}
- Total EAC: ${total_eac:,.0f}
- Portfolio Margin: {portfolio_margin:.1f}%
- Critical Projects: {critical_count}
- Warning Projects: {warning_count}

PROJECT STATUS:
{projects_text}

Generate a professional executive summary email body (no subject line needed) that:
1. Opens with a 2-sentence portfolio health overview
2. Highlights the top 3 risk areas requiring executive attention
3. Notes any projects that improved or deteriorated significantly this week (>1% margin change)
4. Closes with 2-3 recommended executive actions
5. Keep it under 400 words
6. Use a professional but accessible tone — this goes to a construction company CEO/CFO who is not a data analyst
7. Include the project status emojis for quick scanning"""
    
    response = ai_client.chat.completions.create(
        model='gpt4o-analysis',
        messages=[
            {'role': 'system', 'content': 'You are a construction financial advisor writing a weekly executive briefing for a contractor CEO/CFO. Be concise, specific, and action-oriented. Use construction industry language.'},
            {'role': 'user', 'content': prompt}
        ],
        temperature=0.4,
        max_tokens=800
    )
    
    summary_html = response.choices[0].message.content
    
    # Return HTML-formatted email body for Power Automate
    return func.HttpResponse(
        json.dumps({
            'subject': f'Weekly Project Portfolio Summary — {today.strftime("%B %d, %Y")} | {critical_count} Critical, {warning_count} Warning',
            'body': summary_html,
            'critical_count': critical_count,
            'warning_count': warning_count,
            'portfolio_margin': round(portfolio_margin, 1)
        }),
        mimetype='application/json'
    )

INSERT INTO construction.fact_cost_to_complete
   (project_id, cost_code_id, estimate_date, cost_to_date,
    committed_not_invoiced, estimated_cost_to_complete,
    percent_complete, entered_by, notes)
   SELECT 
     p.project_id, 
     cc.cost_code_id,
     CAST(GETUTCDATE() AS DATE),
     @CostToDate_System,
     @CommittedNotInvoiced_System,
     @EstimatedCostToComplete,
     @PercentComplete,
     @SubmittedBy,
     @Notes
   FROM construction.dim_projects p
   CROSS JOIN construction.dim_cost_codes cc
   WHERE p.project_name = @ProjectName
     AND cc.cost_code = @CostCode

# One-time script to import historical project data from CSV/Excel exports

# historical_import.py
# One-time script to import historical project data from CSV/Excel exports

import pandas as pd
import pyodbc
import argparse
import os
import logging
from datetime import datetime

logging.basicConfig(level=logging.INFO)

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('--source-dir', required=True, help='Directory containing CSV/Excel exports')
    parser.add_argument('--target-db', required=True, help='Target database name')
    parser.add_argument('--sql-server', default='sql-construction-analytics.database.windows.net')
    parser.add_argument('--sql-user', default='sqladmin')
    parser.add_argument('--sql-password', required=True)
    args = parser.parse_args()
    
    conn_str = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={args.sql_server};DATABASE={args.target_db};UID={args.sql_user};PWD={args.sql_password};Encrypt=yes;TrustServerCertificate=no'
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    
    # Process each file in the source directory
    for filename in os.listdir(args.source_dir):
        filepath = os.path.join(args.source_dir, filename)
        
        if filename.lower().endswith('.csv'):
            df = pd.read_csv(filepath)
        elif filename.lower().endswith(('.xlsx', '.xls')):
            df = pd.read_excel(filepath)
        else:
            continue
        
        logging.info(f'Processing {filename}: {len(df)} rows')
        
        # Detect file type by column names and import accordingly
        cols_lower = [c.lower().strip() for c in df.columns]
        
        if any('project' in c and 'name' in c for c in cols_lower):
            import_projects(cursor, df)
        elif any('cost' in c and 'code' in c for c in cols_lower) and any('budget' in c for c in cols_lower):
            import_budgets(cursor, df)
        elif any('amount' in c for c in cols_lower) and any('date' in c for c in cols_lower):
            import_costs(cursor, df)
        else:
            logging.warning(f'Could not determine file type for {filename}')
    
    conn.commit()
    conn.close()
    logging.info('Historical import complete')

def import_projects(cursor, df):
    """Import project master data from typical accounting export format."""
    # Normalize column names
    df.columns = [c.lower().strip().replace(' ', '_') for c in df.columns]
    
    for _, row in df.iterrows():
        project_name = str(row.get('project_name', row.get('job_name', '')))
        project_number = str(row.get('project_number', row.get('job_number', '')))
        contract_amount = float(row.get('contract_amount', row.get('contract_value', 0)) or 0)
        budget = float(row.get('original_budget', row.get('budget', contract_amount * 0.85)) or 0)
        status = str(row.get('status', row.get('project_status', 'Complete')))
        
        cursor.execute("""
            IF NOT EXISTS (SELECT 1 FROM construction.dim_projects WHERE project_name = ? AND source_system = 'historical')
            INSERT INTO construction.dim_projects
            (source_system_id, source_system, project_name, project_number,
             contract_amount, original_budget, revised_budget, project_status)
            VALUES (?, 'historical', ?, ?, ?, ?, ?, ?)
        """, project_name, project_number, project_name, project_number,
             contract_amount, budget, budget, status)
    
    logging.info(f'Imported {len(df)} projects')

def import_costs(cursor, df):
    """Import historical cost transactions."""
    df.columns = [c.lower().strip().replace(' ', '_') for c in df.columns]
    imported = 0
    
    for _, row in df.iterrows():
        project_name = str(row.get('project_name', row.get('job_name', '')))
        
        cursor.execute("""
            SELECT project_id FROM construction.dim_projects WHERE project_name = ?
        """, project_name)
        proj = cursor.fetchone()
        if not proj:
            continue
        
        amount = float(row.get('amount', row.get('cost', 0)) or 0)
        trans_date = row.get('date', row.get('transaction_date', datetime.now().date()))
        cost_code_val = str(row.get('cost_code', 'UNASSIGNED'))
        cost_type = str(row.get('cost_type', row.get('type', 'Other')))
        
        # Ensure cost code exists
        cursor.execute("SELECT cost_code_id FROM construction.dim_cost_codes WHERE cost_code = ?", cost_code_val)
        cc = cursor.fetchone()
        if not cc:
            cursor.execute("INSERT INTO construction.dim_cost_codes (cost_code, cost_type) VALUES (?, ?)", cost_code_val, cost_type)
            cursor.execute('SELECT @@IDENTITY')
            cc_id = int(cursor.fetchone()[0])
        else:
            cc_id = cc.cost_code_id
        
        cursor.execute("""
            INSERT INTO construction.fact_actual_costs
            (project_id, cost_code_id, transaction_date, amount, cost_type, description)
            VALUES (?, ?, ?, ?, ?, ?)
        """, proj.project_id, cc_id, trans_date, amount, cost_type,
             str(row.get('description', row.get('memo', ''))))
        imported += 1
    
    logging.info(f'Imported {imported} cost transactions')

def import_budgets(cursor, df):
    """Import budget line items."""
    df.columns = [c.lower().strip().replace(' ', '_') for c in df.columns]
    
    for _, row in df.iterrows():
        project_name = str(row.get('project_name', row.get('job_name', '')))
        cursor.execute("SELECT project_id FROM construction.dim_projects WHERE project_name = ?", project_name)
        proj = cursor.fetchone()
        if not proj:
            continue
        
        cost_code_val = str(row.get('cost_code', 'UNASSIGNED'))
        cursor.execute("SELECT cost_code_id FROM construction.dim_cost_codes WHERE cost_code = ?", cost_code_val)
        cc = cursor.fetchone()
        if not cc:
            cursor.execute("INSERT INTO construction.dim_cost_codes (cost_code) VALUES (?)", cost_code_val)
            cursor.execute('SELECT @@IDENTITY')
            cc_id = int(cursor.fetchone()[0])
        else:
            cc_id = cc.cost_code_id
        
        budget_amt = float(row.get('budget_amount', row.get('original_budget', 0)) or 0)
        
        cursor.execute("""
            INSERT INTO construction.fact_budget
            (project_id, cost_code_id, original_budget_amount, budget_date, snapshot_date)
            VALUES (?, ?, ?, ?, ?)
        """, proj.project_id, cc_id, budget_amt,
             row.get('budget_date', datetime.now().date()), datetime.now().date())
    
    logging.info(f'Imported {len(df)} budget line items')

if __name__ == '__main__':
    main()

# Analyzes completed projects to recommend optimal alert thresholds

# calibrate_thresholds.py
# Analyzes completed projects to recommend optimal alert thresholds

import pyodbc
import json
import argparse
import numpy as np
from datetime import datetime

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument('--output', default='threshold_recommendations.json')
    parser.add_argument('--sql-server', default='sql-construction-analytics.database.windows.net')
    parser.add_argument('--sql-user', default='sqladmin')
    parser.add_argument('--sql-password', required=True)
    parser.add_argument('--target-db', default='ConstructionCostDB')
    args = parser.parse_args()
    
    conn_str = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={args.sql_server};DATABASE={args.target_db};UID={args.sql_user};PWD={args.sql_password};Encrypt=yes'
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    
    # Get completed projects with their original margin and final margin
    cursor.execute("""
        SELECT p.project_id, p.project_name, p.contract_amount, p.original_budget,
               (
                   SELECT SUM(amount) FROM construction.fact_actual_costs
                   WHERE project_id = p.project_id
               ) as total_actual_cost
        FROM construction.dim_projects p
        WHERE p.project_status = 'Complete'
          AND p.contract_amount > 0
    """)
    
    results = []
    for row in cursor.fetchall():
        contract = float(row.contract_amount or 0)
        budget = float(row.original_budget or 0)
        actual = float(row.total_actual_cost or 0)
        
        if contract == 0:
            continue
        
        original_margin_pct = (contract - budget) / contract * 100
        final_margin_pct = (contract - actual) / contract * 100
        erosion = final_margin_pct - original_margin_pct
        
        results.append({
            'project': row.project_name,
            'original_margin': round(original_margin_pct, 2),
            'final_margin': round(final_margin_pct, 2),
            'erosion': round(erosion, 2),
            'loss': final_margin_pct < 0
        })
    
    conn.close()
    
    if not results:
        print('No completed projects found for calibration')
        return
    
    erosions = [r['erosion'] for r in results]
    loss_erosions = [r['erosion'] for r in results if r['loss']]
    
    recommendations = {
        'analysis_date': datetime.now().isoformat(),
        'projects_analyzed': len(results),
        'projects_with_loss': len([r for r in results if r['loss']]),
        'avg_margin_erosion': round(np.mean(erosions), 2),
        'median_margin_erosion': round(np.median(erosions), 2),
        'std_margin_erosion': round(np.std(erosions), 2),
        'recommended_thresholds': {
            'watch': round(np.percentile(erosions, 25), 1),
            'warning': round(np.percentile(erosions, 10), 1),
            'critical': round(min(np.percentile(erosions, 5), -10), 1)
        },
        'rationale': f'Based on {len(results)} completed projects. '
                     f'Average erosion was {np.mean(erosions):.1f}%. '
                     f'{len(loss_erosions)} projects resulted in losses. '
                     f'Thresholds set at 25th/10th/5th percentiles of erosion distribution.',
        'project_details': results
    }
    
    with open(args.output, 'w') as f:
        json.dump(recommendations, f, indent=2)
    
    print(f'Threshold recommendations saved to {args.output}')
    print(f'  WATCH: {recommendations["recommended_thresholds"]["watch"]}%')
    print(f'  WARNING: {recommendations["recommended_thresholds"]["warning"]}%')
    print(f'  CRITICAL: {recommendations["recommended_thresholds"]["critical"]}%')

if __name__ == '__main__':
    main()