-- -----------------------------------------------------------------------------
-- WI.TWISTEPCONTENTCHECKLISTRESPONSE
-- Per-employee tick state for a "Simple" checklist content block (MWISTEPCONTENT,
-- CONTENTTYPE=4). Keyed by (WISTEPCONTENTID, EMPLOYEEID) rather than a
-- WIEXECUTIONID/WISTEPEXECUTIONID row — this screen (workstationdisplayscreen) is a
-- picklist-driven definition viewer that never calls StartExecution/InsertStepExecution,
-- so no WI.TWISTEPEXECUTION row exists to attach responses to. One row per employee per
-- checklist block, upserted on every tick.
-- -----------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
               WHERE TABLE_SCHEMA = 'WI' AND TABLE_NAME = 'TWISTEPCONTENTCHECKLISTRESPONSE')
BEGIN

CREATE TABLE WI.TWISTEPCONTENTCHECKLISTRESPONSE
(
    TWISTEPCONTENTCHECKLISTRESPONSEID  INT             IDENTITY(1,1) NOT NULL,
    WISTEPCONTENTID                    INT             NOT NULL,
    EMPLOYEEID                         INT             NOT NULL,
    RESPONSEJSON                       NVARCHAR(MAX)   NOT NULL,                                       -- [{itemId, isChecked}, ...]
    TENANTID                           INT             NOT NULL,
    CREATEDBYID                        INT             NOT NULL,
    CREATEDON                          DATETIME        NOT NULL CONSTRAINT DF_TWISTEPCONTENTCHKRESP_CREATEDON  DEFAULT GETDATE(),
    MODIFIEDBYID                       INT             NOT NULL,
    MODIFIEDON                         DATETIME        NOT NULL CONSTRAINT DF_TWISTEPCONTENTCHKRESP_MODIFIEDON DEFAULT GETDATE(),

    CONSTRAINT PK_TWISTEPCONTENTCHKRESP_ID                  PRIMARY KEY (TWISTEPCONTENTCHECKLISTRESPONSEID),
    CONSTRAINT FK_TWISTEPCONTENTCHKRESP_WISTEPCONTENTID     FOREIGN KEY (WISTEPCONTENTID) REFERENCES WI.MWISTEPCONTENT(WISTEPCONTENTID),
    CONSTRAINT UK_TWISTEPCONTENTCHKRESP_CONTENT_EMP_TENANT  UNIQUE (WISTEPCONTENTID, EMPLOYEEID, TENANTID)
);

-- Index: point lookup on load — GET_CHECKLIST_RESPONSE
CREATE INDEX IX_TWISTEPCONTENTCHKRESP_CONTENT_EMP
    ON WI.TWISTEPCONTENTCHECKLISTRESPONSE (WISTEPCONTENTID, EMPLOYEEID, TENANTID);

END