-- Database-Schema.sql
-- Complete database schema for Citrix & Entra ID Login Data Capture Project
-- Creates tables, views, indexes, and sample reporting structures
-- Compatible with: Azure SQL Managed Instance / Azure SQL Database

-- =====================================================
-- 1. MAIN DATA TABLES
-- =====================================================

-- Citrix Sessions Table
CREATE TABLE CitrixSessions (
    SessionId INT PRIMARY KEY,
    UserName NVARCHAR(256),
    FullUserName NVARCHAR(256),
    MachineName NVARCHAR(256),
    DesktopGroupName NVARCHAR(256),
    LogOnTime DATETIME2,
    LogOffTime DATETIME2,
    ConnectedViaHostName NVARCHAR(256),
    ClientName NVARCHAR(256),
    ClientAddress NVARCHAR(256),
    ClientVersion NVARCHAR(64),
    ClientType NVARCHAR(64),
    SessionState NVARCHAR(64),
    SessionReconnection NVARCHAR(64),
    ApplicationName NVARCHAR(256),
    DeliveryGroupName NVARCHAR(256),
    SessionDurationMinutes AS DATEDIFF(MINUTE, LogOnTime, ISNULL(LogOffTime, GETUTCDATE())),
    OtherProperties NVARCHAR(MAX), -- JSON for all other/complex fields
    DataSource NVARCHAR(50) DEFAULT 'Citrix',
    InsertedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

-- Entra ID Sign-ins Table
CREATE TABLE EntraSignIns (
    Id NVARCHAR(128) PRIMARY KEY,
    UserId NVARCHAR(128),
    UserPrincipalName NVARCHAR(256),
    UserDisplayName NVARCHAR(256),
    AppId NVARCHAR(128),
    AppDisplayName NVARCHAR(256),
    CreatedDateTime DATETIME2,
    SignInEventTypes NVARCHAR(128),
    RiskLevel NVARCHAR(64),
    RiskState NVARCHAR(64),
    RiskDetail NVARCHAR(128),
    IPAddress NVARCHAR(64),
    ClientAppUsed NVARCHAR(128),
    DeviceId NVARCHAR(128),
    DeviceName NVARCHAR(256),
    DeviceOS NVARCHAR(128),
    DeviceBrowser NVARCHAR(128),
    LocationCity NVARCHAR(128),
    LocationState NVARCHAR(128),
    LocationCountryOrRegion NVARCHAR(128),
    AuthenticationMethod NVARCHAR(256),
    ConditionalAccessStatus NVARCHAR(64),
    IsInteractive BIT,
    IsSuccessful BIT,
    ErrorCode INT,
    FailureReason NVARCHAR(512),
    CorrelationId NVARCHAR(128),
    OtherProperties NVARCHAR(MAX), -- JSON for all other/complex fields
    DataSource NVARCHAR(50) DEFAULT 'EntraID',
    InsertedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

-- Unified Users Table (for cross-system reporting)
CREATE TABLE UnifiedUsers (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    UserPrincipalName NVARCHAR(256) UNIQUE,
    DisplayName NVARCHAR(256),
    CitrixUserName NVARCHAR(256),
    EntraUserId NVARCHAR(128),
    Department NVARCHAR(128),
    JobTitle NVARCHAR(128),
    IsActive BIT DEFAULT 1,
    LastCitrixLogin DATETIME2,
    LastEntraLogin DATETIME2,
    CreatedAt DATETIME2 DEFAULT SYSUTCDATETIME(),
    UpdatedAt DATETIME2 DEFAULT SYSUTCDATETIME()
);

-- =====================================================
-- 2. PERFORMANCE INDEXES
-- =====================================================

-- Citrix Sessions Indexes
CREATE INDEX IX_CitrixSessions_UserName ON CitrixSessions(UserName);
CREATE INDEX IX_CitrixSessions_LogOnTime ON CitrixSessions(LogOnTime);
CREATE INDEX IX_CitrixSessions_MachineName ON CitrixSessions(MachineName);
CREATE INDEX IX_CitrixSessions_SessionState ON CitrixSessions(SessionState);
CREATE INDEX IX_CitrixSessions_ClientAddress ON CitrixSessions(ClientAddress);

-- Entra ID Sign-ins Indexes  
CREATE INDEX IX_EntraSignIns_UserPrincipalName ON EntraSignIns(UserPrincipalName);
CREATE INDEX IX_EntraSignIns_CreatedDateTime ON EntraSignIns(CreatedDateTime);
CREATE INDEX IX_EntraSignIns_AppDisplayName ON EntraSignIns(AppDisplayName);
CREATE INDEX IX_EntraSignIns_IsSuccessful ON EntraSignIns(IsSuccessful);
CREATE INDEX IX_EntraSignIns_IPAddress ON EntraSignIns(IPAddress);
CREATE INDEX IX_EntraSignIns_LocationCountryOrRegion ON EntraSignIns(LocationCountryOrRegion);

-- Unified Users Indexes
CREATE INDEX IX_UnifiedUsers_UserPrincipalName ON UnifiedUsers(UserPrincipalName);
CREATE INDEX IX_UnifiedUsers_CitrixUserName ON UnifiedUsers(CitrixUserName);
CREATE INDEX IX_UnifiedUsers_EntraUserId ON UnifiedUsers(EntraUserId);
CREATE INDEX IX_UnifiedUsers_LastCitrixLogin ON UnifiedUsers(LastCitrixLogin);
CREATE INDEX IX_UnifiedUsers_LastEntraLogin ON UnifiedUsers(LastEntraLogin);

-- =====================================================
-- 3. REPORTING VIEWS
-- =====================================================

-- Combined login activity view
CREATE VIEW vw_CombinedLoginActivity AS
SELECT 
    'Citrix' as LoginType,
    UserName as UserIdentifier,
    FullUserName as DisplayName,
    LogOnTime as LoginTime,
    LogOffTime as LogoutTime,
    ClientAddress as IPAddress,
    MachineName as SourceSystem,
    SessionState as Status,
    DataSource,
    InsertedAt
FROM CitrixSessions
UNION ALL
SELECT 
    'EntraID' as LoginType,
    UserPrincipalName as UserIdentifier,
    UserDisplayName as DisplayName,
    CreatedDateTime as LoginTime,
    NULL as LogoutTime,
    IPAddress,
    AppDisplayName as SourceSystem,
    CASE WHEN IsSuccessful = 1 THEN 'Success' ELSE 'Failed' END as Status,
    DataSource,
    InsertedAt
FROM EntraSignIns;

-- User activity summary view
CREATE VIEW vw_UserActivitySummary AS
SELECT 
    u.UserPrincipalName,
    u.DisplayName,
    u.LastCitrixLogin,
    u.LastEntraLogin,
    COALESCE(c.CitrixLogins, 0) as CitrixLoginsLast30Days,
    COALESCE(e.EntraLoginsLast30Days, 0) as EntraLoginsLast30Days,
    CASE 
        WHEN u.LastCitrixLogin > DATEADD(day, -30, GETUTCDATE()) OR u.LastEntraLogin > DATEADD(day, -30, GETUTCDATE()) 
        THEN 'Active' 
        ELSE 'Inactive' 
    END as ActivityStatus
FROM UnifiedUsers u
LEFT JOIN (
    SELECT UserName, COUNT(*) as CitrixLogins
    FROM CitrixSessions 
    WHERE LogOnTime > DATEADD(day, -30, GETUTCDATE())
    GROUP BY UserName
) c ON u.CitrixUserName = c.UserName
LEFT JOIN (
    SELECT UserPrincipalName, COUNT(*) as EntraLoginsLast30Days
    FROM EntraSignIns 
    WHERE CreatedDateTime > DATEADD(day, -30, GETUTCDATE()) AND IsSuccessful = 1
    GROUP BY UserPrincipalName
) e ON u.UserPrincipalName = e.UserPrincipalName;

-- Daily login statistics view
CREATE VIEW vw_DailyLoginStats AS
SELECT 
    CAST(LoginTime as DATE) as LoginDate,
    LoginType,
    COUNT(*) as TotalLogins,
    COUNT(DISTINCT UserIdentifier) as UniqueUsers,
    SUM(CASE WHEN Status IN ('Success', 'Active', 'Connected') THEN 1 ELSE 0 END) as SuccessfulLogins,
    SUM(CASE WHEN Status NOT IN ('Success', 'Active', 'Connected') THEN 1 ELSE 0 END) as FailedLogins
FROM vw_CombinedLoginActivity
WHERE LoginTime >= DATEADD(day, -90, GETUTCDATE()) -- Last 90 days
GROUP BY CAST(LoginTime as DATE), LoginType;

-- Security events view (failed logins, suspicious activities)
CREATE VIEW vw_SecurityEvents AS
SELECT 
    'Citrix-FailedLogin' as EventType,
    UserName as UserIdentifier,
    FullUserName as DisplayName,
    LogOnTime as EventTime,
    ClientAddress as SourceIP,
    MachineName as TargetResource,
    SessionState as Details,
    'Citrix' as System
FROM CitrixSessions
WHERE SessionState LIKE '%Failed%' OR SessionState LIKE '%Denied%' OR SessionState LIKE '%Error%'

UNION ALL

SELECT 
    'EntraID-FailedLogin' as EventType,
    UserPrincipalName as UserIdentifier,
    UserDisplayName as DisplayName,
    CreatedDateTime as EventTime,
    IPAddress as SourceIP,
    AppDisplayName as TargetResource,
    FailureReason as Details,
    'EntraID' as System
FROM EntraSignIns
WHERE IsSuccessful = 0

UNION ALL

SELECT 
    'EntraID-RiskySignIn' as EventType,
    UserPrincipalName as UserIdentifier,
    UserDisplayName as DisplayName,
    CreatedDateTime as EventTime,
    IPAddress as SourceIP,
    AppDisplayName as TargetResource,
    CONCAT('Risk Level: ', RiskLevel, ', Risk State: ', RiskState) as Details,
    'EntraID' as System
FROM EntraSignIns
WHERE RiskLevel IN ('high', 'medium') AND RiskState = 'atRisk';

-- =====================================================
-- 4. STORED PROCEDURES
-- =====================================================

-- Procedure to update unified users table
CREATE PROCEDURE sp_UpdateUnifiedUsers
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Update last login times for existing users
    UPDATE u
    SET 
        LastCitrixLogin = c.LastCitrixLogin,
        LastEntraLogin = e.LastEntraLogin,
        UpdatedAt = SYSUTCDATETIME()
    FROM UnifiedUsers u
    LEFT JOIN (
        SELECT UserName, MAX(LogOnTime) as LastCitrixLogin
        FROM CitrixSessions
        GROUP BY UserName
    ) c ON u.CitrixUserName = c.UserName
    LEFT JOIN (
        SELECT UserPrincipalName, MAX(CreatedDateTime) as LastEntraLogin
        FROM EntraSignIns
        WHERE IsSuccessful = 1
        GROUP BY UserPrincipalName
    ) e ON u.UserPrincipalName = e.UserPrincipalName;
    
    -- Insert new users from Citrix
    INSERT INTO UnifiedUsers (CitrixUserName, DisplayName, LastCitrixLogin)
    SELECT DISTINCT 
        cs.UserName,
        cs.FullUserName,
        MAX(cs.LogOnTime)
    FROM CitrixSessions cs
    LEFT JOIN UnifiedUsers u ON u.CitrixUserName = cs.UserName
    WHERE u.Id IS NULL AND cs.UserName IS NOT NULL
    GROUP BY cs.UserName, cs.FullUserName;
    
    -- Insert new users from Entra ID
    INSERT INTO UnifiedUsers (UserPrincipalName, EntraUserId, DisplayName, LastEntraLogin)
    SELECT DISTINCT 
        es.UserPrincipalName,
        es.UserId,
        es.UserDisplayName,
        MAX(es.CreatedDateTime)
    FROM EntraSignIns es
    LEFT JOIN UnifiedUsers u ON u.UserPrincipalName = es.UserPrincipalName OR u.EntraUserId = es.UserId
    WHERE u.Id IS NULL AND es.UserPrincipalName IS NOT NULL AND es.IsSuccessful = 1
    GROUP BY es.UserPrincipalName, es.UserId, es.UserDisplayName;
    
END;

-- =====================================================
-- 5. SAMPLE REPORTING QUERIES
-- =====================================================

-- Query 1: Users with no activity in last 30 days
-- SELECT * FROM vw_UserActivitySummary WHERE ActivityStatus = 'Inactive';

-- Query 2: Login failures by system in last 24 hours
/*
SELECT 
    DataSource,
    COUNT(*) as FailureCount
FROM (
    SELECT DataSource FROM CitrixSessions 
    WHERE LogOnTime > DATEADD(day, -1, GETUTCDATE()) 
    AND SessionState LIKE '%Failed%'
    UNION ALL
    SELECT DataSource FROM EntraSignIns 
    WHERE CreatedDateTime > DATEADD(day, -1, GETUTCDATE()) 
    AND IsSuccessful = 0
) failures
GROUP BY DataSource;
*/

-- Query 3: Peak login hours analysis
/*
SELECT 
    DATEPART(hour, LoginTime) as LoginHour,
    LoginType,
    COUNT(*) as LoginCount
FROM vw_CombinedLoginActivity
WHERE LoginTime > DATEADD(day, -7, GETUTCDATE())
GROUP BY DATEPART(hour, LoginTime), LoginType
ORDER BY LoginHour;
*/

-- Query 4: Top applications by usage (Entra ID)
/*
SELECT 
    AppDisplayName,
    COUNT(*) as SignInCount,
    COUNT(DISTINCT UserPrincipalName) as UniqueUsers
FROM EntraSignIns
WHERE CreatedDateTime > DATEADD(day, -30, GETUTCDATE()) AND IsSuccessful = 1
GROUP BY AppDisplayName
ORDER BY SignInCount DESC;
*/

-- Query 5: Geographic distribution of logins
/*
SELECT 
    LocationCountryOrRegion,
    LocationState,
    LocationCity,
    COUNT(*) as LoginCount
FROM EntraSignIns
WHERE CreatedDateTime > DATEADD(day, -7, GETUTCDATE()) AND IsSuccessful = 1
    AND LocationCountryOrRegion IS NOT NULL
GROUP BY LocationCountryOrRegion, LocationState, LocationCity
ORDER BY LoginCount DESC;
*/

-- =====================================================
-- 6. DATA RETENTION POLICY (Optional)
-- =====================================================

-- Stored procedure for data cleanup (run monthly)
CREATE PROCEDURE sp_CleanupOldData
    @RetentionDays INT = 365
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @CutoffDate DATETIME2 = DATEADD(day, -@RetentionDays, GETUTCDATE());
    
    -- Archive or delete old Citrix sessions
    DELETE FROM CitrixSessions WHERE LogOnTime < @CutoffDate;
    
    -- Archive or delete old Entra ID sign-ins  
    DELETE FROM EntraSignIns WHERE CreatedDateTime < @CutoffDate;
    
    -- Clean up orphaned unified users (optional)
    -- UPDATE UnifiedUsers SET IsActive = 0 
    -- WHERE LastCitrixLogin < @CutoffDate AND LastEntraLogin < @CutoffDate;
    
END;

-- =====================================================
-- 7. GRANTS AND PERMISSIONS
-- =====================================================

-- Create a dedicated read-only user for reporting
-- CREATE USER [PowerBIService] WITHOUT LOGIN;
-- GRANT SELECT ON vw_CombinedLoginActivity TO [PowerBIService];
-- GRANT SELECT ON vw_UserActivitySummary TO [PowerBIService];  
-- GRANT SELECT ON vw_DailyLoginStats TO [PowerBIService];
-- GRANT SELECT ON vw_SecurityEvents TO [PowerBIService];

-- Create a service account for Logic Apps
-- CREATE USER [LogicAppsService] WITHOUT LOGIN;
-- GRANT INSERT, SELECT ON CitrixSessions TO [LogicAppsService];
-- GRANT INSERT, SELECT ON EntraSignIns TO [LogicAppsService];
-- GRANT SELECT, INSERT, UPDATE ON UnifiedUsers TO [LogicAppsService];
-- GRANT EXECUTE ON sp_UpdateUnifiedUsers TO [LogicAppsService];

PRINT 'Database schema creation completed successfully!';
PRINT 'Tables created: CitrixSessions, EntraSignIns, UnifiedUsers';
PRINT 'Views created: vw_CombinedLoginActivity, vw_UserActivitySummary, vw_DailyLoginStats, vw_SecurityEvents';
PRINT 'Stored procedures created: sp_UpdateUnifiedUsers, sp_CleanupOldData';
PRINT 'Ready for data collection and reporting!'; 