namespace AnalyticsDAL.Query.Workspace
{
    public static class WorkspaceQB
    {
        // Index: PK_MWORKSPACE_WORKSPACEID, UK_MWORKSPACE_WORKSPACECODE_TENANTID
        public const string GET_WORKSPACE = @"
            SELECT  w.WORKSPACEID       AS WorkspaceId,
                    w.WORKSPACECODE     AS WorkspaceCode,
                    w.WORKSPACENAME     AS WorkspaceName,
                    w.DESCRIPTION       AS Description,
                    w.WORKSPACETYPE     AS WorkspaceType,
                    w.ISDEFAULT         AS IsDefault,
                    w.SETTINGS          AS Settings,
                    w.VERSION           AS Version,
                    w.STATUS            AS Status,
                    w.SORTORDER         AS SortOrder,
                    w.SOURCETYPE        AS SourceType,
                    w.TENANTID          AS TenantId,
                    w.CREATEDBYID       AS CreatedById,
                    w.CREATEDON         AS CreatedOn,
                    cb.USERNAME         AS CreatedByName,
                    w.MODIFIEDBYID      AS ModifiedById,
                    w.MODIFIEDON        AS ModifiedOn,
                    mb.USERNAME         AS ModifiedByName
            FROM    MWORKSPACE w
            LEFT JOIN MUSER cb ON cb.USERID = w.CREATEDBYID
            LEFT JOIN MUSER mb ON mb.USERID = w.MODIFIEDBYID
            WHERE   w.WORKSPACEID = @WorkspaceId
            AND     w.TENANTID    = @TenantId
            AND     w.STATUS     <> 2";

        // Index: IX_MDASHBOARD_WORKSPACEID_TENANTID (partial — use MWORKSPACE's own index)
        public const string GET_SELECTLIST_WORKSPACE = @"
            SELECT  w.WORKSPACEID   AS WorkspaceId,
                    w.WORKSPACECODE AS WorkspaceCode,
                    w.WORKSPACENAME AS WorkspaceName,
                    w.WORKSPACETYPE AS WorkspaceType,
                    w.ISDEFAULT     AS IsDefault
            FROM    MWORKSPACE w
            WHERE   w.TENANTID  = @TenantId
            AND     w.STATUS   <> 2
            ORDER BY w.SORTORDER, w.WORKSPACENAME";

        public const string CHECK_DUPLICATE_CODE = @"
            SELECT COUNT(1)
            FROM   MWORKSPACE
            WHERE  WORKSPACECODE = @WorkspaceCode
            AND    TENANTID      = @TenantId
            AND    STATUS       <> 2
            AND    WORKSPACEID  <> @WorkspaceId";

        public const string CHECK_DUPLICATE_NAME = @"
            SELECT COUNT(1)
            FROM   MWORKSPACE
            WHERE  WORKSPACENAME = @WorkspaceName
            AND    TENANTID      = @TenantId
            AND    STATUS       <> 2
            AND    WORKSPACEID  <> @WorkspaceId";

        public const string SAVE_WORKSPACE = @"
            INSERT INTO MWORKSPACE
                (WORKSPACEID, WORKSPACECODE, WORKSPACENAME, DESCRIPTION,
                 WORKSPACETYPE, ISDEFAULT, SETTINGS,
                 VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@WorkspaceId, @WorkspaceCode, @WorkspaceName, @Description,
                 @WorkspaceType, @IsDefault, @Settings,
                 0, 1, @SortOrder, @SourceType, @TenantId,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE())";

        public const string UPDATE_WORKSPACE = @"
            UPDATE MWORKSPACE
            SET    WORKSPACECODE  = @WorkspaceCode,
                   WORKSPACENAME  = @WorkspaceName,
                   DESCRIPTION    = @Description,
                   WORKSPACETYPE  = @WorkspaceType,
                   ISDEFAULT      = @IsDefault,
                   SETTINGS       = @Settings,
                   SORTORDER      = @SortOrder,
                   STATUS         = @Status,
                   VERSION        = VERSION + 1,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE()
            WHERE  WORKSPACEID    = @WorkspaceId
            AND    TENANTID       = @TenantId";

        public const string DELETE_WORKSPACE = @"
            UPDATE MWORKSPACE
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  WORKSPACEID  = @WorkspaceId
            AND    TENANTID     = @TenantId";

        // Unscoped by design (no TENANTID filter) — used only by the ownership visibility
        // guard, which must be able to see a shared (TENANTID = -1) row to compare it against
        // the caller's TenantId in code. GET_WORKSPACE cannot be reused here because it already
        // filters WHERE TENANTID = @TenantId and would never find a shared row for a different caller.
        public const string GET_WORKSPACE_TENANTID = @"
            SELECT  w.TENANTID
            FROM    MWORKSPACE w
            WHERE   w.WORKSPACEID = @WorkspaceId
            AND     w.STATUS     <> 2";
    }
}