-- Microsoft Common Data Model - Customer Service
-- Reporting and Administrative Queries
-- Version: 1.0

USE CDM_CustomerService;
GO

-- ============================================================================
-- DASHBOARD & KPI QUERIES
-- ============================================================================

-- 1. Executive Dashboard - Key Metrics
CREATE OR ALTER VIEW cdm.v_ExecutiveDashboard AS
SELECT
    (SELECT COUNT(*) FROM cdm.Case WHERE status = 'Active') AS activeCases,
    (SELECT COUNT(*) FROM cdm.Case WHERE status = 'Resolved') AS resolvedCases,
    (SELECT COUNT(*) FROM cdm.Case WHERE status = 'Closed') AS closedCases,
    (SELECT COUNT(*) FROM cdm.Account WHERE accountType = 'Customer' AND isActive = 1) AS totalCustomers,
    (SELECT COUNT(*) FROM cdm.Contract WHERE status = 'Active') AS activeContracts,
    (SELECT SUM(contractValue) FROM cdm.Contract WHERE status = 'Active') AS activeContractValue,
    (SELECT COUNT(DISTINCT contactId) FROM cdm.Case WHERE status = 'Active') AS affectedContacts,
    (SELECT AVG(DATEDIFF(DAY, createdDate, ISNULL(closedDate, GETUTCDATE())))
     FROM cdm.Case WHERE status = 'Closed' AND closedDate IS NOT NULL) AS avgDaysToClose;

GO

-- 2. Case Performance Metrics
CREATE OR ALTER VIEW cdm.v_CasePerformanceMetrics AS
SELECT
    c.caseId,
    c.caseNumber,
    c.title,
    c.priority,
    c.status,
    a.accountName,
    u.firstName + ' ' + u.lastName AS ownerName,
    DATEDIFF(DAY, c.createdDate, GETUTCDATE()) AS daysOpen,
    DATEDIFF(HOUR, c.createdDate, ISNULL(c.resolvedDate, GETUTCDATE())) AS hoursToResolution,
    cr.actualServiceDuration,
    CASE
        WHEN c.status = 'Closed' THEN 'Complete'
        WHEN c.status = 'Resolved' THEN 'Awaiting Close'
        WHEN DATEDIFF(DAY, c.createdDate, GETUTCDATE()) > 30 THEN 'Overdue'
        ELSE 'On Track'
    END AS statusHealth
FROM cdm.Case c
LEFT JOIN cdm.Account a ON c.accountId = a.accountId
LEFT JOIN cdm.[User] u ON c.ownerUserId = u.userId
LEFT JOIN cdm.CaseResolution cr ON c.caseId = cr.caseId
WHERE c.isActive = 1;

GO

-- 3. Agent Performance Report
CREATE OR ALTER VIEW cdm.v_AgentPerformance AS
SELECT
    u.userId,
    u.firstName + ' ' + u.lastName AS agentName,
    COUNT(DISTINCT c.caseId) AS totalCasesOwned,
    SUM(CASE WHEN c.status = 'Active' THEN 1 ELSE 0 END) AS activeCases,
    SUM(CASE WHEN c.status = 'Closed' THEN 1 ELSE 0 END) AS closedCases,
    AVG(CAST(DATEDIFF(HOUR, c.createdDate, ISNULL(c.resolvedDate, GETUTCDATE())) AS FLOAT)) AS avgHoursToResolution,
    SUM(CASE WHEN c.priority = 'Critical' THEN 1 ELSE 0 END) AS criticalCases,
    SUM(CASE WHEN c.priority = 'High' THEN 1 ELSE 0 END) AS highPriorityCases
FROM cdm.[User] u
LEFT JOIN cdm.Case c ON u.userId = c.ownerUserId AND c.isActive = 1
GROUP BY u.userId, u.firstName, u.lastName;

GO

-- 4. Contract Utilization Dashboard
CREATE OR ALTER VIEW cdm.v_ContractUtilizationDashboard AS
SELECT
    c.contractNumber,
    c.contractName,
    a.accountName,
    DATEDIFF(DAY, c.startDate, c.endDate) AS contractDurationDays,
    DATEDIFF(DAY, GETUTCDATE(), c.endDate) AS daysRemaining,
    SUM(cl.quantityIncluded) AS totalUnitsIncluded,
    SUM(cl.quantityUsed) AS totalUnitsUsed,
    CASE
        WHEN SUM(cl.quantityIncluded) > 0
        THEN CAST(CAST(SUM(cl.quantityUsed) AS FLOAT) / CAST(SUM(cl.quantityIncluded) AS FLOAT) * 100 AS DECIMAL(5,2))
        ELSE 0
    END AS utilizationPercent,
    c.contractValue,
    SUM(cl.price) AS lineItemValue,
    CASE
        WHEN CAST(CAST(SUM(cl.quantityUsed) AS FLOAT) / CAST(SUM(cl.quantityIncluded) AS FLOAT) * 100 AS DECIMAL(5,2)) > 80
        THEN 'APPROACHING LIMIT'
        WHEN CAST(CAST(SUM(cl.quantityUsed) AS FLOAT) / CAST(SUM(cl.quantityIncluded) AS FLOAT) * 100 AS DECIMAL(5,2)) > 100
        THEN 'EXCEEDED'
        ELSE 'NORMAL'
    END AS utilizationStatus
FROM cdm.Contract c
LEFT JOIN cdm.Account a ON c.accountId = a.accountId
LEFT JOIN cdm.ContractLine cl ON c.contractId = cl.contractId
WHERE c.isActive = 1 AND c.status = 'Active'
GROUP BY c.contractId, c.contractNumber, c.contractName, a.accountName,
         c.startDate, c.endDate, c.contractValue;

GO

-- ============================================================================
-- ANALYTICAL QUERIES
-- ============================================================================

-- 5. Case Volume by Priority (Last 90 Days)
CREATE OR ALTER PROCEDURE sp_CaseVolumeByPriority
    @Days INT = 90
AS
BEGIN
    SELECT
        c.priority,
        COUNT(*) AS caseCount,
        CAST(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM cdm.Case
            WHERE createdDate >= DATEADD(DAY, -@Days, CAST(GETUTCDATE() AS DATE))) AS DECIMAL(5,2)) AS percentOfTotal,
        AVG(DATEDIFF(HOUR, c.createdDate, ISNULL(c.resolvedDate, GETUTCDATE()))) AS avgHoursOpen
    FROM cdm.Case c
    WHERE c.createdDate >= DATEADD(DAY, -@Days, CAST(GETUTCDATE() AS DATE))
      AND c.isActive = 1
    GROUP BY c.priority
    ORDER BY CASE c.priority
             WHEN 'Critical' THEN 1
             WHEN 'High' THEN 2
             WHEN 'Medium' THEN 3
             WHEN 'Low' THEN 4
             ELSE 5 END;
END;

GO

-- 6. Customer Satisfaction Analysis
CREATE OR ALTER PROCEDURE sp_CustomerSatisfactionAnalysis
    @Months INT = 6
AS
BEGIN
    SELECT
        a.accountId,
        a.accountName,
        COUNT(DISTINCT c.caseId) AS totalCases,
        SUM(CASE WHEN c.status = 'Closed' THEN 1 ELSE 0 END) AS closedCases,
        CAST(SUM(CASE WHEN c.status = 'Closed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS closureRate,
        AVG(CAST(DATEDIFF(DAY, c.createdDate, ISNULL(c.resolvedDate, GETUTCDATE())) AS FLOAT)) AS avgDaysOpen,
        COUNT(DISTINCT c.contactId) AS uniqueContacts,
        SUM(CASE WHEN c.priority = 'Critical' THEN 1 ELSE 0 END) AS criticalCases
    FROM cdm.Account a
    LEFT JOIN cdm.Case c ON a.accountId = c.accountId
        AND c.createdDate >= DATEADD(MONTH, -@Months, GETUTCDATE())
        AND c.isActive = 1
    WHERE a.accountType = 'Customer' AND a.isActive = 1
    GROUP BY a.accountId, a.accountName
    ORDER BY closureRate DESC;
END;

GO

-- 7. SLA Compliance Report
CREATE OR ALTER PROCEDURE sp_SLAComplianceReport
AS
BEGIN
    SELECT
        c.caseNumber,
        c.title,
        sk.kpiName,
        sk.metricValue,
        sk.metricUnit,
        sk.targetValue,
        CASE
            WHEN sk.metricValue <= sk.targetValue THEN 'COMPLIANT'
            WHEN sk.metricValue <= sk.targetValue * 1.1 THEN 'WARNING'
            ELSE 'CRITICAL'
        END AS complianceStatus,
        CAST((sk.metricValue - sk.targetValue) * 100.0 / sk.targetValue AS DECIMAL(5,2)) AS variancePercent,
        a.accountName,
        u.firstName + ' ' + u.lastName AS owner
    FROM cdm.Case c
    JOIN cdm.SLAKPIInstance sk ON c.caseId = sk.caseId
    LEFT JOIN cdm.Account a ON c.accountId = a.accountId
    LEFT JOIN cdm.[User] u ON c.ownerUserId = u.userId
    WHERE c.status IN ('Active', 'Waiting')
    ORDER BY
        CASE
            WHEN sk.metricValue > sk.targetValue THEN 1
            ELSE 2
        END,
        sk.metricValue DESC;
END;

GO

-- 8. Service Activity Schedule
CREATE OR ALTER PROCEDURE sp_ServiceActivitySchedule
    @Days INT = 7
AS
BEGIN
    SELECT
        sa.activityId,
        sa.subject,
        s.serviceName,
        c.caseNumber,
        a.accountName,
        u.firstName + ' ' + u.lastName AS technician,
        CAST(sa.startTime AS DATE) AS scheduleDate,
        FORMAT(sa.startTime, 'HH:mm') AS startTime,
        FORMAT(sa.endTime, 'HH:mm') AS endTime,
        sa.duration,
        sa.location,
        sa.status
    FROM cdm.ServiceActivity sa
    LEFT JOIN cdm.Service s ON sa.serviceId = s.serviceId
    LEFT JOIN cdm.Case c ON sa.caseId = c.caseId
    LEFT JOIN cdm.Account a ON c.accountId = a.accountId
    LEFT JOIN cdm.[User] u ON sa.ownerUserId = u.userId
    WHERE sa.startTime >= GETUTCDATE()
      AND sa.startTime <= DATEADD(DAY, @Days, GETUTCDATE())
      AND sa.isActive = 1
    ORDER BY sa.startTime;
END;

GO

-- ============================================================================
-- ADMINISTRATIVE PROCEDURES
-- ============================================================================

-- 9. Close Resolved Cases
CREATE OR ALTER PROCEDURE sp_CloseResolvedCases
    @DaysOld INT = 7,
    @UserIdClosing UNIQUEIDENTIFIER
AS
BEGIN
    UPDATE cdm.Case
    SET status = 'Closed',
        closedDate = GETUTCDATE(),
        modifiedBy = @UserIdClosing,
        modifiedOn = GETUTCDATE()
    WHERE status = 'Resolved'
      AND resolvedDate <= DATEADD(DAY, -@DaysOld, CAST(GETUTCDATE() AS DATE))
      AND isActive = 1;

    RETURN @@ROWCOUNT;
END;

GO

-- 10. Archive Old Cases
CREATE OR ALTER PROCEDURE sp_ArchiveOldCases
    @YearsOld INT = 2
AS
BEGIN
    UPDATE cdm.Case
    SET isActive = 0
    WHERE status IN ('Closed', 'Resolved')
      AND closedDate <= DATEADD(YEAR, -@YearsOld, GETUTCDATE())
      AND isActive = 1;

    RETURN @@ROWCOUNT;
END;

GO

-- 11. Deactivate Expired Contracts
CREATE OR ALTER PROCEDURE sp_DeactivateExpiredContracts
AS
BEGIN
    UPDATE cdm.Contract
    SET status = 'Expired',
        modifiedOn = GETUTCDATE()
    WHERE endDate < CAST(GETUTCDATE() AS DATE)
      AND status = 'Active'
      AND isActive = 1;

    RETURN @@ROWCOUNT;
END;

GO

-- 12. Update Entitlement Status
CREATE OR ALTER PROCEDURE sp_UpdateEntitlementStatus
AS
BEGIN
    UPDATE cdm.Entitlement
    SET status = 'Expired',
        modifiedOn = GETUTCDATE()
    WHERE endDate < CAST(GETUTCDATE() AS DATE)
      AND status = 'Active'
      AND isActive = 1;

    RETURN @@ROWCOUNT;
END;

GO

-- 13. Update Case Overdue Status
CREATE OR ALTER PROCEDURE sp_UpdateCaseOverdueStatus
    @ThresholdDays INT = 30
AS
BEGIN
    SELECT
        c.caseId,
        c.caseNumber,
        a.accountName,
        DATEDIFF(DAY, c.createdDate, GETUTCDATE()) AS daysOpen
    FROM cdm.Case c
    LEFT JOIN cdm.Account a ON c.accountId = a.accountId
    WHERE c.status IN ('Active', 'Waiting')
      AND DATEDIFF(DAY, c.createdDate, GETUTCDATE()) > @ThresholdDays
      AND c.isActive = 1
    ORDER BY DATEDIFF(DAY, c.createdDate, GETUTCDATE()) DESC;
END;

GO

-- 14. Generate Contract Renewal Alerts
CREATE OR ALTER PROCEDURE sp_ContractRenewalAlerts
    @DaysUntilExpiry INT = 30
AS
BEGIN
    SELECT
        c.contractId,
        c.contractNumber,
        c.contractName,
        a.accountName,
        c.endDate,
        DATEDIFF(DAY, GETUTCDATE(), c.endDate) AS daysUntilExpiry,
        c.contractValue,
        u.firstName + ' ' + u.lastName AS owner,
        u.email
    FROM cdm.Contract c
    LEFT JOIN cdm.Account a ON c.accountId = a.accountId
    LEFT JOIN cdm.[User] u ON c.ownerUserId = u.userId
    WHERE c.status = 'Active'
      AND DATEDIFF(DAY, GETUTCDATE(), c.endDate) <= @DaysUntilExpiry
      AND DATEDIFF(DAY, GETUTCDATE(), c.endDate) > 0
      AND c.isActive = 1
    ORDER BY c.endDate;
END;

GO

-- ============================================================================
-- DATA QUALITY QUERIES
-- ============================================================================

-- 15. Cases without Required Information
CREATE OR ALTER PROCEDURE sp_CasesWithMissingData
AS
BEGIN
    SELECT
        'Missing Account' AS dataQualityIssue,
        COUNT(*) AS caseCount
    FROM cdm.Case
    WHERE accountId IS NULL AND isActive = 1
    UNION ALL
    SELECT
        'Missing Owner',
        COUNT(*)
    FROM cdm.Case
    WHERE ownerUserId IS NULL AND isActive = 1
    UNION ALL
    SELECT
        'Missing Contact (High Priority)',
        COUNT(*)
    FROM cdm.Case
    WHERE contactId IS NULL
      AND priority IN ('High', 'Critical')
      AND status IN ('Active', 'Waiting')
      AND isActive = 1;
END;

GO

-- 16. Orphaned Records
CREATE OR ALTER PROCEDURE sp_FindOrphanedRecords
AS
BEGIN
    -- Orphaned Service Activities (Case not found)
    SELECT 'Orphaned ServiceActivity' AS recordType, COUNT(*) AS count
    FROM cdm.ServiceActivity sa
    WHERE sa.caseId IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM cdm.Case c WHERE c.caseId = sa.caseId)

    UNION ALL

    -- Orphaned Tasks (Case not found)
    SELECT 'Orphaned Task', COUNT(*)
    FROM cdm.Task t
    WHERE t.caseId IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM cdm.Case c WHERE c.caseId = t.caseId)

    UNION ALL

    -- Orphaned ContractLines (Contract not found)
    SELECT 'Orphaned ContractLine', COUNT(*)
    FROM cdm.ContractLine cl
    WHERE NOT EXISTS (SELECT 1 FROM cdm.Contract c WHERE c.contractId = cl.contractId);
END;

GO

-- ============================================================================
-- REPORTING PROCEDURES
-- ============================================================================

-- 17. Monthly Activity Report
CREATE OR ALTER PROCEDURE sp_MonthlyActivityReport
    @Year INT,
    @Month INT
AS
BEGIN
    DECLARE @StartDate DATE = DATEFROMPARTS(@Year, @Month, 1);
    DECLARE @EndDate DATE = EOMONTH(@StartDate);

    SELECT
        FORMAT(@StartDate, 'MMMM yyyy') AS reportMonth,
        (SELECT COUNT(*) FROM cdm.Case WHERE createdDate >= @StartDate AND createdDate <= @EndDate AND isActive = 1) AS newCases,
        (SELECT COUNT(*) FROM cdm.Case WHERE status = 'Closed' AND closedDate >= @StartDate AND closedDate <= @EndDate AND isActive = 1) AS closedCases,
        (SELECT COUNT(*) FROM cdm.ServiceActivity WHERE createdDate >= @StartDate AND createdDate <= @EndDate AND isActive = 1) AS serviceActivities,
        (SELECT COUNT(*) FROM cdm.Task WHERE createdDate >= @StartDate AND createdDate <= @EndDate) AS tasksCreated,
        (SELECT COUNT(*) FROM cdm.Task WHERE status = 'Completed' AND modifiedOn >= @StartDate AND modifiedOn <= @EndDate) AS tasksCompleted,
        (SELECT COUNT(*) FROM cdm.Contract WHERE status = 'Active' AND startDate <= @EndDate AND endDate >= @StartDate) AS activeContracts;
END;

GO

-- 18. Customer Revenue Analysis
CREATE OR ALTER PROCEDURE sp_CustomerRevenueAnalysis
AS
BEGIN
    SELECT
        TOP 20
        a.accountId,
        a.accountName,
        a.industry,
        COUNT(DISTINCT c.contractId) AS numberOfContracts,
        SUM(c.contractValue) AS totalContractValue,
        AVG(c.contractValue) AS avgContractValue,
        COUNT(DISTINCT CASE WHEN c.status = 'Active' THEN c.contractId END) AS activeContracts,
        COUNT(DISTINCT ca.caseId) AS totalCases,
        SUM(CASE WHEN ca.status = 'Closed' THEN 1 ELSE 0 END) AS closedCases
    FROM cdm.Account a
    LEFT JOIN cdm.Contract c ON a.accountId = c.accountId
    LEFT JOIN cdm.Case ca ON a.accountId = ca.accountId
    WHERE a.accountType = 'Customer' AND a.isActive = 1
    GROUP BY a.accountId, a.accountName, a.industry
    ORDER BY totalContractValue DESC;
END;

GO

-- 19. Resource Utilization Report
CREATE OR ALTER PROCEDURE sp_ResourceUtilizationReport
    @Days INT = 30
AS
BEGIN
    SELECT
        r.resourceId,
        r.resourceName,
        r.resourceType,
        COUNT(DISTINCT sa.activityId) AS scheduledActivities,
        SUM(sa.duration) AS totalMinutesScheduled,
        AVG(sa.duration) AS avgActivityDuration,
        COUNT(DISTINCT CASE WHEN sa.status = 'Completed' THEN sa.activityId END) AS completedActivities,
        COUNT(DISTINCT CASE WHEN sa.status = 'In Progress' THEN sa.activityId END) AS inProgressActivities,
        COUNT(DISTINCT CASE WHEN sa.status = 'Scheduled' THEN sa.activityId END) AS scheduledForFuture
    FROM cdm.Resource r
    LEFT JOIN cdm.[User] u ON r.userId = u.userId
    LEFT JOIN cdm.ServiceActivity sa ON u.userId = sa.ownerUserId
        AND sa.startTime >= DATEADD(DAY, -@Days, GETUTCDATE())
        AND sa.isActive = 1
    WHERE r.resourceType = 'User' AND r.isActive = 1
    GROUP BY r.resourceId, r.resourceName, r.resourceType
    ORDER BY totalMinutesScheduled DESC;
END;

GO

-- 20. Knowledge Article Effectiveness
CREATE OR ALTER PROCEDURE sp_KnowledgeArticleEffectiveness
AS
BEGIN
    SELECT
        TOP 20
        ka.articleId,
        ka.title,
        ka.category,
        ka.viewCount,
        COUNT(DISTINCT kai.caseId) AS linkedToCases,
        COUNT(DISTINCT CASE WHEN kai.isResolved = 1 THEN kai.caseId END) AS helpedResolve,
        CASE
            WHEN COUNT(DISTINCT kai.caseId) > 0
            THEN CAST(COUNT(DISTINCT CASE WHEN kai.isResolved = 1 THEN kai.caseId END) * 100.0 / COUNT(DISTINCT kai.caseId) AS DECIMAL(5,2))
            ELSE 0
        END AS resolutionRate,
        ka.publishedDate
    FROM cdm.KnowledgeArticle ka
    LEFT JOIN cdm.KnowledgeArticleIncident kai ON ka.articleId = kai.articleId
    WHERE ka.status = 'Published' AND ka.isActive = 1
    GROUP BY ka.articleId, ka.title, ka.category, ka.viewCount, ka.publishedDate
    ORDER BY ka.viewCount DESC;
END;

GO

-- ============================================================================
-- USAGE EXAMPLES
-- ============================================================================

/*

-- Execute Executive Dashboard View
SELECT * FROM cdm.v_ExecutiveDashboard;

-- Get Agent Performance
SELECT * FROM cdm.v_AgentPerformance
ORDER BY closedCases DESC;

-- Get Contract Utilization
SELECT * FROM cdm.v_ContractUtilizationDashboard
WHERE utilizationStatus != 'NORMAL';

-- Case Volume Analysis
EXEC sp_CaseVolumeByPriority @Days = 90;

-- Customer Satisfaction Analysis
EXEC sp_CustomerSatisfactionAnalysis @Months = 6;

-- SLA Compliance Report
EXEC sp_SLAComplianceReport;

-- Service Activity Schedule
EXEC sp_ServiceActivitySchedule @Days = 7;

-- Overdue Cases (> 30 days)
EXEC sp_UpdateCaseOverdueStatus @ThresholdDays = 30;

-- Contract Renewal Alerts (within 30 days)
EXEC sp_ContractRenewalAlerts @DaysUntilExpiry = 30;

-- Data Quality Issues
EXEC sp_CasesWithMissingData;

-- Monthly Activity Report
EXEC sp_MonthlyActivityReport @Year = 2024, @Month = 9;

-- Top Revenue Customers
EXEC sp_CustomerRevenueAnalysis;

-- Resource Utilization
EXEC sp_ResourceUtilizationReport @Days = 30;

-- Knowledge Article Effectiveness
EXEC sp_KnowledgeArticleEffectiveness;

*/

PRINT 'All reporting and administrative procedures created successfully!';