-- FuturisticREPO - Create Core Tables
-- SOURCE OF TRUTH: CMS | DBAOps Futuristic Enterprise™
USE [FuturisticREPO];
GO

-- config.ServerInventory: Synced from CMS (source of truth)
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('config') AND name = 'ServerInventory')
BEGIN
    CREATE TABLE config.ServerInventory (
        ServerID INT IDENTITY(1,1) NOT NULL,
        ServerName NVARCHAR(256) NOT NULL,
        CMSGroupName NVARCHAR(256) NULL,
        Environment NVARCHAR(50) NULL,
        IsActive BIT NOT NULL DEFAULT 1,
        IsSQLServer BIT NOT NULL DEFAULT 1,
        SQLVersion NVARCHAR(50) NULL,
        CMSLastSyncDate DATETIME2 NULL,
        CreatedDate DATETIME2 DEFAULT SYSDATETIME(),
        ModifiedDate DATETIME2 DEFAULT SYSDATETIME(),
        CONSTRAINT PK_ServerInventory PRIMARY KEY (ServerID),
        CONSTRAINT UQ_ServerInventory UNIQUE (ServerName)
    );
    PRINT 'Created config.ServerInventory';
END
GO

-- license.LicenseTiers: Tier definitions and pricing
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('license') AND name = 'LicenseTiers')
BEGIN
    CREATE TABLE license.LicenseTiers (
        TierID INT IDENTITY(1,1) NOT NULL,
        TierCode NVARCHAR(50) NOT NULL,
        TierName NVARCHAR(200) NOT NULL,
        MaxServers INT NULL,
        MaxComputers INT NULL,
        DurationMonths INT NULL,
        PriceUSD DECIMAL(18,2) NULL,
        IndustryCode NVARCHAR(50) NULL,
        IsActive BIT NOT NULL DEFAULT 1,
        CONSTRAINT PK_LicenseTiers PRIMARY KEY (TierID)
    );
    PRINT 'Created license.LicenseTiers';
END
GO

-- license.LicenseKeys: Active licenses
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('license') AND name = 'LicenseKeys')
BEGIN
    CREATE TABLE license.LicenseKeys (
        LicenseID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
        LicenseKey NVARCHAR(100) NOT NULL,
        TierID INT NOT NULL,
        ActivatedAt DATETIME2 DEFAULT SYSDATETIME(),
        ExpiresAt DATETIME2 NOT NULL,
        ComputerID NVARCHAR(256) NULL,
        IsActive BIT NOT NULL DEFAULT 1,
        CONSTRAINT PK_LicenseKeys PRIMARY KEY (LicenseID),
        CONSTRAINT FK_LicenseKeys_Tier FOREIGN KEY (TierID) REFERENCES license.LicenseTiers(TierID)
    );
    PRINT 'Created license.LicenseKeys';
END
GO

-- config.ComplianceThresholds: Per-industry thresholds
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('config') AND name = 'ComplianceThresholds')
BEGIN
    CREATE TABLE config.ComplianceThresholds (
        ThresholdID INT IDENTITY(1,1) NOT NULL,
        IndustryCode NVARCHAR(50) NOT NULL,
        MetricCode NVARCHAR(100) NOT NULL,
        WarningValue DECIMAL(18,4) NULL,
        CriticalValue DECIMAL(18,4) NULL,
        ComparisonType NVARCHAR(20) NULL,
        ComplianceStandard NVARCHAR(200) NULL,
        IsRequired BIT NOT NULL DEFAULT 1,
        CONSTRAINT PK_ComplianceThresholds PRIMARY KEY (ThresholdID)
    );
    PRINT 'Created config.ComplianceThresholds';
END
GO

-- discovery.DiscoveryResults: Results from 14 risk discovery scripts
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('discovery') AND name = 'DiscoveryResults')
BEGIN
    CREATE TABLE discovery.DiscoveryResults (
        ResultID BIGINT IDENTITY(1,1) NOT NULL,
        DiscoveryType NVARCHAR(100) NOT NULL,
        ServerName NVARCHAR(256) NOT NULL,
        DatabaseName NVARCHAR(256) NULL,
        Finding NVARCHAR(MAX) NULL,
        Severity NVARCHAR(40) NULL,
        Status NVARCHAR(50) NULL,
        CollectedAt DATETIME2 DEFAULT SYSDATETIME(),
        CONSTRAINT PK_DiscoveryResults PRIMARY KEY (ResultID)
    );
    CREATE INDEX IX_DiscoveryResults_Type ON discovery.DiscoveryResults(DiscoveryType, ServerName);
    PRINT 'Created discovery.DiscoveryResults';
END
GO

-- audit.AccessLog: Enterprise safeguard - all access audit
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('audit') AND name = 'AccessLog')
BEGIN
    CREATE TABLE audit.AccessLog (
        LogID BIGINT IDENTITY(1,1) NOT NULL,
        EventTime DATETIME2 DEFAULT SYSDATETIME(),
        EventType NVARCHAR(100) NOT NULL,
        UserName NVARCHAR(256) DEFAULT SUSER_SNAME(),
        ObjectName NVARCHAR(500) NULL,
        Details NVARCHAR(MAX) NULL,
        CONSTRAINT PK_AccessLog PRIMARY KEY (LogID)
    );
    PRINT 'Created audit.AccessLog';
END
GO

-- admin.ErrorLog
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('admin') AND name = 'ErrorLog')
BEGIN
    CREATE TABLE admin.ErrorLog (
        ErrorID INT IDENTITY(1,1) NOT NULL,
        ServerName NVARCHAR(256) NULL,
        ErrorMessage NVARCHAR(MAX) NULL,
        ErrorDate DATETIME2 DEFAULT SYSDATETIME(),
        ErrorSource NVARCHAR(200) NULL,
        CONSTRAINT PK_ErrorLog PRIMARY KEY (ErrorID)
    );
    PRINT 'Created admin.ErrorLog';
END
GO
