-- Microsoft Common Data Model - Customer Service Application Model
-- SQL Server DDL Implementation
-- Version: 1.0
-- Date: 2026-09-23

-- ============================================================================
-- DATABASE AND SCHEMA SETUP
-- ============================================================================

-- Create Database (uncomment to create)
-- CREATE DATABASE CDM_CustomerService;
-- GO

USE CDM_CustomerService;
GO

-- Create Schema for CDM
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'cdm')
BEGIN
  EXEC sp_executesql N'CREATE SCHEMA cdm'
END
GO

-- ============================================================================
-- FOUNDATION TABLES
-- ============================================================================

-- 1. User Table - Represents system users with access to the CRM
CREATE TABLE cdm.User (
    userId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    userName NVARCHAR(200) NOT NULL UNIQUE,
    firstName NVARCHAR(100) NOT NULL,
    lastName NVARCHAR(100) NOT NULL,
    email NVARCHAR(200) NOT NULL UNIQUE,
    phone NVARCHAR(20),
    mobilePhone NVARCHAR(20),
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER
);

-- 2. Site Table - Represents business locations/branches
CREATE TABLE cdm.Site (
    siteId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    siteName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    address NVARCHAR(500),
    city NVARCHAR(100),
    stateProvince NVARCHAR(100),
    postalCode NVARCHAR(20),
    country NVARCHAR(100),
    phone NVARCHAR(20),
    email NVARCHAR(200),
    isHeadquarters BIT DEFAULT 0,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 3. Account Table - Represents customer organizations
CREATE TABLE cdm.Account (
    accountId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    accountName NVARCHAR(200) NOT NULL,
    accountNumber NVARCHAR(50) UNIQUE,
    description NVARCHAR(2000),
    email NVARCHAR(200),
    phone NVARCHAR(20),
    faxPhone NVARCHAR(20),
    websiteUrl NVARCHAR(500),
    parentAccountId UNIQUEIDENTIFIER,
    ownerUserId UNIQUEIDENTIFIER,
    siteId UNIQUEIDENTIFIER,
    accountType NVARCHAR(50), -- 'Prospect', 'Customer', 'Partner'
    industry NVARCHAR(100),
    numberOfEmployees INT,
    revenue DECIMAL(18,2),
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (parentAccountId) REFERENCES cdm.Account(accountId),
    FOREIGN KEY (ownerUserId) REFERENCES cdm.User(userId),
    FOREIGN KEY (siteId) REFERENCES cdm.Site(siteId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 4. Contact Table - Represents individual persons
CREATE TABLE cdm.Contact (
    contactId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    firstName NVARCHAR(100) NOT NULL,
    lastName NVARCHAR(100) NOT NULL,
    email NVARCHAR(200),
    phone NVARCHAR(20),
    mobilePhone NVARCHAR(20),
    accountId UNIQUEIDENTIFIER,
    ownerUserId UNIQUEIDENTIFIER,
    jobTitle NVARCHAR(100),
    department NVARCHAR(100),
    address NVARCHAR(500),
    city NVARCHAR(100),
    stateProvince NVARCHAR(100),
    postalCode NVARCHAR(20),
    country NVARCHAR(100),
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (accountId) REFERENCES cdm.Account(accountId),
    FOREIGN KEY (ownerUserId) REFERENCES cdm.User(userId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- SERVICE CONFIGURATION TABLES
-- ============================================================================

-- 5. Service Table - Represents services offered to customers
CREATE TABLE cdm.Service (
    serviceId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    serviceName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 6. Resource Table - Users or equipment that can be scheduled
CREATE TABLE cdm.Resource (
    resourceId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    resourceName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    resourceType NVARCHAR(50) NOT NULL, -- 'User', 'Equipment', 'Facility'
    userId UNIQUEIDENTIFIER, -- For user type resources
    siteId UNIQUEIDENTIFIER, -- For facility type resources
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (userId) REFERENCES cdm.User(userId),
    FOREIGN KEY (siteId) REFERENCES cdm.Site(siteId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 7. FacilityEquipment Table - Resources that can be scheduled
CREATE TABLE cdm.FacilityEquipment (
    equipmentId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    equipmentName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    resourceId UNIQUEIDENTIFIER NOT NULL UNIQUE,
    location NVARCHAR(200),
    capacity INT,
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (resourceId) REFERENCES cdm.Resource(resourceId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 8. SchedulingGroup Table - Resource group or team
CREATE TABLE cdm.SchedulingGroup (
    groupId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    groupName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    resourceId UNIQUEIDENTIFIER,
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (resourceId) REFERENCES cdm.Resource(resourceId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 9. ResourceSpecification Table - Selection rules for resources
CREATE TABLE cdm.ResourceSpecification (
    specificationId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    specificationName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    serviceId UNIQUEIDENTIFIER,
    minRequiredCount INT DEFAULT 1,
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (serviceId) REFERENCES cdm.Service(serviceId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- CONTRACT TABLES
-- ============================================================================

-- 10. Contract Table - Customer service contracts
CREATE TABLE cdm.Contract (
    contractId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    contractNumber NVARCHAR(100) NOT NULL UNIQUE,
    contractName NVARCHAR(200) NOT NULL,
    accountId UNIQUEIDENTIFIER NOT NULL,
    ownerUserId UNIQUEIDENTIFIER,
    startDate DATE NOT NULL,
    endDate DATE NOT NULL,
    status NVARCHAR(50) NOT NULL, -- 'Draft', 'Active', 'Expired', 'Cancelled'
    contractValue DECIMAL(18,2),
    description NVARCHAR(2000),
    contractType NVARCHAR(50), -- 'Service', 'Support', 'Maintenance'
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (accountId) REFERENCES cdm.Account(accountId),
    FOREIGN KEY (ownerUserId) REFERENCES cdm.User(userId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 11. ContractLine Table - Line items in contracts
CREATE TABLE cdm.ContractLine (
    contractLineId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    contractId UNIQUEIDENTIFIER NOT NULL,
    lineNumber INT NOT NULL,
    serviceId UNIQUEIDENTIFIER NOT NULL,
    description NVARCHAR(2000),
    quantityIncluded INT,
    quantityUsed INT DEFAULT 0,
    price DECIMAL(18,2),
    discount DECIMAL(18,2),
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    UNIQUE (contractId, lineNumber),
    FOREIGN KEY (contractId) REFERENCES cdm.Contract(contractId) ON DELETE CASCADE,
    FOREIGN KEY (serviceId) REFERENCES cdm.Service(serviceId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 12. ServiceContractContact Table - Contacts associated with contracts
CREATE TABLE cdm.ServiceContractContact (
    serviceContractContactId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    contractId UNIQUEIDENTIFIER NOT NULL,
    contactId UNIQUEIDENTIFIER NOT NULL,
    role NVARCHAR(100),
    isPrimary BIT DEFAULT 0,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    UNIQUE (contractId, contactId),
    FOREIGN KEY (contractId) REFERENCES cdm.Contract(contractId) ON DELETE CASCADE,
    FOREIGN KEY (contactId) REFERENCES cdm.Contact(contactId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- ENTITLEMENT TABLES
-- ============================================================================

-- 13. Entitlement Table - Support entitlements for customers
CREATE TABLE cdm.Entitlement (
    entitlementId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    entitlementName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    accountId UNIQUEIDENTIFIER NOT NULL,
    startDate DATE NOT NULL,
    endDate DATE,
    type NVARCHAR(50) NOT NULL, -- 'Support', 'Service', 'Training'
    duration INT, -- Duration in days
    unitType NVARCHAR(50), -- 'Cases', 'Hours', 'Incidents'
    unitCount INT,
    status NVARCHAR(50) NOT NULL, -- 'Active', 'Inactive', 'Expired'
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (accountId) REFERENCES cdm.Account(accountId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 14. EntitlementContact Table - Contacts associated with entitlements
CREATE TABLE cdm.EntitlementContact (
    entitlementContactId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    entitlementId UNIQUEIDENTIFIER NOT NULL,
    contactId UNIQUEIDENTIFIER NOT NULL,
    isPrimary BIT DEFAULT 0,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    UNIQUE (entitlementId, contactId),
    FOREIGN KEY (entitlementId) REFERENCES cdm.Entitlement(entitlementId) ON DELETE CASCADE,
    FOREIGN KEY (contactId) REFERENCES cdm.Contact(contactId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 15. EntitlementProduct Table - Products covered by entitlements
CREATE TABLE cdm.EntitlementProduct (
    entitlementProductId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    entitlementId UNIQUEIDENTIFIER NOT NULL,
    serviceId UNIQUEIDENTIFIER NOT NULL,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    UNIQUE (entitlementId, serviceId),
    FOREIGN KEY (entitlementId) REFERENCES cdm.Entitlement(entitlementId) ON DELETE CASCADE,
    FOREIGN KEY (serviceId) REFERENCES cdm.Service(serviceId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- CASE/INCIDENT TABLES
-- ============================================================================

-- 16. Case Table - Service request cases
CREATE TABLE cdm.Case (
    caseId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    caseNumber NVARCHAR(50) NOT NULL UNIQUE,
    title NVARCHAR(200) NOT NULL,
    description NVARCHAR(MAX),
    priority NVARCHAR(50) NOT NULL DEFAULT 'Medium', -- 'Low', 'Medium', 'High', 'Critical'
    severity NVARCHAR(50), -- 'Low', 'Medium', 'High'
    status NVARCHAR(50) NOT NULL DEFAULT 'Active', -- 'Active', 'Resolved', 'Closed', 'Waiting'
    accountId UNIQUEIDENTIFIER NOT NULL,
    contactId UNIQUEIDENTIFIER,
    ownerUserId UNIQUEIDENTIFIER,
    contractId UNIQUEIDENTIFIER,
    entitlementId UNIQUEIDENTIFIER,
    createdDate DATETIME2(7) DEFAULT GETUTCDATE(),
    resolvedDate DATETIME2(7),
    closedDate DATETIME2(7),
    parentCaseId UNIQUEIDENTIFIER,
    emailAddress NVARCHAR(200),
    resolutionNotes NVARCHAR(MAX),
    billingStatus NVARCHAR(50), -- 'Billable', 'Non-billable'
    actualServiceDuration INT, -- Duration in minutes
    estimatedServiceDuration INT,
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (accountId) REFERENCES cdm.Account(accountId),
    FOREIGN KEY (contactId) REFERENCES cdm.Contact(contactId),
    FOREIGN KEY (ownerUserId) REFERENCES cdm.User(userId),
    FOREIGN KEY (contractId) REFERENCES cdm.Contract(contractId),
    FOREIGN KEY (entitlementId) REFERENCES cdm.Entitlement(entitlementId),
    FOREIGN KEY (parentCaseId) REFERENCES cdm.Case(caseId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 17. CaseResolution Table - Special activity for case resolution
CREATE TABLE cdm.CaseResolution (
    resolutionId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    caseId UNIQUEIDENTIFIER NOT NULL,
    description NVARCHAR(MAX),
    resolutionType NVARCHAR(100),
    resolutionStatus NVARCHAR(50),
    billingStatus NVARCHAR(50), -- 'Billable', 'Non-billable'
    actualServiceDuration INT, -- Duration in minutes
    totalCostAmount DECIMAL(18,2),
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (caseId) REFERENCES cdm.Case(caseId) ON DELETE CASCADE,
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 18. ChildIncidentCount Table - Tracks child incidents for a case
CREATE TABLE cdm.ChildIncidentCount (
    countId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    parentCaseId UNIQUEIDENTIFIER NOT NULL,
    childIncidentCount INT DEFAULT 0,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    modifiedOn DATETIME2(7),
    UNIQUE (parentCaseId),
    FOREIGN KEY (parentCaseId) REFERENCES cdm.Case(caseId) ON DELETE CASCADE
);

-- ============================================================================
-- SERVICE ACTIVITY TABLES
-- ============================================================================

-- 19. ServiceActivity Table - Service activities scheduled for cases
CREATE TABLE cdm.ServiceActivity (
    activityId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    subject NVARCHAR(200) NOT NULL,
    description NVARCHAR(MAX),
    serviceId UNIQUEIDENTIFIER NOT NULL,
    caseId UNIQUEIDENTIFIER,
    contactId UNIQUEIDENTIFIER,
    ownerUserId UNIQUEIDENTIFIER,
    startTime DATETIME2(7) NOT NULL,
    endTime DATETIME2(7) NOT NULL,
    duration INT, -- Duration in minutes
    location NVARCHAR(200),
    status NVARCHAR(50) NOT NULL DEFAULT 'Scheduled', -- 'Scheduled', 'In Progress', 'Completed', 'Cancelled'
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (serviceId) REFERENCES cdm.Service(serviceId),
    FOREIGN KEY (caseId) REFERENCES cdm.Case(caseId),
    FOREIGN KEY (contactId) REFERENCES cdm.Contact(contactId),
    FOREIGN KEY (ownerUserId) REFERENCES cdm.User(userId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 20. ActivityParty Table - Persons or groups associated with activities
CREATE TABLE cdm.ActivityParty (
    partyId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    activityId UNIQUEIDENTIFIER NOT NULL,
    partyType NVARCHAR(50) NOT NULL, -- 'Required', 'Optional', 'Organizer'
    userId UNIQUEIDENTIFIER,
    contactId UNIQUEIDENTIFIER,
    email NVARCHAR(200),
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    FOREIGN KEY (activityId) REFERENCES cdm.ServiceActivity(activityId) ON DELETE CASCADE,
    FOREIGN KEY (userId) REFERENCES cdm.User(userId),
    FOREIGN KEY (contactId) REFERENCES cdm.Contact(contactId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- SLA AND KPI TABLES
-- ============================================================================

-- 21. SLAKPIInstance Table - SLA KPI tracking
CREATE TABLE cdm.SLAKPIInstance (
    kpiInstanceId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    caseId UNIQUEIDENTIFIER NOT NULL,
    kpiName NVARCHAR(200) NOT NULL,
    description NVARCHAR(2000),
    metric NVARCHAR(100), -- 'Response Time', 'Resolution Time', 'Customer Satisfaction'
    metricValue DECIMAL(18,2),
    metricUnit NVARCHAR(50), -- 'Minutes', 'Hours', 'Days', '%'
    targetValue DECIMAL(18,2),
    isWarning BIT DEFAULT 0,
    isCritical BIT DEFAULT 0,
    status NVARCHAR(50), -- 'Compliant', 'Non-Compliant', 'In Progress'
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    modifiedOn DATETIME2(7),
    FOREIGN KEY (caseId) REFERENCES cdm.Case(caseId) ON DELETE CASCADE
);

-- ============================================================================
-- KNOWLEDGE BASE TABLES
-- ============================================================================

-- 22. KnowledgeArticle Table - Knowledge base articles
CREATE TABLE cdm.KnowledgeArticle (
    articleId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    title NVARCHAR(500) NOT NULL,
    content NVARCHAR(MAX),
    category NVARCHAR(100),
    keywords NVARCHAR(500),
    authorUserId UNIQUEIDENTIFIER,
    status NVARCHAR(50) NOT NULL DEFAULT 'Draft', -- 'Draft', 'Published', 'Expired'
    publishedDate DATETIME2(7),
    expirationDate DATETIME2(7),
    viewCount INT DEFAULT 0,
    isActive BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (authorUserId) REFERENCES cdm.User(userId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- 23. KnowledgeArticleIncident Table - Association between articles and cases
CREATE TABLE cdm.KnowledgeArticleIncident (
    associationId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    articleId UNIQUEIDENTIFIER NOT NULL,
    caseId UNIQUEIDENTIFIER NOT NULL,
    isResolved BIT DEFAULT 0,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    UNIQUE (articleId, caseId),
    FOREIGN KEY (articleId) REFERENCES cdm.KnowledgeArticle(articleId) ON DELETE CASCADE,
    FOREIGN KEY (caseId) REFERENCES cdm.Case(caseId) ON DELETE CASCADE,
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId)
);

-- 24. IncidentKnowledgeBaseRecord Table - Knowledge base records for incidents
CREATE TABLE cdm.IncidentKnowledgeBaseRecord (
    recordId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    caseId UNIQUEIDENTIFIER NOT NULL,
    articleId UNIQUEIDENTIFIER NOT NULL,
    notes NVARCHAR(2000),
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    FOREIGN KEY (caseId) REFERENCES cdm.Case(caseId) ON DELETE CASCADE,
    FOREIGN KEY (articleId) REFERENCES cdm.KnowledgeArticle(articleId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- ACTIVITY TABLES (Generic Activities)
-- ============================================================================

-- 25. Task Table - Generic task activities
CREATE TABLE cdm.Task (
    taskId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    subject NVARCHAR(200) NOT NULL,
    description NVARCHAR(MAX),
    priority NVARCHAR(50) NOT NULL DEFAULT 'Medium',
    status NVARCHAR(50) NOT NULL DEFAULT 'Open', -- 'Open', 'In Progress', 'Completed', 'Cancelled'
    dueDate DATE,
    ownerUserId UNIQUEIDENTIFIER NOT NULL,
    caseId UNIQUEIDENTIFIER,
    contactId UNIQUEIDENTIFIER,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    modifiedOn DATETIME2(7),
    modifiedBy UNIQUEIDENTIFIER,
    FOREIGN KEY (ownerUserId) REFERENCES cdm.User(userId),
    FOREIGN KEY (caseId) REFERENCES cdm.Case(caseId),
    FOREIGN KEY (contactId) REFERENCES cdm.Contact(contactId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId),
    FOREIGN KEY (modifiedBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- RESOURCE EXPANSION TABLES
-- ============================================================================

-- 26. ResourceExpansion Table - Resource expansion configurations
CREATE TABLE cdm.ResourceExpansion (
    expansionId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    resourceId UNIQUEIDENTIFIER NOT NULL,
    resourceSpecificationId UNIQUEIDENTIFIER,
    isIncluded BIT DEFAULT 1,
    createdOn DATETIME2(7) DEFAULT GETUTCDATE(),
    createdBy UNIQUEIDENTIFIER,
    UNIQUE (resourceId, resourceSpecificationId),
    FOREIGN KEY (resourceId) REFERENCES cdm.Resource(resourceId) ON DELETE CASCADE,
    FOREIGN KEY (resourceSpecificationId) REFERENCES cdm.ResourceSpecification(specificationId),
    FOREIGN KEY (createdBy) REFERENCES cdm.User(userId)
);

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

-- User indexes
CREATE NONCLUSTERED INDEX IX_User_Email ON cdm.User(email);
CREATE NONCLUSTERED INDEX IX_User_IsActive ON cdm.User(isActive);

-- Site indexes
CREATE NONCLUSTERED INDEX IX_Site_IsActive ON cdm.Site(isActive);

-- Account indexes
CREATE NONCLUSTERED INDEX IX_Account_ParentAccountId ON cdm.Account(parentAccountId);
CREATE NONCLUSTERED INDEX IX_Account_OwnerUserId ON cdm.Account(ownerUserId);
CREATE NONCLUSTERED INDEX IX_Account_IsActive ON cdm.Account(isActive);
CREATE NONCLUSTERED INDEX IX_Account_AccountType ON cdm.Account(accountType);

-- Contact indexes
CREATE NONCLUSTERED INDEX IX_Contact_AccountId ON cdm.Contact(accountId);
CREATE NONCLUSTERED INDEX IX_Contact_Email ON cdm.Contact(email);
CREATE NONCLUSTERED INDEX IX_Contact_IsActive ON cdm.Contact(isActive);

-- Service indexes
CREATE NONCLUSTERED INDEX IX_Service_IsActive ON cdm.Service(isActive);

-- Resource indexes
CREATE NONCLUSTERED INDEX IX_Resource_ResourceType ON cdm.Resource(resourceType);
CREATE NONCLUSTERED INDEX IX_Resource_IsActive ON cdm.Resource(isActive);

-- Contract indexes
CREATE NONCLUSTERED INDEX IX_Contract_AccountId ON cdm.Contract(accountId);
CREATE NONCLUSTERED INDEX IX_Contract_OwnerUserId ON cdm.Contract(ownerUserId);
CREATE NONCLUSTERED INDEX IX_Contract_Status ON cdm.Contract(status);
CREATE NONCLUSTERED INDEX IX_Contract_DateRange ON cdm.Contract(startDate, endDate);

-- ContractLine indexes
CREATE NONCLUSTERED INDEX IX_ContractLine_ServiceId ON cdm.ContractLine(serviceId);

-- Entitlement indexes
CREATE NONCLUSTERED INDEX IX_Entitlement_AccountId ON cdm.Entitlement(accountId);
CREATE NONCLUSTERED INDEX IX_Entitlement_Status ON cdm.Entitlement(status);
CREATE NONCLUSTERED INDEX IX_Entitlement_DateRange ON cdm.Entitlement(startDate, endDate);

-- Case indexes
CREATE NONCLUSTERED INDEX IX_Case_CaseNumber ON cdm.Case(caseNumber);
CREATE NONCLUSTERED INDEX IX_Case_AccountId ON cdm.Case(accountId);
CREATE NONCLUSTERED INDEX IX_Case_ContactId ON cdm.Case(contactId);
CREATE NONCLUSTERED INDEX IX_Case_OwnerUserId ON cdm.Case(ownerUserId);
CREATE NONCLUSTERED INDEX IX_Case_Status ON cdm.Case(status);
CREATE NONCLUSTERED INDEX IX_Case_Priority ON cdm.Case(priority);
CREATE NONCLUSTERED INDEX IX_Case_CreatedDate ON cdm.Case(createdDate);
CREATE NONCLUSTERED INDEX IX_Case_ParentCaseId ON cdm.Case(parentCaseId);

-- ServiceActivity indexes
CREATE NONCLUSTERED INDEX IX_ServiceActivity_CaseId ON cdm.ServiceActivity(caseId);
CREATE NONCLUSTERED INDEX IX_ServiceActivity_ContactId ON cdm.ServiceActivity(contactId);
CREATE NONCLUSTERED INDEX IX_ServiceActivity_OwnerUserId ON cdm.ServiceActivity(ownerUserId);
CREATE NONCLUSTERED INDEX IX_ServiceActivity_Status ON cdm.ServiceActivity(status);
CREATE NONCLUSTERED INDEX IX_ServiceActivity_TimeRange ON cdm.ServiceActivity(startTime, endTime);

-- Task indexes
CREATE NONCLUSTERED INDEX IX_Task_OwnerUserId ON cdm.Task(ownerUserId);
CREATE NONCLUSTERED INDEX IX_Task_CaseId ON cdm.Task(caseId);
CREATE NONCLUSTERED INDEX IX_Task_Status ON cdm.Task(status);
CREATE NONCLUSTERED INDEX IX_Task_DueDate ON cdm.Task(dueDate);

-- KnowledgeArticle indexes
CREATE NONCLUSTERED INDEX IX_KnowledgeArticle_Status ON cdm.KnowledgeArticle(status);
CREATE NONCLUSTERED INDEX IX_KnowledgeArticle_Category ON cdm.KnowledgeArticle(category);

-- SLAKPIInstance indexes
CREATE NONCLUSTERED INDEX IX_SLAKPIInstance_CaseId ON cdm.SLAKPIInstance(caseId);
CREATE NONCLUSTERED INDEX IX_SLAKPIInstance_Status ON cdm.SLAKPIInstance(status);

GO

-- ============================================================================
-- SAMPLE DATA
-- ============================================================================

-- Sample Users
INSERT INTO cdm.User (userId, userName, firstName, lastName, email, phone, isActive)
VALUES
    (NEWID(), 'jsmith', 'John', 'Smith', 'john.smith@company.com', '555-0101', 1),
    (NEWID(), 'mwhite', 'Maria', 'White', 'maria.white@company.com', '555-0102', 1),
    (NEWID(), 'rjones', 'Robert', 'Jones', 'robert.jones@company.com', '555-0103', 1),
    (NEWID(), 'sbrown', 'Sarah', 'Brown', 'sarah.brown@company.com', '555-0104', 1),
    (NEWID(), 'mtaylor', 'Michael', 'Taylor', 'michael.taylor@company.com', '555-0105', 1);

DECLARE @UserId1 UNIQUEIDENTIFIER = (SELECT TOP 1 userId FROM cdm.User WHERE userName = 'jsmith');
DECLARE @UserId2 UNIQUEIDENTIFIER = (SELECT TOP 1 userId FROM cdm.User WHERE userName = 'mwhite');
DECLARE @UserId3 UNIQUEIDENTIFIER = (SELECT TOP 1 userId FROM cdm.User WHERE userName = 'rjones');

-- Sample Sites
INSERT INTO cdm.Site (siteId, siteName, description, city, stateProvince, country, phone, createdBy)
VALUES
    (NEWID(), 'New York Office', 'Main headquarters', 'New York', 'NY', 'USA', '555-0200', @UserId1),
    (NEWID(), 'Los Angeles Office', 'West coast support center', 'Los Angeles', 'CA', 'USA', '555-0201', @UserId1),
    (NEWID(), 'Chicago Office', 'Midwest operations', 'Chicago', 'IL', 'USA', '555-0202', @UserId1);

DECLARE @SiteId1 UNIQUEIDENTIFIER = (SELECT TOP 1 siteId FROM cdm.Site WHERE siteName = 'New York Office');
DECLARE @SiteId2 UNIQUEIDENTIFIER = (SELECT TOP 1 siteId FROM cdm.Site WHERE siteName = 'Los Angeles Office');

-- Sample Accounts
INSERT INTO cdm.Account (accountId, accountName, accountNumber, description, email, phone, ownerUserId, siteId, accountType, industry, numberOfEmployees, isActive, createdBy)
VALUES
    (NEWID(), 'Acme Corporation', 'ACC-001', 'Leading manufacturing company', 'contact@acme.com', '555-1001', @UserId1, @SiteId1, 'Customer', 'Manufacturing', 500, 1, @UserId1),
    (NEWID(), 'TechVision Inc', 'ACC-002', 'Software development services', 'info@techvision.com', '555-1002', @UserId2, @SiteId2, 'Customer', 'Technology', 250, 1, @UserId1),
    (NEWID(), 'Global Services Ltd', 'ACC-003', 'Consulting and support services', 'contact@globalservices.com', '555-1003', @UserId3, @SiteId1, 'Customer', 'Consulting', 1000, 1, @UserId1),
    (NEWID(), 'StartupX', 'ACC-004', 'Early stage technology startup', 'hello@startupx.com', '555-1004', @UserId2, @SiteId2, 'Prospect', 'Technology', 50, 1, @UserId1);

DECLARE @AccountId1 UNIQUEIDENTIFIER = (SELECT TOP 1 accountId FROM cdm.Account WHERE accountName = 'Acme Corporation');
DECLARE @AccountId2 UNIQUEIDENTIFIER = (SELECT TOP 1 accountId FROM cdm.Account WHERE accountName = 'TechVision Inc');
DECLARE @AccountId3 UNIQUEIDENTIFIER = (SELECT TOP 1 accountId FROM cdm.Account WHERE accountName = 'Global Services Ltd');

-- Sample Contacts
INSERT INTO cdm.Contact (contactId, firstName, lastName, email, phone, accountId, ownerUserId, jobTitle, department, isActive, createdBy)
VALUES
    (NEWID(), 'Alice', 'Johnson', 'alice.johnson@acme.com', '555-1101', @AccountId1, @UserId1, 'Manager', 'Operations', 1, @UserId1),
    (NEWID(), 'Bob', 'Williams', 'bob.williams@acme.com', '555-1102', @AccountId1, @UserId1, 'Director', 'IT', 1, @UserId1),
    (NEWID(), 'Carol', 'Davis', 'carol.davis@techvision.com', '555-1103', @AccountId2, @UserId2, 'Project Manager', 'Services', 1, @UserId1),
    (NEWID(), 'David', 'Miller', 'david.miller@globalservices.com', '555-1104', @AccountId3, @UserId3, 'CTO', 'Technology', 1, @UserId1),
    (NEWID(), 'Emma', 'Wilson', 'emma.wilson@startupx.com', '555-1105', (SELECT TOP 1 accountId FROM cdm.Account WHERE accountName = 'StartupX'), @UserId2, 'CEO', 'Executive', 1, @UserId1);

DECLARE @ContactId1 UNIQUEIDENTIFIER = (SELECT TOP 1 contactId FROM cdm.Contact WHERE firstName = 'Alice');
DECLARE @ContactId2 UNIQUEIDENTIFIER = (SELECT TOP 1 contactId FROM cdm.Contact WHERE firstName = 'Carol');

-- Sample Services
INSERT INTO cdm.Service (serviceId, serviceName, description, isActive, createdBy)
VALUES
    (NEWID(), 'Technical Support', 'On-demand technical support for systems and applications', 1, @UserId1),
    (NEWID(), 'Installation Service', 'Professional installation and setup of products', 1, @UserId1),
    (NEWID(), 'Maintenance & Updates', 'Regular maintenance and software updates', 1, @UserId1),
    (NEWID(), 'Training & Consulting', 'Professional training and consulting services', 1, @UserId1),
    (NEWID(), 'Premium Support', '24/7 premium support with SLA guarantees', 1, @UserId1);

DECLARE @ServiceId1 UNIQUEIDENTIFIER = (SELECT TOP 1 serviceId FROM cdm.Service WHERE serviceName = 'Technical Support');
DECLARE @ServiceId2 UNIQUEIDENTIFIER = (SELECT TOP 1 serviceId FROM cdm.Service WHERE serviceName = 'Installation Service');
DECLARE @ServiceId3 UNIQUEIDENTIFIER = (SELECT TOP 1 serviceId FROM cdm.Service WHERE serviceName = 'Premium Support');

-- Sample Resources
INSERT INTO cdm.Resource (resourceId, resourceName, description, resourceType, userId, isActive, createdBy)
VALUES
    (NEWID(), 'John Smith - Support', 'Senior Support Technician', 'User', @UserId1, 1, @UserId1),
    (NEWID(), 'Maria White - Support', 'Support Engineer', 'User', @UserId2, 1, @UserId1),
    (NEWID(), 'Robert Jones - Support', 'Support Manager', 'User', @UserId3, 1, @UserId1),
    (NEWID(), 'Conference Room A', 'Medium capacity meeting room', 'Facility', NULL, 1, @UserId1),
    (NEWID(), 'Support Terminal 1', 'Customer support workstation', 'Equipment', NULL, 1, @UserId1);

-- Sample Contracts
INSERT INTO cdm.Contract (contractId, contractNumber, contractName, accountId, ownerUserId, startDate, endDate, status, contractValue, contractType, isActive, createdBy)
VALUES
    (NEWID(), 'CNT-2024-001', 'Acme Annual Support Contract', @AccountId1, @UserId1, '2024-01-01', '2024-12-31', 'Active', 50000.00, 'Support', 1, @UserId1),
    (NEWID(), 'CNT-2024-002', 'TechVision Premium Service', @AccountId2, @UserId2, '2024-03-01', '2025-02-28', 'Active', 75000.00, 'Service', 1, @UserId1),
    (NEWID(), 'CNT-2024-003', 'Global Services Maintenance', @AccountId3, @UserId3, '2024-01-01', '2024-12-31', 'Active', 100000.00, 'Maintenance', 1, @UserId1);

DECLARE @ContractId1 UNIQUEIDENTIFIER = (SELECT TOP 1 contractId FROM cdm.Contract WHERE contractNumber = 'CNT-2024-001');
DECLARE @ContractId2 UNIQUEIDENTIFIER = (SELECT TOP 1 contractId FROM cdm.Contract WHERE contractNumber = 'CNT-2024-002');

-- Sample Contract Lines
INSERT INTO cdm.ContractLine (contractLineId, contractId, lineNumber, serviceId, description, quantityIncluded, quantityUsed, price)
VALUES
    (NEWID(), @ContractId1, 1, @ServiceId1, 'Technical Support - 100 hours', 100, 25, 5000.00),
    (NEWID(), @ContractId1, 2, @ServiceId3, 'Premium 24/7 Support', NULL, NULL, 20000.00),
    (NEWID(), @ContractId2, 1, @ServiceId2, 'Installation Services - 5 installations', 5, 2, 10000.00),
    (NEWID(), @ContractId2, 2, @ServiceId1, 'Technical Support - 200 hours', 200, 45, 15000.00);

-- Sample Entitlements
INSERT INTO cdm.Entitlement (entitlementId, entitlementName, description, accountId, startDate, endDate, type, duration, unitType, unitCount, status, isActive, createdBy)
VALUES
    (NEWID(), 'Acme - Silver Support', 'Silver tier support entitlement', @AccountId1, '2024-01-01', '2024-12-31', 'Support', 365, 'Cases', 50, 'Active', 1, @UserId1),
    (NEWID(), 'Acme - Gold Support', 'Gold tier support entitlement', @AccountId1, '2024-01-01', '2024-12-31', 'Support', 365, 'Hours', 200, 'Active', 1, @UserId1),
    (NEWID(), 'TechVision - Platinum', 'Platinum service entitlement', @AccountId2, '2024-03-01', '2025-02-28', 'Service', 365, 'Cases', 100, 'Active', 1, @UserId1);

DECLARE @EntitlementId1 UNIQUEIDENTIFIER = (SELECT TOP 1 entitlementId FROM cdm.Entitlement WHERE entitlementName = 'Acme - Silver Support');

-- Sample Cases
INSERT INTO cdm.Case (caseId, caseNumber, title, description, priority, severity, status, accountId, contactId, ownerUserId, contractId, entitlementId, createdDate, emailAddress, isActive, createdBy)
VALUES
    (NEWID(), 'CSE-2024-0001', 'System Login Issues', 'Users unable to login to the main system', 'High', 'High', 'Active', @AccountId1, @ContactId1, @UserId1, @ContractId1, @EntitlementId1, GETUTCDATE(), 'alice.johnson@acme.com', 1, @UserId1),
    (NEWID(), 'CSE-2024-0002', 'Database Performance', 'Database queries running slowly', 'Medium', 'Medium', 'Active', @AccountId1, @ContactId1, @UserId2, @ContractId1, @EntitlementId1, GETUTCDATE(), 'alice.johnson@acme.com', 1, @UserId1),
    (NEWID(), 'CSE-2024-0003', 'Software Installation Request', 'Need to install new version of software', 'Low', 'Low', 'Waiting', @AccountId2, @ContactId2, @UserId2, @ContractId2, NULL, GETUTCDATE(), 'carol.davis@techvision.com', 1, @UserId1),
    (NEWID(), 'CSE-2024-0004', 'Data Import Error', 'Error importing data from CSV file', 'High', 'High', 'Active', @AccountId3, NULL, @UserId3, NULL, NULL, GETUTCDATE(), NULL, 1, @UserId1);

DECLARE @CaseId1 UNIQUEIDENTIFIER = (SELECT TOP 1 caseId FROM cdm.Case WHERE caseNumber = 'CSE-2024-0001');
DECLARE @CaseId2 UNIQUEIDENTIFIER = (SELECT TOP 1 caseId FROM cdm.Case WHERE caseNumber = 'CSE-2024-0002');
DECLARE @CaseId3 UNIQUEIDENTIFIER = (SELECT TOP 1 caseId FROM cdm.Case WHERE caseNumber = 'CSE-2024-0003');

-- Sample ServiceActivities
INSERT INTO cdm.ServiceActivity (activityId, subject, description, serviceId, caseId, contactId, ownerUserId, startTime, endTime, duration, location, status, isActive, createdBy)
VALUES
    (NEWID(), 'Initial Diagnosis - System Login', 'Initial investigation of login issues', @ServiceId1, @CaseId1, @ContactId1, @UserId1, '2024-09-23 10:00:00', '2024-09-23 11:30:00', 90, 'Remote', 'Completed', 1, @UserId1),
    (NEWID(), 'Database Performance Review', 'Performance analysis and optimization', @ServiceId1, @CaseId2, @ContactId1, @UserId2, '2024-09-23 14:00:00', '2024-09-23 16:00:00', 120, 'Remote', 'Scheduled', 1, @UserId1),
    (NEWID(), 'Software Installation', 'Install new software version', @ServiceId2, @CaseId3, @ContactId2, @UserId3, '2024-09-24 09:00:00', '2024-09-24 12:00:00', 180, 'On-site', 'Scheduled', 1, @UserId1);

-- Sample Tasks
INSERT INTO cdm.Task (taskId, subject, description, priority, status, dueDate, ownerUserId, caseId, createdBy)
VALUES
    (NEWID(), 'Verify System Login Fix', 'Test login functionality after fix', 'High', 'In Progress', '2024-09-24', @UserId1, @CaseId1, @UserId1),
    (NEWID(), 'Gather Database Metrics', 'Collect database performance metrics', 'Medium', 'Open', '2024-09-25', @UserId2, @CaseId2, @UserId1),
    (NEWID(), 'Prepare Installation Package', 'Package software for installation', 'Medium', 'Completed', '2024-09-23', @UserId3, @CaseId3, @UserId1),
    (NEWID(), 'Follow-up with Customer', 'Confirm resolution satisfaction', 'Low', 'Open', '2024-09-26', @UserId1, @CaseId1, @UserId1);

-- Sample Knowledge Articles
INSERT INTO cdm.KnowledgeArticle (articleId, title, content, category, keywords, authorUserId, status, publishedDate, isActive, createdBy)
VALUES
    (NEWID(), 'How to Reset System Login', 'Step-by-step guide for resetting system login credentials...', 'User Management', 'login, reset, credentials', @UserId1, 'Published', GETUTCDATE(), 1, @UserId1),
    (NEWID(), 'Database Query Optimization Tips', 'Best practices for optimizing database queries...', 'Database', 'database, query, optimization, performance', @UserId2, 'Published', GETUTCDATE(), 1, @UserId1),
    (NEWID(), 'Software Installation Guide', 'Complete software installation procedures...', 'Installation', 'installation, setup, software', @UserId3, 'Published', GETUTCDATE(), 1, @UserId1),
    (NEWID(), 'Troubleshooting Common Issues', 'Common issues and their solutions...', 'Troubleshooting', 'troubleshooting, errors, solutions', @UserId1, 'Draft', NULL, 1, @UserId1);

-- Sample SLA KPI Instances
INSERT INTO cdm.SLAKPIInstance (kpiInstanceId, caseId, kpiName, metric, metricValue, metricUnit, targetValue, status)
VALUES
    (NEWID(), @CaseId1, 'First Response Time', 'Response Time', 30, 'Minutes', 60, 'Compliant'),
    (NEWID(), @CaseId1, 'Resolution Time', 'Resolution Time', 480, 'Minutes', 1440, 'Compliant'),
    (NEWID(), @CaseId2, 'First Response Time', 'Response Time', 45, 'Minutes', 60, 'Compliant'),
    (NEWID(), @CaseId3, 'First Response Time', 'Response Time', 120, 'Minutes', 120, 'In Progress');

-- Sample Case Resolution
INSERT INTO cdm.CaseResolution (resolutionId, caseId, description, resolutionType, resolutionStatus, billingStatus, actualServiceDuration, totalCostAmount, createdBy)
VALUES
    (NEWID(), @CaseId1, 'System login issue was caused by expired SSL certificate. Certificate updated and login functionality restored.', 'Technical Resolution', 'Resolved', 'Billable', 90, 500.00, @UserId1);

GO

-- ============================================================================
-- VIEWS FOR COMMON QUERIES
-- ============================================================================

-- View: Active Cases Summary
CREATE VIEW cdm.v_ActiveCasesSummary AS
SELECT
    c.caseNumber,
    c.title,
    c.priority,
    c.status,
    a.accountName,
    ct.firstName + ' ' + ct.lastName AS contactName,
    u.firstName + ' ' + u.lastName AS ownerName,
    c.createdDate,
    DATEDIFF(DAY, c.createdDate, GETUTCDATE()) AS daysSinceCreation
FROM cdm.Case c
LEFT JOIN cdm.Account a ON c.accountId = a.accountId
LEFT JOIN cdm.Contact ct ON c.contactId = ct.contactId
LEFT JOIN cdm.[User] u ON c.ownerUserId = u.userId
WHERE c.status IN ('Active', 'Waiting') AND c.isActive = 1;

-- View: Contract Summary
CREATE VIEW cdm.v_ContractSummary AS
SELECT
    c.contractNumber,
    c.contractName,
    a.accountName,
    c.startDate,
    c.endDate,
    c.status,
    c.contractValue,
    COUNT(cl.contractLineId) AS lineCount,
    u.firstName + ' ' + u.lastName AS owner
FROM cdm.Contract c
LEFT JOIN cdm.Account a ON c.accountId = a.accountId
LEFT JOIN cdm.ContractLine cl ON c.contractId = cl.contractId
LEFT JOIN cdm.[User] u ON c.ownerUserId = u.userId
GROUP BY c.contractId, c.contractNumber, c.contractName, a.accountName, c.startDate, c.endDate, c.status, c.contractValue, u.firstName, u.lastName;

-- View: Case Service Activity
CREATE VIEW cdm.v_CaseServiceActivity AS
SELECT
    c.caseNumber,
    c.title,
    sa.subject,
    s.serviceName,
    u.firstName + ' ' + u.lastName AS technician,
    sa.startTime,
    sa.endTime,
    sa.duration,
    sa.status
FROM cdm.Case c
INNER JOIN cdm.ServiceActivity sa ON c.caseId = sa.caseId
INNER JOIN cdm.Service s ON sa.serviceId = s.serviceId
LEFT JOIN cdm.[User] u ON sa.ownerUserId = u.userId
WHERE c.isActive = 1;

-- View: Customer Support Status
CREATE VIEW cdm.v_CustomerSupportStatus AS
SELECT
    a.accountName,
    COUNT(DISTINCT c.caseId) AS totalCases,
    SUM(CASE WHEN c.status = 'Active' THEN 1 ELSE 0 END) AS activeCases,
    SUM(CASE WHEN c.status = 'Resolved' THEN 1 ELSE 0 END) AS resolvedCases,
    SUM(CASE WHEN c.status = 'Closed' THEN 1 ELSE 0 END) AS closedCases,
    COUNT(DISTINCT e.entitlementId) AS activeEntitlements
FROM cdm.Account a
LEFT JOIN cdm.Case c ON a.accountId = c.accountId
LEFT JOIN cdm.Entitlement e ON a.accountId = e.accountId AND e.status = 'Active'
GROUP BY a.accountId, a.accountName;

GO

PRINT '========================================';
PRINT 'CDM Customer Service Database Created Successfully!';
PRINT '========================================';
PRINT 'Total Tables: 26';
PRINT 'Tables include:';
PRINT '  - Foundation: User, Site, Account, Contact';
PRINT '  - Services: Service, Resource, FacilityEquipment, SchedulingGroup, ResourceSpecification';
PRINT '  - Contracts: Contract, ContractLine, ServiceContractContact';
PRINT '  - Entitlements: Entitlement, EntitlementContact, EntitlementProduct';
PRINT '  - Cases: Case, CaseResolution, ChildIncidentCount';
PRINT '  - Activities: ServiceActivity, ActivityParty, Task';
PRINT '  - Knowledge: KnowledgeArticle, KnowledgeArticleIncident, IncidentKnowledgeBaseRecord';
PRINT '  - SLA: SLAKPIInstance';
PRINT '  - Other: ResourceExpansion';
PRINT 'Views: 4 (v_ActiveCasesSummary, v_ContractSummary, v_CaseServiceActivity, v_CustomerSupportStatus)';
PRINT '========================================';