Implementation Guide: Benchmark client premiums vs. market and flag accounts due for remarketing

-- Applied Epic SQL Server backend (if direct SQL access is available for
-- Applied Epic on-premise)

SELECT CarrierName, COUNT(*) as PolicyCount, MAX(LastModifiedDate) as MostRecent FROM dbo.Policy GROUP BY CarrierName ORDER BY PolicyCount DESC

# verify bandwidth and latency to Azure/SaaS endpoints

# Network assessment - verify bandwidth and latency to Azure/SaaS endpoints:
Test-NetConnection -ComputerName login.microsoftonline.com -Port 443
Test-NetConnection -ComputerName *.database.windows.net -Port 1433
Test-NetConnection -ComputerName api.appliedsystems.com -Port 443
# Run speed test
Invoke-WebRequest -Uri 'https://www.speedtest.net' -UseBasicParsing

SELECT LastName, FirstName, DateOfBirth, COUNT(*) as DuplicateCount
FROM dbo.Client
GROUP BY LastName, FirstName, DateOfBirth
HAVING COUNT(*) > 1
ORDER BY DuplicateCount DESC

POST https://api.appliedsystems.com/epic/v1/policies/{policyId}
Content-Type: application/json

{ "lineOfBusiness": "PERSONAL_AUTO", "coverageType": "FULL_COVERAGE" }

# Login to Azure CLI
az login

# Set subscription context
az account set --subscription "<client-subscription-id>"

# Create Resource Group
az group create --name rg-insurance-analytics --location eastus2

# Create Azure SQL Server
az sql server create \
  --name sql-insurance-benchmarking \
  --resource-group rg-insurance-analytics \
  --location eastus2 \
  --admin-user sqladmin \
  --admin-password '<StrongPassword123!>'

# Configure firewall rules (allow Azure services + agency office IP)
az sql server firewall-rule create \
  --resource-group rg-insurance-analytics \
  --server sql-insurance-benchmarking \
  --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-insurance-analytics \
  --server sql-insurance-benchmarking \
  --name AgencyOfficeIP \
  --start-ip-address <agency-public-ip> \
  --end-ip-address <agency-public-ip>

# Create the premium data warehouse database
az sql db create \
  --resource-group rg-insurance-analytics \
  --server sql-insurance-benchmarking \
  --name db-premium-benchmarking \
  --service-objective S1 \
  --max-size 250GB

# Enable TDE (Transparent Data Encryption) - enabled by default but verify
az sql db tde set \
  --resource-group rg-insurance-analytics \
  --server sql-insurance-benchmarking \
  --database db-premium-benchmarking \
  --status Enabled

# Enable auditing for compliance
az sql server audit-policy update \
  --resource-group rg-insurance-analytics \
  --name sql-insurance-benchmarking \
  --state Enabled \
  --storage-account <storage-account-name>

# Create Azure Data Factory instance
az datafactory create \
  --resource-group rg-insurance-analytics \
  --factory-name adf-insurance-etl \
  --location eastus2

# Create Azure Key Vault for API credentials
az keyvault create \
  --name kv-insurance-analytics \
  --resource-group rg-insurance-analytics \
  --location eastus2 \
  --sku standard

# Store AMS API credentials in Key Vault
az keyvault secret set \
  --vault-name kv-insurance-analytics \
  --name ams-api-client-id \
  --value '<ams-api-client-id>'

az keyvault secret set \
  --vault-name kv-insurance-analytics \
  --name ams-api-client-secret \
  --value '<ams-api-client-secret>'

-- Connect to Azure SQL Database and execute schema creation
-- Use Azure Data Studio or SSMS

-- Core policy data table
CREATE TABLE dbo.Policies (
    PolicyID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    AMSPolicyNumber NVARCHAR(50) NOT NULL,
    AMSSource NVARCHAR(20) NOT NULL, -- 'EPIC', 'AMS360', 'EZLYNX', 'HAWKSOFT'
    ClientID UNIQUEIDENTIFIER NOT NULL,
    ClientName NVARCHAR(200) NOT NULL,
    CarrierCode NVARCHAR(20) NOT NULL,
    CarrierName NVARCHAR(200) NOT NULL,
    LineOfBusiness NVARCHAR(50) NOT NULL, -- 'PERSONAL_AUTO', 'HOMEOWNERS', 'COMMERCIAL_PROPERTY', etc.
    CoverageType NVARCHAR(100),
    PolicyEffectiveDate DATE NOT NULL,
    PolicyExpirationDate DATE NOT NULL,
    AnnualPremium DECIMAL(12,2) NOT NULL,
    PriorTermPremium DECIMAL(12,2),
    PremiumChangeAmount AS (AnnualPremium - ISNULL(PriorTermPremium, AnnualPremium)),
    PremiumChangePercent AS (
        CASE WHEN ISNULL(PriorTermPremium, 0) > 0 
        THEN ROUND(((AnnualPremium - PriorTermPremium) / PriorTermPremium) * 100, 2)
        ELSE 0 END
    ),
    State NVARCHAR(2) NOT NULL,
    ZipCode NVARCHAR(10),
    IndustryCode NVARCHAR(20), -- NAICS for commercial lines
    RenewalDate DATE,
    ProducerCode NVARCHAR(20),
    ProducerName NVARCHAR(200),
    ClaimsCount INT DEFAULT 0,
    ClaimsAmountTotal DECIMAL(12,2) DEFAULT 0,
    AccountTenureYears INT,
    TotalAccountPremium DECIMAL(12,2), -- all policies for this client combined
    RemarketingFlag BIT DEFAULT 0,
    RemarketingReason NVARCHAR(500),
    RemarketingDate DATE,
    RemarketingStatus NVARCHAR(20) DEFAULT 'NONE', -- 'NONE','FLAGGED','IN_PROGRESS','COMPLETED','DECLINED'
    LastSyncDate DATETIME2 DEFAULT GETUTCDATE(),
    CreatedDate DATETIME2 DEFAULT GETUTCDATE(),
    ModifiedDate DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_Policies_Renewal (RenewalDate, RemarketingFlag),
    INDEX IX_Policies_LOB (LineOfBusiness, State),
    INDEX IX_Policies_Client (ClientID)
);

-- Market benchmark data table
CREATE TABLE dbo.MarketBenchmarks (
    BenchmarkID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    DataSource NVARCHAR(50) NOT NULL, -- 'IVANS', 'ZYWAVE', 'MANUAL'
    LineOfBusiness NVARCHAR(50) NOT NULL,
    CoverageType NVARCHAR(100),
    State NVARCHAR(2) NOT NULL,
    ZipCodePrefix NVARCHAR(5), -- first 3-5 digits for geographic grouping
    IndustryCode NVARCHAR(20),
    BenchmarkPeriod DATE NOT NULL, -- first day of the benchmark period
    MedianPremium DECIMAL(12,2),
    AvgPremium DECIMAL(12,2),
    P25Premium DECIMAL(12,2), -- 25th percentile
    P75Premium DECIMAL(12,2), -- 75th percentile
    SampleSize INT,
    AvgPremiumChangePercent DECIMAL(6,2), -- market-wide average increase
    LastUpdated DATETIME2 DEFAULT GETUTCDATE(),
    INDEX IX_Benchmarks_Lookup (LineOfBusiness, State, BenchmarkPeriod)
);

-- Premium history for trend tracking
CREATE TABLE dbo.PremiumHistory (
    HistoryID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    PolicyID UNIQUEIDENTIFIER FOREIGN KEY REFERENCES dbo.Policies(PolicyID),
    TermEffectiveDate DATE NOT NULL,
    TermExpirationDate DATE NOT NULL,
    TermPremium DECIMAL(12,2) NOT NULL,
    CarrierCode NVARCHAR(20),
    RecordedDate DATETIME2 DEFAULT GETUTCDATE()
);

-- Remarketing workflow tracking
CREATE TABLE dbo.RemarketingActions (
    ActionID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    PolicyID UNIQUEIDENTIFIER FOREIGN KEY REFERENCES dbo.Policies(PolicyID),
    ClientID UNIQUEIDENTIFIER NOT NULL,
    TriggerType NVARCHAR(50) NOT NULL, -- 'PREMIUM_INCREASE', 'BENCHMARK_OUTLIER', 'MANUAL', 'RENEWAL_REVIEW'
    TriggerValue NVARCHAR(100), -- e.g., '18.5% increase'
    FlaggedDate DATE NOT NULL,
    AssignedProducer NVARCHAR(200),
    CarriersSubmitted NVARCHAR(MAX), -- JSON array of carrier names
    QuotesReceived INT DEFAULT 0,
    BestAlternativePremium DECIMAL(12,2),
    BestAlternativeCarrier NVARCHAR(200),
    Outcome NVARCHAR(20) DEFAULT 'PENDING', -- 'PENDING','REMARKETED','RETAINED','LOST'
    OutcomeDate DATE,
    SavingsAmount DECIMAL(12,2),
    Notes NVARCHAR(MAX),
    CreatedDate DATETIME2 DEFAULT GETUTCDATE(),
    ModifiedDate DATETIME2 DEFAULT GETUTCDATE()
);

-- Configuration table for remarketing thresholds
CREATE TABLE dbo.RemarketingThresholds (
    ThresholdID INT IDENTITY(1,1) PRIMARY KEY,
    LineOfBusiness NVARCHAR(50) NOT NULL,
    PremiumIncreaseThresholdPercent DECIMAL(5,2) NOT NULL DEFAULT 15.00,
    MinPolicyPremium DECIMAL(12,2) DEFAULT 500.00,
    MinAccountPremium DECIMAL(12,2) DEFAULT 1000.00,
    MinAccountTenureYears INT DEFAULT 0,
    MaxClaimsCount INT DEFAULT 99,
    ExcludeNewBusiness BIT DEFAULT 0,
    DaysBeforeRenewalToFlag INT DEFAULT 90,
    IsActive BIT DEFAULT 1,
    CreatedDate DATETIME2 DEFAULT GETUTCDATE()
);

-- Insert default thresholds
INSERT INTO dbo.RemarketingThresholds (LineOfBusiness, PremiumIncreaseThresholdPercent, MinPolicyPremium, MinAccountPremium, DaysBeforeRenewalToFlag)
VALUES 
('PERSONAL_AUTO', 15.00, 500.00, 1000.00, 90),
('HOMEOWNERS', 15.00, 800.00, 1500.00, 90),
('COMMERCIAL_PROPERTY', 10.00, 2000.00, 5000.00, 120),
('COMMERCIAL_AUTO', 10.00, 2000.00, 5000.00, 120),
('COMMERCIAL_GL', 10.00, 1500.00, 5000.00, 120),
('WORKERS_COMP', 10.00, 3000.00, 5000.00, 120),
('BOP', 12.00, 1000.00, 3000.00, 90),
('UMBRELLA', 20.00, 300.00, 1000.00, 60);
-- Create stored procedure for the remarketing flagging engine
CREATE PROCEDURE dbo.usp_FlagAccountsForRemarketing
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Reset flags for policies outside the renewal window
    UPDATE p SET 
        RemarketingFlag = 0,
        RemarketingStatus = 'NONE',
        ModifiedDate = GETUTCDATE()
    FROM dbo.Policies p
    WHERE p.RemarketingFlag = 1
    AND p.RemarketingStatus = 'FLAGGED'
    AND p.RenewalDate < DATEADD(DAY, -30, GETDATE());
    
    -- Flag policies that meet remarketing criteria
    UPDATE p SET
        RemarketingFlag = 1,
        RemarketingReason = 
            CASE 
                WHEN p.PremiumChangePercent >= t.PremiumIncreaseThresholdPercent 
                    THEN CONCAT('Premium increase of ', p.PremiumChangePercent, '% exceeds ', t.PremiumIncreaseThresholdPercent, '% threshold')
                WHEN p.AnnualPremium > ISNULL(b.P75Premium, 999999999)
                    THEN CONCAT('Premium $', FORMAT(p.AnnualPremium, 'N2'), ' exceeds 75th percentile market benchmark of $', FORMAT(b.P75Premium, 'N2'))
                ELSE 'Multiple criteria met'
            END,
        RemarketingDate = GETUTCDATE(),
        RemarketingStatus = 'FLAGGED',
        ModifiedDate = GETUTCDATE()
    FROM dbo.Policies p
    INNER JOIN dbo.RemarketingThresholds t ON p.LineOfBusiness = t.LineOfBusiness AND t.IsActive = 1
    LEFT JOIN dbo.MarketBenchmarks b ON 
        p.LineOfBusiness = b.LineOfBusiness 
        AND p.State = b.State
        AND b.BenchmarkPeriod = (
            SELECT MAX(BenchmarkPeriod) FROM dbo.MarketBenchmarks 
            WHERE LineOfBusiness = p.LineOfBusiness AND State = p.State
        )
    WHERE p.RemarketingStatus IN ('NONE', 'FLAGGED')
    AND p.RenewalDate BETWEEN GETDATE() AND DATEADD(DAY, t.DaysBeforeRenewalToFlag, GETDATE())
    AND p.AnnualPremium >= t.MinPolicyPremium
    AND ISNULL(p.TotalAccountPremium, p.AnnualPremium) >= t.MinAccountPremium
    AND ISNULL(p.ClaimsCount, 0) <= t.MaxClaimsCount
    AND ISNULL(p.AccountTenureYears, 99) >= t.MinAccountTenureYears
    AND (
        p.PremiumChangePercent >= t.PremiumIncreaseThresholdPercent
        OR p.AnnualPremium > ISNULL(b.P75Premium, 999999999)
    );
    
    -- Insert remarketing actions for newly flagged policies
    INSERT INTO dbo.RemarketingActions (PolicyID, ClientID, TriggerType, TriggerValue, FlaggedDate, AssignedProducer)
    SELECT 
        p.PolicyID,
        p.ClientID,
        CASE 
            WHEN p.PremiumChangePercent >= t.PremiumIncreaseThresholdPercent THEN 'PREMIUM_INCREASE'
            ELSE 'BENCHMARK_OUTLIER'
        END,
        CASE 
            WHEN p.PremiumChangePercent >= t.PremiumIncreaseThresholdPercent 
                THEN CONCAT(p.PremiumChangePercent, '% increase')
            ELSE CONCAT('Above P75: $', FORMAT(p.AnnualPremium, 'N2'))
        END,
        GETDATE(),
        p.ProducerName
    FROM dbo.Policies p
    INNER JOIN dbo.RemarketingThresholds t ON p.LineOfBusiness = t.LineOfBusiness
    WHERE p.RemarketingStatus = 'FLAGGED'
    AND NOT EXISTS (
        SELECT 1 FROM dbo.RemarketingActions ra 
        WHERE ra.PolicyID = p.PolicyID 
        AND ra.Outcome = 'PENDING'
    );
    
    -- Return summary
    SELECT 
        LineOfBusiness,
        COUNT(*) as FlaggedCount,
        AVG(PremiumChangePercent) as AvgPremiumIncrease,
        SUM(AnnualPremium) as TotalFlaggedPremium
    FROM dbo.Policies
    WHERE RemarketingFlag = 1 AND RemarketingStatus = 'FLAGGED'
    GROUP BY LineOfBusiness;
END;
GO

# REST Linked Service configuration for Applied Epic API

# Azure Data Factory - Create Linked Service for AMS API (Applied Epic example)
# In ADF Studio, create a new REST Linked Service:
# Name: ls_AppliedEpicAPI
# Base URL: https://api.appliedsystems.com/epic/v1/
# Authentication Type: OAuth2 Client Credentials
# Client ID: @Microsoft.KeyVault(SecretUri=https://kv-insurance-analytics.vault.azure.net/secrets/ams-api-client-id/)
# Client Secret: @Microsoft.KeyVault(SecretUri=https://kv-insurance-analytics.vault.azure.net/secrets/ams-api-client-secret/)
# Token endpoint: https://auth.appliedsystems.com/oauth2/token
# Scope: epic.policies.read epic.clients.read

# AMS data extraction class with OAuth2 auth, paginated policy pull, LOB
# normalization, and Azure SQL upsert

# Python script for AMS data extraction (can run as Azure Function or ADF custom activity)
# Save as ams_extract.py

import requests
import pyodbc
import json
from datetime import datetime, timedelta

class AMSExtractor:
    def __init__(self, ams_type, api_base_url, client_id, client_secret, sql_conn_string):
        self.ams_type = ams_type
        self.api_base_url = api_base_url
        self.client_id = client_id
        self.client_secret = client_secret
        self.sql_conn = pyodbc.connect(sql_conn_string)
        self.token = None
    
    def authenticate(self):
        """Obtain OAuth2 token from AMS API"""
        if self.ams_type == 'EPIC':
            token_url = 'https://auth.appliedsystems.com/oauth2/token'
        elif self.ams_type == 'AMS360':
            token_url = 'https://api.vertafore.com/oauth/token'
        else:
            raise ValueError(f'Unsupported AMS type: {self.ams_type}')
        
        response = requests.post(token_url, data={
            'grant_type': 'client_credentials',
            'client_id': self.client_id,
            'client_secret': self.client_secret,
            'scope': 'policies.read clients.read'
        })
        response.raise_for_status()
        self.token = response.json()['access_token']
    
    def extract_policies(self, modified_since=None):
        """Extract policies modified since last sync"""
        if not self.token:
            self.authenticate()
        
        headers = {'Authorization': f'Bearer {self.token}', 'Content-Type': 'application/json'}
        
        if modified_since is None:
            modified_since = (datetime.utcnow() - timedelta(days=1)).isoformat()
        
        policies = []
        page = 1
        while True:
            params = {
                'modifiedSince': modified_since,
                'page': page,
                'pageSize': 100,
                'status': 'Active'
            }
            response = requests.get(
                f'{self.api_base_url}/policies',
                headers=headers,
                params=params
            )
            response.raise_for_status()
            data = response.json()
            
            if not data.get('items'):
                break
            policies.extend(data['items'])
            page += 1
        
        return policies
    
    def normalize_policy(self, raw_policy):
        """Normalize AMS-specific policy data to common schema"""
        if self.ams_type == 'EPIC':
            return {
                'AMSPolicyNumber': raw_policy.get('policyNumber'),
                'AMSSource': 'EPIC',
                'ClientName': raw_policy.get('insuredName', ''),
                'CarrierCode': raw_policy.get('carrierCode', ''),
                'CarrierName': raw_policy.get('carrierName', ''),
                'LineOfBusiness': self._map_lob(raw_policy.get('lineOfBusiness', '')),
                'CoverageType': raw_policy.get('coverageDescription', ''),
                'PolicyEffectiveDate': raw_policy.get('effectiveDate'),
                'PolicyExpirationDate': raw_policy.get('expirationDate'),
                'AnnualPremium': float(raw_policy.get('annualPremium', 0)),
                'PriorTermPremium': float(raw_policy.get('priorTermPremium', 0)) if raw_policy.get('priorTermPremium') else None,
                'State': raw_policy.get('ratingState', ''),
                'ZipCode': raw_policy.get('zipCode', ''),
                'ProducerCode': raw_policy.get('producerCode', ''),
                'ProducerName': raw_policy.get('producerName', ''),
            }
        # Add AMS360, EZLynx, HawkSoft mappings as needed
        return raw_policy
    
    def _map_lob(self, ams_lob):
        """Map AMS-specific LOB codes to standard codes"""
        lob_mapping = {
            'Personal Auto': 'PERSONAL_AUTO',
            'Homeowner': 'HOMEOWNERS',
            'Home': 'HOMEOWNERS',
            'Commercial Property': 'COMMERCIAL_PROPERTY',
            'Commercial Auto': 'COMMERCIAL_AUTO',
            'General Liability': 'COMMERCIAL_GL',
            'Workers Compensation': 'WORKERS_COMP',
            'BOP': 'BOP',
            'Businessowners': 'BOP',
            'Umbrella': 'UMBRELLA',
            'Excess': 'UMBRELLA',
        }
        return lob_mapping.get(ams_lob, ams_lob.upper().replace(' ', '_'))
    
    def upsert_to_warehouse(self, normalized_policies):
        """Upsert normalized policy data into Azure SQL"""
        cursor = self.sql_conn.cursor()
        for p in normalized_policies:
            cursor.execute("""
                MERGE dbo.Policies AS target
                USING (SELECT ? AS AMSPolicyNumber, ? AS AMSSource) AS source
                ON target.AMSPolicyNumber = source.AMSPolicyNumber AND target.AMSSource = source.AMSSource
                WHEN MATCHED THEN UPDATE SET
                    ClientName = ?, CarrierCode = ?, CarrierName = ?,
                    LineOfBusiness = ?, CoverageType = ?,
                    PolicyEffectiveDate = ?, PolicyExpirationDate = ?,
                    AnnualPremium = ?, PriorTermPremium = ?,
                    State = ?, ZipCode = ?,
                    ProducerCode = ?, ProducerName = ?,
                    RenewalDate = ?, LastSyncDate = GETUTCDATE(), ModifiedDate = GETUTCDATE()
                WHEN NOT MATCHED THEN INSERT (
                    AMSPolicyNumber, AMSSource, ClientID, ClientName,
                    CarrierCode, CarrierName, LineOfBusiness, CoverageType,
                    PolicyEffectiveDate, PolicyExpirationDate,
                    AnnualPremium, PriorTermPremium, State, ZipCode,
                    ProducerCode, ProducerName, RenewalDate
                ) VALUES (?, ?, NEWID(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
            """, (
                p['AMSPolicyNumber'], p['AMSSource'],
                p['ClientName'], p['CarrierCode'], p['CarrierName'],
                p['LineOfBusiness'], p['CoverageType'],
                p['PolicyEffectiveDate'], p['PolicyExpirationDate'],
                p['AnnualPremium'], p['PriorTermPremium'],
                p['State'], p['ZipCode'],
                p['ProducerCode'], p['ProducerName'],
                p['PolicyExpirationDate'],  # RenewalDate = expiration for renewal policies
                # INSERT values
                p['AMSPolicyNumber'], p['AMSSource'], p['ClientName'],
                p['CarrierCode'], p['CarrierName'],
                p['LineOfBusiness'], p['CoverageType'],
                p['PolicyEffectiveDate'], p['PolicyExpirationDate'],
                p['AnnualPremium'], p['PriorTermPremium'],
                p['State'], p['ZipCode'],
                p['ProducerCode'], p['ProducerName'],
                p['PolicyExpirationDate']
            ))
        cursor.commit()
        return len(normalized_policies)

# Azure Data Factory pipeline definition; import into ADF Studio

{
  "name": "pipeline_daily_policy_sync",
  "properties": {
    "activities": [
      {
        "name": "Extract_AMS_Policies",
        "type": "AzureFunctionActivity",
        "typeProperties": {
          "functionName": "ams_extract",
          "method": "POST",
          "body": {
            "ams_type": "EPIC",
            "modified_since": "@pipeline().TriggerTime"
          }
        }
      },
      {
        "name": "Run_Remarketing_Flagging",
        "type": "SqlServerStoredProcedure",
        "dependsOn": [{"activity": "Extract_AMS_Policies", "dependencyConditions": ["Succeeded"]}],
        "typeProperties": {
          "storedProcedureName": "dbo.usp_FlagAccountsForRemarketing"
        },
        "linkedServiceName": {
          "referenceName": "ls_AzureSQL_PremiumWarehouse",
          "type": "LinkedServiceReference"
        }
      }
    ]
  }
}

# Azure Function to pull benchmark data monthly

# For Zywave Analytics API integration (if API access is available):
# Configure Azure Function to pull benchmark data monthly

import requests
import pyodbc

def ingest_zywave_benchmarks(zywave_api_key, sql_conn_string):
    headers = {'Authorization': f'Bearer {zywave_api_key}'}
    
    lines_of_business = [
        'PERSONAL_AUTO', 'HOMEOWNERS', 'COMMERCIAL_PROPERTY',
        'COMMERCIAL_AUTO', 'COMMERCIAL_GL', 'WORKERS_COMP', 'BOP'
    ]
    states = ['AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA',
              'KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ',
              'NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT',
              'VA','WA','WV','WI','WY']
    
    conn = pyodbc.connect(sql_conn_string)
    cursor = conn.cursor()
    
    for lob in lines_of_business:
        for state in states:
            response = requests.get(
                f'https://api.zywave.com/analytics/v1/benchmarks',
                headers=headers,
                params={'lineOfBusiness': lob, 'state': state}
            )
            if response.status_code == 200:
                data = response.json()
                cursor.execute("""
                    MERGE dbo.MarketBenchmarks AS target
                    USING (SELECT ? AS DataSource, ? AS LineOfBusiness, ? AS State, ? AS BenchmarkPeriod) AS source
                    ON target.DataSource = source.DataSource 
                        AND target.LineOfBusiness = source.LineOfBusiness 
                        AND target.State = source.State
                        AND target.BenchmarkPeriod = source.BenchmarkPeriod
                    WHEN MATCHED THEN UPDATE SET
                        MedianPremium = ?, AvgPremium = ?, P25Premium = ?, P75Premium = ?,
                        SampleSize = ?, AvgPremiumChangePercent = ?, LastUpdated = GETUTCDATE()
                    WHEN NOT MATCHED THEN INSERT (
                        DataSource, LineOfBusiness, State, BenchmarkPeriod,
                        MedianPremium, AvgPremium, P25Premium, P75Premium,
                        SampleSize, AvgPremiumChangePercent
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
                """, (
                    'ZYWAVE', lob, state, data.get('period'),
                    data.get('medianPremium'), data.get('avgPremium'),
                    data.get('p25Premium'), data.get('p75Premium'),
                    data.get('sampleSize'), data.get('avgChangePercent'),
                    'ZYWAVE', lob, state, data.get('period'),
                    data.get('medianPremium'), data.get('avgPremium'),
                    data.get('p25Premium'), data.get('p75Premium'),
                    data.get('sampleSize'), data.get('avgChangePercent')
                ))
    cursor.commit()
    conn.close()

-- Alternative: Manual benchmark data import via CSV
-- For agencies without API access to Zywave/Ivans, prepare a CSV template
-- and import quarterly using ADF or SSMS

-- CSV Template (benchmark_import_template.csv):
-- DataSource,LineOfBusiness,CoverageType,State,ZipCodePrefix,BenchmarkPeriod,MedianPremium,AvgPremium,P25Premium,P75Premium,SampleSize,AvgPremiumChangePercent
-- INDUSTRY,HOMEOWNERS,,FL,,2025-01-01,2850.00,3100.00,2200.00,3800.00,15000,12.5
-- INDUSTRY,PERSONAL_AUTO,,FL,,2025-01-01,1950.00,2100.00,1500.00,2600.00,25000,8.3

-- Import command using SSMS:
BULK INSERT dbo.MarketBenchmarks
FROM '/data/benchmark_import_template.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FORMAT = 'CSV'
);

// Measure: Total Policies
Total Policies = COUNTROWS(Policies)

// Measure: Total Premium
Total Premium = SUM(Policies[AnnualPremium])

// Measure: Average Premium Change %
Avg Premium Change = AVERAGE(Policies[PremiumChangePercent])

// Measure: Policies Flagged for Remarketing
Flagged for Remarketing = 
    CALCULATE(
        COUNTROWS(Policies),
        Policies[RemarketingFlag] = TRUE()
    )

// Measure: Remarketing Rate
Remarketing Rate = 
    DIVIDE(
        [Flagged for Remarketing],
        [Total Policies],
        0
    )

// Measure: Premium at Risk (total premium of flagged policies)
Premium at Risk = 
    CALCULATE(
        SUM(Policies[AnnualPremium]),
        Policies[RemarketingFlag] = TRUE()
    )

// Measure: Policies Above Market Benchmark
Above Market = 
    CALCULATE(
        COUNTROWS(Policies),
        FILTER(
            Policies,
            Policies[AnnualPremium] > RELATED(MarketBenchmarks[P75Premium])
        )
    )

// Measure: Remarketing Success Rate
Remarketing Success Rate = 
    DIVIDE(
        CALCULATE(COUNTROWS(RemarketingActions), RemarketingActions[Outcome] = "REMARKETED"),
        CALCULATE(COUNTROWS(RemarketingActions), RemarketingActions[Outcome] <> "PENDING"),
        0
    )

// Measure: Total Savings Achieved
Total Savings = 
    CALCULATE(
        SUM(RemarketingActions[SavingsAmount]),
        RemarketingActions[Outcome] = "REMARKETED"
    )

// Measure: Renewals Next 30/60/90 Days
Renewals Next 30 Days = 
    CALCULATE(
        COUNTROWS(Policies),
        Policies[RenewalDate] >= TODAY(),
        Policies[RenewalDate] <= TODAY() + 30
    )

Renewals Next 60 Days = 
    CALCULATE(
        COUNTROWS(Policies),
        Policies[RenewalDate] >= TODAY(),
        Policies[RenewalDate] <= TODAY() + 60
    )

Renewals Next 90 Days = 
    CALCULATE(
        COUNTROWS(Policies),
        Policies[RenewalDate] >= TODAY(),
        Policies[RenewalDate] <= TODAY() + 90
    )

# defines the ProducerFilter role so each producer sees only their own book

// RLS Role Definition
// Power BI Desktop > Modeling > Manage Roles
// Role Name: ProducerFilter
// Table: Policies
[ProducerName] = USERNAME()

# syncs InsuredMine pipeline stage changes back to Azure SQL

# InsuredMine Webhook Configuration for Azure SQL Integration
# This webhook pushes remarketing status updates from InsuredMine back to Azure SQL

# Azure Function to receive InsuredMine webhook events:
import azure.functions as func
import pyodbc
import json

def main(req: func.HttpRequest) -> func.HttpResponse:
    try:
        payload = req.get_json()
        event_type = payload.get('event_type')
        deal = payload.get('deal', {})
        
        conn_string = os.environ['SQL_CONNECTION_STRING']
        conn = pyodbc.connect(conn_string)
        cursor = conn.cursor()
        
        if event_type == 'deal.stage_changed':
            policy_number = deal.get('custom_fields', {}).get('policy_number')
            new_stage = deal.get('stage_name')
            
            status_mapping = {
                'Flagged for Review': 'FLAGGED',
                'Producer Review': 'FLAGGED',
                'Submission Sent': 'IN_PROGRESS',
                'Quotes Received': 'IN_PROGRESS',
                'Client Presentation': 'IN_PROGRESS',
                'Remarketed - Won': 'COMPLETED',
                'Retained - Current Carrier': 'COMPLETED',
                'Declined - No Action': 'DECLINED'
            }
            
            outcome_mapping = {
                'Remarketed - Won': 'REMARKETED',
                'Retained - Current Carrier': 'RETAINED',
                'Declined - No Action': 'LOST'
            }
            
            new_status = status_mapping.get(new_stage, 'IN_PROGRESS')
            outcome = outcome_mapping.get(new_stage)
            
            cursor.execute("""
                UPDATE dbo.Policies 
                SET RemarketingStatus = ?, ModifiedDate = GETUTCDATE()
                WHERE AMSPolicyNumber = ?
            """, (new_status, policy_number))
            
            if outcome:
                cursor.execute("""
                    UPDATE dbo.RemarketingActions 
                    SET Outcome = ?, OutcomeDate = GETDATE(), ModifiedDate = GETUTCDATE()
                    WHERE PolicyID = (SELECT PolicyID FROM dbo.Policies WHERE AMSPolicyNumber = ?)
                    AND Outcome = 'PENDING'
                """, (outcome, policy_number))
            
            cursor.commit()
        
        conn.close()
        return func.HttpResponse('OK', status_code=200)
    except Exception as e:
        return func.HttpResponse(str(e), status_code=500)

# Azure Function: push_remarketing_to_crm.py
# Trigger: Timer (runs daily at 7:00 AM after the 6:00 AM policy sync)
# Or: Event Grid trigger from ADF pipeline completion

import azure.functions as func
import pyodbc
import requests
import json
import os
from datetime import datetime

def main(timer: func.TimerRequest) -> None:
    sql_conn_string = os.environ['SQL_CONNECTION_STRING']
    insuredmine_api_key = os.environ['INSUREDMINE_API_KEY']
    insuredmine_base_url = 'https://api.insuredmine.com/v1'
    
    conn = pyodbc.connect(sql_conn_string)
    cursor = conn.cursor()
    
    # Get newly flagged policies not yet pushed to CRM
    cursor.execute("""
        SELECT 
            p.PolicyID, p.AMSPolicyNumber, p.ClientName, p.CarrierName,
            p.LineOfBusiness, p.AnnualPremium, p.PriorTermPremium,
            p.PremiumChangePercent, p.RenewalDate, p.ProducerName,
            p.RemarketingReason, p.State
        FROM dbo.Policies p
        LEFT JOIN dbo.RemarketingActions ra ON p.PolicyID = ra.PolicyID AND ra.Outcome = 'PENDING'
        WHERE p.RemarketingFlag = 1 
        AND p.RemarketingStatus = 'FLAGGED'
        AND ra.ActionID IS NOT NULL
        AND NOT EXISTS (
            SELECT 1 FROM dbo.RemarketingActions ra2 
            WHERE ra2.PolicyID = p.PolicyID 
            AND ra2.CRMDealCreated = 1
        )
    """)
    
    flagged_policies = cursor.fetchall()
    
    headers = {
        'Authorization': f'Bearer {insuredmine_api_key}',
        'Content-Type': 'application/json'
    }
    
    for policy in flagged_policies:
        deal_data = {
            'pipeline_name': 'Premium Remarketing Pipeline',
            'stage_name': 'Flagged for Review',
            'deal_name': f'Remarket: {policy.ClientName} - {policy.LineOfBusiness}',
            'deal_value': float(policy.AnnualPremium),
            'assigned_to': policy.ProducerName,
            'expected_close_date': policy.RenewalDate.isoformat() if policy.RenewalDate else None,
            'custom_fields': {
                'policy_number': policy.AMSPolicyNumber,
                'carrier': policy.CarrierName,
                'current_premium': float(policy.AnnualPremium),
                'prior_premium': float(policy.PriorTermPremium) if policy.PriorTermPremium else None,
                'premium_change_pct': float(policy.PremiumChangePercent),
                'line_of_business': policy.LineOfBusiness,
                'remarketing_reason': policy.RemarketingReason,
                'state': policy.State
            },
            'tags': ['auto-remarketing', policy.LineOfBusiness.lower()]
        }
        
        response = requests.post(
            f'{insuredmine_base_url}/deals',
            headers=headers,
            json=deal_data
        )
        
        if response.status_code in (200, 201):
            # Mark as pushed to CRM
            cursor.execute("""
                UPDATE dbo.RemarketingActions 
                SET CRMDealCreated = 1, ModifiedDate = GETUTCDATE()
                WHERE PolicyID = ? AND Outcome = 'PENDING'
            """, (str(policy.PolicyID),))
    
    cursor.commit()
    conn.close()

ALTER TABLE dbo.RemarketingActions ADD CRMDealCreated BIT DEFAULT 0;

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

# Fortinet FortiGate 60F Configuration
# Access FortiGate web console at https://192.168.1.99 (default)

# 1. Create VLAN for Analytics Traffic
config system interface
    edit "analytics-vlan"
        set vdom "root"
        set ip 10.10.20.1 255.255.255.0
        set allowaccess ping https ssh
        set interface "internal"
        set vlanid 20
    next
end

# 2. Create address objects for cloud services
config firewall address
    edit "Azure-SQL"
        set type fqdn
        set fqdn "sql-insurance-benchmarking.database.windows.net"
    next
    edit "Azure-DataFactory"
        set type fqdn
        set fqdn "*.datafactory.azure.net"
    next
    edit "InsuredMine-API"
        set type fqdn
        set fqdn "api.insuredmine.com"
    next
    edit "Applied-API"
        set type fqdn
        set fqdn "api.appliedsystems.com"
    next
    edit "PowerBI-Service"
        set type fqdn
        set fqdn "*.powerbi.com"
    next
end

# 3. Create firewall policy for analytics VLAN outbound
config firewall policy
    edit 100
        set name "Analytics-to-Cloud"
        set srcintf "analytics-vlan"
        set dstintf "wan1"
        set srcaddr "all"
        set dstaddr "Azure-SQL" "Azure-DataFactory" "InsuredMine-API" "Applied-API" "PowerBI-Service"
        set action accept
        set schedule "always"
        set service "HTTPS" "MSSQL"
        set ssl-ssh-profile "certificate-inspection"
        set logtraffic all
        set comments "Allow analytics traffic to cloud services"
    next
end

# 4. Enable logging for compliance audit trail
config log fortianalyzer setting
    set status enable
end
config log syslogd setting
    set status enable
    set server "<syslog-server-ip>"
end
# Azure SQL - Enable Advanced Threat Protection
az sql db threat-policy update \
  --resource-group rg-insurance-analytics \
  --server sql-insurance-benchmarking \
  --name db-premium-benchmarking \
  --state Enabled \
  --email-addresses security@msp-domain.com agency-principal@agency.com

# Enable Azure SQL Auditing to Log Analytics
az monitor diagnostic-settings create \
  --name sql-audit-logs \
  --resource /subscriptions/<sub-id>/resourceGroups/rg-insurance-analytics/providers/Microsoft.Sql/servers/sql-insurance-benchmarking/databases/db-premium-benchmarking \
  --workspace /subscriptions/<sub-id>/resourceGroups/rg-insurance-analytics/providers/Microsoft.OperationalInsights/workspaces/<workspace-name> \
  --logs '[{"category":"SQLSecurityAuditEvents","enabled":true,"retentionPolicy":{"enabled":true,"days":365}}]'
# Enforce TLS 1.2 minimum on Azure SQL
az sql server update \
  --resource-group rg-insurance-analytics \
  --name sql-insurance-benchmarking \
  --minimal-tls-version 1.2

# Test 1: Verify AMS data extraction
# Run the extraction pipeline manually
az datafactory pipeline create-run \
  --resource-group rg-insurance-analytics \
  --factory-name adf-insurance-etl \
  --name pipeline_daily_policy_sync

# Verify data in Azure SQL
SELECT TOP 20 
    AMSPolicyNumber, ClientName, CarrierName, LineOfBusiness,
    AnnualPremium, PriorTermPremium, PremiumChangePercent,
    RenewalDate, State
FROM dbo.Policies
ORDER BY LastSyncDate DESC;
# Test 2: Verify remarketing flagging
EXEC dbo.usp_FlagAccountsForRemarketing;

# Check flagged accounts
SELECT 
    p.ClientName, p.AMSPolicyNumber, p.CarrierName,
    p.AnnualPremium, p.PriorTermPremium, p.PremiumChangePercent,
    p.RemarketingReason, p.RemarketingStatus,
    t.PremiumIncreaseThresholdPercent as ThresholdUsed
FROM dbo.Policies p
INNER JOIN dbo.RemarketingThresholds t ON p.LineOfBusiness = t.LineOfBusiness
WHERE p.RemarketingFlag = 1
ORDER BY p.PremiumChangePercent DESC;
# Test 3: Verify benchmark comparison accuracy
SELECT 
    p.ClientName, p.LineOfBusiness, p.State,
    p.AnnualPremium as ClientPremium,
    b.MedianPremium as MarketMedian,
    b.P75Premium as Market75thPctile,
    CASE WHEN p.AnnualPremium > b.P75Premium THEN 'ABOVE MARKET' ELSE 'WITHIN MARKET' END as BenchmarkStatus
FROM dbo.Policies p
INNER JOIN dbo.MarketBenchmarks b ON 
    p.LineOfBusiness = b.LineOfBusiness AND p.State = b.State
WHERE b.BenchmarkPeriod = (SELECT MAX(BenchmarkPeriod) FROM dbo.MarketBenchmarks)
ORDER BY (p.AnnualPremium - b.P75Premium) DESC;
# Test 4: Verify InsuredMine deal creation
# Check that flagged policies appear as deals in InsuredMine
curl -X GET 'https://api.insuredmine.com/v1/deals?pipeline=Premium%20Remarketing%20Pipeline&stage=Flagged%20for%20Review' \
  -H 'Authorization: Bearer <insuredmine-api-key>' | python -m json.tool

-- Stored Procedure: dbo.usp_CalculateRemarketingScore
-- Calculates a 0-100 remarketing urgency score for each policy
-- Higher score = more urgent remarketing candidate

CREATE PROCEDURE dbo.usp_CalculateRemarketingScore
AS
BEGIN
    SET NOCOUNT ON;

    -- Add RemarketingScore column if not exists
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Policies') AND name = 'RemarketingScore')
        ALTER TABLE dbo.Policies ADD RemarketingScore DECIMAL(5,2) DEFAULT 0;

    UPDATE p SET RemarketingScore = (
        -- Component 1: Premium Increase Score (0-30 points)
        -- 0% increase = 0 points, 30%+ increase = 30 points, linear interpolation
        CASE 
            WHEN p.PremiumChangePercent >= 30 THEN 30.0
            WHEN p.PremiumChangePercent > 0 THEN p.PremiumChangePercent * 1.0
            ELSE 0.0
        END
        +
        -- Component 2: Market Benchmark Deviation Score (0-25 points)
        -- At or below median = 0, at P75 = 12.5, at 2x median = 25
        CASE
            WHEN b.MedianPremium IS NULL THEN 0.0  -- no benchmark data
            WHEN p.AnnualPremium <= b.MedianPremium THEN 0.0
            WHEN p.AnnualPremium >= b.MedianPremium * 2 THEN 25.0
            ELSE ROUND(((p.AnnualPremium - b.MedianPremium) / NULLIF(b.MedianPremium, 0)) * 25.0, 2)
        END
        +
        -- Component 3: Account Value Score (0-20 points)
        -- Higher total account premium = higher priority for retention
        CASE
            WHEN ISNULL(p.TotalAccountPremium, p.AnnualPremium) >= 25000 THEN 20.0
            WHEN ISNULL(p.TotalAccountPremium, p.AnnualPremium) >= 10000 THEN 15.0
            WHEN ISNULL(p.TotalAccountPremium, p.AnnualPremium) >= 5000 THEN 10.0
            WHEN ISNULL(p.TotalAccountPremium, p.AnnualPremium) >= 2000 THEN 5.0
            ELSE 2.0
        END
        +
        -- Component 4: Renewal Urgency Score (0-15 points)
        -- Closer to renewal = more urgent
        CASE
            WHEN DATEDIFF(DAY, GETDATE(), p.RenewalDate) <= 14 THEN 15.0
            WHEN DATEDIFF(DAY, GETDATE(), p.RenewalDate) <= 30 THEN 12.0
            WHEN DATEDIFF(DAY, GETDATE(), p.RenewalDate) <= 60 THEN 8.0
            WHEN DATEDIFF(DAY, GETDATE(), p.RenewalDate) <= 90 THEN 4.0
            ELSE 1.0
        END
        +
        -- Component 5: Claims Favorability Score (0-10 points)
        -- Fewer claims = easier to remarket, higher score
        CASE
            WHEN ISNULL(p.ClaimsCount, 0) = 0 THEN 10.0
            WHEN p.ClaimsCount = 1 THEN 6.0
            WHEN p.ClaimsCount = 2 THEN 3.0
            ELSE 0.0  -- 3+ claims, very hard to remarket
        END
    ),
    ModifiedDate = GETUTCDATE()
    FROM dbo.Policies p
    LEFT JOIN dbo.MarketBenchmarks b ON 
        p.LineOfBusiness = b.LineOfBusiness 
        AND p.State = b.State
        AND b.BenchmarkPeriod = (
            SELECT MAX(BenchmarkPeriod) FROM dbo.MarketBenchmarks 
            WHERE LineOfBusiness = p.LineOfBusiness AND State = p.State
        )
    WHERE p.RemarketingFlag = 1;

    -- Output top remarketing candidates
    SELECT TOP 50
        p.ClientName, p.AMSPolicyNumber, p.CarrierName,
        p.LineOfBusiness, p.State,
        p.AnnualPremium, p.PriorTermPremium, p.PremiumChangePercent,
        p.RemarketingScore,
        p.RenewalDate,
        DATEDIFF(DAY, GETDATE(), p.RenewalDate) as DaysToRenewal,
        p.ClaimsCount,
        p.TotalAccountPremium,
        b.MedianPremium as MarketMedian,
        b.P75Premium as MarketP75
    FROM dbo.Policies p
    LEFT JOIN dbo.MarketBenchmarks b ON 
        p.LineOfBusiness = b.LineOfBusiness AND p.State = b.State
        AND b.BenchmarkPeriod = (SELECT MAX(BenchmarkPeriod) FROM dbo.MarketBenchmarks WHERE LineOfBusiness = p.LineOfBusiness AND State = p.State)
    WHERE p.RemarketingFlag = 1
    ORDER BY p.RemarketingScore DESC;
END;
GO

# Azure Function with timer trigger running daily at 7:30 AM weekdays

# renewal_intelligence_agent.py
# Azure Function - Timer Trigger: runs daily at 7:30 AM

import azure.functions as func
import pyodbc
import os
from openai import AzureOpenAI
from datetime import datetime
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

def main(timer: func.TimerRequest) -> None:
    sql_conn = pyodbc.connect(os.environ['SQL_CONNECTION_STRING'])
    
    client = AzureOpenAI(
        api_key=os.environ['AZURE_OPENAI_API_KEY'],
        api_version='2024-06-01',
        azure_endpoint=os.environ['AZURE_OPENAI_ENDPOINT']
    )
    
    # Get unique producers with flagged accounts
    cursor = sql_conn.cursor()
    cursor.execute("""
        SELECT DISTINCT ProducerName, ProducerEmail
        FROM dbo.Policies p
        INNER JOIN dbo.ProducerContacts pc ON p.ProducerCode = pc.ProducerCode
        WHERE p.RemarketingFlag = 1 
        AND p.RemarketingStatus = 'FLAGGED'
        AND p.RenewalDate BETWEEN GETDATE() AND DATEADD(DAY, 90, GETDATE())
    """)
    producers = cursor.fetchall()
    
    for producer in producers:
        producer_name = producer.ProducerName
        producer_email = producer.ProducerEmail
        
        # Get top 5 remarketing priorities for this producer
        cursor.execute("""
            SELECT TOP 5
                p.ClientName, p.AMSPolicyNumber, p.CarrierName,
                p.LineOfBusiness, p.AnnualPremium, p.PriorTermPremium,
                p.PremiumChangePercent, p.RemarketingScore,
                p.RenewalDate, p.TotalAccountPremium,
                p.ClaimsCount, p.AccountTenureYears, p.State,
                b.MedianPremium, b.P75Premium,
                p.RemarketingReason
            FROM dbo.Policies p
            LEFT JOIN dbo.MarketBenchmarks b ON 
                p.LineOfBusiness = b.LineOfBusiness AND p.State = b.State
                AND b.BenchmarkPeriod = (SELECT MAX(BenchmarkPeriod) FROM dbo.MarketBenchmarks WHERE LineOfBusiness = p.LineOfBusiness AND State = p.State)
            WHERE p.ProducerName = ?
            AND p.RemarketingFlag = 1
            AND p.RemarketingStatus = 'FLAGGED'
            ORDER BY p.RemarketingScore DESC
        """, (producer_name,))
        
        accounts = cursor.fetchall()
        if not accounts:
            continue
        
        # Build context for GPT
        account_summaries = []
        for a in accounts:
            summary = {
                'client': a.ClientName,
                'policy': a.AMSPolicyNumber,
                'carrier': a.CarrierName,
                'lob': a.LineOfBusiness,
                'current_premium': float(a.AnnualPremium),
                'prior_premium': float(a.PriorTermPremium) if a.PriorTermPremium else None,
                'change_pct': float(a.PremiumChangePercent),
                'score': float(a.RemarketingScore),
                'renewal_date': a.RenewalDate.strftime('%B %d, %Y'),
                'total_account': float(a.TotalAccountPremium) if a.TotalAccountPremium else None,
                'claims': int(a.ClaimsCount) if a.ClaimsCount else 0,
                'tenure_years': int(a.AccountTenureYears) if a.AccountTenureYears else None,
                'market_median': float(a.MedianPremium) if a.MedianPremium else None,
                'market_p75': float(a.P75Premium) if a.P75Premium else None,
                'reason': a.RemarketingReason
            }
            account_summaries.append(summary)
        
        # Generate AI briefing
        prompt = f"""You are an insurance agency operations assistant. Generate a morning briefing email 
for producer {producer_name} summarizing their top remarketing priority accounts.

For each account, provide:
1. A 2-sentence summary of why this account needs attention
2. A specific recommended action (e.g., 'Submit to Travelers and Hartford for competitive quotes')
3. Any risk factors to be aware of (claims, tenure, etc.)

Be concise, professional, and action-oriented. Use dollar amounts and percentages.
Do not fabricate carrier names for recommendations — use general advice like 'submit to your top 3 appointed carriers for this line'.

Accounts to summarize:
{json.dumps(account_summaries, indent=2, default=str)}

Format as an HTML email body with a professional layout. Use a table for the account summary 
and bullet points for action items. Include a header 'Your Remarketing Priority Briefing - {datetime.now().strftime("%A, %B %d, %Y")}'.
"""
        
        response = client.chat.completions.create(
            model='gpt-5.4',
            messages=[
                {'role': 'system', 'content': 'You are an insurance industry expert assistant that helps producers prioritize their renewal book and identify remarketing opportunities. Be specific, data-driven, and actionable.'},
                {'role': 'user', 'content': prompt}
            ],
            temperature=0.3,
            max_tokens=2000
        )
        
        email_body = response.choices[0].message.content
        
        # Send email via SMTP (or Microsoft Graph API)
        send_briefing_email(producer_email, producer_name, email_body)
    
    sql_conn.close()


def send_briefing_email(to_email, producer_name, html_body):
    smtp_server = os.environ.get('SMTP_SERVER', 'smtp.office365.com')
    smtp_port = int(os.environ.get('SMTP_PORT', 587))
    smtp_user = os.environ['SMTP_USER']
    smtp_pass = os.environ['SMTP_PASS']
    
    msg = MIMEMultipart('alternative')
    msg['Subject'] = f'Remarketing Priority Briefing - {datetime.now().strftime("%A, %B %d")}'
    msg['From'] = smtp_user
    msg['To'] = to_email
    
    msg.attach(MIMEText(html_body, 'html'))
    
    with smtplib.SMTP(smtp_server, smtp_port) as server:
        server.starttls()
        server.login(smtp_user, smtp_pass)
        server.sendmail(smtp_user, to_email, msg.as_string())

-- Weekly anomaly detection stored procedure
CREATE PROCEDURE dbo.usp_DetectPremiumAnomalies
AS
BEGIN
    SET NOCOUNT ON;

    -- Create temp table for results
    CREATE TABLE #Anomalies (
        AnomalyType NVARCHAR(50),
        Severity NVARCHAR(10), -- 'HIGH', 'MEDIUM', 'LOW'
        Description NVARCHAR(MAX),
        AffectedPolicies INT,
        AffectedPremium DECIMAL(14,2),
        RecommendedAction NVARCHAR(MAX)
    );

    -- Anomaly 1: Carrier with above-average rate increases
    INSERT INTO #Anomalies
    SELECT 
        'CARRIER_RATE_SPIKE' as AnomalyType,
        CASE 
            WHEN AVG(PremiumChangePercent) > 25 THEN 'HIGH'
            WHEN AVG(PremiumChangePercent) > 15 THEN 'MEDIUM'
            ELSE 'LOW'
        END as Severity,
        CONCAT(CarrierName, ' is averaging ', ROUND(AVG(PremiumChangePercent), 1), 
               '% premium increases across ', COUNT(*), ' policies in ', LineOfBusiness,
               '. Market average is ', 
               ISNULL((SELECT TOP 1 ROUND(AVG(AvgPremiumChangePercent), 1) FROM dbo.MarketBenchmarks WHERE LineOfBusiness = p.LineOfBusiness), 'N/A'),
               '%.') as Description,
        COUNT(*) as AffectedPolicies,
        SUM(AnnualPremium) as AffectedPremium,
        CONCAT('Consider reducing exposure to ', CarrierName, ' for ', LineOfBusiness, 
               '. Evaluate appointing alternative carriers in this space.') as RecommendedAction
    FROM dbo.Policies p
    WHERE PremiumChangePercent > 10
    AND RenewalDate BETWEEN GETDATE() AND DATEADD(MONTH, 6, GETDATE())
    GROUP BY CarrierName, LineOfBusiness
    HAVING COUNT(*) >= 5 AND AVG(PremiumChangePercent) > 15;

    -- Anomaly 2: Geographic rate concentration
    INSERT INTO #Anomalies
    SELECT
        'GEO_RATE_CONCENTRATION' as AnomalyType,
        CASE 
            WHEN AVG(PremiumChangePercent) > 20 THEN 'HIGH'
            WHEN AVG(PremiumChangePercent) > 12 THEN 'MEDIUM'
            ELSE 'LOW'
        END,
        CONCAT('Premiums in ', State, ' for ', LineOfBusiness, ' are averaging ',
               ROUND(AVG(PremiumChangePercent), 1), '% increases across ', COUNT(*), ' policies.',
               ' This may indicate a hardening market in this geography.'),
        COUNT(*),
        SUM(AnnualPremium),
        CONCAT('Proactively remarket ', LineOfBusiness, ' policies in ', State,
               ' before renewal. Consider surplus lines carriers for difficult placements.')
    FROM dbo.Policies
    WHERE PremiumChangePercent > 8
    AND RenewalDate BETWEEN GETDATE() AND DATEADD(MONTH, 6, GETDATE())
    GROUP BY State, LineOfBusiness
    HAVING COUNT(*) >= 3 AND AVG(PremiumChangePercent) > 12;

    -- Anomaly 3: Consecutive annual increases (multi-year pain)
    INSERT INTO #Anomalies
    SELECT
        'CONSECUTIVE_INCREASES' as AnomalyType,
        'HIGH',
        CONCAT(p.ClientName, ' (', p.AMSPolicyNumber, ') has had premium increases for ',
               consecutive_years.YearsOfIncreases, ' consecutive years. Current premium $',
               FORMAT(p.AnnualPremium, 'N2'), ', cumulative increase of ',
               ROUND(consecutive_years.CumulativeIncrease, 1), '%.'),
        1,
        p.AnnualPremium,
        CONCAT('High retention risk. Schedule proactive outreach to ', p.ClientName,
               ' with competitive alternatives before the next renewal on ', FORMAT(p.RenewalDate, 'MMM dd, yyyy'), '.')
    FROM dbo.Policies p
    CROSS APPLY (
        SELECT 
            COUNT(*) as YearsOfIncreases,
            ROUND((EXP(SUM(LOG(1 + ph.PremiumChangePct/100.0))) - 1) * 100, 1) as CumulativeIncrease
        FROM (
            SELECT 
                ROW_NUMBER() OVER (ORDER BY TermEffectiveDate DESC) as rn,
                (TermPremium - LAG(TermPremium) OVER (ORDER BY TermEffectiveDate)) / 
                    NULLIF(LAG(TermPremium) OVER (ORDER BY TermEffectiveDate), 0) * 100 as PremiumChangePct
            FROM dbo.PremiumHistory
            WHERE PolicyID = p.PolicyID
        ) ph
        WHERE ph.PremiumChangePct > 0 AND ph.rn <= 3
        HAVING COUNT(*) >= 2
    ) consecutive_years
    WHERE p.RenewalDate BETWEEN GETDATE() AND DATEADD(DAY, 120, GETDATE());

    -- Anomaly 4: Accounts significantly above market benchmark
    INSERT INTO #Anomalies
    SELECT
        'BENCHMARK_OUTLIER' as AnomalyType,
        CASE 
            WHEN p.AnnualPremium > b.P75Premium * 1.5 THEN 'HIGH'
            ELSE 'MEDIUM'
        END,
        CONCAT(p.ClientName, '''s ', p.LineOfBusiness, ' premium of $', FORMAT(p.AnnualPremium, 'N2'),
               ' is ', ROUND(((p.AnnualPremium - b.MedianPremium) / NULLIF(b.MedianPremium, 0)) * 100, 0),
               '% above the market median of $', FORMAT(b.MedianPremium, 'N2'),
               ' and exceeds the 75th percentile ($', FORMAT(b.P75Premium, 'N2'), ').'),
        1,
        p.AnnualPremium,
        CONCAT('Strong remarketing candidate. Current carrier may not be competitive for this risk profile. Submit to top 3 alternative carriers.')
    FROM dbo.Policies p
    INNER JOIN dbo.MarketBenchmarks b ON p.LineOfBusiness = b.LineOfBusiness AND p.State = b.State
        AND b.BenchmarkPeriod = (SELECT MAX(BenchmarkPeriod) FROM dbo.MarketBenchmarks WHERE LineOfBusiness = p.LineOfBusiness AND State = p.State)
    WHERE p.AnnualPremium > b.P75Premium * 1.25
    AND p.RenewalDate BETWEEN GETDATE() AND DATEADD(DAY, 120, GETDATE());

    -- Output anomalies
    SELECT * FROM #Anomalies ORDER BY 
        CASE Severity WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 ELSE 3 END,
        AffectedPremium DESC;

    DROP TABLE #Anomalies;
END;
GO

# Configurable AMS data normalization module

# ams_normalizer.py
# Configurable AMS data normalization module

import json
from datetime import datetime
from typing import Dict, List, Optional

# AMS Field Mapping Configuration
AMS_FIELD_MAPPINGS = {
    'EPIC': {
        'policy_number': 'policyNumber',
        'client_name': 'insuredName',
        'carrier_code': 'carrierCode',
        'carrier_name': 'carrierName',
        'line_of_business': 'lineOfBusiness',
        'coverage_type': 'coverageDescription',
        'effective_date': 'effectiveDate',
        'expiration_date': 'expirationDate',
        'annual_premium': 'annualPremium',
        'prior_premium': 'priorTermPremium',
        'state': 'ratingState',
        'zip_code': 'zipCode',
        'producer_code': 'producerCode',
        'producer_name': 'producerName',
        'industry_code': 'naicsCode',
        'claims_count': 'claimCount',
        'claims_total': 'totalIncurred',
        'account_premium': 'totalAccountPremium',
    },
    'AMS360': {
        'policy_number': 'PolicyNumber',
        'client_name': 'InsuredName',
        'carrier_code': 'CompanyCode',
        'carrier_name': 'CompanyName',
        'line_of_business': 'PolicyType',
        'coverage_type': 'CoverageCode',
        'effective_date': 'EffDate',
        'expiration_date': 'ExpDate',
        'annual_premium': 'WrittenPremium',
        'prior_premium': 'PriorPremium',
        'state': 'StateCode',
        'zip_code': 'Zip',
        'producer_code': 'ExecCode',
        'producer_name': 'ExecName',
        'industry_code': 'SICCode',
        'claims_count': 'NumberOfClaims',
        'claims_total': 'TotalPaid',
        'account_premium': 'AccountPremium',
    },
    'EZLYNX': {
        'policy_number': 'policy_num',
        'client_name': 'named_insured',
        'carrier_code': 'carrier_id',
        'carrier_name': 'carrier_name',
        'line_of_business': 'line_of_business',
        'coverage_type': 'coverage_desc',
        'effective_date': 'eff_date',
        'expiration_date': 'exp_date',
        'annual_premium': 'total_premium',
        'prior_premium': 'prior_term_premium',
        'state': 'state',
        'zip_code': 'zip',
        'producer_code': 'agent_code',
        'producer_name': 'agent_name',
        'industry_code': None,
        'claims_count': 'num_claims',
        'claims_total': 'total_claims_paid',
        'account_premium': 'household_premium',
    },
    'HAWKSOFT': {
        'policy_number': 'PolicyNum',
        'client_name': 'ClientName',
        'carrier_code': 'CoCode',
        'carrier_name': 'CompanyName',
        'line_of_business': 'LOB',
        'coverage_type': 'Type',
        'effective_date': 'EffectiveDate',
        'expiration_date': 'ExpirationDate',
        'annual_premium': 'Premium',
        'prior_premium': 'PriorPremium',
        'state': 'State',
        'zip_code': 'ZipCode',
        'producer_code': 'AgentCode',
        'producer_name': 'AgentName',
        'industry_code': None,
        'claims_count': None,
        'claims_total': None,
        'account_premium': None,
    }
}

# Line of Business standardization mapping
LOB_STANDARDIZATION = {
    # Applied Epic
    'Personal Auto': 'PERSONAL_AUTO',
    'Personal Automobile': 'PERSONAL_AUTO',
    'Homeowner': 'HOMEOWNERS',
    'Homeowners': 'HOMEOWNERS',
    'Home': 'HOMEOWNERS',
    'Dwelling Fire': 'HOMEOWNERS',
    'Commercial Property': 'COMMERCIAL_PROPERTY',
    'Commercial Auto': 'COMMERCIAL_AUTO',
    'Commercial Automobile': 'COMMERCIAL_AUTO',
    'General Liability': 'COMMERCIAL_GL',
    'CGL': 'COMMERCIAL_GL',
    'Workers Compensation': 'WORKERS_COMP',
    'Workers Comp': 'WORKERS_COMP',
    'WC': 'WORKERS_COMP',
    'BOP': 'BOP',
    'Businessowners': 'BOP',
    'Business Owners': 'BOP',
    'Umbrella': 'UMBRELLA',
    'Excess Liability': 'UMBRELLA',
    'Commercial Umbrella': 'UMBRELLA',
    'Inland Marine': 'INLAND_MARINE',
    'Professional Liability': 'PROFESSIONAL_LIABILITY',
    'E&O': 'PROFESSIONAL_LIABILITY',
    'D&O': 'DIRECTORS_OFFICERS',
    'Cyber': 'CYBER_LIABILITY',
    'EPLI': 'EPLI',
    # AMS360 codes
    'PA': 'PERSONAL_AUTO',
    'HO': 'HOMEOWNERS',
    'CP': 'COMMERCIAL_PROPERTY',
    'CA': 'COMMERCIAL_AUTO',
    'GL': 'COMMERCIAL_GL',
    'WK': 'WORKERS_COMP',
    'BP': 'BOP',
    'UM': 'UMBRELLA',
    'IM': 'INLAND_MARINE',
}


class AMSNormalizer:
    def __init__(self, ams_type: str):
        if ams_type not in AMS_FIELD_MAPPINGS:
            raise ValueError(f'Unsupported AMS: {ams_type}. Supported: {list(AMS_FIELD_MAPPINGS.keys())}')
        self.ams_type = ams_type
        self.mapping = AMS_FIELD_MAPPINGS[ams_type]
    
    def normalize_policy(self, raw_data: Dict) -> Dict:
        """Transform a raw AMS policy record into the normalized warehouse schema."""
        normalized = {
            'AMSPolicyNumber': self._get_field(raw_data, 'policy_number'),
            'AMSSource': self.ams_type,
            'ClientName': self._get_field(raw_data, 'client_name', ''),
            'CarrierCode': self._get_field(raw_data, 'carrier_code', ''),
            'CarrierName': self._get_field(raw_data, 'carrier_name', ''),
            'LineOfBusiness': self._standardize_lob(self._get_field(raw_data, 'line_of_business', '')),
            'CoverageType': self._get_field(raw_data, 'coverage_type', ''),
            'PolicyEffectiveDate': self._parse_date(self._get_field(raw_data, 'effective_date')),
            'PolicyExpirationDate': self._parse_date(self._get_field(raw_data, 'expiration_date')),
            'AnnualPremium': self._parse_decimal(self._get_field(raw_data, 'annual_premium', 0)),
            'PriorTermPremium': self._parse_decimal(self._get_field(raw_data, 'prior_premium')),
            'State': self._standardize_state(self._get_field(raw_data, 'state', '')),
            'ZipCode': str(self._get_field(raw_data, 'zip_code', '')).strip()[:10],
            'ProducerCode': self._get_field(raw_data, 'producer_code', ''),
            'ProducerName': self._get_field(raw_data, 'producer_name', ''),
            'IndustryCode': self._get_field(raw_data, 'industry_code'),
            'ClaimsCount': self._parse_int(self._get_field(raw_data, 'claims_count', 0)),
            'ClaimsAmountTotal': self._parse_decimal(self._get_field(raw_data, 'claims_total', 0)),
            'TotalAccountPremium': self._parse_decimal(self._get_field(raw_data, 'account_premium')),
        }
        
        # Calculate renewal date (= expiration date for renewal policies)
        normalized['RenewalDate'] = normalized['PolicyExpirationDate']
        
        # Validate required fields
        if not normalized['AMSPolicyNumber']:
            raise ValueError('Policy number is required')
        if normalized['AnnualPremium'] is None or normalized['AnnualPremium'] < 0:
            raise ValueError(f'Invalid premium for policy {normalized["AMSPolicyNumber"]}')
        
        return normalized
    
    def normalize_batch(self, raw_records: List[Dict]) -> List[Dict]:
        """Normalize a batch of raw AMS records, skipping invalid ones."""
        results = []
        errors = []
        for record in raw_records:
            try:
                normalized = self.normalize_policy(record)
                if normalized['AnnualPremium'] > 0:  # Skip $0 premium policies
                    results.append(normalized)
            except (ValueError, KeyError) as e:
                errors.append({'record': record, 'error': str(e)})
        
        if errors:
            print(f'WARNING: {len(errors)} records failed normalization out of {len(raw_records)}')
            for err in errors[:5]:
                print(f'  - {err["error"]}')
        
        return results
    
    def _get_field(self, data: Dict, standard_field: str, default=None):
        ams_field = self.mapping.get(standard_field)
        if ams_field is None:
            return default
        return data.get(ams_field, default)
    
    def _standardize_lob(self, lob_value: str) -> str:
        if not lob_value:
            return 'OTHER'
        cleaned = lob_value.strip()
        return LOB_STANDARDIZATION.get(cleaned, cleaned.upper().replace(' ', '_'))
    
    def _standardize_state(self, state: str) -> str:
        if not state:
            return ''
        return state.strip().upper()[:2]
    
    def _parse_date(self, value) -> Optional[str]:
        if value is None:
            return None
        if isinstance(value, datetime):
            return value.strftime('%Y-%m-%d')
        if isinstance(value, str):
            for fmt in ('%Y-%m-%d', '%m/%d/%Y', '%Y-%m-%dT%H:%M:%S', '%Y-%m-%dT%H:%M:%SZ'):
                try:
                    return datetime.strptime(value.strip(), fmt).strftime('%Y-%m-%d')
                except ValueError:
                    continue
        return str(value)[:10]
    
    def _parse_decimal(self, value) -> Optional[float]:
        if value is None:
            return None
        if isinstance(value, (int, float)):
            return round(float(value), 2)
        if isinstance(value, str):
            cleaned = value.replace('$', '').replace(',', '').strip()
            if cleaned:
                return round(float(cleaned), 2)
        return None
    
    def _parse_int(self, value) -> int:
        if value is None:
            return 0
        return int(float(str(value).strip() or 0))


# Usage example:
# normalizer = AMSNormalizer('EPIC')
# raw_policies = epic_api.get_policies(modified_since='2025-01-01')
# normalized = normalizer.normalize_batch(raw_policies)
# warehouse.upsert(normalized)