-- DBAOps Sovereign™ — v4 Fleet Detail & Technology Detection Schema
-- Adds: server contacts/owners, application inventory, HA/DR technology detection, ETL jobs
-- Powered by RHOYLLE INNOVATION EMPIRE LLC

USE SovereignRepository;
GO

-- New schema for ETL
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'etl')
    EXEC('CREATE SCHEMA etl');
GO

-- =============================================
-- Extend ctl.Servers with additional metadata
-- =============================================
IF COL_LENGTH('ctl.Servers', 'ProductBuild') IS NULL
    ALTER TABLE ctl.Servers ADD ProductBuild NVARCHAR(64) NULL;
IF COL_LENGTH('ctl.Servers', 'OperatingSystem') IS NULL
    ALTER TABLE ctl.Servers ADD OperatingSystem NVARCHAR(128) NULL;
IF COL_LENGTH('ctl.Servers', 'IPAddress') IS NULL
    ALTER TABLE ctl.Servers ADD IPAddress NVARCHAR(64) NULL;
IF COL_LENGTH('ctl.Servers', 'FQDN') IS NULL
    ALTER TABLE ctl.Servers ADD FQDN NVARCHAR(256) NULL;
IF COL_LENGTH('ctl.Servers', 'Port') IS NULL
    ALTER TABLE ctl.Servers ADD Port INT NULL;
IF COL_LENGTH('ctl.Servers', 'DataCenter') IS NULL
    ALTER TABLE ctl.Servers ADD DataCenter NVARCHAR(128) NULL;
IF COL_LENGTH('ctl.Servers', 'Location') IS NULL
    ALTER TABLE ctl.Servers ADD Location NVARCHAR(256) NULL;
IF COL_LENGTH('ctl.Servers', 'Notes') IS NULL
    ALTER TABLE ctl.Servers ADD Notes NVARCHAR(MAX) NULL;
IF COL_LENGTH('ctl.Servers', 'IsActive') IS NULL
    ALTER TABLE ctl.Servers ADD IsActive BIT NOT NULL DEFAULT 1;
IF COL_LENGTH('ctl.Servers', 'DecommissionedAt') IS NULL
    ALTER TABLE ctl.Servers ADD DecommissionedAt DATETIME2 NULL;
IF COL_LENGTH('ctl.Servers', 'DecommissionedBy') IS NULL
    ALTER TABLE ctl.Servers ADD DecommissionedBy NVARCHAR(256) NULL;
GO

-- =============================================
-- ctl.ServerContacts — owners, DBAs, technicians
-- =============================================
IF OBJECT_ID('ctl.ServerContacts','U') IS NOT NULL DROP TABLE ctl.ServerContacts;
CREATE TABLE ctl.ServerContacts (
    ContactId       INT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    ContactName     NVARCHAR(256) NOT NULL,
    ContactRole     NVARCHAR(64) NOT NULL,
    Email           NVARCHAR(256) NULL,
    Phone           NVARCHAR(64) NULL,
    Department      NVARCHAR(128) NULL,
    IsPrimary       BIT NOT NULL DEFAULT 0,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_ContactRole CHECK (ContactRole IN (
        'Owner','DBA','Technician','AppDeveloper','Manager','Auditor',
        'SecurityOfficer','Vendor','OnCall','Other'
    ))
);
GO
CREATE INDEX IX_ServerContacts_Server ON ctl.ServerContacts (ServerId);
GO

-- =============================================
-- ctl.ServerApplications — apps hosted per server
-- =============================================
IF OBJECT_ID('ctl.ServerApplications','U') IS NOT NULL DROP TABLE ctl.ServerApplications;
CREATE TABLE ctl.ServerApplications (
    AppId           INT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    ApplicationName NVARCHAR(256) NOT NULL,
    AppVersion      NVARCHAR(64) NULL,
    AppOwner        NVARCHAR(256) NULL,
    AppType         NVARCHAR(64) NULL,
    DatabaseName    NVARCHAR(256) NULL,
    ConnectionString NVARCHAR(MAX) NULL,
    ServiceAccount  NVARCHAR(256) NULL,
    IntegrationNotes NVARCHAR(MAX) NULL,
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Active',
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_AppType CHECK (AppType IN (
        'WebApp','API','ETL','Reporting','Microservice','Desktop',
        'BatchJob','ThirdParty','Internal','Other'
    ))
);
GO
CREATE INDEX IX_ServerApps_Server ON ctl.ServerApplications (ServerId);
GO

-- =============================================
-- ctl.DatabaseOwnership — who owns which database
-- =============================================
IF OBJECT_ID('ctl.DatabaseOwnership','U') IS NOT NULL DROP TABLE ctl.DatabaseOwnership;
CREATE TABLE ctl.DatabaseOwnership (
    OwnershipId     INT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    DatabaseName    NVARCHAR(256) NOT NULL,
    OwnerName       NVARCHAR(256) NOT NULL,
    OwnerEmail      NVARCHAR(256) NULL,
    OwnerRole       NVARCHAR(64) NULL,
    Department      NVARCHAR(128) NULL,
    Classification  NVARCHAR(64) NULL,
    Notes           NVARCHAR(MAX) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT UQ_DBOwnership UNIQUE (ServerId, DatabaseName)
);
GO

-- =============================================
-- hadr.TechnologyDetection — all HA/DR technologies
-- =============================================
IF OBJECT_ID('hadr.TechnologyDetection','U') IS NOT NULL DROP TABLE hadr.TechnologyDetection;
CREATE TABLE hadr.TechnologyDetection (
    DetectionId     UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL REFERENCES ctl.Servers(ServerId),
    Technology      NVARCHAR(64) NOT NULL,
    TechType        NVARCHAR(64) NOT NULL,
    [Name]          NVARCHAR(256) NULL,
    Status          NVARCHAR(64) NULL,
    Role            NVARCHAR(64) NULL,
    PartnerServer   NVARCHAR(256) NULL,
    DatabaseName    NVARCHAR(256) NULL,
    ConfigJson      NVARCHAR(MAX) NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_TechType CHECK (TechType IN (
        'AlwaysOn_AG','AlwaysOn_FCI','LogShipping','Mirroring',
        'Replication_Transactional','Replication_Merge','Replication_Snapshot',
        'Clustering_WSFC','Clustering_Linux','Streaming_Replication',
        'DataGuard','GoldenGate','GroupReplication','InnoDB_Cluster',
        'Azure_GeoReplication','AWS_MultiAZ','AWS_ReadReplica','Other'
    ))
);
GO
CREATE INDEX IX_TechDetection_Server ON hadr.TechnologyDetection (ServerId, TechType);
GO

-- =============================================
-- etl.Jobs — ETL pipeline tracking
-- =============================================
IF OBJECT_ID('etl.JobHistory','U') IS NOT NULL DROP TABLE etl.JobHistory;
IF OBJECT_ID('etl.Jobs','U') IS NOT NULL DROP TABLE etl.Jobs;

CREATE TABLE etl.Jobs (
    JobId           UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    JobName         NVARCHAR(256) NOT NULL UNIQUE,
    Description     NVARCHAR(MAX) NULL,
    SourceType      NVARCHAR(64) NOT NULL,
    SourceConfig    NVARCHAR(MAX) NULL,
    DestinationType NVARCHAR(64) NOT NULL,
    DestinationConfig NVARCHAR(MAX) NULL,
    TransformScript NVARCHAR(MAX) NULL,
    Schedule        NVARCHAR(64) NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    CreatedBy       NVARCHAR(256) NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_ETL_SourceType CHECK (SourceType IN (
        'SQL_Query','REST_API','CSV_File','JSON_File','Database_Table','Custom_Script'
    )),
    CONSTRAINT CK_ETL_DestType CHECK (DestinationType IN (
        'SQL_Table','REST_API','CSV_File','JSON_File','Database_Table','Custom_Script'
    ))
);
GO

CREATE TABLE etl.JobHistory (
    RunId           UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    JobId           UNIQUEIDENTIFIER NOT NULL REFERENCES etl.Jobs(JobId),
    StartedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CompletedAt     DATETIME2 NULL,
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Running',
    RowsExtracted   INT NULL,
    RowsTransformed INT NULL,
    RowsLoaded      INT NULL,
    ErrorMessage    NVARCHAR(MAX) NULL,
    DurationMs      INT NULL,
    CONSTRAINT CK_ETL_RunStatus CHECK (Status IN ('Running','Completed','Failed','Cancelled'))
);
GO
CREATE INDEX IX_ETLHistory_Job ON etl.JobHistory (JobId, StartedAt DESC);
GO

PRINT 'DBAOps Sovereign™ — v4 fleet detail & technology detection schema applied.';
GO
