-- =============================================================================
-- ENABLEMENT (SHOW) — SPACE, CHANNEL, CONTENT LINK
-- GoodBooks · SQL Server · GB5
-- Version : 1.0
-- Tables  : MSHOWSPACE, MSHOWCHANNEL, TSHOWCHANNEL, TSHOWCONTENTLINK
--
-- TSHOWRULE and TSHOWSCHEDULE already exist in the database (read by
-- EnablementDAL.Query.Show.ShowResolverQB) — no migration needed for those,
-- only new write SQL (see ShowRuleQB.cs / ShowScheduleQB.cs).
-- =============================================================================


-- =============================================================================
-- TABLE : MSHOWSPACE
-- Purpose: "Knowledge Space" — top-level content category a Show belongs to.
--          One Space per Show (TSHOW.SPACEID FK, already present on TSHOW).
-- =============================================================================

CREATE TABLE [DBO].MSHOWSPACE
(
    -- PK
    SPACEID             INT             NOT NULL,

    -- BUSINESS COLUMNS
    SPACECODE           NVARCHAR(50)    NOT NULL,   -- e.g. 'IT_NOTICES'
    SPACENAME           NVARCHAR(200)   NOT NULL,   -- e.g. 'IT & System Notices'
    DESCRIPTION         NVARCHAR(1000),
    OWNERTEAM           NVARCHAR(200),              -- e.g. 'IT Team'
    ICON                NVARCHAR(10),               -- emoji, e.g. '🛡️'
    COLORHEX            NVARCHAR(10)    NOT NULL CONSTRAINT DF_MSHOWSPACE_COLORHEX      DEFAULT '#2563EB',
    ISACTIVE            TINYINT         NOT NULL CONSTRAINT DF_MSHOWSPACE_ISACTIVE      DEFAULT 1,  -- 0 No, 1 Yes

    -- STANDARD FIELDS
    VERSION             SMALLINT        NOT NULL CONSTRAINT DF_MSHOWSPACE_VERSION       DEFAULT 0,
    STATUS              TINYINT         NOT NULL CONSTRAINT DF_MSHOWSPACE_STATUS        DEFAULT 1,
    SORTORDER           SMALLINT        NOT NULL CONSTRAINT DF_MSHOWSPACE_SORTORDER     DEFAULT 9999,
    CREATEDBYID         INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_MSHOWSPACE_CREATEDON     DEFAULT GETDATE(),
    MODIFIEDBYID        INT             NOT NULL,
    MODIFIEDON          DATETIME        NOT NULL CONSTRAINT DF_MSHOWSPACE_MODIFIEDON    DEFAULT GETDATE(),
    SOURCETYPE          TINYINT         NOT NULL CONSTRAINT DF_MSHOWSPACE_SOURCETYPE    DEFAULT 5,
    TENANTID            INT             NOT NULL CONSTRAINT DF_MSHOWSPACE_TENANTID      DEFAULT -1,

    CONSTRAINT PK_MSHOWSPACE_SPACEID                PRIMARY KEY (SPACEID),
    CONSTRAINT FK_MSHOWSPACE_CREATEDBYID            FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID),
    CONSTRAINT FK_MSHOWSPACE_MODIFIEDBYID           FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_MSHOWSPACE_TENANTID               FOREIGN KEY (TENANTID)      REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT UQ_MSHOWSPACE_SPACECODE_TENANTID     UNIQUE (SPACECODE, TENANTID),
    CONSTRAINT CK_MSHOWSPACE_ISACTIVE               CHECK (ISACTIVE   IN (0,1)),
    CONSTRAINT CK_MSHOWSPACE_STATUS                 CHECK (STATUS     IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MSHOWSPACE_SOURCETYPE             CHECK (SOURCETYPE IN (1,2,3,4,5))
)
GO


-- =============================================================================
-- TABLE : MSHOWCHANNEL
-- Purpose: Subscription-based delivery pipe for Shows.
--
-- SUBSCRIPTIONTYPE values:
--   0 = Mandatory           → all targeted users, cannot unsubscribe
--   1 = Auto-subscribed     → subscribed by default, can opt out
--   2 = Optional            → user must opt in
--
-- AUTOSUBSCRIBERULE values:
--   0 = All users
--   1 = By role
--   2 = By OU
--   3 = Manual only
-- =============================================================================

CREATE TABLE [DBO].MSHOWCHANNEL
(
    -- PK
    CHANNELID           INT             NOT NULL,

    -- BUSINESS COLUMNS
    CHANNELCODE         NVARCHAR(50)    NOT NULL,   -- e.g. 'IT_ALERTS'
    CHANNELNAME         NVARCHAR(200)   NOT NULL,   -- e.g. 'IT Alerts'
    DESCRIPTION         NVARCHAR(1000),
    SUBSCRIPTIONTYPE    TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_SUBSCRIPTIONTYPE   DEFAULT 2,  -- 0 Mandatory, 1 Auto-subscribed, 2 Optional
    AUTOSUBSCRIBERULE   TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_AUTOSUBSCRIBERULE  DEFAULT 3,  -- 0 All, 1 ByRole, 2 ByOU, 3 ManualOnly
    ISPORTALINTERNAL    TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_ISPORTALINTERNAL   DEFAULT 1,
    ISPORTALESS         TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_ISPORTALESS        DEFAULT 0,
    ISPORTALVENDOR      TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_ISPORTALVENDOR     DEFAULT 0,
    ISPORTALCUSTOMER    TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_ISPORTALCUSTOMER   DEFAULT 0,
    ISACTIVE            TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_ISACTIVE           DEFAULT 1,  -- 0 No, 1 Yes

    -- STANDARD FIELDS
    VERSION             SMALLINT        NOT NULL CONSTRAINT DF_MSHOWCHANNEL_VERSION        DEFAULT 0,
    STATUS              TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_STATUS         DEFAULT 1,
    SORTORDER           SMALLINT        NOT NULL CONSTRAINT DF_MSHOWCHANNEL_SORTORDER      DEFAULT 9999,
    CREATEDBYID         INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_MSHOWCHANNEL_CREATEDON      DEFAULT GETDATE(),
    MODIFIEDBYID        INT             NOT NULL,
    MODIFIEDON          DATETIME        NOT NULL CONSTRAINT DF_MSHOWCHANNEL_MODIFIEDON     DEFAULT GETDATE(),
    SOURCETYPE          TINYINT         NOT NULL CONSTRAINT DF_MSHOWCHANNEL_SOURCETYPE     DEFAULT 5,
    TENANTID            INT             NOT NULL CONSTRAINT DF_MSHOWCHANNEL_TENANTID       DEFAULT -1,

    CONSTRAINT PK_MSHOWCHANNEL_CHANNELID            PRIMARY KEY (CHANNELID),
    CONSTRAINT FK_MSHOWCHANNEL_CREATEDBYID          FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID),
    CONSTRAINT FK_MSHOWCHANNEL_MODIFIEDBYID         FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_MSHOWCHANNEL_TENANTID             FOREIGN KEY (TENANTID)      REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT UQ_MSHOWCHANNEL_CHANNELCODE_TENANTID UNIQUE (CHANNELCODE, TENANTID),
    CONSTRAINT CK_MSHOWCHANNEL_SUBSCRIPTIONTYPE     CHECK (SUBSCRIPTIONTYPE  IN (0,1,2)),
    CONSTRAINT CK_MSHOWCHANNEL_AUTOSUBSCRIBERULE    CHECK (AUTOSUBSCRIBERULE IN (0,1,2,3)),
    CONSTRAINT CK_MSHOWCHANNEL_ISPORTALINTERNAL     CHECK (ISPORTALINTERNAL  IN (0,1)),
    CONSTRAINT CK_MSHOWCHANNEL_ISPORTALESS          CHECK (ISPORTALESS       IN (0,1)),
    CONSTRAINT CK_MSHOWCHANNEL_ISPORTALVENDOR       CHECK (ISPORTALVENDOR    IN (0,1)),
    CONSTRAINT CK_MSHOWCHANNEL_ISPORTALCUSTOMER     CHECK (ISPORTALCUSTOMER  IN (0,1)),
    CONSTRAINT CK_MSHOWCHANNEL_ISACTIVE             CHECK (ISACTIVE          IN (0,1)),
    CONSTRAINT CK_MSHOWCHANNEL_STATUS               CHECK (STATUS            IN (0,1,2,3,4,5)),
    CONSTRAINT CK_MSHOWCHANNEL_SOURCETYPE           CHECK (SOURCETYPE        IN (1,2,3,4,5))
)
GO


-- =============================================================================
-- TABLE : TSHOWCHANNEL
-- Purpose: Many-to-many assignment — a Show can be delivered on one or more
--          Channels. TSHOW.CHANNELID (existing single-value column) is kept
--          as the "primary" channel for backward compatibility; the full set
--          of assigned channels lives here.
-- =============================================================================

CREATE TABLE [DBO].TSHOWCHANNEL
(
    -- PK
    SHOWCHANNELID       INT             NOT NULL,

    -- PARENT FKs
    SHOWID              INT             NOT NULL,
    CHANNELID           INT             NOT NULL,

    -- STANDARD FIELDS
    VERSION             SMALLINT        NOT NULL CONSTRAINT DF_TSHOWCHANNEL_VERSION       DEFAULT 0,
    STATUS              TINYINT         NOT NULL CONSTRAINT DF_TSHOWCHANNEL_STATUS        DEFAULT 1,
    SORTORDER           SMALLINT        NOT NULL CONSTRAINT DF_TSHOWCHANNEL_SORTORDER     DEFAULT 9999,
    CREATEDBYID         INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_TSHOWCHANNEL_CREATEDON     DEFAULT GETDATE(),
    MODIFIEDBYID        INT             NOT NULL,
    MODIFIEDON          DATETIME        NOT NULL CONSTRAINT DF_TSHOWCHANNEL_MODIFIEDON    DEFAULT GETDATE(),
    SOURCETYPE          TINYINT         NOT NULL CONSTRAINT DF_TSHOWCHANNEL_SOURCETYPE    DEFAULT 5,
    TENANTID            INT             NOT NULL CONSTRAINT DF_TSHOWCHANNEL_TENANTID      DEFAULT -1,

    CONSTRAINT PK_TSHOWCHANNEL_SHOWCHANNELID        PRIMARY KEY (SHOWCHANNELID),
    CONSTRAINT FK_TSHOWCHANNEL_SHOWID               FOREIGN KEY (SHOWID)        REFERENCES TSHOW(SHOWID),
    CONSTRAINT FK_TSHOWCHANNEL_CHANNELID            FOREIGN KEY (CHANNELID)     REFERENCES MSHOWCHANNEL(CHANNELID),
    CONSTRAINT FK_TSHOWCHANNEL_CREATEDBYID          FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID),
    CONSTRAINT FK_TSHOWCHANNEL_MODIFIEDBYID         FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TSHOWCHANNEL_TENANTID             FOREIGN KEY (TENANTID)      REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT UQ_TSHOWCHANNEL_SHOWID_CHANNELID     UNIQUE (SHOWID, CHANNELID),
    CONSTRAINT CK_TSHOWCHANNEL_STATUS               CHECK (STATUS     IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TSHOWCHANNEL_SOURCETYPE           CHECK (SOURCETYPE IN (1,2,3,4,5))
)
GO


-- =============================================================================
-- TABLE : TSHOWCONTENTLINK
-- Verbatim from ~/Downloads/enablement/enablement-resolver-ddl.sql (lines 168-212)
-- Purpose: Links a show or a specific step to external or internal content.
--          When STEPID = -1, the link belongs to the show header level.
--
-- LINKTYPE values:      1 InternalCMS, 2 ExternalURL, 3 File, 4 Video, 5 Document
-- DISPLAYHINT values:   0 Inline, 1 SidePanel, 2 NewTab, 3 Download
-- =============================================================================

CREATE TABLE [DBO].TSHOWCONTENTLINK
(
    -- PK
    SHOWCONTENTLINKID   INT             NOT NULL,

    -- PARENT FKs
    SHOWID              INT             NOT NULL,
    STEPID              INT             NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_STEPID     DEFAULT -1,  -- -1 = show-level link

    -- SEQUENCE
    SLNO                SMALLINT        NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_SLNO       DEFAULT 1,

    -- BUSINESS COLUMNS
    LINKTYPE            TINYINT         NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_LINKTYPE   DEFAULT 1,  -- 1 InternalCMS, 2 ExternalURL, 3 File, 4 Video, 5 Document
    CONTENTID           INT             NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_CONTENTID  DEFAULT -1,  -- -1 = not applicable
    LINKURL             NVARCHAR(2000),
    LINKTITLE           NVARCHAR(200),
    DISPLAYHINT         TINYINT         NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_DISPLAYHINT DEFAULT 0, -- 0 Inline, 1 SidePanel, 2 NewTab, 3 Download
    ISACTIVE            TINYINT         NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_ISACTIVE   DEFAULT 1,  -- 0 No, 1 Yes
    REMARKS             NVARCHAR(1000),

    -- STANDARD FIELDS
    VERSION             SMALLINT        NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_VERSION    DEFAULT 0,
    STATUS              TINYINT         NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_STATUS     DEFAULT 1,
    SORTORDER           SMALLINT        NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_SORTORDER  DEFAULT 9999,
    CREATEDBYID         INT             NOT NULL,
    CREATEDON           DATETIME        NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_CREATEDON  DEFAULT GETDATE(),
    MODIFIEDBYID        INT             NOT NULL,
    MODIFIEDON          DATETIME        NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_MODIFIEDON DEFAULT GETDATE(),
    SOURCETYPE          TINYINT         NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_SOURCETYPE DEFAULT 5,
    TENANTID            INT             NOT NULL CONSTRAINT DF_TSHOWCONTENTLINK_TENANTID   DEFAULT -1,

    CONSTRAINT PK_TSHOWCONTENTLINK_SHOWCONTENTLINKID    PRIMARY KEY (SHOWCONTENTLINKID),
    CONSTRAINT FK_TSHOWCONTENTLINK_SHOWID               FOREIGN KEY (SHOWID)        REFERENCES TSHOW(SHOWID),
    CONSTRAINT FK_TSHOWCONTENTLINK_STEPID               FOREIGN KEY (STEPID)        REFERENCES TSHOWSTEP(STEPID),
    CONSTRAINT FK_TSHOWCONTENTLINK_CREATEDBYID          FOREIGN KEY (CREATEDBYID)   REFERENCES MUSER(USERID),
    CONSTRAINT FK_TSHOWCONTENTLINK_MODIFIEDBYID         FOREIGN KEY (MODIFIEDBYID)  REFERENCES MUSER(USERID),
    CONSTRAINT FK_TSHOWCONTENTLINK_TENANTID             FOREIGN KEY (TENANTID)      REFERENCES MCLIENT(CLIENTID),
    CONSTRAINT CK_TSHOWCONTENTLINK_LINKTYPE             CHECK (LINKTYPE     IN (1,2,3,4,5)),
    CONSTRAINT CK_TSHOWCONTENTLINK_DISPLAYHINT          CHECK (DISPLAYHINT  IN (0,1,2,3)),
    CONSTRAINT CK_TSHOWCONTENTLINK_ISACTIVE             CHECK (ISACTIVE     IN (0,1)),
    CONSTRAINT CK_TSHOWCONTENTLINK_STATUS               CHECK (STATUS       IN (0,1,2,3,4,5)),
    CONSTRAINT CK_TSHOWCONTENTLINK_SOURCETYPE           CHECK (SOURCETYPE   IN (1,2,3,4,5))
)
GO


-- =============================================================================
-- MAUTONUMBER seed rows — one per new PK-generating entity.
-- Follows the pattern in GB5Solution/MM/MMDAL/Query/MTR/mtr.sql.
-- =============================================================================

INSERT INTO MAUTONUMBER (ENTITYCODE, AUTOID)
SELECT 'SHOWRULE', 0
WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'SHOWRULE');
GO

INSERT INTO MAUTONUMBER (ENTITYCODE, AUTOID)
SELECT 'SHOWSCHEDULE', 0
WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'SHOWSCHEDULE');
GO

INSERT INTO MAUTONUMBER (ENTITYCODE, AUTOID)
SELECT 'SHOWSPACE', 0
WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'SHOWSPACE');
GO

INSERT INTO MAUTONUMBER (ENTITYCODE, AUTOID)
SELECT 'SHOWCHANNEL', 0
WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'SHOWCHANNEL');
GO

INSERT INTO MAUTONUMBER (ENTITYCODE, AUTOID)
SELECT 'SHOWCHANNELASSIGNMENT', 0
WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'SHOWCHANNELASSIGNMENT');
GO

INSERT INTO MAUTONUMBER (ENTITYCODE, AUTOID)
SELECT 'SHOWCONTENTLINK', 0
WHERE NOT EXISTS (SELECT 1 FROM MAUTONUMBER WHERE ENTITYCODE = 'SHOWCONTENTLINK');
GO


-- =============================================================================
-- MANUAL FOLLOW-UP — NOT automated by this script:
--
-- 1. MEVENTTYPE seed rows for the new audit events (ShowRule/ShowSchedule/
--    ShowSpace/ShowChannel/ShowContentLink Created/Updated/Deleted), per the
--    Observability Definition of Done in CLAUDE.md. EVENTTYPEID allocation is
--    centrally managed — coordinate with whoever owns the MEVENTTYPE ID range
--    before inserting, to avoid colliding with another module's seed script.
--
-- 2. Default data seed (10 Spaces / 7 Channels from the spec doc) — see
--    GB5Solution/Enablement/Migration/V002__ShowSpaceChannelDefaultData.sql
--    (create once SaveShowSpace/SaveShowChannel are live, or seed directly
--    here with explicit IDs if MAUTONUMBER-driven inserts aren't desired for
--    seed data).
-- =============================================================================