/*
 * DBAOps Sovereign™ — Schema v9: Platinum Magic Completion
 *
 * Closes every gap in the 7 Platinum capabilities:
 * 1. Data Sovereignty  → ContainmentCertificate
 * 2. Crypto Chain      → MerkleProof, EvidencePackage
 * 3. Classification    → PatternLibrary, Taxonomy, DataLineage
 * 4. FinOps            → WhatIfScenario, ROIProof
 * 5. Self-Healing      → RunbookDefinition, RunbookStep, HealingPattern
 * 6. Unified Estate    → UniversalMetric, TopologyLink, HealthScoreHistory
 * 7. Knowledge         → KnowledgeArticle, InteractiveTutorial, Certification
 */

USE SovereignRepository;
GO

-- ===== 1. Data Sovereignty — Containment Certificates =====

IF OBJECT_ID('sovereignty.ContainmentCertificate', 'U') IS NULL
BEGIN
  CREATE TABLE sovereignty.ContainmentCertificate (
    CertId          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    TenantId        UNIQUEIDENTIFIER NULL,
    CertType        NVARCHAR(32)  NOT NULL, -- ITAR, CJIS, FedRAMP, IL4, IL5, IL6, HIPAA, Custom
    GeneratedAt     DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
    ValidFrom       DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME(),
    ValidTo         DATETIME2     NOT NULL,
    GeneratedBy     NVARCHAR(256) NOT NULL,
    DataFlowsSurveyed INT NOT NULL DEFAULT 0,
    ExternalEndpointsFound INT NOT NULL DEFAULT 0,
    AllDataContained BIT NOT NULL DEFAULT 1,
    Violations       NVARCHAR(MAX) NULL, -- JSON array of violations
    CertHash         CHAR(64) NOT NULL,
    CertSignature    NVARCHAR(MAX) NULL,
    CertBody         NVARCHAR(MAX) NOT NULL, -- full certificate text
    AuditorName      NVARCHAR(256) NULL,
    AuditorOrg       NVARCHAR(256) NULL,
    Status           NVARCHAR(16) NOT NULL DEFAULT 'Active' -- Active, Revoked, Expired
  );
END;
GO

-- ===== 2. Crypto Chain — Merkle Proof + Evidence Packages =====

IF OBJECT_ID('sec.MerkleProof', 'U') IS NULL
BEGIN
  CREATE TABLE sec.MerkleProof (
    ProofId         BIGINT IDENTITY(1,1) PRIMARY KEY,
    ChainStartId    BIGINT NOT NULL,
    ChainEndId      BIGINT NOT NULL,
    MerkleRoot      CHAR(64) NOT NULL,
    TreeDepth       INT NOT NULL,
    LeafCount       INT NOT NULL,
    ProofJson       NVARCHAR(MAX) NOT NULL, -- full Merkle tree structure
    ComputedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('sec.EvidencePackage', 'U') IS NULL
BEGIN
  CREATE TABLE sec.EvidencePackage (
    PackageId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    PackageType     NVARCHAR(32) NOT NULL, -- CourtEvidence, AuditReport, ComplianceProof, ForensicExport
    Title           NVARCHAR(256) NOT NULL,
    GeneratedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    GeneratedBy     NVARCHAR(256) NOT NULL,
    ChainStartId    BIGINT NULL,
    ChainEndId      BIGINT NULL,
    MerkleRoot      CHAR(64) NULL,
    EventCount      INT NOT NULL DEFAULT 0,
    IncludesTimestamps BIT NOT NULL DEFAULT 1,
    IncludesSignatures BIT NOT NULL DEFAULT 1,
    PackageHash     CHAR(64) NOT NULL,
    PackageFormat   NVARCHAR(16) NOT NULL DEFAULT 'JSON', -- JSON, PDF, PKCS7
    PackageBody     NVARCHAR(MAX) NOT NULL,
    CustodyChainValid BIT NOT NULL DEFAULT 1,
    Status          NVARCHAR(16) NOT NULL DEFAULT 'Active'
  );
END;
GO

-- ===== 3. Classification — Pattern Library + Taxonomy + Lineage =====

IF OBJECT_ID('classification.PatternLibrary', 'U') IS NULL
BEGIN
  CREATE TABLE classification.PatternLibrary (
    PatternId       INT IDENTITY(1,1) PRIMARY KEY,
    PatternName     NVARCHAR(128) NOT NULL,
    PatternCode     NVARCHAR(64) NOT NULL UNIQUE,
    RegexPattern    NVARCHAR(MAX) NOT NULL,
    ColumnHeuristics NVARCHAR(MAX) NULL, -- JSON array of column name patterns
    ClassificationType NVARCHAR(32) NOT NULL,
    SensitivityLevel NVARCHAR(16) NOT NULL DEFAULT 'High',
    RegulatoryFrameworks NVARCHAR(256) NULL,
    Description     NVARCHAR(512) NULL,
    IsBuiltIn       BIT NOT NULL DEFAULT 1,
    IsEnabled       BIT NOT NULL DEFAULT 1
  );
END;
GO

IF OBJECT_ID('classification.Taxonomy', 'U') IS NULL
BEGIN
  CREATE TABLE classification.Taxonomy (
    TaxonomyId      INT IDENTITY(1,1) PRIMARY KEY,
    LevelCode       NVARCHAR(32) NOT NULL UNIQUE,
    LevelName       NVARCHAR(64) NOT NULL,
    LevelOrder      INT NOT NULL,
    Description     NVARCHAR(512) NULL,
    Color           NVARCHAR(16) NULL,
    HandlingRules   NVARCHAR(MAX) NULL, -- JSON
    RetentionDays   INT NULL,
    EncryptionRequired BIT NOT NULL DEFAULT 0,
    MaskingRequired BIT NOT NULL DEFAULT 0
  );
END;
GO

IF OBJECT_ID('classification.DataLineage', 'U') IS NULL
BEGIN
  CREATE TABLE classification.DataLineage (
    LineageId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    SourceServerId  UNIQUEIDENTIFIER NOT NULL,
    SourceDatabase  NVARCHAR(256) NOT NULL,
    SourceSchema    NVARCHAR(128) NOT NULL DEFAULT 'dbo',
    SourceTable     NVARCHAR(256) NOT NULL,
    SourceColumn    NVARCHAR(256) NULL,
    TransformType   NVARCHAR(64) NULL, -- ETL, Replication, Backup, Export, Copy, API
    TargetServerId  UNIQUEIDENTIFIER NULL,
    TargetDatabase  NVARCHAR(256) NULL,
    TargetSchema    NVARCHAR(128) NULL,
    TargetTable     NVARCHAR(256) NULL,
    TargetColumn    NVARCHAR(256) NULL,
    TargetExternal  NVARCHAR(256) NULL,
    ContainsPII     BIT NOT NULL DEFAULT 0,
    ContainsPHI     BIT NOT NULL DEFAULT 0,
    ContainsPCI     BIT NOT NULL DEFAULT 0,
    IsApproved      BIT NOT NULL DEFAULT 0,
    DetectedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    LastSeenAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
  CREATE NONCLUSTERED INDEX IX_Lineage_Source ON classification.DataLineage (SourceServerId, SourceDatabase, SourceTable);
END;
GO

-- ===== 4. FinOps — What-If Scenarios + ROI Proof =====

IF OBJECT_ID('finops.WhatIfScenario', 'U') IS NULL
BEGIN
  CREATE TABLE finops.WhatIfScenario (
    ScenarioId      UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ScenarioName    NVARCHAR(256) NOT NULL,
    ScenarioType    NVARCHAR(32) NOT NULL, -- CloudMigration, Consolidation, EditionUpgrade, RIOptimization
    CreatedBy       NVARCHAR(256) NOT NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    InputServers    NVARCHAR(MAX) NOT NULL, -- JSON array of serverIds
    InputParams     NVARCHAR(MAX) NOT NULL, -- JSON
    CurrentMonthlyCost DECIMAL(18,2) NOT NULL,
    ProjectedMonthlyCost DECIMAL(18,2) NOT NULL,
    MonthlySavings  DECIMAL(18,2) NOT NULL,
    AnnualSavings   DECIMAL(18,2) NOT NULL,
    MigrationCost   DECIMAL(18,2) NULL,
    BreakEvenMonths INT NULL,
    RiskLevel       NVARCHAR(16) NOT NULL DEFAULT 'Medium',
    Feasibility     NVARCHAR(16) NOT NULL DEFAULT 'Medium', -- Easy, Medium, Hard
    Recommendations NVARCHAR(MAX) NULL,
    Status          NVARCHAR(16) NOT NULL DEFAULT 'Draft' -- Draft, Approved, Implementing, Completed
  );
END;
GO

IF OBJECT_ID('finops.ROIProof', 'U') IS NULL
BEGIN
  CREATE TABLE finops.ROIProof (
    ProofId         UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    PeriodStart     DATETIME2 NOT NULL,
    PeriodEnd       DATETIME2 NOT NULL,
    GeneratedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    TotalTimeSavedHours DECIMAL(10,2) NOT NULL DEFAULT 0,
    TotalDowntimePreventedHours DECIMAL(10,2) NOT NULL DEFAULT 0,
    TotalCostSavingsUSD DECIMAL(18,2) NOT NULL DEFAULT 0,
    TotalLicenseSavingsUSD DECIMAL(18,2) NOT NULL DEFAULT 0,
    TotalBreachesAverted INT NOT NULL DEFAULT 0,
    BreachCostAvoidedUSD DECIMAL(18,2) NOT NULL DEFAULT 0,
    SelfHealingExecutions INT NOT NULL DEFAULT 0,
    ComplianceViolationsFixed INT NOT NULL DEFAULT 0,
    OnboardingTimeSavedDays INT NOT NULL DEFAULT 0,
    TotalROIUSD     DECIMAL(18,2) NOT NULL DEFAULT 0,
    ReportBody      NVARCHAR(MAX) NULL
  );
END;
GO

-- ===== 5. Self-Healing — Runbooks + Learning Engine =====

IF OBJECT_ID('ops.RunbookDefinition', 'U') IS NULL
BEGIN
  CREATE TABLE ops.RunbookDefinition (
    RunbookId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RunbookName     NVARCHAR(256) NOT NULL,
    RunbookCode     NVARCHAR(64) NOT NULL UNIQUE,
    Description     NVARCHAR(MAX) NULL,
    TriggerCondition NVARCHAR(MAX) NOT NULL, -- JSON
    Category        NVARCHAR(64) NOT NULL,
    IsEnabled       BIT NOT NULL DEFAULT 1,
    RequiresApproval BIT NOT NULL DEFAULT 1,
    MaxBlastRadiusPercent INT NOT NULL DEFAULT 10,
    ProgressiveTrustLevel NVARCHAR(16) NOT NULL DEFAULT 'Manual', -- Manual, ApprovalRequired, Supervised, FullyAuto
    TrustScore      INT NOT NULL DEFAULT 0, -- 0-100, increases with successful runs
    SuccessCount    INT NOT NULL DEFAULT 0,
    FailureCount    INT NOT NULL DEFAULT 0,
    CreatedBy       NVARCHAR(256) NOT NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('ops.RunbookStep', 'U') IS NULL
BEGIN
  CREATE TABLE ops.RunbookStep (
    StepId          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    RunbookId       UNIQUEIDENTIFIER NOT NULL,
    StepOrder       INT NOT NULL,
    StepType        NVARCHAR(32) NOT NULL, -- Condition, SQLAction, InfraAction, Notification, Wait, Gate
    StepName        NVARCHAR(256) NOT NULL,
    Configuration   NVARCHAR(MAX) NOT NULL, -- JSON
    OnSuccess       NVARCHAR(32) NOT NULL DEFAULT 'Continue', -- Continue, Skip, Goto
    OnFailure       NVARCHAR(32) NOT NULL DEFAULT 'Abort', -- Abort, Rollback, Continue, Goto
    GotoStepOrder   INT NULL,
    TimeoutSec      INT NOT NULL DEFAULT 300,
    FOREIGN KEY (RunbookId) REFERENCES ops.RunbookDefinition(RunbookId)
  );
END;
GO

IF OBJECT_ID('ops.HealingPattern', 'U') IS NULL
BEGIN
  CREATE TABLE ops.HealingPattern (
    PatternId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    AlertType       NVARCHAR(128) NOT NULL,
    MetricName      NVARCHAR(128) NOT NULL,
    ManualFixCount  INT NOT NULL DEFAULT 0,
    ObservedActions NVARCHAR(MAX) NULL, -- JSON array of observed manual resolutions
    SuggestedRunbook NVARCHAR(MAX) NULL, -- auto-generated runbook suggestion
    Confidence      INT NOT NULL DEFAULT 0, -- 0-100
    AutomationSuggested BIT NOT NULL DEFAULT 0,
    AcceptedByUser  BIT NULL,
    DetectedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    LastOccurrence  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('ops.BlastRadiusConfig', 'U') IS NULL
BEGIN
  CREATE TABLE ops.BlastRadiusConfig (
    ConfigId        INT IDENTITY(1,1) PRIMARY KEY,
    MaxFleetPercent INT NOT NULL DEFAULT 10,
    MaxConcurrentActions INT NOT NULL DEFAULT 3,
    RequireApprovalAbovePercent INT NOT NULL DEFAULT 5,
    RollbackOnFailurePercent INT NOT NULL DEFAULT 50,
    MaintenanceWindowRequired BIT NOT NULL DEFAULT 0,
    NotifyOnExecution BIT NOT NULL DEFAULT 1,
    NotifyChannels  NVARCHAR(MAX) NULL -- JSON: email, Teams, Slack, etc.
  );
END;
GO

-- ===== 6. Unified Estate — Universal Metrics + Topology =====

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'fleet')
  EXEC('CREATE SCHEMA fleet');
GO

IF OBJECT_ID('fleet.UniversalMetric', 'U') IS NULL
BEGIN
  CREATE TABLE fleet.UniversalMetric (
    MetricId        BIGINT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    Engine          NVARCHAR(64) NOT NULL,
    CollectedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CpuNormalized   DECIMAL(5,2) NULL, -- 0-100
    MemoryNormalized DECIMAL(5,2) NULL,
    IONormalized    DECIMAL(5,2) NULL,
    WaitNormalized  DECIMAL(5,2) NULL,
    ConnectionsNormalized DECIMAL(5,2) NULL,
    ThroughputNormalized DECIMAL(10,2) NULL,
    PerformanceScore INT NOT NULL DEFAULT 0,
    AvailabilityScore INT NOT NULL DEFAULT 0,
    SecurityScore   INT NOT NULL DEFAULT 0,
    ComplianceScore INT NOT NULL DEFAULT 0,
    CostEfficiencyScore INT NOT NULL DEFAULT 0,
    HealthScore     INT NOT NULL DEFAULT 0, -- weighted composite
    FOREIGN KEY (ServerId) REFERENCES ctl.Servers(ServerId)
  );
  CREATE NONCLUSTERED INDEX IX_UnivMetric_Server ON fleet.UniversalMetric (ServerId, CollectedAt DESC);
END;
GO

IF OBJECT_ID('fleet.TopologyLink', 'U') IS NULL
BEGIN
  CREATE TABLE fleet.TopologyLink (
    LinkId          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    SourceServerId  UNIQUEIDENTIFIER NOT NULL,
    SourceType      NVARCHAR(32) NOT NULL, -- Server, Database, Application, Service
    SourceName      NVARCHAR(256) NOT NULL,
    TargetServerId  UNIQUEIDENTIFIER NULL,
    TargetType      NVARCHAR(32) NOT NULL,
    TargetName      NVARCHAR(256) NOT NULL,
    LinkType        NVARCHAR(32) NOT NULL, -- Replication, AG, LogShipping, ETL, AppConnection, Backup, Dependency
    LinkStatus      NVARCHAR(16) NOT NULL DEFAULT 'Active',
    Metadata        NVARCHAR(MAX) NULL, -- JSON
    DetectedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

IF OBJECT_ID('fleet.HealthScoreHistory', 'U') IS NULL
BEGIN
  CREATE TABLE fleet.HealthScoreHistory (
    HistId          BIGINT IDENTITY(1,1) PRIMARY KEY,
    ServerId        UNIQUEIDENTIFIER NOT NULL,
    Engine          NVARCHAR(64) NOT NULL,
    RecordedAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    PerformanceScore INT NOT NULL,
    AvailabilityScore INT NOT NULL,
    SecurityScore   INT NOT NULL,
    ComplianceScore INT NOT NULL,
    CostEfficiencyScore INT NOT NULL,
    HealthScore     INT NOT NULL,
    Breakdown       NVARCHAR(MAX) NULL -- JSON detail
  );
END;
GO

IF OBJECT_ID('fleet.CrossPlatformInsight', 'U') IS NULL
BEGIN
  CREATE TABLE fleet.CrossPlatformInsight (
    InsightId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    InsightType     NVARCHAR(32) NOT NULL, -- MigrationSuggestion, Underutilized, PatternMatch, Comparison
    SourceServerId  UNIQUEIDENTIFIER NOT NULL,
    TargetPlatform  NVARCHAR(64) NULL,
    Title           NVARCHAR(256) NOT NULL,
    Description     NVARCHAR(MAX) NOT NULL,
    EstimatedImpact NVARCHAR(MAX) NULL, -- JSON
    Confidence      INT NOT NULL DEFAULT 0,
    GeneratedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO

-- ===== 7. Knowledge — Knowledge Articles + Tutorials + Certifications =====

IF OBJECT_ID('learning.KnowledgeArticle', 'U') IS NULL
BEGIN
  CREATE TABLE learning.KnowledgeArticle (
    ArticleId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    Title           NVARCHAR(256) NOT NULL,
    Category        NVARCHAR(64) NOT NULL,
    Tags            NVARCHAR(MAX) NULL, -- JSON array
    ContentHtml     NVARCHAR(MAX) NOT NULL,
    AuthoredBy      NVARCHAR(256) NOT NULL,
    AuthoredAt      DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt       DATETIME2 NULL,
    RelatedAlerts   NVARCHAR(MAX) NULL, -- JSON array of alert types
    RelatedMetrics  NVARCHAR(MAX) NULL, -- JSON array of metric keys
    IsOrgSpecific   BIT NOT NULL DEFAULT 1,
    ViewCount       INT NOT NULL DEFAULT 0,
    HelpfulCount    INT NOT NULL DEFAULT 0,
    Engine          NVARCHAR(64) NOT NULL DEFAULT 'All'
  );
END;
GO

IF OBJECT_ID('learning.InteractiveTutorial', 'U') IS NULL
BEGIN
  CREATE TABLE learning.InteractiveTutorial (
    TutorialId      INT IDENTITY(1,1) PRIMARY KEY,
    TutorialCode    NVARCHAR(64) NOT NULL UNIQUE,
    Title           NVARCHAR(256) NOT NULL,
    Description     NVARCHAR(MAX) NULL,
    Category        NVARCHAR(64) NOT NULL,
    DifficultyLevel INT NOT NULL DEFAULT 1,
    EstimatedMinutes INT NOT NULL DEFAULT 15,
    Steps           NVARCHAR(MAX) NOT NULL, -- JSON array of tutorial steps
    UsesRealData    BIT NOT NULL DEFAULT 0,
    Engine          NVARCHAR(64) NOT NULL DEFAULT 'All',
    PrerequisiteModules NVARCHAR(MAX) NULL, -- JSON
    IsActive        BIT NOT NULL DEFAULT 1,
    CompletionCount INT NOT NULL DEFAULT 0
  );
END;
GO

IF OBJECT_ID('learning.Certification', 'U') IS NULL
BEGIN
  CREATE TABLE learning.Certification (
    CertificationId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    UserId          UNIQUEIDENTIFIER NOT NULL,
    CertificationType NVARCHAR(64) NOT NULL, -- ModuleCompletion, TutorialCompletion, SkillPath, ExpertLevel
    CertificateName NVARCHAR(256) NOT NULL,
    IssuedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    ExpiresAt       DATETIME2 NULL,
    ModuleIds       NVARCHAR(MAX) NULL, -- JSON array
    QuizScoreAvg    DECIMAL(5,2) NULL,
    CertHash        CHAR(64) NOT NULL,
    IsValid         BIT NOT NULL DEFAULT 1
  );
END;
GO

-- ===== Seed: Pattern Library (30+ built-in patterns) =====

IF NOT EXISTS (SELECT 1 FROM classification.PatternLibrary WHERE PatternCode = 'PAT-SSN-US')
BEGIN
  INSERT INTO classification.PatternLibrary (PatternName, PatternCode, RegexPattern, ColumnHeuristics, ClassificationType, SensitivityLevel, RegulatoryFrameworks, Description) VALUES
    ('US Social Security Number', 'PAT-SSN-US', '\d{3}-\d{2}-\d{4}', '["ssn","social_security","socialsecurity","ss_number","ssnumber"]', 'PII', 'Critical', 'GDPR,HIPAA,PCI-DSS,CJIS', 'US SSN in XXX-XX-XXXX format'),
    ('Credit Card (Visa/MC/Amex)', 'PAT-CC-GENERIC', '(?:4\d{3}|5[1-5]\d{2}|3[47]\d{2})[\s-]?\d{4}[\s-]?\d{4}[\s-]?\d{3,4}', '["creditcard","cc_number","cardnumber","card_num","pan"]', 'PCI', 'Critical', 'PCI-DSS', 'Visa, MasterCard, Amex card numbers'),
    ('Email Address', 'PAT-EMAIL', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}', '["email","email_address","emailaddr","e_mail","contact_email"]', 'PII', 'High', 'GDPR,CCPA', 'Standard email address pattern'),
    ('US Phone Number', 'PAT-PHONE-US', '(?:\+1[\s-]?)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}', '["phone","telephone","phone_number","mobile","cell"]', 'PII', 'Medium', 'GDPR,CCPA', 'US phone in various formats'),
    ('Date of Birth', 'PAT-DOB', '(?:19|20)\d{2}[-/](?:0[1-9]|1[0-2])[-/](?:0[1-9]|[12]\d|3[01])', '["dob","date_of_birth","birthdate","birth_date","dateofbirth"]', 'PII', 'High', 'GDPR,HIPAA', 'Date of birth in YYYY-MM-DD'),
    ('Medical Record Number', 'PAT-MRN', 'MRN[\s:#-]?\d{6,10}', '["mrn","medical_record","medicalrecord","patient_id","patientid"]', 'PHI', 'Critical', 'HIPAA', 'Medical record number formats'),
    ('ICD-10 Diagnosis Code', 'PAT-ICD10', '[A-Z]\d{2}(?:\.\d{1,4})?', '["diagnosis","icd","icd10","diagnosiscode","icd_code"]', 'PHI', 'Critical', 'HIPAA', 'ICD-10 diagnosis codes'),
    ('National Drug Code', 'PAT-NDC', '\d{5}-\d{4}-\d{2}', '["ndc","drug_code","nationaldrugcode","medication_code"]', 'PHI', 'High', 'HIPAA', 'National Drug Code format'),
    ('IP Address (IPv4)', 'PAT-IPV4', '\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}', '["ip","ip_address","ipaddress","client_ip","source_ip"]', 'PII', 'Medium', 'GDPR', 'IPv4 address'),
    ('Passport Number', 'PAT-PASSPORT', '[A-Z]{1,2}\d{6,9}', '["passport","passport_number","passportno","travel_doc"]', 'PII', 'Critical', 'GDPR', 'Passport number formats'),
    ('Bank Account (IBAN)', 'PAT-IBAN', '[A-Z]{2}\d{2}[A-Z0-9]{4}\d{7}(?:[A-Z0-9]{0,16})?', '["iban","bank_account","bankaccount","account_number"]', 'Financial', 'Critical', 'GLBA,GDPR', 'International Bank Account Number'),
    ('US Driver License', 'PAT-DL-US', '[A-Z]\d{7,12}', '["drivers_license","dl_number","license_number","driverlicense"]', 'PII', 'High', 'GDPR,CJIS', 'US driver license number patterns'),
    ('Password/Secret', 'PAT-SECRET', '(?:password|passwd|secret|api_key|apikey|token|private_key)', '["password","passwd","secret","api_key","apikey","token","credential","private_key"]', 'Credential', 'Critical', 'NIST,CIS', 'Stored passwords or secrets'),
    ('Bitcoin Address', 'PAT-BTC', '[13][a-km-zA-HJ-NP-Z1-9]{25,34}', '["bitcoin","btc","wallet","crypto_address"]', 'Financial', 'High', 'BSA/AML', 'Bitcoin wallet address'),
    ('US EIN/Tax ID', 'PAT-EIN', '\d{2}-\d{7}', '["ein","tax_id","taxid","employer_id","fein"]', 'Financial', 'High', 'SOX,GLBA', 'Employer Identification Number'),
    ('MAC Address', 'PAT-MAC', '(?:[0-9A-Fa-f]{2}[:-]){5}[0-9A-Fa-f]{2}', '["mac","mac_address","macaddress","hardware_addr"]', 'PII', 'Low', 'GDPR', 'Network MAC address'),
    ('GPS/Geolocation', 'PAT-GEO', '-?\d{1,3}\.\d{4,8}\s*,\s*-?\d{1,3}\.\d{4,8}', '["latitude","longitude","lat","lng","geo","location","coordinates"]', 'PII', 'High', 'GDPR', 'GPS coordinates'),
    ('Vehicle Identification Number', 'PAT-VIN', '[A-HJ-NPR-Z0-9]{17}', '["vin","vehicle_id","vehicleidentification"]', 'PII', 'Medium', 'GDPR', 'Vehicle VIN'),
    ('Biometric Template', 'PAT-BIOMETRIC', '.*', '["fingerprint","biometric","face_template","iris","retina","voice_print"]', 'PII', 'Critical', 'GDPR,BIPA,HIPAA', 'Biometric data indicators (column name only)'),
    ('Genetic/DNA Data', 'PAT-GENETIC', '.*', '["dna","genetic","genome","allele","genotype","snp"]', 'PHI', 'Critical', 'GINA,HIPAA,GDPR', 'Genetic data indicators (column name only)');
END;
GO

-- ===== Seed: Taxonomy Levels =====

IF NOT EXISTS (SELECT 1 FROM classification.Taxonomy WHERE LevelCode = 'PUBLIC')
BEGIN
  INSERT INTO classification.Taxonomy (LevelCode, LevelName, LevelOrder, Description, Color, EncryptionRequired, MaskingRequired) VALUES
    ('PUBLIC',       'Public',       1, 'Freely available information',                        '#22c55e', 0, 0),
    ('INTERNAL',     'Internal',     2, 'Internal use only, not for public distribution',      '#3b82f6', 0, 0),
    ('CONFIDENTIAL', 'Confidential', 3, 'Business sensitive, limited distribution',            '#f59e0b', 1, 0),
    ('RESTRICTED',   'Restricted',   4, 'Highly sensitive, need-to-know basis',                '#f97316', 1, 1),
    ('TOP_SECRET',   'Top Secret',   5, 'Classified, maximum protection required',             '#ef4444', 1, 1);
END;
GO

-- ===== Seed: Blast Radius Config =====

IF NOT EXISTS (SELECT 1 FROM ops.BlastRadiusConfig WHERE ConfigId = 1)
BEGIN
  INSERT INTO ops.BlastRadiusConfig (MaxFleetPercent, MaxConcurrentActions, RequireApprovalAbovePercent, RollbackOnFailurePercent, MaintenanceWindowRequired, NotifyOnExecution, NotifyChannels)
  VALUES (10, 3, 5, 50, 0, 1, '["email","teams"]');
END;
GO

-- ===== Seed: Interactive Tutorials =====

IF NOT EXISTS (SELECT 1 FROM learning.InteractiveTutorial WHERE TutorialCode = 'TUT-PERF-101')
BEGIN
  INSERT INTO learning.InteractiveTutorial (TutorialCode, Title, Description, Category, DifficultyLevel, EstimatedMinutes, Steps, UsesRealData, Engine) VALUES
    ('TUT-PERF-101', 'Performance Troubleshooting 101', 'Guided walkthrough for diagnosing SQL Server performance issues using real fleet data', 'Performance', 1, 30, '[{"step":1,"title":"Identify the Problem","instruction":"Navigate to the Telemetry page and identify the server with the highest CPU","action":"navigate","target":"/telemetry"},{"step":2,"title":"Analyze Wait Stats","instruction":"Click the server to see its wait statistics. Which wait type is dominant?","action":"inspect","target":"waits"},{"step":3,"title":"Find the Culprit","instruction":"Go to Top Queries to find the query consuming the most CPU","action":"navigate","target":"/queries"},{"step":4,"title":"Check Indexes","instruction":"Does the query have missing index recommendations?","action":"analyze","target":"indexes"},{"step":5,"title":"Apply the Fix","instruction":"Use the Self-Healing page to apply the recommended index optimization","action":"navigate","target":"/self-healing"}]', 1, 'SQL Server'),
    ('TUT-INDEX-201', 'Index Optimization Masterclass', 'Learn to identify and fix index issues using your actual database metrics', 'Performance', 2, 45, '[{"step":1,"title":"Find Fragmented Indexes","instruction":"Open Telemetry and filter for index fragmentation above 30%","action":"navigate","target":"/telemetry"},{"step":2,"title":"Analyze Impact","instruction":"Identify which fragmented indexes are on high-traffic tables","action":"analyze","target":"index-impact"},{"step":3,"title":"Review Missing Indexes","instruction":"Check the missing index DMV recommendations","action":"inspect","target":"missing-indexes"},{"step":4,"title":"Calculate ROI","instruction":"Estimate the performance improvement from rebuilding vs reorganizing","action":"calculate","target":"roi"},{"step":5,"title":"Execute Maintenance","instruction":"Use Self-Healing to schedule index maintenance during your maintenance window","action":"navigate","target":"/self-healing"}]', 1, 'SQL Server'),
    ('TUT-SEC-301', 'Security Hardening Workshop', 'Hands-on security hardening for your SQL Server fleet', 'Security', 3, 60, '[{"step":1,"title":"Audit Current Posture","instruction":"Open Security Ops to review your fleet security score","action":"navigate","target":"/security-ops"},{"step":2,"title":"Find Vulnerabilities","instruction":"Check for unencrypted databases that contain sensitive data","action":"navigate","target":"/discovery-engine"},{"step":3,"title":"Review Credentials","instruction":"Identify stale logins and orphaned accounts","action":"navigate","target":"/credential-hygiene"},{"step":4,"title":"Enable TDE","instruction":"Use Self-Healing to enable TDE on databases with PII/PHI","action":"navigate","target":"/self-healing"},{"step":5,"title":"Verify Compliance","instruction":"Generate an evidence pack to prove your security improvements","action":"navigate","target":"/evidence-packs"}]', 1, 'SQL Server'),
    ('TUT-DR-201', 'Disaster Recovery Drill', 'Walk through a complete DR validation exercise', 'High Availability', 2, 45, '[{"step":1,"title":"Review DR Plans","instruction":"Open DR Validation and review your current DR plans","action":"navigate","target":"/dr-validation"},{"step":2,"title":"Check Recoverability","instruction":"Review the Recoverability page for your most critical databases","action":"navigate","target":"/recoverability"},{"step":3,"title":"Verify Backups","instruction":"Confirm restore verification tests have passed recently","action":"navigate","target":"/restore-verification"},{"step":4,"title":"Test Failover","instruction":"Simulate an AG failover using the Safe Remediation plan","action":"navigate","target":"/safe-remediation"},{"step":5,"title":"Document Results","instruction":"Record the drill results and update your DR plan","action":"navigate","target":"/dr-validation"}]', 0, 'SQL Server');
END;
GO

-- ===== Seed: Sample Runbook Definitions =====

IF NOT EXISTS (SELECT 1 FROM ops.RunbookDefinition WHERE RunbookCode = 'RB-HIGH-CPU')
BEGIN
  DECLARE @rb1 UNIQUEIDENTIFIER = NEWID();
  DECLARE @rb2 UNIQUEIDENTIFIER = NEWID();
  DECLARE @rb3 UNIQUEIDENTIFIER = NEWID();

  INSERT INTO ops.RunbookDefinition (RunbookId, RunbookName, RunbookCode, Description, TriggerCondition, Category, MaxBlastRadiusPercent, ProgressiveTrustLevel, CreatedBy) VALUES
    (@rb1, 'High CPU Auto-Resolution', 'RB-HIGH-CPU', 'Automated response to sustained high CPU: identify top query, check for plan regression, update stats or kill query', '{"metric":"cpuPercent","operator":">","threshold":90,"duration":"10m"}', 'Performance', 5, 'ApprovalRequired', 'system'),
    (@rb2, 'Log File Emergency', 'RB-LOG-FULL', 'Emergency log backup and optional shrink when transaction log approaches capacity', '{"metric":"logUsagePercent","operator":">","threshold":90}', 'Space', 10, 'Supervised', 'system'),
    (@rb3, 'AG Health Recovery', 'RB-AG-RECOVER', 'Detect and recover from AG synchronization issues including resume and failover', '{"metric":"agSyncState","operator":"!=","threshold":"SYNCHRONIZED","duration":"5m"}', 'HADR', 5, 'ApprovalRequired', 'system');

  INSERT INTO ops.RunbookStep (RunbookId, StepOrder, StepType, StepName, Configuration, OnFailure) VALUES
    (@rb1, 1, 'Condition', 'Check CPU still high', '{"query":"SELECT cpu_percent FROM sys.dm_os_ring_buffers","threshold":85}', 'Abort'),
    (@rb1, 2, 'SQLAction', 'Identify top CPU query', '{"query":"SELECT TOP 1 session_id, cpu_time FROM sys.dm_exec_requests ORDER BY cpu_time DESC"}', 'Continue'),
    (@rb1, 3, 'SQLAction', 'Update statistics on affected tables', '{"action":"UPDATE STATISTICS @Table WITH FULLSCAN"}', 'Continue'),
    (@rb1, 4, 'Condition', 'Recheck CPU after 60s', '{"wait":60,"query":"SELECT cpu_percent","threshold":80}', 'Continue'),
    (@rb1, 5, 'SQLAction', 'Kill runaway query if still high', '{"action":"KILL @SessionId","requiresApproval":true}', 'Abort'),
    (@rb1, 6, 'Notification', 'Notify team', '{"channels":["email","teams"],"message":"High CPU auto-resolved on @ServerName"}', 'Continue'),
    (@rb2, 1, 'SQLAction', 'Emergency log backup', '{"action":"BACKUP LOG @DB TO DISK = @Path WITH COMPRESSION"}', 'Abort'),
    (@rb2, 2, 'Condition', 'Check log size reduced', '{"query":"SELECT log_reuse_wait_desc FROM sys.databases","expected":"NOTHING"}', 'Continue'),
    (@rb2, 3, 'SQLAction', 'Shrink if safe', '{"action":"DBCC SHRINKFILE(@LogFile, @TargetMB)","condition":"logFreePercent > 50"}', 'Continue'),
    (@rb2, 4, 'Notification', 'Alert DBA team', '{"channels":["email"],"message":"Log emergency resolved on @DB"}', 'Continue'),
    (@rb3, 1, 'Condition', 'Verify secondary is accessible', '{"query":"SELECT is_local FROM sys.dm_hadr_availability_replica_states","expected":1}', 'Abort'),
    (@rb3, 2, 'SQLAction', 'Resume data movement', '{"action":"ALTER DATABASE @DB SET HADR RESUME"}', 'Continue'),
    (@rb3, 3, 'Condition', 'Wait for sync (5 min)', '{"wait":300,"query":"SELECT synchronization_state_desc","expected":"SYNCHRONIZED"}', 'Continue'),
    (@rb3, 4, 'Notification', 'Notify of resolution', '{"channels":["email","teams"],"message":"AG @AGName synchronization restored"}', 'Continue');
END;
GO

PRINT 'Schema v9 (Platinum Magic Completion) applied successfully.';
GO
