/*
 * DBAOps Sovereign™ — Schema v11: Operations Excellence
 *
 *  1. Ticketing / ITSM Integration
 *  2. On-Call Schedule & Escalation
 *  3. Vendor Directory
 *  4. Web Server / Application Server Inventory
 *  5. Deprecated Features & EOL Detection
 *  6. Fleet & Audit Chart Snapshots
 */

USE SovereignRepository;
GO

-- ===== 1. Ticketing / ITSM Integration =====

IF OBJECT_ID('ops.TicketingConfig', 'U') IS NULL
BEGIN
  CREATE TABLE ops.TicketingConfig (
    ConfigId        INT IDENTITY(1,1) PRIMARY KEY,
    Provider        NVARCHAR(32) NOT NULL, -- ServiceNow, Jira, AzureDevOps, Generic
    BaseUrl         NVARCHAR(512) NOT NULL,
    ApiKeyEncrypted NVARCHAR(MAX) NULL,
    ProjectKey      NVARCHAR(64) NULL,
    DefaultPriority NVARCHAR(16) NOT NULL DEFAULT 'Medium',
    DefaultAssignee NVARCHAR(256) NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('ops.Ticket', 'U') IS NULL
BEGIN
  CREATE TABLE ops.Ticket (
    TicketId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ExternalId      NVARCHAR(64) NULL,
    ExternalUrl     NVARCHAR(512) NULL,
    Provider        NVARCHAR(32) NOT NULL,
    Title           NVARCHAR(512) NOT NULL,
    Description     NVARCHAR(MAX) NULL,
    Priority        NVARCHAR(16) NOT NULL DEFAULT 'Medium', -- Critical, High, Medium, Low
    Status          NVARCHAR(32) NOT NULL DEFAULT 'Open', -- Open, InProgress, Blocked, Resolved, Closed
    Category        NVARCHAR(64) NULL, -- Performance, Security, Compliance, Backup, HADR, Patching, Access, General
    ServerId        UNIQUEIDENTIFIER NULL,
    AssignedTo      NVARCHAR(256) NULL,
    ReportedBy      NVARCHAR(256) NOT NULL,
    SourceAlertId   UNIQUEIDENTIFIER NULL,
    SourceExceptionId UNIQUEIDENTIFIER NULL,
    EscalationLevel INT NOT NULL DEFAULT 0,
    SlaTargetHours  INT NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ResolvedAt      DATETIME2 NULL,
    ClosedAt        DATETIME2 NULL,
    ResolutionNotes NVARCHAR(MAX) NULL
  );
  CREATE NONCLUSTERED INDEX IX_Ticket_Status ON ops.Ticket (Status, Priority, CreatedAt DESC);
END;
GO

IF OBJECT_ID('ops.TicketComment', 'U') IS NULL
BEGIN
  CREATE TABLE ops.TicketComment (
    CommentId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    TicketId        UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES ops.Ticket(TicketId),
    Author          NVARCHAR(256) NOT NULL,
    CommentText     NVARCHAR(MAX) NOT NULL,
    IsInternal      BIT NOT NULL DEFAULT 0,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

-- ===== 2. On-Call Schedule & Escalation =====

IF OBJECT_ID('ops.OnCallSchedule', 'U') IS NULL
BEGIN
  CREATE TABLE ops.OnCallSchedule (
    ScheduleId      INT IDENTITY(1,1) PRIMARY KEY,
    ScheduleName    NVARCHAR(128) NOT NULL,
    TeamName        NVARCHAR(128) NOT NULL,
    RotationType    NVARCHAR(16) NOT NULL DEFAULT 'Weekly', -- Daily, Weekly, BiWeekly, Monthly
    IsActive        BIT NOT NULL DEFAULT 1
  );
END;
GO

IF OBJECT_ID('ops.OnCallRotation', 'U') IS NULL
BEGIN
  CREATE TABLE ops.OnCallRotation (
    RotationId      INT IDENTITY(1,1) PRIMARY KEY,
    ScheduleId      INT NOT NULL FOREIGN KEY REFERENCES ops.OnCallSchedule(ScheduleId),
    DBAName         NVARCHAR(256) NOT NULL,
    DBAEmail        NVARCHAR(256) NOT NULL,
    DBAPhone        NVARCHAR(32) NULL,
    StartDate       DATETIME2 NOT NULL,
    EndDate         DATETIME2 NOT NULL,
    IsPrimary       BIT NOT NULL DEFAULT 1,
    IsBackup        BIT NOT NULL DEFAULT 0
  );
  CREATE NONCLUSTERED INDEX IX_OnCall_Dates ON ops.OnCallRotation (StartDate, EndDate);
END;
GO

IF OBJECT_ID('ops.EscalationPath', 'U') IS NULL
BEGIN
  CREATE TABLE ops.EscalationPath (
    PathId          INT IDENTITY(1,1) PRIMARY KEY,
    PathName        NVARCHAR(128) NOT NULL,
    Category        NVARCHAR(64) NOT NULL, -- Performance, Security, HADR, Backup, General
    Level1Contact   NVARCHAR(256) NOT NULL,
    Level1Method    NVARCHAR(32) NOT NULL DEFAULT 'Email', -- Email, Phone, SMS, Teams, PagerDuty, Slack
    Level1WaitMin   INT NOT NULL DEFAULT 15,
    Level2Contact   NVARCHAR(256) NULL,
    Level2Method    NVARCHAR(32) NULL,
    Level2WaitMin   INT NULL DEFAULT 30,
    Level3Contact   NVARCHAR(256) NULL,
    Level3Method    NVARCHAR(32) NULL,
    Level3WaitMin   INT NULL DEFAULT 60,
    Level4Contact   NVARCHAR(256) NULL,
    Level4Method    NVARCHAR(32) NULL,
    IsActive        BIT NOT NULL DEFAULT 1
  );
END;
GO

-- ===== 3. Vendor Directory =====

IF OBJECT_ID('inv.Vendor', 'U') IS NULL
BEGIN
  CREATE TABLE inv.Vendor (
    VendorId        INT IDENTITY(1,1) PRIMARY KEY,
    VendorName      NVARCHAR(256) NOT NULL,
    VendorType      NVARCHAR(32) NOT NULL, -- Software, Hardware, Cloud, Service, Consulting
    ContactName     NVARCHAR(256) NULL,
    ContactEmail    NVARCHAR(256) NULL,
    ContactPhone    NVARCHAR(32) NULL,
    SupportUrl      NVARCHAR(512) NULL,
    AccountNumber   NVARCHAR(64) NULL,
    ContractExpiry  DATETIME2 NULL,
    SlaResponseHours INT NULL,
    Notes           NVARCHAR(MAX) NULL,
    IsActive        BIT NOT NULL DEFAULT 1
  );
END;
GO

IF OBJECT_ID('inv.VendorProductMap', 'U') IS NULL
BEGIN
  CREATE TABLE inv.VendorProductMap (
    MapId           INT IDENTITY(1,1) PRIMARY KEY,
    VendorId        INT NOT NULL FOREIGN KEY REFERENCES inv.Vendor(VendorId),
    ServerId        UNIQUEIDENTIFIER NULL,
    ProductName     NVARCHAR(256) NOT NULL,
    ProductVersion  NVARCHAR(64) NULL,
    LicenseType     NVARCHAR(32) NULL,
    AnnualCost      DECIMAL(18,2) NULL
  );
END;
GO

-- ===== 4. Web Server / Application Server Inventory =====

IF OBJECT_ID('inv.WebServer', 'U') IS NULL
BEGIN
  CREATE TABLE inv.WebServer (
    WebServerId     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    Hostname        NVARCHAR(256) NOT NULL,
    ServerType      NVARCHAR(32) NOT NULL, -- IIS, Nginx, Apache, Tomcat, Kestrel, Node, Other
    OperatingSystem NVARCHAR(128) NULL,
    IPAddress       NVARCHAR(64) NULL,
    Environment     NVARCHAR(16) NOT NULL DEFAULT 'Production', -- Production, Staging, Development, QA, DR
    Location        NVARCHAR(128) NULL,
    OwnerId         NVARCHAR(256) NULL,
    Status          NVARCHAR(16) NOT NULL DEFAULT 'Active', -- Active, Maintenance, Decommissioned
    DetectedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('inv.WebServerDeployment', 'U') IS NULL
BEGIN
  CREATE TABLE inv.WebServerDeployment (
    DeploymentId    UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    WebServerId     UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES inv.WebServer(WebServerId),
    ApplicationName NVARCHAR(256) NOT NULL,
    AppPool         NVARCHAR(128) NULL,
    VirtualDirectory NVARCHAR(256) NULL,
    BindingUrl      NVARCHAR(512) NULL,
    FrameworkVersion NVARCHAR(64) NULL,
    DatabaseServerId UNIQUEIDENTIFIER NULL,
    DatabaseName    NVARCHAR(256) NULL,
    ConnectionStringRef NVARCHAR(128) NULL,
    Status          NVARCHAR(16) NOT NULL DEFAULT 'Running', -- Running, Stopped, Error, Deploying
    LastDeployedAt  DATETIME2 NULL,
    LastDeployedBy  NVARCHAR(256) NULL
  );
END;
GO

-- ===== 5. Deprecated Features & EOL Detection =====

IF OBJECT_ID('inv.DeprecatedFeature', 'U') IS NULL
BEGIN
  CREATE TABLE inv.DeprecatedFeature (
    FeatureId       INT IDENTITY(1,1) PRIMARY KEY,
    Engine          NVARCHAR(64) NOT NULL,
    FeatureName     NVARCHAR(256) NOT NULL,
    DeprecatedInVersion NVARCHAR(32) NOT NULL,
    RemovedInVersion NVARCHAR(32) NULL,
    ReplacementFeature NVARCHAR(256) NULL,
    RiskLevel       NVARCHAR(16) NOT NULL DEFAULT 'Medium',
    RemediationSteps NVARCHAR(MAX) NULL,
    MicrosoftDocUrl NVARCHAR(512) NULL,
    IsActive        BIT NOT NULL DEFAULT 1
  );
END;
GO

IF OBJECT_ID('inv.DeprecatedFeatureDetection', 'U') IS NULL
BEGIN
  CREATE TABLE inv.DeprecatedFeatureDetection (
    DetectionId     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    FeatureId       INT NOT NULL FOREIGN KEY REFERENCES inv.DeprecatedFeature(FeatureId),
    DetectedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CurrentVersion  NVARCHAR(32) NULL,
    UsageCount      INT NULL,
    IsRemediated    BIT NOT NULL DEFAULT 0,
    RemediatedAt    DATETIME2 NULL,
    RemediatedBy    NVARCHAR(256) NULL,
    Notes           NVARCHAR(MAX) NULL
  );
END;
GO

IF OBJECT_ID('inv.EndOfLifeProduct', 'U') IS NULL
BEGIN
  CREATE TABLE inv.EndOfLifeProduct (
    EOLId           INT IDENTITY(1,1) PRIMARY KEY,
    ProductName     NVARCHAR(256) NOT NULL,
    ProductVersion  NVARCHAR(64) NOT NULL,
    MainstreamEnd   DATETIME2 NULL,
    ExtendedEnd     DATETIME2 NULL,
    SecurityEnd     DATETIME2 NULL,
    RecommendedUpgrade NVARCHAR(256) NULL,
    Notes           NVARCHAR(MAX) NULL
  );
END;
GO

-- ===== Seed: Deprecated SQL Server Features =====

IF NOT EXISTS (SELECT 1 FROM inv.DeprecatedFeature WHERE FeatureName = 'Database Mirroring')
BEGIN
  INSERT INTO inv.DeprecatedFeature (Engine, FeatureName, DeprecatedInVersion, RemovedInVersion, ReplacementFeature, RiskLevel, RemediationSteps, MicrosoftDocUrl) VALUES
    ('SQL Server', 'Database Mirroring', '2012', NULL, 'Always On Availability Groups', 'High', 'Migrate mirrored databases to AG replicas. Test failover before decommissioning mirror endpoints.', 'https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server'),
    ('SQL Server', 'SQL Server Compact (SQLCE)', '2012', '2022', 'SQLite or SQL Server Express LocalDB', 'High', 'Migrate embedded databases to SQLite or LocalDB. Update connection strings.', NULL),
    ('SQL Server', 'ntext / text / image data types', '2005', NULL, 'nvarchar(max) / varchar(max) / varbinary(max)', 'Medium', 'ALTER COLUMN to max types. Test application compatibility.', NULL),
    ('SQL Server', 'RAISERROR with string format', '2012', NULL, 'THROW statement', 'Low', 'Replace RAISERROR @msg with THROW. Update error handling patterns.', NULL),
    ('SQL Server', 'sp_addlogin / sp_droplogin', '2005', NULL, 'CREATE LOGIN / DROP LOGIN', 'Medium', 'Replace all calls to sp_addlogin with CREATE LOGIN DDL.', NULL),
    ('SQL Server', 'SET ROWCOUNT for DML', '2008', NULL, 'TOP clause in UPDATE/DELETE', 'Medium', 'Replace SET ROWCOUNT with TOP(n) in DML statements.', NULL),
    ('SQL Server', 'Remote servers (sp_addserver)', '2008', NULL, 'Linked servers', 'High', 'Migrate from remote servers to linked server configuration.', NULL),
    ('SQL Server', 'trace flags for query hints', '2016', NULL, 'USE HINT query hint', 'Low', 'Replace trace flag query hints with USE HINT syntax.', NULL),
    ('SQL Server', 'SQL Mail (xp_sendmail)', '2008R2', '2012', 'Database Mail (sp_send_dbmail)', 'Critical', 'Migrate all email jobs to Database Mail. Remove SQL Mail references.', NULL),
    ('SQL Server', 'XP_API extended procs', '2005', NULL, 'CLR or external application', 'High', 'Replace xp_ calls with CLR assemblies or external service calls.', NULL),
    ('SQL Server', 'Legacy Cardinality Estimator', '2014', NULL, 'New CE (compatibility level 120+)', 'Medium', 'Increase database compatibility level. Test query plan regressions.', NULL),
    ('SQL Server', 'RC4 encryption algorithm', '2016', NULL, 'AES encryption', 'Critical', 'Migrate all encrypted endpoints and connections from RC4 to AES-256.', NULL),
    ('SQL Server', 'Contained database authentication', '2022', NULL, 'Microsoft Entra ID authentication', 'Low', 'Evaluate migration to Entra ID for contained database users.', NULL),
    ('SQL Server', 'sysadmin for BULK INSERT', '2019', NULL, 'ADMINISTER BULK OPERATIONS permission', 'Medium', 'Grant specific permission instead of sysadmin for bulk operations.', NULL),
    ('SQL Server', 'MD2/MD4/MD5/SHA/SHA1 algorithms', '2016', NULL, 'SHA2_256 / SHA2_512', 'Critical', 'Replace weak hash algorithms with SHA2 variants. Update certificate chains.', NULL);
END;
GO

-- ===== Seed: End-of-Life Products =====

IF NOT EXISTS (SELECT 1 FROM inv.EndOfLifeProduct WHERE ProductName = 'SQL Server 2012')
BEGIN
  INSERT INTO inv.EndOfLifeProduct (ProductName, ProductVersion, MainstreamEnd, ExtendedEnd, SecurityEnd, RecommendedUpgrade) VALUES
    ('SQL Server 2012', '11.0', '2017-07-11', '2022-07-12', '2025-07-08', 'SQL Server 2022'),
    ('SQL Server 2014', '12.0', '2019-07-09', '2024-07-09', '2024-07-09', 'SQL Server 2022'),
    ('SQL Server 2016', '13.0', '2021-07-13', '2026-07-14', NULL, 'SQL Server 2022'),
    ('SQL Server 2017', '14.0', '2022-10-11', '2027-10-12', NULL, 'SQL Server 2022'),
    ('SQL Server 2019', '15.0', '2025-02-28', '2030-01-08', NULL, 'SQL Server 2022'),
    ('SQL Server 2022', '16.0', '2028-01-11', '2033-01-11', NULL, 'Current'),
    ('Windows Server 2012 R2', '6.3', '2018-10-09', '2023-10-10', '2026-10-13', 'Windows Server 2022'),
    ('Windows Server 2016', '10.0.14393', '2022-01-11', '2027-01-12', NULL, 'Windows Server 2022'),
    ('Oracle 12c', '12.2', '2018-03-31', '2022-03-31', NULL, 'Oracle 19c or 23ai'),
    ('Oracle 19c', '19.0', '2024-04-30', '2027-04-30', NULL, 'Oracle 23ai'),
    ('PostgreSQL 12', '12', '2024-11-14', '2024-11-14', NULL, 'PostgreSQL 16'),
    ('PostgreSQL 13', '13', '2025-11-13', '2025-11-13', NULL, 'PostgreSQL 16'),
    ('PostgreSQL 14', '14', '2026-11-12', '2026-11-12', NULL, 'PostgreSQL 17');
END;
GO

-- ===== Seed: On-Call and Escalation =====

IF NOT EXISTS (SELECT 1 FROM ops.OnCallSchedule WHERE ScheduleName = 'Primary DBA On-Call')
BEGIN
  INSERT INTO ops.OnCallSchedule (ScheduleName, TeamName, RotationType) VALUES
    ('Primary DBA On-Call', 'Database Operations', 'Weekly'),
    ('Backup DBA On-Call', 'Database Operations', 'Weekly'),
    ('Security Escalation', 'Security Team', 'Monthly');

  INSERT INTO ops.EscalationPath (PathName, Category, Level1Contact, Level1Method, Level1WaitMin, Level2Contact, Level2Method, Level2WaitMin, Level3Contact, Level3Method, Level3WaitMin) VALUES
    ('Performance Critical', 'Performance', 'on-call-dba@company.com', 'PagerDuty', 15, 'dba-lead@company.com', 'Phone', 30, 'vp-engineering@company.com', 'Phone', 60),
    ('Security Incident', 'Security', 'security-oncall@company.com', 'PagerDuty', 5, 'ciso@company.com', 'Phone', 15, 'ceo@company.com', 'Phone', 30),
    ('HADR Failover', 'HADR', 'on-call-dba@company.com', 'PagerDuty', 10, 'dba-lead@company.com', 'Phone', 20, 'director-it@company.com', 'Phone', 45),
    ('Backup Failure', 'Backup', 'on-call-dba@company.com', 'Email', 30, 'dba-lead@company.com', 'Email', 60, NULL, NULL, NULL),
    ('General Operations', 'General', 'on-call-dba@company.com', 'Email', 60, 'dba-lead@company.com', 'Email', 120, NULL, NULL, NULL);
END;
GO

PRINT 'Schema v11 (Operations Excellence) applied successfully.';
GO
