Implementation Guide: Analyze turnover patterns, engagement survey data, and identify retention risk factors

# Export employee data from HRIS for quality assessment (example for BambooHR API)
curl -u 'API_KEY:x' 'https://api.bamboohr.com/api/gateway.php/COMPANY_DOMAIN/v1/reports/custom?format=csv&fields=id,firstName,lastName,hireDate,terminationDate,department,division,jobTitle,supervisor,payRate,employmentStatus' -o hr_data_export.csv

# Quick data quality check using Python (run on MSP technician laptop)
python3 -c "
import pandas as pd
df = pd.read_csv('hr_data_export.csv')
print('Total Records:', len(df))
print('\nNull counts per column:')
print(df.isnull().sum())
print('\nCompleteness percentage:')
print((1 - df.isnull().sum() / len(df)) * 100)
print('\nTermination records with missing reason:')
termed = df[df['terminationDate'].notna()]
print(f'{termed["terminationDate"].notna().sum()} terminations, {termed.isnull().sum().get("terminationReason", 0)} missing reason')
"

# Login to Azure CLI
az login

# Create resource group for HR Analytics
az group create --name rg-hr-analytics --location eastus

# Create Azure SQL Server
az sql server create \
  --name sql-hranalytics-CLIENT \
  --resource-group rg-hr-analytics \
  --location eastus \
  --admin-user hradmin \
  --admin-password 'GENERATE_STRONG_PASSWORD_HERE'

# Create serverless database (auto-pause after 60 min idle to save cost)
az sql db create \
  --resource-group rg-hr-analytics \
  --server sql-hranalytics-CLIENT \
  --name db-people-analytics \
  --edition GeneralPurpose \
  --family Gen5 \
  --capacity 2 \
  --compute-model Serverless \
  --auto-pause-delay 60 \
  --min-capacity 0.5 \
  --max-size 32GB

# Configure firewall to allow Azure services and MSP IP
az sql server firewall-rule create \
  --resource-group rg-hr-analytics \
  --server sql-hranalytics-CLIENT \
  --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-hr-analytics \
  --server sql-hranalytics-CLIENT \
  --name AllowMSPOffice \
  --start-ip-address MSP_PUBLIC_IP \
  --end-ip-address MSP_PUBLIC_IP

# Enable Azure AD authentication
az sql server ad-admin create \
  --resource-group rg-hr-analytics \
  --server sql-hranalytics-CLIENT \
  --display-name 'HR Analytics Admin' \
  --object-id AZURE_AD_GROUP_OBJECT_ID

-- HR star schema including dimension tables, fact tables, and date dimension
-- population

# Connect to Azure SQL using sqlcmd or Azure Data Studio
# Then execute the following DDL:

CREATE SCHEMA hr;
GO

-- Dimension: Employees (SCD Type 2 for historical tracking)
CREATE TABLE hr.dim_employees (
    employee_key INT IDENTITY(1,1) PRIMARY KEY,
    employee_id NVARCHAR(50) NOT NULL,
    first_name NVARCHAR(100),
    last_name NVARCHAR(100),
    email NVARCHAR(255),
    hire_date DATE NOT NULL,
    termination_date DATE NULL,
    termination_reason NVARCHAR(255) NULL,
    termination_type NVARCHAR(50) NULL, -- Voluntary, Involuntary, Retirement
    department NVARCHAR(100),
    division NVARCHAR(100),
    job_title NVARCHAR(200),
    job_level NVARCHAR(50),
    manager_employee_id NVARCHAR(50),
    location NVARCHAR(200),
    employment_type NVARCHAR(50), -- Full-time, Part-time, Contract
    tenure_months AS DATEDIFF(MONTH, hire_date, ISNULL(termination_date, GETDATE())),
    is_active BIT DEFAULT 1,
    effective_date DATE DEFAULT GETDATE(),
    end_date DATE NULL,
    is_current BIT DEFAULT 1,
    created_at DATETIME2 DEFAULT GETDATE(),
    updated_at DATETIME2 DEFAULT GETDATE()
);
GO

-- Dimension: Date (for time-series analysis)
CREATE TABLE hr.dim_date (
    date_key INT PRIMARY KEY, -- YYYYMMDD format
    full_date DATE NOT NULL,
    year INT,
    quarter INT,
    month INT,
    month_name NVARCHAR(20),
    week_of_year INT,
    day_of_week INT,
    is_weekend BIT,
    fiscal_year INT,
    fiscal_quarter INT
);
GO

-- Fact: Turnover Events
CREATE TABLE hr.fact_turnover (
    turnover_key INT IDENTITY(1,1) PRIMARY KEY,
    employee_key INT REFERENCES hr.dim_employees(employee_key),
    termination_date_key INT REFERENCES hr.dim_date(date_key),
    hire_date_key INT REFERENCES hr.dim_date(date_key),
    department NVARCHAR(100),
    manager_employee_id NVARCHAR(50),
    location NVARCHAR(200),
    tenure_at_termination_months INT,
    termination_reason NVARCHAR(255),
    termination_type NVARCHAR(50),
    was_regrettable BIT DEFAULT 0,
    last_performance_rating NVARCHAR(50),
    last_engagement_score DECIMAL(5,2),
    salary_at_termination DECIMAL(12,2),
    created_at DATETIME2 DEFAULT GETDATE()
);
GO

-- Fact: Engagement Survey Responses
CREATE TABLE hr.fact_engagement (
    response_key INT IDENTITY(1,1) PRIMARY KEY,
    employee_key INT REFERENCES hr.dim_employees(employee_key),
    survey_date_key INT REFERENCES hr.dim_date(date_key),
    survey_name NVARCHAR(200),
    overall_engagement_score DECIMAL(5,2),
    manager_effectiveness_score DECIMAL(5,2),
    career_development_score DECIMAL(5,2),
    compensation_satisfaction_score DECIMAL(5,2),
    work_life_balance_score DECIMAL(5,2),
    belonging_inclusion_score DECIMAL(5,2),
    recommendation_score DECIMAL(5,2), -- eNPS
    response_comments_sentiment NVARCHAR(20), -- Positive, Neutral, Negative
    created_at DATETIME2 DEFAULT GETDATE()
);
GO

-- Fact: Performance Reviews
CREATE TABLE hr.fact_performance (
    review_key INT IDENTITY(1,1) PRIMARY KEY,
    employee_key INT REFERENCES hr.dim_employees(employee_key),
    review_date_key INT REFERENCES hr.dim_date(date_key),
    review_period NVARCHAR(50),
    overall_rating NVARCHAR(50),
    numeric_rating DECIMAL(3,1),
    goals_met_percentage DECIMAL(5,2),
    manager_employee_id NVARCHAR(50),
    created_at DATETIME2 DEFAULT GETDATE()
);
GO

-- Fact: Compensation History
CREATE TABLE hr.fact_compensation (
    comp_key INT IDENTITY(1,1) PRIMARY KEY,
    employee_key INT REFERENCES hr.dim_employees(employee_key),
    effective_date_key INT REFERENCES hr.dim_date(date_key),
    base_salary DECIMAL(12,2),
    bonus_target_pct DECIMAL(5,2),
    total_comp DECIMAL(12,2),
    comp_ratio DECIMAL(5,2), -- ratio to market midpoint
    last_raise_pct DECIMAL(5,2),
    months_since_last_raise INT,
    created_at DATETIME2 DEFAULT GETDATE()
);
GO

-- Computed: Retention Risk Score (updated by ETL pipeline)
CREATE TABLE hr.retention_risk (
    risk_key INT IDENTITY(1,1) PRIMARY KEY,
    employee_key INT REFERENCES hr.dim_employees(employee_key),
    calculated_date DATE DEFAULT GETDATE(),
    risk_score DECIMAL(5,2), -- 0-100 scale
    risk_tier NVARCHAR(20), -- Low, Medium, High, Critical
    top_risk_factor_1 NVARCHAR(200),
    top_risk_factor_2 NVARCHAR(200),
    top_risk_factor_3 NVARCHAR(200),
    lattice_flight_risk NVARCHAR(20), -- from Lattice AI model
    model_version NVARCHAR(50),
    created_at DATETIME2 DEFAULT GETDATE()
);
GO

-- Populate date dimension (2020-2030)
DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate DATE = '2030-12-31';
WITH DateCTE AS (
    SELECT @StartDate AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM DateCTE WHERE dt < @EndDate
)
INSERT INTO hr.dim_date (date_key, full_date, year, quarter, month, month_name, week_of_year, day_of_week, is_weekend, fiscal_year, fiscal_quarter)
SELECT
    CAST(FORMAT(dt, 'yyyyMMdd') AS INT),
    dt,
    YEAR(dt), DATEPART(QUARTER, dt), MONTH(dt), DATENAME(MONTH, dt),
    DATEPART(WEEK, dt), DATEPART(WEEKDAY, dt),
    CASE WHEN DATEPART(WEEKDAY, dt) IN (1,7) THEN 1 ELSE 0 END,
    CASE WHEN MONTH(dt) >= 7 THEN YEAR(dt) + 1 ELSE YEAR(dt) END,
    CASE WHEN MONTH(dt) BETWEEN 7 AND 9 THEN 1 WHEN MONTH(dt) BETWEEN 10 AND 12 THEN 2 WHEN MONTH(dt) BETWEEN 1 AND 3 THEN 3 ELSE 4 END
FROM DateCTE OPTION (MAXRECURSION 5000);
GO

# 4. After client HR admin authorizes, exchange the auth code for an access token:
curl -X POST https://api.tryfinch.com/auth/token \
  -H 'Content-Type: application/json' \
  -d '{
    "client_id": "YOUR_CLIENT_ID",
    "client_secret": "YOUR_CLIENT_SECRET",
    "code": "AUTH_CODE_FROM_REDIRECT",
    "redirect_uri": "https://your-msp-domain.com/finch/callback"
  }'

# 5. Test the connection by pulling the company directory:
curl https://api.tryfinch.com/employer/directory \
  -H 'Authorization: Bearer ACCESS_TOKEN' \
  -H 'Finch-API-Version: 2020-09-17'

# 6. Pull individual employee details:
curl -X POST https://api.tryfinch.com/employer/individual \
  -H 'Authorization: Bearer ACCESS_TOKEN' \
  -H 'Content-Type: application/json' \
  -H 'Finch-API-Version: 2020-09-17' \
  -d '{"requests": [{"individual_id": "EMPLOYEE_ID_FROM_DIRECTORY"}]}'

# 7. Pull employment details (hire date, title, department, manager):
curl -X POST https://api.tryfinch.com/employer/employment \
  -H 'Authorization: Bearer ACCESS_TOKEN' \
  -H 'Content-Type: application/json' \
  -H 'Finch-API-Version: 2020-09-17' \
  -d '{"requests": [{"individual_id": "EMPLOYEE_ID_FROM_DIRECTORY"}]}'

# 1. Work with Lattice sales to provision tenant at https://lattice.com
# Request modules: Performance + Engagement + Analytics (+ Grow if budget allows)
# Lattice will provide: tenant URL (e.g., clientname.latticehq.com), admin credentials

# 2. Configure Azure AD SSO for Lattice:
# In Azure Portal > Entra ID > Enterprise Applications > New Application
# Search 'Lattice' in the gallery and add it
# Under Single Sign-On, select SAML
# Configure:
#   Identifier (Entity ID): https://clientname.latticehq.com
#   Reply URL (ACS): https://clientname.latticehq.com/auth/saml/callback
#   Sign-On URL: https://clientname.latticehq.com
#   Download the Federation Metadata XML and upload to Lattice admin panel

# 3. Configure SCIM Provisioning in Azure AD:
# In the Lattice Enterprise App > Provisioning
# Set Provisioning Mode to 'Automatic'
# Admin Credentials:
#   Tenant URL: https://clientname.latticehq.com/scim/v2
#   Secret Token: (generate from Lattice Admin > Integrations > SCIM)
# Test Connection, then configure attribute mappings:
#   Map: userPrincipalName -> userName
#   Map: displayName -> displayName
#   Map: givenName -> name.givenName
#   Map: surname -> name.familyName
#   Map: mail -> emails[type eq 'work'].value
#   Map: department -> urn:ietf:params:scim:schemas:extension:enterprise:2.0:User:department
#   Map: manager -> urn:ietf:params:scim:schemas:extension:enterprise:2.0:User:manager

# 4. Set provisioning scope to 'Sync only assigned users and groups'
# Create an Azure AD group 'Lattice-Users' and assign all employees
# Start provisioning and monitor the provisioning logs

# 5. In Lattice Admin Panel:
#   - Set company fiscal year and review cycles
#   - Configure department/team hierarchy matching HRIS structure
#   - Enable Analytics module under Admin > Modules
#   - Enable Engagement module and configure survey templates

# provision Function App, storage account, Key Vault secrets, and deploy

# Option A: Python Azure Function for ETL (recommended for cost efficiency)
# Create a new Azure Function App

az functionapp create \
  --resource-group rg-hr-analytics \
  --consumption-plan-location eastus \
  --runtime python \
  --runtime-version 3.11 \
  --functions-version 4 \
  --name func-hr-etl-CLIENT \
  --storage-account sthranalyticsCLIENT \
  --os-type Linux

# Create storage account first if not exists:
az storage account create \
  --name sthranalyticsclient \
  --resource-group rg-hr-analytics \
  --location eastus \
  --sku Standard_LRS

# Store secrets in Azure Key Vault:
az keyvault create --name kv-hr-CLIENT --resource-group rg-hr-analytics --location eastus
az keyvault secret set --vault-name kv-hr-CLIENT --name FinchAccessToken --value 'FINCH_TOKEN'
az keyvault secret set --vault-name kv-hr-CLIENT --name SqlConnectionString --value 'SERVER=sql-hranalytics-CLIENT.database.windows.net;DATABASE=db-people-analytics;UID=hradmin;PWD=PASSWORD'
az keyvault secret set --vault-name kv-hr-CLIENT --name LatticeApiKey --value 'LATTICE_API_KEY'

# Deploy the ETL function (see custom_ai_components for full source code)
# The function runs on a timer trigger: daily at 2 AM UTC
func azure functionapp publish func-hr-etl-CLIENT

-- Turnover Rate (annualized)
Turnover Rate = 
  DIVIDE(
    COUNTROWS(FILTER(fact_turnover, fact_turnover[termination_type] = 'Voluntary')),
    AVERAGE(COUNTROWS(FILTER(dim_employees, dim_employees[is_active] = 1))),
    0
  ) * (12 / DISTINCTCOUNT(dim_date[month]))

-- Rolling 12-Month Turnover
Rolling 12M Turnover = 
  VAR _EndDate = MAX(dim_date[full_date])
  VAR _StartDate = DATEADD(MAX(dim_date[full_date]), -12, MONTH)
  RETURN DIVIDE(
    CALCULATE(COUNTROWS(fact_turnover), DATESBETWEEN(dim_date[full_date], _StartDate, _EndDate)),
    CALCULATE(DISTINCTCOUNT(dim_employees[employee_key]), dim_employees[is_active] = 1),
    0)

-- Average Engagement Score
Avg Engagement = AVERAGE(fact_engagement[overall_engagement_score])

-- Retention Risk Distribution
High Risk Count = COUNTROWS(FILTER(retention_risk, retention_risk[risk_tier] = 'High' || retention_risk[risk_tier] = 'Critical'))
High Risk % = DIVIDE([High Risk Count], COUNTROWS(FILTER(dim_employees, dim_employees[is_active] = 1)), 0)

# This is deployed as part of the Azure Function App created in Step 7
# See custom_ai_components section for the complete Python implementation

# Deploy the risk scoring function:
cd hr-analytics-functions/
func azure functionapp publish func-hr-etl-CLIENT

# Verify the function is running:
az functionapp function list \
  --name func-hr-etl-CLIENT \
  --resource-group rg-hr-analytics \
  --output table

# Check function execution logs:
func azure functionapp logstream func-hr-etl-CLIENT

# Manually trigger a test run:
curl -X POST 'https://func-hr-etl-CLIENT.azurewebsites.net/api/calculate_retention_risk' \
  -H 'x-functions-key: FUNCTION_KEY' \
  -H 'Content-Type: application/json' \
  -d '{"test_mode": true}'

# Power Automate Flow 1: High Flight Risk Alert
# Trigger: Scheduled (daily at 6 AM after risk scores update)
# 
# Flow steps:
# 1. Initialize variable: RiskThreshold = 75
# 2. Execute SQL query against Azure SQL:
#    SELECT e.first_name, e.last_name, e.department, e.job_title,
#           e.manager_employee_id, r.risk_score, r.risk_tier,
#           r.top_risk_factor_1, r.top_risk_factor_2, r.top_risk_factor_3
#    FROM hr.retention_risk r
#    JOIN hr.dim_employees e ON r.employee_key = e.employee_key
#    WHERE r.risk_tier IN ('High', 'Critical')
#    AND r.calculated_date = CAST(GETDATE() AS DATE)
#    AND e.is_active = 1
# 3. Apply to each result:
#    a. Get manager email from Azure AD using manager_employee_id
#    b. Send adaptive card to manager via Teams:
#       Title: '⚠️ Retention Alert: [Employee Name]'
#       Body: Risk Score, Risk Tier, Top 3 Risk Factors
#       Action button: 'Schedule 1:1' (deep link to Outlook calendar)
#       Action button: 'View Full Profile' (deep link to Lattice)
#    c. Send summary email to HR Director with all high-risk employees

# Power Automate Flow 2: Engagement Score Drop Alert
# Trigger: When a new row is added to fact_engagement (via SQL connector)
# Condition: If overall_engagement_score < previous_score - 15% 
#   AND previous_score was in top 50th percentile
# Action: Send Teams notification to HRBP for that department

# Power Automate Flow 3: New Termination Alert
# Trigger: When Finch webhook fires for employment status change to 'terminated'
# Action: Log to fact_turnover table, send notification to HR Director,
#         trigger exit interview scheduling workflow

# Configuration:
# 1. Go to https://make.powerautomate.com
# 2. Create each flow using the Premium SQL Server connector
# 3. Test each flow with sample data before going live
# 4. Set error handling: configure 'Run after' on failure to send
#    error notification to MSP monitoring mailbox

# 1. FortiGate 40F Configuration (if installed)
# Access FortiGate admin console at https://192.168.1.99

# Create HR VLAN interface:
config system interface
    edit 'HR-Analytics-VLAN'
        set vdom root
        set type vlan
        set vlanid 120
        set interface internal
        set ip 10.120.1.1 255.255.255.0
        set allowaccess ping https ssh
    next
end

# Create address objects for SaaS platforms:
config firewall address
    edit 'Lattice-Cloud'
        set type fqdn
        set fqdn 'latticehq.com'
    next
    edit 'Azure-SQL'
        set type fqdn
        set fqdn 'sql-hranalytics-CLIENT.database.windows.net'
    next
    edit 'PowerBI-Service'
        set type fqdn
        set fqdn 'app.powerbi.com'
    next
    edit 'Finch-API'
        set type fqdn
        set fqdn 'api.tryfinch.com'
    next
end

# Create firewall policy allowing HR VLAN to SaaS only:
config firewall policy
    edit 100
        set name 'HR-Analytics-Outbound'
        set srcintf 'HR-Analytics-VLAN'
        set dstintf 'wan1'
        set srcaddr 'all'
        set dstaddr 'Lattice-Cloud' 'Azure-SQL' 'PowerBI-Service' 'Finch-API'
        set action accept
        set schedule always
        set service HTTPS
        set ssl-ssh-profile certificate-inspection
        set logtraffic all
    next
end

# 2. Azure SQL Security Configuration:
# Enable Advanced Threat Protection:
az sql db threat-policy update \
  --resource-group rg-hr-analytics \
  --server sql-hranalytics-CLIENT \
  --name db-people-analytics \
  --state Enabled \
  --email-addresses 'security@msp-domain.com'

# Enable auditing:
az sql server audit-policy update \
  --resource-group rg-hr-analytics \
  --name sql-hranalytics-CLIENT \
  --state Enabled \
  --storage-account sthranalyticsclient

# Check Lattice API returns survey data:
curl -X GET 'https://api.latticehq.com/v1/engagement/surveys/SURVEY_ID/results' \
  -H 'Authorization: Bearer LATTICE_API_KEY' \
  -H 'Content-Type: application/json'

# Manually trigger ETL function to pull survey data:
curl -X POST 'https://func-hr-etl-CLIENT.azurewebsites.net/api/sync_engagement_data' \
  -H 'x-functions-key: FUNCTION_KEY'

SELECT COUNT(*) as response_count FROM hr.fact_engagement WHERE survey_name LIKE '%Pilot%';

SELECT department, AVG(overall_engagement_score) as avg_score
FROM hr.fact_engagement e
JOIN hr.dim_employees d ON e.employee_key = d.employee_key
WHERE survey_name LIKE '%Pilot%'
GROUP BY department;

# Trigger retention risk recalculation:
curl -X POST 'https://func-hr-etl-CLIENT.azurewebsites.net/api/calculate_retention_risk' \
  -H 'x-functions-key: FUNCTION_KEY'

SELECT e.department, r.risk_tier, COUNT(*) as cnt
FROM hr.retention_risk r
JOIN hr.dim_employees e ON r.employee_key = e.employee_key
WHERE r.calculated_date = CAST(GETDATE() AS DATE)
GROUP BY e.department, r.risk_tier
ORDER BY e.department, r.risk_tier;

# Azure Function main entry point (Python 3.11, Timer trigger)

# Azure Function: sync_hris_data
# File: sync_hris_data/__init__.py
# Runtime: Python 3.11
# Trigger: Timer (daily at 2 AM UTC)
# Requirements: azure-functions, pyodbc, requests, azure-keyvault-secrets, azure-identity

import azure.functions as func
import pyodbc
import requests
import json
import logging
import os
from datetime import datetime, date
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

def main(mytimer: func.TimerRequest) -> None:
    logging.info('HRIS ETL sync started at %s', datetime.utcnow())
    
    # Retrieve secrets from Key Vault
    kv_url = os.environ['KEY_VAULT_URL']  # e.g., https://kv-hr-CLIENT.vault.azure.net/
    credential = DefaultAzureCredential()
    kv_client = SecretClient(vault_url=kv_url, credential=credential)
    
    finch_token = kv_client.get_secret('FinchAccessToken').value
    sql_conn_str = kv_client.get_secret('SqlConnectionString').value
    
    # Finch API base URL
    FINCH_BASE = 'https://api.tryfinch.com'
    FINCH_HEADERS = {
        'Authorization': f'Bearer {finch_token}',
        'Finch-API-Version': '2020-09-17',
        'Content-Type': 'application/json'
    }
    
    # Step 1: Pull company directory (all employees)
    logging.info('Pulling company directory from Finch...')
    directory = []
    has_more = True
    offset = 0
    while has_more:
        resp = requests.get(
            f'{FINCH_BASE}/employer/directory',
            headers=FINCH_HEADERS,
            params={'limit': 100, 'offset': offset}
        )
        resp.raise_for_status()
        data = resp.json()
        directory.extend(data.get('individuals', []))
        paging = data.get('paging', {})
        offset += paging.get('count', 0)
        has_more = offset < paging.get('total', 0)
    
    logging.info(f'Retrieved {len(directory)} employees from directory')
    
    # Step 2: Pull individual details in batches of 20
    individual_ids = [{'individual_id': emp['id']} for emp in directory]
    individuals = []
    for i in range(0, len(individual_ids), 20):
        batch = individual_ids[i:i+20]
        resp = requests.post(
            f'{FINCH_BASE}/employer/individual',
            headers=FINCH_HEADERS,
            json={'requests': batch}
        )
        resp.raise_for_status()
        individuals.extend(resp.json().get('responses', []))
    
    # Step 3: Pull employment details in batches of 20
    employments = []
    for i in range(0, len(individual_ids), 20):
        batch = individual_ids[i:i+20]
        resp = requests.post(
            f'{FINCH_BASE}/employer/employment',
            headers=FINCH_HEADERS,
            json={'requests': batch}
        )
        resp.raise_for_status()
        employments.extend(resp.json().get('responses', []))
    
    # Step 4: Merge and load into Azure SQL
    conn = pyodbc.connect(sql_conn_str)
    cursor = conn.cursor()
    
    upsert_count = 0
    for idx, emp_dir in enumerate(directory):
        try:
            emp_id = emp_dir['id']
            ind = individuals[idx].get('body', {}) if idx < len(individuals) else {}
            empl = employments[idx].get('body', {}) if idx < len(employments) else {}
            
            first_name = ind.get('first_name', '')
            last_name = ind.get('last_name', '')
            email_list = ind.get('emails', [])
            email = email_list[0].get('data', '') if email_list else ''
            
            department = emp_dir.get('department', {}).get('name', '') if isinstance(emp_dir.get('department'), dict) else str(emp_dir.get('department', ''))
            manager_id = emp_dir.get('manager', {}).get('id', '') if isinstance(emp_dir.get('manager'), dict) else ''
            
            hire_date = empl.get('start_date', None)
            end_date = empl.get('end_date', None)
            title = empl.get('title', '')
            is_active = 1 if empl.get('is_active', True) else 0
            
            income = empl.get('income', {})
            if income:
                amount = income.get('amount', 0)
                unit = income.get('unit', 'yearly')
                currency = income.get('currency', 'usd')
                # Normalize to annual
                if unit == 'monthly':
                    annual_salary = (amount or 0) / 100 * 12  # Finch returns cents
                elif unit == 'hourly':
                    annual_salary = (amount or 0) / 100 * 2080
                else:
                    annual_salary = (amount or 0) / 100
            else:
                annual_salary = None
            
            location = empl.get('location', {})
            location_str = ''
            if location:
                city = location.get('city', '')
                state = location.get('state', '')
                location_str = f'{city}, {state}'.strip(', ')
            
            employment_type = empl.get('employment', {}).get('type', 'full_time') if isinstance(empl.get('employment'), dict) else 'full_time'
            
            # Upsert logic: check if employee exists, update if changed, insert if new
            cursor.execute(
                '''MERGE hr.dim_employees AS target
                USING (SELECT ? AS employee_id) AS source
                ON target.employee_id = source.employee_id AND target.is_current = 1
                WHEN MATCHED AND (
                    target.department != ? OR target.job_title != ? OR 
                    target.manager_employee_id != ? OR target.is_active != ?
                ) THEN UPDATE SET
                    end_date = CAST(GETDATE() AS DATE),
                    is_current = 0,
                    updated_at = GETDATE()
                WHEN NOT MATCHED THEN INSERT (
                    employee_id, first_name, last_name, email, hire_date,
                    termination_date, department, job_title, manager_employee_id,
                    location, employment_type, is_active, effective_date, is_current
                ) VALUES (
                    ?, ?, ?, ?, ?,
                    ?, ?, ?, ?,
                    ?, ?, ?, CAST(GETDATE() AS DATE), 1
                );''',
                emp_id, department, title, manager_id, is_active,
                emp_id, first_name, last_name, email, hire_date,
                end_date, department, title, manager_id,
                location_str, employment_type, is_active
            )
            
            # For changed records, insert new current version
            if cursor.rowcount > 0:
                cursor.execute(
                    '''IF NOT EXISTS (SELECT 1 FROM hr.dim_employees WHERE employee_id = ? AND is_current = 1)
                    INSERT INTO hr.dim_employees (
                        employee_id, first_name, last_name, email, hire_date,
                        termination_date, department, job_title, manager_employee_id,
                        location, employment_type, is_active, effective_date, is_current
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CAST(GETDATE() AS DATE), 1)''',
                    emp_id,
                    emp_id, first_name, last_name, email, hire_date,
                    end_date, department, title, manager_id,
                    location_str, employment_type, is_active
                )
            
            # Upsert compensation data
            if annual_salary:
                cursor.execute(
                    '''MERGE hr.fact_compensation AS target
                    USING (
                        SELECT employee_key FROM hr.dim_employees 
                        WHERE employee_id = ? AND is_current = 1
                    ) AS source ON target.employee_key = source.employee_key
                        AND target.effective_date_key = CAST(FORMAT(GETDATE(), 'yyyyMMdd') AS INT)
                    WHEN NOT MATCHED THEN INSERT (
                        employee_key, effective_date_key, base_salary, total_comp
                    ) VALUES (
                        source.employee_key,
                        CAST(FORMAT(GETDATE(), 'yyyyMMdd') AS INT),
                        ?, ?
                    );''',
                    emp_id, annual_salary, annual_salary
                )
            
            upsert_count += 1
            
        except Exception as e:
            logging.error(f'Error processing employee {emp_dir.get("id", "unknown")}: {str(e)}')
            continue
    
    conn.commit()
    cursor.close()
    conn.close()
    
    logging.info(f'ETL complete. Processed {upsert_count}/{len(directory)} employees.')

# Timer trigger binding configuration (daily at 2 AM UTC)

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

azure-functions
pyodbc
requests
azure-keyvault-secrets
azure-identity

# Python 3.11, Timer trigger daily at 2:30 AM UTC

# Azure Function: sync_engagement_data
# File: sync_engagement_data/__init__.py
# Runtime: Python 3.11
# Trigger: Timer (daily at 2:30 AM UTC, 30 min after HRIS sync)

import azure.functions as func
import pyodbc
import requests
import logging
import os
from datetime import datetime
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

def main(mytimer: func.TimerRequest) -> None:
    logging.info('Lattice engagement sync started at %s', datetime.utcnow())
    
    kv_url = os.environ['KEY_VAULT_URL']
    credential = DefaultAzureCredential()
    kv_client = SecretClient(vault_url=kv_url, credential=credential)
    
    lattice_key = kv_client.get_secret('LatticeApiKey').value
    sql_conn_str = kv_client.get_secret('SqlConnectionString').value
    
    LATTICE_BASE = 'https://api.latticehq.com/v1'
    LATTICE_HEADERS = {
        'Authorization': f'Bearer {lattice_key}',
        'Content-Type': 'application/json'
    }
    
    conn = pyodbc.connect(sql_conn_str)
    cursor = conn.cursor()
    
    # Pull engagement survey results
    # Note: Lattice API endpoints may vary; consult current docs
    try:
        # Get list of surveys
        resp = requests.get(f'{LATTICE_BASE}/engagement/surveys', headers=LATTICE_HEADERS)
        resp.raise_for_status()
        surveys = resp.json().get('data', [])
        
        for survey in surveys:
            survey_id = survey['id']
            survey_name = survey.get('name', '')
            survey_date = survey.get('closed_at', survey.get('created_at', ''))
            
            if not survey_date:
                continue
            
            # Get aggregated results by employee (if accessible via API)
            # Lattice may return anonymized/aggregated data depending on plan
            results_resp = requests.get(
                f'{LATTICE_BASE}/engagement/surveys/{survey_id}/results',
                headers=LATTICE_HEADERS
            )
            
            if results_resp.status_code == 200:
                results = results_resp.json().get('data', {})
                
                # Process dimension-level scores
                dimensions = results.get('dimensions', [])
                for dim in dimensions:
                    dim_name = dim.get('name', '')
                    segments = dim.get('segments', [])
                    
                    for segment in segments:
                        segment_type = segment.get('type', '')  # department, location, etc.
                        segment_value = segment.get('value', '')
                        avg_score = segment.get('average_score', 0)
                        response_count = segment.get('response_count', 0)
                        
                        # Store segment-level engagement data
                        cursor.execute(
                            '''INSERT INTO hr.fact_engagement_segments (
                                survey_name, survey_date, dimension_name,
                                segment_type, segment_value, avg_score, response_count
                            ) VALUES (?, ?, ?, ?, ?, ?, ?)''',
                            survey_name,
                            survey_date[:10] if survey_date else None,
                            dim_name, segment_type, segment_value,
                            avg_score, response_count
                        )
                
                # Process overall scores
                overall = results.get('overall', {})
                enps = results.get('enps', {})
                
                logging.info(
                    f'Survey "{survey_name}": Overall score={overall.get("score", "N/A")}, '
                    f'eNPS={enps.get("score", "N/A")}, '
                    f'Response rate={results.get("response_rate", "N/A")}'
                )
        
        conn.commit()
        logging.info('Engagement data sync complete.')
        
    except requests.exceptions.HTTPError as e:
        logging.error(f'Lattice API error: {e}')
        logging.error(f'Response: {e.response.text if e.response else "No response"}')
    except Exception as e:
        logging.error(f'Engagement sync error: {str(e)}')
    finally:
        cursor.close()
        conn.close()

# function.json:
# {
#   "scriptFile": "__init__.py",
#   "bindings": [
#     {
#       "name": "mytimer",
#       "type": "timerTrigger",
#       "direction": "in",
#       "schedule": "0 30 2 * * *"
#     }
#   ]
# }

# File: calculate_retention_risk/__init__.py — Runtime: Python 3.11 —
# Trigger: Timer (daily at 3 AM UTC) or HTTP (for manual trigger)

# Azure Function: calculate_retention_risk
# File: calculate_retention_risk/__init__.py
# Runtime: Python 3.11
# Trigger: Timer (daily at 3 AM UTC) or HTTP (for manual trigger)

import azure.functions as func
import pyodbc
import logging
import os
import json
from datetime import datetime, date
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

# ============================================================
# RETENTION RISK SCORING MODEL v1.0
# ============================================================
# Methodology: Weighted composite scoring
# Total possible score: 100 (highest risk)
# Each factor contributes a weighted sub-score
# 
# Factor Weights (must sum to 100):
# - Tenure Risk Zone:           20 points
# - Engagement Score Trend:     25 points
# - Compensation Position:      15 points
# - Manager Stability:          10 points
# - Performance Trajectory:     10 points
# - Time Since Last Promotion:  10 points
# - Recent Organizational Change: 5 points
# - Work-Life Balance Signal:    5 points
# ============================================================

MODEL_VERSION = 'v1.0-weighted'

def calculate_tenure_risk(tenure_months: int) -> tuple:
    """Tenure-based flight risk. Research shows highest voluntary turnover
    occurs at 1-2 years and again at 4-5 years (post-vesting)."""
    if tenure_months is None:
        return 10, 'Unknown tenure'
    elif tenure_months < 6:
        return 8, 'New hire (< 6 months) - still in onboarding'
    elif 6 <= tenure_months < 12:
        return 14, 'Approaching 1-year mark - common early departure window'
    elif 12 <= tenure_months < 24:
        return 20, 'In 1-2 year high-risk window - peak voluntary turnover zone'
    elif 24 <= tenure_months < 36:
        return 14, '2-3 years tenure - moderate risk, seeking growth'
    elif 36 <= tenure_months < 48:
        return 10, '3-4 years - lower risk if engaged'
    elif 48 <= tenure_months < 60:
        return 16, '4-5 year mark - equity vesting cliff / career reassessment'
    elif 60 <= tenure_months < 84:
        return 8, '5-7 years - established, lower flight risk'
    else:
        return 4, '7+ years - deeply embedded, lowest tenure risk'

def calculate_engagement_risk(current_score: float, previous_score: float, 
                                company_avg: float) -> tuple:
    """Engagement survey score analysis. Both absolute level and trend matter."""
    if current_score is None:
        return 15, 'No engagement data available - unable to assess sentiment'
    
    risk = 0
    factors = []
    
    # Absolute score assessment (0-5 scale typical for Lattice)
    if current_score < 2.5:
        risk += 20
        factors.append('Very low engagement score (below 2.5/5)')
    elif current_score < 3.0:
        risk += 15
        factors.append('Low engagement score (below 3.0/5)')
    elif current_score < 3.5:
        risk += 8
        factors.append('Below-average engagement score')
    elif current_score >= 4.0:
        risk += 0
        factors.append('Strong engagement score')
    else:
        risk += 3
    
    # Trend analysis (drop from previous survey)
    if previous_score and current_score:
        change = current_score - previous_score
        if change <= -0.8:
            risk += 5
            factors.append(f'Significant engagement drop ({change:.1f} points)')
        elif change <= -0.4:
            risk += 3
            factors.append(f'Moderate engagement decline ({change:.1f} points)')
    
    # Below company average
    if company_avg and current_score < (company_avg - 0.5):
        risk += 2
        factors.append('Significantly below company average engagement')
    
    return min(risk, 25), '; '.join(factors) if factors else 'Engagement within normal range'

def calculate_compensation_risk(comp_ratio: float, months_since_raise: int,
                                  salary: float) -> tuple:
    """Compensation position relative to market and internal equity."""
    risk = 0
    factors = []
    
    if comp_ratio is not None:
        if comp_ratio < 0.80:
            risk += 12
            factors.append(f'Significantly below market (comp ratio: {comp_ratio:.0%})')
        elif comp_ratio < 0.90:
            risk += 8
            factors.append(f'Below market midpoint (comp ratio: {comp_ratio:.0%})')
        elif comp_ratio < 0.95:
            risk += 4
            factors.append(f'Slightly below market (comp ratio: {comp_ratio:.0%})')
        else:
            risk += 0
    else:
        risk += 5
        factors.append('Compensation market data unavailable')
    
    if months_since_raise is not None:
        if months_since_raise > 24:
            risk += 3
            factors.append(f'No raise in {months_since_raise} months')
        elif months_since_raise > 18:
            risk += 2
            factors.append(f'{months_since_raise} months since last raise')
    
    return min(risk, 15), '; '.join(factors) if factors else 'Compensation position adequate'

def calculate_manager_risk(manager_tenure_months: int, manager_turnover_count: int,
                            manager_engagement_avg: float) -> tuple:
    """Manager stability and effectiveness signals."""
    risk = 0
    factors = []
    
    if manager_tenure_months is not None and manager_tenure_months < 6:
        risk += 4
        factors.append('New manager (< 6 months) - transition risk')
    
    if manager_turnover_count is not None and manager_turnover_count >= 3:
        risk += 4
        factors.append(f'Manager has had {manager_turnover_count} reports leave in past 12 months')
    elif manager_turnover_count is not None and manager_turnover_count >= 2:
        risk += 2
        factors.append(f'Manager has had {manager_turnover_count} reports leave in past 12 months')
    
    if manager_engagement_avg is not None and manager_engagement_avg < 3.0:
        risk += 2
        factors.append(f'Team average engagement is low ({manager_engagement_avg:.1f}/5)')
    
    return min(risk, 10), '; '.join(factors) if factors else 'Manager stability adequate'

def calculate_performance_risk(current_rating: float, previous_rating: float) -> tuple:
    """Performance trajectory — both high performers (poached) and declining performers (disengaged) are risks."""
    risk = 0
    factors = []
    
    if current_rating is None:
        return 5, 'No performance data available'
    
    # High performers are retention risks (external demand)
    if current_rating >= 4.5:
        risk += 6
        factors.append('Top performer - high external market demand')
    elif current_rating >= 4.0:
        risk += 3
        factors.append('Strong performer - moderate external demand')
    
    # Declining performance signals disengagement
    if previous_rating and current_rating < previous_rating - 0.5:
        risk += 4
        factors.append(f'Performance declined from {previous_rating:.1f} to {current_rating:.1f}')
    
    return min(risk, 10), '; '.join(factors) if factors else 'Performance trajectory stable'

def calculate_promotion_risk(months_since_promotion: int, job_level: str) -> tuple:
    """Time since last promotion — career stagnation is a top driver of turnover."""
    if months_since_promotion is None:
        return 5, 'Promotion history unavailable'
    
    if months_since_promotion > 36:
        return 8, f'No promotion in {months_since_promotion} months - potential career stagnation'
    elif months_since_promotion > 24:
        return 5, f'{months_since_promotion} months since last promotion'
    elif months_since_promotion > 18:
        return 3, f'{months_since_promotion} months since last promotion'
    else:
        return 0, 'Recent promotion - career progression on track'

def calculate_org_change_risk(recent_reorg: bool, team_size_change_pct: float) -> tuple:
    """Recent organizational disruption signals."""
    risk = 0
    factors = []
    
    if recent_reorg:
        risk += 3
        factors.append('Department underwent recent reorganization')
    
    if team_size_change_pct is not None and abs(team_size_change_pct) > 25:
        risk += 2
        factors.append(f'Team size changed by {team_size_change_pct:.0f}% recently')
    
    return min(risk, 5), '; '.join(factors) if factors else 'No significant org disruption'

def assign_risk_tier(score: float) -> str:
    if score >= 75:
        return 'Critical'
    elif score >= 55:
        return 'High'
    elif score >= 35:
        return 'Medium'
    else:
        return 'Low'

def get_top_factors(factor_scores: list, n: int = 3) -> list:
    """Return top N contributing factors sorted by risk contribution."""
    sorted_factors = sorted(factor_scores, key=lambda x: x[0], reverse=True)
    return [f[1] for f in sorted_factors[:n] if f[0] > 0]


def main(req: func.HttpRequest = None, mytimer: func.TimerRequest = None) -> str:
    logging.info('Retention risk calculation started at %s', datetime.utcnow())
    
    kv_url = os.environ['KEY_VAULT_URL']
    credential = DefaultAzureCredential()
    kv_client = SecretClient(vault_url=kv_url, credential=credential)
    sql_conn_str = kv_client.get_secret('SqlConnectionString').value
    
    conn = pyodbc.connect(sql_conn_str)
    cursor = conn.cursor()
    
    # Get company-wide engagement average for relative comparison
    cursor.execute('''
        SELECT AVG(overall_engagement_score) 
        FROM hr.fact_engagement e
        JOIN (
            SELECT employee_key, MAX(survey_date_key) as latest_survey
            FROM hr.fact_engagement GROUP BY employee_key
        ) latest ON e.employee_key = latest.employee_key 
            AND e.survey_date_key = latest.latest_survey
    ''')
    company_avg_engagement = cursor.fetchone()[0] or 3.5
    
    # Pull all active employees with their latest data points
    cursor.execute('''
        SELECT 
            e.employee_key,
            e.employee_id,
            e.department,
            e.job_title,
            e.job_level,
            e.tenure_months,
            e.manager_employee_id,
            -- Latest engagement
            eng_latest.overall_engagement_score as current_engagement,
            eng_prev.overall_engagement_score as previous_engagement,
            -- Latest performance
            perf_latest.numeric_rating as current_performance,
            perf_prev.numeric_rating as previous_performance,
            -- Compensation
            comp.comp_ratio,
            comp.months_since_last_raise,
            comp.base_salary,
            -- Manager metrics
            mgr_emp.tenure_months as manager_tenure,
            mgr_turnover.turnover_count as manager_turnover_12m,
            mgr_eng.avg_team_engagement as manager_team_engagement
        FROM hr.dim_employees e
        LEFT JOIN (
            SELECT employee_key, overall_engagement_score,
                   ROW_NUMBER() OVER (PARTITION BY employee_key ORDER BY survey_date_key DESC) as rn
            FROM hr.fact_engagement
        ) eng_latest ON e.employee_key = eng_latest.employee_key AND eng_latest.rn = 1
        LEFT JOIN (
            SELECT employee_key, overall_engagement_score,
                   ROW_NUMBER() OVER (PARTITION BY employee_key ORDER BY survey_date_key DESC) as rn
            FROM hr.fact_engagement
        ) eng_prev ON e.employee_key = eng_prev.employee_key AND eng_prev.rn = 2
        LEFT JOIN (
            SELECT employee_key, numeric_rating,
                   ROW_NUMBER() OVER (PARTITION BY employee_key ORDER BY review_date_key DESC) as rn
            FROM hr.fact_performance
        ) perf_latest ON e.employee_key = perf_latest.employee_key AND perf_latest.rn = 1
        LEFT JOIN (
            SELECT employee_key, numeric_rating,
                   ROW_NUMBER() OVER (PARTITION BY employee_key ORDER BY review_date_key DESC) as rn
            FROM hr.fact_performance
        ) perf_prev ON e.employee_key = perf_prev.employee_key AND perf_prev.rn = 2
        LEFT JOIN (
            SELECT employee_key, comp_ratio, months_since_last_raise, base_salary,
                   ROW_NUMBER() OVER (PARTITION BY employee_key ORDER BY effective_date_key DESC) as rn
            FROM hr.fact_compensation
        ) comp ON e.employee_key = comp.employee_key AND comp.rn = 1
        LEFT JOIN hr.dim_employees mgr_emp 
            ON e.manager_employee_id = mgr_emp.employee_id AND mgr_emp.is_current = 1
        LEFT JOIN (
            SELECT t.manager_employee_id, COUNT(*) as turnover_count
            FROM hr.fact_turnover t
            WHERE t.termination_date_key >= CAST(FORMAT(DATEADD(MONTH, -12, GETDATE()), 'yyyyMMdd') AS INT)
            AND t.termination_type = 'Voluntary'
            GROUP BY t.manager_employee_id
        ) mgr_turnover ON e.manager_employee_id = mgr_turnover.manager_employee_id
        LEFT JOIN (
            SELECT d.manager_employee_id, AVG(eng.overall_engagement_score) as avg_team_engagement
            FROM hr.dim_employees d
            JOIN (
                SELECT employee_key, overall_engagement_score,
                       ROW_NUMBER() OVER (PARTITION BY employee_key ORDER BY survey_date_key DESC) as rn
                FROM hr.fact_engagement
            ) eng ON d.employee_key = eng.employee_key AND eng.rn = 1
            WHERE d.is_current = 1 AND d.is_active = 1
            GROUP BY d.manager_employee_id
        ) mgr_eng ON e.manager_employee_id = mgr_eng.manager_employee_id
        WHERE e.is_current = 1 AND e.is_active = 1
    ''')
    
    employees = cursor.fetchall()
    columns = [desc[0] for desc in cursor.description]
    
    processed = 0
    for row in employees:
        emp = dict(zip(columns, row))
        
        # Calculate each risk factor
        tenure_score, tenure_factor = calculate_tenure_risk(emp['tenure_months'])
        engagement_score, engagement_factor = calculate_engagement_risk(
            emp['current_engagement'], emp['previous_engagement'], company_avg_engagement
        )
        comp_score, comp_factor = calculate_compensation_risk(
            emp['comp_ratio'], emp['months_since_last_raise'], emp['base_salary']
        )
        manager_score, manager_factor = calculate_manager_risk(
            emp['manager_tenure'], emp['manager_turnover_12m'], emp['manager_team_engagement']
        )
        performance_score, performance_factor = calculate_performance_risk(
            emp['current_performance'], emp['previous_performance']
        )
        # Promotion and org change would come from additional data sources
        # Using defaults for initial deployment
        promotion_score, promotion_factor = 5, 'Promotion data not yet available'
        org_change_score, org_change_factor = 2, 'Org change data not yet available'
        wlb_score = 3  # Default until WLB survey dimension is populated
        
        # Calculate composite score
        total_score = (
            tenure_score + engagement_score + comp_score + 
            manager_score + performance_score + promotion_score + 
            org_change_score + wlb_score
        )
        total_score = min(total_score, 100)
        
        risk_tier = assign_risk_tier(total_score)
        
        # Get top 3 risk factors
        factor_scores = [
            (tenure_score, tenure_factor),
            (engagement_score, engagement_factor),
            (comp_score, comp_factor),
            (manager_score, manager_factor),
            (performance_score, performance_factor),
            (promotion_score, promotion_factor),
            (org_change_score, org_change_factor),
        ]
        top_factors = get_top_factors(factor_scores, 3)
        
        # Upsert risk score
        cursor.execute(
            '''INSERT INTO hr.retention_risk (
                employee_key, calculated_date, risk_score, risk_tier,
                top_risk_factor_1, top_risk_factor_2, top_risk_factor_3,
                lattice_flight_risk, model_version
            ) VALUES (?, CAST(GETDATE() AS DATE), ?, ?, ?, ?, ?, ?, ?)''',
            emp['employee_key'],
            round(total_score, 2),
            risk_tier,
            top_factors[0] if len(top_factors) > 0 else None,
            top_factors[1] if len(top_factors) > 1 else None,
            top_factors[2] if len(top_factors) > 2 else None,
            None,  # Populated when Lattice flight risk API is available
            MODEL_VERSION
        )
        processed += 1
    
    conn.commit()
    
    # Log summary statistics
    cursor.execute('''
        SELECT risk_tier, COUNT(*) as cnt 
        FROM hr.retention_risk 
        WHERE calculated_date = CAST(GETDATE() AS DATE)
        GROUP BY risk_tier
    ''')
    summary = {row[0]: row[1] for row in cursor.fetchall()}
    
    cursor.close()
    conn.close()
    
    result_msg = f'Risk scoring complete. Processed {processed} employees. Distribution: {json.dumps(summary)}'
    logging.info(result_msg)
    
    if req:
        return func.HttpResponse(result_msg, status_code=200)
    return result_msg

# function.json (timer trigger version):
# {
#   "scriptFile": "__init__.py",
#   "bindings": [
#     {
#       "name": "mytimer",
#       "type": "timerTrigger",
#       "direction": "in",
#       "schedule": "0 0 3 * * *"
#     }
#   ]
# }

# function.json (HTTP trigger version for manual runs):
# {
#   "scriptFile": "__init__.py",
#   "bindings": [
#     {
#       "name": "req",
#       "type": "httpTrigger",
#       "direction": "in",
#       "authLevel": "function",
#       "methods": ["post"]
#     },
#     {
#       "name": "$return",
#       "type": "http",
#       "direction": "out"
#     }
#   ]
# }

// ==============================================
// POWER BI DAX MEASURES — HR RETENTION ANALYTICS
// ==============================================
// Add these measures to the Power BI data model
// Assumes star schema with fact_turnover, fact_engagement,
// dim_employees, dim_date tables as defined in Step 3
// ==============================================

// --- HEADCOUNT MEASURES ---

Active Headcount = 
    COUNTROWS(
        FILTER(dim_employees, dim_employees[is_active] = TRUE() && dim_employees[is_current] = TRUE())
    )

Avg Headcount Period = 
    VAR _StartHC = CALCULATE([Active Headcount], FIRSTDATE(dim_date[full_date]))
    VAR _EndHC = CALCULATE([Active Headcount], LASTDATE(dim_date[full_date]))
    RETURN DIVIDE(_StartHC + _EndHC, 2)

// --- TURNOVER MEASURES ---

Total Terminations = 
    COUNTROWS(fact_turnover)

Voluntary Terminations = 
    CALCULATE(
        COUNTROWS(fact_turnover),
        fact_turnover[termination_type] = "Voluntary"
    )

Involuntary Terminations = 
    CALCULATE(
        COUNTROWS(fact_turnover),
        fact_turnover[termination_type] = "Involuntary"
    )

Regrettable Terminations = 
    CALCULATE(
        COUNTROWS(fact_turnover),
        fact_turnover[was_regrettable] = TRUE()
    )

Turnover Rate = 
    DIVIDE([Total Terminations], [Avg Headcount Period], 0)

Voluntary Turnover Rate = 
    DIVIDE([Voluntary Terminations], [Avg Headcount Period], 0)

Annualized Turnover Rate = 
    VAR _Months = DISTINCTCOUNT(dim_date[month])
    VAR _MonthlyRate = DIVIDE([Total Terminations], [Avg Headcount Period] * _Months, 0)
    RETURN _MonthlyRate * 12

Rolling 12M Turnover = 
    VAR _EndDate = MAX(dim_date[full_date])
    VAR _StartDate = EDATE(_EndDate, -12)
    VAR _Terms = CALCULATE(
        COUNTROWS(fact_turnover),
        DATESBETWEEN(dim_date[full_date], _StartDate, _EndDate)
    )
    VAR _AvgHC = CALCULATE([Avg Headcount Period], DATESBETWEEN(dim_date[full_date], _StartDate, _EndDate))
    RETURN DIVIDE(_Terms, _AvgHC, 0)

Turnover Rate YoY Change = 
    VAR _CurrentYear = [Annualized Turnover Rate]
    VAR _PriorYear = CALCULATE([Annualized Turnover Rate], SAMEPERIODLASTYEAR(dim_date[full_date]))
    RETURN _CurrentYear - _PriorYear

// --- COST OF TURNOVER ---

Estimated Turnover Cost = 
    // Industry benchmark: cost of replacing an employee = 0.5x to 2x annual salary
    // Using 1x as conservative default; adjust multiplier per client's industry
    VAR _CostMultiplier = 1.0
    RETURN 
        SUMX(
            fact_turnover,
            fact_turnover[salary_at_termination] * _CostMultiplier
        )

Avg Cost Per Termination = 
    DIVIDE([Estimated Turnover Cost], [Total Terminations], 0)

Regrettable Turnover Cost = 
    VAR _CostMultiplier = 1.5  // Higher cost for regrettable departures
    RETURN
        SUMX(
            FILTER(fact_turnover, fact_turnover[was_regrettable] = TRUE()),
            fact_turnover[salary_at_termination] * _CostMultiplier
        )

// --- TENURE ANALYSIS ---

Avg Tenure at Termination = 
    AVERAGE(fact_turnover[tenure_at_termination_months])

Avg Current Tenure = 
    AVERAGE(dim_employees[tenure_months])

First Year Turnover Rate = 
    VAR _FirstYearTerms = CALCULATE(
        COUNTROWS(fact_turnover),
        fact_turnover[tenure_at_termination_months] < 12
    )
    VAR _NewHires = CALCULATE(
        COUNTROWS(dim_employees),
        dim_employees[tenure_months] < 12
    ) + _FirstYearTerms
    RETURN DIVIDE(_FirstYearTerms, _NewHires, 0)

// --- ENGAGEMENT MEASURES ---

Avg Engagement Score = 
    AVERAGE(fact_engagement[overall_engagement_score])

eNPS = 
    VAR _Promoters = CALCULATE(
        COUNTROWS(fact_engagement),
        fact_engagement[recommendation_score] >= 9
    )
    VAR _Detractors = CALCULATE(
        COUNTROWS(fact_engagement),
        fact_engagement[recommendation_score] <= 6
    )
    VAR _Total = COUNTROWS(fact_engagement)
    RETURN DIVIDE(_Promoters - _Detractors, _Total, 0) * 100

Engagement Score Trend = 
    VAR _CurrentAvg = [Avg Engagement Score]
    VAR _PreviousAvg = CALCULATE(
        [Avg Engagement Score],
        PREVIOUSQUARTER(dim_date[full_date])
    )
    RETURN _CurrentAvg - _PreviousAvg

Manager Effectiveness Score = 
    AVERAGE(fact_engagement[manager_effectiveness_score])

Career Development Score = 
    AVERAGE(fact_engagement[career_development_score])

// --- RETENTION RISK MEASURES ---

High Risk Employees = 
    CALCULATE(
        COUNTROWS(retention_risk),
        retention_risk[risk_tier] IN {"High", "Critical"}
    )

High Risk Percentage = 
    DIVIDE([High Risk Employees], [Active Headcount], 0)

Avg Risk Score = 
    AVERAGE(retention_risk[risk_score])

Critical Risk Count = 
    CALCULATE(
        COUNTROWS(retention_risk),
        retention_risk[risk_tier] = "Critical"
    )

Risk Score KPI Color = 
    SWITCH(
        TRUE(),
        [High Risk Percentage] > 0.25, "Red",
        [High Risk Percentage] > 0.15, "Yellow",
        "Green"
    )

// --- MANAGER ANALYTICS ---

Manager Turnover Rate = 
    // Use in context of a manager slicer/filter
    VAR _ManagerTerms = [Total Terminations]
    VAR _ManagerHC = CALCULATE(
        COUNTROWS(dim_employees),
        dim_employees[is_active] = TRUE()
    )
    RETURN DIVIDE(_ManagerTerms, _ManagerHC, 0)

Span of Control = 
    CALCULATE(
        COUNTROWS(dim_employees),
        dim_employees[is_active] = TRUE()
    )

{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "type": "AdaptiveCard",
  "version": "1.4",
  "body": [
    {
      "type": "ColumnSet",
      "columns": [
        {
          "type": "Column",
          "width": "auto",
          "items": [
            {
              "type": "Image",
              "url": "https://cdn-icons-png.flaticon.com/512/6897/6897039.png",
              "size": "Small",
              "style": "Default"
            }
          ]
        },
        {
          "type": "Column",
          "width": "stretch",
          "items": [
            {
              "type": "TextBlock",
              "text": "⚠️ Retention Risk Alert",
              "weight": "Bolder",
              "size": "Medium",
              "color": "@{if(equals(triggerBody()?['risk_tier'],'Critical'),'Attention','Warning')}"
            },
            {
              "type": "TextBlock",
              "text": "An employee on your team has been flagged as @{triggerBody()?['risk_tier']} retention risk.",
              "isSubtle": true,
              "wrap": true,
              "spacing": "None"
            }
          ]
        }
      ]
    },
    {
      "type": "FactSet",
      "facts": [
        {
          "title": "Employee",
          "value": "@{triggerBody()?['first_name']} @{triggerBody()?['last_name']}"
        },
        {
          "title": "Department",
          "value": "@{triggerBody()?['department']}"
        },
        {
          "title": "Job Title",
          "value": "@{triggerBody()?['job_title']}"
        },
        {
          "title": "Risk Score",
          "value": "@{triggerBody()?['risk_score']}/100 (@{triggerBody()?['risk_tier']})"
        },
        {
          "title": "Tenure",
          "value": "@{triggerBody()?['tenure_months']} months"
        }
      ],
      "separator": true
    },
    {
      "type": "TextBlock",
      "text": "Top Risk Factors:",
      "weight": "Bolder",
      "spacing": "Medium"
    },
    {
      "type": "TextBlock",
      "text": "1. @{triggerBody()?['top_risk_factor_1']}",
      "wrap": true,
      "color": "Attention"
    },
    {
      "type": "TextBlock",
      "text": "2. @{triggerBody()?['top_risk_factor_2']}",
      "wrap": true,
      "color": "Warning",
      "isVisible": "@{not(empty(triggerBody()?['top_risk_factor_2']))}"
    },
    {
      "type": "TextBlock",
      "text": "3. @{triggerBody()?['top_risk_factor_3']}",
      "wrap": true,
      "color": "Warning",
      "isVisible": "@{not(empty(triggerBody()?['top_risk_factor_3']))}"
    },
    {
      "type": "TextBlock",
      "text": "Recommended Action: Schedule a 1:1 check-in within the next 48 hours to discuss career development, workload, and engagement. Review the full retention profile for context before the meeting.",
      "wrap": true,
      "spacing": "Medium",
      "isSubtle": true
    }
  ],
  "actions": [
    {
      "type": "Action.OpenUrl",
      "title": "📅 Schedule 1:1 Check-in",
      "url": "https://outlook.office.com/calendar/compose?subject=1:1%20Check-in%20with%20@{triggerBody()?['first_name']}&attendees=@{triggerBody()?['email']}&body=Regular%20check-in%20to%20discuss%20career%20development%20and%20engagement."
    },
    {
      "type": "Action.OpenUrl",
      "title": "📊 View Full Profile in Lattice",
      "url": "https://CLIENTNAME.latticehq.com/people/@{triggerBody()?['employee_id']}"
    },
    {
      "type": "Action.OpenUrl",
      "title": "📈 Open Retention Dashboard",
      "url": "https://app.powerbi.com/groups/WORKSPACE_ID/reports/REPORT_ID/ReportSection_RetentionRisk"
    }
  ]
}

SELECT e.employee_key, e.employee_id, e.first_name, e.last_name,
       e.email, e.department, e.job_title, e.tenure_months,
       e.manager_employee_id, r.risk_score, r.risk_tier,
       r.top_risk_factor_1, r.top_risk_factor_2, r.top_risk_factor_3
       FROM hr.retention_risk r
       JOIN hr.dim_employees e ON r.employee_key = e.employee_key
       WHERE r.risk_tier IN ('High', 'Critical')
       AND r.calculated_date = CAST(GETDATE() AS DATE)
       AND e.is_active = 1

# Power Automate + Azure Function (process_exit_feedback/__init__.py)

# Workflow: Exit Interview Feedback Loop
# Trigger: Finch webhook for employment status change OR
#          manual entry when HR records a termination
# Platform: Power Automate + Azure Function

# Part 1: Power Automate Flow — Trigger and Data Collection
# =========================================================
# Flow Name: 'Exit Interview Capture & Model Feedback'
# 
# Step 1: Trigger — When an item is modified (SharePoint list: 'Terminations')
#   OR Recurrence (daily) with SQL query for new terminations
#   Query: SELECT e.employee_id, e.first_name, e.last_name, e.department,
#          e.termination_date, e.termination_reason, e.termination_type
#          FROM hr.dim_employees e
#          WHERE e.termination_date >= DATEADD(DAY, -1, GETDATE())
#          AND e.is_current = 1
#
# Step 2: For each new termination:
#   a. Create a Microsoft Forms survey link pre-populated with employee info
#   b. Send exit interview invitation email to the departing employee
#   c. Send notification to HR Manager: 'Please conduct exit interview with [Name]'
#   d. Set a 7-day reminder if exit interview not completed
#
# Step 3: When exit interview form is submitted:
#   a. Parse form responses
#   b. Call Azure Function 'process_exit_feedback' with responses

# Part 2: Azure Function — Model Feedback Processing
# ====================================================
# File: process_exit_feedback/__init__.py

import azure.functions as func
import pyodbc
import json
import logging
from datetime import datetime
import os
from azure.identity import DefaultAzureCredential
from azure.keyvault.secrets import SecretClient

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Processing exit interview feedback')
    
    body = req.get_json()
    employee_id = body.get('employee_id')
    
    # Exit interview data points
    primary_reason = body.get('primary_departure_reason')  # Career growth, Compensation, Manager, Work-life balance, Relocation, Other
    would_return = body.get('would_return')  # Yes/No/Maybe
    manager_rating = body.get('manager_satisfaction', 0)  # 1-5
    growth_rating = body.get('career_growth_satisfaction', 0)  # 1-5
    comp_rating = body.get('compensation_satisfaction', 0)  # 1-5
    culture_rating = body.get('culture_satisfaction', 0)  # 1-5
    recommendation = body.get('would_recommend', 0)  # 0-10 (exit eNPS)
    free_text = body.get('additional_comments', '')
    was_regrettable = body.get('was_regrettable', False)  # HR classification
    
    kv_url = os.environ['KEY_VAULT_URL']
    credential = DefaultAzureCredential()
    kv_client = SecretClient(vault_url=kv_url, credential=credential)
    sql_conn_str = kv_client.get_secret('SqlConnectionString').value
    
    conn = pyodbc.connect(sql_conn_str)
    cursor = conn.cursor()
    
    # Get the employee's last retention risk score before departure
    cursor.execute('''
        SELECT TOP 1 r.risk_score, r.risk_tier, 
               r.top_risk_factor_1, r.top_risk_factor_2, r.top_risk_factor_3
        FROM hr.retention_risk r
        JOIN hr.dim_employees e ON r.employee_key = e.employee_key
        WHERE e.employee_id = ?
        ORDER BY r.calculated_date DESC
    ''', employee_id)
    
    risk_row = cursor.fetchone()
    pre_departure_score = risk_row[0] if risk_row else None
    pre_departure_tier = risk_row[1] if risk_row else None
    predicted_factor_1 = risk_row[2] if risk_row else None
    
    # Store exit interview data
    cursor.execute('''
        INSERT INTO hr.exit_interviews (
            employee_id, interview_date, primary_reason,
            would_return, manager_rating, growth_rating,
            comp_rating, culture_rating, exit_enps,
            was_regrettable, pre_departure_risk_score,
            pre_departure_risk_tier, predicted_top_factor,
            actual_reason_matches_prediction
        ) VALUES (?, GETDATE(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''',
        employee_id, primary_reason,
        would_return, manager_rating, growth_rating,
        comp_rating, culture_rating, recommendation,
        was_regrettable, pre_departure_score,
        pre_departure_tier, predicted_factor_1,
        1 if _reason_matches_factor(primary_reason, predicted_factor_1) else 0
    )
    
    # Update fact_turnover with exit interview enrichment
    cursor.execute('''
        UPDATE t SET
            t.was_regrettable = ?,
            t.last_engagement_score = (
                SELECT TOP 1 overall_engagement_score
                FROM hr.fact_engagement fe
                JOIN hr.dim_employees e ON fe.employee_key = e.employee_key
                WHERE e.employee_id = ?
                ORDER BY fe.survey_date_key DESC
            )
        FROM hr.fact_turnover t
        JOIN hr.dim_employees e ON t.employee_key = e.employee_key
        WHERE e.employee_id = ?
    ''', was_regrettable, employee_id, employee_id)
    
    conn.commit()
    
    # Generate model accuracy update
    cursor.execute('''
        SELECT 
            COUNT(*) as total_exits,
            SUM(CASE WHEN pre_departure_risk_tier IN ('High', 'Critical') THEN 1 ELSE 0 END) as correctly_flagged,
            SUM(CASE WHEN actual_reason_matches_prediction = 1 THEN 1 ELSE 0 END) as reason_match,
            AVG(pre_departure_risk_score) as avg_pre_departure_score
        FROM hr.exit_interviews
        WHERE interview_date >= DATEADD(MONTH, -6, GETDATE())
    ''')
    accuracy = cursor.fetchone()
    
    accuracy_report = {
        'total_exits_6m': accuracy[0],
        'correctly_flagged_high_risk': accuracy[1],
        'detection_rate': round(accuracy[1] / accuracy[0] * 100, 1) if accuracy[0] > 0 else 0,
        'reason_prediction_accuracy': round(accuracy[2] / accuracy[0] * 100, 1) if accuracy[0] > 0 else 0,
        'avg_pre_departure_score': round(accuracy[3], 1) if accuracy[3] else 0
    }
    
    cursor.close()
    conn.close()
    
    logging.info(f'Exit feedback processed for {employee_id}. Model accuracy: {json.dumps(accuracy_report)}')
    
    return func.HttpResponse(
        json.dumps({'status': 'success', 'accuracy_report': accuracy_report}),
        mimetype='application/json'
    )

def _reason_matches_factor(actual_reason, predicted_factor):
    """Check if the actual departure reason aligns with the predicted risk factor."""
    if not actual_reason or not predicted_factor:
        return False
    reason_lower = actual_reason.lower()
    factor_lower = predicted_factor.lower()
    
    mappings = {
        'compensation': ['comp', 'salary', 'pay', 'market', 'raise'],
        'career growth': ['promotion', 'career', 'growth', 'stagnation', 'development'],
        'manager': ['manager', 'leadership', 'supervisor', 'team'],
        'work-life balance': ['balance', 'workload', 'burnout', 'hours'],
        'engagement': ['engagement', 'sentiment', 'disengaged'],
    }
    
    for category, keywords in mappings.items():
        if any(kw in reason_lower for kw in keywords):
            if any(kw in factor_lower for kw in keywords):
                return True
    return False

# SQL DDL for exit_interviews table (add to Step 3):
# CREATE TABLE hr.exit_interviews (
#     interview_key INT IDENTITY(1,1) PRIMARY KEY,
#     employee_id NVARCHAR(50),
#     interview_date DATETIME2,
#     primary_reason NVARCHAR(200),
#     would_return NVARCHAR(20),
#     manager_rating DECIMAL(3,1),
#     growth_rating DECIMAL(3,1),
#     comp_rating DECIMAL(3,1),
#     culture_rating DECIMAL(3,1),
#     exit_enps INT,
#     was_regrettable BIT,
#     pre_departure_risk_score DECIMAL(5,2),
#     pre_departure_risk_tier NVARCHAR(20),
#     predicted_top_factor NVARCHAR(200),
#     actual_reason_matches_prediction BIT,
#     created_at DATETIME2 DEFAULT GETDATE()
# );