-- ============================================================
-- 056_MSWDDLOBJECTHISTORY.sql
--
-- Per-object ("file") version history for SW.MSWDDLSCRIPT — the git-blob-history analogue
-- this platform never had: MSWDDLSCRIPT.OBJECTNAME carries no uniqueness constraint and no
-- linkage between successive versions of the "same" object today (confirmed live: multiple
-- rows can already share an OBJECTNAME, e.g. a CREATE TABLE followed later by a separate
-- ALTER TABLE ADD COLUMN script, with nothing recording that the second supersedes the first).
--
-- This closes gaps doc #2 (cross-Gb5system package promotion): promoting a package from one
-- Gb5system's own control plane to another needs to know, for each object inside it, whether
-- the TARGET already has this exact content (by CHECKSUM) or an older/different version — the
-- same "does the remote already have this blob" question git answers via its own object graph.
-- DDLSCRIPTID/PACKAGEID are local AutoNumber surrogate keys, never portable across two
-- independent Gb5system installations; CHECKSUM (already computed, SHA-256, by
-- DdlScriptBLL.ComputeChecksum) is the one value that IS portable/comparable across them —
-- exactly like a git blob's own content hash being the thing that survives a clone, not the
-- object's local filesystem path.
--
-- One row per NEW version created (DdlScriptBLL.Save's isNew branch only — editing an
-- unapproved Draft in place before it's ever published doesn't get its own history entry,
-- same as git never versions an uncommitted working-tree edit). ISCURRENTTIP marks the tip of
-- each object's own chain; enforced via a filtered unique index rather than a second,
-- independently-queried "MAX" computation, so "what's the current version of object X" is a
-- single indexed lookup, not a graph walk.
--
-- Scope (client/industry/country/partner-specific customization) deliberately has NO column
-- here — PackageScopeType is a confirmed, deliberate "whole-package, not per-script" design
-- decision (SwDAL.Enums.PackageScopeType's own doc comment). A custom/client-specific object
-- stays namespace-separated by NAME instead, via the already-established platform-wide "Z_"
-- prefix convention (043_ALTER_MSWREFDATASOURCE_ScopeType.sql's own header) — a client-custom
-- object is simply a differently-named object with its own independent history chain, bundled
-- into a Client/Industry/Country-scoped package rather than a Universal one. No scope
-- dimension needed on this table for that to work correctly.
-- ============================================================

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE schema_id = SCHEMA_ID('SW') AND name = 'MSWDDLOBJECTHISTORY')
BEGIN
    CREATE TABLE SW.MSWDDLOBJECTHISTORY (
        DDLOBJECTHISTORYID  INT           NOT NULL,
        DBMODELID           INT           NOT NULL,
        SCHEMANAME          NVARCHAR(128) NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_SCHEMA DEFAULT (N'dbo'),
        OBJECTNAME          NVARCHAR(500) NOT NULL,
        DDLSCRIPTID         INT           NOT NULL,
        PREVIOUSDDLSCRIPTID INT           NULL,
        CHECKSUM            NVARCHAR(64)  NOT NULL,
        ISCURRENTTIP        BIT           NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_ISTIP DEFAULT (1),
        VERSION             SMALLINT      NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_VERSION DEFAULT (0),
        STATUS              TINYINT       NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_ROWSTATUS DEFAULT (1),
        SORTORDER           SMALLINT      NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_SORTORDER DEFAULT (9999),
        CREATEDBYID         INT           NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_CREATEDBYID DEFAULT (-1),
        CREATEDON           DATETIME2     NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_CREATEDON DEFAULT (SYSUTCDATETIME()),
        MODIFIEDBYID        INT           NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_MODIFIEDBYID DEFAULT (-1),
        MODIFIEDON          DATETIME2     NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_MODIFIEDON DEFAULT (SYSUTCDATETIME()),
        SOURCETYPE          TINYINT       NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_SOURCETYPE DEFAULT (5),
        TENANTID            INT           NOT NULL CONSTRAINT DF_MSWDDLOBJHIST_TENANTID DEFAULT (-1),
        CONSTRAINT PK_MSWDDLOBJECTHISTORY PRIMARY KEY (DDLOBJECTHISTORYID),
        CONSTRAINT FK_MSWDDLOBJHIST_MODEL FOREIGN KEY (DBMODELID) REFERENCES SW.MSWDBMODEL (DBMODELID),
        CONSTRAINT FK_MSWDDLOBJHIST_SCRIPT FOREIGN KEY (DDLSCRIPTID) REFERENCES SW.MSWDDLSCRIPT (DDLSCRIPTID),
        CONSTRAINT FK_MSWDDLOBJHIST_PREVSCRIPT FOREIGN KEY (PREVIOUSDDLSCRIPTID) REFERENCES SW.MSWDDLSCRIPT (DDLSCRIPTID),
        CONSTRAINT CK_MSWDDLOBJHIST_ISTIP CHECK (ISCURRENTTIP IN (0,1))
    );

    -- Enforces "at most one current tip per object" as a real data-integrity guarantee, not
    -- just an app-level convention — mirrors this table's own git-blob-history analogy: a file
    -- has exactly one current state at any point in time.
    CREATE UNIQUE INDEX UX_MSWDDLOBJHIST_CURRENTTIP
        ON SW.MSWDDLOBJECTHISTORY (DBMODELID, SCHEMANAME, OBJECTNAME)
        WHERE ISCURRENTTIP = 1;

    CREATE INDEX IX_MSWDDLOBJHIST_DDLSCRIPTID ON SW.MSWDDLOBJECTHISTORY (DDLSCRIPTID);
    CREATE INDEX IX_MSWDDLOBJHIST_CHECKSUM ON SW.MSWDDLOBJECTHISTORY (CHECKSUM);
END;
GO