Implementation Guide: Benchmark material costs against historical bids and market indexes

# PowerShell - Connect to Microsoft Graph and provision licenses
Install-Module Microsoft.Graph -Scope CurrentUser
Connect-MgGraph -Scopes 'User.ReadWrite.All','Organization.Read.All'
# Verify available licenses
Get-MgSubscribedSku | Select-Object SkuPartNumber, ConsumedUnits, PrepaidUnits
# Create security group for Power BI access
New-MgGroup -DisplayName 'CostBenchmarking-Users' -MailEnabled:$false -SecurityEnabled:$true -MailNickname 'costbenchmarking'
# Add users to group
$groupId = (Get-MgGroup -Filter "displayName eq 'CostBenchmarking-Users'").Id
$userId = (Get-MgUser -Filter "userPrincipalName eq 'estimator@clientdomain.com'").Id
New-MgGroupMember -GroupId $groupId -DirectoryObjectId $userId

ProjectName, BidDate, CSIDivision, MaterialDescription, Quantity, Unit, UnitCost, ExtendedCost, BidResult

# RSMeans mapping table structure (create as SharePoint List or Excel file)
# Columns: ClientMaterialCode, ClientMaterialName, RSMeansLineItemID, RSMeansDescription, CSIDivision, Unit
# Example rows:
# CONC-3000, 3000 PSI Ready Mix Concrete, 03 31 13.35 0300, Structural concrete ready mix 3000 psi, 03, C.Y.
# REBAR-4, #4 Rebar Grade 60, 03 21 11.60 0500, Reinforcing steel bars #4, 03, Ton
# LMBR-2X4, 2x4 SPF Stud 8ft, 06 11 10.01 0020, Framing lumber 2x4x8 SPF, 06, MBF
# Create this mapping for all material categories tracked by the client

# install dependencies, scaffold directories, test API connection, and
# schedule monthly run

# Register for BLS API key at: https://data.bls.gov/registrationEngine/
# Save API key securely (Azure Key Vault or .env file)

# Install Python dependencies
pip install requests pandas python-dotenv schedule openpyxl

# Create project directory structure
mkdir -p ~/cost-benchmarking/data/bls_ppi
mkdir -p ~/cost-benchmarking/data/rsmeans_exports
mkdir -p ~/cost-benchmarking/data/proest_exports
mkdir -p ~/cost-benchmarking/logs

# Create the BLS PPI ETL script (see custom_ai_components for full implementation)
# Save as ~/cost-benchmarking/scripts/bls_ppi_fetcher.py

# Test the API connection
python -c "import requests; r = requests.get('https://api.bls.gov/publicAPI/v2/timeseries/data/WPU0811', json={'seriesid':['WPU0811'],'startyear':'2023','endyear':'2024'}); print(r.json()['status'])"

# Set up monthly cron job (Linux/Mac) or Task Scheduler (Windows)
# Linux/Mac crontab entry:
# 0 6 1 * * cd ~/cost-benchmarking && python scripts/bls_ppi_fetcher.py >> logs/bls_ppi.log 2>&1

# Windows Task Scheduler: Create task running monthly on the 1st at 6:00 AM
# Action: Start a program
# Program: python.exe
# Arguments: C:\cost-benchmarking\scripts\bls_ppi_fetcher.py
# Start in: C:\cost-benchmarking

ClientAvgUnitCost = AVERAGE(MaterialCosts[UnitCost])
RSMeansUnitCost = LOOKUPVALUE(RSMeans[UnitCost], RSMeans[LineItemID], MaterialCosts[RSMeansLineItemID])
CostVariancePct = DIVIDE([ClientAvgUnitCost] - [RSMeansUnitCost], [RSMeansUnitCost], 0)
PPIChangeYoY = CALCULATE(AVERAGE(BLS_PPI[YoYChangePct]), FILTER(BLS_PPI, BLS_PPI[SeriesID] = SELECTEDVALUE(Materials[PPISeriesID])))
BidWinRate = DIVIDE(COUNTROWS(FILTER(Projects, Projects[BidResult] = "Win")), COUNTROWS(Projects))
MaterialCostTrend = CALCULATE([ClientAvgUnitCost], DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -12, MONTH))

# Install dependencies
pip install openai flask pandas python-dotenv

# Create .env file with API key
echo 'OPENAI_API_KEY=sk-your-key-here' > ~/cost-benchmarking/.env

# See custom_ai_components for full implementation of the query app

# Test the API connection
python -c "from openai import OpenAI; import os; from dotenv import load_dotenv; load_dotenv(); client = OpenAI(); r = client.chat.completions.create(model='gpt-5.4-mini', messages=[{'role':'user','content':'Hello'}]); print(r.choices[0].message.content)"

# Run the app locally for testing
cd ~/cost-benchmarking
python scripts/cost_query_app.py
# Access at http://localhost:5000

# For production deployment on Azure App Service:
az webapp create --resource-group CostBenchmarking-RG --plan CostBenchmark-Plan --name client-cost-query --runtime 'PYTHON:3.11'
az webapp config appsettings set --name client-cost-query --settings OPENAI_API_KEY=sk-your-key-here
az webapp deploy --name client-cost-query --src-path ./cost-benchmarking.zip

# Save as ~/cost-benchmarking/scripts/health_check.py
# (see custom_ai_components for health_check.py implementation)

schtasks /create /tn "CostBenchmark-HealthCheck" /tr "python C:\cost-benchmarking\scripts\health_check.py" /sc daily /st 08:00

# Fetches BLS Producer Price Index data for construction materials. Run
# monthly via cron/Task Scheduler.

#!/usr/bin/env python3
"""
bls_ppi_fetcher.py
Fetches BLS Producer Price Index data for construction materials.
Run monthly via cron/Task Scheduler.
"""
import os
import json
import logging
import requests
import pandas as pd
from datetime import datetime
from dotenv import load_dotenv

load_dotenv()
logging.basicConfig(
    filename=os.path.join(os.path.dirname(__file__), '..', 'logs', 'bls_ppi.log'),
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# Configuration
BLS_API_URL = 'https://api.bls.gov/publicAPI/v2/timeseries/data/'
BLS_API_KEY = os.getenv('BLS_API_KEY', '')  # Register at https://data.bls.gov/registrationEngine/
OUTPUT_DIR = os.path.join(os.path.dirname(__file__), '..', 'data', 'bls_ppi')

# Construction material PPI series
SERIES_CONFIG = {
    'WPU0811': {'name': 'Lumber', 'category': 'Wood & Plastics', 'csi_division': '06'},
    'WPU1017': {'name': 'Steel Mill Products', 'category': 'Metals', 'csi_division': '05'},
    'WPU1332': {'name': 'Concrete Products', 'category': 'Concrete', 'csi_division': '03'},
    'WPU0553': {'name': 'Diesel Fuel', 'category': 'Equipment/Transport', 'csi_division': 'NA'},
    'WPU1022': {'name': 'Copper Wire & Cable', 'category': 'Electrical', 'csi_division': '26'},
    'WPU133':  {'name': 'Asphalt Paving', 'category': 'Paving', 'csi_division': '32'},
    'WPU0711': {'name': 'Gypsum Products', 'category': 'Finishes', 'csi_division': '09'},
    'WPU0241': {'name': 'Industrial Chemicals', 'category': 'Adhesives/Sealants', 'csi_division': '07'},
    'WPU1331': {'name': 'Ready-Mix Concrete', 'category': 'Concrete', 'csi_division': '03'},
    'WPU072':  {'name': 'Flat Glass', 'category': 'Openings', 'csi_division': '08'},
    'WPU0241': {'name': 'Paint & Coatings', 'category': 'Finishes', 'csi_division': '09'},
    'WPU1061': {'name': 'Plumbing Fixtures', 'category': 'Plumbing', 'csi_division': '22'},
}

def fetch_bls_data(series_ids, start_year, end_year):
    """Fetch data from BLS API v2.0 for given series IDs."""
    headers = {'Content-type': 'application/json'}
    payload = {
        'seriesid': series_ids,
        'startyear': str(start_year),
        'endyear': str(end_year),
        'catalog': False,
        'calculations': True,  # Includes % change calculations
        'annualaverage': True,
        'aspects': False,
    }
    if BLS_API_KEY:
        payload['registrationkey'] = BLS_API_KEY
    
    response = requests.post(BLS_API_URL, json=payload, headers=headers, timeout=30)
    response.raise_for_status()
    
    data = response.json()
    if data['status'] != 'REQUEST_SUCCEEDED':
        raise Exception(f"BLS API error: {data.get('message', 'Unknown error')}")
    
    return data['Results']['series']

def process_series_data(series_list):
    """Convert BLS API response to a flat DataFrame."""
    rows = []
    for series in series_list:
        series_id = series['seriesID']
        config = SERIES_CONFIG.get(series_id, {'name': 'Unknown', 'category': 'Unknown', 'csi_division': 'NA'})
        
        for item in series['data']:
            if item['period'] == 'M13':  # Skip annual average rows
                continue
            
            month_num = int(item['period'].replace('M', ''))
            try:
                date = datetime(int(item['year']), month_num, 1)
            except ValueError:
                continue
            
            row = {
                'SeriesID': series_id,
                'MaterialName': config['name'],
                'Category': config['category'],
                'CSIDivision': config['csi_division'],
                'Date': date.strftime('%Y-%m-%d'),
                'Year': int(item['year']),
                'Month': month_num,
                'IndexValue': float(item['value']),
                'PctChange1Month': None,
                'PctChange12Month': None,
            }
            
            # Extract % change calculations if available
            if 'calculations' in item:
                calcs = item['calculations']
                if 'pct_changes' in calcs:
                    row['PctChange1Month'] = calcs['pct_changes'].get('1', None)
                    row['PctChange12Month'] = calcs['pct_changes'].get('12', None)
            
            rows.append(row)
    
    return pd.DataFrame(rows)

def main():
    logging.info('Starting BLS PPI data fetch')
    os.makedirs(OUTPUT_DIR, exist_ok=True)
    
    current_year = datetime.now().year
    start_year = current_year - 5  # 5 years of history
    
    all_series_ids = list(SERIES_CONFIG.keys())
    
    # BLS API allows max 50 series per request; we're well under
    # But limit to 10 years per request
    all_data = []
    
    # Fetch in batches of 10 series (BLS recommends smaller batches)
    batch_size = 10
    for i in range(0, len(all_series_ids), batch_size):
        batch = all_series_ids[i:i + batch_size]
        logging.info(f'Fetching batch: {batch}')
        try:
            series_data = fetch_bls_data(batch, start_year, current_year)
            df_batch = process_series_data(series_data)
            all_data.append(df_batch)
        except Exception as e:
            logging.error(f'Error fetching batch {batch}: {e}')
    
    if all_data:
        df = pd.concat(all_data, ignore_index=True)
        df = df.sort_values(['SeriesID', 'Date'])
        
        # Save consolidated file
        output_file = os.path.join(OUTPUT_DIR, 'bls_ppi_consolidated.csv')
        df.to_csv(output_file, index=False)
        logging.info(f'Saved {len(df)} rows to {output_file}')
        
        # Save timestamped archive copy
        archive_file = os.path.join(OUTPUT_DIR, f'bls_ppi_{datetime.now().strftime("%Y%m%d")}.csv')
        df.to_csv(archive_file, index=False)
        logging.info(f'Archived to {archive_file}')
        
        # Print summary
        print(f'Successfully fetched {len(df)} data points for {df["SeriesID"].nunique()} series')
        print(f'Date range: {df["Date"].min()} to {df["Date"].max()}')
        print(f'Output: {output_file}')
    else:
        logging.error('No data fetched from any series')
        print('ERROR: No data fetched. Check logs for details.')

if __name__ == '__main__':
    main()

/* =============================================================
   POWER BI DAX MEASURES FOR COST ANOMALY DETECTION
   Add these to the Power BI data model created in Step 7
   ============================================================= */

/* --- DIMENSION TABLE: AnomalyThresholds --- */
/* Create as a calculated table or manual entry table */
AnomalyThresholds = 
    DATATABLE(
        "ThresholdName", STRING,
        "UpperPct", DOUBLE,
        "LowerPct", DOUBLE,
        "Description", STRING,
        {
            {"Material vs RSMeans", 0.15, -0.15, "Client unit cost vs RSMeans benchmark"},
            {"Material vs PPI Trend", 0.10, -0.10, "Material cost change vs PPI change"},
            {"Bid vs Actual", 0.20, -0.20, "Estimated material cost vs actual job cost"}
        }
    )

/* --- CORE BENCHMARK MEASURES --- */

// Client's average unit cost for selected material and time period
Client_Avg_Unit_Cost = 
    AVERAGE(MaterialCosts[UnitCost])

// RSMeans benchmark unit cost (looked up via mapping table)
RSMeans_Benchmark_Cost = 
    CALCULATE(
        AVERAGE(RSMeans[UnitCost]),
        TREATAS(
            VALUES(MaterialCosts[RSMeansLineItemID]),
            RSMeans[LineItemID]
        )
    )

// Variance percentage: positive = client pays MORE than market
Cost_Variance_Pct = 
    VAR ClientCost = [Client_Avg_Unit_Cost]
    VAR MarketCost = [RSMeans_Benchmark_Cost]
    RETURN
        IF(
            AND(NOT(ISBLANK(ClientCost)), NOT(ISBLANK(MarketCost))),
            DIVIDE(ClientCost - MarketCost, MarketCost, 0),
            BLANK()
        )

// Anomaly flag based on thresholds
Is_Cost_Anomaly = 
    VAR Variance = [Cost_Variance_Pct]
    RETURN
        SWITCH(
            TRUE(),
            Variance > 0.15, "OVER MARKET",
            Variance < -0.15, "UNDER MARKET",
            "NORMAL"
        )

// Anomaly severity (for conditional formatting)
Anomaly_Severity = 
    VAR Variance = ABS([Cost_Variance_Pct])
    RETURN
        SWITCH(
            TRUE(),
            Variance > 0.30, 3,  // Critical
            Variance > 0.15, 2,  // Warning
            Variance > 0.10, 1,  // Watch
            0                     // Normal
        )

/* --- PPI TREND MEASURES --- */

// Latest PPI index value for selected material
Latest_PPI_Value = 
    CALCULATE(
        MAX(BLS_PPI[IndexValue]),
        FILTER(
            BLS_PPI,
            BLS_PPI[Date] = CALCULATE(MAX(BLS_PPI[Date]))
        )
    )

// PPI Year-over-Year change
PPI_YoY_Change = 
    VAR LatestDate = CALCULATE(MAX(BLS_PPI[Date]))
    VAR LatestValue = CALCULATE(MAX(BLS_PPI[IndexValue]), BLS_PPI[Date] = LatestDate)
    VAR PriorYearDate = EDATE(LatestDate, -12)
    VAR PriorValue = CALCULATE(MAX(BLS_PPI[IndexValue]), BLS_PPI[Date] = PriorYearDate)
    RETURN
        DIVIDE(LatestValue - PriorValue, PriorValue, BLANK())

// PPI Month-over-Month change
PPI_MoM_Change = 
    VAR LatestDate = CALCULATE(MAX(BLS_PPI[Date]))
    VAR LatestValue = CALCULATE(MAX(BLS_PPI[IndexValue]), BLS_PPI[Date] = LatestDate)
    VAR PriorMonthDate = EDATE(LatestDate, -1)
    VAR PriorValue = CALCULATE(MAX(BLS_PPI[IndexValue]), BLS_PPI[Date] = PriorMonthDate)
    RETURN
        DIVIDE(LatestValue - PriorValue, PriorValue, BLANK())

// PPI Rapid Change Alert (>10% MoM)
PPI_Rapid_Change_Alert = 
    IF(ABS([PPI_MoM_Change]) > 0.10, "RAPID CHANGE", "STABLE")

/* --- BID PERFORMANCE MEASURES --- */

// Win rate for bids where material costs were within 10% of market
Win_Rate_At_Market = 
    VAR BidsAtMarket = 
        CALCULATE(
            COUNTROWS(Projects),
            FILTER(
                Projects,
                ABS([Cost_Variance_Pct]) <= 0.10
                && Projects[BidResult] = "Win"
            )
        )
    VAR TotalBidsAtMarket = 
        CALCULATE(
            COUNTROWS(Projects),
            FILTER(Projects, ABS([Cost_Variance_Pct]) <= 0.10)
        )
    RETURN
        DIVIDE(BidsAtMarket, TotalBidsAtMarket, BLANK())

// Estimated savings if anomalous materials were purchased at market rate
Potential_Savings = 
    SUMX(
        FILTER(
            MaterialCosts,
            [Is_Cost_Anomaly] = "OVER MARKET"
        ),
        MaterialCosts[Quantity] * ([Client_Avg_Unit_Cost] - [RSMeans_Benchmark_Cost])
    )

/* --- CALCULATED TABLE: Anomaly Summary --- */
/* Use as a data source for the Anomaly Detection dashboard page */
Anomaly_Summary = 
    ADDCOLUMNS(
        SUMMARIZE(
            MaterialCosts,
            Materials[MaterialName],
            Materials[CSIDivision],
            Materials[Category]
        ),
        "ClientAvgCost", [Client_Avg_Unit_Cost],
        "MarketBenchmark", [RSMeans_Benchmark_Cost],
        "VariancePct", [Cost_Variance_Pct],
        "AnomalyStatus", [Is_Cost_Anomaly],
        "Severity", [Anomaly_Severity],
        "PPITrend", [PPI_YoY_Change],
        "PPIAlert", [PPI_Rapid_Change_Alert]
    )

/* --- CONDITIONAL FORMATTING RULES --- */
/* Apply in Power BI visual formatting:
   - Cost_Variance_Pct: Red if > 0.15, Green if < -0.15, Yellow if between -0.10 and 0.10
   - Anomaly_Severity: Red = 3, Orange = 2, Yellow = 1, Green = 0
   - PPI_Rapid_Change_Alert: Red background for 'RAPID CHANGE'
*/

# Natural language interface for construction cost benchmarking

#!/usr/bin/env python3
"""
cost_query_app.py
Natural language interface for construction cost benchmarking.
Run with: python cost_query_app.py
Access at: http://localhost:5000
"""
import os
import json
import pandas as pd
from datetime import datetime
from flask import Flask, request, jsonify, render_template_string
from openai import OpenAI
from dotenv import load_dotenv

load_dotenv()

app = Flask(__name__)
client = OpenAI(api_key=os.getenv('OPENAI_API_KEY'))

DATA_DIR = os.path.join(os.path.dirname(__file__), '..', 'data')

def load_context_data():
    """Load current data summaries for LLM context."""
    context = {}
    
    # Load BLS PPI summary
    ppi_path = os.path.join(DATA_DIR, 'bls_ppi', 'bls_ppi_consolidated.csv')
    if os.path.exists(ppi_path):
        df_ppi = pd.read_csv(ppi_path)
        # Get latest values for each series
        latest_ppi = df_ppi.sort_values('Date').groupby('MaterialName').tail(1)
        context['ppi_summary'] = latest_ppi[['MaterialName', 'Date', 'IndexValue', 'PctChange12Month']].to_dict('records')
    
    # Load client cost summary (from ProEst export)
    client_path = os.path.join(DATA_DIR, 'proest_exports', 'cost_summary.csv')
    if os.path.exists(client_path):
        df_client = pd.read_csv(client_path)
        context['client_costs'] = df_client.head(50).to_dict('records')  # Top 50 materials
    
    # Load RSMeans benchmarks
    rsmeans_path = os.path.join(DATA_DIR, 'rsmeans_exports', 'rsmeans_current.csv')
    if os.path.exists(rsmeans_path):
        df_rsmeans = pd.read_csv(rsmeans_path)
        context['rsmeans_benchmarks'] = df_rsmeans.head(50).to_dict('records')
    
    return context

def build_messages(user_query, context_data):
    """Build the message array with data context."""
    context_str = json.dumps(context_data, indent=2, default=str)
    
    # Truncate context if too large (keep under 100K chars for token efficiency)
    if len(context_str) > 100000:
        context_str = context_str[:100000] + '\n... [data truncated for length]'
    
    messages = [
        {'role': 'system', 'content': SYSTEM_PROMPT},
        {'role': 'user', 'content': f"""Here is the current cost benchmarking data:\n\n{context_str}\n\nEstimator's question: {user_query}"""}
    ]
    return messages

HTML_TEMPLATE = """
<!DOCTYPE html>
<html>
<head>
    <title>Cost Benchmarking Assistant</title>
    <style>
        body { font-family: 'Segoe UI', Arial, sans-serif; max-width: 900px; margin: 0 auto; padding: 20px; background: #f5f5f5; }
        h1 { color: #1a472a; border-bottom: 3px solid #f59e0b; padding-bottom: 10px; }
        .query-box { background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); margin-bottom: 20px; }
        textarea { width: 100%; height: 80px; font-size: 16px; padding: 10px; border: 2px solid #ddd; border-radius: 4px; resize: vertical; }
        textarea:focus { border-color: #f59e0b; outline: none; }
        button { background: #1a472a; color: white; padding: 12px 24px; font-size: 16px; border: none; border-radius: 4px; cursor: pointer; margin-top: 10px; }
        button:hover { background: #2d6a3e; }
        .response { background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); white-space: pre-wrap; line-height: 1.6; }
        .examples { color: #666; font-size: 14px; margin-top: 10px; }
        .examples span { background: #e8f5e9; padding: 2px 8px; border-radius: 3px; cursor: pointer; display: inline-block; margin: 2px; }
        .loading { display: none; color: #f59e0b; font-weight: bold; }
    </style>
</head>
<body>
    <h1>&#128200; Cost Benchmarking Assistant</h1>
    <div class='query-box'>
        <form id='queryForm'>
            <textarea id='query' placeholder='Ask about material costs, benchmarks, or market trends...'></textarea>
            <button type='submit'>Ask</button>
            <span class='loading' id='loading'>Analyzing cost data...</span>
        </form>
        <div class='examples'>
            <p>Try asking:</p>
            <span onclick="document.getElementById('query').value=this.textContent">How does our concrete cost compare to RSMeans?</span>
            <span onclick="document.getElementById('query').value=this.textContent">What materials have increased the most in the past year?</span>
            <span onclick="document.getElementById('query').value=this.textContent">Show me our top cost anomalies</span>
            <span onclick="document.getElementById('query').value=this.textContent">Should we lock in lumber pricing now?</span>
        </div>
    </div>
    <div class='response' id='response'></div>
    <script>
        document.getElementById('queryForm').addEventListener('submit', async (e) => {
            e.preventDefault();
            const query = document.getElementById('query').value;
            if (!query.trim()) return;
            document.getElementById('loading').style.display = 'inline';
            document.getElementById('response').textContent = '';
            try {
                const res = await fetch('/api/query', {
                    method: 'POST',
                    headers: {'Content-Type': 'application/json'},
                    body: JSON.stringify({query: query})
                });
                const data = await res.json();
                document.getElementById('response').textContent = data.answer || data.error;
            } catch(err) {
                document.getElementById('response').textContent = 'Error: ' + err.message;
            }
            document.getElementById('loading').style.display = 'none';
        });
    </script>
</body>
</html>
"""

@app.route('/')
def index():
    return render_template_string(HTML_TEMPLATE)

@app.route('/api/query', methods=['POST'])
def query():
    user_query = request.json.get('query', '')
    if not user_query.strip():
        return jsonify({'error': 'Please enter a question.'}), 400
    
    try:
        context_data = load_context_data()
        messages = build_messages(user_query, context_data)
        
        response = client.chat.completions.create(
            model='gpt-5.4-mini',
            messages=messages,
            temperature=0.3,  # Low temperature for factual accuracy
            max_tokens=2000,
        )
        
        answer = response.choices[0].message.content
        
        # Log query for analytics
        with open(os.path.join(DATA_DIR, '..', 'logs', 'queries.log'), 'a') as f:
            f.write(json.dumps({
                'timestamp': datetime.now().isoformat(),
                'query': user_query,
                'tokens_used': response.usage.total_tokens,
                'model': response.model
            }) + '\n')
        
        return jsonify({'answer': answer, 'tokens_used': response.usage.total_tokens})
    
    except Exception as e:
        return jsonify({'error': f'Error processing query: {str(e)}'}), 500

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000, debug=False)

# Daily pipeline health monitoring for the cost benchmarking system.

#!/usr/bin/env python3
"""
health_check.py
Daily pipeline health monitoring for the cost benchmarking system.
Run daily at 8:00 AM after Power BI scheduled refresh.
"""
import os
import smtplib
import json
import logging
from datetime import datetime, timedelta
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from dotenv import load_dotenv

load_dotenv()
logging.basicConfig(
    filename=os.path.join(os.path.dirname(__file__), '..', 'logs', 'health_check.log'),
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

DATA_DIR = os.path.join(os.path.dirname(__file__), '..', 'data')

# Configuration
ALERT_EMAIL_TO = os.getenv('ALERT_EMAIL_TO', 'msp-alerts@yourmsp.com')
ALERT_EMAIL_FROM = os.getenv('ALERT_EMAIL_FROM', 'monitoring@yourmsp.com')
SMTP_SERVER = os.getenv('SMTP_SERVER', 'smtp.office365.com')
SMTP_PORT = int(os.getenv('SMTP_PORT', '587'))
SMTP_USER = os.getenv('SMTP_USER', '')
SMTP_PASS = os.getenv('SMTP_PASS', '')
CLIENT_NAME = os.getenv('CLIENT_NAME', 'Construction Client')

def check_file_freshness(filepath, max_age_days, description):
    """Check if a file exists and was modified within the expected timeframe."""
    if not os.path.exists(filepath):
        return {'status': 'FAIL', 'message': f'{description}: File not found at {filepath}'}
    
    mod_time = datetime.fromtimestamp(os.path.getmtime(filepath))
    age = datetime.now() - mod_time
    
    if age > timedelta(days=max_age_days):
        return {
            'status': 'WARN',
            'message': f'{description}: File is {age.days} days old (threshold: {max_age_days} days). Last modified: {mod_time.strftime("%Y-%m-%d %H:%M")}'
        }
    
    # Check file isn't empty
    if os.path.getsize(filepath) < 100:  # Less than 100 bytes is suspicious
        return {'status': 'WARN', 'message': f'{description}: File appears empty ({os.path.getsize(filepath)} bytes)'}
    
    return {
        'status': 'OK',
        'message': f'{description}: OK (modified {mod_time.strftime("%Y-%m-%d %H:%M")}, {os.path.getsize(filepath):,} bytes)'
    }

def check_csv_records(filepath, min_records, description):
    """Check that a CSV file has a minimum number of data rows."""
    if not os.path.exists(filepath):
        return {'status': 'FAIL', 'message': f'{description}: File not found'}
    
    try:
        with open(filepath, 'r') as f:
            line_count = sum(1 for _ in f) - 1  # Subtract header
        
        if line_count < min_records:
            return {
                'status': 'WARN',
                'message': f'{description}: Only {line_count} records (expected >= {min_records})'
            }
        return {
            'status': 'OK',
            'message': f'{description}: {line_count} records (threshold: {min_records})'
        }
    except Exception as e:
        return {'status': 'FAIL', 'message': f'{description}: Error reading file: {e}'}

def run_all_checks():
    """Run all health checks and return results."""
    results = []
    
    # Check BLS PPI data freshness (should be updated monthly)
    results.append(check_file_freshness(
        os.path.join(DATA_DIR, 'bls_ppi', 'bls_ppi_consolidated.csv'),
        max_age_days=35,  # Monthly + 5 day grace period
        description='BLS PPI Data'
    ))
    
    # Check BLS PPI record count
    results.append(check_csv_records(
        os.path.join(DATA_DIR, 'bls_ppi', 'bls_ppi_consolidated.csv'),
        min_records=100,  # 10 series x 12 months minimum
        description='BLS PPI Record Count'
    ))
    
    # Check ProEst export freshness (should be weekly)
    results.append(check_file_freshness(
        os.path.join(DATA_DIR, 'proest_exports', 'cost_summary.csv'),
        max_age_days=10,  # Weekly + 3 day grace period
        description='ProEst Bid Data Export'
    ))
    
    # Check RSMeans export freshness (should be quarterly)
    results.append(check_file_freshness(
        os.path.join(DATA_DIR, 'rsmeans_exports', 'rsmeans_current.csv'),
        max_age_days=100,  # Quarterly + 10 day grace period
        description='RSMeans Benchmark Data'
    ))
    
    # Check QuickBooks export freshness (should be monthly)
    results.append(check_file_freshness(
        os.path.join(DATA_DIR, 'proest_exports', 'qb_jobcost_export.csv'),
        max_age_days=35,
        description='QuickBooks Job Cost Data'
    ))
    
    # Check log files for recent errors
    log_dir = os.path.join(os.path.dirname(__file__), '..', 'logs')
    for log_name in ['bls_ppi.log', 'queries.log']:
        log_path = os.path.join(log_dir, log_name)
        if os.path.exists(log_path):
            try:
                with open(log_path, 'r') as f:
                    lines = f.readlines()[-20:]  # Last 20 lines
                    error_lines = [l for l in lines if 'ERROR' in l.upper()]
                    if error_lines:
                        results.append({
                            'status': 'WARN',
                            'message': f'Log {log_name}: {len(error_lines)} recent errors detected'
                        })
                    else:
                        results.append({'status': 'OK', 'message': f'Log {log_name}: No recent errors'})
            except Exception as e:
                results.append({'status': 'WARN', 'message': f'Log {log_name}: Cannot read - {e}'})
    
    return results

def send_alert(results):
    """Send email alert if any checks failed or warned."""
    failures = [r for r in results if r['status'] in ('FAIL', 'WARN')]
    
    if not failures:
        logging.info('All health checks passed. No alert needed.')
        return
    
    subject = f'[{CLIENT_NAME}] Cost Benchmarking Pipeline Alert - {len(failures)} issue(s)'
    
    body = f'Cost Benchmarking Pipeline Health Check - {datetime.now().strftime("%Y-%m-%d %H:%M")}\n'
    body += f'Client: {CLIENT_NAME}\n'
    body += '=' * 60 + '\n\n'
    
    body += 'ISSUES DETECTED:\n'
    for r in failures:
        body += f'  [{r["status"]}] {r["message"]}\n'
    
    body += '\nALL RESULTS:\n'
    for r in results:
        body += f'  [{r["status"]}] {r["message"]}\n'
    
    body += f'\n\nAction required: Please investigate and resolve the above issues.\n'
    body += f'Dashboard: https://app.powerbi.com (check {CLIENT_NAME} workspace)\n'
    
    try:
        msg = MIMEMultipart()
        msg['From'] = ALERT_EMAIL_FROM
        msg['To'] = ALERT_EMAIL_TO
        msg['Subject'] = subject
        msg.attach(MIMEText(body, 'plain'))
        
        with smtplib.SMTP(SMTP_SERVER, SMTP_PORT) as server:
            server.starttls()
            server.login(SMTP_USER, SMTP_PASS)
            server.send_message(msg)
        
        logging.info(f'Alert sent to {ALERT_EMAIL_TO}')
    except Exception as e:
        logging.error(f'Failed to send alert email: {e}')
        print(f'ALERT EMAIL FAILED: {e}')
        print(body)  # Print to stdout so Task Scheduler log captures it

def main():
    logging.info('Starting health check')
    results = run_all_checks()
    
    # Log all results
    for r in results:
        level = logging.WARNING if r['status'] != 'OK' else logging.INFO
        logging.log(level, f'[{r["status"]}] {r["message"]}')
    
    # Print summary
    ok_count = sum(1 for r in results if r['status'] == 'OK')
    warn_count = sum(1 for r in results if r['status'] == 'WARN')
    fail_count = sum(1 for r in results if r['status'] == 'FAIL')
    print(f'Health Check: {ok_count} OK, {warn_count} WARN, {fail_count} FAIL')
    
    # Send alert if issues found
    send_alert(results)
    
    logging.info('Health check complete')

if __name__ == '__main__':
    main()