#!/bin/bash

# ============================================================================
# Salesforce Security Audit Report Generation Script
# Version: 1.0
# Purpose: Automate extraction of SOQL data and generate security reports
# ============================================================================

# Color codes for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Configuration
ORG_ALIAS="${1:-production}"
OUTPUT_DIR="./security-reports-$(date +%Y%m%d)"
TEMP_DIR="./temp-query-results"
TIMESTAMP=$(date +"%Y-%m-%d %H:%M:%S")
REPORT_DATE=$(date +"%Y-%m-%d")

# Create directories
mkdir -p "$OUTPUT_DIR"
mkdir -p "$TEMP_DIR"

echo -e "${BLUE}========================================${NC}"
echo -e "${BLUE}Salesforce Security Audit Report Generator${NC}"
echo -e "${BLUE}========================================${NC}"
echo -e "Organization: ${GREEN}$ORG_ALIAS${NC}"
echo -e "Report Date: ${GREEN}$REPORT_DATE${NC}"
echo ""

# ============================================================================
# FUNCTION: Execute SOQL Query and Save Results
# ============================================================================
execute_query() {
    local query_name=$1
    local query=$2
    local output_file="$TEMP_DIR/${query_name}.csv"

    echo -e "${YELLOW}Executing: ${query_name}...${NC}"

    # Execute SOQL query using Salesforce CLI
    sfdx data:query --query "$query" \
        --target-org "$ORG_ALIAS" \
        --result-format csv \
        > "$output_file" 2>/dev/null

    if [ $? -eq 0 ]; then
        echo -e "${GREEN}✓ ${query_name} completed${NC}"
        return 0
    else
        echo -e "${RED}✗ ${query_name} failed${NC}"
        return 1
    fi
}

# ============================================================================
# SECTION 1: CRITICAL SECURITY METRICS
# ============================================================================
echo -e "\n${BLUE}[1/5] Extracting Critical Security Metrics...${NC}"

# System Administrator Analysis
execute_query "system_admins" \
"SELECT Username, Email, FirstName, LastName, IsActive, TwoFactorEnabled, LastLoginDate, CreatedDate \
FROM User \
WHERE Profile.Name = 'System Administrator' \
ORDER BY IsActive DESC, LastLoginDate DESC"

# MFA Coverage Analysis
execute_query "mfa_coverage" \
"SELECT Profile.Name, COUNT(Id), \
COUNT(CASE WHEN TwoFactorEnabled = true THEN 1 END) \
FROM User \
WHERE IsActive = true \
GROUP BY Profile.Name"

# Critical Permissions
execute_query "view_all_data" \
"SELECT Id, Name FROM Profile WHERE PermissionsViewAllData = true"

execute_query "modify_all_data" \
"SELECT Id, Name FROM Profile WHERE PermissionsModifyAllData = true"

# ============================================================================
# SECTION 2: LAYER-BASED ASSESSMENT
# ============================================================================
echo -e "\n${BLUE}[2/5] Performing Layer-Based Assessment...${NC}"

# Foundation Layer
execute_query "password_policies" \
"SELECT Name, MinPasswordLength, PasswordComplexity, PasswordExpiration, PasswordHistory \
FROM PasswordPolicy"

execute_query "login_ip_ranges" \
"SELECT Profile.Name, IpStartAddress, IpEndAddress \
FROM LoginIpRange"

# User Experience Layer
execute_query "pii_fields" \
"SELECT EntityDefinition.QualifiedApiName, QualifiedApiName, DataType, IsEncrypted \
FROM FieldDefinition \
WHERE DataType IN ('Email', 'Phone', 'Address')"

# Application Logic Layer
execute_query "apex_sharing" \
"SELECT Name, ApiVersion, CreatedDate, LastModifiedDate \
FROM ApexClass \
WHERE Body LIKE '%without sharing%' \
AND Status = 'Active'"

execute_query "flow_security" \
"SELECT ApiName, ProcessType, RunInMode \
FROM FlowDefinitionView \
WHERE IsActive = true"

# Data Layer
execute_query "data_export_users" \
"SELECT Username, Profile.Name \
FROM User \
WHERE Profile.PermissionsDataExport = true \
AND IsActive = true"

# Integration Layer
execute_query "connected_apps" \
"SELECT Name, CreatedDate \
FROM ConnectedApplication"

execute_query "api_users" \
"SELECT Username, Profile.Name, LastLoginDate \
FROM User \
WHERE Profile.PermissionsApiUserOnly = true"

# ============================================================================
# SECTION 3: COMPLIANCE DATA
# ============================================================================
echo -e "\n${BLUE}[3/5] Gathering Compliance Data...${NC}"

execute_query "gdpr_pii_fields" \
"SELECT COUNT(Id) \
FROM FieldDefinition \
WHERE DataType IN ('Email', 'Phone', 'Address') \
AND IsEncrypted = false"

execute_query "audit_trail_status" \
"SELECT Id, Name \
FROM SetupAuditTrail \
WHERE CreatedDate = LAST_N_DAYS:7 \
LIMIT 10"

# ============================================================================
# SECTION 4: GENERATE DASHBOARD REPORT
# ============================================================================
echo -e "\n${BLUE}[4/5] Generating Dashboard Report...${NC}"

# Process CSV files and generate dashboard
python3 << 'EOF'
import csv
import json
import os
from datetime import datetime

def read_csv(filename):
    """Read CSV file and return data"""
    filepath = f"./temp-query-results/{filename}.csv"
    if not os.path.exists(filepath):
        return []

    with open(filepath, 'r') as f:
        reader = csv.DictReader(f)
        return list(reader)

def calculate_metrics():
    """Calculate security metrics from query results"""
    metrics = {}

    # System Admin metrics
    sys_admins = read_csv("system_admins")
    total_admins = len(sys_admins)
    mfa_enabled = sum(1 for admin in sys_admins if admin.get('TwoFactorEnabled') == 'true')

    metrics['sys_admin_total'] = total_admins
    metrics['sys_admin_mfa'] = mfa_enabled
    metrics['sys_admin_no_mfa'] = total_admins - mfa_enabled
    metrics['sys_admin_mfa_percent'] = round((mfa_enabled / total_admins * 100) if total_admins > 0 else 0, 2)

    # Critical permissions
    view_all = read_csv("view_all_data")
    modify_all = read_csv("modify_all_data")

    metrics['profiles_view_all'] = len(view_all)
    metrics['profiles_modify_all'] = len(modify_all)

    # PII fields
    pii_fields = read_csv("pii_fields")
    encrypted_pii = sum(1 for field in pii_fields if field.get('IsEncrypted') == 'true')

    metrics['pii_total'] = len(pii_fields)
    metrics['pii_encrypted'] = encrypted_pii
    metrics['pii_unencrypted'] = len(pii_fields) - encrypted_pii

    # Apex security
    apex_issues = read_csv("apex_sharing")
    metrics['apex_without_sharing'] = len(apex_issues)

    # Calculate overall score
    score = 100
    score -= metrics['sys_admin_no_mfa'] * 5  # -5 points per admin without MFA
    score -= metrics['profiles_modify_all'] * 3  # -3 points per profile with Modify All
    score -= metrics['apex_without_sharing'] * 2  # -2 points per class without sharing
    score -= (metrics['pii_unencrypted'] / max(metrics['pii_total'], 1)) * 20  # Up to -20 for unencrypted PII

    metrics['overall_score'] = max(0, min(100, score))

    # Determine risk level
    if metrics['overall_score'] >= 85:
        metrics['risk_level'] = 'LOW'
    elif metrics['overall_score'] >= 70:
        metrics['risk_level'] = 'MEDIUM'
    elif metrics['overall_score'] >= 50:
        metrics['risk_level'] = 'HIGH'
    else:
        metrics['risk_level'] = 'CRITICAL'

    return metrics

# Calculate metrics
metrics = calculate_metrics()

# Save metrics to JSON
with open('./temp-query-results/metrics.json', 'w') as f:
    json.dump(metrics, f, indent=2)

print(f"Overall Security Score: {metrics['overall_score']}/100")
print(f"Risk Level: {metrics['risk_level']}")
print(f"Critical Issues Found: {metrics['sys_admin_no_mfa'] + metrics['profiles_modify_all']}")
EOF

# ============================================================================
# SECTION 5: CREATE FINAL REPORTS
# ============================================================================
echo -e "\n${BLUE}[5/5] Creating Final Reports...${NC}"

# Read metrics
if [ -f "./temp-query-results/metrics.json" ]; then
    METRICS=$(cat ./temp-query-results/metrics.json)
    SCORE=$(echo $METRICS | python3 -c "import sys, json; print(json.load(sys.stdin)['overall_score'])")
    RISK=$(echo $METRICS | python3 -c "import sys, json; print(json.load(sys.stdin)['risk_level'])")
else
    SCORE="N/A"
    RISK="UNKNOWN"
fi

# Generate Executive Summary
cat > "$OUTPUT_DIR/executive-summary.md" << EOSUM
# Security Audit Executive Summary
**Generated**: $TIMESTAMP
**Organization**: $ORG_ALIAS

## Overall Security Score: $SCORE/100
**Risk Level**: $RISK

## Key Findings
$(python3 -c "
import json
metrics = json.load(open('./temp-query-results/metrics.json'))
print(f'- System Admins without MFA: {metrics[\"sys_admin_no_mfa\"]}')
print(f'- Profiles with Modify All Data: {metrics[\"profiles_modify_all\"]}')
print(f'- Unencrypted PII Fields: {metrics[\"pii_unencrypted\"]}/{metrics[\"pii_total\"]}')
print(f'- Apex Classes without Sharing: {metrics[\"apex_without_sharing\"]}')
")

## Recommendations
1. Enable MFA for all System Administrators immediately
2. Review and reduce Modify All Data permissions
3. Encrypt all PII fields using Platform Encryption
4. Update Apex classes to use 'with sharing' keyword

## Next Steps
- Review detailed reports in this directory
- Schedule remediation tasks based on priority
- Re-run audit in 30 days to measure improvement
EOSUM

# Copy query results to output directory
cp -r "$TEMP_DIR"/*.csv "$OUTPUT_DIR/"

# Generate HTML Dashboard (optional)
cat > "$OUTPUT_DIR/dashboard.html" << 'EOHTML'
<!DOCTYPE html>
<html>
<head>
    <title>Security Audit Dashboard</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }
        .header { background: #1f4788; color: white; padding: 20px; border-radius: 5px; }
        .metric-card { background: white; padding: 15px; margin: 10px; border-radius: 5px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); display: inline-block; min-width: 200px; }
        .metric-value { font-size: 36px; font-weight: bold; }
        .metric-label { color: #666; margin-top: 5px; }
        .critical { color: #d32f2f; }
        .warning { color: #f57c00; }
        .good { color: #388e3c; }
        .chart { margin: 20px 0; }
    </style>
</head>
<body>
    <div class="header">
        <h1>Salesforce Security Audit Dashboard</h1>
        <p>Generated: <span id="timestamp"></span></p>
    </div>

    <div class="metrics">
        <div class="metric-card">
            <div class="metric-value" id="score">--</div>
            <div class="metric-label">Security Score</div>
        </div>

        <div class="metric-card">
            <div class="metric-value critical" id="critical">--</div>
            <div class="metric-label">Critical Issues</div>
        </div>

        <div class="metric-card">
            <div class="metric-value warning" id="mfa">--%</div>
            <div class="metric-label">MFA Coverage</div>
        </div>
    </div>

    <script>
        // Load metrics from JSON file
        fetch('metrics.json')
            .then(response => response.json())
            .then(data => {
                document.getElementById('score').textContent = data.overall_score;
                document.getElementById('critical').textContent = data.sys_admin_no_mfa + data.profiles_modify_all;
                document.getElementById('mfa').textContent = data.sys_admin_mfa_percent + '%';

                // Color code based on values
                const scoreEl = document.getElementById('score');
                if (data.overall_score >= 85) scoreEl.className = 'metric-value good';
                else if (data.overall_score >= 70) scoreEl.className = 'metric-value warning';
                else scoreEl.className = 'metric-value critical';
            });

        document.getElementById('timestamp').textContent = new Date().toLocaleString();
    </script>
</body>
</html>
EOHTML

# Copy metrics to output
cp "$TEMP_DIR/metrics.json" "$OUTPUT_DIR/"

# Clean up temp directory (optional)
# rm -rf "$TEMP_DIR"

# ============================================================================
# COMPLETION
# ============================================================================
echo -e "\n${GREEN}========================================${NC}"
echo -e "${GREEN}Report Generation Complete!${NC}"
echo -e "${GREEN}========================================${NC}"
echo -e "Reports saved to: ${BLUE}$OUTPUT_DIR${NC}"
echo -e "\nGenerated files:"
echo -e "  - executive-summary.md"
echo -e "  - dashboard.html"
echo -e "  - metrics.json"
echo -e "  - *.csv (raw query results)"
echo -e "\n${YELLOW}Overall Security Score: $SCORE/100${NC}"
echo -e "${YELLOW}Risk Level: $RISK${NC}"
echo -e "\n${GREEN}Next steps:${NC}"
echo -e "  1. Review executive-summary.md for key findings"
echo -e "  2. Open dashboard.html in a browser for visual overview"
echo -e "  3. Analyze CSV files for detailed data"
echo -e "  4. Create remediation tasks based on findings"