-- v26 — Software release catalog (multi-OS installer download portal)

IF OBJECT_ID('deploy.SoftwareRelease', 'U') IS NULL
BEGIN
  CREATE TABLE deploy.SoftwareRelease (
    ReleaseId        UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ProductCode      NVARCHAR(64) NOT NULL,
    Version          NVARCHAR(32) NOT NULL,
    Channel          NVARCHAR(32) NOT NULL DEFAULT 'stable',
    Title            NVARCHAR(256) NOT NULL,
    ReleaseNotes     NVARCHAR(MAX) NULL,
    MinLicenseTier   NVARCHAR(64) NULL,
    IsPublished      BIT NOT NULL DEFAULT 0,
    PublishedAt      DATETIME2 NULL,
    CreatedAt        DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    CreatedBy        NVARCHAR(256) NULL,
    CONSTRAINT UQ_SoftwareRelease_ProductVersion UNIQUE (ProductCode, Version, Channel)
  );
  CREATE INDEX IX_SoftwareRelease_Published ON deploy.SoftwareRelease (IsPublished, PublishedAt DESC);
END;
GO

IF OBJECT_ID('deploy.SoftwareReleaseArtifact', 'U') IS NULL
BEGIN
  CREATE TABLE deploy.SoftwareReleaseArtifact (
    ArtifactId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ReleaseId        UNIQUEIDENTIFIER NOT NULL REFERENCES deploy.SoftwareRelease(ReleaseId),
    Platform         NVARCHAR(32) NOT NULL,
    Architecture     NVARCHAR(32) NOT NULL DEFAULT 'x64',
    PackageType      NVARCHAR(32) NOT NULL,
    FileName         NVARCHAR(512) NOT NULL,
    StoragePath      NVARCHAR(1024) NOT NULL,
    Sha256Hex        NVARCHAR(64) NOT NULL,
    FileSizeBytes    BIGINT NOT NULL,
    ContentType      NVARCHAR(128) NULL,
    InstallCommand   NVARCHAR(512) NULL,
    IsActive         BIT NOT NULL DEFAULT 1,
    DownloadCount    INT NOT NULL DEFAULT 0,
    UploadedAt       DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    UploadedBy       NVARCHAR(256) NULL,
    CONSTRAINT UQ_ReleaseArtifact_File UNIQUE (ReleaseId, Platform, Architecture, PackageType)
  );
  CREATE INDEX IX_ReleaseArtifact_Release ON deploy.SoftwareReleaseArtifact (ReleaseId, IsActive);
END;
GO

IF OBJECT_ID('deploy.DownloadAudit', 'U') IS NULL
BEGIN
  CREATE TABLE deploy.DownloadAudit (
    DownloadId       UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,
    ArtifactId       UNIQUEIDENTIFIER NOT NULL REFERENCES deploy.SoftwareReleaseArtifact(ArtifactId),
    UserEmail        NVARCHAR(256) NULL,
    ClientIp         NVARCHAR(64) NULL,
    UserAgent        NVARCHAR(512) NULL,
    DownloadedAt     DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
  );
END;
GO
