-- ============================================================================
-- JBRhoylle DBA Suite v2.4.0 - Complete Database Schema
-- Author: JB Gakumba - Sr SQL DBA / DBAOps Architect
-- ============================================================================

USE [master]
GO

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'JBRhoylleRepository')
    CREATE DATABASE [JBRhoylleRepository]
GO

USE [JBRhoylleRepository]
GO

-- SCHEMAS
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'config') EXEC('CREATE SCHEMA [config]')
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'fact') EXEC('CREATE SCHEMA [fact]')
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'monitoring') EXEC('CREATE SCHEMA [monitoring]')
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'compliance') EXEC('CREATE SCHEMA [compliance]')
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'dashboard') EXEC('CREATE SCHEMA [dashboard]')
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'inventory') EXEC('CREATE SCHEMA [inventory]')
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'patching') EXEC('CREATE SCHEMA [patching]')
GO

-- CONFIG.SERVERINVENTORY
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ServerInventory' AND schema_id = SCHEMA_ID('config'))
CREATE TABLE config.ServerInventory (
    ServerID INT IDENTITY(1,1) PRIMARY KEY,
    ServerName NVARCHAR(255) NOT NULL UNIQUE,
    Environment NVARCHAR(50),
    SQLVersion NVARCHAR(100),
    Edition NVARCHAR(100),
    IsActive BIT DEFAULT 1,
    IsSQLServer BIT DEFAULT 1,
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedDate DATETIME DEFAULT GETDATE()
)
GO

-- CONFIG.REACHABILITYSTATUS
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ReachabilityStatus' AND schema_id = SCHEMA_ID('config'))
CREATE TABLE config.ReachabilityStatus (
    ServerName NVARCHAR(255) PRIMARY KEY,
    IsOnline BIT,
    LastChecked DATETIME,
    ResponseTimeMs INT,
    ErrorMessage NVARCHAR(MAX)
)
GO

-- CONFIG.ONCALLROTATION
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'OnCallRotation' AND schema_id = SCHEMA_ID('config'))
CREATE TABLE config.OnCallRotation (
    RotationID INT IDENTITY(1,1) PRIMARY KEY,
    PrimaryDBA NVARCHAR(255),
    PrimaryPhone NVARCHAR(50),
    PrimaryEmail NVARCHAR(255),
    BackupDBA NVARCHAR(255),
    BackupPhone NVARCHAR(50),
    BackupEmail NVARCHAR(255),
    RotationStartDate DATETIME,
    RotationEndDate DATETIME,
    IsActive BIT DEFAULT 1
)
GO

-- CONFIG.LICENSEINFO
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'LicenseInfo' AND schema_id = SCHEMA_ID('config'))
CREATE TABLE config.LicenseInfo (
    LicenseID INT IDENTITY(1,1) PRIMARY KEY,
    LicenseKey NVARCHAR(50),
    Organization NVARCHAR(255),
    Tier NVARCHAR(50),
    MaxServers INT,
    ExpirationDate DATE,
    InstalledDate DATETIME DEFAULT GETDATE()
)
GO

-- FACT.DISKSPACEMETRICS
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'DiskSpaceMetrics' AND schema_id = SCHEMA_ID('fact'))
CREATE TABLE fact.DiskSpaceMetrics (
    MetricID INT IDENTITY(1,1) PRIMARY KEY,
    ServerName NVARCHAR(255),
    DriveLetter NVARCHAR(10),
    TotalSpaceGB DECIMAL(18,2),
    FreeSpaceGB DECIMAL(18,2),
    FreePercent DECIMAL(5,2),
    CaptureDate DATETIME DEFAULT GETDATE(),
    INDEX IX_DiskSpace_Server (ServerName, CaptureDate)
)
GO

-- FACT.BACKUPHISTORY
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'BackupHistory' AND schema_id = SCHEMA_ID('fact'))
CREATE TABLE fact.BackupHistory (
    BackupID INT IDENTITY(1,1) PRIMARY KEY,
    ServerName NVARCHAR(255),
    DatabaseName NVARCHAR(255),
    BackupType NVARCHAR(50),
    BackupStartDate DATETIME,
    BackupFinishDate DATETIME,
    BackupSizeMB DECIMAL(18,2),
    CaptureDate DATETIME DEFAULT GETDATE()
)
GO

-- MONITORING.ALERTS
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Alerts' AND schema_id = SCHEMA_ID('monitoring'))
CREATE TABLE monitoring.Alerts (
    AlertID INT IDENTITY(1,1) PRIMARY KEY,
    AlertType NVARCHAR(50),
    Severity NVARCHAR(20),
    ServerName NVARCHAR(255),
    Title NVARCHAR(255),
    Message NVARCHAR(MAX),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ResolvedDate DATETIME,
    ResolvedBy NVARCHAR(100)
)
GO

-- COMPLIANCE.CONTROLS
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Controls' AND schema_id = SCHEMA_ID('compliance'))
CREATE TABLE compliance.Controls (
    ControlID INT IDENTITY(1,1) PRIMARY KEY,
    FrameworkName NVARCHAR(100),
    ControlCode NVARCHAR(50),
    ControlName NVARCHAR(255),
    Description NVARCHAR(MAX),
    Severity NVARCHAR(20)
)
GO

-- COMPLIANCE.RESULTS
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Results' AND schema_id = SCHEMA_ID('compliance'))
CREATE TABLE compliance.Results (
    ResultID INT IDENTITY(1,1) PRIMARY KEY,
    ControlID INT,
    ServerName NVARCHAR(255),
    Status NVARCHAR(50),
    ScanDate DATETIME DEFAULT GETDATE()
)
GO

-- VIEWS
IF EXISTS (SELECT 1 FROM sys.views WHERE name = 'vw_ServerHealth' AND schema_id = SCHEMA_ID('dashboard'))
    DROP VIEW dashboard.vw_ServerHealth
GO
CREATE VIEW dashboard.vw_ServerHealth AS
SELECT si.ServerName, si.Environment, si.SQLVersion,
       ISNULL(rs.IsOnline, 0) AS IsOnline, rs.LastChecked,
       CASE WHEN rs.IsOnline = 0 THEN 'Critical'
            WHEN rs.ResponseTimeMs > 1000 THEN 'Warning'
            ELSE 'Healthy' END AS HealthStatus
FROM config.ServerInventory si
LEFT JOIN config.ReachabilityStatus rs ON si.ServerName = rs.ServerName
WHERE si.IsActive = 1
GO

PRINT 'DBAOps Repository schema created successfully!'
GO
